In this article I will show you how to flashback a database to a restore point, restoring also the database role that the database was having at the time of the restore point creation.
Current Data Guard Configuration:
DGMGRL> show configuration;
Configuration - testdg
Protection Mode: MaxPerformance
Databases:
test - Primary database
testsby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Creating backup for data guard files in Primary Database
SQL> show parameters broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /oracle/app/oracle/product/11.2.0/db_1/dbs/dr1test.dat
dg_broker_config_file2 string /oracle/app/oracle/product/11.2.0/db_1/dbs/dr2test.dat
cp /oracle/app/oracle/product/11.2.0/db_1/dbs/dr1test.dat /oracle/app/oracle/product/11.2.0/db_1/dbs/dr1test.dat.test_primary
cp /oracle/app/oracle/product/11.2.0/db_1/dbs/dr2test.dat /oracle/app/oracle/product/11.2.0/db_1/dbs/dr2test.dat.test_primary
Creating backup for data guard files in Standby Database
SQL> show parameters broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0/db_1/dbs/dr1testsby.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0/db_1/dbs/dr2testsby.dat
cp /u01/app/oracle/product/11.2.0/db_1/dbs/dr1testsby.dat /u01/app/oracle/product/11.2.0/db_1/dbs/dr1testsby.dat.test_standby
cp /u01/app/oracle/product/11.2.0/db_1/dbs/dr2testsby.dat /u01/app/oracle/product/11.2.0/db_1/dbs/dr2testsby.dat.test_standby
Disable transportation of archived log in Primary Database:
DGMGRL> edit database test set state = 'TRANSPORT-OFF';
Disable Apply archived logs in Standby Database:
DGMGRL> edit database testsby set state = 'APPLY-OFF';
Create a restore point in Standby:
sqlplus / as sysdba
shutdown immediate;
startup mount;
CREATE RESTORE POINT test_primary_dg GUARANTEE FLASHBACK DATABASE;
Taking a clean backup of the control file in Standby Database:
SQL> shutdown immediate;
SQL> startup nomount;
SQL> show parameters control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/testsby/controlfile/current.4032.1086886045, +FRA1/testsby/controlfile/current.259.1086886045
. oraenv
+ASM
asmcmd
ASMCMD> cp +DATA1/testsby/controlfile/current.4032.1086886045 /u01/app/oracle/product/11.2.0/db_1/dbs/control_standby.ctl
copying +DATA1/testsby/controlfile/current.4032.1086886045 -> /u01/app/oracle/product/11.2.0/db_1/dbs/control_standby.ctl
ASMCMD> exit
bash-4.3$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/dbs/control_standby.ctl
-rw-r----- 1 oracle oinstall 7716864 Oct 26 19:52 /u01/app/oracle/product/11.2.0/db_1/dbs/control_standby.ctl
bash-4.3$
Start the Standby database in mount state and enable Transportation and Apply for archived logs:
bash-4.3$ env|grep ORA
ORACLE_SID=test
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
bash-4.3$
sqlplus / as sysdba
alter database mount standby database;
exit
dgmgrl /
DGMGRL> edit database test set state = 'TRANSPORT-ON';
DGMGRL> edit database testsby set state = 'APPLY-ON';
Create a restore point in Primary Database:
sqlplus / as sysdba
shutdown immediate;
startup mount;
CREATE RESTORE POINT test_primary GUARANTEE FLASHBACK DATABASE;
Taking a clean backup of the control file in Primary Database:
shutdown immediate;
startup nomount;
SQL> show parameters control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/test/controlfile/current.3979.1086882659, +RECO1/test/controlfile/current.763.1086882659
SQL>
. oraenv
+ASM
asmcmd
ASMCMD> cp +DATA1/test/controlfile/current.3979.1086882659 /oracle/app/oracle/product/11.2.0/db_1/dbs/control_primary.ctl
copying +DATA1/test/controlfile/current.3979.1086882659 -> /oracle/app/oracle/product/11.2.0/db_1/dbs/control_primary.ctl
ASMCMD> exit
bash-3.2$ ls -ltr /oracle/app/oracle/product/11.2.0/db_1/dbs/control_primary.ctl
-rw-r----- 1 oracle oinstall 8404992 Oct 25 09:02 /oracle/app/oracle/product/11.2.0/db_1/dbs/control_primary.ctl
bash-3.2$
Stard the Primary Database up normally:
ORACLE_SID=test
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1
sqlplus / as sysdba
alter database mount;
alter database open;
Perform Switchover to the Standby Database:
dgmgrl /
DGMGRL> show configuration (WAIT UNTIL STANDBY IS TOTALLY SYNC)
DGMGRL> switchover to testsby;
Mount former Primary Database:
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
dgmgrl /
DGMGRL> show configuration;
DGMGRL> show configuration;
Configuration - testdg
Protection Mode: MaxPerformance
Databases:
testsby - Primary database
test - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
Now let's start the procedure of rolling back the databases using Flashback across the Dataguard Logs. The goal is to have exactly the same configuration from the beginning where "test" was the Primary Database and "testsby" was the Physical Standby.
Disable transport in the new Primary database and disable Apply in the new Standby Database
DGMGRL> edit database testsby set state = 'TRANSPORT-OFF';
DGMGRL> edit database test set state = 'APPLY-OFF';
In the Former Standby Database (Now new Primary DB):
Shutdown the database and flashback the database to the restore point we created:
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to RESTORE POINT test_primary_dg;
Check that even when flashback the database, it still have the current role (Primary Database), this is because Flashback command doesn't revert the Dataguard Role, but we will make it happen.
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY MOUNTED
SQL>
Shutdown the former Standby (currently Standby again) and restore the controlfile and dataguard configuration files:
shutdown immediate;
startup nomount;
rman target /
restore standby controlfile from '/u01/app/oracle/product/11.2.0/db_1/dbs/control_standby.ctl';
exit;
Drop the restore point in the former Standby (currently Standby again):
sqlplus / as sysdba
alter database mount standby database;
DROP RESTORE POINT TEST_PRIMARY_DG;
alter database flashback off;
Reboot the former Standby (currently Standby again):
shutdown immediate;
startup nomount;
alter database mount standby database;
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL>
SQL> alter system set dg_broker_start=false scope=both;
Flashback the format Primary Database:
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to RESTORE POINT test_primary;
SQL> shutdown immediate;
SQL> startup nomount;
Restore the Control file in the Former Primary, so that we can restore the Dataguard Role:
rman target /
restore primary controlfile from '/oracle/app/oracle/product/11.2.0/db_1/dbs/control_primary.ctl';
exit;
sqlplus / as sysdba
SQL> alter database mount;
Verify the new role of the Database, the new role is Primary Database (The original role):
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY MOUNTED
Drop the Restore Point in the Former Primary (Currently Primary again):
SQL> DROP RESTORE POINT TEST_PRIMARY;
SQL> alter database flashback off;
SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database until cancel;
SQL> alter database open resetlogs;
Restore the data guard configuration files in the Former Primary (Currently Primary again):
cp /oracle/app/oracle/product/11.2.0/db_1/dbs/dr1test.dat.test_primary /oracle/app/oracle/product/11.2.0/db_1/dbs/dr1test.dat
cp /oracle/app/oracle/product/11.2.0/db_1/dbs/dr2test.dat.test_primary /oracle/app/oracle/product/11.2.0/db_1/dbs/dr2test.dat
Restore the data guard configuration files in the Former Standby (Currently Standby again):
cp /u01/app/oracle/product/11.2.0/db_1/dbs/dr1testsby.dat.test_standby /u01/app/oracle/product/11.2.0/db_1/dbs/dr1testsby.dat
cp /u01/app/oracle/product/11.2.0/db_1/dbs/dr2testsby.dat.test_standby /u01/app/oracle/product/11.2.0/db_1/dbs/dr2testsby.dat
Reset the broker in both databases:
Former Standby (currently Standby again):
sqlplus / as sysdba
alter system set dg_broker_start=true scope=both;
Former Primary (currently Primary again):
sqlplus / as sysdba
alter system set dg_broker_start=true scope=both;
DGMGRL> edit database testsby set state = 'TRANSPORT-ON';
DGMGRL> edit database test set state = 'APPLY-ON';
Verify the current configuration:
DGMGRL> show configuration;
Configuration - testdg
Protection Mode: MaxPerformance
Databases:
test - Primary database
testsby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
As you can see, we were able to restore the both databases to a restore point with Flashback Database and also we were able to restore the Data Guard Role of each Database.
No hay comentarios:
Publicar un comentario