Home > HOME > ORACLE: NOMOUNT, MOUNT And OPEN MODES in Oracle

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
About these ads
Categories: HOME
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: