04 - Managing the Oracle Instance

4.0 Oracle Database Management Framework

There are three major components of the Oracle database management framework:

1. The Management Interface.
2. A listener that allows connection to the database. 
3. The database instance that is being managed.

Oracle Corporation’s recommended sequence for starting a database is to start the Management Interface, then the database listener, and then the database.

4.0.1  Management Interface

This may be either a management agent running on the database server which connects it to Oracle Enterprise Manager Grid Control or the stand-alone Oracle Enterprise Manager Database Control/Database Console. Database Control is a tool for managing one database (though this database can be clustered).

To start Database Control, use the emctl utility. This will be located in the ORACLE_HOME/bin directory. The three commands to start or stop Database Control and to check its status are:

emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

Figure below shows the startup of Database Control.

 

 4.0.2  Database Listener

The database listener is a process that monitors a port for database connection requests. These requests use Oracle Net, Oracle’s proprietary communications protocol. Oracle Net is a layered protocol running over whatever underlying network protocol is in use, probably TCP/IP.

There are two ways to start the database listener:

• With the lsnrctl utility
• With Database Control

The lsnrctl utility is in the $ORACLE_HOME/bin directory. The key commands are

lsnrctl start [listener]
lsnrctl status [listener]

where listener is the name of listener. This will have defaulted to LISTENER, which is correct in most cases. Figure below shows the startup of listener.

 

4.0.3  Database Instance

4.0.3.1  Database Startup

When you start up a database, you create an instance of that database and you determine the state of the database. In fact, one does not start or stop a database: an instance may be started and stopped; a database is mounted and opened, and then dismounted and closed.

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.

Database can be startup or shutdown from SQL*Plus, using the STARTUP and SHUTDOWN commands, or through Database Control.

If you want to override the default initialization parameter file with a text initialization parameter file, you can specify the PFILE clause of the STARTUP command to identify the initialization parameter file.

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

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.

•  Connect to Oracle Database as SYSDBA.Ordinary users cannot start up or shut down a database. This is because an ordinary user is authenticated against the data dictionary. It is logically impossible for an ordinary user to start up an instance and open or create a database, since the data dictionary cannot be read until the database is open.

         Dictionary authentication, anyone connecting with the above syntax cannot issue startup or shutdown
         commands.

         Instruct Oracle to go to the external password file to validate the username/password combination or

        Use OS authentication; Oracle will go to the host operating system and check whether the OS user 

        running SQL*Plus is a member of the OS group that owns the Oracle software.

Note: SYSDBA and SYSOPER are not users; they are privileges that can be granted to users. By default, only user SYS has these privileges until they are deliberately granted to other users.

At any moment, a database will be in one of the following states:

• SHUTDOWN
• NOMOUNT
• MOUNT
• OPEN
• RESTRICT

4.0.3.1.1  SHUTDOWN State

When the database is SHUTDOWN, all files are closed and the instance does not exist.

4.0.3.1.2  NOMOUNT State

In NOMOUNT mode, the instance has been started, but no connection has been made to a database. Starting an instance is typically includes the following tasks:

• Searching $ORACLE_HOME/dbs for a parameter file of a particular name in this order:
• spfile$ORACLE_SID.ora 
• If not found, spfile.ora 
• If not found, init$ORACLE_SID.ora

          Specifying the PFILE parameter with STARTUP overrides the default behavior.

• Allocating the SGA.
• Starting the background processes. 
• Opening the alert_$ORACLE_SID.log file and the trace files. The location of the alert log is given by the BACKGROUND_DUMP_DEST parameter.

An instance is typically started only in NOMOUNT mode during:

• Database Creation
• Re-creation of Control Files 
• Backup & Recovery Scenarios

Note: The only files used in NOMOUNT mode are the parameter file and the alert log. The parameters in the parameter file are used to build the SGA in memory and to start the background processes. Entries will be written out to the alert log describing this process.

The startup process with NOMOUNT mode is described graphically in the figure below.

 

The following figure shows the STRATUP command with NOMOUNT state.

 

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

 

4.0.3.1.3  MOUNT State

Once the instance is successfully started in NOMOUNT state, it may be transitioned to MOUNT state. Mounting a database includes the following tasks:

• Associating a database with a previously started instance
• Locating and opening the control files specified in the parameter file by using the CONTROL_FILES parameter
• Reading the control files to obtain the names and statuses of the data files and online redo log files. However, no checks are performed to verify the existence of the data files and online redo log files at this time.

An instance is typically started and mounts a database, but do not open it to perform specific maintenance operations. For example:

• Renaming data files (Data files for an offline tablespace can be renamed when the database is open)
• Enabling and disabling online redo log file archiving options
• Performing full database recovery

Note: A database may be left in MOUNT mode even though an OPEN request has been made. This may be because the database needs to be recovered in some way.

The startup process with MOUNT mode is described graphically in the figure below.

 

The following figure shows the STRATUP command with MOUNT state.

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

 

4.0.3.1.4  OPEN State

In OPEN state the database is in normal operation mode. Normal database operation means that any valid user can connect to the database and perform typical data access operations. Opening the database includes the following tasks:

• Opening the online data files
• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the database, then the Oracle server returns an error. The Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor (SMON) background process initiates instance recovery.

The overall startup process is described graphically in the figure below.

The following figure shows the STRATUP command which by default bring the database in open state.

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

4.0.3.1.5  RESTRICT State

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 need to 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

Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. 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 machine that the instance is running on. You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.

The following figure shows the STRATUP command with RESTRICT state.

 

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

 

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

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

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.

ALTER DATABASE MOUNT;

You can make a mounted but closed database available for general use by opening the database.

ALTER DATABASE OPEN;

Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes.

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read/write mode (the default) as follows:

ALTER DATABASE OPEN READ WRITE;

4.0.3.2  Database Shutdown

To shut down a database and instance, you must first connect as SYSOPER or SYSDBA. To initiate database shutdown, use the SQL*Plus SHUTDOWN command or on EM Database Control page, you can click the Shutdown button to shut down the database and instance. If you then click the Advanced Options button, you can select the mode of the shutdown.

There are several modes for shutting down a database. 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 command cancels with the following message:

ORA-01013: user requested cancel of current operation

Following are the different shutdown modes:

• NORMAL
• IMMEDIATE
• TRANSACTIONAL
• ABORT

4.0.3.2.1  NORMAL Mode


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

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.

Note: Typically, a normal shutdown is useless: there is always someone logged on, even if it is only the Database Control process.

4.0.3.2.2  IMMEDIATE Mode


It is the most typically used option. To shut down a database immediately, use the SHUTDOWN command 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

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 transaction exists, 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. The figure below shows the contents of the alert_$ORACLE_SID.log file when the database is shutdown immediately.

4.0.3.2.3  TRANSACTIONAL Mode

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

Transactional database shutdown proceeds with the following conditions:

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

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off. The next startup of the database will not require any instance recovery procedures.

4.0.3.2.4  ABORT Mode

You can shut down a database instantaneously by aborting the database instance. Use the SHUTDOWN command with the ABORT clause:

Use abort database shutdown only in the following situations:

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

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 instance recovery procedures.

4.0.3.3  Quiescing a Database

In quiesced state you want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. 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 24x7 availability.

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.

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

Non-DBA active sessions will continue until they become inactive. 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. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE and V$SESSION view.

SELECT bl.sid, user, osuser, type, program

FROM v$blocking_quiesce bl, v$session se

where bl.sid = se.sid;

You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance.

The following statement restores the database to normal operation:

Note: Here DBA is defined only as user SYS or SYSTEM. Also you cannot perform a cold backup when the database is in the quiesced state.

4.0.3.4  Suspending and Resuming a Database

Suspended state halts all I/O to datafiles and control files. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The suspend command 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.

The suspended state lets you back up a database without I/O interference.


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

The V$INSTANCE view can be queried to confirm database status.

The following statement restores the database to normal operation: