A deep understanding of Oracle database startup and shutdown

Sep 16 posted in Oracle Database (RSS), 212 views.

Oracle Database offers several different database startup and shutdown mode, this article details of these startup and shutdown mode, as well as the difference between their different functions.

1, start and shutdown Oracle Database

For most Oracle DBA for startup and shutdown Oracle database, the most common way is the command line mode, the Server Manager. From the Oracle 8i later, the system will all the features of Server Manager are concentrated to a SQL * Plus, the means for the future from the 8i database startup and shutdown can be directly through the SQL * Plus to complete, rather than require an additional Server Manager, but the the system in order to maintain backwards compatibility, still retained the Server Manager tool. There is also available through the graphical user tool (GUI), Oracle Enterprise Manager to complete the system startup and shutdown, Instance Manager graphical user interface is very simple and not go into details here.

To start and shutdown database, Oracle must have administrator privileges to a user login, usually also based on a user login with the SYSDBA privileges. INTERNAL general we used to start and close the database user (INTERNAL user SYS is actually a synonym for users to connect SYSDBA). The new version of the Oracle database will be gradually phased out INTERNAL this internal users, so we better set the DBA user has SYSDBA privileges.

Second, the database startup (STARTUP)

Start a database requires three steps:

1, create an Oracle instance (non-installation stage)
2, from the database instance installation (installation stage)
3, open the database (open stage)

In the Startup command, you can through different options to control the different databases to start steps.

1, STARTUP NOMOUNT

NONOUNT option to just create an Oracle instance. Read init.ora initialization parameter file, start the background process, initializing the system global area (SGA). Init.ora file defines the configuration of instances, including the size of the memory structure and start a background process the number and types. Instance name is set according to Oracle_SID do not have to open the database with the same name. When the instance opens, the system will display an SGA memory structure and size of the list, as follows:

SQL> startup nomount
ORACLE instance started.
Total System Global Area 35431692 bytes
Fixed Size 70924 bytes
Variable Size 18505728 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes

2, STARTUP MOUNT

This command creates a database instance and installed, but did not open the database. Oracle control file on the system to read data files and redo log file contents, but does not open the file. This open approach often used in the database maintenance operations, such as data file name change, change redo logs, as well as open archiving methods. In this Open, in addition to the list can be seen outside the SGA system, the system will be given "database has finished loading" prompt.

3, STARTUP

This command creates an instance of complete, install the database instance and open to all three steps. At this point the database to make data files and redo log files online, and usually one or more rollback request. In addition to this time the system can be seen in front of Startup Mount mode all the tips, it will also give a "database already open" prompt. At this point, the database system is in normal working state, can accept user requests.

If we adopt the STARTUP NOMOUNT or STARTUP MOUNT the database, open a command mode, you must use ALTER DATABASE command to perform open a database operation. For example, if you open the database in order to STARTUP NOMOUNT, meaning that an instance has been created, but the database is not installed and open. This is a must run the following two commands, the database in order to start properly.

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

And if STARTUP MOUNT way to start the database, only need to run the following command that can open a database:

ALTER DATABASE OPEN.

4, other Open

In addition to the three databases described above Open option, there are also some other options.

(1) STARTUP RESTRICT

In this way, the database will be successfully opened, but only to allow some privileged users (with DBA role user) can use the database. This approach is often used for database maintenance, such as data import / export operations do not want other users to connect to the database to manipulate data.

(2) STARTUP FORCE

This command is forced to close the database (shutdown abort) and start the database (startup) 2 command a comprehensive. This command has encountered a problem only in the closure of the database can not close the database used.

(3) ALTER DATABASE OPEN READ ONLY;

This command creates an instance and installing a database, in order to open the database read-only. For those who simply provide access to product database functionality can be used in this way opens.

Third, the closure of the database (SHUTDOWN)

For the closure of the database, there are four different closure options, the following description of its conduct 11.

1, SHUTDOWN NORMAL

This is the province of the database is indeed close the SHUTDOWN command options. This means that if you send SHUTDOWN command, that is, SHUTDOWN NORNAL meaning.

Issuing this command, any new connections are a further not allowed to connect to the database. Before closing the database, Oracle will wait for all users currently connected to withdraw from the database until after the closure of the database. In this way the database off the next startup does not require any instance recovery. But the point to note is that this approach may turn off a database needs to be a few days, maybe longer.

2, SHUTDOWN IMMEDIATE

This is commonly used as a way to shut down the database, would like to quickly shut down the database, but want a clean closure of the database, often using this approach.

Oracle is currently being processed SQL statement immediately interrupted, the system did not submit any rollback of the transaction total. If the system exists in a very long uncommitted transaction, using this approach also takes time to shut down the database (the transaction rollback time). System does not wait for a connection to the database to all users exit the system, forced to roll back all of the current active transaction, and then disconnect all the connections the user.

3, SHUTDOWN TRANSACTIONAL

This option is only available after Oracle 8i can be used. This command is often used to plan to close the database, which allows to connect to the system and are currently active transaction is finished, run this command, any new connections and services are not allowed. In all activities of the transaction is completed, the database will SHUTDOWN IMMEDIATE the same way close the database.

4, SHUTDOWN ABORT

This is a last resort shut down the database, but also there is no way to shut down the database in the circumstances that forced the approach, generally not to use. If the following occurs in this way could be considered close the database.

1, the database is in a non-normal working state, can not shutdown normal or shutdown immediate so ordered the closure of the database;

2, need for immediate closure of the database;

3, start the database instance encountered problems;

All are running SQL statement that will be immediately suspended. All uncommitted transactions will not be rolled back. Oracle does not wait for users currently connected to the database exit the system. The next time you start the database instance recovery is required, so the next time you start may take more time than usual.

Table 1 can clearly see the difference between four different closure of the database and contacts.

Table 1 Shutdown the database in different ways comparative table

Close method AITN
To allow new connections × × × ×
To wait until the current session the suspension × × × √
To wait until the current transaction suspension × × √ √
Forced CheckPoint, close all files × √ √ √

Where: A-Abort I-Immediate T-Transaction N-Nornal