Shulou(Shulou.com)06/01 Report--

To understand the start and stop of an Oracle database, you need to understand the definitions of the terms "instance" and "database":

Database (database): collection of physical operating system files or disks (disk).

Instance (instance): a set of Oracle background processes / threads and a shared memory area that is shared by threads / processes running on the same computer.

The two words can sometimes be used interchangeably, but the concepts of the two are completely different. The relationship between the instance and the database is that the database can be composed of multiple instances mount and open, while the instance can mount and open a database at any point in time.

Oracle System Identifier (SID)

SID is the only name of the Oracle instance on the server. On UNIX and Linux machines, Oracle uses SID and Oracle home values to create shared memory keys, that is, SID and Oracle home specify an instance, and SID is also used to locate parameter files.

With the above concepts in mind, let's take a look at the startup and shutdown process of the Oracle database.

1. Startup of Oracle instance and database

There are many ways to start the Oracle database, and the easiest way to start the Oracle database is to use sqlplus to execute the startup command.

First, let's take a look at the official picture:

From the figure above, you can see that the library Oracle goes through the following stages from shutdown state to open state:

1) start the instance without mount database

The instance is started, but the database is not associated with it. The corresponding command is startup nomount.

Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)

Reads the parameter file to determine the values of initialization parameters

Allocates the SGA based on the initialization parameter settings

Starts the Oracle background processes

Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax

2) the database is mount

The instance is started, and the control file of the database is opened to associate with a database. The database is still close to the user. The right order is alter database mount.

To mount the database, the instance obtains the names of the database control files specified in the CONTROL_FILES initialization parameter and opens the files. Oracle Database reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.

In a mounted database, the database is closed and accessible only to database administrators. Administrators can keep the database closed while completing specific maintenance operations. However, the database is not available for normal operations.

3) the database is open

The instance is started and the associated database is open. The data in the database can be accessed by users. The corresponding command is alter database open.

Opens the online data files in tablespaces other than undo tablespaces

If a tablespace was offline when the database was previously shut down (see "Online and Offline Tablespaces"), then the tablespace and its corresponding data files will be offline when the database reopens.

Acquires an undo tablespace

If multiple undo tablespaces exists, then the UNDO_TABLESPACE initialization parameter designates the undo tablespace to use. If this parameter is not set, then the first available undo tablespace is chosen.

Opens the online redo log files

2. Shutdown of Oracle instances and databases

Usually close the Oracle database and use sqlplus to execute the shutdown command

Let's look at the official picture:

You can also see from the above figure that there are three stages from the database open state to the shutdown state:

1) the database is closed

The database is still in mount state, but the online data files and log files are closed.

The database close operation is implicit in a database shutdown. The nature of the operation depends on whether the database shutdown is normal or abnormal.

When a database is closed as part of a SHUTDOWN with any option other than ABORT, Oracle Database writes data in the SGA to the data files and online redo log files. Next, the database closes online data files and online redo log files. Any offline data files of offline tablespaces have been closed already. When the database reopens, any tablespace that was offline remains offline.

At this stage, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed.

If a SHUTDOWN ABORT or abnormal termination occurs, then the instance of an open database closes and shuts down the database instantaneously. Oracle Database does not write data in the buffers of the SGA to the data files and redo log files. The subsequent reopening of the database requires instance recovery, which Oracle Database performs automatically.

2) the database is umount

The instance is started, but is no longer associated with the database through the control file.

After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. At this point, the instance remains in memory.

3) the instance is shutdown

The instance is shutdown.

The final step in database shutdown is shutting down the instance. When the database instance is shut down, the SGA is removed from memory and the background processes are terminated.

There are four modes of database shutdown: ABORT, IMMEDIATE, TRANSACTIONAL, NORMAL. The following table describes the behavior of the database in each mode.

Database BehaviorABORTIMMEDIATETRANSACTIONALNORMAL

Permits new user connections

No

No

No

No

Waits until current sessions end

No

No

No

Yes

Waits until current transactions end

No

No

Yes

Yes

Performs a checkpoint and closes open files

No

Yes

Yes

Yes

SHUTDOWN ABORT

This mode is intended for emergency situations, such as when no other form of shutdown is successful. This mode of shutdown is the fastest. However, a subsequent open of this database may take substantially longer because instance recovery must be performed to make the data files consistent.

Note:

Because SHUTDOWN ABORT does not checkpoint the open data files, instance recovery is necessary before the database can reopen. The other shutdown modes do not require instance recovery before the database can reopen.

SHUTDOWN IMMEDIATE

This mode is typically the fastest next to SHUTDOWN ABORT. Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back.

SHUTDOWN TRANSACTIONAL

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

SHUTDOWN NORMAL

This is the default mode of shutdown. The database waits for all connected users to disconnect before shutting down.

The following example demonstrates the startup and shutdown process of the Oracle database, in which the Oracle version is 11.2.0.1

1. Start the database

Currently there are no processes or shared memory. Set the ORACLE_SID and ORACLE_HOME environment variables.

Execute sqlplus / as sysdba to connect to an empty instance, there is still no shared memory, only a process oracletest has been added

Start the database instance with startup nomount, which by default looks for the spfile parameter file to start the instance, or you can use the startup nomount pfile='/dir/init.ora' to specify the parameter file to start, allocate shared memory in memory and create background processes.

Check the current instance status. You can only check a small number of views, such as v$instance, but most views cannot be queried, such as v$database and v$datafile. An error will be reported: ORA-01507: database not mounted

Use the alter database mount command mount database, this state can only query part of the view, most of the views at the beginning of dba can not be queried will report an error: ORA-01219: database not open: queries allowed on fixed tables/views only

Use the alter database open command to open the database:

The current database is opened and services can be provided.

2. Close the database

The entire startup and shutdown process is recorded in the alert log file. The alert log directory of 11g is $ORACLE_BASE/diag/rdbms/dbname/sid/trace. The file name is alert_sid.log.

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e40540/startup.htm#CNCPT89043

"the Art of 9I10G11G programming goes deep into database architecture"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.