3
Starting Up and Shutting Down

This chapter describes the procedures for starting and stopping an Oracle database, and includes the following topics:

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 it and perform typical data access operations. However, there are other options and these are also discussed in this section.

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

Preparing to Start an Instance

You need to perform some preliminary steps before attempting to start an instance of your database .

Start SQL*Plus without connecting to the database by entering:
```
SQLPLUS
```
Connect to Oracle as SYSDBA:
```
CONNECT username/password AS sysdba
```
Note that you cannot be connected via a multi-threaded server.

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

See Also:
CONNECT, STARTUP, and SHUTDOWN are SQL*Plus commands. They are described, and their syntax is presented, in SQL*Plus User's Guide and Reference.

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. While three methods are mentioned, using SQL*Plus is the only method that is within the scope of this book.

Using SQL*Plus

To start up a database use SQL*Plus to connect to Oracle with administrator privileges (as shown previously) and then issue the STARTUP command. When you enter a STARTUP command, you can specify the database name and the full path of the initialization parameter file:

STARTUP database_name PFILE=myinit.ora

If you do not specify database_name, Oracle uses the value specified by the DB_NAME initialization parameter in the specified PFILE. The manner in which you specify the path for the initialization parameter file is operating system specific. If you do not specify the PFILE option, Oracle uses the default parameter file location, as specified in the Oracle installation guide for your operating system.

You can start an instance and database in a variety of ways:

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 recreation 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 restricted mode that allows access for database administrators only.

Note:
You cannot start a database instance if you are connected to the database via a multi-threaded server process.

In addition, you can force the instance to start, or start the instance and have complete media recovery begin immediately. If your operating system supports the Oracle Parallel Server (OPS), you may start an instance and mount the database in either exclusive or shared mode.

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 wish to bring up SQL*Plus. RMAN is not discussed in this book, but is the topic of Oracle8i Recovery Manager User's Guide and Reference.

Using Oracle Enterprise Manager

You can choose to use the Oracle Enterprise Manager for administering your database, including starting up and shutting 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 allows you to perform the functions discussed in this book using a GUI interface, rather than command lines.

See the following books to become familiar with the Oracle Enterprise Manager:

Starting an Instance: Scenarios

The following scenarios describe and illustrate the many ways in which you can start up an instance. For more information about the restrictions that apply when combining options of the STARTUP command, see the SQL*Plus User's Guide and Reference.

Note:
You may encounter problems starting up an instance if control files, database files, or redo log files 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 returns a warning message and does not mount the database. If one or more of the datafiles or redo log files is not available or cannot be opened when attempting to open a database, Oracle returns a warning message and does not open the database.

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:

Renaming datafiles, as described in Chapter 10, "Managing Datafiles".
Adding, dropping, or renaming redo log files, as described in Chapter 6, "Managing the Online Redo Log".
Enabling and disabling redo log archiving options, as described in Chapter 7, "Managing Archived Redo Logs".
Performing full database recovery. Database recovery is the topic of Oracle8i Backup and Recovery Guide and Oracle8i Recovery Manager User's Guide and Reference.

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

STARTUP MOUNT;

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 and then mount and open the database by using the STARTUP command by itself (this example uses the database name as specified by the DB_NAME initialization parameter in the standard PFILE):

STARTUP;

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 structure maintenance, such as rebuilding indexes
Perform an export or import of database data
Perform a data load (with SQL*Loader)
Temporarily prevent typical users from using data

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

See Also:
For more information on the ALTER SYSTEM statement, see the Oracle8i SQL Reference.

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. To understand the side effects of aborting the current instance, see "Shutting Down with the ABORT Option".

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.

Starting in Exclusive or Parallel Mode

If your Oracle server allows multiple instances to access a single database concurrently (Oracle Parallel Server option), choose whether to mount the database exclusively or in parallel. For example, to open in parallel mode you can issue:

STARTUP OPEN sales PFILE=initsale.ora PARALLEL;

Multiple instances can now access the database.

If you specify EXCLUSIVE (the default), then the database can only be mounted and opened by the current instance. The following statement starts an instance, mounts and opens the database named sales in exclusive mode, and restricts access to administrative personnel.

STARTUP OPEN sales PFILE=initsale.ora EXCLUSIVE RESTRICT;

See Also:
For more information about starting up in exclusive or parallel mode, see the Oracle8i Parallel Server Administration, Deployment, and Performance manual.

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 procedure topics, see your operating system-specific Oracle documentation.

Starting Remote Instances

If your local Oracle server is part of a distributed database, you might need 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

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.

When mounting the database, indicate whether to mount the database exclusively to this instance or concurrently to other instances.

To mount a database to a previously started instance, use the SQL statement ALTER DATABASE with the MOUNT option. Use the following statement when you want to mount a database in exclusive mode:

ALTER DATABASE MOUNT;

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), see "Starting an Instance and Mounting a Database".

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 "state" modifications that don't generate redo. For example, you can take datafiles offline or bring them online since these operations do not effect data content.

Ideally, you open a database read-only when you alternate a standby database between read-only and recovery mode; note 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;

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

See Also:
For more information about the ALTER DATABASE statement, see the Oracle8i SQL Reference.
For more conceptual details about opening a database in read-only mode, see Oracle8i Concepts.

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 might want to kill 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.

For reasons why you might want to place an instance in restricted mode, see "Restricting Access to a Database at Startup".

Shutting Down a Database

The following sections describe shutdown procedures:

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

Attention:
You cannot shut down a database if you are connected to the database via a multi-threaded server process.

To shut down a database and instance, first connect as SYSOPER or SYSDBA. Figure 3-1 shows the sequence of events when the different SHUTDOWN commands are entered during a transfer of funds from one bank account to another.

Figure 3-1 Sequence of Events During Different Types of SHUTDOWN.

Text description of the illustration starta.gif.

Shutting Down with the NORMAL Option

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.

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

 SHUTDOWN NORMAL;

Shutting Down with the IMMEDIATE Option

Use immediate database shutdown only in the following situations:

A power shutdown is going to occur soon.
The database or one of its applications is functioning irregularly.

Immediate database shutdown proceeds with the following conditions:

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.

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

SHUTDOWN IMMEDIATE;

Note:
The SHUTDOWN IMMEDIATE statement disconnects all existing idle connections and shuts down the database. If, however, you have submitted processes (for example, inserts, selects or updates) that are awaiting results, the SHUTDOWN TRANSACTIONAL statement allows the process to complete before disconnecting.

Shutting Down with the TRANSACTIONAL Option

When you wish 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;

After submitting this statement, no client can start a new transaction on this instance. If clients attempt to start a new transaction, they are disconnected. 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 neither 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.

Aborting an instance shuts down a database and yields the following results:

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.

If both the normal and immediate shutdown options do not work, abort the current database instance immediately by issuing the SHUTDOWN command with the ABORT option:

SHUTDOWN ABORT;

Suspending and Resuming a Database

The ALTER SYSTEM SUSPEND statement suspends a database by halting all 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 becomes suspended all preexisting I/O operations will complete and any new database accesses will be in a queued state.

The suspend command suspends the database, and is not specific to an instance. Therefore, in an OPS 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. Note that 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. For details about backing up a database using the database suspend/resume feature, see Oracle8i Backup and Recovery Guide.

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

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

Using Initialization Parameter Files

The following sections include information about how to use initialization parameter files:

To start an instance, Oracle must read an initialization parameter file, which is a text file containing a list of instance configuration parameters. Often, although not always, this file is named INIT.ORA or INITsid.ORA, where sid is operating system specific.

Note:
If you are using Oracle Enterprise Manager, see the Oracle Enterprise Manager Administrator's Guide for information about using stored configurations as an alternative to the initialization parameter file.

You can edit parameter values in a parameter file with a basic text editor; however, editing methods are operating system specific. Oracle treats string literals defined for National Language Support (NLS) parameters in the file as if they are in the database character set.

See Also:
For more information about initialization parameter files specific to your installation, see your operating system-specific Oracle documentation.
A description for every initialization parameter is contained in the Oracle8i Reference.

The Sample Initialization Parameter File

A sample parameter file (INIT.ORA or INITsid.ORA) is included in the Oracle distribution set. This sample file's parameters are adequate for initial installations of an Oracle database. After your system is operating and you have some experience with Oracle, you will probably want to change some parameter values.

See Also:
For more information about optimizing a database's performance using the initialization parameter file, see Oracle8i Designing and Tuning for Performance.

The Number of Initialization Parameter Files

Each Oracle database has at least one initialization parameter file that corresponds only to that database. This way, database-specific parameters such as DB_NAME and CONTROL_FILES in a given file always pertain to a particular database. It is also possible to have several different initialization parameter files for a single database. For example, you can have several different parameter files for a single database so you can optimize the database's performance in different situations.

The Location of the Initialization Parameter File in Distributed Environments

The client you use to access the database must be able to read a database's initialization parameter file to start a database's instance. Therefore, always store a database's parameter file on the computer executing the client.

In non-distributed processing installations, the same computer executes Oracle and the client. This computer already has the parameter file stored on one of its disk drives. In distributed processing installations, however, local client workstations can administer a database stored on a remote machine. In this type of configuration, the local client machines must each store a copy of the parameter file for the corresponding databases.

See Also:
For more information about using administering Oracle in a distributed environment, see Oracle8i Distributed Database Systems.

3 Starting Up and Shutting Down