Oracle Data Guard 快速启动故障切换指南

db1_b_static：用于连接到数据库“b”上的静态 Data Guard 服务的别名

示例 tnsnames.ora 条目：

db1_a=
  (description=
    (SDU=32767)
    (address_list=
      (address=(protocol=tcp)(host=dbhost-a)(port=1522))
    )
    (connect_data=
      (service_name=db1_a.demo.org)
      (server=dedicated)
    )
  )

db1_b=
  (description=
    (SDU=32767)
    (address_list=
      (address=(protocol=tcp)(host=dbhost-b)(port=1522))
    )
    (connect_data=
      (service_name=db1_b.demo.org)
      (server=dedicated)
    )
  )

db1_a_static=
  (description=
    (SDU=32767)
    (address_list=
      (address=(protocol=tcp)(host=dbhost-a)(port=1522))
    )
    (connect_data=
      (service_name=db1_a_static.demo.org)
      (server=dedicated)
    )
  )

db1_b_static=
  (description=
    (SDU=32767)
    (address_list=
      (address=(protocol=tcp)(host=dbhost-b)(port=1522))
    )
    (connect_data=
      (service_name=db1_b_static.demo.org)
      (server=dedicated)
    )
  )

启动 Data Guard 监听器

lsnrctl start LISTENER_DG

测试 Oracle Net 连接

tnsping db1_a

Attempting to contact (description= (SDU=32767) (address_list= (address=(protocol=tcp)(host=dbhost-a)(port=1522)))
 (connect_data= (service_name=db1_a.demo.org) (server=dedicated)))
OK (0 msec)

tnsping db1_b

Attempting to contact (description= (SDU=32767) (address_list= (address=(protocol=tcp)(host=dbhost-b)(port=1522)))
 (connect_data= (service_name=db1_b.demo.org) (server=dedicated)))
OK (0 msec)

准备主数据库

需要主数据库处于安装（未打开）状态的步骤集中在以下题为需要主数据库回弹的步骤的部分中。

将 Data Guard 监听器添加到 local_listeners 参数

设置

alter system set local_listener='(address=(host=dbhost-a)(port=1522)(protocol=tcp))';
alter system register;

验证

show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (address=(host=dbhost-a)(port=
                                                 1522)(protocol=tcp))

lsnrctl status listener_dg

...
Services Summary...
Service "DB1_A" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
Service "DB1_A_XPT" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
Service "db1XDB" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
Service "db1_a_dgmgrl.demo.org" has 1 instance(s).
  Instance "db1", status UNKNOWN, has 1 handler(s) for this service...
Service "db1_a_static.demo.org" has 1 instance(s).
  Instance "db1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启用强制日志记录

启用

alter database force logging;

验证

select force_logging from v$database;

FOR
---
YES

创建 spfile

创建

create spfile='?/dbs/spfile${ORACLE_SID}.ora' from pfile='?/dbs/init${ORACLE_SID}.ora';
alter system set spfile='?/dbs/spfiledb1.ora';

验证

show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfiledb1.ora

创建口令文件

创建

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID

验证

select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

启用远程登录

启用

alter system set remote_login_passwordfile=exclusive scope=spfile;

验证

show parameter remote_login_passwordfile 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

设置 db_unique_name

设置

alter system set db_unique_name = db1_a scope=spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      db1_a

验证

show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      db1_a

配置快速恢复区

配置

alter system set db_recovery_file_dest_size = 20g scope=both;
alter system set db_recovery_file_dest = '/u01/fra' scope=both;

验证

show parameter db_recovery_file 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

启用自动备用文件管理

启用

alter system set standby_file_management=auto;

验证

show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

设置 log_archive_config

设置

alter system set log_archive_config='DG_CONFIG=(db1_b)' scope=both;

验证

show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(db1_b)

创建备用重做日志

创建

select bytes, count(group#) from v$log group by bytes;

     BYTES COUNT(GROUP#)
---------- -------------
  52428800             3

select max(group#) from v$log;

MAX(GROUP#)
-----------
          3

 

在本示例中，有 3 个最大 ORL，最大 group# 为 3。我们将从 group# 11 开始递增，创建 4 个 SRL。从 11 开始是完全形式化的，这样使稍后添加的新 ORL 组可以保持其 group# 与现有 ORL 拥有相同的顺序。

 

alter database add standby logfile group 11 '/u02/oradata/db1/stby-t01-g11-m1.log' size 52428800;
alter database add standby logfile group 12 '/u02/oradata/db1/stby-t01-g12-m1.log' size 52428800;
alter database add standby logfile group 13 '/u02/oradata/db1/stby-t01-g13-m1.log' size 52428800;
alter database add standby logfile group 14 '/u02/oradata/db1/stby-t01-g14-m1.log' size 52428800;

验证

select group#, type, member from v$logfile where type = 'STANDBY';

    GROUP# TYPE    MEMBER
---------- ------- ----------------------------------------
        11 STANDBY /u02/oradata/db1/stby-t01-g11-m1.log
        12 STANDBY /u02/oradata/db1/stby-t01-g12-m1.log
        13 STANDBY /u02/oradata/db1/stby-t01-g13-m1.log
        14 STANDBY /u02/oradata/db1/stby-t01-g14-m1.log

需要主数据库回弹的步骤

启用存档日志模式

启用

alter database archivelog;

验证

select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

启用闪回数据库

启用

alter database flashback on;
alter system set db_flashback_retention_target = 60 scope=both;

验证

select flashback_on from v$database;

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

show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     60

启用最高可用性模式

启用

alter database set standby database to maximize availability;

验证

select protection_mode from v$database;

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

创建备用数据库

将主数据库中的口令文件复制到备用数据库

创建 oratab 条目

db1:/u01/app/oracle/product/11.1.0/db_1:Y

创建 init.ora 文件

db_name = db1

设置环境

以非安装方式启动备用数据库

startup nomount

使用 RMAN 创建备用数据库

运行 RMAN 实用程序并连接到目标（主数据库）和辅助对象（新备用数据库）。

rman target sys/password@db1_a_static auxiliary sys/password@db1_b_static

connected to target database: DB1 (DBID=1234567890)
connected to auxiliary database: X (not mounted)

将默认设备类型设置为磁盘

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

从活动主数据库复制数据库

DUPLICATE TARGET DATABASE
 FOR STANDBY
 FROM ACTIVE DATABASE
 DORECOVER
 SPFILE
 SET db_unique_name='db1_b'
 SET log_archive_config=''
 SET log_file_name_convert= ' ',' '
 SET local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost-b)(PORT=1522)))'
 NOFILENAMECHECK; 

在备用数据库启用闪回数据库

启用

alter database flashback on;
alter system set db_flashback_retention_target = 60 scope=both;

验证

select flashback_on from v$database;

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

show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     60

设置 Data Guard Broker 配置文件的位置

设置（主数据库和备用数据库）

alter system set dg_broker_config_file1='/u01/app/oracle/admin/db1/dgbroker/dg1db1.dat';
alter system set dg_broker_config_file2='/u02/app/oracle/admin/db1/dgbroker/dg2db1.dat';

验证

show parameter dg_broker_config_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string /u01/app/oracle/admin/db1/dgbr
                                            oker/dg1db1.dat
dg_broker_config_file2               string /u02/app/oracle/admin/db1/dgbr
                                            oker/dg2db1.dat

启用 Data Guard Broker

启用（主数据库和备用数据库）

alter system set dg_broker_start=true;

验证

show parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

配置 Broker

在主数据库上启动 dgmgrl 实用程序并以 SYS 身份进行连接

dgmgrl sys/password@db1_a

创建 Broker 配置

添加主数据库

create configuration 'FSF' as
primary database is db1_a
connect identifier is db1_a;

Configuration "FSF" created with primary database "db1_a"

添加备用数据库

add database db1_b as
connect identifier is db1_b
maintained as physical;

Database "db1_b" added

验证配置

show configuration

Configuration
 Name: FSF
 Enabled: NO
 Protection Mode: MaxAvailability
 Databases:
 db1_a - Primary database
 db1_b - Physical standby database

Fast-Start Failover: DISABLED

Current status for "FSF":
DISABLED

编辑数据库属性

edit database db1_a set property LogXptMode='SYNC';
edit database db1_a set property NetTimeout=10;
edit database db1_b set property NetTimeout=10;

启用配置

enable configuration;

验证配置

show configuration

Configuration
 Name: FSF
 Enabled: YES
 Protection Mode: MaxAvailability
 Databases:
 db1_a - Primary database
 db1_b - Physical standby database

Fast-Start Failover: DISABLED

Current status for "FSF":
SUCCESS

启用快速启动故障切换

enable fast_start failover;

Enabled.

show configuration

Configuration
  Name:                FSF
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    db1_a - Primary database
    db1_b - Physical standby database
          - Fast-Start Failover target

Fast-Start Failover: ENABLED

Current status for "FSF":
Warning: ORA-16608: one or more databases have warnings

show database db1_a statusreport

STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *      ERROR ORA-16819: fast-start failover observer not started

配置观察器

验证到主数据库和备用数据库的连接

tnsping db1_a

Attempting to contact (description= (SDU=32767) (address_list= (address=(protocol=tcp)(host=dbhost-a)(port=1522)))
 (connect_data= (service_name=db1_a.demo.org) (server=dedicated)))
OK (0 msec)

tnsping db1_b

Attempting to contact (description= (SDU=32767) (address_list= (address=(protocol=tcp)(host=dbhost-b)(port=1522)))
 (connect_data= (service_name=db1_b.demo.org) (server=dedicated)))
OK (0 msec)

启动观察器

dgmgrl sys/password@db1_a
start observer;

observer started

验证配置

dgmgrl sys/password@db1_a
show configuration verbose

Configuration
  Name:                FSF
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    db1_a - Primary database
    db1_b - Physical standby database
          - Fast-Start Failover target

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              db1_b
  Observer:            observer.demo.org
  Lag Limit:           30 seconds (not in use)
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Current status for "FSF":
SUCCESS

show fast_start failover;

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              db1_b
  Observer:            observer.demo.org
  Lag Limit:           30 seconds (not in use)
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

测试配置

测试双向转换

dgmgrl sys/password@db1_a
switchover to db1_b

Performing switchover NOW, please wait...
New primary database "db1_b" is opening...
Operation requires shutdown of instance "db1" on database "db1_a"
Shutting down instance "db1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db1" on database "db1_a"
Starting instance "db1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db1_b"

show configuration

Configuration
  Name:                FSF
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    db1_b - Primary database
    db1_a - Physical standby database
          - Fast-Start Failover target

Fast-Start Failover: ENABLED

Current status for "FSF":
SUCCESS

switchover to db1_a

Performing switchover NOW, please wait...
New primary database "db1_a" is opening...
Operation requires shutdown of instance "db1" on database "db1_b"
Shutting down instance "db1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db1" on database "db1_b"
Starting instance "db1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "db1_a"

show configuration

Configuration
  Name:                FSF
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    db1_a - Primary database
    db1_b - Physical standby database
          - Fast-Start Failover target

Fast-Start Failover: ENABLED

Current status for "FSF":
SUCCESS

双向测试 FSFO 故障切换

检查闪回数据库记忆

select (sysdate - oldest_flashback_time)*24*60 as history from v$flashback_database_log;

   HISTORY
----------
    140.35

创建一些测试数据

-- Note that DDL statements automatically commit
create table x as select * from all_objects;

Table created.

select count(*) from x;

  COUNT(*)
----------
     68855

启动 FSFO 故障切换

shutdown abort

观察器日志：

Initiating Fast-Start Failover to database "db1_b"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "db1_b"

dgmgrl sys/password@db1_b
show configuration

Configuration
  Name:                FSF
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    db1_b - Primary database
    db1_a - Physical standby database (disabled)
          - Fast-Start Failover target

Fast-Start Failover: ENABLED

Current status for "FSF":
Warning: ORA-16608: one or more databases have warnings

查看测试数据

select count(*) from x;

  COUNT(*)
----------
     68855

将之前中止的主数据库恢复为备用数据库

startup mount

Initiating reinstatement for database "db1_a"...
Reinstating database "db1_a", please wait...
Operation requires shutdown of instance "db1" on database "db1_a"
Shutting down instance "db1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db1" on database "db1_a"
Starting instance "db1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "db1_a" ...
Reinstatement of database "db1_a" succeeded

Configuration
  Name:                FSF
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    db1_b - Primary database
    db1_a - Physical standby database
          - Fast-Start Failover target

Fast-Start Failover: ENABLED

Current status for "FSF":
SUCCESS

在另一个方向重复操作

主数据库停滞

监视 FSFO 准备情况

询问 Broker

询问主数据库

备用数据库应用

闪回数据库历史记录

客户端通知

附录

创建观察器包装

创建钱夹

mkdir -p /u01/app/oracle/admin/wallet

mkstore -wrl /u01/app/oracle/admin/wallet -createEntry oracle.security.client.default_username SYS
mkstore -wrl /u01/app/oracle/admin/wallet -createEntry oracle.security.client.default_password <sys password>

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/wallet)
    )
  )
SQLNET.WALLET_OVERRIDE = TRUE

确定观察器状态文件（fsfo.dat 文件）的存储位置

mkdir -p /u01/app/oracle/admin/db1/observer

观察器启动命令

nohup dgmgrl /@db1 "start observer file='/u01/app/oracle/admin/db1/observer/fsfo.dat'" / 
  >> /u01/app/oracle/admin/db1/observer/dgmgrl.log &

有助于了解观察器的信息

• 所有与观察器相关联的连接（包括初始连接）必须使用专用服务器连接。使用 Shared Server (MTS) 或连接池可能导致无法预料的行为。
• 为实现可靠的启动，初始连接应始终连接到主数据库。运行中的观察器在角色转换后将自动跟随主数据库，但如果初始连接连接到故障数据库或者包含过期或损坏的 Broker 配置文件的数据库，新（重新）启动的观察器将不会启动。
• 启动可能会失败，原因为“cORA-16647: could not start more than one observer”。如果之前的观察器进程未注销即终止并且新观察器未使用之前的 fsfo.dat 文件，那么即使没有观察器在实际运行也会发生该错误。如果出现此情形，运行“stop observer”并重试。
• 至少配置两台主机来运行观察器是一个比较好的办法，这样，其中一台主机可以在另一台发生故障时进行替换。

故障切换脚本

DB_ROLE_CHANGE 系统事件

包括多个备用数据库的 FSFO 配置

备用节点

转换限制

1. 禁用 FSFO
2. 将故障切换目标更改为转换到的备用数据库
3. 启用 FSFO
4. 转换

1. 禁用 FSFO
2. 转换
3. 将故障切换目标更改为所希望的备用数据库
4. 启用 FSFO

                    本文出自：http://blogold.chinaunix.net/u1/37322/showart_2301178.html