I'm Top Oracle DBA

This article shows how to open a database on Physical Standby (Read only with Apply or Active Data Guard)

On Physical Standby

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch
Oldest online log sequence     64
Next log sequence to archive   0
Current log sequence           64
SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;

      DBID NAME      LOG_MODE     OPEN_MODE            SWITCHOVER# DATABASE_ROLE    SWITCHOVER_STATUS    DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
 468941731 DB11G     ARCHIVELOG   MOUNTED                472560866 PHYSICAL STANDBY NOT ALLOWED          DISABLED NONE    DB11G_STDBY

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

MAX(SEQUENCE#)
--------------
            63

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> select process from v$managed_standby;

PROCESS
---------
ARCH
ARCH
RFS
RFS
RFS
MRP0

6 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only ;

Database altered.

SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME from v$database;

      DBID NAME      LOG_MODE     OPEN_MODE            SWITCHOVER# DATABASE_ROLE    SWITCHOVER_STATUS    DATAGUAR GUARD_S DB_UNIQUE_NAME
---------- --------- ------------ -------------------- ----------- ---------------- -------------------- -------- ------- ------------------------------
 468941731 DB11G     ARCHIVELOG   READ ONLY              472560866 PHYSICAL STANDBY NOT ALLOWED          DISABLED NONE    DB11G_STDBY

SQL> select process from v$managed_standby;                          

PROCESS
---------
ARCH
ARCH
RFS
RFS
RFS

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

Database altered.

SQL> select process from v$managed_standby;         

PROCESS
---------
ARCH
ARCH
RFS
RFS
RFS
MRP0

6 rows selected.

***Finished***

Test switch log

On Primary

SQL> alter system switch logfile ;

System altered.

SQL> 
SQL> 
SQL> 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch
Oldest online log sequence     63
Next log sequence to archive   65
Current log sequence           65

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

MAX(SEQUENCE#)
--------------
            64

Check the sequence on Physical Standby again

that sequence has to same as the Primary sequence with applied

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch
Oldest online log sequence     64
Next log sequence to archive   0
Current log sequence           65
SQL> select max(sequence#) from v$archived_log  where applied='YES';

MAX(SEQUENCE#)
--------------
            64