How to open physical standby database

How to open physical standby database in read only mode

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------

TSTDB     MOUNTED              PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.

ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.7917E+10 bytes
Fixed Size                  8636632 bytes
Variable Size            4294969128 bytes
Database Buffers         2.3555E+10 bytes
Redo Buffers               58470400 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> select * from t1;

D
-

X

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------

TSTDB     READ ONLY            PHYSICAL STANDBY

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0

Current log sequence           0

SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.7917E+10 bytes
Fixed Size                  8636632 bytes
Variable Size            4294969128 bytes
Database Buffers         2.3555E+10 bytes
Redo Buffers               58470400 bytes

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------

TSTDB     MOUNTED              PHYSICAL STANDBY

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        123          1        406
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        124         57          1
MRP0      APPLYING_LOG          1        124         57     409600

9 rows selected.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  2  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
  3    4    5    6  ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;  7

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------

         1                    123                   123

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)

--------------

           122