OracleManagement < CMS/AMS

Tags: view all tags

Startup and shutdown database

SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open;
SQL> shutdown immediate;

Preparing to Start Up an Instance

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

Ensure that environment variables are set so that you connect to the desired Oracle instance. For details, see "Selecting an Instance with Environment Variables".
Start SQL*Plus without connecting to the database:
```
SQLPLUS /NOLOG  
```
Connect to Oracle Database as SYSDBA:
```
CONNECT username/password AS SYSDBA 
```
Starting Up an Instance

You use the SQL*Plus STARTUP command to start up an Oracle Database instance. 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.
Force the instance to start after a startup or shutdown problem, or 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.

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. (You can optionally specify a PFILE 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 clause:

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:

Enabling and disabling redo log archiving options. For more information, please refer to Chapter 7, "Managing Archived Redo Logs".
Performing full database recovery. For more information, please refer to Oracle Database Backup and Recovery Basics

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

STARTUP MOUNT

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 the availability of a 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 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)

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

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.

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

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

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:

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. This timeout behavior is discussed in this additional section:

Shutdown Timeout

Shutting Down with the NORMAL Clause

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

SHUTDOWN NORMAL

The NORMAL clause is optional, because this is the default shutdown method if no clause is provided.

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 Clause

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

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