db1 –liu
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
db2 –yang
SQL> select * from v$archive_gap;
no rows selected
SQL>
第一种情况 正常的failover
phsical standby :
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL>
Wed Jun 27 13:47:15 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Wed Jun 27 13:47:15 2012
Terminal Recovery: Stopping real time apply
Wed Jun 27 13:47:15 2012
MRP0: Background Media Recovery cancelled with status 16037
Wed Jun 27 13:47:15 2012
Errors in file /u01/app/oracle/admin/liu/bdump/liu_mrp0_10260.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 456564
Wed Jun 27 13:47:16 2012
Errors in file /u01/app/oracle/admin/liu/bdump/liu_mrp0_10260.trc:
ORA-16037: user requested cancel of managed recovery operation
Wed Jun 27 13:47:16 2012
MRP0: Background Media Recovery process shutdown (liu)
Wed Jun 27 13:47:16 2012
Terminal Recovery: Stopped real time apply
Wed Jun 27 13:47:16 2012
Attempt to do a Terminal Recovery (liu)
Wed Jun 27 13:47:16 2012
Media Recovery Start: Managed Standby Recovery (liu)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Media Recovery Waiting for thread 1 sequence 2 (in transit)
Terminal Recovery timestamp is ’06/27/2012 13:47:18′
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 2 redo required
Terminal Recovery: /u01/app/oracle/oradata/liu/redo05.log
Identified End-Of-Redo for thread 1 sequence 2
Wed Jun 27 13:47:18 2012
Incomplete recovery applied all redo ever generated.
Recovery completed through change 456566
Wed Jun 27 13:47:18 2012
Media Recovery Complete (liu)
Terminal Recovery: successful completion
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Resetting standby activation ID 2612058581 (0x9bb0d9d5)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
db2 作为主库
[oracle@yang ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jun 27 14:01:34 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
—————————————-
479312
SQL>
DB1 作为备库 flashback
SQL>
FLASHBACK DATABASE TO SCN 479312SQL>
2 ;
Flashback complete.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
继续开启apply
ttempt to start background Managed Standby Recovery process (liu)
MRP0 started with pid=20, OS id=22199
Wed Jun 27 14:52:31 2012
MRP0: Background Managed Standby Recovery process started (liu)
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u01/arch/1_10_787062936.dbf
Media Recovery Log /u01/arch/1_11_787062936.dbf
Media Recovery Log /u01/arch/1_12_787062936.dbf
Wed Jun 27 14:52:37 2012
Completed: alter database recover managed standby database disconnect
Wed Jun 27 14:52:38 2012
Media Recovery Log /u01/arch/1_13_787062936.dbf
Media Recovery Log /u01/arch/1_14_787062936.dbf
Media Recovery Waiting for thread 1 sequence 15 (in transit)
应用正常
第二种情况 active database 的 failover
此处模拟网络断开
SQL> create table failover(id int);
Table created.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system switch logfile;
System altered.
这个归档不会传输过去
physical standby 操作：
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL>
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> recover standby database until cancel;
ORA-00279: change 500198 generated at 06/27/2012 14:57:52 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_16_787062936.dbf
ORA-00280: change 500198 for thread 1 is in sequence #16
Specify log: {
cancel;
ORA-00308: cannot open archived log ‘cancel;’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database activate standby database;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 150995216 bytes
Database Buffers 364904448 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
—————————————-
500196
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY
SQL>
已经切换为primary
Resetting resetlogs activation ID 2612053838 (0x9bb0c74e)
Online log /u01/app/oracle/oradata/liu/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/liu/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/liu/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 500196
此时发生了resetlogs 操作
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
此时我们看一下原来的primary 端：
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 150995216 bytes
Database Buffers 364904448 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> flashback database to scn 500196;
Flashback complete.
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 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 150995216 bytes
Database Buffers 364904448 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 150995216 bytes
Database Buffers 364904448 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
看一下standby 端的log :
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/arch
Redo Shipping Client Connected as PUBLIC
— Connected User is Valid
RFS LogMiner: Client disabled from further notification
RFS[1]: New Archival REDO Branch(resetlogs_id): 787074684 Prior: 787074091
RFS[1]: Archival Activation ID: 0x9bb07de2 Current: 0x0
RFS[1]: Effect of primary database OPEN RESETLOGS
—
RFS[1]: Assigned to RFS process 23110
RFS[1]: Identified database type as ‘physical standby’
Wed Jun 27 15:05:38 2012
RFS LogMiner: Client disabled from further notification
RFS[1]: Archived Log: ‘/u01/arch/1_1_787071479.dbf’
New incarnation branch detected in ArchiveLog, filename /u01/arch/1_1_787071479.dbf
Inspection of file changed rdi from 2 to 3
Setting recovery target incarnation to 3
incarnation 自动进行了切换
Wed Jun 27 15:05:40 2012
RFS[1]: Incarnation entry added for Branch(resetlogs_id): 787071479 (liu)
Wed Jun 27 15:05:40 2012
Setting recovery target incarnation to 3
Wed Jun 27 15:05:40 2012
MRP0: Incarnation has changed! Retry recovery…
重置之后开始recover
Wed Jun 27 15:05:40 2012
Errors in file /u01/app/oracle/admin/liu/bdump/liu_mrp0_23056.trc:
ORA-19906: recovery target incarnation changed during recovery
Recovery interrupted!
Wed Jun 27 15:05:40 2012
Errors in file /u01/app/oracle/admin/liu/bdump/liu_mrp0_23056.trc:
ORA-19906: recovery target incarnation changed during recovery
Wed Jun 27 15:06:00 2012
Managed Standby Recovery not using Real Time Apply
Media Recovery apply resetlogs offline range for datafile 1, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 2, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 3, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 4, incarnation : 0
开始resetlog
Media Recovery Log /u01/arch/1_1_787071479.dbf
Media Recovery Waiting for thread 1 sequence 2
Fetching gap sequence in thread 1, gap sequence 2-14
开始fetching gap ;
我们在两个服务器上查看 table failover
SQL> desc failover;
ERROR:
ORA-04043: object test_liu does not exist
丢失了这张表，也就是说发生故障的scn 点直至failover 完成之后的数据 将全部丢失
总结一下：
1. 正常情况的failover 切换之后主备角色发生变化 DG集群中的其他standby端 可以不受到影响
2. 非常规情况下的failover (active database) 需要发生media recover,open需要resetlogs DG集群中的所有备库需要flashback到 STANDBY_BECAME_PRIMARY_SCN 点的SCN 将会丢失数据
3. 两种情况下 都需要打开flashback log
