New 11g feature (not sure if this came out in 11gR1 or 11gR2) – but it doesn’t get any easier… or should I say it used to be a lot harder.
This feature creates a “guaranteed restore point” and generates flashback logs – whether flashback database is on or not to do the flashback. So no, you do not have to turn on flashback database – v$database.flashback_on can equal NO.
Here are the scripts and a crontab to cycle back and forth. Harder part is creating the standby – shown here.
Flopping back and forth between a physical standby and a fully update-able snapshot standby that is open for update, delete, insert etc. is now a piece of cake…
Convert a phyical standby to a snapshot standby
[oracle@ol69stby oscripts]$ cat dg_to_snap.bash
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
$ORACLE_HOME/bin/dgmgrl << EOF
connect sys/Juster11#@ggstby
convert database ‘ggstby’ to snapshot standby;
exit
EOF
Now here’s the log of that action
[oracle@ol69stby oscripts]$ cat dg_to_snap.log
DGMGRL for Linux: Release 12.2.0.1.0 – Production on Wed Sep 20 18:20:01 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> Connected to “GGSTBY”
Connected as SYSDBA.
DGMGRL> Converting database “ggstby” to a Snapshot Standby database, please wait…
Database “ggstby” converted successfully
DGMGRL> DGMGRL for Linux: Release 12.2.0.1.0 – Production on Wed Sep 20 18:53:01 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> Connected to “GGSTBY”
Connected as SYSDBA.
DGMGRL> Converting database “ggstby” to a Snapshot Standby database, please wait…
Database “ggstby” converted successfully
Lets look at the restore point that was created for the snapshot for us (automatically)
SQL> set line 200
SQL> column name a60
SQL> select scn, name, guarantee_flashback_database, storage_size
from v$restore_point
SCN NAME GUA STORAGE_SIZE
———- ———————————————————— — ————
11092882 SNAPSHOT_STANDBY_REQUIRED_09/20/2017 18:53:11 YES 52428800
Convert a snapshot standby to a physical standby
[oracle@ol69stby oscripts]$ cat dg_to_physical.bash
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
$ORACLE_HOME/bin/dgmgrl << EOF
connect sys/Juster11#@ggstby
convert database ‘ggstby’ to physical standby;
exit
EOF
Setup a crontab for either or both
[oracle@ol69stby oscripts]$ crontab -l
30 3 * * * cat /proc/meminfo >> /tmp/meminfo
#20 18 * * * /oscripts/dg_to_snap.bash >> /oscripts/dg_to_snap.log
#31 10 * * * /oscripts/dg_to_show_config.bash >> /oscripts/dg_show_config.log
37 18 * * * /oscripts/dg_to_physical.bash >> /oscripts/dg_to_physical.log
#41 10 * * * /oscripts/dg_to_show_config.bash >> /oscripts/dg_show_config.log
If you have a standby snapshot – this view provides info
SQL> desc v$restore_point
Name Null? Type
—————————————– ——– —————————-
SCN NUMBER
DATABASE_INCARNATION# NUMBER
GUARANTEE_FLASHBACK_DATABASE VARCHAR2(3)
STORAGE_SIZE NUMBER
TIME TIMESTAMP(9)
RESTORE_POINT_TIME TIMESTAMP(9)
PRESERVED VARCHAR2(3)
NAME VARCHAR2(128)
PDB_RESTORE_POINT VARCHAR2(3)
CLEAN_PDB_RESTORE_POINT VARCHAR2(3)
PDB_INCARNATION# NUMBER
CON_ID NUMBER
SQL> set line 200
SQL> select scn, name, guarantee_flashback_database, storage_size from v$restore_point;