Starting Up and Shutting Down

3 Starting Up and Shutting Down

When you start up a database, you create an instance of that database and you determine the state of the database. Shutting down a currently running Oracle Database instance can optionally close and dismount a database.

Starting Up a Database
When you start up a database, you create an instance of that database and you determine the state of the database. Normally, you start up an instance by mounting and opening the database. Doing so makes the database available for any valid user to connect to and perform typical data access operations.
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.
Shutting Down a Database
You can shut down a database with SQL*Plus or Oracle Restart.
Quiescing a Database
A quiesced database allows only DBA transactions, queries, fetches, or PL/SQL statements.
Suspending and Resuming a Database
The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to data files (file header and file data) and control files. The suspended state lets you back up a database 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. Use the ALTER SYSTEM RESUME statement to resume normal database operations.
Delaying Instance Abort
The INSTANCE_ABORT_DELAY_TIME initialization parameter specifies the amount of time, in seconds, to delay shutting down a database when an error causes the instance to abort.

See Also:

Oracle Real Application Clusters Administration and Deployment Guide for additional information specific to an Oracle Real Application Clusters environment

Parent topic: Basic Database Administration

3.1 Starting Up a Database

When you start up a database, you create an instance of that database and you determine the state of the database. Normally, you start up an instance by mounting and opening the database. Doing so makes the database available for any valid user to connect to and perform typical data access operations.

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.
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).
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).
Preparing to Start Up an Instance
You must perform some preliminary steps before attempting to start an instance of your database using SQL*Plus.
Starting Up an Instance
You can start up an instance using SQL*Plus or Oracle Restart.

Parent topic: Starting Up and Shutting Down

3.1.1 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 Configuring Automatic Restart of an Oracle Database for information about Oracle Restart.

Starting Up a Database Using SQL*Plus
You can start a SQL*Plus session, connect to Oracle Database with administrator privileges, and then issue the STARTUP command. Using SQL*Plus in this way is the only method described in detail in this book.
Starting Up a Database 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.
Starting Up a Database Using Cloud Control
You can use Cloud Control to administer your database, including starting it up and shutting it down. Cloud Control combines a GUI console, agents, common services, and tools to provide an integrated and comprehensive systems management platform for managing Oracle products. Cloud Control enables you to perform the functions discussed in this book using a GUI interface, rather than command line operations.
Starting Up a Database Using SRVCTL
When Oracle Restart is installed and configured for your database, Oracle recommends that you use SRVCTL to start the database.

Parent topic: Starting Up a Database

3.1.1.1 Starting Up a Database Using SQL*Plus

You can start a SQL*Plus session, connect to Oracle Database with administrator privileges, and then issue the STARTUP command. Using SQL*Plus in this way is the only method described in detail in this book.

Run the SQL*Plus STARTUP command.

Related Topics

SQL*Plus User's Guide and Reference

Parent topic: About Database Startup Options

3.1.1.2 Starting Up a Database 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.

Run an RMAN STARTUP command.

See Also:

Oracle Database Backup and Recovery Reference for information about the RMAN STARTUP command

Parent topic: About Database Startup Options

3.1.1.3 Starting Up a Database Using Cloud Control

You can use Cloud Control to administer your database, including starting it up and shutting it down. Cloud Control combines a GUI console, agents, common services, and tools to provide an integrated and comprehensive systems management platform for managing Oracle products. Cloud Control enables you to perform the functions discussed in this book using a GUI interface, rather than command line operations.

In Cloud Control, start the database instance.

See Also:

The Cloud Control online help

Parent topic: About Database Startup Options

3.1.1.4 Starting Up a Database Using SRVCTL

When Oracle Restart is installed and configured for your database, Oracle recommends that you use SRVCTL to start the database.

Starting the database instance with SRVCTL ensures that:

Any components on which the database depends (such as Oracle Automatic Storage Management and the Oracle Net listener) are automatically started first, and in the proper order.
The database is started according to the settings in its Oracle Restart configuration. An example of such a setting is the server parameter file location.
Environment variables stored in the Oracle Restart configuration for the database are set before starting the instance.

To start a database instance with SRVCTL:

Run the srvctl start database command.

See "srvctl start database" and "Starting and Stopping Components Managed by Oracle Restart" for details.

Parent topic: About Database Startup Options

3.1.2 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).

About Initialization Parameter Files and Startup
When you start the database instance, it attempts to read the initialization parameters from an SPFILE in a platform-specific default location. If it finds no SPFILE, then it searches for a text initialization parameter file.
Starting Up with SQL*Plus with a Nondefault Server Parameter File
With SQL*Plus, you can use the PFILE clause to start an instance with a nondefault server parameter file.
Starting Up with SRVCTL with a Nondefault Server Parameter File
If your database is being managed by Oracle Restart, then you can specify the location of a nondefault SPFILE by setting or modifying the SPFILE location option in the Oracle Restart configuration for the database.

See Also:

Creating and Configuring an Oracle Database, for more information about initialization parameters, initialization parameter files, and server parameter files

Parent topic: Starting Up a Database

3.1.2.1 About Initialization Parameter Files and Startup

When you start the database instance, it attempts to read the initialization parameters from an SPFILE in a platform-specific default location. If it finds no SPFILE, then it searches for a text initialization parameter file.

Table 2-3 lists PFILE and SPFILE default names and locations.

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

The location specified by the -spfile option in the SRVCTL commands srvctl add database or srvctl modify database

You can check the current setting with the srvctl config database command.
spfileORACLE_SID.ora
spfile.ora
initORACLE_SID.ora

The first three files are SPFILEs and the fourth is a text initialization parameter file. If DBCA created the SPFILE in an Oracle Automatic Storage Management disk group, then the database searches for the SPFILE in the disk group.

When AS COPY is not specified in a CREATE SPFILE statement and the database is defined as a resource in Oracle Clusterware, if you specify both the spfile_name and the FROM PFILE clause, then this statement automatically updates the SPFILE name and location in the database resource. When AS COPY is specified in a CREATE SPFILE statement, the SPFILE is copied, and the database resource is not updated.

Note:

The spfile.ora file is included in this search path because in an Oracle 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 an Oracle Real Application Clusters environment, see Oracle Real Application Clusters Administration and Deployment Guide.

If you (or the Database Configuration Assistant) created a server parameter file, but you want to override it with a text initialization parameter file, then you can do so with SQL*Plus, specifying the PFILE clause of the STARTUP command to identify the initialization parameter file:

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

Nondefault Server Parameter Files

A nondefault server parameter file (SPFILE) is an SPFILE that is in a location other than the default location. It is not usually necessary to start an instance with a nondefault SPFILE. However, should such a need arise, both SRVCTL (with Oracle Restart) and SQL*Plus provide ways to do so. These are described later in this section.

Initialization Files and Oracle Automatic Storage Management

A database that uses Oracle Automatic Storage Management (Oracle ASM) usually has a nondefault SPFILE. If you use the Database Configuration Assistant (DBCA) to configure a database to use Oracle ASM, DBCA creates an SPFILE for the database instance in an Oracle ASM disk group, and then causes a text initialization parameter file (PFILE) to be created in the default location in the local file system to point to the SPFILE, as explained in the next section.

Related Topics

SRVCTL Command Reference for Oracle Restart

Parent topic: Specifying Initialization Parameters at Startup

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

With SQL*Plus, you can use the PFILE clause to start an instance with a nondefault server parameter file.

To start up with SQL*Plus with a nondefault server parameter file:

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.
Start up the instance pointing to this initialization parameter file.
```
STARTUP PFILE = /u01/oracle/dbs/spf_init.ora
```

The SPFILE must reside on the database host computer. Therefore, the preceding method also provides a means for a client system to start a database that uses an SPFILE. It also eliminates the need for a client system to maintain a client-side initialization parameter file. When the client system reads the initialization parameter file containing the SPFILE parameter, it passes the value to the server where the specified SPFILE is read.

Parent topic: Specifying Initialization Parameters at Startup

3.1.2.3 Starting Up with SRVCTL with a Nondefault Server Parameter File

If your database is being managed by Oracle Restart, then you can specify the location of a nondefault SPFILE by setting or modifying the SPFILE location option in the Oracle Restart configuration for the database.

To start up with SRVCTL with a nondefault server parameter file:

Prepare to run SRVCTL as described in "Preparing to Run SRVCTL".
Enter the following command:
```
srvctl modify database -db db_unique_name -spfile spfile_path
```
where db_unique_name must match the DB_UNIQUE_NAME initialization parameter setting for the database.

Enter the following command:

srvctl start database -db db_unique_name [options]

See "SRVCTL Command Reference for Oracle Restart" for more information.

Parent topic: Specifying Initialization Parameters at Startup

3.1.3 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.

Note:

When using Oracle Restart, Oracle strongly recommends that you use SRVCTL to create database services.

Parent topic: Starting Up a Database

3.1.4 Preparing to Start Up an Instance

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

Note:

The following instructions are for installations where Oracle Restart is not in use. If your database is being managed by Oracle Restart, follow the instructions in "Starting and Stopping Components Managed by Oracle Restart".

To prepare for starting an instance:

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.
If you intend to use operating system authentication, log in to the database host computer as a member of the OSDBA group.
Ensure that environment variables are set so that you connect to the desired Oracle instance.
Start SQL*Plus without connecting to the database:
```
SQLPLUS /NOLOG
```
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.

See Also:

"Using Operating System Authentication"
"Submitting Commands and SQL to the Database" for information about setting environment variables to connect to an Oracle instance
SQL*Plus User's Guide and Reference for descriptions and syntax for the CONNECT, STARTUP, and SHUTDOWN commands

Parent topic: Starting Up a Database

3.1.5 Starting Up an Instance

You can start up an instance using SQL*Plus or Oracle Restart.

About 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.
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 data access operations.
Starting an Instance Without Mounting a Database
You can start an instance without mounting a database. Typically, you do so only during database creation.
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.
Restricting Access to an Instance at Startup
You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users).
Forcing an Instance to Start
In unusual circumstances, you might experience problems when attempting to start a database instance, and you can force a database instance to start.
Starting an Instance, Mounting a Database, and Starting Complete Media Recovery
If you know that media recovery is required, then you can start an instance, mount a database to the instance, and have the recovery process automatically start.
Automatic Database Startup at Operating System Start
Many sites use procedures to enable automatic startup of one or more Oracle Database instances and databases immediately following a system start.
Starting Remote Instances
If your local Oracle Database server is part of a distributed database, then you might want to start a remote instance and database.

Parent topic: Starting Up a Database

3.1.5.1 About 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.

With SQL*Plus and Oracle Restart, 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.

Note:

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

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.

Note:

It is possible to encounter problems starting up an instance if control files, database files, or online redo logs are not available. If one or more of the files specified by the CONTROL_FILES initialization parameter does not exist or cannot be opened when you attempt to mount a database, Oracle Database returns a warning message and does not mount the database. If one or more of the data files or online redo logs is not available or cannot be opened when attempting to open a database, the database returns a warning message and does not open the database.

See Also:

SQL*Plus User's Guide and Reference for details on the STARTUP command syntax
"Starting and Stopping Components Managed by Oracle Restart" for instructions for starting a database that is managed by Oracle Restart.

Parent topic: Starting Up an Instance

3.1.5.2 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 data access operations.

The following command starts an instance, reads the initialization parameters from the default location, and then mounts and opens the database.

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP	srvctl start database -db db_unique_name

where db_unique_name matches the DB_UNIQUE_NAME initialization parameter.

Parent topic: Starting Up an Instance

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

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP NOMOUNT	srvctl start database -db db_unique_name -startoption nomount

Parent topic: Starting Up an Instance

3.1.5.4 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:

Starting with Oracle Database 12c Release 1 (12.1.0.2), putting a database instance in force full database caching mode. For more information, see "Using Force Full Database Caching Mode".
Enabling and disabling redo log archiving options. For more information, see Managing Archived Redo Log Files.
Performing full database recovery. For more information, see Oracle Database Backup and Recovery User's Guide.

The following command starts an instance and mounts the database, but leaves the database closed:

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP MOUNT	srvctl start database -db db_unique_name -startoption mount

Parent topic: Starting Up an Instance

3.1.5.5 Restricting Access to an Instance at Startup

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users).

Use this mode of instance startup when you must accomplish one of the following tasks:

Perform an export or import of data
Perform a data load (with SQL*Loader)
Temporarily prevent typical users from using data
Perform certain migration or 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. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the system that the instance is running on.

The following command starts an instance (and mounts and opens the database) in restricted mode:

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP RESTRICT	srvctl start database -db db_unique_name -startoption restrict

You can use the restrict mode in combination with the mount, nomount, and open modes.

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 that you must restrict access, you can use the ALTER SYSTEM statement to do so, as described in "Restricting Access to an Open Database".

See Also:

Oracle Database SQL Language Reference for more information on the ALTER SYSTEM statement

Parent topic: Starting Up an Instance

3.1.5.6 Forcing an Instance to Start

In unusual circumstances, you might experience problems when attempting to start a database instance, and you can force a database instance to start.

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

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP FORCE	srvctl start database -db db_unique_name -startoption force

If an instance is running, the force mode shuts it down with mode ABORT before restarting it. In this case, the alert log shows the message "Shutting down instance (abort)" followed by "Starting ORACLE instance (normal)."

See Also:

"Shutting Down with the Abort Mode" to understand the side effects of aborting the current instance

Parent topic: Starting Up an Instance

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

If you know that media recovery is required, then you can start an instance, mount a database to the instance, and have the recovery process automatically start.

To do so, use one of these commands:

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP OPEN RECOVER	srvctl start database -db db_unique_name -startoption "open,recover"

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

Parent topic: Starting Up an Instance

3.1.5.8 Automatic Database Startup at Operating System Start

Many sites use procedures to enable automatic startup of one or more Oracle Database 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.

The preferred (and platform-independent) method of configuring automatic startup of a database is Oracle Restart. See Configuring Automatic Restart of an Oracle Database for details.

Parent topic: Starting Up an Instance

3.1.5.9 Starting Remote Instances

If your local Oracle Database server is part of a distributed database, then 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.

Parent topic: Starting Up an Instance

3.2 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.

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.
Opening a Closed Database
When a database is mounted but closed, you can make it available for general use by opening it.
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.
Restricting Access to an Open Database
When a database is in restricted mode, only users with the RESTRICTED SESSION privilege can initiate new connections. Users connecting as SYSDBA or connecting with the DBA role have this privilege.

Parent topic: Starting Up and Shutting Down

3.2.1 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;
```

See Also:

"Starting an Instance and Mounting a Database" for a list of operations that require the database to be mounted and closed (and procedures to start an instance and mount a database in one step)

Parent topic: Altering Database Availability

3.2.2 Opening a Closed Database

When a database is mounted but closed, you can make it available for general use by opening it.

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.

Parent topic: Altering Database Availability

3.2.3 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.

Note:

You cannot use the RESETLOGS clause with a READ ONLY clause.

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.

See Also:

Oracle Database SQL Language Reference for more information about the ALTER DATABASE statement

Parent topic: Altering Database Availability

3.2.4 Restricting Access to an Open Database

When a database is in restricted mode, only users with the RESTRICTED SESSION privilege can initiate new connections. Users connecting as SYSDBA or connecting with the DBA role have this privilege.

To place an already running instance in restricted mode:

Run the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause.

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.

See Also:

"Terminating Sessions" for directions for killing user sessions
"Restricting Access to an Instance at Startup" to learn some reasons for placing an instance in restricted mode

Parent topic: Altering Database Availability

3.3 Shutting Down a Database

You can shut down a database with SQL*Plus or Oracle Restart.

About Shutting Down the Database
When Oracle Restart is not in use, you can shut down a database instance with SQL*Plus by connecting as SYSOPER, SYSDBA, SYSBACKUP, or SYSDG and issuing the SHUTDOWN command. If your database is being managed by Oracle Restart, the recommended way to shut down the database is with the srvctl stop database command.
Shutting Down with the Normal Mode
When you shut down a database with the normal mode, the database waits for all connected users to disconnect before shutting down. Normal mode is the default mode of shutdown.
Shutting Down with the Immediate Mode
When you shut down a database with the immediate mode, Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back.
Shutting Down with the Transactional Mode
When you shut down a database with transactional mode, the database prevents users from starting new transactions, but waits for all current transactions to complete before shutting down. This mode can take a significant amount of time depending on the nature of the current transactions.
Shutting Down with the Abort Mode
You can shut down a database instantaneously by aborting the database instance.
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.

Parent topic: Starting Up and Shutting Down

3.3.1 About Shutting Down the Database

When Oracle Restart is not in use, you can shut down a database instance with SQL*Plus by connecting as SYSOPER, SYSDBA, SYSBACKUP, or SYSDG and issuing the SHUTDOWN command. If your database is being managed by Oracle Restart, the recommended way to shut down the database is with the srvctl stop database 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

Note:

You cannot shut down a database if you are connected to the database through a shared server process.

There are several modes for shutting down a database: normal, immediate, transactional, and abort. Some shutdown modes wait for certain events to occur (such as transactions completing or users disconnecting) before actually bringing down the database. There is a one-hour timeout period for these events.

See Also:

Configuring Automatic Restart of an Oracle Database for information about Oracle Restart.

Parent topic: Shutting Down a Database

3.3.2 Shutting Down with the Normal Mode

When you shut down a database with the normal mode, the database waits for all connected users to disconnect before shutting down. Normal mode is the default mode of shutdown.

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

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
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.

Parent topic: Shutting Down a Database

3.3.3 Shutting Down with the Immediate Mode

When you shut down a database with the immediate mode, Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back.

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:

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
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.

Parent topic: Shutting Down a Database

3.3.4 Shutting Down with the Transactional Mode

When you shut down a database with transactional mode, the database prevents users from starting new transactions, but waits for all current transactions to complete before shutting down. This mode can take a significant amount of time depending on the nature of the current transactions.

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

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
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.

Parent topic: Shutting Down a Database

3.3.5 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:

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
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.

Parent topic: Shutting Down a Database

3.3.6 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.

Parent topic: Shutting Down a Database

3.4 Quiescing a Database

A quiesced database allows only DBA transactions, queries, fetches, or PL/SQL statements.

About Quiescing a Database
Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a quiesced state, in the sense that no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements are running in the system.
Placing a Database into a Quiesced State
When you place a database in quiesced state, 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.
Restoring the System to Normal Operation
When you restore the system to normal operation, all non-DBA activity is allowed to proceed.
Viewing the Quiesce State of an Instance
You can view the quiesce state of an instance by querying the V$INSTANCE view.

Parent topic: Starting Up and Shutting Down

3.4.1 About Quiescing a Database

Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a quiesced state, in the sense that no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements are running in the system.

Note:

In this discussion of quiesce database, a DBA is defined as user SYS or SYSTEM. Other users, including those with the DBA role, are not allowed to issue the ALTER SYSTEM QUIESCE DATABASE statement or proceed after the database is quiesced.

The quiesced state lets administrators perform actions that cannot safely be done otherwise. These actions include:

Actions that 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 when the table is first exported, then dropped, and finally imported. A concurrent user who attempts to access the table after it was dropped, but before import, would not have an accurate view of the situation.

Without the ability to quiesce the database, you would need 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 a smaller restriction, because it eliminates the disruption to users and the downtime associated with shutting down and restarting the database.

When the database is in the quiesced state, it is through the facilities of the Database Resource Manager that non-DBA sessions are prevented from becoming active. Therefore, while this statement is in effect, any attempt to change the current resource plan will be queued until after the system is unquiesced. See Managing Resources with Oracle Database Resource Manager for more information about the Database Resource Manager.

Parent topic: Quiescing a Database

3.4.2 Placing a Database into a Quiesced State

When you place a database in quiesced state, 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.

To place a database into a quiesced state, issue the following SQL statement:
```
ALTER SYSTEM QUIESCE RESTRICTED;
```

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;

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.

Note:

You cannot perform a cold backup when the database is in the quiesced state, because Oracle Database background processes may still perform updates for internal purposes even while the database is quiesced. In addition, the file headers of online data files continue to appear to be accessible. They do not look the same as if a clean shutdown had been performed. However, you can still take online backups while the database is in a quiesced state.

See Also:

Oracle Database Reference for more information about the V$BLOCKING_QUIESCE view
Oracle Database Reference for more information about the V$SESSION view

Parent topic: Quiescing a Database

3.4.3 Restoring the System to Normal Operation

When you restore the system to normal operation, all non-DBA activity is allowed to proceed.

To restore the database to normal operation, issue the following SQL statement:
```
ALTER SYSTEM UNQUIESCE;
```

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.

Parent topic: Quiescing a Database

3.4.4 Viewing the Quiesce State of an Instance

You can view the quiesce state of an instance by querying the V$INSTANCE view.

To view the quiesce state of an instance:

Query the ACTIVE_STATE column of the V$INSTANCE view.

The column 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.

Parent topic: Quiescing a Database

3.5 Suspending and Resuming a Database

The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to data files (file header and file data) and control files. The suspended state lets you back up a database 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. Use the ALTER SYSTEM RESUME statement to resume normal database operations.

To suspend database operations:

Run the ALTER SYSTEM SUSPEND statement.

To resume database operations:

Run the ALTER SYSTEM RESUME statement.

The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when you issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, if a new instance is started while another instance is being suspended, then the new instance is not suspended.

The SUSPEND and RESUME commands can be issued 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 statement 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 cannot 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, because copies of a suspended database can contain uncommitted updates.

Note:

Do not use the ALTER SYSTEM SUSPEND statement as a substitute for placing a tablespace in hot backup mode. Precede any database suspend operation by an ALTER TABLESPACE BEGIN BACKUP statement.

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

See Also:

Oracle Database Backup and Recovery User's Guide for details about backing up a database using the database suspend/resume feature

Parent topic: Starting Up and Shutting Down

3.6 Delaying Instance Abort

The INSTANCE_ABORT_DELAY_TIME initialization parameter specifies the amount of time, in seconds, to delay shutting down a database when an error causes the instance to abort.

Some errors cause the Oracle database instance to abort. You can use the INSTANCE_ABORT_DELAY_TIME initialization parameter to specify the amount of time to delay shutting down the instance. A database administrator can use the delay time to get information about the error and minimize problems that can result when an instance aborts. For example, a database administrator might use the delay time to get diagnostics, redirect connections using Transparent Application Failover (TAF), and flush the buffer cache. A message is written to the alert log when a delayed abort is initiated.

Caution:

Do not set the INSTANCE_ABORT_DELAY_TIME value too high. Since the instance is closing because of an error, some processes or resources might be corrupted or unavailable, which can make complex actions impossible.

To delay instance abort:

Set the INSTANCE_ABORT_DELAY_TIME initialization parameter to the number of seconds to delay shutting down an instance when an error causes it to abort.

This parameter is set to 0 by default.

Example 3-1 Setting the INSTANCE_ABORT_DELAY_TIME Initialization Parameter

ALTER SYSTEM SET INSTANCE_ABORT_DELAY_TIME=60;

Parent topic: Starting Up and Shutting Down