Table of contents
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…
