It is a DBA task to startup and shutdown of the Oracle instance / Database. It is important to understand the options used.
To start up or shut down appropriate privileges are needed. Two special connection accounts / authorizations are available for startup and shutdown: SYSDBA or SYSOPER.
The SYSDBA authorization allows to perform any database task. The SYSOPER authorization is a less powerful authorization that allows startup and shutdown abilities but restricts other administrative tasks, such as access to non-administrative schema objects.
These authorizations are managed either through a passwords file or via operating-system control.
When a database is initially installed, only the SYS schema can connect to the database with the SYSDBA authorization. You can grant this authorization and the SYSOPER authorization to give others the ability to perform these tasks without connecting as the SYS user.
Oracle 10g Database Startup: The Oracle instance is composed of a set of logical memory structures and background processes.
When Oracle is started, these memory structures and background processes are initialized and started so that users can communicate with the Oracle database.
Whenever an Oracle database is started, it goes through a series of steps to ensure database consistency. When it starts up, a database passes through three modes:
NOMOUNT, MOUNT, and OPEN.
STARTUP NOMOUNT starts the instance without mounting the database. When a database is started in this mode, the parameter file is read and the background processes and memory structures are initiated, but they are not attached or communicating with the disk structures of the database. When the instance is in this state, the database is not available for use.
If a database is started in NOMOUNT mode, you can perform certain tasks. One of the most common is to run a script that creates the underlying database.
At times, a database may not be able to go to the next mode (called MOUNT mode) and remains in NOMOUNT mode. For example, this can occur if Oracle has a problem accessing the control file structures, which contain important information to continue with the startup process. If these structures are damaged or not available, the database startup process cannot continue until the problem is resolved.
The STARTUP MOUNT option performs all the work of the STARTUP NOMOUNT option but also attaches and interacts with the database structures. At this point, Oracle obtains information from the control files that it uses to locate and attach to the main database structures.
Certain administrative tasks can be performed while the database is in this mode, for example, recovery. You can also physically change file locations or place the database in archive log mode.
The STARTUP OPEN option is the default startup mode if no mode is specified on the STARTUP command line. STARTUP OPEN performs all the steps of the STARTUP NOMOUNT and STARTUP MOUNT options. This option makes the database available to all users.
Although you typically use the STARTUP NOMOUNT, STARTUP MOUNT, and STARTUP OPEN options, a few other startup options are available that you can use in certain situations: STARTUP FORCE and STARTUP RESTRICT. These are discussed next.
You can use the STARTUP FORCE startup option if you are experiencing difficulty starting the database in a normal fashion. For example, if a database server lost power and the database stopped abruptly, it can leave the database in a state in which a STARTUP FORCE startup is necessary. This type of startup should not normally be required but can be used if a normal startup does not work. What is also different about STARTUP FORCE is that it can issued no matter what mode the database is in. STARTUP FORCE does a shutdown abort and then restarts the database.
The STARTUP RESTRICT option starts up the database and places it in OPEN mode, but gives access only to users who have the RESTRICTED SESSION privilege. You might want to open a database using the RESTRICTED option when you want to perform maintenance on the database while it is open but ensure that users cannot connect and perform work on the database. You might also want to open the database using the RESTRICTED option to perform database exports or imports and guarantee that no users are accessing the system during these activities. After you are done with your work, you can disable the restricted session, ALTER SYSTEM DISABLE RESTRICTED SESSION, so everyone can connect to the database.
When you invoke the Enterprise Manager console, you are notified that the database instance is down.
Click the Startup button located on the Database Control screen to open the Startup/Shutdown: Specify Host And Target Database Credentials .
On this screen, supply an operating system username and password and an Oracle user ID and password that has either the SYSDBA or SYSOPER account authentication.
After you enter the appropriate user ID and password information, click OK to open the Startup/Shutdown: Confirmation screen.
Click Yes to continue, No to cancel, or Advanced Options to select advanced startup options.
From Advanced Options, the type of startup needed can be selected (NOMOUNT, MOUNT, or OPEN)
Choose the parameter file to use.
Click OK to return the previous screen. By default, Oracle starts with the OPEN option and uses the default initialization file.
Click Show SQL to see the actual startup command that will be executed.
After choosing the type of startup, click Yes .
You will be presented with a screen indicating that the database is being started.
If Oracle does not encounter any problems (start up involves background recovery of database if not properly shut down earlier or if there had been any failure before closing the database previously.
It is notified that the database is now open and available.
Starting Oracle Using SQL*Plus
Connect to SQL*Plus as a user with SYSOPER or SYSDBA privileges.
The syntax is as follows:
STARTUP [NOMOUNT|MOUNT|OPEN] [PFILE/SPFILE=] [RESTRICT]
SQL*Plus Startup Command Examples
|STARTUP NOMOUNT pfile=/u01/oracle/init.ora||Start up Oracle in NOMOUNT mode using a non-default parameter file|
|STARTUP MOUNT||Start up Oracle in MOUNT mode using a default SPFILE or PFILE|
|STARTUP OPEN||Start up Oracle in OPEN mode using a default SPFILE or PFILE|
|STARTUP RESTRICT||Start up Oracle in OPEN mode and allow only users with restricted session privileges to connect to the database|
|STARTUP FORCE||Force database startup using the default PFILE or SPFILE|
|STARTUP OPEN PFILE=/u01/sp01.ora||Start up Oracle in OPEN mode using a nondefault parameter file|
Example of how to use the STARTUP FORCE command with a nondefault
To start up an Oracle database using SQL*Plus:
D:\oracle\ora10g>sqlplus "/ as sysdba"
SQL> startup force pfile=d:\oracle\ora10g\initORCL1.ora
If you are running Oracle on Windows, you can also start the database when you start the associated Oracle service. Starting the Oracle service automatically starts the Oracle database.
Shutting Down an Oracle 10g Database: (Understanding shut down options)
SHUTDOWN NORMAL This is the default type of shutdown that Oracle performs if no shutdown option is specified.
No new Oracle connections are allowed from the time the SHUTDOWN NORMAL command is issued.
The database will wait until all users are disconnected to proceed with the shutdown process.
This type of shutdown is also known as a "clean" shutdown because when Oracle is started again, no recovery is necessary.
SHUTDOWN TRANSACTIONAL No new connections or new transactions are allowed from the time the SHUTDOWN TRANSACTIONAL command is issued.
Once all active transactions on the database have completed, all client connections are disconnected.
A transactional shutdown does allow User processes to complete prior to the disconnection. This can prevent a User from losing work. Useful when long running transactions that need to be completed prior to shutdown. This type of shutdown is also a clean shutdown and does not require any recovery on a subsequent startup.
SHUTDOWN IMMEDIATE No new Oracle connections are allowed. Any uncommitted transactions are rolled back. Thus, a user in the middle of a transaction will lose all the uncommitted work. Oracle does not wait for clients to disconnect. Any unfinished transactions are rolled back, and their database connections are terminated.
Even though Oracle is forcing transactions to roll back and disconnecting users, an immediate shutdown is still considered a clean shutdown. No recovery activity takes place when Oracle is subsequently restarted.
SHUTDOWN ABORT No new Oracle connections are allowed. Any SQL statements currently in progress are terminated, regardless of their state. Uncommitted work is not rolled back. Oracle disconnects all client connections immediately upon the issuance of the SHUTDOWN ABORT command.
Do not use SHUTDOWN ABORT regularly unless other options for database shutdown fail or if you are experiencing some type of database problem that is preventing Oracle from performing a clean shutdown.
This type of shutdown is not a clean shutdown and requires recovery when the database is subsequently started.
Shutting Down Oracle Using EM Database Control: Invoke the EM Database Control from web browser. Click the Shutdown button in the General section.
After you click Shutdown, you are presented with the Startup/Shutdown: Specify Host and Target Database Credentials screen. Supply an OS user ID and password to log into the target database machine.
After you authenticate, the Startup/Shutdown: Confirmation screen is displayed.
The default shutdown selected when you are using the EM Database Control is SHUTDOWN IMMEDIATE.
Oracle also displays the current status of the database on this form.
To perform a nondefault type of shutdown, click the Advanced Options button . In the Startup/Shutdown: Advanced Shutdown Options screen select the type of shutdown. After selecting the the type of shutdown, click OK , and then click Yes in the Confirmation screen to open a screen informing you that the database shutdown is in progress.