Enable and disable Oracle databases in SQL * Plus

Close Database

As with database startup, there are several options available to close the database. In whatever circumstances, readers need to figure out these close options

The syntax for shutting down a database is SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT].

Note: If you do not disable OracleDBControlSID in windows Service, shutdown or shutdown normal will not be used to close the database, but other options have response results.

1. NORMAL (NORMAL) Options

The shutdown and shutdown normal functions the same. Using the shutdown statement with the normal option will close the database normally. When the normal option is used, Oracle performs the following operations:

1. Prevent any user from establishing new connections

2. Wait for all users that are currently establishing a connection to actively disconnect. Connected users can continue their current work and even commit new transactions.

3. Once all users are disconnected, the database can be closed, detached, and the routine can be terminated.

Note: If you close the database with this option, you do not need to perform any restoration operations when starting the database next time.

Ii. TRANSACTIONAL (Transaction Processing) Options

The transactional option is a little more active than the normal option. It can shut down the database as short as possible. When you close the database by using the transactional option, Oracle will close the database after all the uncommitted transactions are completed. When you use the transactional option to close the database, oracle will perform the following operations:

1. Stop any user from establishing new connections and stop the current connected user from starting any new transactions.

2. Wait until all the uncommitted active transactions are committed, and disconnect the user immediately.

3. Once all users are disconnected, the database is closed, detached, and the routine is terminated.

Note: If you close the database with this option, you do not need to perform any restoration operations when starting the database next time.

3. IMMEDIATE (IMMEDIATE) Option

This option is usually used to close the database in the following situations:

1. Automatic Database Backup will be started soon

2. Power supply is about to be interrupted

3. An exception occurs in the database itself or a database application, and the user cannot be notified to take the initiative to disconnect the connection at this time, or the database user cannot define the disconnection operation.

When you use the IMMEDIATE option to shut down a database, Oracle performs the following operations:

1. Stop any user from establishing new connections and stop the current connected user from starting any new transactions.

2. Any uncommitted transactions are returned.

3. Oracle does not wait for the user to disconnect, but directly closes or uninstalls the database and terminates the routine.

Note: If you close the database with this option, you do not need to perform any restoration operations when starting the database next time.

4. ABORT Option

You can use the abort option to close a database in the following situations.

1. The database itself or a database application has an exception and other options are invalid.

2. Shut down the database immediately in case of an emergency

3. An error occurs when the data warehouse routine is started.

When you use the abort option to close the database, Oracle will perform the following operations:

1. Stop any user from establishing new connections and stop the current connected user from starting any new transactions.

2. immediately end the SQL statement being executed

3. No uncommitted transactions will be rolled back.

4. Disconnect all users immediately, close and unmount the database, and terminate the routine.

Note: If you use this option to close the database, some data information may be lost and the database integrity may be damaged because the current unfinished transactions are not rolled back, you need to recover the database the next time you start the database.

Start Database

The database has three startup modes, which represent the three steps for starting the database. When the database administrator uses the startup command, you can specify different options to decide which startup mode to push the database. After entering a certain mode, you can use the alter database command to promote the database to a higher startup mode, but cannot lower the database to the previous startup mode.

Startup Mode	Description	Corresponding interface prompt information
NOMOUNT	Starts the routine, but does not load the database, that is, only the first step of the startup step is completed.	Oracle routine started
MOUNT	Start the routine, load the database, but do not open the database, that is, only the first and second steps of the Start step are completed	Oracle routine started Database loaded
OPEN	Start the routine, load the database, open the database, and complete all three steps	Oracle routine started Database loaded The database has been opened.

The database STARTUP syntax is: STARTUP [NOMOUNT | MOUNT | OPEN | FORSE] [RESTRICT] [PFILE = 'pfile _ name'];

I. NOMOUNT Option

This option only creates a routine, but is not installed in the database. Oracle reads parameter files. It only creates various Memory Structures and background service processes for routines. Users can communicate with the database, but cannot use any files in the database.

To perform the following maintenance tasks, you must use this option to start the database:

1. Run a script for creating a new database.

2. Recreate the control file

Note: In this startup mode, you can only access the data dictionary views related to the SGA zone. The information of these views is obtained from the SGA zone and whether the database is loaded, whether to enable or not.

Ii. MOUNT options

This option not only creates a course, but also loads a database, but does not open the database. Oracle reads the control file and obtains information about the physical structure of the database, such as the database name, data file location, and name, to prepare for the next step to open the database.

In this mode, only the database administrator can use some commands to modify the database. the user cannot establish a connection or session with the database. This is necessary for some specific database maintenance work.

To perform the following maintenance, you must use this option to start the database:

1. Rename, add, delete data files, and redo log files

2. Perform full database recovery

3. Change the database archiving Mode

Note: In this mode, in addition to accessing the data dictionary views related to the SGA area, you can also access the data dictionary views related to the control file, the view information is obtained from the control file.

3. OPEN Options

This option not only creates a routine, but also loads the database and opens the database. This is the normal startup mode. If the startup statement does not specify any options, you can use this option to perform the German startup.

After the database is set to open, any user with the create session permission can connect to the database and perform regular data access operations.

4. FORSE options

If you encounter difficulties when starting the database in normal mode, you can use the FORSE option to start the database. The difference between the FORSE option and the normal start option is that you can use this option no matter what mode the database is in. That is, the FORSE option first closes the abnormal database and then restarts it, you do not need to use the shutdown statement to shut down the database in advance.

V. RESTRICT options

When you use this option to start a database, the database is started to open mode. However, only users with restricted session permission can access the database. If you want to execute maintenance tasks in the open mode of the database and ensure that other users cannot establish connections or execute tasks on the database, you need to use the restrict option to open the database, to complete the following tasks:

1. Import or export database data

2. Execute Database loading (using SQL * Loader)

3. temporarily stop normal users from using data

4. Port or upgrade the database

After the work is completed, you can use the alter system disable restrict session Statement to disable the restricted session so that users can connect to and use the database.

6. PFILE options

The data library routine must read an initialization parameter file at startup, and oracle must obtain the parameter configuration information about the routine from the initialization parameter file. If the pfile option is not specified when the startup statement is executed, oracle will first read the default server initialization parameter file (spfile). If the default server initialization parameter file is not found, oracle will continue to read the default text initialization parameter file (pfile). If it does not reach the text initialization parameter file, it will fail to start.

When using the startup statement, you can use the pfile option to specify a text initialization parameter file.