ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE10G的FAST-START FAILOVER配置

ORACLE10G的FAST-START FAILOVER配置

原创 Linux操作系统 作者:anchen211 时间:2009-04-14 23:17:27 0 删除 编辑

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

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


连接到:
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博客 ” ,链接:http://blog.itpub.net/9375/viewspace-589054/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 一个面试题
请登录后发表评论 登录
全部评论

注册时间:2008-11-04

  • 博文量
    129
  • 访问量
    177204