Category: Entertainment

Presentation Description

No description available.


Presentation Transcript

Managing an Oracle Instance:

Managing an Oracle Instance


Objectives After completing this lesson, you should be able to do the following: Create and manage initialization parameter files Start up and shut down an instance Monitor and use diagnostic files

Initialization Parameter Files:

spfiledb01.ora Initialization Parameter Files CONNECT / AS SYSDBA STARTUP Oracle Instance SGA Redo Log Buffer Shared Pool Data Dictionary Cache Library Cache DBW0 SMON PMON CKPT LGWR Others Database Buffer Cache Java Pool Large Pool

Initialization Parameter Files:

Initialization Parameter Files Entries are specific to the instance being started Two types of parameters: Explicit: Having an entry in the file Implicit: No entry within the file, but assuming the Oracle default values Multiple initialization parameter files can exist Changes to entries in the file take effect based on the type of initialization parameter file used Static parameter file, PFILE Persistent parameter file, SPFILE

PFILE initSID.ora:

PFILE initSID.ora Text file Modified with an operating system editor Modifications made manually Changes take effect on the next startup Only opened during instance startup Default location is $ORACLE_HOME/dbs

Creating a PFILE:

Creating a PFILE Created from a sample init.ora file Sample installed by the Oracle Universal Installer Copy sample using operating system copy command Uniquely identify by database SID Modify the initSID.ora Edit the parameters Specific to database needs cp init.ora $ORACLE_HOME/dbs/initdba01.ora

PFILE Example:

PFILE Example # Initialization Parameter File: initdba01.ora db_name = dba01 instance_name = dba01 control_files = ( home/dba01/ORADATA/u01/control01dba01.ctl, home/dba01/ORADATA/u02/control01dba02.ctl) db_block_size = 4096 db_cache_size = 4M shared_pool_size = 50000000 java_pool_size = 50000000 max_dump_file_size = 10240 background_dump_dest = /home/dba01/ADMIN/BDUMP user_dump_dest = /home/dba01/ADMIN/UDUMP core_dump_dest = /home/dba01/ADMIN/CDUMP undo_management = AUTO undo_tablespace = UNDOTBS . . .

SPFILE spfileSID.ora:

SPFILE spfileSID.ora Binary file Maintained by the Oracle server Always resides on the server side Ability to make changes persistent across shutdown and startup Can self-tune parameter values Can have Recovery Manager support backing up to the initialization parameter file

Creating an SPFILE:

Creating an SPFILE Created from a PFILE file where SPFILE-NAME : SPFILE to be created PFILE-NAME : PFILE creating the SPFILE Can be executed before or after instance startup CREATE SPFILE = ‘ $ORACLE_HOME/dbs/spfileDBA01.ora ’ FROM PFILE = ‘ $ORACLE_HOME/dbs/initDBA01.ora ’ ;

SPFILE Example:

SPFILE Example *.background_dump_dest=‘/home/dba01/ADMIN/BDUMP’ *.compatible='9.0.0' *.control_files='/home/dba01/ORADATA/u01/ctrl01.ctl’ *.core_dump_dest=‘/home/dba01/ADMIN/CDUMP’ *.db_block_size=4096 *.db_name='dba01‘ *.db_domain=‘world’ *.global_names=TRUE *.instance_name='dba01' *.remote_login_passwordfile='exclusive‘ *.java_pool_size=50000000’ *.shared_pool_size=50000000 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS' . . .

Oracle Managed Files:

Oracle Managed Files Oracle Managed Files (OMF) simplify file administration • OMF are created and deleted by the Oracle server as directed by SQL commands • OMF are established by setting two parameters: – DB_CREATE_FILE_DEST : Set to give the default location for data files – DB_CREATE_ONLINE_LOG_DEST_N : Set to give the default locations for online redo logs and control files, up to a maximum of 5 locations

Oracle Managed File Example :

Oracle Managed File Example To create a database where data files, control files, and online redo log files are created in separate directories: • Set the initialization parameters: Issue the CREATE DATABASE SQL statement.

STARTUP Command Behavior:

STARTUP Command Behavior Order of Precedence spfileSID.ora Default SPFILE initSID.ora Default PFILE Specified PFILE can override precedence PFILE can indicate to use SPFILE STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora SPFILE = /database/startup/spfileDBA1.ora

Modifying Parameters in SPFILE:

Modifying Parameters in SPFILE Parameter value changes made by ALTER SYSTEM Specify whether the change is temporary or persistent Delete or reset values ALTER SYSTEM SET undo_tablespace = 'UNDO2' ; ALTER SYSTEM SET undo_tablespace = 'UNDO2' SCOPE=BOTH; ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='*';

Starting Up a Database NOMOUNT:


Starting Up a Database MOUNT:

Starting Up a Database MOUNT OPEN MOUNT NOMOUNT SHUTDOWN Control file opened for this instance Instance started STARTUP SHUTDOWN

Starting Up a Database OPEN:

Starting Up a Database OPEN OPEN MOUNT NOMOUNT SHUTDOWN All files opened as described by the control file for this instance Control file opened for this instance Instance started STARTUP SHUTDOWN

STARTUP Command:

STARTUP Command Start up the instance and open the database: STARTUP STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora


ALTER DATABASE Command Change the state of the database from NOMOUNT to MOUNT : Open the database as a read-only database: ALTER DATABASE db01 MOUNT; ALTER DATABASE db01 OPEN READ ONLY;

Opening a Database in Restricted Mode:

Opening a Database in Restricted Mode Use the STARTUP command to restrict access to a database: Use the ALTER SYSTEM command to place an instance in restricted mode: STARTUP RESTRICT ALTER SYSTEM ENABLE RESTRICTED SESSION;

Opening a Database in Read-Only Mode:

Opening a Database in Read-Only Mode Opening a database in read-only mode Can be used to: Execute queries Execute disk sorts using locally managed tablespaces Take datafiles offline and online, but not tablespaces Perform recovery of offline datafiles and tablespaces STARTUP MOUNT ALTER DATABASE OPEN READ ONLY;

Shutting Down the Database:

Shutting Down the Database Shutdown mode: A = ABORT I = IMMEDIATE T = TRANSACTIONAL N = NORMAL A No No No No T No No Yes Yes I No No No Yes Shutdown Mode Allow new connections Wait until current sessions end Wait until current transactions end Force a checkpoint and close files N No Yes Yes Yes

Shutdown Options:

Shutdown Options On the way down: Database buffer cache written to the datafiles Uncommitted changes rolled back Resources released On the way up: No instance recovery During a Shutdown Normal, Shutdown Transactional or Shutdown Immediate Consistent database (clean database)

Shutdown Options:

Shutdown Options On the way down: Modified buffers are not written to the datafiles Uncommitted changes are not rolled back On the way up: Redo logs used to reapply changes Undo segments used to roll back uncommitted changes Resources released During a Shutdown Abort or Instance Failure or Startup Force Inconsistent database (dirty database)

Monitoring an Instance Using Diagnostic Files:

Diagnostic files Contain information about significant events encountered Used to resolve problems Used to better manage the database on a day-to-day basis Several types exist: alertSID.log file Background trace files User trace files Monitoring an Instance Using Diagnostic Files

Alert Log File:

Alert Log File alertSID.log file: Records the commands Records results of major events Used for day-to-day operational information Used for diagnosing database errors Each entry has a time stamp associated with it Must be managed by DBA Location defined by BACKGROUND_DUMP_DEST

Background Trace Files:

Background Trace Files Background trace files Logs errors detected by any background process Used to diagnose and troubleshoot errors Created when a background process encounters an error Location defined by BACKGROUND_DUMP_DEST

User Trace File:

User Trace File User trace file Produced by the user process Can be generated by a server process Contains statistics for traced SQL statements Contains user error messages Created when a user encounters user session errors Location is defined by USER_DUMP_DEST Size defined by MAX_DUMP_FILE_SIZE

Enabling or Disabling User Tracing:

Enabling or Disabling User Tracing Session level: Using the ALTER SESSION command: ALTER SESSION SET SQL_TRACE = TRUE Executing DBMS procedure: dbms_system.SET_SQL_TRACE_IN_SESSION Instance level Setting the initialization parameter: SQL_TRACE = TRUE


Summary In this lesson, you should have learned how to: Create and manage initialization parameter files Start up and shut down an instance Monitor and use diagnostic files

Practice 3 Overview:

Practice 3 Overview This practice covers the following topics: Creating an SPFILE Starting up and shutting down the database in different modes

authorStream Live Help