Deep understanding of Oracle database startup and shutdown

  • 2020-05-24 06:23:28
  • OfStack

The ORACLE tutorial you are looking at is: get a deep understanding of the startup and shutdown of the Oracle database.

The Oracle database provides several different ways to start and close a database, and this article details the differences between these ways and their different capabilities.

1. Start and close the Oracle database

For most Oracle DBA, the most common way to start and close the Oracle database is Server Manager at the command line. Since Oracle 8i, the system has centralized all the functions of Server Manager into SQL*Plus. That is to say, the database startup and shutdown from 8i can be completed directly through SQL*Plus instead of Server Manager. However, in order to maintain downward compatibility, the system still retains Server Manager tools. The system can also be started and shut down by the graphical user tool (GUI), Oracle Enterprise Manager. The graphical user interface, Instance Manager, is very simple and will not be detailed here.

To start and close the database, you must log in as a user with Oracle administrator privileges, usually SYSDBA. We usually use INTERNAL users to start and close the database (INTERNAL users are actually synonyms for SYS users connecting with SYSDBA). The new version of the Oracle database will phase out INTERNAL as an internal user, so we'd better set DBA users to have SYSDBA permissions.

2. Database startup (STARTUP)

Starting a database requires three steps:

1. Create an Oracle instance (non-installation phase)

2. Install the database by the instance (installation stage)

3. Database opening (opening stage)

In the Startup command, you can control different database startup steps with different options.

1, STARTUP NOMOUNT

The NONOUNT option creates only one instance of Oracle. Read the init.ora initialization parameter file, start the background process, and initialize the system global area (SGA). The Init.ora file defines the configuration of the instance, including the size of the memory structure and the number and type of background processes to start. The instance name is set to Oracle_SID and must be the same as the open database name. When the instance opens, the system displays a list of the memory structure and size of SGA, as shown below:


2, STARTUP MOUNT

This command creates the instance and installs the database, but does not open the database. The Oracle system reads the contents of the control file about the data file and the redo log file, but does not open the file. This opening is often used in database maintenance operations, such as renaming data files, changing the relog, and opening the archive. In this mode, in addition to seeing the list of SGA systems, the system will also give a "database is loaded" prompt.

3, STARTUP

This command completes all three steps to create an instance, install the instance, and open the database. At this point the database brings the data file and the redo log file online, usually with a request for one or more rollback segments. In addition to seeing all the previous hints in Startup Mount mode, the system will also give a "database is open" prompt. At this point, the database system is in a normal working state and can accept user requests.

If you use STARTUP NOMOUNT or STARTUP MOUNT's database open command, you must use the ALTER DATABASE command to open the database. For example, if you open a database with STARTUP NOMOUNT, that means the instance has been created, but the database is not installed and opened. You must run the following two commands to get the database started correctly.


If you start the database with STARTUP MOUNT, you only need to run the following command to open the database:


4. Other opening methods

In addition to the three database opening options described earlier, there are several other options.

(1) STARTUP RESTRICT

This way, the database will be opened successfully, but only a privileged user (who has the DBA role) will be allowed to use the database. This is often used for database maintenance, such as data import/export operations where you do not want other users to connect to the database to manipulate the data.

(2) STARTUP FORCE

This command is a combination of forcing the database shut down (shutdown abort) and starting the database (startup). This command is used only when a problem is encountered in closing the database and the database cannot be closed.

(3) ALTER DATABASE OPEN READ ONLY;

This command opens the database read-only after the instance is created and the database is installed. Product databases that provide only query functionality can be opened this way.

[NextPage]

3. Database shutdown (SHUTDOWN)

There are four different shutdown options for database shutdown, which are described 11 below.

1, SHUTDOWN NORMAL

This is the database shutdown SHUTDOWN command save option. In other words, if you issue a command like SHUTDOWN, that means SHUTDOWN NORNAL.

After issuing this command, any new connections will no longer be allowed to connect to the database. Before the database is closed, Oracle will wait for all users currently connected to exit the database before shutting down the database. Shutting down the database in this way does not require any instance recovery on the next startup. But one thing to note is that with this approach, it might take a few days to shut down a database, maybe longer.

2, SHUTDOWN IMMEDIATE

This is a common way we close the database, want to close the database quickly, but want to let the database clean shut down, often used this way.

The SQL statement currently being processed by Oracle is immediately broken and any uncommitted transactions in the system are rolled back. If there is a long uncommitted transaction in the system, it will also take a period of time (the transaction rollback time) to shut down the database in this manner. Instead of waiting for all users connected to the database to exit the system, the system forcibly rolls back all currently active transactions, and then disconnects all connected users.

3, SHUTDOWN TRANSACTIONAL

This option is only available after Oracle 8i. This command, which is often used to plan a shutdown of the database, causes the currently connected and active transaction to complete. After running this command, no new connection or transaction is allowed. After all active transactions are completed, the database will be shut down in the same manner as SHUTDOWN IMMEDIATE.

4, SHUTDOWN ABORT

This is the last move to close the database, but also in the case of no way to close the database only have to use the way, 1 do not use. Consider shutting down the database this way if one of the following situations occurs.

1. The database is in an abnormal working state, and cannot be closed with commands like shutdown normal or shutdown immediate;

2. The database needs to be closed immediately;

3. Encountered problems when starting the database instance;

All running SQL statements are immediately aborted. All uncommitted transactions will not be rolled back. Oracle also does not wait for users currently connected to the database to exit the system. The next time the database is started requires instance recovery, so the next startup may take longer than usual.

Table 1 clearly shows the differences and associations of the four different shutdown databases.

Table 1 Shutdown database compares tables in different ways


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

On 1 page


Related articles: