Oracle 11.2.0.4.0 Dataguard部署和日常维护(6)-Dataguard Snapshot篇

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停止应用日志(此时备库的闪回处于关闭状态)

select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
NO

alter database recover managed standby database cancel;

4. standby转换为snapshot standby

alter database convert to snapshot standby;

select flashback_on from v$database;      #convert to snapshot standby命令相当于创建了一个担保还原点,这和使用担保还原点(Guaranteed Restore Points)状态类似
FLASHBACK_ON
------------------------------------------------------
RESTORE POINT ONLY

select NAME from V$RESTORE_POINT;

NAME
--------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_07/06/2017 06:02:50

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