Repository navigation
38. Shutdown Types, Checkpoint, Alert Log
There are 4 types of Shutdown. Before learning them there is an extremely important thing to know.
Note: In industry never shutdown a database without written approval from the upper management (written official approval not verbal). Or else there are serious consequences for that like a huge penalty and loss of career.
Like show in the above diagram all of the options are done during normal shutdown other than waiting for a new connection.
I. Waiting for the current session to end: Yes
After writting the command normal shutdown won't occur until other sessions of the database isn't closed.
II. Waiting for the current transaction to end: Yes
After writting the command normal shutdown won't occur until all the transactions of the database are either committed or rollbacked.
III. Force Checkpoint: Yes
After writting the command normal shutdown trigger the full Checkpoint background process which will make sure the datafiles, the control file, the redolog files are in sync.
Like show in the above diagram only waiting for the transaction to end will and force Checkpoint will be done during transactional shutdown.
I. Waiting for the current session to end: No
II. Waiting for the current transaction to end: Yes
III. Force Checkpoint: Yes
Like show in the above diagram only force Checkpoint will be done during transactional shutdown.
I. Waiting for the current session to end: No
II. Waiting for the current transaction to end: No
III. Force Checkpoint: Yes
As shown in the command nothing is done during this shutdown.
I. Waiting for the current session to end: No
II. Waiting for the current transaction to end: No
III. Force Checkpoint: No
Doing this shutdown normally will just break the database. As during the startup process the database checks all the files, the data files, the control files and the redo log files. If it find them not in sync then the database will not start. The full Checkpoint process which runs as the database shuts down will make sure all the files remain in sync. As abort shutdown doesn't run the full Checkpoint background process this completely breaks the database.
This shutdown only is performed when any of the physical files are deleted. If that happens all other types of shutdown won't occur as all other shutdown will fire of full Checkpoint background process and when it sees any of the file it supposed to write the checkpoint information on, is missing it will show an error. So other shutdown won't be successful then. Abort shutdown will be used then to shutdown the database before recovery is done.
Note: Again in industry never do any kind of shutdown no matter normal, transactional or immediate or abort without the explicit written official approval from the business or higher management. This is extremely important as doing any of these without permission have dangerous consequences.
Checkpoint background process: When there is a new SCN number comes in the redo log file (only happens when any user commit a DML Transaction) then that SCN Number, Log Sequence Number, Archive Log Sequence Number all these number bundles together into something called checkpoint information. These checkpoint information is written in the control file and header of data file. In case of full checkpoint it also instructs DBWn to write all the committed dirty blocks in the data file.
All the activities that is happening the our database is stored in a file. Anything and everything starting from starting the database, then the queries run by all users including sysdba and all others, shutdowns all of them are stored in a physical file. That file is called Alert Log.
We can't always keep our eyes glued to the database right. So there is this alert log file. Information of all activity happening in the database is written in a file. Also information of the errors happening in the database are also written there (error is written in this format: ora-number. eg ora-12345). In industry this file is linked to the email of the support officer who receive a mail with the error id whenever there is a new error. The support person informs dba about the error.
The dba runs the grep command on the alert log file with the oracle error id to know the detail of the error and then proceeds to correct it.
/u01/app/oracle/diag/rdbms/prod/prod/trace/
Note: tail -number of linesf filename: Continuous display of the file. This command opens a file in continuous mode i,e it will update any new line being written in the file continuously.
tail -12 file.txt will open the last 12 lines of the file. Adding f to it: tail -12f file.txt will open it in continuous mode. The Alert log file is always being written continuously when the database is on. So open it in continuous mode to see the processes properly.
See this is continuous.
We will use the alert log to see how does different types of shutdown behaves differently.
I. Normal
Keeping a live session:
See I have an active session of hr user open. In normal shutdown see it is waiting for it to be disconnected. Looking at the alert log it is also stopped. Lets end the session and see what happens.
See the shutdown is complete.
Keeping an incomplete transaction: If the presence of an active session has stopped the normal shutdown it will also naturally stop at this as if there is no incomplete transaction without an active session.
II. Transactional
Keeping a live session:
See, even though there is an active session going the database still shuts down.
Keeping an incomplete transaction:
See this time there is an incomplete transactional. So the shutdown is waiting.
The moment the transaction gets completed the shutdown resumes and gets completed as well.
III. Immediate
Keeping a live session:
See there is an active session but it still shutdowns.
Keeping an incomplete transaction:
See there is an incomplete transaction but the shutdown still didn't wait and the database shuts down.
Abort Shutdown:
Lets try the abort shutdown but doing it would break our database. This is only an experiment as its our own database. But never try these with any industrial database unless its necessary and they have already given permission.
See in case of abort shutdown nothing happens. It just shutdown instantly.
