1. 设置备库的闪回目录
show parameter db_recovery_file; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------------------- db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4G
2. 开启备库的闪回功能
select flashback_on from v$database; FLASHBACK_ON ------------------------------------------------------ NO alter database flashback on; alter database flashback on * ERROR at line 1: ORA-01153: an incompatible media recovery is active alter database recover managed standby database cancel; alter database flashback on; select flashback_on from v$database; FLASHBACK_ON ------------------------------------------------------ YES
alter database recover managed standby database using current logfile disconnect from session;
3. 检查主备库同步状态
- 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 79 79 2 79 80
- 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 79 79
4. 取消备库的恢复进程
select process,status from v$managed_standby; PROCESS STATUS ------------------- ------------------------------------ ARCH CLOSING ARCH CLOSING ARCH CONNECTED ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE MRP0 APPLYING_LOG alter database recover managed standby database cancel; select process,status from v$managed_standby; PROCESS STATUS ------------------- ------------------------------------ ARCH CLOSING ARCH CLOSING ARCH CONNECTED ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE
5. 创建备库的还原点
create restore point before_open_standby guarantee flashback database; select name from v$restore_point; NAME -------------------------------------------------- BEFORE_OPEN_STANDBY
6. 在主库归档日志
alter system archive log current;
7. 确认备库已经归档了最新的日志
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 80 79
8. 延迟指向被激活的备库的日志归档目的地
show parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ log_archive_dest_state_2 string ENABLE alter system set log_archive_dest_state_2='DEFER'; show parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ log_archive_dest_state_2 string DEFER
9. 激活备库
alter database activate standby database; alter database open; select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE ------------------------------------------------------------ ------------------------------------------------ READ WRITE PRIMARY
10. 向备库写入测试数据
begin for i in 1..10000 loop insert into test10 values (i,'shall'); end loop; commit; end; / PL/SQL procedure successfully completed. select count(*) from test10; COUNT(*) ---------- 10000
11. 将备库闪回至还原点
shutdown immediate; startup mount; flashback database to restore point before_open_standby; alter database convert to physical standby; shutdown immediate; startup mount; alter database recover managed standby database using current logfile disconnect from session;
12. 重新启用到备库的日志归档目的地
alter system set log_archive_dest_state_2='ENABLE'; show parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ log_archive_dest_state_2 string ENABLE
13. 测试数据同步是否正常
- on primary
alter system archive log current; 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 83 83 2 83 84
- 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 83 83
14. 清理闪回点
select name from v$restore_point; NAME -------------------------------------------------- BEFORE_OPEN_STANDBY drop restore point BEFORE_OPEN_STANDBY;