物理STANDBY的FAILOVER切换(失败切换)

1.类型

   正常切换 switchover

   失败切换 failover

   强行切换 active

2.下文是failover的方法

  

注意点: 对物理STANDBY，尽量不要用ACTIVATE STANDBY DATABASE，否则容易造成数据的丢失。

 

物理STANDBY的FAILOVER切换: 会把当前的一个物理STANDBY切换为PRIMARY数据库。

 

 

由于PRIMARY数据库已经无法启动，所以FAILOVER切换所需的条件并不多，

只要检查STANDBY是否运行在最大保护模式下，如果是的话，需要将其置为最大性能模式，否则切换到PRIMARY角色也无法启动。

 

为了模拟FAILOVER的特点，首先在主库上运行一个比较大的事务，然后通过SHUTDOWN ABORT来关闭PRIMARY数据库：

[oracle@yangtk ~]$ sqlplus yangtk/yangtk

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 5 13:11:14 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE T_FAILOVER (ID NUMBER);

 

SQL> DECLARE
2 V_JOB NUMBER;
3 BEGIN
4 DBMS_JOB.SUBMIT(V_JOB,
5 'BEGIN FOR I IN 1..10000000 LOOP INSERT INTO T_FAILOVER VALUES (I); COMMIT; END LOOP; END;',
6 SYSDATE);
7 COMMIT;
8 END;
9 /

 

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

SQL> CONN / AS SYSDBA

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

SQL> SHUTDOWN ABORT

 

 

 

下面登陆STANDBY数据库：

[oracle@yangtk2 ~]$ sqlplus "/ as sysdba"

 

 

1. SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

   no rows selectd

检查是否存在GAP，如果存在拷贝相应的归档到STANDBY数据库，并注册，再次执行上面的查询，直到上面的查询不返回记录。

在PRIMARY数据库和其他的数据库中检查是否存在本地没有的归档文件，拷贝并注册这些文件：

 

SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;===standby 上执行===

THREAD# LAST
---------- ----------
1 228

把所有的归档都拷贝到STANDBY站点，使用ALTER DATABASE REGISTER PHYSICAL LOGFILE语句进行注册，然后停止应用归档：

2.sql>alter database register logfile '/u01/oradata/archive/1_87.dbf';

    3.SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

 

下面将STANDBY数据库切换为PRIMARY数据库：

4 .SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

       可选参数[force | wait | nowait ]

 

5.SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

6.SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

 

7.SQL> ALTER DATABASE OPEN;

 

检查数据库是否已经切换成功：

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PRIMARY

至此，FAILOVER切换完成。这个时候应该马上对新的PRIMARY数据库进行备份。

 

归档是否都拷贝到本地，和日志传输的方式，决定FAILOVER过程是否会丢失数据。这个例子中FAILOVER切换没有造成数据的丢失：

SQL> SELECT COUNT(*) FROM YANGTK.T_FAILOVER;

COUNT(*)
----------
77880

重启原来的PRIMARY数据库：

SQL> STARTUP
ORACLE instance started.

Total System Global Area 267825152 bytes
Fixed Size 1299316 bytes
Variable Size 167775372 bytes
Database Buffers 96468992 bytes
Redo Buffers 2281472 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM YANGTK.T_FAILOVER;

COUNT(*)
----------
77880

 

补充1：===========如何应用redo log=========重要=====================

模拟的场景是primary库损坏，redo log还存在(通常redo会做冗余，如果没有了redo log就更省事了，但是会丢数据)

 

4.2 recover standby

-----
注：register redo log 是不可行的：
SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oradata/dgtest/redo01.log';
ALTER DATABASE REGISTER LOGFILE '/u01/oradata/dgtest/redo01.log'
*
ERROR at line 1:
ORA-16088: archive log has not been completely archived
-----

下面的顺序可以从primary的v$logfile里查到，sequence（primary dwon了肯定查不到，但是可以试，下面只列出了正确的顺序）

SQL> recover standby database until cancel;
ORA-00279: change 562426 generated at 05/20/2008 17:28:10 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dgarch/1_22_654889856.dbf
ORA-00280: change 562426 for thread 1 is in sequence #22

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/dgtest/redo03.log
ORA-00279: change 563485 generated at 05/20/2008 18:24:18 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dgarch/1_23_654889856.dbf
ORA-00280: change 563485 for thread 1 is in sequence #23
ORA-00278: log file '/u01/oradata/dgtest/redo03.log' no longer needed for this
recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/dgtest/redo01.log
ORA-00279: change 583521 generated at 05/21/2008 10:42:37 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dgarch/1_24_654889856.dbf
ORA-00280: change 583521 for thread 1 is in sequence #24
ORA-00278: log file '/u01/oradata/dgtest/redo01.log' no longer needed for this
recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/dgtest/redo02.log
Log applied.
Media recovery complete.

 

recovery 完成之后就要failover了。

 

补充2：=======================重要===================

biti_rainy:
1 你在standby 上 手工 recover  database until  cancel
然后以手工方式 指定 file ，把 redo  log 当作 archive 输入应用
就好了

2 其实，将standby的控制文件重新建立，就跟正常数据库备份与恢复是一样的了

 

3 如果是拷贝主站点的redo  logfile 到备站点上应用，那还谈什么 failover

  max  performance 下standby本来就没有联机日志，何来替换一说？  直接拷贝过来应用恢复就可以

 

 max  performance 下standby本来就没有联机日志

=ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; ==会创建online redo log，根据controlfile

piner:

注册只是为了能自动恢复，其实最后阶段可以手工恢复，不注册也可以的

最终目的就是为了最大程度的减少数据丢失，另外一点你可能混淆了，备用端是没有redo log的，只有standby log，他们是不同的概念，也是不同的文件。

如果能完全拷贝到主数据库的联机日志，那么可能不恢复standby log的内容，

如果拷贝不到的话，你可以选择恢复standby log的内容，这样数据的损失量会比较小。

 

 

 

 

 

补充3：=======================重要===================

Step 1 Identify and resolve any gaps in the archived redo log files.

To determine if there are gaps in the archived redo log files on the target standby database, query the V$ARCHIVE_GAP view.

The V$ARCHIVE_GAP view contains the sequence numbers of the archived redo log files that are known to be missing for each thread. The data returned reflects the highest gap only.

For example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
In this example the gap comprises archived redo log files with sequences 90, 91, and 92 for thread 1. If possible, copy all of the identified missing archived redo log files to the target standby database from the primary database and register them. This must be done for each thread.

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

STEP 2 Copy any other missing archived redo log files.

To determine if there are any other missing archived redo log files, query the V$ARCHIVED_LOG view on the target standby database to obtain the highest sequence number for each thread.

For example:

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

THREAD LAST
---------- ----------
1 100

Copy any available archived redo log files from the primary database that contains sequence numbers higher than the highest sequence number available on the target standby database to the target standby database and register them. This must be done for each thread.

step 1是找 GAP ,也就是中间缺失的
step 2是找最后的。

比如说，standby处有seq=1,2,4,5,6
那么第一步找出 seq=3的archive log
第二步去看看是否有seq=7,8,9……的archive log