2.4. Starting Up and Shutting Down an Oracle Database
As a DBA, you are responsible for startup and shutdown of the Oracle instance. Oracle gives authorized administrators the ability to perform this task using a variety of interfaces. It is important to understand the options that are available to you to start up and shut down the Oracle instance and when the various options can or should be used.
To start up or shut down an Oracle instance, you need to be connected to the database with the appropriate privileges. Two special connection accounts authorizations are available for startup and shutdown: SYSDBA or SYSOPER. The SYSDBA authorization is an all-empowering authorization that allows you to perform any database task. The SYSOPER authorization is a less powerful authorization that allows startup and shutdown abilities but restricts other administrative tasks, such as access to nonadministrative schema objects. These authorizations are managed either through a passwords file or via operating-system control.
When a database is initially installed, only the SYS schema can connect to the database with the SYSDBA authorization. You can grant this authorization and the SYSOPER authorization to give others the ability to perform these tasks without connecting as the SYS user.
Now we will discuss how to perform a database startup.
2.4.1. Oracle 10g Database Startup
As described in Chapter 1, the Oracle instance is composed of a set of logical memory structures and background processes that users interact with to communicate with the Oracle database. When Oracle is started, these memory structures and background processes are initialized and started so that users can communicate with the Oracle database.
Whenever an Oracle database is started, it goes through a series of steps to ensure database consistency. When it starts up, a database passes through three modes: NOMOUNT, MOUNT, and OPEN. We will review each of these startup modes and a few other special startup options and discuss when you need to use these options. We'll then discuss how to use the available interfaces to start up an Oracle instance.
STARTUP NOMOUNT
STARTUP NOMOUNT starts the instance without mounting the database. When a database is started in this mode, the parameter file is read and the background processes and memory structures are initiated, but they are not attached or communicating with the disk structures of the database. When the instance is in this state, the database is not available for use.
If a database is started in NOMOUNT mode, you can perform certain tasks. One of the most common is to run a script that creates the underlying database.
At times, a database may not be able to go to the next mode (called MOUNT mode) and remains in NOMOUNT mode. For example, this can occur if Oracle has a problem accessing the control file structures, which contain important information to continue with the startup process. If these structures are damaged or not available, the database startup process cannot continue until the problem is resolved.
STARTUP MOUNT
The STARTUP MOUNT option performs all the work of the STARTUP NOMOUNT option but also attaches and interacts with the database structures. At this point, Oracle obtains information from the control files that it uses to locate and attach to the main database structures.
Certain administrative tasks can be performed while the database is in this mode, for example, recovery. You can also physically change file locations or place the database in archive log mode.
STARTUP OPEN
The STARTUP OPEN option is the default startup mode if no mode is specified on the STARTUP command line. STARTUP OPEN performs all the steps of the STARTUP NOMOUNT and STARTUP MOUNT options. This option makes the database available to all users.
Although you typically use the STARTUP NOMOUNT, STARTUP MOUNT, and STARTUP OPEN options, a few other startup options are available that you can use in certain situations: STARTUP FORCE and STARTUP RESTRICT. These are discussed next.
STARTUP FORCE
You can use the STARTUP FORCE startup option if you are experiencing difficulty starting the database in a normal fashion. For example, if a database server lost power and the database stopped abruptly, it can leave the database in a state in which a STARTUP FORCE startup is necessary. This type of startup should not normally be required but can be used if a normal startup does not work. What is also different about STARTUP FORCE is that it can issued no matter what mode the database is in. STARTUP FORCE does a shutdown abort and then restarts the database.
STARTUP RESTRICT
The STARTUP RESTRICT option starts up the database and places it in OPEN mode, but gives access only to users who have the RESTRICTED SESSION privilege. You might want to open a database using the RESTRICTED option when you want to perform maintenance on the database while it is open but ensure that users cannot connect and perform work on the database. You might also want to open the database using the RESTRICTED option to perform database exports or imports and guarantee that no users are accessing the system during these activities. After you are done with your work, you can disable the restricted session, ALTER SYSTEM DISABLE RESTRICTED SESSION, so everyone can connect to the database.
2.4.1.1. Starting Up Oracle Using EM Database Control
Now that you understand the various startup options, let's look at how to use the EM Database Control to start up the Oracle instance.
When you invoke the Enterprise Manager console, you are notified that the database instance is down (see Figure 2.30).
Click the Startup button located on the Database Control screen to open the Startup/Shutdown: Specify Host And Target Database Credentials screen (see Figure 2.31). On this screen, you need to supply an operating system username and password and an Oracle user ID and password that has either the SYSDBA or SYSOPER account authentication. After you enter the appropriate user ID and password information, click OK to open the Startup/Shutdown: Confirmation screen, as shown in Figure 2.32.
Figure 2.30. The EM Database Control database status screen
Figure 2.31. Startup/Shutdown: Specify Host And Target Database Credentials screen
From here, you can click Yes to continue, No to cancel, or Advanced Options to select advanced startup options.
If you click Advanced Options, you can select the type of startup you want (see Figure 2.33). You can choose your startup mode (NOMOUNT, MOUNT, or OPEN), you can choose the parameter file to use, and you can choose to force database startup or to start the database in RESTRICTED mode. Click OK to return the previous screen. By default, Oracle starts with the OPEN option and uses the default initialization file.
NOTE
You can also click Show SQL to see the actual startup command that will be executed.
After you choose the type of startup, click Yes. The startup process may take some time to complete depending on system speed and whether Oracle has to perform any recovery operations during the startup process. You will be presented with a screen indicating that the database is being started (see Figure 2.34). If Oracle does not encounter any problems with the startup process, you will be notified that the database is now open and available.
2.4.1.2. Starting Oracle Using SQL*Plus
You can also use the command-line facility SQL*Plus to start the Oracle database. You will need to connect to SQL*Plus as a user with SYSOPER or SYSDBA privileges. Here is a syntax diagram of the startup options available:
STARTUP [NOMOUNT|MOUNT|OPEN] [PFILE/SPFILE=] [RESTRICT]
Figure 2.32. Startup/Shutdown: Confirmation screen
Figure 2.33. Startup/Shutdown: Advanced Startup Options screen
Figure 2.34. Startup/Shutdown: Activity Information screen
Table 2.5 shows some examples of startup commands that you can use from within SQL*Plus.
Here is an example of how you can use the STARTUP FORCE command with a nondefault parameter file to start up an Oracle database using SQL*Plus:
D:oracleora10g>sqlplus "/ as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jul 19 15:59:22 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to an idle instance. SQL> startup force pfile=d:oracleora10ginitORCL1.ora ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145750508 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytes Database mounted. Database opened. SQL>
NOTE
If you are running Oracle on Windows, you can also start the database when you start the associated Oracle service. Starting the Oracle service automatically starts the Oracle database.
2.4.2. Shutting Down an Oracle 10g Database
In some instances, you will need to shut down a database, for example, to perform regularly scheduled cold backups of the database or to perform database upgrades. Whatever the case, you need to understand the shutdown options. Just as with database startup, several options are available, as well as a variety of interfaces that you can use.
SHUTDOWN NORMAL
A normal shutdown is the default type of shutdown that Oracle performs if no shutdown options are provided. You need to be aware of the following when doing a normal shutdown:
No new Oracle connections are allowed from the time the SHUTDOWN NORMAL command is issued.
The database will wait until all users are disconnected to proceed with the shutdown process.
Because Oracle waits until all users are disconnected before shutting down, you can find yourself waiting indefinitely for a client who may be connected but is no longer doing any work or may have left for the day. This can require extra work, identifying which connections are still active and either notifying the users to disconnect or forcing the client disconnections by killing their session. This type of shutdown is also known as a "clean" shutdown because when you start Oracle again, no recovery is necessary.
SHUTDOWN TRANSACTIONAL
A transactional shutdown of the database is a bit more aggressive than a normal shutdown. The characteristics of the transactional shutdown are as follows:
No new Oracle connections are allowed from the time the SHUTDOWN TRANSACTIONAL command is issued.
No new transactions are allowed to start from the time the SHUTDOWN TRANSACTIONAL command is issued.
Once all active transactions on the database have completed, all client connections are disconnected.
A transactional shutdown does allow client processes to complete prior to the disconnection. This can prevent a client from losing work and can be valuable especially if the database has long-running transactions that need to be completed prior to shutdown. This type of shutdown is also a clean shutdown and does not require any recovery on a subsequent startup.
SHUTDOWN IMMEDIATE
The immediate shutdown method is the next most aggressive option. An immediate shutdown is characterized as follows:
No new Oracle connections are allowed from the time the SHUTDOWN IMMEDIATE command is issued.
Any uncommitted transactions are rolled back. Thus, a user in the middle of a transaction will lose all the uncommitted work.
Oracle does not wait for clients to disconnect. Any unfinished transactions are rolled back, and their database connections are terminated.
This type of shutdown works well if you want to perform unattended or scripted shutdowns of the database and you need to ensure that the database will shut down without getting hung up during the process by clients who are connected. Even though Oracle is forcing transactions to roll back and disconnecting users, an immediate shutdown is still considered a clean shutdown. No recovery activity takes place when Oracle is subsequently restarted.
SHUTDOWN ABORT
A shutdown abort is the most aggressive type of shutdown and has the following characteristics:
No new Oracle connections are allowed from the time the SHUTDOWN ABORT command is issued.
Any SQL statements currently in progress are terminated, regardless of their state.
Uncommitted work is not rolled back.
Oracle disconnects all client connections immediately upon the issuance of the SHUTDOWN ABORT command.
Do not use SHUTDOWN ABORT regularly. Use it only if the other options for database shutdown fail or if you are experiencing some type of database problem that is preventing Oracle from performing a clean shutdown. This type of shutdown is not a clean shutdown and requires recovery when the database is subsequently started.
2.4.3. Shutting Down Oracle Using EM Database Control
You can use the EM Database Control to shut down the Oracle database. To do so, invoke the EM Database Control from your web browser. Click the Shutdown button in the General section (see Figure 2.35).
After you click Shutdown, you are presented with the Startup/Shutdown: Specify Host and Target Database Credentials screen (see Figure 2.36). You must supply an OS user ID and password to log into the target database machine. If you are not using Operating System Authentication, you must also enter an Oracle user ID and password that has SYSDBA authority.
After you authenticate, the Startup/Shutdown: Confirmation screen is displayed (see Figure 2.37). The default shutdown selected when you are using the EM Database Control is SHUTDOWN IMMEDIATE. Oracle also displays the current status of the database on this form.
Figure 2.35. The EM Database Control Home screen
Figure 2.36. Startup/Shutdown: Specify Host and Target Database Credentials screen
To perform a nondefault type of shutdown, click the Advanced Options button. In the Startup/ Shutdown: Advanced Shutdown Options screen (see Figure 2.38), you can select the type of shutdown.
After you select the type of shutdown, click OK, and then click Yes in the Confirmation screen to open a screen informing you that the database shutdown is in progress (see Figure 2.39). Once the process has completed, click the Refresh button, and you will see that the database is now shut down (see Figure 2.40). In this EM Database Control database status screen, you can start the database.
Figure 2.37. The Startup/Shutdown: Confirmation screen
Figure 2.38. The Startup/Shutdown: Advanced Shutdown Options screen
Figure 2.39. The Startup/Shutdown: Activity Information screen
Figure 2.40. The EM Database Control database status screen
2.4.4. Shutting Down Oracle Using SQL*Plus
You can also use the command-line facility SQL*Plus to shut down the Oracle database. You will need to connect to SQL*Plus as a user with SYSOPER or SYSDBA privileges. Here is a syntax diagram of the shutdown options available to you:
SHUTDOWN [NORMAL|IMMEDIATE|RESTRICT|ABORT]
Here is an example of how to use the SHUTDOWN IMMEDIATE command to shut down an Oracle database using SQL*Plus:
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jul 19 15:30:25 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
NOTE
If you are running in a Windows environment and shut down the database using either the Database Control or SQL*Plus tools, the Oracle Service will continue to run. Even though the Oracle Windows service is running, the database is not available until a subsequent startup command is issued.
2.4.5. Monitoring the Database Alert Log
The Database alert log, sometimes referred to as the alert file, contains information about certain activities and errors that occur within your database. The alert log contains a chronological summary of these events. The alert log contains a wealth of information that you can use to diagnose system problems and review histories of activities that have occurred on the system. Some of the events and actions recorded in the alert log include the following:
Startup and shutdown information, including a record of every time a database is started or shut down
Certain types of administrative actions, such as ALTER SYSTEM or ALTER DATABASE commands
Certain types of database errors, such as internal Oracle errors (ORA-600 errors) or space errors (ORA-1642, for example)
The values of initialization parameters that have had values different from their default values
Here is an excerpt from an Oracle 10g alert log:
Starting ORACLE instance (normal)
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.2.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 83886080
large_pool_size = 8388608
java_pool_size = 50331648
control_files =
D:ORACLEPRODUCT10.1.0ORADATAORCLCONTROL01.CTL,
D:ORACLEPRODUCT10.1.0ORADATAORCLCONTROL02.CTL,
D:ORACLEPRODUCT10.1.0ORADATAORCLCONTROL03.CTL
db_block_size = 8192
db_cache_size = 25165824
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = D:oracleproduct10.1.0flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes = 10
background_dump_dest = D:ORACLEPRODUCT10.1.0ADMINORCLBDUMP
user_dump_dest = D:ORACLEPRODUCT10.1.0ADMINORCLUDUMP
core_dump_dest = D:ORACLEPRODUCT10.1.0ADMINORCLCDUMP
sort_area_size = 65536
db_name = orcl
open_cursors = 300
pga_aggregate_target = 25165824
Thu Jul 22 15:25:55 2004
starting up 1 dispatcher(s) for network address
'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
LGWR started with pid=5, OS id=2140
SMON started with pid=7, OS id=2136
RECO started with pid=8, OS id=2184
PMON started with pid=2, OS id=2132CKPT started with pid=6, OS id=2180 MMAN started with pid=3, OS id=2116 CJQ0 started with pid=9, OS id=2192 DBW0 started with pid=4, OS id=2128 Thu Jul 22 15:26:01 2004 alter database mount exclusive Thu Jul 22 15:26:17 2004 Controlfile identified with block size 16384 Thu Jul 22 15:26:21 2004 Setting recovery target incarnation to 2 Thu Jul 22 15:26:22 2004 Successful mount of redo thread 1, with mount id 1059986201 Thu Jul 22 15:26:22 2004 Database mounted in Exclusive Mode. Completed: alter database mount exclusive Thu Jul 22 15:26:22 2004 alter database open Thu Jul 22 15:26:23 2004 Beginning crash recovery of 1 threads Thu Jul 22 15:26:24 2004 Started first pass scan Thu Jul 22 15:26:25 2004 Completed first pass scan 1579 redo blocks read, 169 data blocks need recovery Thu Jul 22 15:26:25 2004 Started redo application at Thread 1: logseq 19, block 3084, scn 0.0 Recovery of Online Redo Log: Thread 1 Group 3 Seq 19 Reading mem 0 Mem# 0 errs 0: D:ORACLEPRODUCT10.1.0ORADATAORCLREDO03.LOG Thu Jul 22 15:26:28 2004 Completed redo application Thu Jul 22 15:26:28 2004 Completed crash recovery at Thread 1: logseq 19, block 4663, scn 0.490142 169 data blocks read, 169 data blocks written, 1579 redo blocks read Thu Jul 22 15:26:31 2004 Thread 1 advanced to log sequence 20 Maximum redo generation record size = 120832 bytes Maximum redo generation change vector size = 116476 bytes Private_strands 7 at log switch Thread 1 opened at log sequence 20
Current log# 1 seq# 20 mem# 0:
D:ORACLEPRODUCT10.1.0ORADATAORCLREDO01.LOG
Successful open of redo thread 1
Thu Jul 22 15:26:31 2004
Starting background process MMON
MMON started with pid=15, OS id=2248
Thu Jul 22 15:27:11 2004
Starting background process MMNL
MMNL started with pid=16, OS id=2296
Thu Jul 22 15:27:17 2004
Completed: alter database openThis excerpt shows a successful startup of a database. Notice the section that lists the nondefault initialization parameters. Also notice that Oracle performed an automatic recovery of the database. This indicates that the database was not shut down cleanly prior to this startup. You can also see that Oracle is starting dispatcher processes, which indicates that we are running Oracle Shared Server.
The parameter that governs the location of the alert log is called BACKGROUND_DUMP_DEST. This parameter is set to a path that designates where Oracle should place the log. Here is an example of this setting on a Unix system in the initialization parameter file:
BACKGROUND_DUMP_DEST = /u01/app/oracle/admin/PROD/bdump
The alert log is continuously appended to, so it is a good idea to periodically purge it. Many DBAs do so daily or weekly, saving a copy of the current alert log to a backup and clearing the current alert log. It is a good idea to save the log contents. You can use it to review when initialization parameters changed and to review database errors or problems recorded in the log.