I'm Top Oracle DBA

Step by Step for Snapshot Standby on Oracle Database 11G

|

This article shows the Step of Snapshot Standby on Oracle Database 11G.

Mention

  1. They have downtime on the standby site.
  2. snapshot standby opens to read and write but does not apply archive if you want to apply you need to convert to physical standby and apply so when you enable standby to snapshot you can insert, update and delete data but when you convert back to physical standby to apply archive everything that you change at snapshot standby will be gone.
  3. You must do everything at the standby site only.

Step

  1. Physical Standby to Snapshot Standby
  2. Snapshot Standby to Physical Standby

Step 1

Physical Standby to Snapshot Standby
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> select process from v$managed_standby;         

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

SQL> 
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> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> select process from v$managed_standby;    

PROCESS
---------
ARCH
ARCH

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                        0 SNAPSHOT STANDBY NOT ALLOWED          DISABLED NONE    DB11G_STDBY

SQL> ALTER DATABASE OPEN;

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 WRITE             472561317 SNAPSHOT STANDBY NOT ALLOWED          DISABLED NONE    DB11G_STDBY

Step 2

Snapshot Standby to Physical Standby
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 WRITE             472561317 SNAPSHOT STANDBY NOT ALLOWED          DISABLED NONE    DB11G_STDBY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> 
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
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                        0 PHYSICAL STANDBY RECOVERY NEEDED      DISABLED NONE    DB11G_STDBY

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

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

SQL> select process from v$managed_standby;    

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

8 rows selected.

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
RFS
RFS
RFS
MRP0

9 rows selected.

SQL> 

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

MAX(SEQUENCE#)
--------------
            71
1,149
error: Content is protected !!