Oracle 11.2.0.4.0 Dataguard部署和日常維護(6)-Dataguard Snapshot

標籤：測試   --   sysaux   connect   4.0   shu   ada   dbf   user   

1. 檢查當前主備庫同步狀態

• on primary

select ads.dest_id,max(sequence#) "Current Sequence",           max(log_sequence) "Last Archived"       from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads       where ad.dest_id=al.dest_id       and al.dest_id=ads.dest_id       and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )       group by ads.dest_id;DEST_ID Current Sequence Last Archived------- ---------------- -------------      1           73        73      2           73        74

• on standby

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"      from (select thread# thrd, max(sequence#) almax          from v$archived_log          where resetlogs_change#=(select resetlogs_change# from v$database)          group by thread#) al,         (select thread# thrd, max(sequence#) lhmax          from v$log_history          where resetlogs_change#=(select resetlogs_change# from v$database)          group by thread#) lh     where al.thrd = lh.thrd;  Thread Last Seq Received Last Seq Applied---------- ----------------- ----------------         1               73              73

2. standby配置閃回日誌

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            big integer                      4G

3. standby停止應用日誌

alter database recover managed standby database cancel;

4. standby轉換為snapshot standby

alter database convert to snapshot standby;

5. 將standby啟動到open狀態

alter database open; select DATABASE_ROLE,name,OPEN_MODE from v$database;DATABASE_ROLE        NAME                       OPEN_MODE-------------------- --------------------------- ---------------SNAPSHOT STANDBY     USERDATA                    READ WRITE

6. 對snapshot standby資料庫進行壓力測試或者Real Application Testing(RAT)或者其他讀寫操作

create tablespace usertbs datafile ‘/u01/app/oracle/oradata/userdata/usertbs01.dbf‘ size 50m;select file_name from dba_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. 測試結束後,將snapshot standby轉換為physical standby,並且重新開始應用日誌

shutdown immediate;startup mount;alter database convert to physical standby;shutdown immediate;startup mount;alter database recover managed standby database using current logfile disconnect from session;select DATABASE_ROLE,name,OPEN_MODE from v$database;DATABASE_ROLE         NAME                              OPEN_MODE-------------------- ---------------------------------- ---------------PHYSICAL STANDBY     USERDATA                           MOUNTED

8. 檢查primary庫和standby庫的日誌是同步的

• on primary

alter system archive log current;select ads.dest_id,max(sequence#) "Current Sequence",           max(log_sequence) "Last Archived"       from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads       where ad.dest_id=al.dest_id       and al.dest_id=ads.dest_id       and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )       group by ads.dest_id;DEST_ID Current Sequence Last Archived------- ---------------- -------------      1           78        78      2           78        79

•  on standby

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"      from (select thread# thrd, max(sequence#) almax          from v$archived_log          where resetlogs_change#=(select resetlogs_change# from v$database)          group by thread#) al,         (select thread# thrd, max(sequence#) lhmax          from v$log_history          where resetlogs_change#=(select resetlogs_change# from v$database)          group by thread#) lh     where al.thrd = lh.thrd;    Thread Last Seq Received Last Seq Applied---------- ----------------- ----------------     1          78           78

Oracle 11.2.0.4.0 Dataguard部署和日常維護(6)-Dataguard Snapshot