|
DataGuard
|
Hi, everyone. We will continue the topic DataGuard. Furthermore, we will also introduce the Flashback Database available since Oracle 10g.
What is Flashback?
We have examined Flashback Query as a new function of Oracle 9i and explained the result in Ora Ora Oracle.
Flashback Query, just as what its name describes, is the function to specify <SCN> (System Change Number) and to recover the selected result at that time.
In Oracle10g Flashback function has been greatly improved. We want to use Flashback Database to include the failovered primary database as standby database in DataGuard environment.
Try Flashback
While an error occurs, the primary database would be switchovered to standby database. However, in order to include that primary server in DataGuard environment, it is necessary to recover the system back to the status right before the error occurs. Flashback can be used for such purpose. Let's validate Flashback now.
In order to keep Flashback for two days, we set DB_FLASHBACK_RETENTION_TARGET as 2880(min.) although the default setting is 1440 min. (i.e. one day).
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880;
System changed.
|
Alter Flashback to ON.
SQL> alter database flashback on;
Database changed.
|
Confirm again by checking FLASHBACK_ON in v$database view.
SQL> SELECT FLASHBACK_ON from v$database;
FLASHBACK_ON
--------------------
YES
|
It is valid now.
Execute switchover by failover.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;
Database changed.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database changed.
SQL> select name,database_role,controlfile_type from v$database;
NAME DATABASE_ROLE CONTROL
------------------------------ ---------------- -------
PRIMARY PRIMARY CURRENT
|
Switchover is well done.
Take the original primary database as standby databse.
Execute Flashback after the original primary database is recovered.
Check SCN by STANDBY_BECAME_PRIMARY_SCN in v$database.
SQL> select STANDBY_BECAME_PRIMARY_SCN from v$database;
STANDBY_BECAME_PRIMARY_SCN
--------------------------
591903
|
This SCN is the system change number right before the error occurred. It has been recovered.
SQL> flashback database to scn 591903;
Falshback is finished.
|
Falshback is finished. The system was recovered to the status right before the error occurred.
Preparation for standby database
Create the control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';
Database changed.
|
Replace the current control file with this newly created control file.
Shutdown the system once and then...
SQL> recover managed standby database using current logfile disconnect;
Media recovery is finished.
|
Confirm it again.
SQL> select name , database_role from v$database;
NAME DATABASE_ROLE
------------------------------ ----------------
PRIMARY PHYSICAL STANDBY
|
It is included as the standby database.
That's it for today.
Masaru Hayashi
|
|
