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

0    168    1

Tags:

👉 本文共约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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
单实例物理DG配置客户端无缝切换的详细过程后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部