DataGuard Failover之后将原主库恢复成新的备库
一. 使用闪回数据库功能实现
1. 前提条件:
a)闪回数据库功能在failover之前必须是启用的
b)必须保留有足够的闪回日志可以恢复到切换之前的SCN
2. 测试前检查:
a)检查主库闪回功能是否开启:
SQL> select flashback_on,protection_mode from v$database;
FLASHBACK_ON PROTECTION_MODE
------------------ --------------------
YES MAXIMUM PERFORMANCE
b)检查主备库是否同步:
SQL> SELECT MAX(al.SEQUENCE#) "Last Seq Recieved", MAX(lh.SEQUENCE#) "Last Seq Applied"
FROM v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
349 349
主备库结果一致
c)插入测试数据
insert into emtest.test1 values(1,to_char(sysdate,'yyyymmdd hh24:mi:ss'));
commit;
alter system switch logfile; --此处切换一次日志确保备库已经同步
再插入一条数据,但是不切换REDO(因为测试环境并没有使用lgwr sync方式同步,因此redo数据没有马上应用到备库)
insert into emtest.test1 values(2,to_char(sysdate,'yyyymmdd hh24:mi:ss'));
commit;
检查主库数据:
SQL> select * from emtest.test1;
ID1 VAR1
---------- ---------------------
1 20161225 08:16:54
2 20161225 08:21:01
检查备库数据:
SQL> select * from emtest.test1;
ID1 VAR1
---------- ---------------------
1 20161225 08:16:54
3. 在备库执行FAILOVER切换
a)检查备库当前状态
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- ---------------- --------------------
RHORCL rhorclstd PHYSICAL STANDBY READ ONLY WITH APPLY
b)模拟主库宕机
SQL> shutdown abort;
ORACLE instance shut down.
c)切换前同步
由于宕机之前主备库的数据存在差异,意味着有数据并没有完成同步,如果此时主库能够启动到MOUNT状态,可以读取主库的控制文件,然后进行日志flush,最大可能性的避免数据丢失
SQL> startup mount
SQL> alter system flush redo to 'rhorclstd';
从alertlog中可以看到LGWR进程被调度将redo数据归档到备库,同时确认备库是否应用完成
Flush Redo: Primary highest seen SCN set to 0x0.0x69813c
ARCH: Noswitch archival of thread 1, sequence 351
ARCH: End-Of-Redo Branch archival of thread 1 sequence 351
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 351 for destination LOG_ARCHIVE_DEST_2
Flush End-Of-Redo Log thread 1 sequence 351
Archived Log entry 42 added for thread 1 sequence 351 ID 0xf0c58634 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will wait for rhorclstd standby to have applied all redo
Final check for a target standby that has recovered all redo.
再次检查备库数据,发现数据同步完成:
SQL> select * from emtest.test1;
ID1 VAR1
---------- ---------------------
1 20161225 08:16:54
2 20161225 08:21:01
d)FAILOVER切换
SQL> alter database recover managed standby database finish;
Database altered.
SQL> select open_mode, switchover_status from v$database;
OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY TO PRIMARY
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
RHORCL rhorclstd PRIMARY MOUNTED
SQL> alter database open;
Database altered. --切换成功
SQL> insert into emtest.test1 values(3,to_char(sysdate,'yyyymmdd hh24:mi:ss'));
1 row created.
SQL> commit;
Commit complete. --插入数据测试成功
4. 使用Flashback重建新备库
a)获取备库切换成主库时的SCN号,在备库执行:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
6914362
b)闪回原主库到standby_became_primary_scn
SQL> startup mount
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 452988904 bytes
Database Buffers 788529152 bytes
Redo Buffers 8892416 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 6914362;
Flashback complete.
c)将原主库转换为备库
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 452988904 bytes
Database Buffers 788529152 bytes
Redo Buffers 8892416 bytes
Database mounted.
d)确认新主库配置归档参数到新备库(通常原DG环境已有该配置),在新主库执行:
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string SERVICE=rhorcl LGWR ASYNC VALI
D_FOR=(ONLINE_LOGFILES,PRIMARY
_ROLE) DB_UNIQUE_NAME=rhorcl
SQL> show parameter dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string enable
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
DEST_ID STATUS ERROR
---------- --------- -----------------------------------------------------------------
2 VALID
e)在新备库启动日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
f)检查主备库同步状态
SQL> select process, status, thread#, sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CLOSING 1 4
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 1 3
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 1 5
MRP0 WAIT_FOR_LOG 1 5
8 rows selected.
二. 使用RMAN备份恢复实现
1. 前提条件:
a)存在所有数据文件的RMAN备份
b)存在standby controlfile,如果没有也可以从新主库创建。
2. 操作步骤(与第一部分雷同,简要介绍)
SQL> select to_char(STANDBY_BECAME_PRIMARY_SCN) from v$database;
RMAN> DELETE ARCHIVELOG FROM SCN STANDBY_BECAME_PRIMARY_SCN;
RMAN> RESTORE DATABASE;
SQL> startup mount
SQL> recover managed standby database using current logfile through all switchover disconnect;
原理都是一样的,需要把原主库退回到切换时间点对应的SCN(STANDBY_BECAME_PRIMARY_SCN)的状态,然后再进行日志恢复。
以上测试环境为Linux 6.5+ORACLE 11.2.0.4
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21687111/viewspace-2131356/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21687111/viewspace-2131356/