560 likes | 924 Views
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
E N D
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
Userprocess Serverprocess PGA ALERTfile Control files Parameterfile Redo logfiles Data files Passwordfile Overview Instance SGA Shared pool
Database Administrator Users The two database administrator usersSYS and SYSTEM are: • Automatically created • Granted the DBA role
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
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
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
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
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.
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.
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
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) • ...
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
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
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]
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
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
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
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
Shutdown Time • Normal 4 3 2 1 Immediate Abort Transactional Time
Oracle Memory and Background Processes: Connecting to a Shared Server Red lines trace the path between the client and the instance
Oracle Memory and Background Processes: Connecting to a Dedicated Server Red lines trace the path between the client and the instance
Oracle Memory : PGA & SGA The SGA is allocated when the instance starts up The PGA has private areas for each application
Oracle Memory : SGA Components The shared pool stores parsed SQL commands for possible reuse The buffer cache stores data blocks read from the datafiles
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
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
Background Processes: DBWn & CKPT DBWn (Database Writer) writes modified buffers to the datafiles CKPT (Checkpoint Process) signals the DBWn process and assigns SCN
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
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
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$
Accessing Dynamic Performance Views OPEN Data dictionary MOUNT Dynamic performance views reading data from disk NOMOUNT Dynamic performance views reading from memory SHUTDOWN
Example V$PARAMETERV$SGAV$OPTIONV$PROCESSV$SESSION V$VERSIONV$INSTANCE SGA V$THREADV$CONTROLFILE V$DATABASEV$DATAFILEV$DATAFILE_HEADER V$LOGFILE Control file
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%’;
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;
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;
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’;
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
Controlling the Trace File Instance SGA Shared pool Serverprocess Userprocess ALERT file USER_DUMP_DEST BACKGROUND_DUMP_DEST
Guidelines • Check the ALERT file periodically to: • Detect internal errors (ORA-600)and block corruption errors • Monitor database operations • View the nondefault initialization parameter
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