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------------------------------------------------------NOalter 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 ONLYselect 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