Managing an Oracle Instance

Managing an Oracle Instance

Initialization Parameter Files

To start an instance, the Oracle server must read the initialization parameter file. Two types of

Initialization parameter files exist:

• Static parameter file, PFILE, commonly referred to as initSID.ora

• Persistent parameter file, SPFILE, commonly referred to as spfileSID.ora

Parameter File Contents

• A list of instance parameters

• The name of the database the instance is associated with

• Allocations for memory structures of the System Global Area (SGA)

• What to do with filled online redo log files

• The names and locations of control files

• Information on undo segments

PFILE

The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE. By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. A sample init.ora file is created by the Universal Installer during installation. This sample init.ora file can be used to create an instance-specific initSID.ora.

# Initialization Parameter File: initdb01.ora

db_name = db01

instance_name = db01

control_files = ( /u03/oradata/db01/control01db01.ctl,

/u03/oradata/db01/control02db01.ctl)

db_block_size = 4096

db_block_buffers = 500

shared_pool_size = 31457280 # 30M Shared Pool

db_files = 1024

max_dump_file_size = 10240

background_dump_dest = /u05/oracle9i/admin/db01/bdump

user_dump_dest = /u05/oracle9i/admin/db01/udump

core_dump_dest = /u05/oracle9i/admin/db01/cdump

undo_management = auto

undo_tablespace = undtbs

SPFILE

SPFILE, new to Oracle9i, is a binary file. The file is not meant to be modified manually and must always reside on the server side. By default, the file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup.

The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change.

• MEMORY: Changes the parameter value only in the currently running instance

• SPFILE: Changes the parameter value in the SPFILE only

• BOTH: Changes the parameter value in the currently running instance and the SPFILE

ALTER SYSTEM SET parameter = value [SCOPE = MEMORY|SPFILE|BOTH]

The SPFILE can be modified with the ALTER SYSTEM command when the instance starts

Using the SPFILE or using the PFILE with the parameter SPFILE.

Creating an SPFILE

An SPFILE is created from an initSID.ora file using the CREATE SPFILE command.

This can be executed before or after the database is open.

CREATE SPFILE FROM PFILE;

*.background_dump_dest='$ORACLE_HOME/admin/db01/bdump'

*.compatible='9.0.0'

*.control_files='/u03/oradata/db01/ctrl01db01.ctl','/u03/orad

ata/db01/ctrl02db01.ctl'

*.core_dump_dest='$ORACLE_HOME/admin/db01/cdump'

*.db_block_buffers=500

*.db_block_size=4096

*.db_files=40

*.db_name='db01'

*.instance_name='db01'

*.remote_login_passwordfile='exclusive'

*.shared_pool_size=31457280 # 30M Shared Pool

*.undo_management='AUTO'

db01.undo_tablespace='UNDOTBS01'

db02.undo_tablespace='UNDOTBS02'

Starting Up a Database

When starting the database, you select the state in which it starts. The following scenarios

describe different stages of starting up an instance.

Starting up a database (NOMOUNT)

Usually you would start an instance without mounting a database only during database creation or the re-creation of control files.

Starting an instance includes the following tasks:

• Reading the initialization file from $ORACLE_HOME/dbs in the following order:

– First spfileSID.ora. If not found then

– spfile.ora

– initSID.ora

• Specifying the PFILE parameter with STARTUP overrides the default behavior

• Allocating the SGA

• Starting the background processes

• Opening the alertSID.log file and the trace files

The database must be named with the DB_NAME parameter either in the initialization file or

in the STARTUP command.

Starting up a database (MOUNT)

To perform specific maintenance operations, you start an instance and mount a database but do not open the database.

For example, the database must be mounted but not open during the following tasks:

• Renaming data files

• Enabling and disabling redo log archiving options

• Performing full database recovery

Mounting a database includes the following tasks:

• Associating a database with a previously started instance

• Locating and opening the control files specified in the parameter file

• Reading the control files to obtain the names and status of the datafiles and redo log

files. (However, no checks are performed to verify the existence of the data files and online redo log files at this time.)

Starting up a database (OPEN)

Normal database operation means that an instance is started and the database is mounted and open; with normal database operation, any valid user can connect to the database and perform typical data access operations. Opening the database includes the following tasks:

• Opening the online data files

• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the Database, the Oracle server returns an error. During this final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor background process (SMON) initiates instance recovery.

FORCE --> aborts the running instance before performing a normal startup

RESTRICT --> enables only users with RESTRICTED SESSION privilege to access the database

RECOVER --> begins media recovery when the database starts

The 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;

READ WRITE --> opens the database in read-write mode, so that users can generate redo logs

READ ONLY --> restricts users to read-only transactions, preventing them from generating redo log information

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;

Shutdown Options

Shutting Down the Database

Shutdown Mode	A	I	T	N
Allow new connections	x	x	x	x
Wait until current sessions end	x	x	x	o
Wait until current transactions end	x	x	o	o
Force a checkpoint and close files	x	o	o	o

Shutdown Mode:                                       

x

o

 NO              • NORMAL --> N                                             

YES              • TRANSACTIONAL --> T                      

                    • IMMEDIATE --> I

                                 • ABORT --> A

Managing an Instance by Monitoring Diagnostic Files

Diagnostic files contain information about significant events encountered while the instance is operational.

• Used to resolve problems or to better manage the database on a day-to-day basis.

• Several types of diagnostic files exist:

– alertSID.log file -->Information for day-to-day operation of the database, Each entry has a time stamp associated with it. Its location is defined by BACKGROUND_DUMP_DEST

– Background trace files -->Vital information when background processes, such as SMON,

PMON, DBWn, and others fail. Their location is defined by BACKGROUND_DUMP_DEST

– User trace files --> Vital information for fatal user errors or user forced traced files. Its  location is defined by USER_DUMP_DEST