Oracle Concepts -  Database and Instance Startup and Shutdown Oracle Tips by Burleson Consulting

Database Startup and Shutdown

When the instance and database are created, on UNIX the oratab file should have the instance name added to it. This allows the operating system command files in the $ORACLE_HOME/

bin/dbstart and $ORACLE_HOME/bin/dbshut files to see the instance, these files startup and shutdown the databases listed in the oratab .

On NT4.0, startup and shutdown are generally handled by the strt<SID>.cmd and orashut.bat files located in (assuming your home drive is C:) C:/orant/database. The oradim7x (where x is the subversion) program is used to start, stop, and maintain the databases on NT4.0. By specifying command sets in the .CMD files different actions can be taken in regard to the Oracle database system. For example, the startdb.cmd file for an instance with a sid of TEST, an INTERNAL password of ORACLE, might look like this:

c:\orant\bin\oradim73 -startup -sid TEST -usrpwd ORACLE
                      -pfile
c:\oracle1\ortest1\admin\pfile\initORTEST1.ora
                      -starttype SRVC, INST

A shutdown script for the same instance would look like this:

c:\orant\bin\oradim73      -shutdown -sid TEST
                           -SURPWD ORACLE
                           -SHTTYPE  SRVC, INST
                           -SHUTMODE a

Startup 

The database is open and ready for use after being created. Once the operating system is shut down, or the database is shut down, it must be started before it can be accessed.

UNIX Startup.  On UNIX systems, the DBA has to perform the following steps to ensure the instance and database startup each time the system starts up.

1. Log in as root.

2. Edit the /etc/oratab file. Change the last field for your $ORACLE_SID to Y.

3. Add a line similar to the following to your /etc/rc file; be sure you use the full path to the dbstart procedure.

    su - oracle_owner -c /users/oracle/bin/dbstart

Manual Startup.  On all systems manual startup is accomplished either via the supplied scripts or through the SVRMGR or in later Oracle8i release, SQLPLUS program. To startup a database using SVRMGR or SQLPLUS, use the following procedure. The command used is the STARTUP command; its format follows.

STARTUP  [RESTRICTED] [FORCE] [PFILE=filename]
          [EXCLUSIVE or PARALLEL]
          [MOUNT or OPEN] dbname
          [NOMOUNT]
          [RECOVER]

1. Log in to SVRMGR as INTERNAL or into SQLPLUS as SYS.

2. Issue one of the following commands:

* STARTUP OPEN dbname PFILE=filename  This command starts the instance, and opens the database named dbname using the parameter file specified by the filename following the PFILE= clause. This starts up the database in the default, EXCLUSIVE mode.

* STARTUP RESTRICT OPEN dbname PFILE=filename  This command starts the instance, and opens the database named dbname using the parameter file specified by the filename following the PFILE= clause. This starts up the database in the restricted only mode (only users with RESTRICTED SESSION privilege can log in).

* STARTUP NOMOUNT  This command starts the instance, but leaves the database dismounted and closed. Cannot be used with EXCLUSIVE, MOUNT, or OPEN. Normally this command is used only when creating a database. There are some maintenance activities that require the database to be in NOMOUNT but generally it is only used with database creation.

* STARTUP MOUNT  This command starts the instance and mounts the database, but leaves it closed.

* STARTUP OPEN dbname PARALLEL

This command starts the instance, opens the database and puts the database in PARALLEL mode for multi-instance use in pre-Oracle8 versions. In Oracle8, simply setting the initialization parameter PARALLEL_SERVER to TRUE starts the instance in parallel server (shared) mode. PARALLEL is obsolete in Oracle8. It cannot be used with EXCLUSIVE or NOMOUNT or if the INIT.ORA parameter SINGLE_PROCESS is set to TRUE. The SHARED parameter is obsolete in Oracle8.

3. STARTUP OPEN dbname EXCLUSIVE

This command is functionally identical to (a) above. Cannot be specified if PARALLEL or NOMOUNT is also specified in pre-Oracle8 versions. EXCLUSIVE is obsolete in Oracle8. If PARALLEL_SERVE is FALSE, the database defaults to EXCLUSIVE.

4. The FORCE parameter can be used with any of the above options to force a shutdown and restart of the database into that mode. This is not normally done and is only used for debugging and testing.

5. The RECOVER option can be used to immediately start recovery of the database on startup if desired.

Errors that can occur during a startup include missing files, improperly specified PFILE path or name, or corrupted file errors. If these occur, the database will immediately shut down. Using OEM (Oracle Enterprise Manager) you must log in as an account that has been assigned the SYSOPER or SYSDBA roles in order to start up or shut down an instance.

Shutdown 

The databases should be shut down before system shutdowns, before full backups and any time system operations require it to be shut down.

UNIX Shutdown.  For UNIX there are several things that need to be done to ensure shutdown occurs. The following procedure, for the HP-UX version of UNIX, demonstrates these steps.

1. Log in as root.

2. Edit the /etc/oratab file. Make the last field a Y for the $ORACLE_SID you want shut down.

3. Add the following entry to your /etc/shutdown file. Be sure to use full path to the dbshut utility.

       su - oracle_owner -c /usr/oracle/bin/dbshut

You should alter the shutdown scripts to do a SHUTDOWN IMMEDIATE. This backs out any uncommitted user transactions, logs them out and then shuts down the database. If a normal SHUTDOWN is performed, the system politely waits for all users to log off of Oracle . . . if Joe is on vacation and left his terminal up in a form, you could have a long wait. The other shutdown, SHUTDOWN ABORT, should only be used for emergencies as it stops the database just as it is, with operations pending or not. A SHUTDOWN ABORT will require a recovery on startup.

The above provides for automatic shutdown when the operating system shuts down. For a normal shutdown, execute the dbshut procedure for UNIX, the SHUTDOWN_

sid.COM procedure for VMS, where the sid is the SID for the database you want to shut down. If it has been created, the stop<sid>.cmd script is used to shutdown an Oracle instance on NT.

To perform a manual shutdown on either system, perform the following procedure.

* Log in to SVRMGR as INTERNAL.

* Issue the appropriate SHUTDOWN command.

* No option means SHUTDOWN NORMAL.  The database waits for all users to disconnect, prohibits new connects, then closes and dismounts the database, then shuts down the instance.

* SHUTDOWN IMMEDIATE.  Cancels current calls like a system interrupt, and closes and dismounts the database, then shuts down the instance. PMON gracefully shuts down the user processes. No instance recovery is required on startup.

* SHUTDOWN ABORT.  This doesn?t wait for anything. It shuts the database down now. Instance recovery will probably be required on startup. You should escalate to this by trying the other shutdowns first.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.