- 21 Nov, 2021
- 0 Comments
- 4 Mins Read
Startup and shutdown sequence of oracle database
STARTUP/SHUTDOWN SEQUENCE OF ORACLE DATABASE
1. To check the database is up or down...
ps -ef |grep pmon
2. To check how many databases are on the server, either up or down...
cat /etc/oratab
3. To check the status of the instance...[Status can be either in NOMOUNT, MOUNT or OPEN]
sqlplus / as sysdba select status from v$instance;
4. Different ways you can shutdown an Oracle database...
- A) Shutdown (or Shutdown normal);
- Blocks All connections immediately;
- Wait for all connections to logout gracefully
- Once all the connections are disconnected, database is marked as “shutdown completed”.
- B) Shutdown transactional;
- Blocks All connections immediately
- Kills all connections that are “Select” only.
- Wait for all DML (Delete, Update, Insert) transactions to end. Meaning? User has to either commit or rollback before the transaction is marked as completed.
- Once all the DML transactions ends, database is marked as “shutdown completed”.
- C) Shutdown immediate;
- Blocks All connections immediately
- Kills all connections.
- Select are killed immediately;
- Insert, Update and Deletes are rolled back to previous state.
- D) Shutdown abort;
- Kills the PMON process, hence database is not functional.
- All connected “select only” queries are aborted and will be cleaned up by PMON process.
- All DML transactions (transactions which were doing insert, update or delete) will be consider as “In-Doubt Transactions” during the next database started. At this point, SMON process will perform rollback or rollforward to mark the transaction complete.
5. How to startup the database so that users can read and write to database?
export ORACLE_SID=DatabaseName (or) . oraenv sqlplus / as sysdba startup
6. When “startup” is issued, what stages instance goes through before starting the database?
0 | Closed |
1 | nomount |
2 | mount |
3 | open |
Describe each stage.
nomount :-
- Oracle assigns a small memory to the named instance.
- Oracle Verifies initialization parameter file is available in $ORACLE_HOME/dbs location.
- Default initialization parameter file in 12c and 19c is spfile
- If spfile is not found, init$ORACLE.SID.ora will be referred to start the database.
- Once the initialization parameter is finalized (spfile or pfile), Oracle will check the accuracy of each parameter inside the initialization parameter file. Verification process looks for the following:
- All locations which are given to parameter are available on OS level. If the location is not valid, an error is trigged and recorded in alert log.
- All numeric values should be valid. If they are not valid numers, an error is trigged and recorded in alert log.
- Errors are also raised when typos are found. These errors are again recorded in alert log.
- Listener is notified that the instance. However, the service is marked as “Blocked”
Once all of the above passes, instance status is moved to next step which is “mount” stage
mount :-
- Oracle reads the initialization parameter file for the location of controlfile.
- Controlfile keeps record of the following:
- The database name
- Tablespace Names
- Names and locations of associated datafiles
- Names and locations of redo log files
- The timestamp of the database creation
- The current log sequence number → we will cover this backup and recovery
- Checkpoint information → we will cover this later.
- Backup and Recovery Information. → we will cover this backup and recovery
- Archivelog Information. → we will cover this backup and recovery
- Verifies all multiplexed controlfils are same in size. (checksum). If not, error is raised and recorded in alert log.
- Listener Service status is changed to “Ready”, however the dispatcher is still not allocated to the listener.
Once all of the above check passes, instance is moved to next stage of opening the database
open :-
- Listener requests are given a dispatcher to allow the traffic to connect to the database for I/O.
- Database status is finally changed to open
7. Different ways of shutting down database
Shutdown = shutdown normal
shutdown immediate;
shutdown transactional;
shutdown abort;
8. Different stages of starting up database
startup nomount; ==> requires spfile or pfile
alter database mount; ==> requires controlfiles
alter database open; ==> requires datafiles and redologs
9. If I start the database in mount stage, how can I open the database?
startup mount;
alter database open;
10. What is the difference between spfile and init.ora file? (Also called spfile and pfile).
PFile | SPFile |
|
|
11. To verify if the database is started from spfile, type the following.
show parameter spfile
If you see the path of spfile, database is started with spfile.
If you see the path is null (emtpy), database is started with pfile [init<DBName>.ora]
Example:
12. To create init.ora (pfile)
create pfile=/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora
Example:
create pfile='/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora' from spfile;
13. To create spfile.ora (spfile)
create spfile='/data/app/oracle/product/19C/dbhome_3/dbs/spfileTESTDB.ora' from pfile;
If the instance is already started with spfile, then you will get an error like below:
14. To start the database from pfile instead of spfile [Assuming the DB is down and pfile is already created.
startup pfile='/data/app/oracle/product/19C/d bhome_3/dbs/spfileTESTDB.ora'