ORACLE: NOMOUNT, MOUNT And OPEN MODES in Oracle
Lets take a look at the different OPEN_MODES in Oracle and Analyse what happens at every step.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT PFILE=C:\oracle\product\10.2.0\admin\HARRY\pfile\init.ora.5182011155654
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1291916 bytes
Variable Size 167774580 bytes
Database Buffers 415236096 bytes
Redo Buffers 7094272 bytes
In NOMOUNT MODE we create 1. DB's and 2. CONTROLFILES.
SQL> DESC V$INSTANCE
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(12)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(15)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)
BLOCKED VARCHAR2(3)
SQL> SELECT HOST_NAME,INSTANCE_NAME,VERSION FROM V$INSTANCE;
HOST_NAME INSTANCE_NAME VERSION
---------------------------------------------------------------- ---------------- -----------------
TEST-PC harry 10.2.0.3.0
SQL> DESC V$CONTROLFILE;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
SQL> DESC V$database
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)
SQL> SELECT * FROM V$CONTROLFILE;
no rows selected
SQL> SELECT * FROM V$DATABASE;
SELECT * FROM V$DATABASE
*
ERROR at line 1:
ORA-01507: database not mounted
So, now in nomount mode, the database SGA and Background process have been initialized only. Only the Instance is created, however, the database can not be queried as it is not initialized, as well as, the CONTROL FILES HAVE YET NOT BEEN READ, although they are present in the INIT.ORA file. They will be read in the Next Mode.
SQL> ALTER DATABASE MOUNT;
Database altered.
SQL> SELECT NAME, OPEN_MODE, LOG_MODE FROM V$DATABASE;
NAME OPEN_MODE LOG_MODE
-------------------- ---------- ------------
HARRY MOUNTED ARCHIVELOG
SQL> SELECT * FROM V$CONTROLFILE;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------- --- ---------- --------------
C:\HARRY\HARRY\CONTR NO 16384 430
OL01.CTL
C:\HARRY\HARRY\CONTR NO 16384 430
OL02.CTL
C:\HARRY\HARRY\CONTR NO 16384 430
OL03.CTL
Now that the Database is MOUNTED, Oracle can read the Control files and knows where all the datafiles are present.
Control file has pointers that provide connection b/w Datafiles, Redo Logs, Archived Logs, Memory and Last Updated SCN.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 23 52428800 1 YES INACTIVE 959048 23-JUN-11
3 1 25 52428800 1 NO CURRENT 1033570 25-JUN-11
2 1 24 52428800 1 YES INACTIVE 996350 24-JUN-11
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------- ---------------------
3 ONLINE C:\HARRY\HARRY\REDO03.LOG NO
2 STALE ONLINE C:\HARRY\HARRY\REDO02.LOG NO
1 ONLINE C:\HARRY\HARRY\REDO01.LOG NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
So from here, we can see that the Database in Mount Mode has all the required parameters initialized,
however the database itself is still not open to users.
MOUNT MODE IS WHERE MAINTENANCE WORK IS DONE.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry OPEN
SQL> SELECT NAME, OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
-------------------- ----------
HARRY READ WRITE
Now the database is open for use.
IN A NUTSHELL:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1291916 bytes
Variable Size 289409396 bytes
Database Buffers 293601280 bytes
Redo Buffers 7094272 bytes
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry STARTED
SQL> alter database mount
2 ;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry MOUNTED
SQL> alter database open;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry OPEN
Categories: HOME
Comments (0)
Trackbacks (0)
Leave a comment
Trackback
Sharma
Over 10 years of rich and diversified experience in IT Support and Infrastructure Management and Support encompassing various process streams of Oracle Database Administration/Support Performance Tuning. In depth expertise on performing gap assessment, identifying areas of improvements and Designing and implementing the delivery for global and local clients and operational excellence.
>
Visitors to this site
oracleEdge 