You can repeat this cycle of activate, flashback and revert as many times as is necessary.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
step 1:
Cancel Redo Apply and create a guaranteed restore point.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/u01/app/oracle/flash_rec
overy_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> create restore point before_switch_to_primary guarantee flashback database;
Restore point created.
SQL> select scn,name,time from v$restore_point;
SCN
----------
NAME
--------------------------------------------------------------------------------
TIME
---------------------------------------------------------------------------
481243
BEFORE_SWITCH_TO_PRIMARY
31-AUG-11 10.49.34.000000000 AM
alertlog file
------------------------------------------------------------------------------
Wed Aug 31 10:48:09 2011
MRP0: Background Media Recovery process shutdown (dbstby)
Wed Aug 31 10:48:10 2011
Managed Standby Recovery Canceled (dbstby)
Wed Aug 31 10:48:10 2011
Completed: alter database recover managed standby database cancel
Wed Aug 31 10:49:33 2011
Starting background process RVWR
RVWR started with pid=49, OS id=1629
Wed Aug 31 10:49:34 2011
Created guaranteed restore point BEFORE_SWITCH_TO_PRIMARY
------------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
481243
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/u01/app/oracle/oradata/dbstby/arch/
Oldest online log sequence 47
Next log sequence to archive 0
Current log sequence 49
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on primary database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 2:
Prepare the primary database to have the physical standby be diverged.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
481860
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/u01/app/oracle/oradata/dbtest/arch/
Oldest online log sequence 47
Next log sequence to archive 49
Current log sequence 49
SQL>
SQL> alter system archive log current;
System altered.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 3 Activate the physical standby database.
SQL> alter database activate standby database;
Database altered.
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 209718268 bytes
Database Buffers 532676608 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/u01/app/oracle/oradata/dbstby/arch/
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>
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.
alert log file
------------------------------------------------------
Wed Aug 31 10:54:18 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9]: Assigned to RFS process 8542
RFS[9]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[9]: No standby redo logfiles created
Wed Aug 31 10:54:32 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[10]: Assigned to RFS process 8884
RFS[10]: Identified database type as 'physical standby'
RFS[10]: No standby redo logfiles created
RFS[10]: Archived Log: '/home/u01/app/oracle/oradata/dbstby/arch/1_49_760329654.arc'
Wed Aug 31 10:55:33 2011
alter database activate standby database
Wed Aug 31 10:55:33 2011
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (dbstby)
Wed Aug 31 10:55:35 2011
RESETLOGS after complete recovery through change 481244
Resetting resetlogs activation ID 1153152819 (0x44bbb733)
Online log /home/u01/app/oracle/oradata/dbstby/redo01.log: Thread 1 Group 1 was previously cleared
Online log /home/u01/app/oracle/oradata/dbstby/redo02.log: Thread 1 Group 2 was previously cleared
Online log /home/u01/app/oracle/oradata/dbstby/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 481242
Wed Aug 31 10:55:35 2011
Setting recovery target incarnation to 5
Wed Aug 31 10:55:35 2011
Converting standby mount to primary mount.
Wed Aug 31 10:55:35 2011
ACTIVATE STANDBY: Complete - Database mounted as primary (dbstby)
Completed: alter database activate standby database
-----------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on primary database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/u01/app/oracle/oradata/dbtest/arch/
Oldest online log sequence 51
Next log sequence to archive 53
Current log sequence 53
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 4: Revert the activated database back to a physical standby database.
SQL> startup force mount
ORACLE instance started.
Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 209718268 bytes
Database Buffers 532676608 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL> select name,scn,time from v$restore_point;
NAME SCN TIME
------------------------------ ---------- ---------------------------------------------------------------------------
BEFORE_SWITCH_TO_PRIMARY 481243 31-AUG-11 10.49.34.000000000 AM
SQL> flashback database to restore point BEFORE_SWITCH_TO_PRIMARY;
Flashback complete.
SQL> alter database convert to physical standby ;
Database altered.
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 209718268 bytes
Database Buffers 532676608 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/u01/app/oracle/oradata/dbstby/arch/
Oldest online log sequence 53
Next log sequence to archive 0
Current log sequence 54
SQL>
reference:-
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
Part Number B14239-05
1 comment:
A Good Example indeed...
This is same that of snapshot standby database which is introduced in oracle 11g.
Keep on posting.. your post are very useful.
Neeraj
Post a Comment