This article is to open the Standby
database in read write mode for any reporting or testing and then move it back
to standby database using the flashback technology.
One of the customer I support has a
large database with a strict high-availability requirement. Every Six month They
will do DR Drill activity by open physical standby database in flashback mode
and to check DR is sufficient to handle the load same as production .
As DBAs, Before switchover DR(Standby
Database) ,we must check if any problems in physical standby . Then opened DR
(Standby DB) is temporarily in read/write.
Once task of testing (DR DRILL)
finish we flash back database to guarantee restore point (flashed back to a
point in the past to be reverted back to a physical standby database.).After
testing we can start real time sync from production to DR Server, without the
need to recreate the physical standby database from a backup copy of the
primary database.
Perform the following steps to
activate the physical standby database as a production database and later
resynchronize it with the primary database.
Lab:-
Primary Database Name :- DB11G
Standby Database Name :- DB11GSTD
DB version :-
11.2.0.1
Standby Database:-
Enable Flashback Database on the
Physical Standby Database (Skip this step if FRA already configured)
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G MOUNTED MAXIMUM AVAILABILITY PHYSICAL
STANDBY
SQL>
SQL> archive log
list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log
sequence 45
Next log sequence to
archive 0
Current log
sequence 49
SQL>
Run the following statements on the physical
standby database to configure a 32GB flash recovery area in the
/u01/app/oracle/flash_recovery_area directory with a retention time of 24
hours:
SQL> alter system set
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
System altered.
SQL> alter system set
db_recovery_file_dest_size=3882M;
System altered.
SQL> alter system set
db_flashback_retention_target=1440;
System altered.
SQL> show parameter
db_recovery_file_dest
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3882M
SQL> show parameter
db_flashback_retention_target
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
Stop Redo Apply on the physical standby database and verify it is in MOUNT
mode
SQL> alter database
recover managed standby database cancel;
Database altered.
SQL>
Turn on Flashback
Logging.
SQL> select open_mode
from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database
flashback on;
Database altered.
SQL> select
flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
The size and location of the FRA and
the Flashback Database retention policy time listed above should be modified
for your environment.
Place the physical standby database back into recovery mode by starting Redo Apply.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL>
Cancel Redo Apply and create a guaranteed restore point.
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- --------------------
-------------------- ----------------
DB11G MOUNTED MAXIMUM AVAILABILITY PHYSICAL
STANDBY
SQL>
SQL>
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> CREATE RESTORE
POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE;
Restore point created.
To Confirim the details of restore point and its scn and
time stamp run
SQL> select
NAME,SCN,TIME from v$restore_point;
NAME
--------------------------------------------------------------------------------
SCN
----------
TIME
---------------------------------------------------------------------------
STANDBY_FLASHBACK_TESTING
1143386
15-FEB-15
08.34.41.000000000 PM
Primary Database :-
On the primary database, switch logs
so the SCN of the restore point will be archived on the physical standby
database. When using standby redo log files, this step is essential to ensure
the database can be properly flashed back to the restore point.
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G READ WRITE MAXIMUM AVAILABILITY PRIMARY
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
Defer log archive destinations
pointing to the standby that will be activated.
SQL> ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2=DEFER;
System altered.
Standby Database:-
Activate the physical standby database
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G MOUNTED MAXIMUM AVAILABILITY PHYSICAL
STANDBY
SQL> ALTER DATABASE
ACTIVATE STANDBY DATABASE;
Database altered.
SQL>
Once its done you can check the controlfile status will be changed from
Standby to Current.
SQL> select
CONTROLFILE_TYPE from v$database;
CONTROL
-------
CURRENT
SQL>
Then open the database
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- --------------------
-------------------- ----------------
DB11G MOUNTED MAXIMUM AVAILABILITY PRIMARY
SQL> ALTER DATABASE
SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database altered.
SQL> ALTER DATABASE
OPEN;
Database altered.
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G READ WRITE MAXIMUM PERFORMANCE PRIMARY
SQL>
Standby Testing
Once the standby database has been
activated, you can run reporting tools or perform other testing and activities
for days or
even weeks, independent of the primary
database
Test Application by pointing standby database.
Example :-
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G READ WRITE MAXIMUM PERFORMANCE PRIMARY
SQL> create user
dr_testing identified by dr_testing ;
User created.
SQL> grant dba to
dr_testing ;
Grant succeeded.
SQL> conn
dr_testing/dr_testing
Connected.
SQL> create table
db_data_files_summary as select * from dba_data_files;
Table created.
SQL> create table
object_report as select * from dba_objects where owner='SYS';
Table created.
SQL> insert into
object_report select * from dba_objects where owner='SYSTEM';
529 rows created.
SQL> commit;
Commit complete.
SQL> select count(*)
from object_report;
COUNT(*)
----------
31343
SQL>
After completion of testing follow
next steps for revert to physical standby.
Revert the active standby database back to Physical standby database
Mount the database
SQL> conn / as sysdba
Connected.
SQL>
SQL> shutdown
immediate;
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL> startup mount;
ORACLE instance started.
Total System Global
Area 442601472 bytes
Fixed Size 2214176 bytes
Variable Size 322963168 bytes
Database Buffers 113246208 bytes
Redo Buffers 4177920 bytes
Database mounted.
SQL>
SQL>
Flashback the database to restore point
SQL>
SQL> FLASHBACK
DATABASE TO RESTORE POINT Standby_flashback_testing ;
Flashback complete.
SQL>
You can confirm the same
by checking the controlfile status. It will be now backup controlfile
SQL> select controlfile_type
from v$database;
CONTROL
-------
BACKUP
SQL>
Convert to Standby database
SQL> ALTER DATABASE
CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> STARTUP MOUNT
FORCE;
ORACLE instance started.
Total System Global
Area 442601472 bytes
Fixed Size 2214176 bytes
Variable Size 322963168 bytes
Database Buffers 113246208 bytes
Redo Buffers 4177920 bytes
Database mounted.
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL>
SQL> alter database
set standby database to maximize availability;
Database altered.
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G MOUNTED MAXIMUM AVAILABILITY PHYSICAL
STANDBY
SQL>
SQL> alter database
recover managed standby database using current logfile disconnect;
Database altered.
SQL>
Primary Database
Re-enable archiving to the physical standby database
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G READ WRITE MAXIMUM AVAILABILITY PRIMARY
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL>
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G READ WRITE MAXIMUM AVAILABILITY PRIMARY
Ensure that all the
transaction to reach to standby database
SQL> create user
after_dr_dril_valid identified by after_dr_dril_valid;
User created.
SQL> grant dba to
after_dr_dril_valid;
Grant succeeded.
SQL> conn
after_dr_dril_valid/after_dr_dril_valid
Connected.
SQL> create table
package_summary as select * from dba_objects where object_type='PACKAGE' and
owner='SYS';
Table created.
SQL> insert into
package_summary select * from dba_objects where object_type='PACKAGE' and
owner='SYSTEM';
1 row created.
SQL> insert into
package_summary select * from dba_objects where object_type='PACKAGE' and
owner='HR';
0 rows created.
SQL> commit;
Commit complete.
SQL> select count(*)
from package_summary;
COUNT(*)
----------
623
SQL> conn / as sysdba
Connected.
SQL> alter system
switch logfile;
System altered.
SQL> /
System altered.
SQL>
Standby Database:-
Open the database in Read only mode
and ensure that all the transaction done in active mode are no more
SQL> select
NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------
-------------------- -------------------- ----------------
DB11G MOUNTED MAXIMUM AVAILABILITY PHYSICAL
STANDBY
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE
OPEN READ ONLY;
Database altered.
SQL> conn
dr_testing/dr_testing
ERROR:
ORA-01017: invalid
username/password; logon denied
Warning: You are no
longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> conn
after_dr_dril_valid/after_dr_dril_valid
Connected.
SQL> select * from
tab;
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
PACKAGE_SUMMARY TABLE
SQL> select count(*)
from PACKAGE_SUMMARY;
COUNT(*)
----------
623
SQL>
Drop the restore point
SQL> shutdown
immediate;
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL> startup mount;
ORACLE instance started.
Total System Global
Area 442601472 bytes
Fixed Size 2214176 bytes
Variable Size 322963168 bytes
Database Buffers 113246208 bytes
Redo Buffers 4177920 bytes
Database mounted.
SQL> DROP RESTORE
POINT Standby_flashback_testing ;
Restore point dropped.
SQL>
Enable DR sync
SQL> alter database
recover managed standby database using current logfile disconnect;
Database altered.
SQL>
Summary:
This procedure demonstrates how to
open the Standby database in read write mode and then move it back to standby
database using the flashback technology.
Reference: Oracle Metalink Note Doc ID
805438.1
