4
Starting Up and Shutting Down

This chapter describes the procedures for starting up and shutting down an Oracle database, and contains the following topics:

• Starting Up a Database
• Altering Database Availability
• Shutting Down a Database
• Quiescing a Database
• Suspending and Resuming a Database

  See Also: For additional information specific to an Oracle Real Application Clusters environment: Oracle9i Real Application Clusters Administration Oracle9i Real Application Clusters Setup and Configuration

Starting Up a Database

When you start up a database, you create an instance of that database, and you choose the state in which the database starts. Normally, you would start up an instance by mounting and opening the database, thus making it available for any valid user to connect to and perform typical data access operations. However, there are other options and these are also discussed in this section.

This section contains the following topics relating to starting up an instance of a database:

• Options for Starting Up a Database
• Preparing to Start an Instance
• Using SQL*Plus to Start Up a Database
• Starting an Instance: Scenarios

Options for Starting Up a Database

There are options as to the method you use for starting up (and administering) an instance of your database.

Using SQL*Plus

To start up a database use SQL*Plus to connect to Oracle with administrator privileges and then issue the STARTUP command. While three methods are presented, using SQL*Plus is the only method that is within the scope of this book.

Using Recovery Manager

You can also use Recovery Manager (RMAN) to execute STARTUP (and SHUTDOWN) commands. You may prefer to do this if your are within the RMAN environment and do not want to invoke SQL*Plus.

Using Oracle Enterprise Manager

You can choose to use the Oracle Enterprise Manager for administering your database, including starting it up and shutting it down. The Oracle Enterprise Manager is a separate Oracle product, that combines a graphical console, agents, common services, and tools to provide an integrated and comprehensive systems management platform for managing Oracle products. It enables you to perform the functions discussed in this book using a GUI interface, rather than command lines.

Preparing to Start an Instance

You must perform some preliminary steps before attempting to start an instance of your database using SQL*Plus.

1. Start SQL*Plus without connecting to the database:

   SQLPLUS /NOLOG
   
   

2. Connect to Oracle as SYSDBA:

   CONNECT username/password AS SYSDBA
   
   

Now you are connected to Oracle and ready to start up an instance of your database.

Using SQL*Plus to Start Up a Database

You use the STARTUP command to start up a database instance. To start an instance, Oracle must read instance configuration parameters (the initialization parameters) from either a server parameter file or a traditional text initialization parameter file.

When you issue the STARTUP command with no PFILE clause, Oracle reads the initialization parameters from a server parameter file (SPFILE) in a platform-specific default location.

In the platform-specific default location, Oracle locates your initialization parameter file by examining filenames in the following order:

1. spfile$ORACLE_SID.ora
2. spfile.ora
3. init$ORACLE_SID.ora

   Note: The spfile.ora file is included in this search path because in a Real Application Clusters environment one server parameter file is used to store the initialization parameter settings for all instances. There is no instance specific location for storing a server parameter file. For more information about the server parameter file for a Real Application Clusters environment, see Oracle9i Real Application Clusters Administration.

You can direct Oracle to read initialization parameters from a traditional text initialization parameter file, by using the PFILE clause of the STARTUP command. For example:

STARTUP PFILE = /u01/oracle/dbs/init.ora

Further, you can use this PFILE clause to start an instance with a nondefault server parameter file as follows:

1. Create a one line text initialization parameter file that contains only the SPFILE parameter. The value of the parameter is the nondefault server parameter file location.

   For example, create a text initialization parameter file /u01/oracle/dbs/spf_init.ora that contains only the following parameter:

   SPFILE = /u01/oracle/dbs/test_spfile.ora
   

   Note: You cannot use the IFILE initialization parameter within a text initialization parameter file to point to a server parameter file. In this context, you must use the SPFILE initialization parameter.

2. Start up the instance pointing to this initialization parameter file.

   STARTUP PFILE = /u01/oracle/dbs/spf_init.ora
   
   

Since the server parameter file must reside on the machine running the database server, the above method also provides a means for a client machine to start a database that uses a server parameter file. It also eliminates the need for a client machine to maintain a client-side initialization parameter file. When the client machine reads the initialization parameter file containing the SPFILE parameter, it passes the value to the server where the specified server parameter file is read.

You can start an instance in various modes:

• Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files.
• Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database.
• Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only.

  Note: You cannot start a database instance if you are connected to the database through a shared server process.

In addition, you can force the instance to start, or start the instance and have complete media recovery begin immediately. The STARTUP command options that you specify to achieve these states are illustrated in the following section.

Starting an Instance: Scenarios

The following scenarios describe and illustrate the various states in which you can start up an instance. Some restrictions apply when combining options of the STARTUP command.

Starting an Instance, and Mounting and Opening a Database

Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform typical data access operations.

Start an instance, read the initialization parameters from the default server parameter file location, and then mount and open the database by using the STARTUP command by itself (you can, of course, optionally specify a PFILE or SPFILE clause):

STARTUP

Starting an Instance Without Mounting a Database

You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT option:

STARTUP NOMOUNT 

Starting an Instance and Mounting a Database

You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. For example, the database must be mounted but not open during the following tasks:

Task	For more information...
Renaming datafiles	Chapter 12, "Managing Datafiles"
Adding, dropping, or renaming redo log files	Chapter 7, "Managing the Online Redo Log"
Enabling and disabling redo log archiving options	Chapter 8, "Managing Archived Redo Logs"
Performing full database recovery	Oracle9i User-Managed Backup and Recovery Guide Oracle9i Recovery Manager User's Guide

Start an instance and mount the database, but leave it closed by using the STARTUP command with the MOUNT option:

STARTUP MOUNT

Restricting Access to a Database at Startup

You can start an instance and mount and open a database in restricted mode so that the database is available only to administrative personnel (not general database users). Use this mode of database startup when you need to accomplish one of the following tasks:

• Perform an export or import of database data
• Perform a data load (with SQL*Loader)
• Temporarily prevent typical users from using data
• During certain migration and upgrade operations

Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege.

Start an instance (and, optionally, mount and open the database) in restricted mode by using the STARTUP command with the RESTRICT option:

STARTUP RESTRICT

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

If you open the database in nonrestricted mode and later find you need to restrict access, you can use the ALTER SYSTEM statement to do so, as described in "Restricting Access to an Open Database".

Forcing an Instance to Start

In unusual circumstances, you might experience problems when attempting to start a database instance. You should not force a database to start unless you are faced with the following:

• You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.
• You experience problems when starting an instance.

If one of these situations arises, you can usually solve the problem by starting a new instance (and optionally mounting and opening the database) using the STARTUP command with the FORCE option:

STARTUP FORCE

If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.

Starting an Instance, Mounting a Database, and Starting Complete Media Recovery

If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER option:

STARTUP OPEN RECOVER

If you attempt to perform recovery when no recovery is required, Oracle issues an error message.

Automatic Database Startup at Operating System Start

Many sites use procedures to enable automatic startup of one or more Oracle instances and databases immediately following a system start. The procedures for performing this task are specific to each operating system. For information about automatic startup, see your operating system specific Oracle documentation.

Starting Remote Instances

If your local Oracle server is part of a distributed database, you might want to start a remote instance and database. Procedures for starting and stopping remote instances vary widely depending on communication protocol and operating system.

Altering Database Availability

You can alter the availability of a database. You may want to do this in order to restrict access for maintenance reasons or to make the database read only. The following sections explain how to alter a database's availability:

• Mounting a Database to an Instance
• Opening a Closed Database
• Opening a Database in Read-Only Mode
• Restricting Access to an Open Database

Mounting a Database to an Instance

When you need to perform specific administrative operations, the database must be started and mounted to an instance, but closed. You can achieve this scenario by starting the instance and mounting the database.

To mount a database to a previously started, but not opened instance, use the SQL statement ALTER DATABASE with the MOUNT option as follows:

ALTER DATABASE MOUNT

Opening a Closed Database

You can make a mounted but closed database available for general use by opening the database. To open a mounted database, use the ALTER DATABASE statement with the OPEN option:

ALTER DATABASE OPEN

After executing this statement, any valid Oracle user with the CREATE SESSION system privilege can connect to the database.

Opening a Database in Read-Only Mode

Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not effect data content.

If a query against a database in read-only mode uses temporary tablespace, for example to do disk sorts, then the issuer of the query must have a locally managed tablespace assigned as the default temporary tablespace. Otherwise, the query will fail. This is explained in "Creating a Locally Managed Temporary Tablespace".

Ideally, you open a database in read-only mode when you alternate a standby database between read-only and recovery mode. Be aware that these are mutually exclusive modes.

The following statement opens a database in read-only mode:

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read-write mode as follows:

ALTER DATABASE OPEN READ WRITE;

However, read-write is the default mode.

Restricting Access to an Open Database

To place an instance in restricted mode, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause. After placing an instance in restricted mode, you should consider killing all current user sessions before performing any administrative tasks. To lift an instance from restricted mode, use ALTER SYSTEM with the DISABLE RESTRICTED SESSION option.

Shutting Down a Database

To initiate database shutdown, use the SQL*Plus SHUTDOWN command. Control is not returned to the session that initiates a database shutdown until shutdown is complete. Users who attempt connections while a shutdown is in progress receive a message like the following:

ORA-01090: shutdown in progress - connection is not permitted

To shut down a database and instance, you must first connect as SYSOPER or SYSDBA. There are several modes for shutting down a database. These are discussed in the following sections:

• Shutting Down with the NORMAL Option
• Shutting Down with the IMMEDIATE Option
• Shutting Down with the TRANSACTIONAL Option
• Shutting Down with the ABORT Option

Shutting Down with the NORMAL Option

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL option:

SHUTDOWN NORMAL

Normal database shutdown proceeds with the following conditions:

• No new connections are allowed after the statement is issued.
• Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database.

The next startup of the database will not require any instance recovery procedures.

Shutting Down with the IMMEDIATE Option

Use immediate database shutdown only in the following situations:

• To initiate an automated and unattended backup
• When a power shutdown is going to occur soon
• When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE option:

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
• Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
• Oracle does not wait for users currently connected to the database to disconnect. Oracle implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

Shutting Down with the TRANSACTIONAL Option

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL option:

SHUTDOWN TRANSACTIONAL

Transactional database shutdown proceeds with the following conditions:

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
• After all transactions have completed, any client still connected to the instance is disconnected.
• At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

Shutting Down with the ABORT Option

You can shut down a database instantaneously by aborting the database's instance. If possible, perform this type of shutdown only in the following situations:

• The database or one of its applications is functioning irregularly and none of the other types of shutdown works.
• You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
• You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT option:

SHUTDOWN ABORT

An aborted database shutdown proceeds with the following conditions:

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
• Current client SQL statements being processed by Oracle are immediately terminated.
• Uncommitted transactions are not rolled back.
• Oracle does not wait for users currently connected to the database to disconnect. Oracle implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

Quiescing a Database

There are times when there is a need to put a database into a state where only DBA transactions, queries, fetches, or PL/SQL statements are allowed. This is called a quiesced state, in the sense that there are no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements in the system. This quiesced state allows you or other administrators to perform actions that cannot safely be done otherwise. These actions are categorized as follows:

• Actions that can fail if concurrent user transactions access the same object. For example, changing the schema of a database table or adding a column to an existing table where a no-wait lock is required.
• Actions whose undesirable intermediate effect can be seen by concurrent user transactions. For example, a multistep procedure for reorganizing a table where the table is first exported, then dropped, and finally imported. A concurrent user who attempted to access the table after it was dropped, but before import, would see disturbing results.

Without the ability to quiesce the database, you would be required to shut down the database and reopen it in restricted mode. This is a serious restriction, especially for systems requiring 24 x 7 availability. Quiescing a database is much less of a restriction because it eliminates the disruption to users and downtime associated with shutting down and restarting the database.

Placing a Database into a Quiesced State

To place a database into a quiesced state, issue the following statement:

ALTER SYSTEM QUIESCE RESTRICTED;

Any non-DBA active sessions will proceed until they become inactive. An active session is defined as a session that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. If a user, for example, issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action (for example, the previously mentioned SQL query) will be processed.

Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement finishes, and the database is considered as in a quiesced state. In an Oracle Real Application Clusters environment, this statement affects all instances, not just the one that issues the statement.

The ALTER SYSTEM QUIESCE RESTRICTED statement may wait a long time for active sessions to become inactive. If you interrupt the request, or if your session abnormally terminates for some reason before all active sessions are quiesced, Oracle will automatically undo any partial effects of the statement.

If a query is carried out by successive multiple Oracle Call Interface (OCI) fetches, the ALTER SYSTEM QUIESCE RESTRICTED statement does not wait for all fetches to finish; it only waits for the current fetch to finish.

For both dedicated and shared server connections, all non-DBA logins after this statement is issued are queued by the Database Resource Manager, and are not allowed to proceed. To the user, it appears as if the login is hung. The login will resume when the database is unquiesced.

The database remains in the quiesced state even if the session that issued the statement exits. A DBA must log in to the database to issue the statement that specifically unquiesces the database.

While in the quiesced state, you cannot use file system copy to backup the database's datafiles as cold backups, even if you do a checkpoint on every instance. The reason for this is that in the quiesced state the file headers of online datafiles continue to look like they are being accessed. They do not look the same as if a clean shutdown were done. Similarly, to perform a hot backup of the datafiles of any online tablespace while the database is in a quiesced state, you are still required to first place the tablespace into backup mode using the ALTER TABLESPACE... BEGIN BACKUP statement.

Restoring the System to Normal Operation

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which imposed the quiesce state. If the session issuing the ALTER SYSTEM UNQUIESCE statement should terminate abnormally, the Oracle database server ensures that the unquiesce operation finishes.

Viewing the Quiesce State of an Instance

The V$INSTANCE view can be queried to see the current state of an instance. It contains a column named ACTIVE_STATE, whose values are shown in the following table:

ACTIVE_STATE	Description
NORMAL	Normal unquiesced state
QUIESCING	Being quiesced, but there are still active non-DBA sessions running
QUIESCED	Quiesced, no active non-DBA sessions are active or allowed

Suspending and Resuming a Database

The ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles (file header and file data) and control files, thus allowing a database to be backed up without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

The suspend command suspends the database, and is not specific to an instance. Therefore, in an Oracle Real Application Clusters environment, if the suspend command is entered on one system, then internal locking mechanisms will propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, do not start a new instance while you suspend another instance, since the new instance will not be suspended.

Use the ALTER SYSTEM RESUME statement to resume normal database operations. You can specify the SUSPEND and RESUME from different instances. For example, if instances 1, 2, and 3 are running, and you issue an ALTER SYSTEM SUSPEND statement from instance 1, then you can issue a RESUME from instance 1, 2, or 3 with the same effect.

The suspend/resume feature is useful in systems that allow you to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If you use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then you can use the suspend/resume feature to facilitate the split.

The suspend/resume feature is not a suitable substitute for normal shutdown operations, however, since copies of a suspended database can contain uncommitted updates.

The following statements illustrate ALTER SYSTEM SUSPEND/RESUME usage. The V$INSTANCE view is queried to confirm database status.

SQL> ALTER SYSTEM SUSPEND;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
SUSPENDED

SQL> ALTER SYSTEM RESUME;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
ACTIVE