Startup Command Behavior, Startup Event Sequence, What happens at every stage during startup, Oracle database Internal Processing of Sequence.

Hey Folks,

We will review here some basic stuff that is Database Startup command behavior and What happens internally in Oracle when we fire the command startup. Let's Start :

Startup Command Behavior : 

• Order of Providences :
• SpfileSID.ora
• Default Spfile
• InitSID.ora
• Default Pfile

• Specified PFILE  can Override the precedence

           STARTUP PFILE = $ORACLE_HOME/dbs/initORCL.ora

• PFILE  Can Indicate to use SPFILE

          SPFILE = $ORACLE_HOME/dbs/initORCL.ora

 

Order Precedence Details :

• When the STARTUP command is used, Then the  spfileSID.ora file on the server side is used to startup the instance.
• If the spfileSID.ora file is not found ,then the default SPFILE on the server side is used to start the instance
• If the default SPFILE not found , then the initSID.ora file on the server side will be used to start the instance.

A Specified PFILE can override the use of the default SPFILE to start instance.

A PFILE can optionally contain a definition to indicate use of an SPFILE. This is the only way to start the instance with an SPFILE in a non default Location.

To start the Database with an SPFILE not in default location  :

 SPFILE =  < Full Path and filename > must be place on PFILE.

Example : In PFILE we can mention as below (For start the instance using SPFILE available at non Default Location.       

  SPFILE  = /U01/oradata/PFILE/spfileSID.ora

Parameters that Should be Specified in the Initialization Parameter File :

  

Parameter	Description
BACKGROUND_DUMP_DEST	Location Where Background Process Trace files are written (LGWR,DBWn, and so on ). Also the location for the alert log file.
COMPATIBLE	Version of the server with which this instance should be compatible
CONTROL_FILES	Name of the control files
DB_CACHE_SIZE	Specifies the size of the cache for standard block size buffers
DB_NAME	Database identifier of Eight 8 Characters or fewer. This is the only Parameter that is required when a new database is created
SHARED_POOL_SIZE	Size in the bytes of the Shared Pool
USER_DUMP_DEST	Location Where user debugging trace file are created on behalf of user process

Note : The Default Values depend on the version of the Oracle Server.

Commonly Modified Parameters :

 

Parameter	Description
IFILE	Name of another parameter file to be embedded within the current parameter file. Up to three levels of nesting is possible.
LOG_BUFFER	Number of bytes allocated to the Redo Log Buffer in the SGA
MAX_DUMP_FILE_SIZE	Maximum Size of trace files, Specified as number of Operating System Blocks
PROCESSES	Maximum Number of operation system processed that can connect Simultaneously
SQL_TRACE	Enables or Disables the SQL trace facility for every USER session.
TIMED_STATISTICS	Enables Or Disables timings in trace files and in monitor screens

Database Startup Stages  and What Action event Occur at every Stage : 

Starting Up a Database : When Starting the Database, You select the state in which it starts. The fallowing scenarios describe different stages of starting up an instance

1. NOMOUNT :

An Instance would be started in the NOMOUNT stage only during Database creation or the re-creation of control files.

Starting an instance includes the fallowing tasks :

• Reading the initialization parameter file from " $ORACLE_HOME/dbs " in the fallowing order.
• First spfileSID.ora
• If not found then, spfile.ora
• If not found then, initSID.ora

      Specifying the PFILE parameter with STARTUP overrides the default behavior.

• Allocating the SGA
• Starting the background processes
• Opening The  alertSID.log and the trace files

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

Command Used : SQL > startup nomount ; 

2. MOUNT :

Mounting the Database to perform Specific maintenance operations, You start an instance and mount the database but do not open the database.

For Example : The Database must be mounted but not open during the Fallowing Tasks :

• Renaming Data files
• Enabling and Disabling online redo log file archiving options
• Performing full Database recovery

Mounting a database includes the fallowing tasks :

• Associating a database with previously started instance 
• Locating and opening the control files specified in the parameter file
• Reading the control file to obtain the names and status of the data files and online redo log files. However No checks performed to verify the existence of the data files and online redo log files at this time.

Command Used : SQL > startup mount  or

                                     SQL > startup nomount;  and Then SQL > alter database mount;

3. 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 fallowing 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 The 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 SMON background  Process initiates instance recovery.

 Command : SQL>  startup ;

STARTUP command :

To startup an instance and open the database by using below commands :

SQL> startup;

SQL> startup pfile = $ORACLE_HOME/dbs/initORCL.ora

To Start up an instance, use the fallowing option with STARTUP command when it requires:

STARTUP  [FORCE] [ RESTRICT]  [PFILE=file Path and Name]

[OPEN  [Recover] [ Database]

|MOUNT

|NOMOUNT

Note : This Is the not a complete syntax

Where : 

OPEN : Enables users to access the database. 

MOUNT : Mounts the Database for certain DBA activities but does not provide user access to the database.

NOMOUNT : Creates the SGA and starts up the background processes but does not provide access to the database

PFILE-Parfile : Enables a non-default initialization parameter file to be used to configure the instance.

FORCE : Shuts Down the current Oracle Database instance (if it is running) with SHUTDOWN mode ABORT, before restarting it.

RESTRICT : Enables only users with RESTRICTED SESSION privilege to access the database.

RECOVER : Begins media recovery when the database starts.

 

Automating Database Startup :

ON Unix : Automating Database start up and shut down can be controlled by the entries in a special operating system file. For example, oratab in the /var/opt/oracle directory.

Trouble Shooting :

If any errors are occurs while issuing the STARTUP command then First Issue the SHUTDOWN command and then again try to issue STARTUP command.

Note : STARTUP and SHUTDOWN commands are SQL*plus commands, and not SQL commands. 

Thank You !!