Converting a physical standby to snapshot standby

 

Converting to snapshot standby

All is starting from a physical standby configuration like the one below:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

Issue Data Guard Broker (DGB) command to convert your physical standby to a snapshot standby:

DGMGRL> convert database rac2 to snapshot standby;
Converting database "rac2" to a Snapshot Standby database, please wait...
Database "rac2" converted successfully
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Snapshot standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

And that’s it, you now have a read-write snapshot standby:

DGMGRL> show database rac2;
 
Database - rac2
 
  Role:            SNAPSHOT STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       41 minutes 24 seconds
  Instance(s):
    rac2
 
Database Status:
SUCCESS

Technology behind in flashback database with creation of a guaranteed restore point (as Fast Recovery Area is configured all is transparent):

SQL> show parameter flash
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                  string
db_flash_cache_size                  big INTEGER 0
db_flashback_retention_target        INTEGER     1440
SQL> SELECT flashback_on FROM v$database;
 
FLASHBACK_ON
------------------
RESTORE POINT ONLY
 
SQL> SET lines 200
SQL> SELECT * FROM V$FLASHBACK_DATABASE_LOG;
 
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------ ---------------- -------------- ------------------------
             1048478 07-JUN-11                      1440       16384000                171122688
 
SQL> col name FOR a50
SQL> SELECT * FROM V$FLASHBACK_DATABASE_LOGFILE;
 
NAME                                                     LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME         TYPE
-------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ------------------ ---------
+DATA/rac2/flashback/log_1.284.753208805                    1          1          1    8192000             0                    NORMAL
+DATA/rac2/flashback/log_2.285.753208809                    2          1          1    8192000             0                    RESERVED
 
SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;
 
BEGIN_TIME         END_TIME           FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------ ------------------ -------------- ---------- ---------- ------------------------
07-JUN-11          07-JUN-11                 6209536    7118848    3195904                        0
 
SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;
 
BEGIN_TIME         END_TIME           FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------ ------------------ -------------- ---------- ---------- ------------------------
07-JUN-11          07-JUN-11                 6217728    7118848    3257344                        0
 
SQL> SELECT * FROM V$FLASHBACK_DATABASE_LOGFILE;
 
NAME                                                     LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME         TYPE
-------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ------------------ ---------
+DATA/rac2/flashback/log_1.284.753208805                    1          1          1    8192000             0                    NORMAL
+DATA/rac2/flashback/log_2.285.753208809                    2          1          1    8192000             0                    RESERVED
 
SQL> SELECT * FROM V$RESTORE_POINT;
 
       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME
---------- --------------------- --- ------------ ---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE NAME
--------------------------------------------------------------------------- --- --------------------------------------------------
   1048478                     2 YES      8192000 07-JUN-11 04.40.03.000000000 PM
                                                                            YES SNAPSHOT_STANDBY_REQUIRED_06/07/2011 16:40:03

Testing

Your physical standby has change its role and is now opened read write. All changes will be anyway lost when converted back to physical standby:

SQL> SELECT DATABASE_ROLE FROM v$database;
 
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
 
SQL> SELECT * FROM yjaquier.test ORDER BY val;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three
         4 Four
 
SQL> INSERT INTO yjaquier.test VALUES(5,'Five');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM yjaquier.test ORDER BY val;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three
         4 Four
         5 Five

Obviously more you wait and update your snapshot standby and more activity you have on primary database and more you will generate flashback log files and archived log files. Redo files are still sent from primary but not applied, standby database simply archived them. All those archived log files would need to be applied when converting back to physical standby:

SQL> SET lines 200
SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;
 
BEGIN_TIME         END_TIME           FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------ ------------------ -------------- ---------- ---------- ------------------------
08-JUN-11          08-JUN-11                       0     360448     499712                        0
08-JUN-11          08-JUN-11                  647168    4595712    3517952                119316480
08-JUN-11          08-JUN-11                  344064    4358144    3429888                125804544
08-JUN-11          08-JUN-11                  434176    4718592    3552768                133644288
08-JUN-11          08-JUN-11                  434176    4153344    3453440                142442496
08-JUN-11          08-JUN-11                  303104    4399104    3494912                152592384
08-JUN-11          08-JUN-11                  573440    4653056    3600896                164683776
08-JUN-11          08-JUN-11                  507904    4554752    3700224                178397184
08-JUN-11          08-JUN-11                 7495680   18145280   18286080                195035136
08-JUN-11          08-JUN-11                  876544    4669440    3517440                196730880
07-JUN-11          08-JUN-11                 1015808    5177344    3533824                218677248
07-JUN-11          07-JUN-11                 1286144    5349376    3958784                246423552
07-JUN-11          07-JUN-11                58671104   76439552   39696896                282329088
07-JUN-11          07-JUN-11                 1245184    4669440    3530240                 57630720
07-JUN-11          07-JUN-11                 1097728    4546560    3549184                 64561152
07-JUN-11          07-JUN-11                 1589248    4595712    3598336                 77291520
07-JUN-11          07-JUN-11                 1851392    4161536    3451392                 96878592
07-JUN-11          07-JUN-11                 6242304    7348224    3411968                149151744
 
18 ROWS selected.
SQL> col name FOR a50
SQL> SELECT * FROM V$FLASHBACK_DATABASE_LOGFILE;
 
NAME                                                     LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME         TYPE
-------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ------------------ ---------
+DATA/rac2/flashback/log_1.284.753208805                    1          1          1    8192000             0                    NORMAL
+DATA/rac2/flashback/log_2.285.753208809                    2          1          2    8192000       1059183 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_3.323.753217253                    3          1          3    4096000       1073612 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_4.301.753228097                    4          1          4    3981312       1075013 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_5.310.753228141                    5          1          5    3981312       1075721 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_6.300.753228169                    6          1          6    3981312       1076347 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_7.292.753228209                    7          1          7    3981312       1076706 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_8.293.753228227                    8          1          8    3981312       1076897 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_9.289.753228237                    9          1          9    3981312       1077154 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_10.291.753228249                  10          1         10    3981312       1077334 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_11.288.753228261                  11          1         11    3981312       1077461 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_12.327.753228267                  12          1         12    3981312       1077492 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_13.328.753228275                  13          1         13    3981312       1077547 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_14.275.753228277                  14          1         14    3981312       1077831 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_15.331.753228299                  15          1         15    3981312       1078262 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_16.332.753228315                  16          1         16    3981312       1079307 07-JUN-11          NORMAL
+DATA/rac2/flashback/log_17.333.753228349                  17          1         17    3981312       1088804 08-JUN-11          NORMAL
+DATA/rac2/flashback/log_18.334.753235217                  18          1         18    3981312       1100163 08-JUN-11          NORMAL
+DATA/rac2/flashback/log_19.336.753244277                  19          1         19    3981312       1100542 08-JUN-11          NORMAL
+DATA/rac2/flashback/log_20.338.753244409                  20          1         20    3981312       1128220 08-JUN-11          NORMAL
+DATA/rac2/flashback/log_21.339.753267649                  21          1          1    3981312             0                    RESERVED
 
21 ROWS selected.

And more you use space in Fast Recovery Area:

SQL> SELECT * FROM V$RECOVERY_AREA_USAGE;
 
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                         .2                         0               1
REDO LOG                           7.14                         0               7
ARCHIVED LOG                       9.32                         0              42
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                      1.84                         0              21
FOREIGN ARCHIVED LOG                  0                         0               0
 
7 ROWS selected.

Remark:
As I did not have much activity on my primary test database archived log file percentage usage has not increased much.

Converting back to physical standby

Once you have completed your testing activity you can convert it back to physical standby with one simple command:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Snapshot standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> convert database rac2 to physical standby;
Converting database "rac2" to a Physical Standby database, please wait...
Operation requires shutdown of instance "rac2" on database "rac2"
Shutting down instance "rac2"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rac2" on database "rac2"
Starting instance "rac2"...
ORACLE instance started.
Database mounted.
Continuing to convert database "rac2" ...
Operation requires shutdown of instance "rac2" on database "rac2"
Shutting down instance "rac2"...
ORA-01109: database not open
 
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rac2" on database "rac2"
Starting instance "rac2"...
ORACLE instance started.
Database mounted.
Database "rac2" converted successfully

And you are back to original situation:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

And flashback log files have been deleted:

SQL> SET lines 200
SQL> SELECT * FROM V$RECOVERY_AREA_USAGE;
 
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                         .2                         0               1
REDO LOG                           7.14                         0               7
ARCHIVED LOG                       9.96                         0              45
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0
 
7 ROWS selected.
 
SQL> SELECT * FROM V$FLASHBACK_DATABASE_LOGFILE;
 
no ROWS selected
 
SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;
 
no ROWS selected

We can’t query our test table (physical standby in MOUNT state) but all changes performed when database was in snapshot standby mode are lost…

This entry was posted in Oracle and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>