Manage Oracle database

The Oracle database system is composed of two parts: an instance and a database

The startup of the Oracle database is divided into steps, involving three states: instance startup, database loading and opening

Start the database and instance

Steps to start a database instance

  1. Create and start the instance corresponding to the database
  2. Load the database for the instance
  3. Set the database to open

Create and start the instance (nomount) corresponding to the database.
In nomount mode, you can do the following:

  1. Create database
  2. Rebuild the control file
  3. In nomount mode, only those views of the data dictionary related to the SGA area can be accessed

Mount the database for the instance (mount)
When the nomount phase is reached, the next startup phase is mount

通过alter database mount;来从nomount阶段或通过startup mount来到达这个阶段
  • In the mount phase, the database control file needs to be opened when loading the database, but the data file and redo log file cannot be read or written, so the user cannot operate the database
  • Maintenance work in mount mode includes:
  • Rename data file
  • Add, delete, and rename log files
  • Perform a full database recovery operation
  • Change the archive mode of the database

Set the database to open (open)

  • After the mount phase is over, all the information of the database system has been read
  • In the open phase, the database opens all online data files and redo log files based on the control file information. If any data file or redo log file in the control file cannot be opened normally, the database will fail to open, and database recovery is required
  • If the database has been started to the mount state, you can use the command: alter database open to open the database

Start mode


 - Startup nomount
 -  Startup mount
 -  Startup open
 -  Startup force
Startup force:当启动例程时碰到问题,可利用强制执行来强制启动一个没有关闭的或没有正常启动的数据库

Steps to close the database:

  • Close the database
  • Instance uninstall database
  • Finally terminate the instance
shutdown [normal | transactional | immediate | abort]

The following steps will be completed when the database routine is closed:

  • The first stage: ORACLE will write the contents of the execution buffer to the re-execution log file, write the changed data in the database buffer to the data file, then close the data file and the re-execution log file, the control file is still open, the database Cannot perform general access operations
  • The second stage: close the database, the routine begins to unload the database, close the control file, but the SGA memory and background support processes are still running
  • The third stage: the routine is closed, the SGA memory is released, and all background processes are terminated

Normal shutdown method (NORMAL):

  • If there is no limit on the time to close the database, the normal way is usually used to close the database. Using the SHTUDOWN statement with a NORMAL clause will shut down the database in the normal way
  • Normal: All users connected to the database must disconnect from the database before closing the database . No routine recovery process is required the next time the database is started. Remember to disconnect all database connections, otherwise the shutdown operation cannot be completed successfully.
SQL> shutdown normal
	 数据库已经关闭
	 已经卸载数据库
	 ORACLE 例程已经关闭

Immediately close method (IMMEDIATE):

  • Immediate mode can close the database in the shortest possible time. When closing the database in immediate mode, Oracle will perform the following operations:
  • Prevent any user from establishing a new connection, and at the same time prevent the currently connected user from starting any new transaction
  • Any uncommitted transactions are rolled back
  • Oracle no longer waits for the user to actively disconnect, but directly shuts down, uninstalls the database, and terminates the instance
SQL> shutdown immediate
	 数据库已经关闭
	 已经卸载数据库
	 ORACLE 例程已经关闭

Transaction closing method (TRABSACTIONL):

  • The transaction mode is between the normal shutdown mode and the immediate shutdown mode. It can close the database in the shortest possible time, but it can ensure that all current active transactions can be committed
  • When using the transaction mode to shut down the database, Oracle will wait for all uncommitted transactions to complete before shutting down the database
  • Transaction processing closure can prevent the client from losing work, while not requiring all users to log out
  • When closing the database in transaction mode, Oracle performs the following operations:
  • Prevent any user from establishing a new connection, and at the same time prevent the currently connected user from starting any new transaction
  • Wait for all uncommitted active transactions to be submitted, and then immediately disconnect the user
  • Shut down, uninstall the database, and terminate the instance

Termination of closing method (ABORT):

  • If none of the above three closing methods can close the database, it means that a serious error has occurred in the database. At this time, you can only use the termination method to close the database. Terminating the shutdown mode will lose part of the data information, which will damage the integrity of the database
  • Therefore, if the instance is restarted and the database is opened, the background process SMON will perform instance recovery
  • Under normal circumstances, you should try to avoid using this method to close the database
  • When using the shutdownabort command to shut down the database, Oracle will:
  • Prevent any user from establishing a new connection, and at the same time prevent the currently connected user from starting a new transaction
  • Submit the SQL statement currently being executed immediately
  • Any uncommitted transactions will not be rolled back
  • Disconnect all users immediately, close, uninstall the database, and terminate the instance