Posted by: Monish | September 27, 2008

Startup & Shutdown

STARTUP

Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform typical data access operations.

Start an instance, read the initialization parameters from the default server parameter file location, and then mount and open the database by using the STARTUP command by itself (you can, of course, optionally specify the PFILE clause):

STARTUP NOMOUNT

You can start an instance without mounting a database. Typically, you can do so only during database creation. Use the STARTUP command with the NOMOUNT clause:

STARTUP MOUNT

You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. For example, the database must be mounted but not open during the following tasks:

  • Enabling and disabling redo log-archiving options.
  • Performing full database recovery.

Start an instance and mount the database, but leave it closed by using the STARTUP command with the MOUNT clause:

STARTUP FORCE

In unusual circumstances, you might experience problems when attempting to start a database instance. You should not force a database to start unless you are faced with the following:

  • You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.
  • You experience problems when starting an instance.

If one of these situations arises, you can usually solve the problem by starting a new instance (and optionally mounting and opening the database) using the STARTUP command with the FORCE clause:

STARTUP RESTRICT

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

  • Perform an export or import of database data
  • Perform a data load (with SQL*Loader)
  • Temporarily prevent typical users from using data
  • During certain migration and upgrade operations

Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.

Start an instance (and, optionally, mount and open the database) in restricted mode by using the STARTUP command with the RESTRICT clause:

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

STARTUP OPEN RECOVER

If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER clause.

=====================================================================

SHUTDOWN NORMAL

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:

Normal database shutdown proceeds with the following conditions:

  • No new connections are allowed after the statement is issued.
  • Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

The next startup of the database will not require any instance recovery procedures.

SHUTDOWN IMMEDIATE

Use immediate database shutdown only in the following situations:

  • To initiate an automated and unattended backup
  • When a power shutdown is going to occur soon
  • When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:

Immediate database shutdown proceeds with the following conditions:

  • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
  • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
  • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

SHUTDOWN TRANSACTIONAL

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:

Transactional database shutdown proceeds with the following conditions:

  • No new connections or new transactions allowed starting after the statement is issued.
  • After all transactions have completed, any client still connected to the instance is disconnected.
  • At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

SHUTDOWN ABORT

You can shut down a database instantaneously by aborting the database instance. If possible, perform this type of shutdown only in the following situations:

The database or one of its applications is functioning irregularly and none of the other types of shutdown works.

  • You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
  • You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:

An aborted database shutdown proceeds with the following conditions:

  • After issuing the statement, no new connection or transaction are allowed.
  • Current client SQL statements being processed by Oracle Database are immediately terminated.
  • Uncommitted transactions are not roll back.
  • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

>>> Please leave your comment on the above post..


Responses

  1. Good points . Really nice for better understanding with simple words. keep it up

  2. This is a good summary… but I have an extra few related questions:

    If you have already executed …
    SQL> Shutdown normal;

    which will wait forever, until users finish and disconnect,
    then how do you change/switch the style of shutdown to use a
    SQL> Shutdown immediate
    instead ?? They’re BOTH doing a shutdown, and it’s just a matter of whether the database waits or not… and as a DBA, I might want to speed up the
    process of doing a shutdown, without doing something drastic, like a SQL> Shutdown abort;

    I’m thinking of a syntax more like:
    SQL> alter system shutdown type=immediate;
    . . . (which is not real syntax, just example )
    I know that I could forcably kill every database session, and let the database shutdown after all user connections are gone; but I’d rather see a cleaner syntax.

    Now, if that is possible, here’s a slightly harder one:
    You are on a large production server, with several client & user apps, plus hundereds of users, when somebody issues a SQL> Shutdown Immediate;
    Oracle starts ending transactions, and performing rollbacks, but ti will be several minutes before the
    DB-sessions are terminated, and the database is actually shut down. Since I’m a DBA, I can still connect to the database with sqlplus. In the next 2 minutes, how do I issue a command to CANCEL an Oracle shutdown that’s already in progress, and tell the database to Stay UP, returning to normal “open” production status?? (so most of the applications don’t go down, & 75% of the users aren’t even affected??)

    There *is* a command of:
    SQL> Startup Force;
    But inside Oracle, it actually performs a faster
    ::: Shutdown Abort followed by a
    ::: Startup

    So, while the database does come up in 2 minutes, the Apps+users are cut off from the database, and have to re-connnect, or even re-start dozens of applicationsl

    For this, I just want to have Oracle *stop* what it’s doing, return to open/un-restricted status, and stay in regular production. I’m thinking some other
    command like ALTER SYSTEM, force open cancel shutdown;
    Alter system disable restricted session;
    ( or something like that )

    The desired effect iw that SGA stays in memory, most users/apps only pause for a bit, but never disconnect, the database is quickly back on-line, and running productively.

    So — if you fined these, let me know.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories

%d bloggers like this: