1 / 40

Managing an Oracle Instance

Managing an Oracle Instance. Objectives. Setting up operating system and password file authentication Creating the parameter file Starting up an instance and opening the database Closing a database and shutting down the instance Getting and setting parameter values Managing sessions

vivien
Download Presentation

Managing an Oracle Instance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing an Oracle Instance

  2. Objectives • Setting up operating system and password file authentication • Creating the parameter file • Starting up an instance and opening the database • Closing a database and shutting down the instance • Getting and setting parameter values • Managing sessions • Monitoring ALERT and trace files

  3. Userprocess Serverprocess PGA ALERTfile Control files Parameterfile Redo logfiles Data files Passwordfile Overview Instance SGA Shared pool

  4. Database Administrator Users The two database administrator usersSYS and SYSTEM are: • Automatically created • Granted the DBA role

  5. SYS Password: change_on_install Owner of the database data dictionary SYSTEM Password: manager Owner of additional internal tables used by Oracle tools User SYS and SYSTEM

  6. Authentication Methods Remote database administration Local database administration Yes Yes Do you have a secure connection? Do you want to use OS authentication? Use OS authentication No No Use a password file

  7. Operating System Authentication • Set up the user to be authenticated by the operating system. • Set REMOTE_LOGIN_PASSWORDFILE (in init.ora) to NONE. • Use the following commands to connect to a database: CONNECT / AS SYSDBACONNECT / AS SYSOPER

  8. Using Password File Authentication • Create the password file using the password utility: • Set REMOTE_LOGIN_PASSWORDFILE (init.ora) to EXCLUSIVE or SHARED • Use the following command to connect to a database: $orapwd file=$ORACLE_HOME/dbs/orapwU15\ password=admin entries=5 CONNECT INTERNAL/ADMIN

  9. Changing the Internal Password • Use the password utility on NT and UNIX to delete and create the password file. • or • Use the ORADIM80 utility on NT to delete and create a new password file.

  10. Uses of Parameters • init<SID>.ora • Size the SGA • Set database and instance defaults • Set user or process limits • Define various physical attributes (e.g., database block size) • Specify control files, datafiles, redo log files, etc.

  11. The Initialization Parameter File Instance SGA Shared pool Librarycache Database buffercache Redo logbuffer Datadictionarycache DBWR PMON CKPT LGWR ARCH SMON initU15.ora SVRMGR> CONNECT / AS SYSDBA SVRMGR> STARTUP PFILE=/DISK1/initU15.ora

  12. Parameter File Example • # Initialization Parameter File: initU15.ora • db_name = U15 • control_files = (/DISK1/control01.con, • /DISK2/control02.con) • db_block_size = 8192 • db_block_buffers = 2000 • shared_pool_size = 30000000 • log_buffer = 64K • processes = 50 • db_files = 100 • log_files = 10 • max_dump_file_size = 10240 • background_dump_dest = (/home/disk3/user15/BDUMP) • user_dump_dest = (/home/disk3/user15/UDUMP) • core_dump_dest = (/home/disk3/user15/CDUMP) • rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08) • ...

  13. Startup and Shutdown in Stages OPEN STARTUP All files opened as described by the control file for this instance. MOUNT Control file opened for this instance. NOMOUNT SHUTDOWN Instance started. SHUTDOWN

  14. Startup and Shutdown (cont.) • Starting an instance includes: • Reading the parameter file • Allocating the SGA • Starting the background processes • Opening the ALERT and trace files

  15. STARTUP Command Start up the instance, and open the database. STARTUP PFILE=/DISK1/initU15.ora STARTUP [FORCE][RESTRICT][PFILE = filename] [OPEN [RECOVER][database] | MOUNT | NOMOUNT]

  16. STARTUP Command OPEN: enables users to access the database MOUNT: mounts the database for certain activities but does not allow user access to db NOMOUNT: creates SGA + bg processes; no access to database FORCE: aborts the running instance before performing a normal startup RESTRICT: restricted access RECOVER: begins media recovery

  17. ALTER DATABASE Command • Change state of the database from NOMOUNT to MOUNT • ALTER DATABASE database MOUNT • Open the database as a read-only database • ALTER DATABASE database OPEN READ ONLY

  18. Opening a Database in Read-Only Mode • Any database can be opened as a read-only database • A read-only database can be used to: • Execute queries • Execute disk sorts • Take datafiles offline and online • Perform recovery of offline data files

  19. Shutdown Options Shutdown Mode Allow new connections Wait until current sessions end Wait until current transactions end Force a checkpoint and close files A X X X X I X X X O T X X O O N X O O O Shutdown mode: A Abort I Immediate T Transactional N Normal NO YES

  20. Shutdown Time • Normal 4 3 2 1 Immediate Abort Transactional Time

  21. Oracle Memory and Background Processes: Connecting to a Shared Server Red lines trace the path between the client and the instance

  22. Oracle Memory and Background Processes: Connecting to a Dedicated Server Red lines trace the path between the client and the instance

  23. Oracle Memory : PGA & SGA The SGA is allocated when the instance starts up The PGA has private areas for each application

  24. Oracle Memory : SGA Components The shared pool stores parsed SQL commands for possible reuse The buffer cache stores data blocks read from the datafiles

  25. Oracle Memory: SGA Components The redo log buffer stores all changes to data before the changes are written to the datafiles The cursor pool is optional and provides extra memory for program cursors The large pool is optional and adds more memory for better response time

  26. Background Processes: PMON & SMON PMON (Process Monitor) cleans up remaining resources after the server and user processes end SMON (System Monitor) handles recovery if needed, cleans up old temporary tables, and restores space

  27. Background Processes: DBWn & CKPT DBWn (Database Writer) writes modified buffers to the datafiles CKPT (Checkpoint Process) signals the DBWn process and assigns SCN

  28. Background Processes: ARCn & LGWR ARCn (Archiver) copies redo log files to archive log files LGWR (Log Writer) writes redo log buffers to the redo log files

  29. Background Processes: RECO & LMS RECO (Recoverer Process) fixes errors if changes across distributed databases fail LMS (Lock Manager Service) handles updates that affect data in multiple clustered databases

  30. Dynamic Performance Views • Maintained by the Oracle Server and continuously updated • Contain data on disk and memory structures • Contain data that is useful for performance tuning • Have public synonyms with the prefix V$

  31. Accessing Dynamic Performance Views OPEN Data dictionary MOUNT Dynamic performance views reading data from disk NOMOUNT Dynamic performance views reading from memory SHUTDOWN

  32. Example V$PARAMETERV$SGAV$OPTIONV$PROCESSV$SESSION V$VERSIONV$INSTANCE SGA V$THREADV$CONTROLFILE V$DATABASEV$DATAFILEV$DATAFILE_HEADER V$LOGFILE Control file

  33. Displaying Current Parameter Values • Use the Server Manager command: SHOW PARAMETER control • Query the dynamic performance viewV$PARAMETER: SELECT name FROM v$parameter WHERE name LIKE ‘%control%’;

  34. Dynamic Initialization Parameters Some initialization parameters can bemodified while an instance is running. ALTER SESSION SET SQL_TRACE=true; ALTER SYSTEM SET TIMED_STATISTICS=true; ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED;

  35. Enable and Disable Restricted Session • Use the STARTUP command to restrict access to a database: STARTUP RESTRICT • Use the ALTER SYSTEM command to place an instance in restricted mode: ALTER SYSTEM ENABLE RESTRICTED SESSION;

  36. Terminating Sessions • . Identify which session to terminate with the dynamic performance view V$SESSION: SELECT sid, serial# FROM v$session WHERE username=‘SCOTT’; • . Execute the ALTER SYSTEM command: ALTER SYSTEM KILL SESSION ‘7,15’;

  37. Trace Files • Trace files can be written by server and background processes. • Oracle dumps information about errors in trace files. • The ALERT file consists of a chronological log of messages and errors. • Server process tracing can be enabled or disabled by: • An ALTER SESSION command • The parameter SQL_TRACE

  38. Controlling the Trace File Instance SGA Shared pool Serverprocess Userprocess ALERT file USER_DUMP_DEST BACKGROUND_DUMP_DEST

  39. Guidelines • Check the ALERT file periodically to: • Detect internal errors (ORA-600)and block corruption errors • Monitor database operations • View the nondefault initialization parameter

  40. Summary • Creating the parameter file • Starting up and shutting down an instance • Understanding the use of dynamic performance views • Manage sessions • Describing the use of trace files

More Related