ORACLE10G的FAST-START FAILOVER配置

FAST-START FAILOVER是ORACLE10G的一项新功能。这个功能可以实现当主库宕机时，预定的从库自动快速可靠地进行失败切换(FAILOVER)。切换完成之后，原来的主库恢复正常之后，将会自动地配置为从库。这的确是一项令DBA心动的功能，大大减少了DBA的维护和管理工作。尤其是减少了在出现突然问题时的心慌意乱和手忙脚乱。
以前我也没有做过此配置，今晚正好有心情，就简单做了个配置实验。

1 设置保护模式（有DG BROKER配置必须使用此模式）
主库中：
SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION MAXIMUM PROTECTION

SQL> Alter database set standby database to maximize Availability;

数据库已更改。

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

2 设置FLASHBACK
主从库中均需设置
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             264244100 bytes
Database Buffers          339738624 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。

配置FLASHBACK参数
SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 0
SQL> alter system set db_recovery_file_dest_size=5120m scope=both;

系统已更改。

SQL> alter system set db_recovery_file_dest='D:\oracle\oradata\mystock\flash_recovery_area' scope=both;

系统已更改。

SQL> Alter database flashback on;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

3 连接字符串配置

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

MYSTOCK =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ca)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mystock)
    )
)

MYTEST =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ca)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mytest)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

4 配置FAST-START FAILOVER

DGMGRL> connect sys/oracle@mystock
已连接。
DGMGRL> show configuration

Configuration
Name:                mystock_primary
Enabled:             NO
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
    mystock - Primary database
    mytest - Physical standby database

"mystock_primary" 的当前状态:
DISABLED

DGMGRL> enable configuration
已启用。
DGMGRL> show configuration

Configuration
Name:                mystock_primary
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
    mystock - Primary database
    mytest - Physical standby database

"mystock_primary" 的当前状态:
警告: ORA-16607: 一个或多个数据库出现故障

出现这个错误请检查主从库是否都配置了DG_BROKER_START=TRUE，我这里出错原因是从库没有配置；修正后，

DGMGRL> connect sys/oracle@mystock
已连接。
DGMGRL> show configuration

Configuration
Name:                mystock_primary
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: DISABLED
Databases:
    mystock - Primary database
    mytest - Physical standby database

"mystock_primary" 的当前状态:
SUCCESS

DGMGRL>

主库：
DGMGRL> show database verbose 'mystock';

Database
Name:            mystock
Role:            PRIMARY
Enabled:         YES
Intended State: ONLINE
Instance(s):
    mystock

Properties:
    InitialConnectIdentifier        = 'mystock'
    LogXptMode                      = 'ARCH'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'CA'
    SidName                         = 'mystock'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=CA)(PORT=1521))'
    StandbyArchiveLocation          = 'D:\oracle\oradata\mystock\arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'arc_%t_%s_%r.log'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

"mystock" 的当前状态:
SUCCESS

DGMGRL> alter database 'mystock' set property LogXptMode = 'SYNC';
alter database 'mystock' set property LogXptMode = 'SYNC';
^
在 "database" 处或之前出现语法错误
DGMGRL> alter resource 'mystock' set property LogXptMode = 'SYNC';
错误: ORA-16789: 备用重做日志缺失

失败。
DGMGRL>

此错误原因是在主库没有添加STANDBY REDO LOG，添加后，错误消失
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
2 ('D:\ORACLE\ORADATA\mystock\REDO05.LOG') SIZE 50M;

数据库已更改。

SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
2 ('D:\ORACLE\ORADATA\mystock\REDO06.LOG') SIZE 50M;

数据库已更改。

SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
2 ('D:\ORACLE\ORADATA\mystock\REDO07.LOG') SIZE 50M;

数据库已更改。

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
2 ('D:\ORACLE\ORADATA\mystock\REDO04.LOG') SIZE 50M;

数据库已更改。

DGMGRL> connect sys/oracle@mystock
已连接。
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show database verbose 'mystock';

Database
Name:            mystock
Role:            PRIMARY
Enabled:         YES
Intended State: ONLINE
Instance(s):
    mystock

Properties:
    InitialConnectIdentifier        = 'mystock'
    LogXptMode                      = 'ARCH'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'CA'
    SidName                         = 'mystock'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=CA)(PORT=1521))'
    StandbyArchiveLocation          = 'D:\oracle\oradata\mystock\arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'arc_%t_%s_%r.log'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

"mystock" 的当前状态:
SUCCESS

确认两个库的LogXptMode属性均为'SYNC',不是的改正之。
DGMGRL> alter resource 'mystock' set property LogXptMode = 'SYNC';
已更新属性 "logxptmode"
DGMGRL>

5 enable fast_start failover

DGMGRL> enable fast_start failover;
已启用。
DGMGRL>

在日志D:\oracle\product\10.2.0\admin\mystock\bdump\drcmystock.log中有如下日志：

DG 2009-04-14-22:09:10        0 2 684194694 DMON: Fast-Start Failover (FSFO) has been enabled between:
DG 2009-04-14-22:09:10        0 2 684194694   Primary = "mystock"
DG 2009-04-14-22:09:10        0 2 684194694   Standby = "mytest"
DG 2009-04-14-22:09:10        0 2 684194694 DMON: EDIT_DRC: success

同样在日志D:\oracle\product\10.2.0\admin\mytest\bdump\drcmytest.log中有如下日志：

DG 2009-04-14-22:09:08        0 2 0 drcx: FSFO storing state flags=0x101, version=1, bid=0x0 (0), threshold=30
DG 2009-04-14-22:09:08        0 2 0 drcx: FSFO storing state flags=0x101, version=1, bid=0x657078e5 (1701869797), threshold=30
DG 2009-04-14-22:09:10        0 2 0 drcx: FSFO storing state flags=0x103, version=2, bid=0x657078e5 (1701869797), threshold=30
DG 2009-04-14-22:09:12        0 2 0 DRCX: Start receiving metadata file: D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\DR1MYTEST.DAT
DG 2009-04-14-22:09:12        0 2 0 DRCX: Receiving block #1, 86 blocks.
DG 2009-04-14-22:09:12        0 2 0 DRCX: End receiving metadata file: opcode EDIT_DRC
DG 2009-04-14-22:09:12        0 2 684194694 DMON: chief lock convert for resync

DGMGRL> show configuration verbose;

Configuration
Name:                mystock_primary
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: ENABLED
Databases:
    mystock - Primary database
    mytest - Physical standby database
            - Fast-Start Failover target

Fast-Start Failover
Threshold: 30 seconds
Observer: (none)

"mystock_primary" 的当前状态:
警告: ORA-16608: 一个或多个数据库出现警告

DGMGRL>

DGMGRL> show database verbose 'mystock'

Database
Name:            mystock
Role:            PRIMARY
Enabled:         YES
Intended State: ONLINE
Instance(s):
    mystock

Properties:
    InitialConnectIdentifier        = 'mystock'
    LogXptMode                      = 'SYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = 'mytest'
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'CA'
    SidName                         = 'mystock'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=CA)(PORT=1521))'
    StandbyArchiveLocation          = 'D:\oracle\oradata\mystock\arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'arc_%t_%s_%r.log'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

"mystock" 的当前状态:
警告: ORA-16819: 未启动快速启动故障转移观察器

DGMGRL>

要启动观察器，仅需在主库运行。

DGMGRL> start observer
观察器已启动

设置FastStartFailoverThreshold参数
DGMGRL> edit configuration set property FastStartFailoverThreshold=120;
已更新属性 "faststartfailoverthreshold"

DGMGRL> show configuration verbose;

Configuration
Name:                mystock_primary
Enabled:             YES
Protection Mode:     MaxAvailability
Fast-Start Failover: ENABLED
Databases:
    mystock - Primary database
    mytest - Physical standby database
            - Fast-Start Failover target

Fast-Start Failover
Threshold: 120 seconds
Observer: ca

"mystock_primary" 的当前状态:
SUCCESS

观察器状态也可以从以下SQL中查到：

SQL> select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;

配置成功了！做个测试：
在主库关机
C:\Documents and Settings\an.chen>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 4月 14 22:41:20 2009

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown abort
ORACLE 例程已经关闭。
SQL>

然后去从库查询状态：

SQL> select t.PROTECTION_MODE,t.DATABASE_ROLE from v$database t;

PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM AVAILABILITY PRIMARY

OK！从库角色变为了主库角色！
同时看到观察器的输出：
22:55:39.57 2009年4月14日星期二
正在为数据库 "mytest" 启动快速启动故障转移...
立即执行故障转移, 请稍候...
故障转移成功, 新的主数据库为 "mytest"
22:56:17.48 2009年4月14日星期二

可以看到我这个测试库在1分钟之内就实现了故障转移，的确不错！

然后，再重新启动原来的主库：
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             276827012 bytes
Database Buffers          327155712 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> select t.PROTECTION_MODE,t.DATABASE_ROLE from v$database t;

PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM AVAILABILITY PRIMARY

SQL>
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-16649: 在 Data Guard 中介已评估快速启动故障转移状态之后, 数据库将打开

接着就在观察器中发现如下输出
23:01:23.84 2009年4月14日星期二
正在为数据库 "mystock" 启动恢复过程...
正在恢复数据库 "mystock", 请稍候...
操作要求关闭实例 "mystock" (在数据库 "mystock" 上)
正在关闭实例 "mystock"...
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
操作要求启动实例 "mystock" (在数据库 "mystock" 上)
正在启动实例 "mystock"...
无法连接到数据库
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

失败。
You are no longer connected to ORACLE
Please connect again.
无法启动实例 "mystock"
必须手动启动实例 "mystock"
恢复数据库 "mystock" 失败
23:02:13.20 2009年4月14日星期二

未能正常启动。我认为可能是因为我一机多实例的原因，造成不能正常启动数据库。
我在原来的主库手动启动：

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             276827012 bytes
Database Buffers          327155712 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select t.PROTECTION_MODE,t.DATABASE_ROLE from v$database t;

PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY

SQL>

成功！

ITPub博客

ORACLE10G的FAST-START FAILOVER配置

最新文章