Starting and shutting down an Oracle DB instance

To start and shut down a database, you must log in as a user with Oracle Administrator privileges, typically logged in as a user with SYSDBA permissions. Generally we use the SYS user to start and close the database with a SYSDBA connection. Here are the differences between the different database startup and shutdown methods of the Oracle database and their different capabilities.
1 Starting the database (startup)

In the startup command, different options are available to control the different startup steps of the database.

1, STARTUP Nomount

The Nonount option simply creates an Oracle instance. Reads the Init.ora initialization parameter file, initiates a background process, initializes the system global Zone (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 that are started. The instance name is set according to Oracle_sid and does not have to be the same as the open database name. When the instance is open, the system displays a list of the structure and size of the SGA memory 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

The command creates an 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 files. This open mode is often used in database maintenance operations, such as renaming a data file, changing the redo log and opening the archive, and executing the database recovery. In this open mode, in addition to seeing the list of SGA systems, the system also gives "Database mounted." "Prompt.

3. STARTUP

This command completes all three steps to create an instance, install an instance, and open a database. At this point the database makes the data file and the redo log file online, and typically requests one or more rollback segments. In addition to the system can see the front startup mount mode of all the hints, but also give a "Database opened." "Prompt. At this point, the database system is in a normal working state and can accept user requests.

If you use the startup Nomount or startup Mount database to open a command, you must use the ALTER DATABASE command to perform the Open database operation. For example, if you open the database as startup Nomount, that is, the instance has been created, but the database is not installed and opened. The following two commands must be run for the database to start correctly.
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

If you start the database in startup mount, you can open the database by running only the following command:
ALTER DATABASE OPEN;

4. Other Open mode

In addition to the three types of database open options described earlier, there are other options.

(1) STARTUP RESTRICT

In this way, the database will be opened successfully, but only some privileged users (users with DBA roles) are allowed to use the database. This approach is often used to maintain the database, such as data import/export operations when you do not want other users to connect to the database operation data, data loading, specific migration or upgrade operations.

(2) STARTUP force

This command is actually a synthesis of forcibly shutting down the database (shutdown abort) and starting the database (startup) two commands. This command is only used when the database is closed and the database is not closed.

(3) ALTER DATABASE OPEN READ only;

This command opens the database as read-only after the instance is created and the database is installed. A product database that only provides query functionality can be opened in this way.

2 Shutdown of the database (SHUTDOWN)

There are four different options for shutting down the database.

1. SHUTDOWN NORMAL

This is the database shutdown Shutdown command does save the option. In other words, if you enter a command such as shutdown, the shutdown nornal command is executed.

When this command is issued, any new connections will no longer be allowed to connect to the database. Before the database shuts down, Oracle waits for all users who are currently connected to exit from the database before shutting down the database. By shutting down the database in this way, no instance recovery is required at the next boot. However, it is important to note that in this way, it may take a few days or longer to close a database.

2, SHUTDOWN IMMEDIATE

This is a common way to close the database, want to close the database quickly, but also want to make the database cleanly shut down, often in this way.
The SQL statement currently being processed by Oracle is immediately interrupted, and any transactions that are not committed in the system are rolled back. If a long uncommitted transaction exists in the system, it can take a while for the database to be shut down in this manner (the transaction is rolled back time). The system does not wait for all users connected to the database to exit the system, forcibly rollback all currently active transactions, and then disconnects all connected users.

3, SHUTDOWN transactional

This option is only available after Oracle 8i. This command is commonly used to schedule a shutdown of the database, which causes the current connection to the system to execute and the active transaction is completed, and any new connections and transactions are disallowed after the command is run. After all active transactions are complete, the database shuts down the database in the same way as shutdown immediate.

4, SHUTDOWN ABORT

This is the last resort to close the database, but also in the absence of any way to close the database in the case of the way to be used, generally do not use. You might consider shutting down the database this way if the following conditions occur.

1, the database is in an abnormal working state, can not be used shutdown normal or shutdown immediate such a command to close the database;
2, need to close the database immediately;
3, when starting the database instance encountered problems;

All running SQL statements will be aborted immediately. All uncommitted transactions will not be rolled back. Oracle also does not wait for users who are currently connected to the database to exit the system. The next time you start a database, you need to perform an instance recovery, so the next boot may take longer than usual.

The following table distinguishes and links the four different shut-down databases.
Off mode Abort Immediate Transaction nornal
Allow new connection to XXXX
Wait until the current session aborts Xxx√
Wait until the current transaction aborts xx√√
Force checkpoint, close all files x√√√

User: System Password: System User: sys password: SYS login to add as Sysdba

Two can be added as SYSDBA is the highest level of authority. But SYS must be added. System can be added without additional

Starting and shutting down an Oracle DB instance