How to Physical Standby into Snapshot Standby database

Snapshot physical standby database

What is a snapshot standby database?

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

What it doesn’t do? or how it is different from physical standby database?

A snapshot standby database receives and archives, but does not apply, redo data from a primary database. It retains all the redo and only applied when it converts back into physical standby. Make sure you have enough space on the standby to retain all the redo/archive logs shipped from primary.

When the redo data is applied back to the standby?

As said above Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database.

Benefits of snapshot standby database?

You can open the standby database in READ-WRITE mode and apply any updates/patches or make code changes on the snapshot standby and see the results before applying them onto the primary directly.Once satisfied with the results you can convert the snapshot database back into physical standby and “All the changes(local updates) you applied on the snapshot standby will be discarded automatically using flashback logs*” and redo data from the primary database will be applied to the physical standby.

What you cannot do(disadvantages) with snapshot standby?

• You cannot switchover to snapshot standby database.
• If you are using maximum protection mode then you cannot use snapshot standby database.

What are the Prerequisites for snapshot standby database then?

If you ever want to convert the snapshot standby database back to physical standby then you should enable flashback on the standby database so that it can record all the local changes and discard them when you are ready to convert the snapshot standby back into physical standby.

Demo:

In my demo my I am using maximum performance protection mode:

SQL> select  PROTECTION_MODE from v$database;           

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

Enable flashback on Primary & Standby:

Standby:

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl       MOUNTED       PHYSICAL STANDBY             NO

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database flashback on;

Database altered.

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl       MOUNTED       PHYSICAL STANDBY             YES

Primary:

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

msft           READ WRITE           PRIMARY              NO

SQL> alter database flashback on;

Database altered.

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

msft           READ WRITE           PRIMARY              YES

Convert the physical standby into snapshot standby:

On Standby:

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl           MOUNTED           SNAPSHOT STANDBY     YES

Now open the standby database for updates:

SQL> alter database open;

Database altered.

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl           READ WRITE       SNAPSHOT STANDBY         YES

Let’s make some changes on the Primary & Standby:

Standby:

SQL> create table snapshot (col1 number,col2 varchar2(10));

Table created.

SQL> begin    

 2  for i in 1..1000 loop

 3    insert into snapshot values(i,'ABC');

 4  END LOOP;

 5  end;

 6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from snapshot;

 COUNT(*)

----------

     1000

SQL> commit;

Commit complete.

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

SQL> insert into hr.regions values (10,'North Africa');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

   10 North Africa

6 rows selected.

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl       READ WRITE       SNAPSHOT STANDBY YES

Verify the primary:

Primary is clean from standby changes:

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

msft       READ WRITE       PRIMARY      YES

SQL> select object_name from dba_objects where object_name='SNAPSHOT';

no rows selected

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

Nows let's make some changes in Primary:

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

SQL> create table primary (col1 number,col2 varchar2(10));

Table created.

SQL> begin

 2   for i in 1..2000 loop

 3     for j in 1..100 loop

 4       insert into primary values (i,j);

 5     end loop;

 6   end loop;

 7  end;

 8  /

PL/SQL procedure successfully completed.

SQL> select count(*) from primary;  ←  New table in Primary

 COUNT(*)

----------

    200000

SQL> commit;

Commit complete.

SQL> insert into hr.regions values (20,'India');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

   20 India    ←  New data in Primary

6 rows selected.

Redo data from primary is not applied onto the snapshot standby hence the changes from primary are not found in Standby:

On Standby:

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl       READ WRITE       SNAPSHOT STANDBY YES

SQL> select object_name from dba_objects where object_name='PRIMARY';

no rows selected

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

   10 North Africa     ← Data not in Primary

6 rows selected.

Now Let’s convert the Snapshot standby back to Physical Standby:

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  430075904 bytes

Fixed Size           2214056 bytes

Variable Size         335546200 bytes

Database Buffers       88080384 bytes

Redo Buffers           4235264 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database ;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl       MOUNTED       PHYSICAL STANDBY YES

SQL> recover managed standby database disconnect;

Media recovery complete.

SQL> select process,sequence#,status from v$managed_standby;

PROCESS    SEQUENCE# STATUS

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

ARCH           0 CONNECTED

ARCH           0 CONNECTED

ARCH           0 CONNECTED

ARCH           0 CONNECTED

MRP0         33 WAIT_FOR_LOG

SQL> /

PROCESS    SEQUENCE# STATUS

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

ARCH         33 CLOSING

ARCH         36 CLOSING

ARCH           0 CONNECTED

ARCH           0 CONNECTED

MRP0         37 WAIT_FOR_LOG

RFS           0 IDLE

RFS           0 IDLE

RFS           0 IDLE

RFS         37 IDLE

9 rows selected.

Verify the changes from Primary are applied to the Physical Standby database:

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from hr.regions;

REGION_ID REGION_NAME

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

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

    5 Central America

   20 India    ←  New data from Primary

6 rows selected.

SQL> select object_name from dba_objects where object_name='PRIMARY';

OBJECT_NAME
--------------------------------------------------------------------------------
PRIMARY   <-- Table we created in Primary is now reflected into Physical Standby

 

SQL> select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_ OPEN_MODE       DATABASE_ROLE     FLASHBACK_ON

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

orcl       READ ONLY       PHYSICAL STANDBY YES

This concludes the successful role transition from

• Physical Standby to Snapshot Standby
• Snapshot Standby to Physical Standby