[转帖]DATAGUARD BROKER配置步骤_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2295 | 回复: 1   主题: [转帖]DATAGUARD BROKER配置步骤        上一篇   下一篇 
吐泡沫的桐希
注册用户
等级:新兵
经验:66
发帖:3
精华:0
注册:2016-9-29
状态:离线
发送短消息息给吐泡沫的桐希 加好友    发送短消息息给吐泡沫的桐希 发消息
发表于: IP:您无权察看 2019-9-19 10:23:16 | [全部帖] [楼主帖] 楼主

配置broker的前提是你的有个运行正常的dataguard,我只写了在dataguard基础上配置的broker,dataguard的步骤省略了。

1.主备库查看dg_broker_start的状态

 SQL> show parameter dg_broker_start;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start
boolean
FALSE


该参数默认会被设为FALSE,设为TRUE会随数据库实例而自动启动

SQL>alter system set dg_broker_start = true scope=both;
Systemaltered.
SQL>SHOW PARAMETER DG_BROKER_START
NAME                              TYPE    VALUE
----------------------------------------------- ------------------------------
dg_broker_start                boolean TRUE


2.启用 Flashback

可以通过如下SQL 查看是否启用了Flashback:

SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
NO


在主备库查看:

SQL>show parameter db_recovery_file_dest
NAME                              TYPE    VALUE
----------------------------------------------- ------------------------------
db_recovery_file_dest               string    /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size        big integer 1G


在启用Flashback database 之前,需要先设置db_recovery_file_dest_size参数,而后才可以设置db_recovery_file_dest

如果在备库开启闪回需要先取消recover 进程,不然会报错。

主库:

SQL>alter database flashback on;
Database altered.
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES


备库:

SQL>alter database recover managed standby database cancel;
Databasealtered.
SQL>alter database flashback on;
Database altered.
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>alter database recover managed standby database disconnect from session using current logfile;
Database altered.


3.配置主库、备库的db_domain

主库:

SQL> shutdown immediate
vi /u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora
*.db_domain='cuiyan1.com'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora';
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
SQL> show parameter db_domain;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
db_domain
string
cuiyan1.com


备库;

SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
vi /u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora
*.db_domain='cuiyan2.com'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdsdfdb.ora';
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> show parameter db_domain;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
db_domain
string
cuiyan2.com


4.配置监听

设置listener 

在Data Guard Broker环境中,需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重启数据库的时候用到。通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL用此service_name通过listener连接到数据库,发出启动命令。这个环节很容易出错,下面的配置方法是经过实验成功的。

主库:

vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dsdfdbm_DGMGRL.cuiyan1.com)----------------<db_unique_name>_DGMGRL.<db_domain>
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = dsdfdb)
)
(SID_DESC =
(GLOBAL_DBNAME = dsdfdbm)------------db_unique_name
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = dsdfdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.51)(PORT = 1521))
)
ADR_BASE_LISTENER =/u01/app/oracle
$ lsnrctl reload


备库:

vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dsdfdbs_DGMGRL.cuiyan2.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = dsdfdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.52)(PORT = 1521))
)
ADR_BASE_LISTENER =/u01/app/oracle
$ lsnrctl reload


主备库的tnsnames.ora

dsdfdbs_DGMGRL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.255.0.52)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME =dsdfdbm_DGMGRL.cuiyan2.com)
)
)
dsdfdbm_DGMGRL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.255.0.51)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dsdfdbs_DGMGRL.cuiyan1.com)
)
)
dsdfdbm =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.51)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dsdfdbm)
)
)
dsdfdbs =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.255.0.52)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dsdfdbs)
)
)


5.修改主备库保护模式

一般创建完备库,默认的保护模式是最大性能,主备库都改

SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE
PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


切换到最高可用模式

SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE
PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


6.创建配置及添加备库

[oracle@ibpsdba ~]$ dgmgrl
DGMGRL> connect sys/123456@dsdfdbm
Connected.


语法:

CREATE CONFIGURATION <configurationname> AS
PRIMARY DATABASE IS <database name> --主库的DB_UNIQUE_NAME
CONNECT IDENTIFIER IS <connect identifier>; --主库的TNS_NAME
ADD DATABASE <database name> --备库的DB_UNIQUE_NAME
[AS CONNECT IDENTIFIER IS <connect identifier>] --备库的TNS_NAME
[MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> create configuration 'Broker' as primary database is 'dsdfdbm' connect identifier is dsdfdbm_DGMGRL;
Configuration "Broker" created with primary database "dsdfdbm"
DGMGRL> add database 'dsdfdbs' as connect identifier is dsdfdbs_DGMGRL maintained as physical;
Database "dsdfdbs" added
DGMGRL> show configuration;
Configuration - Broker
Protection Mode: MaxAvailability
Databases:
dsdfdbm - Primary database
dsdfdbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> show configuration;
Configuration - Broker
Protection Mode: MaxAvailability
Databases:
dsdfdbm - Primary database
dsdfdbs - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL> show configuration;
Configuration - Broker
Protection Mode: MaxAvailability
Databases:
dsdfdbm - Primary database
Warning: ORA-16819: fast-start failover observer not started
dsdfdbs - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
DGMGRL> START OBSERVER FILE='/u01/app/oracle/product/11.2.0/db_1/dbs/dr1dsdfdbm.dat';
Observer started


注意:

*启动之后,该前台进程不会退出,会一直挂在这。 直到从其他窗口关闭。

*默认情况下,observer会创建一个二进制的文件 fsfo.dat来保存主库和备库的连接信息。 这个文件会在调用dgmgrl命令的当前窗口下生成。

DGMGRL> show configuration;
Configuration - Broker
Protection Mode: MaxAvailability
Databases:
dsdfdbm - Primary database
dsdfdbs - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS


测试故障转移

主库:

 SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size    2232640 bytes
Variable Size  654315200 bytes
Database Buffers 2533359616 bytes
Redo Buffers   16928768 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened


备库:

 SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 31970
Session ID: 1 Serial number: 165
SQL> select * from t1;
ERROR:
ORA-03114: not connected to ORACLE


退出重新登录

 SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> insert into t1 values(15,'9999');
1 row created.
DGMGRL> connect sys/123456@dsdfdbm
Connected.
DGMGRL> show configuration;
ORA-16795: the standby database needs to be re-created
Configuration details cannot be determined by DGMGRL
DGMGRL> connect sys/123456@dsdfdbs
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.


主库退出重新登录

 SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED




赞(0)    操作        顶端 
联动大白
注册用户
等级:列兵
经验:91
发帖:0
精华:0
注册:2015-5-27
状态:离线
发送短消息息给联动大白 加好友    发送短消息息给联动大白 发消息
发表于: IP:您无权察看 2019-11-19 0:32:34 | [全部帖] [楼主帖] 2  楼

为了方便大家阅读,我对文章中错误号来解释一下吧!

Error Id: ORA-03135

Title: connection lost contact

Description:

connection lost contact

Action:

1) Check if the server session was terminated. 2) Check if the timeout parameters are set properly in sqlnet.ora.

Cause:

1) Server unexpectedly terminated or was forced to terminate. 2) Server timed out the connection.


Error Id: ORA-16649

Title: database will open after Data Guard broker has evaluated Fast-Start Failover status

Description:

database will open after Data Guard broker has evaluated Fast-Start Failover status

Action:

No action is normally required. The Data Guard broker will continue opening the database after determining a Fast-Start failover did not occur. If there is a chance that a Fast-Start Failover did occur, the database will remain in the mounted state and will not open. In this case, check the target standby to see if a role transition took place.

Cause:

The database is being opened while Fast-Start failover is enabled. The message indicates that the Data Guard broker will first determine if conditions are suitable for opening; that is, a Fast-Start failover did not occur while the database was unavailable.


Error Id: ORA-16819

Title: Fast-Start Failover observer not started

Description:

Fast-Start Failover observer not started

Action:

Start the Fast-Start Failover observer by using, for example, the DGMGRL START OBSERVER command.

Cause:

The observer for Fast-Start Failover was not started. As a result, Fast-Start Failover could not happen in the case of a primary database failure.


Error Id: ORA-16795

Title: database resource guard detects that database re-creation is required

Description:

database resource guard detects that database re-creation is required

Action:

Re-create (or flash back) the standby database. Connect to the primary database in the broker configuration and reenable broker management of that database. At this point you may connect to that standby database and resume issuing client commands. Alternatively, many client commands that cannot be completed at the standby database when in this error state can be completed successfully when issued to the primary database. In this case, simply reconnect to the primary database and retry the command.

Cause:

In the act of failover or switchover, the database resource guard may have detected that re-creation of the database is necessary. This occurs when the database resource guard recognizes a situation in which the database in question cannot be a viable standby database for the new primary database. Until this error status is resolved for this database, information about this database and the broker configuration to which it belongs is unavailable to a broker client that is connected to this database. Therefore, all commands directed by that client to this database cannot be completed.


Error Id: ORA-12514

Title: TNS:listener does not currently know of service requested in connect descriptor

Description:

TNS:listener does not currently know of service requested in connect descriptor

Action:

- Wait a moment and try to connect a second time. - Check which services are currently known by the listener by executing: lsnrctl services <listener name> - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener. - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener. - Check for an event in the listener.log file.

Cause:

The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


Error Id: TNS-12514

Title: TNS:listener does not currently know of service requested in connect descriptor

Description:

TNS:listener does not currently know of service requested in connect descriptor

Action:

- Wait a moment and try to connect a second time. - Check which services are currently known by the listener by executing: lsnrctl services <listener name> - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener. - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener. - Check for an event in the listener.log file.

Cause:

The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


也许你已明白,但对一个人有用也是我存在的理由!^_^ By:持之以恒的大白

-- 来自: 北京联动北方科技有限公司



赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论