Oracle dataguard failover 实战
操作步骤
备库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
添加临时文件,删除老的临时文件
附表
具体操作和alert日志如下:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
备库alert
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Tue Apr 9 10:55:24 2013
Terminal Recovery: request posted (orcl) ---备库实例
Tue Apr 9 10:55:27 2013
Terminal Recovery timestamp is '04/09/2013 10:55:27' ---恢复时间戳
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 29879 redo required
Terminal Recovery: /opt/oracle/oradata/research/redo04.log ---standby redo log
Identified End-Of-Redo for thread 1 sequence 29879
Tue Apr 9 10:55:28 2013
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1900634393
Tue Apr 9 10:55:28 2013
MRP0: Media Recovery Complete (orcl)
Terminal Recovery: successful completion
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Resetting standby activation ID 2055592130 (0x7a85d8c2)
Tue Apr 9 10:55:32 2013
Waiting for MRP0 pid 1241 to terminate
Waiting for MRP0 pid 1241 to terminate
Waiting for MRP0 pid 1241 to terminate
Tue Apr 9 10:55:34 2013
MRP0: Background Media Recovery process shutdown (orcl)
Tue Apr 9 10:55:35 2013
Terminal Recovery: completion detected (orcl)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
主库alert
ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC0]: Error 3113 when pinging standby orcl.
Tue Apr 9 10:36:23 2013
Errors in file /opt/oracle/admin/research/bdump/research_lns1_8489.trc:
ORA-03113: end-of-file on communication channel
Tue Apr 9 10:36:23 2013
LGWR: I/O error 3113 archiving log 1 to 'orcl'
主库
SQL> alter system set log_archive_dest_state_2='defer';
System altered
备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Tue Apr 9 11:08:41 2013
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl) ---orcl实例成为PRIMARY
Tue Apr 9 11:08:41 2013
If media recovery active, switchover will wait 900 seconds
Standby terminal recovery start SCN: 1900632860
SwitchOver after complete recovery through change 1900634393
Tue Apr 9 11:08:41 2013
Errors in file /opt/oracle/admin/research/udump/orcl_ora_4506.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/research/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Apr 9 11:08:42 2013
Errors in file /opt/oracle/admin/research/udump/orcl_ora_4506.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/research/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Apr 9 11:08:43 2013
Errors in file /opt/oracle/admin/research/udump/orcl_ora_4506.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/research/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Standby became primary SCN: 1900632859
Tue Apr 9 11:08:44 2013
Switchover: Complete - Database shutdown required (orcl)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Tue Apr 9 11:09:12 2013
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Tue Apr 9 11:09:22 2013
ARCH shutting down
ARC0: Archival stopped
Tue Apr 9 11:09:27 2013
ARCH shutting down
ARC1: Archival stopped
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Tue Apr 9 11:10:33 2013
Shutting down instance: further logons disabled
Tue Apr 9 11:10:33 2013
Stopping background process MMNL
Tue Apr 9 11:10:33 2013
Stopping background process MMON
Tue Apr 9 11:10:34 2013
Shutting down instance (immediate)
License high water mark = 10
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Tue Apr 9 11:10:36 2013
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2022144 bytes
Variable Size 1526727936 bytes
Database Buffers 603979776 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
备库添加临时文件,删除老的临时文件
alter tablespace temp add tempfile xxxxx size 1G autoextend on maxsize 10G;
alter tablespace doudou add tempfile xxxbb size 1G autoextend on maxsize 10G;
shutdown immediate;
startup;
alter database tempfile '/xxxx/temp01.dbf' drop including datafiles;
alter database tempfile '/xxxx/doudou01.dbf' drop including datafiles;