Oracle dataguard failover 实战

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;