Oracle DG

  
   create tablespace "tbs001" datafile '/u01/app/oracle/oradata/icisep/tbs001.dbf' size 100m reuse autoextend on next 1280k maxsize 1024M logging extent management local segment space management auto;

   create table scott.t1 (f1 varchar2(100)) tablespace "tbs001" pctfree 10 initrans 1 maxtrans 255 storage (initial 64k buffer_pool default) logging;

create or replace procedure scott.insert_t1(
  p_times in number,
  p_desc  in varchar2
) as
begin
  for i in 1..p_times loop
    insert into scott.t1 values(to_char(systimestamp,'YYYYMMDD HH24:MI:SSXFF') || ' did ' || p_desc);
  end loop;
  commit;
end;
/

  

   1. Check flashback status on both DBs:
      SQL> Select flashback_on from v$database;
      # The results are 'YES'

   2. Disable the dg_broker_start on both DBs:
      SQL> Alter system set dg_broker_start=false scope=both;

   3. On standby DB(icises):
      SQL> Shutdown immediate;
      SQL> Startup nomount;
      SQL> Alter database mount standby database;

   4. Reset the log_archive_dest for remote:
      SQL> ALTER SYSTEM SET log_archive_dest_3='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest02.macaowater.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=icises_XPT)(INSTANCE_NAME=icises)(SERVER=dedicated)))"','   LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1   reopen=300 db_unique_name="icises" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
      SQL> alter system set log_archive_dest_state_3=enable scope=both;

   5. On primary DB(icisep), login scott to do some transactions:
      SQL> call scott.insert_t1(10000, 'before failover');

   6. On primary DB(icisep), login sys to switch log file:
      SQL> alter system switch logfile;
      SQL> alter system switch logfile;
      SQL> alter system switch logfile;

   7. On primary DB(icisep), create a restore point:
      SQL> CREATE RESTORE POINT before_failover_prim;

   8. On standby DB(icises), create a restore point too:
      SQL> CREATE RESTORE POINT before_failover_stdby;

   9. Emulate a failure on primary DB(icisep):
      SQL> shutdown immediate;

  10. Failover to standby DB(icises):
      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
      SQL> Alter database open;

  11. On new primary DB(icises), set the log_archive_dest for remote:
      SQL> ALTER SYSTEM SET log_archive_dest_3='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest01.macaowater.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=icisep_XPT)(INSTANCE_NAME=icisep)(SERVER=dedicated)))"','   LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1   reopen=300 db_unique_name="icisep" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
      SQL> alter system set log_archive_dest_state_3=enable scope=both;

  12. On new primary DB(icises), login user scott to do some transactions:
      SQL> call scott.insert_t1(20000, 'after failover');
      SQL> call scott.insert_t1(5000, 'after failover, before reinstate');

  13. On new primary DB(icises), login sys to switch log files:
      SQL> alter system switch logfile;
      SQL> alter system switch logfile;
      SQL> alter system switch logfile;

  14. Now starting to re-instate the old primary DB(icisep). on new primary
      DB(icises), check the standby_became_primary_scn:
      SQL> SELECT STANDBY_BECAME_PRIMARY_SCN FROM V$DATABASE;

  15. On old primary DB(icisep):
      SQL> Startup mount;
      SQL> FLASHBACK DATABASE TO SCN {standby_became_primary_scn};  -- ORA-38743: Time/SCN is in the future of the database.
      SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
      SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_state_3=defer scope=both;
      SQL> SHUTDOWN IMMEDIATE;
      SQL> STARTUP noMOUNT;
      SQL> Alter database mount standby database;

  16. Now, switch the log files, On primary DB(icises):
      SQL> call scott.insert_t1(6000, 'after reinstate');
      SQL> ALTER SYSTEM SWITCH LOGFILE;
      SQL> ALTER SYSTEM SWITCH LOGFILE;
      SQL> ALTER SYSTEM SWITCH LOGFILE;

  17. On standby DB(icisep):
      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

  18. On primary DB(icises), login scott to do some transactions:
      SQL> call scott.insert_t1(50000, 'after reinstate, before switchover');

  19. On primary DB(icises), switch log files:
      SQL> alter system switch logfile;
      SQL> alter system switch logfile;
      SQL> alter system switch logfile;

  20. Now, begin to do the switchover, on primary DB(icises):
      SQL> shutdown immediate
      SQL> startup
      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown; -- long time process
      SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_state_3=defer scope=both;

  21. On standby DB(icisep):
      SQL> SHUTDOWN IMMEDIATE;
      SQL> STARTUP MOUNT;
      SQL> Alter database RECOVER MANAGED STANDBY DATABASE FINISH; -- 00283 & 38760
      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown;
      SQL> ALTER DATABASE OPEN;

  22. On primary DB(icisep), enable the remote log_archive_dest:
      SQL> ALTER SYSTEM SET log_archive_dest_state_3=enable SCOPE=BOTH;

  23. On primary DB(icisep), switch log files:
      SQL> ALTER SYSTEM SWITCH LOGFILE;
      SQL> ALTER SYSTEM SWITCH LOGFILE;
      SQL> ALTER SYSTEM SWITCH LOGFILE;

  24. On standby DB(icises):
      SQL> Shutdown immediate;
      SQL> Startup nomount;
      SQL> Alter database mount standby database;
      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

  25. Flashback to before_failover, on primary DB(icisep):
      SQL> Shutdown immediate;
      SQL> Startup mount;
      SQL> flashback database to restore point before_failover_prim;
      SQL> alter database open resetlogs;

  26. Sshutdown the standby DB(icises):
      SQL> shutdown immediate;
      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> flashback database to restore point before_failover_stdby;
      # It's OK.

  26. Enable the dg_broker_start on both DBs:
      SQL> Alter system set dg_broker_start=true scope=both;












Using a Physical Standby Database for Read/Write Testing and Reporting

1.standby DB
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
CREATE RESTORE POINT before_application_patch GUARANTEE FLASHBACK DATABASE;

2.primary DB
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=DEFER;

3.standby DB
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
ALTER DATABASE OPEN;

4.Do many transactions.

5.Revert the activated database back to a physical standby database
SQL> STARTUP MOUNT FORCE;
SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_patch;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

6.enable the remote log archive on primary DB.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

If now the status is not sync.
7. On the standby database, stop the managed recovery process (MRP):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

8. On the standby database, find the SCN which will be used for the incremental backup at the primary database:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

9. In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:
# rman target=icisep
RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step> DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

10. Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created).

11. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:
# rman target=icises
RMAN> CATALOG START WITH '/tmp/ForStandby';

12. Recover the standby database with the cataloged incremental backup:
RMAN> RECOVER DATABASE NOREDO;

13. In RMAN, connect to the primary database and create a standby control file backup:
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

14. Copy the standby control file backup to the standby system.

15. Shut down the standby database and startup nomount:

16. In RMAN, connect to standby database and restore the standby control file:
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

17. Shut down the standby database, remove the redo logs, set LOG_FILE_NAME_CONVERT  and startup mount:
SQL> alter system set LOG_FILE_NAME_CONVERT='/icisep/','/icises/' scope=spfile;
SQL> shutdown immediate;
# rm *.log
SQL> startup mount;

18. On the standby database, clear all standby redo log groups (there may be more than 3):
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

19. On the standby database, restart Flashback Database:
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

20. On the standby database, restart MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;