3 Starting Up and Shutting Down

About Database Startup Options

When Oracle Restart is not in use, you can start up a database instance with SQL*Plus, Recovery Manager, or Oracle Enterprise Manager Cloud Control (Cloud Control). If your database is being managed by Oracle Restart, the recommended way to start the database is with SRVCTL.

See Chapter 4, "Configuring Automatic Restart of an Oracle Database" for information about Oracle Restart.

Specifying Initialization Parameters at Startup

To start an instance, the database must read instance configuration parameters (the initialization parameters) from either a server parameter file (SPFILE) or a text initialization parameter file (PFILE).

The database looks for these files in a default location. You can specify nondefault locations for these files, and the method for doing so depends on whether you start the database with SQL*Plus (when Oracle Restart is not in use) or with SRVCTL (when the database is being managed with Oracle Restart).

The following sections provide details:

• About Initialization Parameter Files and Startup

• Starting Up with SQL*Plus with a Nondefault Server Parameter File

• Starting Up with SRVCTL with a Nondefault Server Parameter File

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

About Automatic Startup of Database Services

When your database is managed by Oracle Restart, you can configure startup options for each individual database service (service). If you set the management policy for a service to AUTOMATIC (the default), the service starts automatically when you start the database with SRVCTL. If you set the management policy to MANUAL, the service does not automatically start, and you must manually start it with SRVCTL. A MANUAL setting does not prevent Oracle Restart from monitoring the service when it is running and restarting it if a failure occurs.

In an Oracle Data Guard (Data Guard) environment in which databases are managed by Oracle Restart, you can additionally control automatic startup of services by assigning Data Guard roles to the services in their Oracle Restart configurations. A service automatically starts upon manual database startup only if the management policy of the service is AUTOMATIC and if one of its assigned roles matches the current role of the database.

See "srvctl add service" and "srvctl modify service" for the syntax for setting the management policy of and Data Guard roles for a service.

Preparing to Start Up an Instance

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

1. Ensure that any Oracle components on which the database depends are started.

   For example, if the database stores data in Oracle Automatic Storage Management (Oracle ASM) disk groups, ensure that the Oracle ASM instance is running and the required disk groups are mounted. Also, it is preferable to start the Oracle Net listener before starting the database.

2. If you intend to use operating system authentication, log in to the database host computer as a member of the OSDBA group.

   See "Using Operating System Authentication" for more information.

3. Ensure that environment variables are set so that you connect to the desired Oracle instance. For details, see "Submitting Commands and SQL to the Database".

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

   SQLPLUS /NOLOG
   

5. Connect to Oracle Database as SYSOPER, SYSDBA, SYSBACKUP, or SYSDG. For example:

   CONNECT username AS SYSDBA
   
   —or—
   
   CONNECT / AS SYSDBA
   

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

Starting Up an Instance

When Oracle Restart is not in use, you use the SQL*Plus STARTUP command to start up an Oracle Database instance. If your database is being managed by Oracle Restart, Oracle recommends that you use the srvctl start database command.

In either case, you can start an instance in various modes:

• NOMOUNT—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.

• MOUNT—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.

• OPEN—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.

• FORCE—Force the instance to start after a startup or shutdown problem.

• OPEN RECOVER—Start the instance and have complete media recovery begin immediately.

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

STARTUP

srvctl start database -db db_unique_name

STARTUP NOMOUNT

srvctl start database -db db_unique_name -startoption nomount

STARTUP MOUNT

srvctl start database -db db_unique_name -startoption mount

STARTUP RESTRICT

srvctl start database -db db_unique_name -startoption restrict

ALTER SYSTEM DISABLE RESTRICTED SESSION;

STARTUP FORCE

srvctl start database -db db_unique_name -startoption force

STARTUP OPEN RECOVER

srvctl start database -db db_unique_name -startoption "open,recover"

Mounting a Database to an Instance

When you 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 clause 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 SQL statement with the OPEN clause:

ALTER DATABASE OPEN;

After executing this statement, any valid Oracle Database 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 data files 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 data files offline or bring them online since these operations do not affect 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".

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.

Limitations of a Read-only Database

• An application must not write database objects while executing against a read-only database. For example, an application writes database objects when it inserts, deletes, updates, or merges rows in a database table, including a global temporary table. An application writes database objects when it manipulates a database sequence. An application writes database objects when it locks rows, when it runs EXPLAIN PLAN, or when it executes DDL. Many of the functions and procedures in Oracle-supplied PL/SQL packages, such as DBMS_SCHEDULER, write database objects. If your application calls any of these functions and procedures, or if it performs any of the preceding operations, your application writes database objects and hence is not read-only.

• When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link. This is true even if you execute a generic SELECT statement on the first database link and the transaction is currently read-only.

• You cannot compile or recompile PL/SQL stored procedures on a read-only database. To minimize PL/SQL invalidation because of remote procedure calls, use REMOTE_DEPENDENCIES_MODE=SIGNATURE in any session that does remote procedure calls on a read-only database.

• You cannot invoke a remote procedure (even a read-only remote procedure) from a read-only database if the remote procedure has never been called on the database. This limitation applies to remote procedure calls in anonymous PL/SQL blocks and in SQL statements. You can either put the remote procedure call in a stored procedure, or you can invoke the remote procedure in the database before it becomes read only.

Restricting Access to an Open Database

To place an already running instance in restricted mode, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause. After this statement successfully completes, only users with the RESTRICTED SESSION privilege can initiate new connections. Users connecting as SYSDBA or connecting with the DBA role have this privilege.

When you place a running instance in restricted mode, no user sessions are terminated or otherwise affected. Therefore, after placing an instance in restricted mode, consider killing (terminating) all current user sessions before performing administrative tasks.

To lift an instance from restricted mode, use ALTER SYSTEM with the DISABLE RESTRICTED SESSION clause.

Shutting Down with the Normal Mode

To shut down a database in normal situations, use one of these commands:

SHUTDOWN [NORMAL]

srvctl stop database -db db_unique_name -stopoption normal

The NORMAL clause of the SQL*Plus SHUTDOWN command is optional because this is the default shutdown method. For SRVCTL, if the -stopoption option is omitted, the shutdown operation proceeds according to the stop options stored in the Oracle Restart configuration for the database. The default stop option is immediate.

Normal database shutdown proceeds with the following conditions:

• No new connections are allowed after the statement is issued.

• Before the database is shut down, the database 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 Mode

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 one of the following commands:

SHUTDOWN IMMEDIATE

srvctl stop database -db db_unique_name -stopoption 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 Database does not wait for users currently connected to the database to disconnect. The database 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 Mode

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use one of the following commands:

SHUTDOWN TRANSACTIONAL

srvctl stop database -db db_unique_name -stopoption 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 Mode

You can shut down a database instantaneously by aborting the database 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 must 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, use one of the following commands:

SHUTDOWN ABORT

srvctl stop database -db db_unique_name -stopoption 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 Database are immediately terminated.

• Uncommitted transactions are not rolled back.

• Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

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

Shutdown Timeout

Shutdown modes that wait for users to disconnect or for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: user requested cancel of current operation. This message is also displayed if you interrupt the shutdown process, for example by pressing CTRL-C. Oracle recommends that you do not attempt to interrupt an instance shutdown. Instead, allow the shutdown process to complete, and then restart the instance.

After ORA-01013 occurs, you must consider the instance to be in an unpredictable state. You must therefore continue the shutdown process by resubmitting a SHUTDOWN command. If subsequent SHUTDOWN commands continue to fail, you must submit a SHUTDOWN ABORT command to bring down the instance. You can then restart the instance.

Placing a Database into a Quiesced State

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

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one 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. For example, If a user 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 is processed.

Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is 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. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view. This view returns only a single column: SID (Session ID). You can join it with V$SESSION to get more information about the session, as shown in the following example:

select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;

See Oracle Database Reference for details on these view.

If you interrupt the request to quiesce the database, or if your session terminates abnormally before all active sessions are quiesced, then Oracle Database automatically reverses any partial effects of the statement.

For queries that are 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.

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 quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCE statement terminates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.

Viewing the Quiesce State of an Instance

You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:

• NORMAL: Normal unquiesced state.

• QUIESCING: Being quiesced, but some non-DBA sessions are still active.

• QUIESCED: Quiesced; no non-DBA sessions are active or allowed.