A deep understanding of the Oracle database startup and shutdown

First, the startup and shutdown of Oracle database

To start and shut down the database must be a user of the Oracle administrator privileges landing, usually that is, the the SYSDBA privileges of the user login. Generally we used the INTERNAL user to start and close the database (Internal user is actually a synonym for the SYS user with SYSDBA connection). The new version of the Oracle database will be phased out INTERNAL internal users, so we’d better set the DBA user with SYSDBA privileges.

Second, the start of the database (STARTUP)

Start a database requires three steps:

1, create an Oracle instance (non-installation phase)

2, installed on an instance of the database (installation phase)

3, open the database (open stage)

The Startup command can be controlled through the different options to different start-up procedure of the database.

1, startup nomount

NONOUNT option is just to create an Oracle instance. Read init.ora initialization parameter file, start a background process, initialize the System Global Area (SGA). The file defines Init.ora instance configuration, including the memory size of the structure and the number of starts a background process and type. Instance name according to the the ORACLE_SID set, not necessarily with the same name as the opened database. When the instance is opened, the system will display a list of a SGA memory structure and size, 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 the instance and install the database, but did not open the database. Oracle system reads the contents of the data files and redo log files in the control file, but it does not open the file. This open often in the database maintenance operations, such as renaming the data file, change the redo log and open archived. In this open way, except that you can see a list of the SGA system, the system is also given database completion of the loading tips.

3, STARTUP

Completion of the command to create an instance, to install instances and all three steps to open the database. The database data files and redo log files online, usually also request one or more rollback segments. The system in addition to can see front Startup Mount tips, but also give a “database is already open” prompt. At this point, the database system is in normal working condition, the user can accept the request.

If you open the database using the STARTUP NOMOUNT or STARTUP MOUNT command mode, you must use the ALTER DATABASE command to open the database to perform the operation. For example, if you STARTUP NOMOUNT way to open the database, which means that the instance has been created, but the database is not installed and open. This must run the following two commands, the database can not start correctly.

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

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

ALTER DATABASE OPEN.

4 Open

Open option in addition to the previously described three database, there is another option.

(1) STARTUP RESTRICT

This way, the database is successfully opened, but only allows some privileged users (DBA role user) before you can use the database. This is used to maintain the database, such as data import / export operations do not want other users to connect to the database operations data.

(2) STARTUP FORCE

In fact, the command is forced to close two commands an integrated database (shutdown abort) and start the database (startup). This command is only in Close the database encounter problems not close the database used.

(3) ALTER DATABASE OPEN READ ONLY;

The command to create an instance and install the database, open the database read-only. For those only product database query function can be used in this way open.

Third, the closure of the database (shutdown)

Close the database, there are four different off option, the following be introduced one by one.

1, SHUTDOWN NORMAL

This is indeed Province option database Close SHUTDOWN command. This means that if you issue the SHUTDOWN command, that is, the SHUTDOWN NORNAL mean.

After issuing this command, any new connection Jiangzai not allowed to connect to the database. Before closing the database, Oracle will wait for all users currently connected to exit from the database before starting to shut down the database. Close the database in this way, the next time you start does not require any instance recovery. But need to note that, in this way, may close a database takes a few days, maybe longer.

2, SHUTDOWN IMMEDIATE

This is commonly used as a way to close the database, to quickly shut down the database, but you want the database to clean off, often used in this way.

Currently being processed by Oracle SQL statement immediately interrupt system, any uncommitted transactions are rolled back. If your system has a very long uncommitted transactions, down this way, the database also takes time (the transaction is rolled back). The system does not wait for all users connected to the database to exit the system, forced to roll back the current active transaction, and then disconnect all connected users.

3, SHUTDOWN TRANSACTIONAL

This option is only after the Oracle 8i can be used. This command is used to plan to shut down the database, it is currently connected to the system and transaction execution activities are completed, run the command, any new connection and transaction are not allowed. In all activities after the transaction is completed, the database will shut down and the the SHUTDOWN IMMEDIATE same way the database.

4 SHUTDOWN ABORT

This is a last resort, close the database is also in the absence of any way to shut down the database had to use, generally do not use. If the following conditions can be considered in this way to close the database.

1, the database is in a non-normal operating conditions, shut down the database can not use this command shutdown normal or shutdown immediate;

2, immediately shut down the database;

3, encounter problems when the database instance is started;

All running SQL statements are aborted. All uncommitted transactions will be rolled back. Oracle does not wait for the connection to the database user to exit the system. Next time you start the database instance recovery required, so next time you start may need more time than usual.

Table 1 can clearly see the differences and connections of the four different close the database.

Table 1 Shutdown the database in different ways contrast table is closed AITN of
Allow new connection × × × × SHUTDOWN NORMAL
Wait until the current session is aborted × × × ? SHUTDOWN IMMEDIATE
Wait until the current transaction aborts × × ? ? SHUTDOWN TRANSACTIONAL
Mandatory CheckPoint, close all files × ? ? ? SHUTDOWN ABORT
Among them: the A-ABORT I-Immediate T-Transaction N-nornal