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
    • On the standby database, find the SCN from which to start the recovery using these two queries:
      STANDBY> SELECT CURRENT_SCN FROM V$DATABASE;
      STANDBY> select  min(change#),min(time) from v$recover_file r, v$datafile f where f.file#=r.file# and f.enabled <>'READ ONLY';

  • Stop Redo Apply on the standby database:
    • STANDBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

  • Connect to the primary database as the RMAN target and create an incremental backup from the current SCN of the standby database that was recorded in step 1:
      RMAN_PRIMARY> RUN {
                        BACKUP as compressed backupset device type disk INCREMENTAL 
                          FROM SCN xxxxxx DATABASE FORMAT '/ORA/dbs00/oracle/backup/ForStandby_%U' tag 'FOR STANDBY';
                        BACKUP device type disk CURRENT CONTROLFILE FOR STANDBY FORMAT '/ORA/dbs00/oracle/backup/ForStandbyCTRL.bck';
                    }

  • Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created):
    • make sure destination directory exists and there is enough space
    • SCP /ORA/dbs00/oracle/backup/ForStandby* standby:/ORA/dbs00/oracle/backup/

  • Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:
    • RMAN_STANDBY> CATALOG START WITH '/ORA/dbs00/oracle/backup/ForStandby_';

  • Connect to the standby database as the RMAN target and apply incremental backups and restore controlfile
    • RMAN_STANDBY> RECOVER device type disk DATABASE NOREDO;
    • RMAN_STANDBY> SHUTDOWN;
    • RMAN_STANDBY> STARTUP NOMOUNT;
    • note with 10g must restore a new controlfile for standby or procedure does not work (SCN is not incremented)
    • RMAN_STANDBY> RESTORE STANDBY CONTROLFILE FROM '/ORA/dbs00/oracle/backup/ForStandbyCTRL.bck';
    • RMAN_STANDBY> SHUTDOWN;
    • RMAN_STANDBY> STARTUP MOUNT;
    • RMAN_STANDBY> crosscheck copy of database;
    • RMAN_STANDBY> delete expired copy of database;
    • note the following is a 'trick' to catalog the actual files as copies so we can then switch. redolog and other ASM files may throw errors, ignore
    • RMAN_STANDBY> catalog start with '+<DBNAME)_DATADG1'; (edit)
    • in the general case use the query below, switch database to copy only works if all datafiles are oracle-managed (i.e. does not work on compass)
    • SQL> select 'alter database rename file '''||f.name||''' to '''||c.name||''';' SQL from v$backup_copy_details c, v$datafile f where c.file#=f.file# and c.name <> f.name order by c.file#;
    • SQL> run the script as generated above
    • alternative if db has all oracle-managed files: RMAN_STANDBY> SWITCH DATABASE TO COPY;

  • Clear all standby redo log groups:
    • STANDBY>  select 'alter database clear logfile group '||group#||';' code_to_run from v$log order by group#; ## run the output

  • 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: r12 - 2008-11-18 - 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