dg重做 oracle_怎么才能够修改配置oracle dg broker

展开全部

32313133353236313431303231363533e59b9ee7ad9431333361326336　系统环境：

操作系统： RedHat EL55_64

Oracle: Oracle 11.2.0.3.0

wKioL1Ngr6CzJRlnAAHIh9Qh6K0020.jpg

Data Guard 配置：

wKioL1NguMjBENx9AAMDBx4eC-w905.jpg

主库bjdb：

02:21:10 SYS@ TestDB12>select name,dbid,database_role,protection_mode from v$database;

NAME DBID DATABASE_ROLE PROTECTION_MODE

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

TESTDB12 2811829300 PRIMARY MAXIMUM AVAILABILITY

Elapsed: 00:00:00.00

02:21:42 SYS@ TestDB12>

备库shdb：

02:21:18 SYS@ shdb>select name,dbid,database_role,protection_mode from v$database;

NAME DBID DATABASE_ROLE PROTECTION_MODE

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

TESTDB12 2811829300 PHYSICAL STANDBY MAXIMUM AVAILABILITY

Elapsed: 00:00:00.01

开启flashback database：

02:22:53 SYS@ TestDB12>select name,flashback_on from v$database;

NAME FLASHBACK_ON

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

TESTDB12 NO

02:23:12 SYS@ TestDB12>show parameter recovery

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area

db_recovery_file_dest_size big integer 4122M

recovery_parallelism integer 0

02:23:44 SYS@ TestDB12>alter database flashback on;

Database altered.

Elapsed: 00:00:01.60

02:24:03 SYS@ TestDB12>select name,flashback_on from v$database;

NAME FLASHBACK_ON

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

TESTDB12 YES

Elapsed: 00:00:00.00

DG Broker 配置：

1.主库设置

2.备库设置

3.创建DataGuard Broker配置

4.添加standby database到配置

5.开启配置

6.验证配置和switch over

DG配置环境：

Database NameTestDB12TestDB12

Database Unqie Namebjdbshdb

Net Service Namebjdbshdb

Version11.2.0.3 for x86_6411.2.0.3 for x86_64

1.主库设置

DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径,DG_BROKER_START参数设置实例启动的时候是否自动启动Broken.

SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1bjdb.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2bjdb.dat' scope=both sid='*';

System altered.

SQL> alter system set DG_BROKER_START=TRUE scope=both sid='*';

System altered.

设置完上面的参数后,我们还需要修改监听listener.ora文件.我们必须添加一个静态注册的service_name为db_unique_name_DGMGRL.db_domain,这个service_name会在DGMGRL重启数据库的时候用到.通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL在通过静态监听中的service_name连接到数据库,发送启动的命令.如果不这么做的话,在做switch over的时候我们容易遇到TNS-12514错误

listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = bjdb_DGMGRL)

(SERVICE_NAME = bjdb)

(SID_NAME = TestDB12)

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

这里需要说明的是GLOBAL_DBNAME=_DGMGRL,.

SERVICE_NAME=,.

SID_NAME=echo $ORACLE_SID.

ORACLE_HOME=echo $ORACLE_HOME

主备库tnsnames 配置：

BJDB =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bjdb)

)

SHDB =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME = shdb)

)

)

2.备库设置

和主库设置一样,同样需要设置DB_BROKER_CONFIG_FILEn参数和DG_BROKER_START参数.还有静态监听.

SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1shdb.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2shdb.dat' scope=both sid='*';

System altered.

SQL> alter system set DG_BROKER_START=TRUE scope=both sid='*';

System altered.

listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = shdb_DGMGRL)

(SERVICE_NAME = shdb)

(SID_NAME = shdb)

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

3.创建DataGuard Broker配置

在主库上使用dgmgrl连接到数据库.创建配置.

[oracle@dg1 admin]$ dgmgrlDGMGRL for Linux: Version 11.2.0.1.0 - 64bit ProductionCopyright (c) 2000, 2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.DGMGRL> connect sys/oracleConnected.

DGMGRL> create configuration 'bjdbcfg' as primary database is 'bjdb' connect identifier is 'bjdb';

Configuration "bjdbcfg" created with primary database "bjdb"

DGMGRL>

这里的参数要说明一下.bjdbcfg是配置的名称,这里可以随便填.PRIMARY DATABASE IS ‘bjdb′ ,这儿的bjdb是指database的db_unique_name,而connect identifier is ‘bjdb′这里的bjdb是指tnsname.ora连接到主库的net service name.

我们可以使用show confiruration查看配置信息.

DGMGRL> show configuration

Configuration - bjdbcfg

Protection Mode: MaxAvailability

Databases:

bjdb - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

4.添加standby database到配置

DGMGRL> add database 'shdb' as connect identifier is shdb maintained as physical;

Database "shdb" added

这里的参数要说明一下.add database ‘shdb′ ,这儿的shdb是指database的db_unique_name,而AS CONNECT IDENTIFIER IS shdb 这里的shdb是指tnsname.ora连接到standby database的net service name.

DGMGRL> show configuration

Configuration - bjdbcfg

Protection Mode: MaxAvailability

Databases:

bjdb - Primary database

shdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

5.开启配置

DGMGRL> enable Configuration;Enabled.DGMGRL> DGMGRL>DGMGRL>

DGMGRL> show configuration;

Configuration - bjdbcfg

Protection Mode: MaxAvailability

Databases:

bjdb - Primary database

shdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

修改DG broker 参数：

编辑数据库属性

LogXptMode

默认情况下，Broker 将主数据库设置为使用异步日志传输。针对最高可用性环境时，需要将此设置更改为同步。

NetTimeout

NetTimeout 属性指定在考虑连接丢失前 LGWR 将阻塞对同步模式中来自备用数据库的确认的等待秒数(对应于log_archive_dest_n 的 NET_TIMEOUT 选项)。默认值为 30 秒。使用最高可用性模式时，考虑降低该值以减少备用数据库不可用时的提交阻塞时间。选择一个足够高的值，避免由间歇性网络问题引起的假性断开。本示例使用 10 秒钟。

ObserverConnectIdentifier(11g 及更高版本)

Oracle 数据库 11g 将 ObserverConnectIdentifier 数据库属性添加到 Broker 配置，使您可以为观察器指定一个连接标识符，用于监视主数据库和故障切换目标。默认情况下，观察器和 Data Guard 使用相同的连接标识符在主数据库和备用数据库间进行重做传输和信息交换(Oracle 数据库 11g 中为DGConnectIdentifier，Oracle 数据库 10g 中为InitialConnectIdentifier)。ObserverConnectIdentifier 使您可以指定观察器使用不同的连接标识符。例如，您可以用此参数使观察器使用与客户端应用程序相同的连接标识符监视数据库。

在本指南中，我们将在保留其他属性的默认值，但您应熟悉所有 Broker 配置和数据库属性。Data Guard Broker 文档(10g 和 11g)第 9 章中包含了每个属性的描述。其中一些属性已经在这两个版本中有所改动。

注：Broker 的许多数据库属性与数据库 spfile 参数相对应。Broker 在角色转换、数据库启动/关闭以及其他事件期间，通过执行相应的 ALTER SYSTEM 命令来维护这些参数。如果这些参数在 Broker 外部进行了修改，将出现警告。要查看特定参数，使用“show database ... StatusReport”命令。

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

DGMGRL> edit database 'bjdb' set property 'logxptmode'='sync';

Property "logxptmode" updated

DGMGRL> edit database 'shdb' set property 'logxptmode'='sync';

Property "logxptmode" updated

DGMGRL>

DGMGRL> enable fast_start failover;

Enabled.

DGMGRL> show configuration;

Configuration - bjdbcfg

Protection Mode: MaxAvailability

Databases:

bjdb - Primary database

Warning: ORA-16819: fast-start failover observer not started

shdb - (*) Physical standby database

Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:

WARNING

DGMGRL>

DGMGRL> start observer;

Observer started

打开新的窗口：

[oracle@shsrv ~]$dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> show configuration;

not logged on

DGMGRL> connect sys/oracle@bjdb

Connected.

DGMGRL> show configuration;

Configuration - bjdbcfg

Protection Mode: MaxAvailability

Databases:

bjdb - Primary database

shdb - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

验证FFS:

主库：

02:58:23 SYS@ TestDB12>col FS_FAILOVER_OBSERVER_HOST for a30

02:58:28 SYS@ TestDB12>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold

02:58:39 2 from v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD

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

YES shsrv 30

Elapsed: 00:00:00.01

02:58:46 SYS@ TestDB12>

备库：

02:59:14 SYS@ shdb>col FS_FAILOVER_OBSERVER_HOST for a30

02:59:16 SYS@ shdb>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD

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

YES shsrv 30

Elapsed: 00:00:00.02

02:59:41 SYS@ shdb>

DGMGRL> show database verbose bjdb;

Database - bjdb

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

TestDB12

Properties:

DGConnectIdentifier = 'bjdb'

ObserverConnectIdentifier = ''

LogXptMode = 'sync'

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 = '3'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/u01/app/oracle/oradata/sh, /u01/app/oracle/oradata/TestDB12'

本回答由网友推荐

已赞过

已踩过<

你对这个回答的评价是？

评论

收起