3
Managing an Oracle Instance
Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Initialization Parameter Files
Oracle Instance
SGA
Shared Pool
Library Database Redo Log
Cache Buffer Cache Buffer
Data Dictionary
Cache Java Pool Large Pool
PMON SMON DBW0 LGWR CKPT Others
spfiledb01.ora
CONNECT / AS SYSDBA
STARTUP
3-3 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-4 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-6 Copyright © Oracle Corporation, 2001. All rights reserved.
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
cp init.ora $ORACLE_HOME/dbs/initdba01.ora
• Modify the initSID.ora
– Edit the parameters
– Specific to database needs
3-7 Copyright © Oracle Corporation, 2001. All rights reserved.
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
. . .
3-8 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-9 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating an SPFILE
• Created from a PFILE file
CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfileDBA01.ora’
FROM PFILE = ‘$ORACLE_HOME/dbs/initDBA01.ora’;
where
– SPFILE-NAME: SPFILE to be created
– PFILE-NAME: PFILE creating the SPFILE
• Can be executed before or after instance startup
3-10 Copyright © Oracle Corporation, 2001. All rights reserved.
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'
. . .
3-13 Copyright © Oracle Corporation, 2001. All rights reserved.
STARTUP Command Behavior
• Order of Precedence
– spfileSID.ora
– Default SPFILE
– initSID.ora
– Default PFILE
• Specified PFILE can override precedence
STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora
• PFILE can indicate to use SPFILE
SPFILE = /database/startup/spfileDBA1.ora
3-14 Copyright © Oracle Corporation, 2001. All rights reserved.
Modifying Parameters in SPFILE
• Parameter value changes made by ALTER SYSTEM
ALTER SYSTEM SET undo_tablespace = 'UNDO2';
• Specify whether the change is temporary or
persistent
ALTER SYSTEM SET undo_tablespace = 'UNDO2'
SCOPE=BOTH;
• Delete or reset values
ALTER SYSTEM RESET undo_suppress_errors
SCOPE=BOTH SID='*';
3-15 Copyright © Oracle Corporation, 2001. All rights reserved.
Starting Up a Database
NOMOUNT
OPEN
STARTUP
MOUNT
NOMOUNT
Instance
started
SHUTDOWN
SHUTDOWN
3-19 Copyright © Oracle Corporation, 2001. All rights reserved.
Starting Up a Database
MOUNT
OPEN
STARTUP
MOUNT
Control file
opened for this
NOMOUNT instance
Instance
started
SHUTDOWN
SHUTDOWN
3-20 Copyright © Oracle Corporation, 2001. All rights reserved.
Starting Up a Database
OPEN
OPEN
STARTUP All files opened as
described by the control
MOUNT file for this instance
Control file
opened for this
NOMOUNT instance
Instance
started
SHUTDOWN
SHUTDOWN
3-21 Copyright © Oracle Corporation, 2001. All rights reserved.
STARTUP Command
Start up the instance and open the database:
STARTUP
STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora
3-22 Copyright © Oracle Corporation, 2001. All rights reserved.
ALTER DATABASE Command
• Change the state of the database from NOMOUNT to
MOUNT:
ALTER DATABASE db01 MOUNT;
• Open the database as a read-only database:
ALTER DATABASE db01 OPEN READ ONLY;
3-25 Copyright © Oracle Corporation, 2001. All rights reserved.
Opening a Database in Restricted Mode
• 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;
3-26 Copyright © Oracle Corporation, 2001. All rights reserved.
Opening a Database in Read-Only Mode
• Opening a database in read-only mode
STARTUP MOUNT
ALTER DATABASE OPEN READ ONLY;
• 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
3-29 Copyright © Oracle Corporation, 2001. All rights reserved.
Shutting Down the Database
Shutdown Mode A I T N
Allow new connections No No No No
Wait until current sessions end No No No Yes
Wait until current transactions end No No Yes Yes
Force a checkpoint and close files No Yes Yes Yes
Shutdown mode:
• A = ABORT
• I = IMMEDIATE
• T = TRANSACTIONAL
• N = NORMAL
3-31 Copyright © Oracle Corporation, 2001. All rights reserved.
Shutdown Options
During a
Shutdown Normal,
Shutdown
On the way down: Transactional
On the way up:
• Database buffer or • No instance
cache written to Shutdown Immediate recovery
the datafiles
• Uncommitted
changes rolled
back
• Resources released
Consistent database
(clean database)
3-32 Copyright © Oracle Corporation, 2001. All rights reserved.
Shutdown Options
During a
Shutdown Abort
On the way down: or On the way up:
Instance Failure • Redo logs used
• Modified buffers
or
are not written to Startup Force to reapply
the datafiles changes
• Uncommitted • Undo segments
changes are not used to roll back
rolled back uncommitted
changes
• Resources
released
Inconsistent database
(dirty database)
3-34 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-36 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-37 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-39 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-40 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-41 Copyright © Oracle Corporation, 2001. All rights reserved.
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
3-43 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 3 Overview
This practice covers the following topics:
• Creating an SPFILE
• Starting up and shutting down the database in
different modes
3-44 Copyright © Oracle Corporation, 2001. All rights reserved.
3-46 Copyright © Oracle Corporation, 2001. All rights reserved.
