单实例物理DG配置客户端无缝切换的详细过程

0    70    1

👉 本文共约65630个字,系统预计阅读时间或需247分钟。

物理dg配置客户端无缝切换 (1)--Data Guard Broker 的配置

前言部分

导读

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① Data Guard Broker 的配置

② Fast-Start Failover 的配置

③ Oracle DataGuard 之客户端TAF 配置

④ 使用DGMGRL 来管理数据库

⑤ 物理dg管理和维护的一些sql

⑥ DataGuard 客户端特级配置

实验环境介绍

项目主库dg库
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDoradg11goradgphy
db_nameoradg11goradg11g
主机IP地址:192.168.59.130192.168.59.130
OS版本及kernel版本RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64
OS hostnamerhel6_lhrrhel6_lhr

本次课程,研究当主备库发生切换时,如何在主库启动一个service,保证客户端的连接能够继续,而且还能够继续select查询操作,而不管主备库是在哪台服务器上;同时保证新的客户连接没有任何的问题。本课程网络上的例子不多,陈老师花了将近一年的时间人肉搜索,最近才找到,急不可待的要分享给大家。

1、DataGuard的配置(快速)

2、创建service

3、创建触发器

4、主备库切换测试

相关知识点扫盲

oracle Data Guard,以最低成本实现最高的数据保护。在硬件上没有特殊要求,普通PC机即可实现。

简单的来说,Data Guard,就是自动创建和维护生产数据库(或主数据库)的一个或多个事务一致的副本(备用数据库)。如果主数据库不可用(因为故障、维护或者灾难),那么可以激活一个备用数据库并使之承担主数据库的角色。然而,在配置完成Data Guard后,若需要实现主备数据库间的切换,需要在主数据库及备用数据库上分别输入多个命令,切换步骤稍显麻烦。所以,一般情况下,DBA会将整个切换过程编辑成脚本,以便自动运行,进行状态切换。当然,oracle 也提供了工具Data Guard Broker,仅在控制端输入一个命令就能方便实现主备数据库间的切换。

在Data Guard Broker的基础上,配置并启用Fast-Start Failover,就能自动检测发现主机故障,实现主备切换,故障转移。

Data Guard Broker是建立在Data Guard基础上的一个对Data Guard配置,集中管理操作的一个平台。Broker的推出是为了简化DG复杂的管理过程,它最大的作用就是集中化的统一管理。

配置Data Guard Broker使用到的客户端工具是DGMGRL。它是一个命令行管理工具。

实验部分

实验目标

Data Guard Broker 的配置并完成实验。

前提条件

注意前提条件,Data Guard已经成功配置完成。

  1. 设置primary和standby启动时参数文件为spfile
  2. 配置DG_BROKER_CONFIG_FILEn
    DG_BROKER_CONFIG_FILEn代表2个参数,分别为DG_BROKER_CONFIG_FILE1,DG_BROKER_CONFIG_FILE2,它们是Data Guard Broker的配置文件名。它们都有默认值(windows下是在ORACLE_HOME/database目录下,linux下是在ORACLE_HOME/dbs目录下),所以一般我们可以不进行特别设置。
  3. 设置listener
    在Data Guard Broker环境中,需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重启数据库的时候用到。通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL用此service_name通过listener连接到数据库,发出启动命令。
  4. 设置DG_BROKER_START为TRUE
    这一步是启动Data Guard Broker monitor(DMON)进程,需要在2个数据库上面都运行下面的命令
    alter system set dg_broker_start=true scope=both;

主库操作:

[oracle@rhel6_lhr lhr]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期一 9月 28 10:17:04 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:17:04 SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string oradglg, oradg11g

db_name string oradg11g

db_unique_name string oradg11g

global_names boolean FALSE

instance_name string oradg11g

lock_name_space string

log_file_name_convert string oradglg, oradg11g

processor_group_name string

service_names string oradg11g

10:17:08 SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileoradg11g.o

ra

10:17:14 SQL> show parameter DG_BROKER_CONFIG_FILE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr1oradg11g.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr2oradg11g.dat

10:20:43 SQL> set line 9999

10:20:44 SQL> col name format a10

10:20:44 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:20:44 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G 2336836 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.01

10:20:44 SQL> SELECT d.DBID,

10:20:44 2 d.DB_UNIQUE_NAME,

10:20:44 3 d.FORCE_LOGGING,

10:20:44 4 d.FLASHBACK_ON,

10:20:44 5 d.FS_FAILOVER_STATUS,

10:20:44 6 d.FS_FAILOVER_CURRENT_TARGET,

10:20:44 7 d.FS_FAILOVER_THRESHOLD,

10:20:44 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:20:44 9 d.FS_FAILOVER_OBSERVER_HOST

10:20:44 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- ------------------------------ --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES DISABLED 0

已用时间: 00: 00: 00.00

10:20:45 SQL>

10:20:45 SQL> ! ps -ef|grep dmon

oracle 7936 7895 0 10:21 pts/4 00:00:00 /bin/bash -c ps -ef|grep dmon

oracle 7938 7936 0 10:21 pts/4 00:00:00 grep dmon

10:21:17 SQL> show parameter dg_broker

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr1oradg11g.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr2oradg11g.dat

dg_broker_start boolean FALSE

10:21:39 SQL> show parameter dg_broker_start

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_start boolean FALSE

10:21:53 SQL> alter system set dg_broker_start=true scope=both;

系统已更改。

已用时间: 00: 00: 00.02

10:21:56 SQL> ! ps -ef|grep dmon

oracle 7975 1 0 10:21 ? 00:00:00 ora_dmon_oradg11g

oracle 7976 7895 0 10:22 pts/4 00:00:00 /bin/bash -c ps -ef|grep dmon

oracle 7978 7976 0 10:22 pts/4 00:00:00 grep dmon

10:22:02 SQL>

10:22:02 SQL> show parameter dg_broker_start

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_start boolean TRUE

10:23:48 SQL> set line 9999

10:23:58 SQL> col name format a10

10:23:58 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:23:58 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G 2337043 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.01

10:23:58 SQL> SELECT d.DBID,

10:23:58 2 d.DB_UNIQUE_NAME,

10:23:58 3 d.FORCE_LOGGING,

10:23:58 4 d.FLASHBACK_ON,

10:23:58 5 d.FS_FAILOVER_STATUS,

10:23:58 6 d.FS_FAILOVER_CURRENT_TARGET,

10:23:58 7 d.FS_FAILOVER_THRESHOLD,

10:23:58 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:23:58 9 d.FS_FAILOVER_OBSERVER_HOST

10:23:58 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- ------------------------------ --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES DISABLED 0

已用时间: 00: 00: 00.00

10:24:00 SQL>

告警日志:

Mon Sep 28 10:21:56 2015

ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

Mon Sep 28 10:21:56 2015

DMON started with pid=37, OS id=7975

Starting Data Guard Broker (DMON)

Mon Sep 28 10:22:04 2015

INSV started with pid=38, OS id=7984

备库操作:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期一 9月 28 10:25:25 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:25:25 SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string oradg11g, oradgphy

db_name string oradg11g

db_unique_name string oradgphy

global_names boolean FALSE

instance_name string oradgphy

lock_name_space string

log_file_name_convert string oradg11g, oradgphy

processor_group_name string

service_names string oradgphy

10:25:27 SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileoradgphy.o

ra

10:25:35 SQL> show parameter DG_BROKER_CONFIG_FILE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr1oradgphy.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr2oradgphy.dat

10:25:43 SQL> set line 9999

10:25:51 SQL> col name format a10

10:25:51 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:25:51 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

SELECT d.DBID,

d.DB_UNIQUE_NAME,

d.FORCE_LOGGING,

d.FLASHBACK_ON,

d.FS_FAILOVER_STATUS,

DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G 2337499 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

已用时间: 00: 00: 00.11

10:25:52 SQL> 10:25:52 2 10:25:52 3 10:25:52 4 10:25:52 5 10:25:52 6 d.FS_FAILOVER_CURRENT_TARGET,

10:25:52 7 d.FS_FAILOVER_THRESHOLD,

10:25:52 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:25:52 9 d.FS_FAILOVER_OBSERVER_HOST

10:25:52 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- ------------------------------ --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES NO DISABLED 0

已用时间: 00: 00: 00.00

10:25:56 SQL> ! ps -ef|grep dmon

oracle 7975 1 0 10:21 ? 00:00:00 ora_dmon_oradg11g

oracle 8461 8410 0 10:26 pts/5 00:00:00 /bin/bash -c ps -ef|grep dmon

oracle 8463 8461 0 10:26 pts/5 00:00:00 grep dmon

10:26:07 SQL> show parameter dg_broker_start

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_start boolean FALSE

10:26:39 SQL> alter system set dg_broker_start=true scope=both;

系统已更改。

已用时间: 00: 00: 00.05

10:26:46 SQL> ! ps -ef|grep dmon

oracle 7975 1 0 10:21 ? 00:00:00 ora_dmon_oradg11g

oracle 8481 1 0 10:26 ? 00:00:00 ora_dmon_oradgphy

oracle 8501 8410 0 10:26 pts/5 00:00:00 /bin/bash -c ps -ef|grep dmon

oracle 8503 8501 0 10:26 pts/5 00:00:00 grep dmon

10:26:50 SQL>

配置db_domain 和 listener 及 tnsnames

目标: 配置db_domain 和 静态的listener ,listener中的GLOBAL_DBNAME为db_unique_name_DGMGRL.db_domain

主备库:

14:05:05 SQL> show parameter db_domain

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_domain string

14:13:46 SQL> alter system set db_domain='lhr.com' scope=spfile;

主备库listener的配置,注意红色的为新添加的内容:

dgmgrl中配置broker并启用

[oracle@rhel6_lhr ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

DGMGRL> help

可使用以下命令:

add 在中介配置中添加备用数据库

connect 连接到 Oracle 数据库实例

convert 将数据库从一种类型转换为另一种

create 创建中介配置

disable 禁用配置, 数据库或快速启动故障转移

edit 编辑配置, 数据库或实例

enable 启用配置, 数据库或快速启动故障转移

exit 退出程序

failover 将备用数据库更改为主数据库

help 显示命令的说明和语法

quit 退出程序

reinstate 将标记为恢复的数据库更改为可行的备用数据库

rem DGMGRL 会忽略注释

remove 删除配置, 数据库或实例

show 显示有关配置, 数据库或实例的信息

shutdown 关闭当前正在运行的 Oracle 数据库实例

sql 执行 SQL 语句

start 启动快速启动故障转移观察程序

startup 启动 Oracle 数据库实例

stop 停止快速启动故障转移观察程序

switchover 在主数据库和备用数据库之间切换角色

使用 "help \" 可以查看各个命令的语法

DGMGRL> connect sys/lhr@tns_oradg11g_dgmgrl

已连接。

DGMGRL>

DGMGRL> show configuration

ORA-16532: Data Guard 中介配置不存在

配置详细资料不能由 DGMGRL 确定

DGMGRL> help create

创建中介配置

语法:

CREATE CONFIGURATION \ AS

PRIMARY DATABASE IS \

CONNECT IDENTIFIER IS \;

DGMGRL> create configuration 'fsf_oradg11g_lhr' as

primary database is 'oradg11g'

connect identifier is tns_oradg11g_dgmgrl;

已创建配置 "fsf_oradg11g_lhr", 其中主数据库为 "oradg11g"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

快速启动故障转移: DISABLED

配置状态:

DISABLED

DGMGRL> add database 'oradgphy' as

connect identifier is tns_oradgphy_dgmgrl

maintained as physical;

已添加数据库 "oradgphy"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

DISABLED

DGMGRL>

DGMGRL> enable configuration

已启用。

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL>

DGMGRL>

DGMGRL> show database verbose oradg11g

数据库 - oradg11g

角色: PRIMARY

预期状态: TRANSPORT-ON

实例:

oradg11g

属性:

DGConnectIdentifier = 'tns_oradg11g_dgmgrl'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'oradgphy, oradg11g'

LogFileNameConvert = 'oradgphy, oradg11g'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

SidName = 'oradg11g'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t%s%r.dbf'

TopWaitEvents = '(monitor)'

数据库状态:

SUCCESS

DGMGRL> show database verbose oradgphy

数据库 - oradgphy

角色: PHYSICAL STANDBY

预期状态: APPLY-ON

传输滞后: 0 秒

应用滞后: 0 秒

实时查询: ON

实例:

oradgphy

属性:

DGConnectIdentifier = 'tns_oradgphy_dgmgrl'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'oradg11g, oradgphy'

LogFileNameConvert = 'oradg11g, oradgphy'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

SidName = 'oradgphy'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradgphy_DGMGRL.lhr.com)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t%s%r.dbf'

TopWaitEvents = '(monitor)'

数据库状态:

SUCCESS

DGMGRL>

DGMGRL>

主库告警日志:

Mon Sep 28 15:20:28 2015

NSV1 started with pid=30, OS id=25660

Mon Sep 28 15:20:54 2015

ALTER SYSTEM SET log_archive_config='dg_config=(oradg11g,oradgphy)' SCOPE=BOTH;

Mon Sep 28 15:20:57 2015

ALTER SYSTEM SET log_archive_dest_2='service="tns_oradgphy_dgmgrl"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="oradgphy" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradg11g';

ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradg11g';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';

ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';

ALTER SYSTEM SET db_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;

ALTER SYSTEM ARCHIVE LOG

Mon Sep 28 15:20:57 2015

NSA2 started with pid=34, OS id=25706

备库告警日志:

NSV0 started with pid=21, OS id=25696

Mon Sep 28 15:20:57 2015

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradgphy';

ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradgphy';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';

ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';

ALTER SYSTEM SET db_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET fal_server='tns_oradg11g_dgmgrl' SCOPE=BOTH;

Mon Sep 28 15:21:01 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[4]: Assigned to RFS process 25708

RFS[4]: Selected log 4 for thread 1 sequence 145 dbid 1403587593 branch 886695024

测试Data Guard Broker 功能

在配置broker完成后,我们就可以利用broker完成switchover了。在DGMGRL中只需要简单的一个命令。
switchover to standby-database
(standby-database: 此参数是db_unique_name,大小写敏感。如果名称是大写的,需要用引号。表示切换standby-database为主机,原主机转换为备机状态)

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL> switchover to oradgphy;

立即执行切换, 请稍候...

新的主数据库 "oradgphy" 正在打开...

操作要求关闭实例 "oradg11g" (在数据库 "oradg11g" 上)

正在关闭实例 "oradg11g"...

ORACLE 例程已经关闭。

操作要求启动实例 "oradg11g" (在数据库 "oradg11g" 上)

正在启动实例 "oradg11g"...

ORACLE 例程已经启动。

数据库装载完毕。

数据库已经打开。

切换成功, 新的主数据库为 "oradgphy"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradgphy - 主数据库

oradg11g - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL>

再切换一次,回到最初状态:

DGMGRL> switchover to oradg11g;

立即执行切换, 请稍候...

新的主数据库 "oradg11g" 正在打开...

操作要求关闭实例 "oradgphy" (在数据库 "oradgphy" 上)

正在关闭实例 "oradgphy"...

ORACLE 例程已经关闭。

操作要求启动实例 "oradgphy" (在数据库 "oradgphy" 上)

正在启动实例 "oradgphy"...

ORACLE 例程已经启动。

数据库装载完毕。

数据库已经打开。

切换成功, 新的主数据库为 "oradg11g"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL>

第一次切换的时候告警日志情况:

主库告警日志:

Mon Sep 28 15:26:24 2015

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 23943] (oradg11g)

Mon Sep 28 15:26:24 2015

Thread 1 advanced to log sequence 146 (LGWR switch)

Current log# 2 seq# 146 mem# 0: /u01/app/oracle/oradata/oradg11g/redo02.log

Mon Sep 28 15:26:24 2015

Stopping background process CJQ0

Stopping background process QMNC

Stopping Job queue slave processes, flags = 27

Mon Sep 28 15:26:27 2015

Archived Log entry 501 added for thread 1 sequence 145 ID 0x5495956b dest 1:

Waiting for Job queue slaves to complete

Job queue slave processes stopped

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 25846 user 'oracle' program 'oracle@rhel6_lhr (W000)'

CLOSE: all sessions shutdown successfully.

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Waiting for all FAL entries to be archived...

All FAL entries have been archived.

Waiting for dest_id 2 to become synchronized...

Active, synchronized Physical Standby switchover target has been identified

Switchover End-Of-Redo Log thread 1 sequence 146 has been fixed

Switchover: Primary highest seen SCN set to 0x0.0x262c94

ARCH: Noswitch archival of thread 1, sequence 146

ARCH: End-Of-Redo Branch archival of thread 1 sequence 146

ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

ARCH: Standby redo logfile selected for thread 1 sequence 146 for destination LOG_ARCHIVE_DEST_2

Archived Log entry 502 added for thread 1 sequence 146 ID 0x5495956b dest 1:

ARCH: Archiving is disabled due to current logfile archival

Primary will check for some target standby to have received alls redo

Final check for a synchronized target standby. Check will be made once.

Archive destination LOG_ARCHIVE_DEST_3 invalidated

DB_UNIQUE_NAME oradglg is not in the Data Guard configuration

Archive destination LOG_ARCHIVE_DEST_4 invalidated

DB_UNIQUE_NAME oradgss is not in the Data Guard configuration

LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target

Active, synchronized target has been identified

Target has also received all redo

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradg11g/oradg11g/trace/oradg11g_rsm0_23943.trc

Clearing standby activation ID 1419089259 (0x5495956b)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Archivelog for thread 1 sequence 146 required for standby recovery

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete - Database shutdown required

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

Mon Sep 28 15:26:37 2015

Performing implicit shutdown abort due to switchover to physical standby

Shutting down instance (abort)

License high water mark = 8

USER (ospid: 26018): terminating the instance

Instance terminated by USER, pid = 26018

Mon Sep 28 15:26:38 2015

Instance shutdown complete

ORA-1092 : opitsk aborting process

Mon Sep 28 15:26:38 2015

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Archive destination LOG_ARCHIVE_DEST_3 invalidated

DB_UNIQUE_NAME oradglg is not in the Data Guard configuration

Archive destination LOG_ARCHIVE_DEST_4 invalidated

DB_UNIQUE_NAME oradgss is not in the Data Guard configuration

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: rhel6_lhr

Release: 2.6.32-504.16.2.el6.x86_64

Version: #1 SMP Tue Apr 21 08:37:59 PDT 2015

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoradg11g.ora

System parameters with non-default values:

processes = 150

memory_target = 300M

memory_max_target = 400M

control_files = "/u01/app/oracle/oradata/oradg11g/control01.ctl"

control_files = "/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl"

db_file_name_convert = "oradgphy"

db_file_name_convert = "oradg11g"

log_file_name_convert = "oradgphy"

log_file_name_convert = "oradg11g"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=oradg11g valid_for=(ALL_LOGFILES,ALL_ROLES)"

log_archive_dest_2 = "service="tns_oradgphy_dgmgrl""

log_archive_dest_2 = "LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="oradgphy" net_timeout=30"

log_archive_dest_2 = "valid_for=(all_logfiles,primary_role)"

log_archive_dest_3 = "SERVICE=tns_oradglg LGWR ASYNC db_unique_name=oradglg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"

log_archive_dest_4 = "SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"

log_archive_dest_state_1 = "ENABLE"

log_archive_dest_state_2 = "ENABLE"

log_archive_dest_state_3 = "defer"

log_archive_dest_state_4 = "defer"

log_archive_min_succeed_dest= 1

fal_client = "oradg11g"

fal_server = "tns_oradgphy_dgmgrl"

log_archive_trace = 0

log_archive_config = "dg_config=(oradg11g,oradgphy)"

log_archiveformat = "%t%s_%r.dbf"

log_archive_max_processes= 4

archive_lag_target = 0

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 4122M

standby_file_management = "AUTO"

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "lhr.com"

global_names = TRUE

dispatchers = "(PROTOCOL=TCP) (SERVICE=oradg11gXDB)"

audit_file_dest = "/u01/app/oracle/admin/oradg11g/adump"

audit_trail = "DB"

db_name = "oradg11g"

db_unique_name = "oradg11g"

open_cursors = 300

dg_broker_start = TRUE

diagnostic_dest = "/u01/app/oracle"

Mon Sep 28 15:26:38 2015

PMON started with pid=2, OS id=26102

Mon Sep 28 15:26:38 2015

PSP0 started with pid=3, OS id=26104

Mon Sep 28 15:26:39 2015

Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST

destination database instance is 'started' not 'mounted'

Mon Sep 28 15:26:39 2015

VKTM started with pid=4, OS id=26112 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Mon Sep 28 15:26:39 2015

GEN0 started with pid=5, OS id=26116

Mon Sep 28 15:26:39 2015

DIAG started with pid=6, OS id=26118

Mon Sep 28 15:26:40 2015

DBRM started with pid=7, OS id=26120

Mon Sep 28 15:26:40 2015

DIA0 started with pid=8, OS id=26122

Mon Sep 28 15:26:40 2015

MMAN started with pid=9, OS id=26124

Mon Sep 28 15:26:40 2015

DBW0 started with pid=10, OS id=26126

Mon Sep 28 15:26:40 2015

LGWR started with pid=11, OS id=26128

Mon Sep 28 15:26:40 2015

CKPT started with pid=12, OS id=26130

Mon Sep 28 15:26:40 2015

SMON started with pid=13, OS id=26132

Mon Sep 28 15:26:40 2015

RECO started with pid=14, OS id=26134

Mon Sep 28 15:26:40 2015

MMON started with pid=15, OS id=26136

Mon Sep 28 15:26:40 2015

MMNL started with pid=16, OS id=26138

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/grid

Mon Sep 28 15:26:40 2015

DMON started with pid=19, OS id=26145

Mon Sep 28 15:26:40 2015

alter database mount

ARCH: STARTING ARCH PROCESSES

Mon Sep 28 15:26:44 2015

ARC0 started with pid=22, OS id=26157

ARC0: Archival started

ARCH: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Successful mount of redo thread 1, with mount id 1419115888

Allocated 3981120 bytes in shared pool for flashback generation buffer

Starting background process RVWR

Mon Sep 28 15:26:45 2015

ARC1 started with pid=23, OS id=26159

Mon Sep 28 15:26:45 2015

RVWR started with pid=24, OS id=26161

Mon Sep 28 15:26:45 2015

ARC2 started with pid=25, OS id=26163

Mon Sep 28 15:26:45 2015

ARC3 started with pid=26, OS id=26165

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Physical Standby Database mounted.

Lost write protection disabled

ARC2: Becoming the active heartbeat ARCH

Completed: alter database mount

alter database open

Data Guard Broker initializing...

Data Guard Broker initialization complete

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

Beginning standby crash recovery.

Serial Media Recovery started

Managed Standby Recovery starting Real Time Apply

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_145c0ktx3cj.arc

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_146c0ktx6mn.arc

Identified End-Of-Redo (switchover) for thread 1 sequence 146 at SCN 0x0.262c94

Resetting standby activation ID 0 (0x0)

Incomplete Recovery applied until change 2501780 time 09/28/2015 15:26:30

Completed standby crash recovery.

SMON: enabling cache recovery

Mon Sep 28 15:26:46 2015

RFS[1]: Assigned to RFS process 26177

RFS[1]: Opened log for thread 1 sequence 148 dbid 1403587593 branch 886695024

Archived Log entry 504 added for thread 1 sequence 148 rlc 886695024 ID 0x54960fb7 dest 2:

Dictionary check beginning

Mon Sep 28 15:26:46 2015

RFS[2]: Assigned to RFS process 26179

RFS[2]: Opened log for thread 1 sequence 147 dbid 1403587593 branch 886695024

Archived Log entry 505 added for thread 1 sequence 147 rlc 886695024 ID 0x54960fb7 dest 2:

Dictionary check complete

Database Characterset is ZHS16GBK

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Physical standby database opened for read only access.

RFS[2]: Selected log 4 for thread 1 sequence 149 dbid 1403587593 branch 886695024

Completed: alter database open

Archived Log entry 506 added for thread 1 sequence 149 ID 0x54960fb7 dest 1:

Mon Sep 28 15:26:46 2015

db_recovery_file_dest_size of 4122 MB is 6.96% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Mon Sep 28 15:26:46 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Assigned to RFS process 26185

RFS[3]: Selected log 4 for thread 1 sequence 150 dbid 1403587593 branch 886695024

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Starting Data Guard Broker (DMON)

Mon Sep 28 15:26:48 2015

INSV started with pid=30, OS id=26191

Mon Sep 28 15:26:51 2015

NSV1 started with pid=31, OS id=26199

Mon Sep 28 15:26:55 2015

RSM0 started with pid=32, OS id=26208

ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradg11g';

ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradg11g';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';

ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';

ALTER SYSTEM SET db_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='oradgphy','oradg11g' SCOPE=SPFILE;

ALTER SYSTEM SET fal_server='tns_oradgphy_dgmgrl' SCOPE=BOTH;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE

Attempt to start background Managed Standby Recovery process (oradg11g)

Mon Sep 28 15:26:59 2015

MRP0 started with pid=33, OS id=26214

MRP0: Background Managed Standby Recovery process started (oradg11g)

started logmerger process

Mon Sep 28 15:27:04 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 2 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Clearing online redo logfile 1 /u01/app/oracle/oradata/oradg11g/redo01.log

Clearing online log 1 of thread 1 sequence number 150

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /u01/app/oracle/oradata/oradg11g/redo02.log

Clearing online log 2 of thread 1 sequence number 146

Mon Sep 28 15:27:05 2015

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /u01/app/oracle/oradata/oradg11g/redo03.log

Clearing online log 3 of thread 1 sequence number 149

Clearing online redo logfile 3 complete

Mon Sep 28 15:27:07 2015

RFS[3]: Selected log 5 for thread 1 sequence 151 dbid 1403587593 branch 886695024

Mon Sep 28 15:27:07 2015

Archived Log entry 507 added for thread 1 sequence 150 ID 0x54960fb7 dest 1:

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_147c0ktxp86.arc

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_148c0ktxp70.arc

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_149c0ktxpgt.arc

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_150c0ktycgh.arc

Media Recovery Waiting for thread 1 sequence 151 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 151 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/oradg11g/standby_redo05.log

备库告警日志:

Mon Sep 28 15:26:24 2015

Archived Log entry 128 added for thread 1 sequence 145 ID 0x5495956b dest 1:

Mon Sep 28 15:26:24 2015

Media Recovery Waiting for thread 1 sequence 146

Mon Sep 28 15:26:30 2015

RFS[6]: Assigned to RFS process 25998

RFS[6]: Selected log 4 for thread 1 sequence 146 dbid 1403587593 branch 886695024

Mon Sep 28 15:26:30 2015

Archived Log entry 129 added for thread 1 sequence 146 ID 0x5495956b dest 1:

Mon Sep 28 15:26:30 2015

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_24252.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Mon Sep 28 15:26:31 2015

MRP0: Background Media Recovery process shutdown (oradgphy)

Managed Standby Recovery Canceled (oradgphy)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database not available for switchover

End-Of-REDO archived log file has not been recovered

Incomplete recovery SCN:0:2481770 archive SCN:0:2501780

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY

Media Recovery Start: Managed Standby Recovery (oradgphy)

started logmerger process

Mon Sep 28 15:26:32 2015

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_09_28/o1_mf_1_146c0ktx6n6.arc

Identified End-Of-Redo (switchover) for thread 1 sequence 146 at SCN 0x0.262c94

Resetting standby activation ID 1419089259 (0x5495956b)

Media Recovery End-Of-Redo indicator encountered

Media Recovery Applied through change 2501780

Media Recovery Complete: End-Of-REDO (oradgphy)

Attempt to set limbo arscn 0:2501780 irscn 0:2501780

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN

ALTER DATABASE SWITCHOVER TO PRIMARY (oradgphy)

Maximum wait for role transition is 15 minutes.

krsv_proc_kill: Killing 2 processes (all RFS)

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Mon Sep 28 15:26:34 2015

SMON: disabling cache recovery

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_rsm0_24244.trc

SwitchOver after complete recovery through change 2501780

Online log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 2501778

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

Switchover: Complete - Database mounted as primary

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN

Mon Sep 28 15:26:36 2015

idle dispatcher 'D000' terminated, pid = (17, 1)

ALTER DATABASE OPEN

Data Guard Broker initializing...

Mon Sep 28 15:26:37 2015

Assigning activation ID 1419120567 (0x54960fb7)

Mon Sep 28 15:26:37 2015

ARC3: Becoming the 'no SRL' ARCH

Thread 1 advanced to log sequence 148 (thread open)

ARC0: Becoming the 'no SRL' ARCH

Thread 1 opened at log sequence 148

Current log# 2 seq# 148 mem# 0: /u01/app/oracle/oradata/oradgphy/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Archived Log entry 130 added for thread 1 sequence 147 ID 0x54960fb7 dest 1:

ARC3: Becoming the 'no SRL' ARCH

Mon Sep 28 15:26:37 2015

NSA2 started with pid=17, OS id=26022

Mon Sep 28 15:26:37 2015

***********************************************************************

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:37

Tracing not turned on.

Tns error struct:

ns main err code: 12537

***********************************************************************

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:37

Tracing not turned on.

Tns error struct:

ns main err code: 12537

[24244] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:21508174 end:21508224 diff:50 (0 seconds)

Dictionary check beginning

TNS-12537: TNS:connection closed

TNS-12537: TNS:connection closed

ns secondary err code: 12560

nt main err code: 507

ns secondary err code: 12560

nt main err code: 507

TNS-00507: Connection closed

nt secondary err code: 0

nt OS err code: 0

TNS-00507: Connection closed

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:37

Tracing not turned on.

Tns error struct:

ns main err code: 12537

TNS-12537: TNS:connection closed

ns secondary err code: 12560

nt main err code: 507

TNS-00507: Connection closed

nt secondary err code: 0

nt OS err code: 0

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is ZHS16GBK

Starting background process SMCO

***********************************************************************

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:37

Tracing not turned on.

Tns error struct:

ns main err code: 12537

TNS-12537: TNS:connection closed

ns secondary err code: 12560

nt main err code: 507

TNS-00507: Connection closed

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:37

Tracing not turned on.

Tns error struct:

ns main err code: 12537

TNS-12537: TNS:connection closed

ns secondary err code: 12560

nt main err code: 507

TNS-00507: Connection closed

nt secondary err code: 0

nt OS err code: 0

Error 12537 received logging on to the standby

FAL[server, ARC3]: Error 12537 creating remote archivelog file 'tns_oradg11g_dgmgrl'

FAL[server, ARC3]: FAL archive failed, see trace file.

ARCH: FAL archive failed. Archiver continuing

ORACLE Instance oradgphy - Archival Error. Archiver continuing.

Mon Sep 28 15:26:37 2015

SMCO started with pid=26, OS id=26033

***********************************************************************

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:37

Tracing not turned on.

Tns error struct:

ns main err code: 12537

TNS-12537: TNS:connection closed

ns secondary err code: 12560

nt main err code: 507

TNS-00507: Connection closed

nt secondary err code: 0

nt OS err code: 0

Error 12537 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 12537.

No Resource Manager plan active

Starting background process QMNC

Mon Sep 28 15:26:37 2015

QMNC started with pid=27, OS id=26036

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: ALTER DATABASE OPEN

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradgphy';

ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradgphy';

Starting background process CJQ0

Mon Sep 28 15:26:37 2015

CJQ0 started with pid=32, OS id=26050

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';

ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';

ALTER SYSTEM SET db_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ARC2: STARTING ARCH PROCESSES

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

ALTER SYSTEM ARCHIVE LOG

Mon Sep 28 15:26:38 2015

ARC4 started with pid=28, OS id=26052

ARC4: Archival started

ARC2: STARTING ARCH PROCESSES COMPLETE

PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 16058.

Thread 1 advanced to log sequence 149 (LGWR switch)

Current log# 3 seq# 149 mem# 0: /u01/app/oracle/oradata/oradgphy/redo03.log

Archived Log entry 131 added for thread 1 sequence 148 ID 0x54960fb7 dest 1:

Mon Sep 28 15:26:41 2015

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:41

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:41

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:41

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:42

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:42

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

Time: 28-SEP-2015 15:26:42

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

ARC3: Becoming the 'no SRL' ARCH

Mon Sep 28 15:26:44 2015

ARC0: Becoming the 'no SRL' ARCH

Shutting down archive processes

ARCH shutting down

ARC4: Archival stopped

Mon Sep 28 15:26:46 2015

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';

ARC0: Becoming the 'no SRL' ARCH

ARC3: Becoming the 'no SRL' ARCH

Thread 1 advanced to log sequence 150 (LGWR switch)

Current log# 1 seq# 150 mem# 0: /u01/app/oracle/oradata/oradgphy/redo01.log

ARC3: Becoming the 'no SRL' ARCH

Archived Log entry 134 added for thread 1 sequence 149 ID 0x54960fb7 dest 1:

ARC0: Becoming the 'no SRL' ARCH

ARC0: Standby redo logfile selected for thread 1 sequence 149 for destination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LNS: Standby redo logfile selected for thread 1 sequence 150 for destination LOG_ARCHIVE_DEST_2

Mon Sep 28 15:27:05 2015

ALTER SYSTEM ARCHIVE LOG

Mon Sep 28 15:27:05 2015

Thread 1 cannot allocate new log, sequence 151

Checkpoint not complete

Current log# 1 seq# 150 mem# 0: /u01/app/oracle/oradata/oradgphy/redo01.log

Mon Sep 28 15:27:07 2015

ARC3: Becoming the 'no SRL' ARCH

Thread 1 advanced to log sequence 151 (LGWR switch)

Current log# 2 seq# 151 mem# 0: /u01/app/oracle/oradata/oradgphy/redo02.log

Archived Log entry 137 added for thread 1 sequence 150 ID 0x54960fb7 dest 1:

Mon Sep 28 15:27:07 2015

LNS: Standby redo logfile selected for thread 1 sequence 151 for destination LOG_ARCHIVE_DEST_2

至此,Data Guard Broker配置结束。

物理dg配置客户端无缝切换 (2)--Fast-Start Failover 的配置

实验环境介绍

项目主库dg库
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDoradg11goradgphy
db_nameoradg11goradg11g
主机IP地址:192.168.59.130192.168.59.130
OS版本及kernel版本RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64
OS hostnamerhel6_lhrrhel6_lhr

相关知识点扫盲

Fast-Start Failover是建立在broker基础上的一个快速故障转换的机制,通过fast-start failover可以自动检测primary的故障,然后自动的failover到预先指定的standby上面,这样可以最大化的减少故障时间,提高数据库的可用性。

Fast-Start Failover是在broker的基础上再增加了一个单独的observer,用来监控primary和standby数据库的状态,一旦primary不可用,observer就会自动的切换到指定的standby上面。

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

实验部分

实验目标

Fast-Start Failover 配置并完成实验。

前提准备条件

primary 与 standby 启用flashback database

在主备库上开启闪回功能,否则后续报错16651 :

[oracle@rhel6_lhr lhr]$ oerr ora 16651

16651, 0000, "requirements not met for enabling fast-start failover"

// *Cause: The attempt to enable fast-start failover could not be completed

// because one or more requirements were not met:

// - The Data Guard configuration must be in either MaxAvailability

// or MaxPerformance protection mode.

// - The LogXptMode property for both the primary database and

// the fast-start failover target standby database must be

// set to SYNC if the configuration protection mode is set to

// MaxAvailability mode.

// - The LogXptMode property for both the primary database and

// the fast-start failover target standby database must be

// set to ASYNC if the configuration protection mode is set to

// MaxPerformance mode.

// - The primary database and the fast-start failover target standby

// database must both have flashback enabled.

// - No valid target standby database was specified in the primary

// database FastStartFailoverTarget property prior to the attempt

// to enable fast-start failover, and more than one standby

// database exists in the Data Guard configuration.

// *Action: Retry the command after correcting the issue:

// - Set the Data Guard configuration to either MaxAvailability

// or MaxPerformance protection mode.

// - Ensure that the LogXptMode property for both the primary

// database and the fast-start failover target standby database

// are set to SYNC if the configuration protection mode is set to

// MaxAvailability.

// - Ensure that the LogXptMode property for both the primary

// database and the fast-start failover target standby database

// are set to ASYNC if the configuration protection mode is set to

// MaxPerformance.

// - Ensure that both the primary database and the fast-start failover

// target standby database have flashback enabled.

// - Set the primary database FastStartFailoverTarget property to

// the DB_UNIQUE_NAME value of the desired target standby database

// and the desired target standby database FastStartFailoverTarget

// property to the DB_UNIQUE_NAME value of the primary database.

主库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 09:50:17 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

09:50:17 SQL> set line 9999

09:50:18 SQL> col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

09:50:18 SQL> col DB_UNIQUE_NAME format a10

09:50:18 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DBUNIQUE\ CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2544025 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.01

09:50:18 SQL> SELECT d.DBID,

09:50:18 2 d.DB_UNIQUE_NAME,

09:50:18 3 d.FORCE_LOGGING,

09:50:18 4 d.FLASHBACK_ON,

09:50:18 5 d.FS_FAILOVER_STATUS,

09:50:18 6 d.FS_FAILOVER_CURRENT_TARGET,

09:50:18 7 d.FS_FAILOVER_THRESHOLD,

09:50:18 8 d.FS_FAILOVER_OBSERVER_PRESENT,

09:50:18 9 d.FS_FAILOVER_OBSERVER_HOST

09:50:18 10 FROM v$database d;

DBID DBUNIQUE\ FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- ---------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES DISABLED 0

已用时间: 00: 00: 00.01

09:50:24 SQL>

备库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:18:39 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:18:39 SQL> set line 9999

10:19:02 SQL> col name format a10

10:19:02 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:19:02 SQL> col DB_UNIQUE_NAME format a15

10:19:02 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

SELECT d.DBID,

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2545958 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

已用时间: 00: 00: 00.00

10:19:02 SQL> 10:19:02 2 d.DB_UNIQUE_NAME,

10:19:02 3 d.FORCE_LOGGING,

10:19:02 4 d.FLASHBACK_ON,

10:19:02 5 d.FS_FAILOVER_STATUS,

10:19:02 6 d.FS_FAILOVER_CURRENT_TARGET,

10:19:02 7 d.FS_FAILOVER_THRESHOLD,

10:19:02 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:19:02 9 d.FS_FAILOVER_OBSERVER_HOST

10:19:02 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES NO DISABLED 0

已用时间: 00: 00: 00.00

10:19:02 SQL> alter database flashback on;

alter database flashback on

*

第 1 行出现错误:

ORA-01153: 激活了不兼容的介质恢复

已用时间: 00: 00: 00.00

10:19:18 SQL> alter database recover managed standby database cancel;

数据库已更改。

已用时间: 00: 00: 01.01

10:19:34 SQL> alter database flashback on;

数据库已更改。

已用时间: 00: 00: 01.40

10:19:38 SQL> set line 9999

10:19:53 SQL> col name format a10

10:19:53 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:19:53 SQL> col DB_UNIQUE_NAME format a15

10:19:53 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2545994 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED

已用时间: 00: 00: 00.00

10:19:53 SQL> SELECT d.DBID,

10:19:53 2 d.DB_UNIQUE_NAME,

10:19:53 3 d.FORCE_LOGGING,

10:19:53 4 d.FLASHBACK_ON,

10:19:53 5 d.FS_FAILOVER_STATUS,

10:19:53 6 d.FS_FAILOVER_CURRENT_TARGET,

10:19:53 7 d.FS_FAILOVER_THRESHOLD,

10:19:53 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:19:53 9 d.FS_FAILOVER_OBSERVER_HOST

10:19:53 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES YES DISABLED 0

已用时间: 00: 00: 00.00

10:19:53 SQL>

确保broker配置为运行在MAX Availability模式

确保broker已经配置,同时运行模式为最大可用模式或者最大性能模式,如果数据库运行模式为最大可用模式,确保参数LogXptMode 配置为SYNC,如果是最大性能模式,则参数LogXptMode 应该为ASYNC

http://www.linuxidc.com/upload/2012_09/120909121264761.gif

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL> show resource verbose 'oradg11g' logxptmode on site 'oradg11g';

LogXptMode = 'ASYNC'

DGMGRL> show resource verbose 'oradgphy' logxptmode on site 'oradgphy';

LogXptMode = 'ASYNC'

DGMGRL> alter resource 'oradg11g' set property logxptmode='SYNC';

已更新属性 "logxptmode"

DGMGRL> alter resource 'oradgphy' set property logxptmode='SYNC';

已更新属性 "logxptmode"

DGMGRL> edit configuration set protection mode as maxavailability;

成功。

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL> show resource verbose 'oradg11g' logxptmode on site 'oradg11g';

LogXptMode = 'SYNC'

DGMGRL> show resource verbose 'oradgphy' logxptmode on site 'oradgphy';

LogXptMode = 'SYNC'

DGMGRL>

主库告警日志:

Tue Sep 29 10:31:27 2015

ALTER SYSTEM SET log_archive_dest_2='service="tns_oradgphy_dgmgrl"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="oradgphy" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;

ALTER SYSTEM SWITCH ALL LOGFILE start (oradg11g)

Tue Sep 29 10:31:27 2015

Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED

Tue Sep 29 10:31:27 2015

NSS2 started with pid=37, OS id=46913

LGWR: Standby redo logfile selected for thread 1 sequence 160 for destination LOG_ARCHIVE_DEST_2

ALTER SYSTEM SWITCH ALL LOGFILE complete (oradg11g)

Thread 1 advanced to log sequence 160 (LGWR switch)

Current log# 2 seq# 160 mem# 0: /u01/app/oracle/oradata/oradg11g/redo02.log

Tue Sep 29 10:31:30 2015

Archived Log entry 522 added for thread 1 sequence 159 ID 0x5495fd70 dest 1:

Tue Sep 29 10:31:31 2015

ARC3: Archive log rejected (thread 1 sequence 159) at host 'tns_oradgphy_dgmgrl'

FAL[server, ARC3]: FAL archive failed, see trace file.

ARCH: FAL archive failed. Archiver continuing

ORACLE Instance oradg11g - Archival Error. Archiver continuing.

Tue Sep 29 10:31:44 2015

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

ALTER SYSTEM ARCHIVE LOG

Tue Sep 29 10:31:45 2015

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Standby redo logfile selected to archive thread 1 sequence 161

LGWR: Standby redo logfile selected for thread 1 sequence 161 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 161 (LGWR switch)

Current log# 3 seq# 161 mem# 0: /u01/app/oracle/oradata/oradg11g/redo03.log

Archived Log entry 525 added for thread 1 sequence 160 ID 0x5495fd70 dest 1:

备库告警日志:

Tue Sep 29 10:31:30 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[4]: Assigned to RFS process 46919

RFS[4]: Selected log 5 for thread 1 sequence 160 dbid 1403587593 branch 886695024

Tue Sep 29 10:31:30 2015

Archived Log entry 148 added for thread 1 sequence 159 ID 0x5495fd70 dest 1:

Tue Sep 29 10:31:31 2015

Media Recovery Waiting for thread 1 sequence 160 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 160 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo05.log

Tue Sep 29 10:31:44 2015

Archived Log entry 149 added for thread 1 sequence 160 ID 0x5495fd70 dest 1:

Tue Sep 29 10:31:44 2015

Media Recovery Waiting for thread 1 sequence 161

Tue Sep 29 10:31:45 2015

Primary database is in MAXIMUM AVAILABILITY mode

Changing standby controlfile to MAXIMUM AVAILABILITY mode

Standby controlfile consistent with primary

RFS[5]: Assigned to RFS process 46931

RFS[5]: Selected log 4 for thread 1 sequence 161 dbid 1403587593 branch 886695024

Recovery of Online Redo Log: Thread 1 Group 4 Seq 161 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log

---从数据库层次查看配置情况是否修改:

主库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:43:18 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:43:18 SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service="tns_oradgphy_dgmgrl",

LGWR SYNC AFFIRM delay=0 opti

onal compression=disable max_f

ailure=0 max_connections=1 reo

pen=300 db_unique_name="oradgp

hy" net_timeout=30, valid_for=

(all_logfiles,primary_role)

log_archive_dest_20 string

log_archive_dest_21 string

log_archive_dest_22 string

log_archive_dest_23 string

log_archive_dest_24 string

log_archive_dest_25 string

log_archive_dest_26 string

log_archive_dest_27 string

log_archive_dest_28 string

log_archive_dest_29 string

10:43:20 SQL> set line 9999

10:43:30 SQL> col name format a10

10:43:30 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:43:30 SQL> col DB_UNIQUE_NAME format a15

10:43:30 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2547638 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.00

10:43:30 SQL> SELECT d.DBID,

10:43:30 2 d.DB_UNIQUE_NAME,

10:43:30 3 d.FORCE_LOGGING,

10:43:30 4 d.FLASHBACK_ON,

10:43:30 5 d.FS_FAILOVER_STATUS,

10:43:30 6 d.FS_FAILOVER_CURRENT_TARGET,

10:43:30 7 d.FS_FAILOVER_THRESHOLD,

10:43:30 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:43:30 9 d.FS_FAILOVER_OBSERVER_HOST

10:43:30 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES DISABLED 0

已用时间: 00: 00: 00.01

10:43:30 SQL>

备库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:43:53 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:43:53 SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service="tns_oradg11g_dgmgrl",

LGWR ASYNC NOAFFIRM delay=0 o

ptional compression=disable ma

x_failure=0 max_connections=1

reopen=300 db_unique_name="ora

dg11g" net_timeout=30, valid_f

or=(all_logfiles,primary_role)

log_archive_dest_20 string

log_archive_dest_21 string

log_archive_dest_22 string

log_archive_dest_23 string

log_archive_dest_24 string

log_archive_dest_25 string

log_archive_dest_26 string

log_archive_dest_27 string

log_archive_dest_28 string

log_archive_dest_29 string

10:43:54 SQL> set line 9999

10:44:01 SQL> col name format a10

10:44:01 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:44:01 SQL> col DB_UNIQUE_NAME format a15

10:44:01 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2547673 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

已用时间: 00: 00: 00.00

10:44:01 SQL> SELECT d.DBID,

10:44:01 2 d.DB_UNIQUE_NAME,

10:44:01 3 d.FORCE_LOGGING,

10:44:01 4 d.FLASHBACK_ON,

10:44:01 5 d.FS_FAILOVER_STATUS,

10:44:01 6 d.FS_FAILOVER_CURRENT_TARGET,

10:44:01 7 d.FS_FAILOVER_THRESHOLD,

10:44:01 8 d.FS_FAILOVER_OBSERVER_PRESENT,

10:44:01 9 d.FS_FAILOVER_OBSERVER_HOST

10:44:01 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES YES DISABLED 0

已用时间: 00: 00: 00.00

10:44:01 SQL>

启动observer观察进程

选定第三台机器,安装DGMGRL,用于启动observer,这里命令为observer server,配置observer server的配置tnsnames.ora文件,保证observer能正常连接到 primary 和 standby 数据库,我们测试就使用同一台机器测试。

新开一个单独的窗口:

[oracle@rhel6_lhr lhr]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl "start observer"

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

观察程序已启动

该窗口一直挂起。。。。
注意启动observer后,DGMGRL就会阻塞在这个命令上。observer的操作信息以后会在这个窗口显示,有启动就有关闭,如下:

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl "stop observer"

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

完成。

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl "start observer"

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

观察程序已启动

配置FastStartFailover

配置每个数据库Failover的目标。这一步是决定当数据库出问题后会自动failover目标

DGMGRL> edit database 'oradg11g' set property 'FastStartFailoverTarget'='oradgphy';

已更新属性 "FastStartFailoverTarget"

DGMGRL>

DGMGRL> edit database 'oradgphy' set property 'FastStartFailoverTarget'='oradg11g';

已更新属性 "FastStartFailoverTarget"

设定FastStartFailoverThreshold值

这个设置是决定了primary坏了多长时间之后会执行自动的failover操作。这里设置的是30s

DGMGRL> edit configuration set property FastStartFailoverThreshold=30;

已更新属性 "faststartfailoverthreshold"

启用Fast-Start Failover

DGMGRL> ENABLE FAST_START FAILOVER;

已启用。

DGMGRL> SHOW FAST_START FAILOVER;

快速启动故障转移: ENABLED

阈值: 30 秒

目标: oradgphy

观察程序: rhel6_lhr

滞后限制: 30 秒 (未使用)

关闭主数据库: TRUE

自动恢复: TRUE

可配置的故障转移条件

健康状况:

Corrupted Controlfile YES

Corrupted Dictionary YES

Inaccessible Logfile NO

Stuck Archiver NO

Datafile Offline YES

Oracle 错误条件:

(无)

DGMGRL>

主库告警日志:

Tue Sep 29 11:09:03 2015

Fast-Start Failover (FSFO) has been enabled between:

Primary = "oradg11g"

Standby = "oradgphy"

Tue Sep 29 11:09:03 2015

FSFP started with pid=42, OS id=49349

从告警日志可以看出,主库上启动了一个进程fsfp的进程:

[oracle@rhel6_lhr ~]$ ps -ef|grep fsfp

oracle 49349 1 0 11:09 ? 00:00:00 ora_fsfp_oradg11g

oracle 49383 43618 0 11:09 pts/1 00:00:00 grep fsfp

[oracle@rhel6_lhr ~]$

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration verbose

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradg11g - 主数据库

oradgphy - (*) 物理备用数据库

(*) 快速启动故障转移目标

属性:

FastStartFailoverThreshold = '30'

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

快速启动故障转移: ENABLED

阈值: 30 秒

目标: oradgphy

观察程序: rhel6_lhr

滞后限制: 30 秒 (未使用)

关闭主数据库: TRUE

自动恢复: TRUE

配置状态:

SUCCESS

DGMGRL>

数据库级别查看,主库:

11:11:56 SQL> set line 9999

11:12:06 SQL> col name format a10

11:12:06 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:12:06 SQL> col DB_UNIQUE_NAME format a15

11:12:06 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2549890 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.00

11:12:06 SQL> SELECT d.DBID,

11:12:06 2 d.DB_UNIQUE_NAME,

11:12:06 3 d.FORCE_LOGGING,

11:12:06 4 d.FLASHBACK_ON,

11:12:06 5 d.FS_FAILOVER_STATUS,

11:12:06 6 d.FS_FAILOVER_CURRENT_TARGET,

11:12:06 7 d.FS_FAILOVER_THRESHOLD,

11:12:06 8 d.FS_FAILOVER_OBSERVER_PRESENT,

11:12:06 9 d.FS_FAILOVER_OBSERVER_HOST

11:12:06 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES SYNCHRONIZED oradgphy 30 YES rhel6_lhr

已用时间: 00: 00: 00.00

11:12:06 SQL>

备库:

11:11:29 SQL> set line 9999

11:12:13 SQL> col name format a10

11:12:13 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:12:13 SQL> col DB_UNIQUE_NAME format a15

11:12:13 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2549900 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

已用时间: 00: 00: 00.00

11:12:13 SQL> SELECT d.DBID,

11:12:13 2 d.DB_UNIQUE_NAME,

11:12:13 3 d.FORCE_LOGGING,

11:12:13 4 d.FLASHBACK_ON,

11:12:13 5 d.FS_FAILOVER_STATUS,

11:12:13 6 d.FS_FAILOVER_CURRENT_TARGET,

11:12:13 7 d.FS_FAILOVER_THRESHOLD,

11:12:14 8 d.FS_FAILOVER_OBSERVER_PRESENT,

11:12:14 9 d.FS_FAILOVER_OBSERVER_HOST

11:12:14 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES YES SYNCHRONIZED oradgphy 30 YES rhel6_lhr

已用时间: 00: 00: 00.01

11:12:14 SQL>

测试Fast-Start Failover 的功能

Shutdown abort 主库

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:18:18 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

11:18:18 SQL> set line 9999

11:18:19 SQL> col name format a10

11:18:19 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:18:19 SQL> col DB_UNIQUE_NAME format a15

11:18:19 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2550295 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.00

11:18:19 SQL> SELECT d.DBID,

11:18:20 2 d.DB_UNIQUE_NAME,

11:18:20 3 d.FORCE_LOGGING,

11:18:20 4 d.FLASHBACK_ON,

11:18:20 5 d.FS_FAILOVER_STATUS,

11:18:20 6 d.FS_FAILOVER_CURRENT_TARGET,

11:18:20 7 d.FS_FAILOVER_THRESHOLD,

11:18:20 8 d.FS_FAILOVER_OBSERVER_PRESENT,

11:18:20 9 d.FS_FAILOVER_OBSERVER_HOST

11:18:20 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES SYNCHRONIZED oradgphy 30 YES rhel6_lhr

已用时间: 00: 00: 00.00

11:18:20 SQL> shutdown abort;

ORACLE 例程已经关闭。

11:18:27 SQL>

查看告警日志及server窗口

11:18:58.99 2015年9月29日 星期二

正在为数据库 "oradgphy" 启动快速启动故障转移...

立即执行故障转移, 请稍候...

故障转移成功, 新的主数据库为 "oradgphy"

11:19:04.72 2015年9月29日 星期二

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration verbose

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradgphy - 主数据库

警告: ORA-16817: 快速启动故障转移配置不同步

oradg11g - (*) 物理备用数据库 (禁用)

ORA-16661: 需要恢复备用数据库

(*) 快速启动故障转移目标

属性:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

快速启动故障转移: ENABLED

阈值: 30 秒

目标: oradg11g

观察程序: rhel6_lhr

滞后限制: 30 秒 (未使用)

关闭主数据库: TRUE

自动恢复: TRUE

配置状态:

WARNING

DGMGRL>

备库告警日志:

Tue Sep 29 11:18:26 2015

RFS[5]: Possible network disconnect with primary database

Tue Sep 29 11:18:26 2015

RFS[6]: Assigned to RFS process 46955

RFS[6]: Possible network disconnect with primary database

Tue Sep 29 11:18:26 2015

RFS[7]: Assigned to RFS process 46921

RFS[7]: Possible network disconnect with primary database

Tue Sep 29 11:18:58 2015

Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed.

Tue Sep 29 11:18:59 2015

Data Guard Broker: Beginning failover

Tue Sep 29 11:18:59 2015

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Tue Sep 29 11:18:59 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_46860.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 2550301

Tue Sep 29 11:18:59 2015

MRP0: Background Media Recovery process shutdown (oradgphy)

Managed Standby Recovery Canceled (oradgphy)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE

Attempt to do a Terminal Recovery (oradgphy)

Media Recovery Start: Managed Standby Recovery (oradgphy)

started logmerger process

Tue Sep 29 11:19:00 2015

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

Terminal Recovery timestamp is '09/29/2015 11:19:00'

Terminal Recovery: applying standby redo logs.

Terminal Recovery: thread 1 seq# 163 redo required

Terminal Recovery:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 163 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log

Identified End-Of-Redo (failover) for thread 1 sequence 163 at SCN 0xffff.ffffffff

Incomplete Recovery applied until change 2550302 time 09/29/2015 11:18:25

Media Recovery Complete (oradgphy)

Terminal Recovery: successful completion

Tue Sep 29 11:19:00 2015

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance oradgphy - Archival Error

ORA-16014: log 4 sequence# 163 not archived, no available destinations

ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo04.log'

Forcing ARSCN to IRSCN for TR 0:2550302

Attempt to set limbo arscn 0:2550302 irscn 0:2550302

Resetting standby activation ID 1419115888 (0x5495fd70)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN

ALTER DATABASE SWITCHOVER TO PRIMARY (oradgphy)

Maximum wait for role transition is 15 minutes.

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

CLOSE: all sessions shutdown successfully.

Tue Sep 29 11:19:03 2015

SMON: disabling cache recovery

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_rsm0_45481.trc

Standby terminal recovery start SCN: 2550301

RESETLOGS after incomplete recovery UNTIL CHANGE 2550302

Online log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 2550300

Tue Sep 29 11:19:03 2015

Setting recovery target incarnation to 5

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

Switchover: Complete - Database mounted as primary

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

ALTER DATABASE OPEN

Data Guard Broker initializing...

Tue Sep 29 11:19:03 2015

Assigning activation ID 1419206889 (0x549760e9)

LGWR: Primary database is in MAXIMUM AVAILABILITY mode

LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O

LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

Thread 1 advanced to log sequence 2 (thread open)

Tue Sep 29 11:19:03 2015

ARC3: Becoming the 'no SRL' ARCH

ARC0: Becoming the 'no SRL' ARCH

ARC3: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch

Thread 1 opened at log sequence 2

Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/oradgphy/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Tue Sep 29 11:19:03 2015

NSA2 started with pid=17, OS id=49982

Error 1034 received logging on to the standby

ARC3: Error 1034 Creating archive log file to 'tns_oradg11g_dgmgrl'

Archived Log entry 152 added for thread 1 sequence 1 ID 0x549760e9 dest 1:

Archiver process freed from errors. No longer stopped

Tue Sep 29 11:19:03 2015

Error 1034 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 1034.

[45481] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:44509384 end:44509514 diff:130 (1 seconds)

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is ZHS16GBK

Starting background process SMCO

Tue Sep 29 11:19:04 2015

SMCO started with pid=18, OS id=49996

No Resource Manager plan active

Starting background process QMNC

Tue Sep 29 11:19:04 2015

QMNC started with pid=20, OS id=49998

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: ALTER DATABASE OPEN

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradgphy';

ALTER SYSTEM SET log_archiveformat='%t%s_%r.dbf' SCOPE=SPFILE SID='oradgphy';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';

ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';

ALTER SYSTEM SET db_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

Failover succeeded. Primary database is now oradgphy.

Tue Sep 29 11:19:04 2015

idle dispatcher 'D000' terminated, pid = (17, 1)

Starting background process CJQ0

Tue Sep 29 11:19:04 2015

CJQ0 started with pid=34, OS id=50027

Thread 1 advanced to log sequence 3 (LGWR switch)

Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/oradgphy/redo03.log

ARC3: STARTING ARCH PROCESSES

Tue Sep 29 11:19:06 2015

ARC4 started with pid=26, OS id=50033

Tue Sep 29 11:19:07 2015

FSFP started with pid=35, OS id=50037

ARC4: Archival started

ARC3: STARTING ARCH PROCESSES COMPLETE

ARC3: Becoming the 'no SRL' ARCH

krsk_srl_archive_int: Enabling archival of deferred physical standby SRLs

Archived Log entry 153 added for thread 1 sequence 2 ID 0x549760e9 dest 1:

Archived Log entry 154 added for thread 1 sequence 163 ID 0x5495fd70 dest 1:

Shutting down archive processes

ARCH shutting down

ARC4: Archival stopped

Tue Sep 29 11:21:44 2015

ARC0: Becoming the 'no SRL' ARCH

Tue Sep 29 11:21:45 2015

ARC1: Becoming the 'no SRL' ARCH

在sqlplus手动启动原主库到mount状态,并观察dgmgrl的server状态

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:22:55 2015

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

已连接到空闲例程。

11:22:55 SQL> startup mount;

ORACLE 例程已经启动。

Total System Global Area 417546240 bytes

Fixed Size 2228944 bytes

Variable Size 385879344 bytes

Database Buffers 20971520 bytes

Redo Buffers 8466432 bytes

数据库装载完毕。

Server 窗口:

11:24:55.93 2015年9月29日 星期二

正在为数据库 "oradg11g" 启动恢复过程...

正在恢复数据库 "oradg11g", 请稍候...

操作要求关闭实例 "oradg11g" (在数据库 "oradg11g" 上)

正在关闭实例 "oradg11g"...

ORA-01109: 数据库未打开

已经卸载数据库。

ORACLE 例程已经关闭。

操作要求启动实例 "oradg11g" (在数据库 "oradg11g" 上)

正在启动实例 "oradg11g"...

ORACLE 例程已经启动。

数据库装载完毕。

继续恢复数据库 "oradg11g"...

已成功恢复数据库 "oradg11g"

11:26:03.30 2015年9月29日 星期二

DGMGRL> show configuration verbose

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradgphy - 主数据库

oradg11g - (*) 物理备用数据库

(*) 快速启动故障转移目标

属性:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

快速启动故障转移: ENABLED

阈值: 30 秒

目标: oradg11g

观察程序: rhel6_lhr

滞后限制: 30 秒 (未使用)

关闭主数据库: TRUE

自动恢复: TRUE

配置状态:

ORA-16610: 命令 "REINSTATE DATABASE oradg11g" 正在进行中

DGM-17017: 无法确定配置状态

DGMGRL> show configuration verbose

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradgphy - 主数据库

oradg11g - (*) 物理备用数据库

(*) 快速启动故障转移目标

属性:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

快速启动故障转移: ENABLED

阈值: 30 秒

目标: oradg11g

观察程序: rhel6_lhr

滞后限制: 30 秒 (未使用)

关闭主数据库: TRUE

自动恢复: TRUE

配置状态:

SUCCESS

DGMGRL>

可以看到状态正常, FSF生效。

测试新的环境是否同步

11:26:56 SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 7

下一个存档日志序列 9

当前日志序列 9

11:31:46 SQL> set line 9999

11:31:50 SQL> col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

11:31:50 SQL> col DB_UNIQUE_NAME format a15

11:31:50 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2551424 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.00

11:31:50 SQL> SELECT d.DBID,

11:31:50 2 d.DB_UNIQUE_NAME,

11:31:50 3 d.FORCE_LOGGING,

11:31:50 4 d.FLASHBACK_ON,

11:31:50 5 d.FS_FAILOVER_STATUS,

11:31:50 6 d.FS_FAILOVER_CURRENT_TARGET,

11:31:50 7 d.FS_FAILOVER_THRESHOLD,

11:31:50 8 d.FS_FAILOVER_OBSERVER_PRESENT,

11:31:50 9 d.FS_FAILOVER_OBSERVER_HOST

11:31:50 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES YES SYNCHRONIZED oradg11g 30 YES rhel6_lhr

已用时间: 00: 00: 00.00

已用时间: 00: 00: 00.07

11:32:56 SQL> create table lhr.testfsfdg as select * from dual;

表已创建。

已用时间: 00: 00: 00.36

11:33:05 SQL> select * from lhr.testfsfdg ;

D

-

X

已用时间: 00: 00: 00.01

11:33:15 SQL>

备库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:31:39 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

11:31:39 SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 8

下一个存档日志序列 0

当前日志序列 9

11:31:41 SQL> set line 9999

11:31:55 SQL> col name format a10

11:31:55 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:31:55 SQL> col DB_UNIQUE_NAME format a15

11:31:55 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

SELECT d.DBID,

DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2551429 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

已用时间: 00: 00: 00.01

11:31:55 SQL> 11:31:55 2 d.DB_UNIQUE_NAME,

11:31:55 3 d.FORCE_LOGGING,

11:31:55 4 d.FLASHBACK_ON,

11:31:55 5 d.FS_FAILOVER_STATUS,

11:31:55 6 d.FS_FAILOVER_CURRENT_TARGET,

11:31:55 7 d.FS_FAILOVER_THRESHOLD,

11:31:55 8 d.FS_FAILOVER_OBSERVER_PRESENT,

11:31:55 9 d.FS_FAILOVER_OBSERVER_HOST

11:31:55 10 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES SYNCHRONIZED oradg11g 30 YES rhel6_lhr

已用时间: 00: 00: 00.01

11:31:55 SQL> select * from lhr.testfsfdg ;

D

-

X

已用时间: 00: 00: 00.00

11:33:21 SQL>

11:33:21 SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 8

下一个存档日志序列 0

当前日志序列 9

11:35:39 SQL>

可以看到日志序列号已经重新开始了。

重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态

我们重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态,

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 13:43:03 2015

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

13:43:03 SQL> set line 9999

13:43:17 SQL> col name format a10

13:43:17 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

13:43:17 SQL> col DB_UNIQUE_NAME format a15

13:43:17 SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2550303 2575340 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY

已用时间: 00: 00: 00.01

13:43:17 SQL> SELECT d.DBID,

13:43:17 2 d.DB_UNIQUE_NAME,

13:43:17 3 d.FORCE_LOGGING,

13:43:17 4 d.FLASHBACK_ON,

13:43:17 5 DATAGUARD_BROKER,

13:43:17 6 d.FS_FAILOVER_STATUS,

13:43:17 7 d.FS_FAILOVER_CURRENT_TARGET,

13:43:17 8 d.FS_FAILOVER_THRESHOLD,

13:43:17 9 d.FS_FAILOVER_OBSERVER_PRESENT,

13:43:17 10 d.FS_FAILOVER_OBSERVER_HOST

13:43:17 11 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON DATAGUAR FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES YES ENABLED SYNCHRONIZED oradg11g 30 YES rhel6_lhr

已用时间: 00: 00: 00.00

13:43:17 SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 12

下一个存档日志序列 14

当前日志序列 14

13:43:21 SQL> shutdown abort;

ORACLE 例程已经关闭。

13:43:26 SQL>

手动启动备库到mount状态后继续查看:

13:45:15 SQL> set line 9999

13:48:27 SQL> col name format a10

13:48:27 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

13:48:27 SQL> col DB_UNIQUE_NAME format a15

13:48:27 SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2575356 2575896 MAXIMUM AVAILABILITY RESYNCHRONIZATION PRIMARY YES READ WRITE NOT ALLOWED

已用时间: 00: 00: 00.00

13:48:27 SQL> SELECT d.DBID,

13:48:27 2 d.DB_UNIQUE_NAME,

13:48:27 3 d.FORCE_LOGGING,

13:48:27 4 d.FLASHBACK_ON,

13:48:27 5 DATAGUARD_BROKER,

13:48:27 6 d.FS_FAILOVER_STATUS,

13:48:27 7 d.FS_FAILOVER_CURRENT_TARGET,

13:48:27 8 d.FS_FAILOVER_THRESHOLD,

13:48:27 9 d.FS_FAILOVER_OBSERVER_PRESENT,

13:48:27 10 d.FS_FAILOVER_OBSERVER_HOST

13:48:27 11 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON DATAGUAR FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES ENABLED REINSTATE REQUIRED oradgphy 30 YES rhel6_lhr

已用时间: 00: 00: 00.00

13:48:27 SQL> archive log list

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 1

下一个存档日志序列 3

当前日志序列 3

13:49:46 SQL>

至此,Fast-Start Failover 的配置及其测试完成。

物理dg配置客户端无缝切换 (3)--客户端TAF 配置

实验环境介绍

项目主库dg库
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDoradg11goradgphy
db_nameoradg11goradg11g
主机IP地址:192.168.59.130192.168.59.130
OS版本及kernel版本RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64
OS hostnamerhel6_lhrrhel6_lhr

实验部分

实验目标

完成Oracle DataGuard 之客户端TAF 配置,并测试无缝切换。

客户端TAF 配置

首先在主库上配置一个TAF的service

此服务在数据库出现故障时会发送通知给客户端,允许查询语句在故障转移发生后继续运行。

在主库端运行:

14:51:45 SQL> begin

14:52:10 2 DBMS_SERVICE.CREATE_SERVICE(service_name => 'dg_taf_lhr',

14:52:10 3 network_name => 'dg_taf_lhr',

14:52:10 4 aq_ha_notifications => TRUE,

14:52:10 5 failover_method => 'BASIC',

14:52:10 6 failover_type => 'SELECT',

14:52:10 7 failover_retries => 30,

14:52:10 8 failover_delay => 5);

14:52:10 9 end;

14:52:10 10 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.07

14:52:10 SQL>

建立一个存储过程,用于调用service,确保只在主库运行

我们创建一个存储过程来实现此目的,如果当前数据库是主库它就启动此服务,如果是备库就停止。

主库执行:

14:54:58 SQL> create or replace procedure dg_taf_proc_lhr is

14:59:46 2 v_role VARCHAR(30);

14:59:46 3 begin

14:59:46 4 select DATABASE_ROLE into v_role from V$DATABASE;

14:59:46 5 if v_role = 'PRIMARY' then

14:59:46 6 DBMS_SERVICE.START_SERVICE('dg_taf_lhr');

14:59:46 7 else

14:59:46 8 DBMS_SERVICE.STOP_SERVICE('dg_taf_lhr');

14:59:46 9 end if;

14:59:46 10 end;

14:59:46 11 /

过程已创建。

已用时间: 00: 00: 00.07

创建1个触发器来确保服务可以运行

创建两个触发器,让数据库在启动和角色转换时运行此存储过程。用于当数据库open时,不需要重启数据库,如果是主库则执行存储过程。当数据库切换后,如果是主库则执行存储过程。

主库执行:

14:59:47 SQL> create or replace TRIGGER dg_taf_trg_startup_lhr

14:59:53 2 after startup or db_role_change on database

14:59:53 3 begin

14:59:53 4 dg_taf_proc_lhr;

14:59:53 5 end;

14:59:53 6 /

触发器已创建

已用时间: 00: 00: 00.26

14:59:54 SQL>

启动新创建的service

在主库上执行该存储过程(或者重启数据库,在启动数据库时会触发执行dg_taf_proc的触发器),在主库做日志的切换,将变化应用到备库

主库执行:

14:59:54 SQL> exec dg_taf_proc_lhr ;

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.01

15:06:57 SQL> alter system switch logfile;

系统已更改。

已用时间: 00: 00: 01.02

15:20:01 SQL>

[oracle@rhel6_lhr ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-9月 -2015 15:07:32

Copyright (c) 1991, 2011, Oracle. All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521)))

LISTENER 的 STATUS

------------------------

别名 LISTENER

版本 TNSLSNR for Linux: Version 11.2.0.3.0 - Production

启动日期 29-9月 -2015 12:14:49

正常运行时间 0 天 2 小时 52 分 43 秒

跟踪级别 off

安全性 ON: Local OS Authentication

SNMP OFF

监听程序参数文件 /u01/app/grid/11.2.0/network/admin/listener.ora

监听端点概要...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521)))

服务摘要..

服务 "+ASM" 包含 1 个实例。

实例 "+ASM", 状态 READY, 包含此服务的 1 个处理程序...

服务 "PLSExtProc" 包含 1 个实例。

实例 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "dg_taf_lhr.lhr.com" 包含 1 个实例。

实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序...

服务 "ora11g" 包含 1 个实例。

实例 "ora11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "oradg11g" 包含 1 个实例。

实例 "oradg11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "oradg11g.lhr.com" 包含 2 个实例。

实例 "oradg11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序...

服务 "oradg11gXDB.lhr.com" 包含 2 个实例。

实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序...

实例 "oradgphy", 状态 READY, 包含此服务的 1 个处理程序...

服务 "oradg11g_DGB.lhr.com" 包含 1 个实例。

实例 "oradg11g", 状态 READY, 包含此服务的 1 个处理程序...

服务 "oradg11g_DGMGRL.lhr.com" 包含 1 个实例。

实例 "oradg11g", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "oradglg" 包含 1 个实例。

实例 "oradglg", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "oradgphy" 包含 1 个实例。

实例 "oradgphy", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "oradgphy.lhr.com" 包含 2 个实例。

实例 "oradgphy", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

实例 "oradgphy", 状态 READY, 包含此服务的 1 个处理程序...

服务 "oradgphy_DGB.lhr.com" 包含 1 个实例。

实例 "oradgphy", 状态 READY, 包含此服务的 1 个处理程序...

服务 "oradgphy_DGMGRL.lhr.com" 包含 1 个实例。

实例 "oradgphy", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "oradgss" 包含 1 个实例。

实例 "oradgss", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "orclasm.lhr.com" 包含 1 个实例。

实例 "orclasm", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

命令执行成功

在备库查询,确认触发器和存过已经应用到备库

15:21:21 SQL> select trigger_name, trigger_name

15:22:09 2 from dba_triggers where trigger_name = 'DG_TAF_TRG_STARTUP_LHR';

TRIGGER_NAME TRIGGER_NAME

------------------------------ ------------------------------

DG_TAF_TRG_STARTUP_LHR DG_TAF_TRG_STARTUP_LHR

已用时间: 00: 00: 00.09

15:22:09 SQL>

15:24:28 SQL> select d.owner,d.OBJECT_NAME

15:24:54 2 from dba_procedures d

15:24:54 3 where d.OBJECT_NAME = 'DG_TAF_PROC_LHR';

OWNER OBJECT_NAME

------------------------------ ------------------------------

SYS DG_TAF_PROC_LHR

已用时间: 00: 00: 00.05

15:24:54 SQL>

客户端tnsnames 配置

dg_taf =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.59.130)(PORT = 1521))

(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.59.130)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg_taf_lhr.lhr.com)

(FAILOVER_MODE =

(TYPE = session)

(METHOD = basic)

(RETRIES = 180)

(DELAY = 5)

)

)

)

注:我的dg环境由于部署在同一台机器,所以host一样,生产环境下必然不一样。

验证客户端的TAF

先说说测试过程,首先我们在windows环境下添加tnsnames,然后cmd中连接到dg环境,执行一个长久的查询(select * from (select * from sys.dba_objects);),此时在dgmgrl中手动shutdown abort掉主库,那么连接的cmd中会停顿一会,等待fast-start failover切换完成后,则继续返回结果。

主备切换不影响用户的select操作,但是如果是dml操作,则所有事务回滚:

D:\Users\xiaomaimiao>sqlplus lhr/lhr@dg_taf

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 29 15:37:20 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set line 9999

SQL> col name format a10

SQL> col FS_FAILOVER_OBSERVER_HOST format a20

SQL> col DB_UNIQUE_NAME format a15

SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradg11g 2575356 2604578 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY YES READ WRITE TO STANDBY

SQL> SELECT d.DBID,

2 d.DB_UNIQUE_NAME,

3 d.FORCE_LOGGING,

4 d.FLASHBACK_ON,

5 DATAGUARD_BROKER,

6 d.FS_FAILOVER_STATUS,

7 d.FS_FAILOVER_CURRENT_TARGET,

8 d.FS_FAILOVER_THRESHOLD,

9 d.FS_FAILOVER_OBSERVER_PRESENT,

10 d.FS_FAILOVER_OBSERVER_HOST

11 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON DATAGUAR FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g YES YES ENABLED SYNCHRONIZED oradgphy 30 YES rhel6_lhr

SQL>

可以看到连接到的是主库。

单实例物理DG配置客户端无缝切换的详细过程

Cmd卡住了,等待observer切换完成后cmd界面继续查询:

15:44:42.75 2015年9月29日 星期二

正在为数据库 "oradgphy" 启动快速启动故障转移...

立即执行故障转移, 请稍候...

故障转移成功, 新的主数据库为 "oradgphy"

15:44:49.93 2015年9月29日 星期二

单实例物理DG配置客户端无缝切换的详细过程

SQL> set line 9999

SQL> col name format a10

SQL> col FS_FAILOVER_OBSERVER_HOST format a20

SQL> col DB_UNIQUE_NAME format a15

SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

DBID NAME DB_UNIQUE_NAME RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS

---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G oradgphy 2605058 2605468 MAXIMUM AVAILABILITY RESYNCHRONIZATION PRIMARY YES READ WRITE NOT ALLOWED

SQL> SELECT d.DBID,

2 d.DB_UNIQUE_NAME,

3 d.FORCE_LOGGING,

4 d.FLASHBACK_ON,

5 DATAGUARD_BROKER,

6 d.FS_FAILOVER_STATUS,

7 d.FS_FAILOVER_CURRENT_TARGET,

8 d.FS_FAILOVER_THRESHOLD,

9 d.FS_FAILOVER_OBSERVER_PRESENT,

10 d.FS_FAILOVER_OBSERVER_HOST

11 FROM v$database d;

DBID DB_UNIQUE_NAME FOR FLASHBACK_ON DATAGUAR FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy YES YES ENABLED REINSTATE REQUIRED oradg11g 30 YES rhel6_lhr

SQL>

切换后,在备库上存储过程启动了TAF的service ,客户端再连接时,自动连接到了当前的主库(原备库)。

@至此,在DG环境下客户端的TAF配置基本完成。

题外话:Java中的配置

使用tnsnames 配置

原文链接:http://aijuans.iteye.com/blog/1488998

格式一: Oracle JDBC Thin using a ServiceName:

jdbc:oracle:thin:@//\:\/\
Example: jdbc:oracle:thin:@//192.168.2.1:1521/XE

注意这里的格式,@后面有//, 这是与使用SID的主要区别。

这种格式是Oracle 推荐的格式,因为对于集群来说,每个节点的SID 是不一样的,但是SERVICE_NAME 确可以包含所有节点。

格式二: Oracle JDBC Thin using an SID:

jdbc:oracle:thin:@\:\:\
Example: jdbc:oracle:thin:192.168.2.1:1521:X01A

Note: Support for SID is being phased out. Oracle recommends that users switch over to usingservice names.

格式三:Oracle JDBC Thin using a TNSName:

jdbc:oracle:thin:@\
Example: jdbc:oracle:thin:@GL

Note:
Support for TNSNames was added in the driver release 10.2.0.1

二.测试

2.1 准备工作:

Oracle 是11gR2

Listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dave)

(ORACLE_HOME =D:\app\Administrator\product\11.2.0\dbhome_1)

(SID_NAME = NEWCCS)

)

)

Tnsnames.ora

DVD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dave)

)

)

2.3 使用 service_name:dave

将2.2 节的dbUrl 改成如下:

String dbUrl = "jdbc:oracle:thin:@//127.0.0.1:1521/dave";

输出结果:

MGMT_VIEW--97

ANQING--94

DVD--93

SYSMAN--95

如果在11g里遇到如下错误:

测试运行Java 类,报错:

java.sql.SQLException: The Network Adapter could not establish the connection

可以尝试更换对应的 jdbc connection driver,官网的说明如下:

JDBC Thin Driver 11g Causes"Java.Sql.Sqlexception: Io Exception: The Network Adapter Could NotEstablish The Connection" While Connecting to Oracle Database 11g [ID947653.1]

Change the JDBC connection driver class inyour application server from:

oracle.jdbc.driver.OracleDriver

to

oracle.jdbc.OracleDriver

2.4 使用TNS name: dvd

String dbUrl = "jdbc:oracle:thin:@dvd";

报错如下:

java.sql.SQLException: Unknown host specified

该问题是因为JVM 没有oracle.net.tns_admin的system property。 解决方法有2种:

方法一:在启动VM 时添加如下参数:

-Doracle.net.tns_admin=D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

方法二:在java 代码里添加:

System.setProperty("oracle.net.tns_admin","D:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\NETWORK\\ADMIN");

添加之后,就可以正常在JDBC中使用tnsnama了。

Java代码 IMG_257

  1. 2.2 测试1,使用SID:newccs
  2. [java] view plaincopy
  3. import java.sql.*;
  4. public class jdbc {
  5. String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:newccs";
  6. String theUser = "dave";
  7. String thePw = "dave";
  8. Connection c = null;
  9. Statement conn;
  10. ResultSet rs = null;
  11. public jdbc() {
  12. try {
  13. Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
  14. c = DriverManager.getConnection(dbUrl, theUser, thePw);
  15. conn = c.createStatement();
  16. } catch (Exception e) {
  17. e.printStackTrace();
  18. }
  19. }
  20. public boolean executeUpdate(String sql) {
  21. try {
  22. conn.executeUpdate(sql);
  23. return true;
  24. } catch (SQLException e) {
  25. e.printStackTrace();
  26. return false;
  27. }
  28. }
  29. public ResultSet executeQuery(String sql) {
  30. rs = null;
  31. try {
  32. rs = conn.executeQuery(sql);
  33. } catch (SQLException e) {
  34. e.printStackTrace();
  35. }
  36. return rs;
  37. }
  38. public void close() {
  39. try {
  40. conn.close();
  41. c.close();
  42. } catch (Exception e) {
  43. e.printStackTrace();
  44. }
  45. }
  46. public static void main(String[] args) {
  47. ResultSet rs;
  48. jdbc conn = new jdbc();
  49. rs = conn.executeQuery("select * from dave where rownum\<5");
  50. try {
  51. while (rs.next()) {
  52. System.out.println(rs.getString("username")+"--"+rs.getString("user_id"));
  53. }
  54. } catch (Exception e) {
  55. e.printStackTrace();
  56. }
  57. }
  58. }
  59. ---输出正常:
  60. MGMT_VIEW--97
  61. ANQING--94
  62. DVD--93
  63. SYSMAN--95

不使用tnsname

jdbc:oracle:thin:@
(description=
(ADDRESS_LIST =
(address=(protocol=tcp)(host=192.168.1.44)(port=1521))
(address=(protocol=tcp)(host=192.168.1.45)(port=1521))
(address=(protocol=tcp)(host=192.168.1.46)(port=1521))
(load_balance=yes)
)
(connect_data =
(service_name=ORACMS)
(failover_mode =
(type=session)
(method=basic)
(retries=5)
(delay=15)
)
)
)

-- 加上注释后的TNS连接串
jdbc:oracle:thin:@
(description=
(ADDRESS_LIST =
(address=(protocol=tcp)(host=192.168.1.44)(port=1521))
(address=(protocol=tcp)(host=192.168.1.45)(port=1521))
(address=(protocol=tcp)(host=192.168.1.46)(port=1521))
(load_balance=yes)//表示是否负载均衡
)
(connect_data =
//(server = dedicated)//该参数表示专用服务器模式
(service_name=ORACMS)//要操作数据库的服务名
(failover_mode =//连接失败后处理的方式
(type=session)//TYPE =SESSION表示当一个连接好的会话的实例发生故障,系统会自动将会话切换到其他可用的实例,前台应用无须再度发起连接,但会话正在执行的SQL 需要重新执行。
(method=basic)//表示初始连接就连接一个接点
(retries=5)//连接失败后重试连接的次数
(delay=15)//连接失败后重试的延迟时间(以秒为单位)
)
)
)

物理dg配置客户端无缝切换 (4)--ora-16652 和 ora-16603错误

实验环境介绍

项目主库dg库
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDoradg11goradgphy
db_nameoradg11goradg11g
主机IP地址:192.168.59.130192.168.59.130
OS版本及kernel版本RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64
OS hostnamerhel6_lhrrhel6_lhr

实验部分

实验目标

可以解决ORA-16603 和 ORA-16652错误。

错误: ORA-16603: Data Guard 中介在配置 ID 中检测到不匹配

DGMGRL> reinstate database oradg11g;

正在恢复数据库 "oradg11g", 请稍候...

错误: ORA-16603: Data Guard 中介在配置 ID 中检测到不匹配

失败。

恢复数据库 "oradg11g" 失败

DGMGRL> remove configuration;

错误: ORA-16603: Data Guard 中介在配置 ID 中检测到不匹配

错误: ORA-16625: 无法访问数据库 "oradgphy"

失败。

DGMGRL> show database verbose oradgphy

数据库 - oradgphy

角色: PRIMARY

预期状态: OFFLINE

实例:

oradgphy

属性:

DGConnectIdentifier = 'tns_oradgphy_dgmgrl'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'oradg11g, oradgphy'

LogFileNameConvert = 'oradg11g, oradgphy'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

SidName = 'oradgphy'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradgphy_DGMGRL.lhr.com)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t%s%r.dbf'

TopWaitEvents = '(monitor)'

数据库状态:

DISABLED

DGMGRL> show database verbose oradg11g

数据库 - oradg11g

角色: PHYSICAL STANDBY

预期状态: OFFLINE

传输滞后: (未知)

应用滞后: (未知)

实时查询: OFF

实例:

oradg11g

属性:

DGConnectIdentifier = 'tns_oradg11g_dgmgrl'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'oradgphy, oradg11g'

LogFileNameConvert = 'oradgphy, oradg11g'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

SidName = 'oradg11g'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t%s%r.dbf'

TopWaitEvents = '(monitor)'

数据库状态:

DISABLED

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradgphy - 主数据库

oradg11g - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

DISABLED

DGMGRL> enable configuration

已启用。

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradgphy - 主数据库

oradg11g - 物理备用数据库 (禁用)

快速启动故障转移: DISABLED

配置状态:

SUCCESS

解决办法:

主备库都执行:

① alter system set DG_BROKER_START=false;

② 删除如下的文件

14:45:50 SQL> show parameter DG_BROKER_CONFIG

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr1oradgphy.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/dr2oradgphy.dat

① alter system set DG_BROKER_START=true;

[oracle@rhel6_lhr admin]$ oerr ora 16603

16603, 00000, "Data Guard broker detected a mismatch in configuration ID"

// *Cause: There was a mismatch in configuration unique ID. This could occur

// if the original configuration was re-created while this database

// was disconnected from the network or the same database was added

// to two different Data Guard broker configurations.

// *Action: Make sure the database belongs to only one broker configuration.

// Shut down the broker by setting the DG_BROKER_START initialization

// parameter to false. Then remove the Data Guard broker configuration

// files. Finally restart the broker by setting the DG_BROKER_START

// initialization parameter to true.

[oracle@rhel6_lhr admin]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr*

-rw-r----- 1 oracle asmadmin 16384 Sep 28 12:11 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1oradg11g.dat

-rw-r----- 1 oracle asmadmin 16384 Sep 28 14:47 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1oradgphy.dat

-rw-r----- 1 oracle asmadmin 16384 Sep 28 12:10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2oradg11g.dat

-rw-r----- 1 oracle asmadmin 16384 Sep 28 14:47 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2oradgphy.dat

[oracle@rhel6_lhr admin]$ rm -rf /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr*

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration;

ORA-16532: Data Guard 中介配置不存在

配置详细资料不能由 DGMGRL 确定

DGMGRL>

DGMGRL>

DGMGRL> create configuration 'fsf_oradg11g_lhr' as

primary database is 'oradgphy'

connect identifier is tns_oradgphy_dgmgrl;

add database 'oradg11g' as

connect identifier is tns_oradg11g_dgmgrl

maintained as physical;

已创建配置 "fsf_oradg11g_lhr", 其中主数据库为 "oradgphy"

DGMGRL> DGMGRL> > > 已添加数据库 "oradg11g"

DGMGRL> DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradgphy - 主数据库

oradg11g - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

DISABLED

DGMGRL> enable configuration

已启用。

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradgphy - 主数据库

oradg11g - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL> switchover to oradg11g

立即执行切换, 请稍候...

新的主数据库 "oradg11g" 正在打开...

操作要求关闭实例 "oradgphy" (在数据库 "oradgphy" 上)

正在关闭实例 "oradgphy"...

ORACLE 例程已经关闭。

操作要求启动实例 "oradgphy" (在数据库 "oradgphy" 上)

正在启动实例 "oradgphy"...

ORACLE 例程已经启动。

数据库装载完毕。

数据库已经打开。

切换成功, 新的主数据库为 "oradg11g"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxPerformance

数据库:

oradg11g - 主数据库

oradgphy - 物理备用数据库

快速启动故障转移: DISABLED

配置状态:

SUCCESS

DGMGRL>

ORA-16652: 已禁用快速启动故障转移目标备用数据库

[oracle@rhel6_lhr lhr]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> disable fast_start failover

错误: ORA-16652: 已禁用快速启动故障转移目标备用数据库

失败。

DGMGRL> reinstate database oradgphy;

正在恢复数据库 "oradgphy", 请稍候...

已成功恢复数据库 "oradgphy"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradg11g - 主数据库

oradgphy - (*) 物理备用数据库

快速启动故障转移: ENABLED

配置状态:

SUCCESS

DGMGRL>

如果报错,那么就重新启动需要恢复的数据库,然后重新执行reinstate database oradgphy;

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradgphy - 主数据库

警告: ORA-16817: 快速启动故障转移配置不同步

oradg11g - (*) 物理备用数据库 (禁用)

ORA-16661: 需要恢复备用数据库

快速启动故障转移: ENABLED

配置状态:

WARNING

DGMGRL> reinstate database oradg11g;

正在恢复数据库 "oradg11g", 请稍候...

已成功恢复数据库 "oradg11g"

DGMGRL> show configuration

配置 - fsf_oradg11g_lhr

保护模式: MaxAvailability

数据库:

oradgphy - 主数据库

oradg11g - (*) 物理备用数据库

快速启动故障转移: ENABLED

配置状态:

SUCCESS

DGMGRL>

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 2 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部