 |
|
Database Startup and
Shutdown
Oracle Tips by Burleson Consulting
|
When the instance and database are created
using DBCA in Linux or UNIX, the oratab in the /etc or /var/opt/oracle
directory is updated to have a listing similar to:
galinux1:/var/oracle/OraHome2:N
If you manually create the database,
you should edit the oratab file to include a similar entry for your
database SID.
The listing in the oratab file consists
of three parts, divided by colons. The first part is the SID for the
instance; in the above example, this is galinux1. The second portion
is the value for the ORACLE_HOME symbol for that instance; the final
value is either Y or N. The third value determines if the database is
started and stopped during startup and shutdown of the host. The
oratab is read by the startup files in the init.d directories, and the
startup files execute the $ORACLE_HOME/bin/dbstart and $ORACLE_HOME/bin/dbshut
files on UNIX.
On NT 4.0, startup and shutdown are
generally handled by the oradim program. Whether databases are started
or stopped automatically is decided by several registry entries in the
HKEY_LOCAL_MACHINE-SOFTWARE-ORACLE-HOMEn registry tree. The registry
entries on NT4.0 SP6 are:
ORA_sid_AUTOSTART. Either "TRUE" or
"FALSE."
ORA_sid_SHUTDOWNTYPE. One of three options:
"i" for immediate, "n" for normal, or "a" for abort; the default is "i."
ORA_sid_SHUTDOWN_TIMEOUT. Set to seconds to
wait for Oracle to shut down; defaults to 30, at which time the system
shuts down anyway.
The oradim program is used to start,
stop, and maintain the databases on NT 4.0 and W2K. By specifying
command sets in .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\oradim -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\oradim -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 or LINUX Startup
On UNIX or Linux systems, the Remote 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/init.d/Dbora file or
its equivalent on your version of UNIX (it may be in an rc.d directory
instead of an init.d directory); be sure you use the full path to the
dbstart procedure.
su -
oracle_owner -c /users/oracle/bin/dbstart
An example of a full Dbora script is shown in
Listing 2.3.
#!/bin/sh
#
# /etc/rc.d/init.d/Dbora
# Description: Starts and stops the Oracle
database and listeners
# See how we were called.
case "$1" in
start)
echo -n "Starting Oracle Databases: "
echo
"----------------------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Starting Oracle
Databases as part of system up." >>
/var/log/oracle
echo
"----------------------------------------------------" >>
/var/log/oracle
su - oracle -c
/users/oracle/9.0.1/bin/dbstart >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Listeners: "
su - oracle -c
"/users/oracle/9.0.1/bin/lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo
"----------------------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo
"----------------------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle
Listeners: "
echo
"----------------------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Shutting Down
Oracle Databases as part of system down." >>
/var/log/oracle
echo
"----------------------------------------------------" >>
/var/log/oracle
su - oracle -c
"/users/oracle/9.0.1/bin/lsnrctl stop" >> /var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
echo -n "Shutting Down Oracle
Databases: "
su - oracle -c
/users/oracle/9.0.1/bin/dbshut >> /var/log/oracle
echo "Done."
echo ""
echo
"----------------------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo
"----------------------------------------------------" >> /var/log/oracle
;;
restart)
echo -n "Restarting Oracle Databases:
"
echo
"----------------------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Restarting Oracle
Databases as part of system up." >>
/var/log/oracle
echo
"----------------------------------------------------" >>
/var/log/oracle
su - oracle -c
/user/oracle/9.0.1/bin/dbshut >> /var/log/oracle
su - oracle -c
/user/oracle/9.0.1/bin/dbstart >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Listeners:
"
su - oracle -c
"/user/oracle/9.0.1/bin/lsnrctl stop" >> /var/log/oracle
su - oracle -c
"/user/oracle/9.0.1/bin/lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo
"----------------------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished."
>> /var/log/oracle
echo
"----------------------------------------------------" >>
/var/log/oracle
touch /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop|restart}"
exit 1
esac
Listing 2.3 Example Dbora script.
To call the above scripts, entries are
needed in the appropriate init.d or rc.d directories corresponding to
the run level at which Oracle needs to be stopped or started. Usually
start in levels 2, 3, and 4, and shut down in 0 and 6.
To accomplish this, you will place
links from the rc2.d, rc3.d, rc4.d, rc0.d, and rc6.d subdirectories to
the Dbora script. The links are usually of the form SnDbora or KnDbora,
where n is an integer corresponding to the order of other start or
stop calls are present: S means start the database, K means kill it.
Typically, you will want an S99Dbora entry in levels 2, 3, and 4
directories, and a K01Dbora entry in levels 0 and 6. The commands
lsnrctl, dbstart, and dbshut may have to be full-path’ed depending on
your environment. The startup links are formed with these commands:
ln -s /etc/init.d/Dbora /etc/init.d/rc2.d/S99Dbora
ln -s /etc/init.d/Dbora /etc/init.d/rc3.d/S99Dbora
ln -s /etc/init.d/Dbora /etc/init.d/rc4.d/S99Dbora
One the script Dbora is in place and
the links have been created on Linux, you must run the insserv command
to tell the various configuration scripts where the Dbora file is
located. The insserv command is available from the root user.
If you get an error on Linux such as:
/etc/init.d/Dbora: bad interpreter:
Permission denied,
this indicates that the program was written in
a Windows environment and was not properly converted to UNIX format. I
suggest getting a program called dos2unix; at the time of this writing
it was available at http://www.bastet.com/software/software.html.
Also, be sure you have the proper execute privileges set on the
scripts.
Manual Startup
On all systems, manual startup is
accomplished via the supplied scripts, through the SVRMGR, or, in the
later Oracle8i and Oracle9i releases, through the SQLPLUS program. To
start up a database using SVRMGR or SQLPLUS, use the following
procedure. The command used is STARTUP; its format follows.
STARTUP [RESTRICTED] [FORCE] [PFILE=filename]
[SPFILE=filename]
[EXCLUSIVE or PARALLEL] (pre 9i
only)
[MOUNT or OPEN] dbname
[NOMOUNT]
[RECOVER]
1.
Log in to SVRMGR as INTERNAL or in to SQLPLUS as SYS or as "/" using
the AS SYSRemote DBA qualifier.
2.
Issue one of the following commands:
a.
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.
b.
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).
c.
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.
d.
STARTUP MOUNT. This command starts the instance and mounts the
database, but leaves it closed.
e.
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. In Oracle9i, the parameter
CLUSTER_SERVER set to TRUE starts RAC. PARALLEL is obsolete in
Oracle8. PARALLEL_SERVER is obsolete in Oracle9i. 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.
f.
STARTUP OPEN dbname EXCLUSIVE. This command is functionally
identical to a, above. It cannot be specified if PARALLEL or NOMOUNT
is also specified in pre-Oracle8 versions. EXCLUSIVE is obsolete in
Oracle8. If PARALLEL_SERVER—or, in Oracle9i CLUSTER_SERVER--is FALSE,
the database defaults to EXCLUSIVE.
g.
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.
h.
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 any of 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 SYSRemote DBA
roles in order to start up or shut down an instance.
Shutdown
The databases should be shut down before
system shutdowns, before full cold backups, and any time system
operations require the system to be shut down.
UNIX Shutdown
For UNIX, several things 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 links to your /etc/init.d rcx.d files (where x is
the run level).
ln -s /etc/init.d/Dbora /etc/init.d/rc0.d/K01Dbora
ln -s /etc/init.d/Dbora /etc/init.d/rc6.d/K01Dbora
You should alter the shutdown script ($ORACLE_HOME/bin/dbshut)
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 new command option , SHUTDOWN
TRANSACTIONAL allows transactions to finish, then logs the user off
and performs shutdown.
The preceding provides for automatic
shutdown when the operating system shuts down. For a normal shutdown,
execute the dbshut procedure for UNIX. If it has been created, the
stop<sid>.cmd script is used to shut down an Oracle instance on NT.
To perform a manual shutdown on all
systems, perform the following procedure:
1.
Log in to SVRMGR as INTERNAL; if on 9i, use the SQLPLUS /NOLOG and log
in as either SYS or "/" using the AS SYSRemote DBA qualifier.
2.
Issue the appropriate SHUTDOWN command.
a.
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.
b.
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.
c.
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.
d.
SHUTDOWN TRANSACTIONAL. Like SHUTDOWN IMMEDIATE, only it waits for
transactions to complete, then boots off any users and shuts down the
database.
See
Code Depot for Full Scripts
 |
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
