Oracle 11.2.0.4.0 Dataguard Deployment and routine maintenance (6)-dataguard Snapshot

1. Check the current master standby sync status

• On primary

SelectADS.DEST_ID,Max(sequence#) " CurrentSequence ",Max(log_sequence) "Last Archived" fromV$archived_log al, v$archive_dest ad, V$archive_dest_status adswheread.dest_id=al.dest_id andal.dest_id=ads.dest_id andal.resetlogs_change#=(Select Max(resetlogs_change#) fromV$archived_log)Group  byads.dest_id;dest_id CurrentSequence last archived------- ---------------- -------------      1            the         the      2            the         About

• On standby

Selectal.thrd "Thread", Almax "last seq Received", Lhmax "last seq Applied" from(Selectthread# THRD,Max(sequence#) Almax fromV$archived_logwhereresetlogs_change#=(Selectresetlogs_change# fromv$Database)          Group  bythread#) Al, (Selectthread# THRD,Max(sequence#) Lhmax fromv$log_historywhereresetlogs_change#=(Selectresetlogs_change# fromv$Database)          Group  bythread#) LHwhereAl.thrd=LH.THRD; Thread last seq Received last seq Applied---------- ----------------- ----------------         1                the               the

2. Standby Configuring the Flashback log

Show Parameter Db_recovery_file_dest;name                     TYPE                              VALUE------------------------------------------------------------------------------ -------------------------db_recovery_file_dest                 string                           /u01/app/Oracle / fast_recovery_area                                   db_recovery_file_dest_size            integer                      4G

3. Standby Stop Application Log

Alter Database database Cancel;

4. Convert Standby to snapshot standby

Alter Database Convert  to snapshot standby;

5. Boot the standby to the open state

Alter Database Open  Select from v$database;D atabase_role        NAME                       open_mode--  ------------------------------------------------------------SNAPSHOT STANDBY     USERDATA                    READ WRITE

6. Stress test on snapshot standby database or real application testing (RAT) or other read and write operations

CreateTablespace Usertbs datafile'/u01/app/oracle/oradata/userdata/usertbs01.dbf'size 50m;Select file_name  fromDba_data_files;file_name------------------------------------------------------------/U01/App/Oracle/Oradata/UserData/users01.dbf/U01/App/Oracle/Oradata/UserData/undotbs01.dbf/U01/App/Oracle/Oradata/UserData/sysaux01.dbf/U01/App/Oracle/Oradata/UserData/system01.dbf/U01/App/Oracle/Oradata/UserData/Usertbs01.dbf

7. After the test is finished, convert the snapshot standby to physical standby and restart the application log

shutdownImmediate;startup Mount;Alter Database Convert  tophysical standby;shutdownImmediate;startup Mount;Alter DatabaseRecover managed StandbyDatabaseUsing CurrentLogFile Disconnect fromsession;SelectDatabase_role,name,open_mode fromv$Database;D atabase_role NAME open_mode-------------------- ---------------------------------- ---------------Physical STANDBY USERDATA Mounted

8. Check that the logs for the primary library and the standby library are synchronized

• On primary

AlterSystem ArchiveLog  Current;SelectADS.DEST_ID,Max(sequence#) " CurrentSequence ",Max(log_sequence) "Last Archived" fromV$archived_log al, v$archive_dest ad, V$archive_dest_status adswheread.dest_id=al.dest_id andal.dest_id=ads.dest_id andal.resetlogs_change#=(Select Max(resetlogs_change#) fromV$archived_log)Group  byads.dest_id;dest_id CurrentSequence last archived------- ---------------- -------------      1            +         +      2            +         -

• On standby

Selectal.thrd "Thread", Almax "last seq Received", Lhmax "last seq Applied" from(Selectthread# THRD,Max(sequence#) Almax fromV$archived_logwhereresetlogs_change#=(Selectresetlogs_change# fromv$Database)          Group  bythread#) Al, (Selectthread# THRD,Max(sequence#) Lhmax fromv$log_historywhereresetlogs_change#=(Selectresetlogs_change# fromv$Database)          Group  bythread#) LHwhereAl.thrd=LH.THRD; Thread last seq Received last seq Applied---------- ----------------- ----------------     1           +            +

Oracle 11.2.0.4.0 Dataguard Deployment and routine maintenance (6)-dataguard Snapshot