WARNING: This web is not used anymore. Please use PDBService.OpenDataGuardForTest instead!
 

Open Physical Standby database for testing and re-enable Dataguard afterwards

This document is based on "Data Guard Scenarions" at http://oracle-documentation.web.cern.ch/oracle-documentation/10gr2doc/server.102/b14239/scenarios.htm#CIHIABDH

  • Note : when Standby is open, it does not receive redo data from primary and cannot provide disaster protection

Open standby in read/write mode

  • On Standby - stop all except one database instance
    • srvctl stop instance -d ` echo ${ORACLE_SID%[0-9]}`  -i ` echo $ORACLE_SID` ## on the nodes to be stopped

  • On Standby - redo apply and create restore point
      STANDBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
      STANDBY> CREATE RESTORE POINT before_stop GUARANTEE FLASHBACK DATABASE;

  • On Primary - switch logs so the SCN of restore point is archived in the standby
    • PRIMARY> ALTER SYSTEM ARCHIVE LOG CURRENT;

  • On Primary and standby - disable archive redo shipping
    • PRIMARY>  ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_x=DEFER SID='*'; ## change x by the correct archiver process #
    • STANDBY>  ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_x=DEFER SID='*'; ## change x by the correct archiver process #

  • On Standby - Activate and open it
    • STANDBY> ALTER DATABASE ACTIVATE STANDBY DATABASE;
    • STANDBY> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
    • STANDBY> ALTER DATABASE OPEN;

  • On Standby - start other instances, if necessary
    • srvctl start instance -d ` echo ${ORACLE_SID%[0-9]}`  -i ` echo $ORACLE_SID` ## on the nodes to be started

Flashback database and resume standby

  • On Standby - stop all except one database instance
    • srvctl stop instance -d ` echo ${ORACLE_SID%[0-9]}`  -i ` echo $ORACLE_SID` ## on the nodes to be stopped

  • On Standby, put in mount mode and flashback database
    • STANDBY> STARTUP MOUNT FORCE;
    • STANDBY> FLASHBACK DATABASE TO RESTORE POINT before_stop;
    • STANDBY> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    • STANDBY> STARTUP MOUNT FORCE;

Standby is not much behind primary

  • (check archive retention on /etc/inittab of pdb-backup, usually 1 day), one can just let oracle automatically fetch the gap
    • STANDBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

  • Drop restore point:
    • STANDBY>  DROP RESTORE POINT before_stop;

  • Re-enable the archive shipping on both ends:
    • PRIMARY>  ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_x=ENABLE SID='*'; ## change x by the correct archiver process #
    • STANDBY>  ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_x=ENABLE SID='*'; ## change x by the correct archiver process #

  • On Standby - start other instances, if necessary
    • srvctl start instance -d ` echo ${ORACLE_SID%[0-9]}`  -i ` echo $ORACLE_SID` ## on the nodes to be started


Standby several days behind primary

  • Standby would not be able to fetch, do a incremental backup of primary or fetch from backup the missing archive logs

  • recover db standby DB using backup and refresh the controlfile as detailed in RecoveryStandbyGaps

  • On Standby - stop all except one database instance
    • srvctl stop instance -d ` echo ${ORACLE_SID%[0-9]}`  -i ` echo $ORACLE_SID` ## on the nodes to be stopped

  • Restart Flashback Database:
    • STANDBY> ALTER DATABASE FLASHBACK OFF;
    • STANDBY> ALTER DATABASE FLASHBACK ON;

  • Start Redo Apply on the physical standby database and drop restore point
    • STANDBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    • STANDBY> DROP RESTORE POINT before_stop;

  • Remove the incremental backups from the standby system:
    • RMAN_STANDBY> DELETE BACKUP device type disk TAG 'FOR STANDBY';

  • Manually remove the incremental backups from the primary system:
    • rm /ORA/dbs00/oracle/backup/ForStandby_*

  • Re-enable the archive shipping on both ends:
    • PRIMARY>  ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_x=ENABLE SID='*'; ## change x by the correct archiver process #
    • STANDBY>  ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_x=ENABLE SID='*'; ## change x by the correct archiver process #

  • On Standby - start other instances, if necessary
    • srvctl start instance -d ` echo ${ORACLE_SID%[0-9]}`  -i ` echo $ORACLE_SID` ## on the nodes to be started

Dataguard checks

      SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
      SQL> select thread#, sequence#,  first_time, next_time, applied from v$archived_log order by 3;

      SQL> select * from v$dataguard_status;
      SQL> alter system archive log current;

Limitations of Flashback Database

Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:

  • Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.

  • You cannot use Flashback Database to undo a shrink datafile operation.

  • If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

  • When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

  • shrinking a datafile or dropping a tablespace can prevent flashing back the Database

Edit | Attach | Watch | Print version | History: r15 < r14 < r13 < r12 < r11 | Backlinks | Raw View | Raw edit | More topic actions...
Topic revision: r13 - 2008-11-21 - LucaCanali
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback