Oracle Database Shutdown Process (4/4)

Oracle Database Shutdown Process (1/4)
Oracle Database Shutdown Process (2/4)
Oracle Database Shutdown Process (3/4)
Oracle Database Shutdown Process (4/4) [You are here]


SHUTDOWN ABORT

This is very nasty way of shutdown Oracle, you should not perform SHUTDOWN ABORT unless is necessary. What happen with SHUTDOWN ABORT is like you power off your computer without notice to anyone. Very fast way of shutdown, when you startup the system there will be database recovery needed, that is because when you startup database all the datafiles are still "open" state, you will only can close all datafiles properly with above 3 shutdowns (NORMAL, TRANSACTIONAL, IMMEDIATE).

1. Again we have active connected session




2. SHUTDOWN ABORT command is issued



As you notice SHUTDOWN ABORT command is not going through the 3 steps of shutdown sequence, it shutdown the system is fast because it skipped 2 sequences, what SHUTDOWN ABORT did was kill all process and release the SGA.

You must not perform SHUTDOWN ABORT unless is necessary.

Oracle Database Shutdown Process (1/4)
Oracle Database Shutdown Process (2/4)
Oracle Database Shutdown Process (3/4)
Oracle Database Shutdown Process (4/4) [You are here]

Oracle Database Shutdown Process (3/4)

Oracle Database Shutdown Process (1/4)
Oracle Database Shutdown Process (2/4)
Oracle Database Shutdown Process (3/4) [You are here]
Oracle Database Shutdown Process (4/4)

SHUTDOWN IMMEDIATE

In my opinion this is
the most commonly use shutdown command. If this command is issued, Oracle will undo all active transactions, no new connection is allowed, all connected sessions will be disconnected and Oracle will start performing shutdown sequence.

1. We have connected session with active transaction.

2. When SHUTDOWN IMMEDIATE is issued, the connected session is disconnected, perform rollback (discarded) to all uncommitted changes and close database as write committed changes to datafiles and continue the rest of the sequence.

3. As result the connected session is lost connection.

The uncommitted record is discarded.

Oracle Database Shutdown Process (1/4)
Oracle Database Shutdown Process (2/4)
Oracle Database Shutdown Process (3/4) [You are here]
Oracle Database Shutdown Process (4/4)

Oracle Database Shutdown Process (2/4)

Oracle Database Shutdown Process (1/4)
Oracle Database Shutdown Process (2/4) [You are here]
Oracle Database Shutdown Process (3/4)
Oracle Database Shutdown Process (4/4)

SHUTDOWN TRANSACTIONAL

If there are any other session is in progress of making changes (transactions), shutdown transactional will wait for sessions with active transactions to be completed before shutdown the database. After the transaction is complete the session will be disconnected. Once you issued SHUTDOWN TRANSACTIONAL there will be no new transaction is allowed.


1. There is a session that try to insert a record in to round_table

INSERT INTO round_table VALUES ('A');

The user is not performing commit (to confirm change) or rollback (to undo change)

2. Database Administrator executing SHUTDOWN TRANSACTIONAL



The SHUTDOWN TRANSACTIONAL appear to be hang, because it waited for other session to complete the active transaction.

3. The active transaction is completed the transaction by issue COMMIT command.

When the all active transaction have been finalize (can be commit or rollback) the SHUTDOWN TRANSACTIONAL will continue the shutdown sequence.

Oracle Database Shutdown Process (1/4)
Oracle Database Shutdown Process (2/4) [You are here]
Oracle Database Shutdown Process (3/4)
Oracle Database Shutdown Process (4/4)

Oracle Database Shutdown Process (1/4)

Oracle Database Shutdown Process (1/4) [You are here]
Oracle Database Shutdown Process (2/4)
Oracle Database Shutdown Process (3/4)
Oracle Database Shutdown Process (4/4)

Oracle database shutdown process is reverse process of Oracle database startup, therefore there are three steps.

1. Close
2. Unmount
3. Shutdown instance

Close
Oracle database writes all confirm (commit, Oracle have command call commit, this mean to confirm the changes) changes that still in SGA (memory) into the datafiles so that the changes is permanent and available for next database open. The uncommited changes (transactions) will be discarded. Therefore this process can take quite long to complete if you have database configured with big SGA and you have alot of changes need to be discarded or written into datafiles.
You may ask what happen if there is power failure and I have huge amount of unsaved changes stored in my huge SGA, will my data lose?
The answer is no, don't worry about that, Oracle is having each of the changes (Oracle using SCN to uniquely identify all transaction, SCN stand for System Change Number) stored in redo log files in your disk (which will be archived if you set it correctly). With that we will able to recover everything.

Unmount
During the unmount, Oracle is disassociate all files with the processes and memory.

Shutdown instance
Oracle clear the SGA and terminate all Oracle process. After this process you will no longer have Oracle database instance process running. Well you may still have Oracle Listener process running which is that is not part of this.


There are four types of shutdown command.

SHUTDOWN NORMAL (this is default type, if you key in command SHUTDOWN without mention any other word then Oracle will perform SHUTDOWN NORMAL)

Shutdown normal, Oracle will wait for all connected user to manually logout from
Oracle session then it will start performing shutdown. So if you have other connected session this command may seems like hang but actually your SHUTDOWN command is waiting the other user to logout before performing the shutdown. Once you issue this command there will be no new login is allowed.

1. We have 1 connected session.

2. Try to perform SHUTDOWN NORMAL

Notice that Shutdown is hanging waiting for all connected sessions to logout.

3. Logout session the connected session and then shutdown sequence is continue.

Oracle Database Shutdown Process (1/4) [You are here]
Oracle Database Shutdown Process (2/4)
Oracle Database Shutdown Process (3/4)
Oracle Database Shutdown Process (4/4)

Oracle Database Startup Process (2/2)

Oracle Database Startup Process (1/2)
Oracle Database Startup Process (2/2) [You are Here]


5. Once you are at mounted state you will able to query selected Oracle management view, in this case we can see the state of the database.
The command is

SELECT name, open_mode FROM v$database;

6. Now we going to upgrade the state to open state. The command is ALTER DATABASE OPEN;

7. Again to see the state of the database we can use the query from step 5. SELECT name, open_mode FROM v$database;

As you can see that I am now at READ WRITE, that is mean I am Open READ WRITE. As I mentioned earlier there are three types of open state mode, READ WRITE, READ ONLY and RESTRICTED.

8. Above all are the stage of Oracle database startup, you don't have to jump from one state to another, I just demonstrate to you to show you different type of startup stage.
Normally you just need to issue command STARTUP to startup the database at open stage.

Now I am shutdown my database and perform normal startup.

As you can see in order for the Oracle database to go to OPEN state Oracle will go through 3 stages, Nomount, Mounted then Open. (I drew 1, 2, 3)

So STARTUP command will perform all three stages.

In short how to startup Oracle database.

1. login as oracle account
sqlplus "/as sysdba"
2. at SQL prompt
startup


Please note:

Once the database at higher stage it will not be able to go back to lower stage, for example from mount state to nomount state, or from open state to mount or to nomount state, the only way to go to lower state is by shutdown the database and startup again.


Oracle Database Startup Process (1/2)
Oracle Database Startup Process (2/2) [You are Here]

Oracle Database Startup Process (1/2)

Oracle Database Startup Process (1/2) [You are Here]
Oracle Database Startup Process (2/2)

There are three stages Oracle will going through to startup the Database instance.
First is Nomount, then it goes to Mount and finally Open.

As end user of the database you will always want your database to be at open stage.

Started database at Nomount stage is typically, you do so only during database creation, when you want to recreate your Oracle Control Files. (Control Files is an important file part of your database)

Started database at Mount stage is needed during the time we perform specific database maintenance (like during backup and recovery, renaming datafiles and many more).

Started database at Open stage is a normal operating operating condition, well, there are three types of Open Mode,
1. Open Read Write (Normal Operation, which you can update the database records)
2. Open Read Only (You are able to query the database records no update operation is allowed, this can for static database or standby database (Oracle Data Guard, will discuss this further).
3. Open Restricted, that's mean no one can access the database, the database is only available for those with administrative privileges, you would want to have Open Restricted mode during maintenance, to prevent other end user without administrative privileges to make any changes while you are working.

You need to have sysdba privilege to startup shutdown database.

1. Connect as sysdba

sqlplus "/as sysdba"

2. in the SQLPLUS prompt type following command to startup nomount

startup nomount

As you can see the Total System Global Area and everything below is the Oracle memory which know as SGA.
SGA and Oracle processes are created when your database at nomount stage.

3. The way to check if the system is at nomount stage you can see the process is running or not. You can use following Linux command.

ps -ef |grep smon

I have to exit from SQLPLUS to the Linux prompt, with SQLPLUS command exit to exit.

You can see there is line in my example
oracle 6854 1 0 22:59 ? 00:00:00 ora_smon_edba01

we see one ora_smon_edba01 process running, please note that 6854 is my process id assigned by my Linux OS, for you case most probably is different number.
smon is system monitor, oracle instance have 1 smon process running whenever instance running. Please note some dba may use pmon (process monitor) instead of smon, is all the same, our intention is to see that we have oracle process running.
edba01 is my Oracle SID (system identification), if you see more than one smon_[your SID] that's mean you have multiple Oracle instance running.

4. Now I login back to Oracle SQLplus and will alter database to mount stage by using command ALTER DATABASE MOUNT;

Oracle Database Startup Process (1/2) [You are Here]
Oracle Database Startup Process (2/2)