配置Broker管理DataGuard

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 【前言】管理Oracle DataGuard的方式有三种:SQLPlus、OEM Grid Control、Broker; 先学习用SQLPlus进行切换,先了解其中的原理。

【前言】管理Oracle DataGuard的方式有三种:SQLPlus、OEM Grid Control、Broker;

先学习用SQLPlus进行切换,先了解其中的原理。

掌握了DataGuard的切换原理之后建议用Broker,因为这个工具会为你的Dataguard管理和切换带来很大的便利;

OEM Grid Control一般不建议使用,因为这个本身的配置也是一件非常麻烦的事情;

 

一、了解Data Guard Broker

【1】Data Guard Broker概述: Broker不是单独安装的功能,也不完全独立于Data Guard。它是标准Oracle数据库企业版安装的一部分,也是Data Guard的组成部分。Broker连接配置中的数据库,通过这个连接可以监控配置中数据库的健康状况。

【2】Broker的组成:主数据库上的Data Guard Monitor(DMON)中配置附加的所有数据库,所有的备库通过主库发出的命令进行变更;

image 

 

二、创建和启用Broker

【1】环境说明:单机环境、数据库版本11.2.0.3、操作系统Centos

  主库 备库
Service BEIJING TIANJIN

 

【2】准备工作

  • 主库和备库都使用spfile
  • 所有的数据库必须在mount(物理备库)或者open(主库和逻辑备库)状态
  • 启用DG_BROKER_START

【3】操作步骤

3.1 启用dg_broker_start

SQL> show parameter dg_broker_start;

NAME                                              TYPE                       VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                               boolean                FALSE

 

SQL> !ps -ef|grep dmon
  oracle 13304040 24182868   0 16:46:23  pts/0  0:00 grep dmon



SQL> alter system set dg_broker_start=TRUE scope=both;

System altered.

 

SQL> show parameter dg_broker_start;

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



SQL> !ps -ef|grep dmon
oracle   43523     1  0 09:11 ?        00:00:03 ora_dmon_joe                   启动后dmon进程也起来了
oracle   45399 45367  0 15:58 pts/2    00:00:00 /bin/bash -c ps -ef|grep dmon
oracle   45401 45399  0 15:58 pts/2    00:00:00 grep dmon

 

3.2 添加主库和备库

[oracle@db01 dbs]$ dgmgrl sys/oracle@BEIJING  连接到主库进行操作
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.
Connected.
DGMGRL> show configuration;

Error:
ORA-16525: the Data Guard broker is not yet available   当前尚未有配置的服务器

Configuration details cannot be determined by DGMGRL

 

添加主库

DGMGRL> create configuration JOEDG as primary database is beijing connect identifier is beijing;

添加primary database的写法:

DGMGRL> help create configuration

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION <configuration name> AS
    PRIMARY DATABASE IS <database name>
    CONNECT IDENTIFIER IS <connect identifier>;

configuration name:可以任意取名

database name:db_unique_name

connect identifier:tnsnames文件配置的名称


DGMGRL> show database verbose BEIJING;

Database - beijing

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    joe

  Properties:
    DGConnectIdentifier             = 'beijing'
    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               = '/u01/app/oracle/oradata/joe, /u01/app/oracle/oradata/joe'
    LogFileNameConvert              = '/u01/app/oracle/oradata/joe, /u01/app/oracle/oradata/joe'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'joe'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=BEIJING_DGMGRL)(INSTANCE_NAME=joe)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

 

添加备库

DGMGRL> add database tianjin as connect identifier is tianjin maintained as physical;

DGMGRL> help add database

Adds a standby database to the broker configuration

Syntax:

  ADD DATABASE <database name>
    [AS CONNECT IDENTIFIER IS <connect identifier>]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];

 

DGMGRL> show database verbose TIANJIN;

Database - tianjin

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    joe

  Properties:
    DGConnectIdentifier             = 'tianjin'
    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               = '/u01/app/oracle/oradata/joe, /u01/app/oracle/oradata/joe'
    LogFileNameConvert              = '/u01/app/oracle/oradata/joe, /u01/app/oracle/oradata/joe'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'joe'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TIANJIN_DGMGRL)(INSTANCE_NAME=joe)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

 

DGMGRL> enable configuration;   启用以上配置

DGMGRL>  show configuration;   查看当前配置

Configuration - joedg

  Protection Mode: MaxPerformance
  Databases:
    beijing - Primary database 
    tianjin - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

 

3.3 主从切换测试

[oracle@db01 trace]$  dgmgrl sys/oracle@BEIJING

DGMGRL> switchover to tianjin;            切换的
Performing switchover NOW, please wait...
New primary database "tianjin" is opening...
Operation requires shutdown of instance "joe" on database "beijing"
Shutting down instance "joe"...
ORACLE instance shut down.
Operation requires startup of instance "joe" on database "beijing"
Starting instance "joe"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
    start up instance "joe" of database "beijing"

 

这时候主库已经是TIANJIN了,所以DGMGRL需要连接到TIANJIN

DGMGRL> show configuration;

Configuration - joedg

  Protection Mode: MaxPerformance
  Databases:
    tianjin - Primary database            主库已经成功切换到TIANJIN了
      Error: ORA-16778: redo transport error for one or more databases   从库现在属于关闭的状态,所以这边也看不到;

    beijing - Physical standby database
      Error: ORA-01034: ORACLE not available

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

 

附切换时的主备库日志;

BEIJING的日志

Tue Dec 01 06:27:29 2015
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 35617] (joe)
Tue Dec 01 06:27:29 2015
Thread 1 advanced to log sequence 88 (LGWR switch)
  Current log# 3 seq# 88 mem# 0: /u01/app/oracle/oradata/joe/redo03.log
Tue Dec 01 06:27:29 2015
Stopping background process CJQ0
Stopping background process QMNC
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 35716 user 'oracle' program 'oracle@db01 (TNS V1-V3)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
Active process 37951 user 'grid' program 'oracle@db01'
Active process 38004 user 'oracle' program 'oracle@db01 (W000)'
CLOSE: all sessions shutdown successfully.             关闭数据库
Waiting for all non-current ORLs to be archived...
Waiting for the ORL for thread 1 sequence 87 to be archived...  归档日志
ORL for thread 1 sequence 87 has been 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 88 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x18229e
ARCH: Noswitch archival of thread 1, sequence 88
ARCH: End-Of-Redo Branch archival of thread 1 sequence 88
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_1
ARCH: Standby redo logfile selected for thread 1 sequence 88 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 149 added for thread 1 sequence 88 ID 0xab7867f0 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.
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/beijing/joe/trace/joe_rsm0_35617.trc
Clearing standby activation ID 2876794864 (0xab7867f0)
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 88 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
Tue Dec 01 06:27:41 2015
Performing implicit shutdown abort due to switchover to physical standby
Shutting down instance (abort)
License high water mark = 8
USER (ospid: 38026): terminating the instance
Instance terminated by USER, pid = 38026
Tue Dec 01 06:27:42 2015
Instance shutdown complete
ORA-1092 : opitsk aborting process

 

TIANJIN数据库的日志

Error 12537 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'beijing'. Error is 12537.
FAL[server, ARC3]: Error 12537 creating remote archivelog file 'beijing'
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance joe - Archival Error. Archiver continuing.
[43554] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:419982524 end:419983344 diff:820 (8 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 WE8MSWIN1252
Starting background process SMCO
Sun Jan 10 16:26:34 2016
SMCO started with pid=25, OS id=45549
No Resource Manager plan active
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_1
******************************************************************
Thread 1 advanced to log sequence 91 (LGWR switch)
  Current log# 3 seq# 91 mem# 0: /u01/app/oracle/oradata/joe/redo03.log
Sun Jan 10 16:26:37 2016
Starting background process QMNC
Sun Jan 10 16:26:38 2016
QMNC started with pid=27, OS id=45553
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='joe';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='joe';
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='/u01/app/oracle/oradata/joe','/u01/app/oracle/oradata/joe' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/joe','/u01/app/oracle/oradata/joe' SCOPE=SPFILE;
ARC2: STARTING ARCH PROCESSES
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Sun Jan 10 16:26:39 2016
ARC4 started with pid=30, OS id=45555
Thread 1 advanced to log sequence 92 (LGWR switch)
  Current log# 1 seq# 92 mem# 0: /u01/app/oracle/oradata/joe/redo01.log
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Error 1034 received logging on to the standby
ARCH: Error 1034 Creating archive log file to 'beijing'
Error 1034 received logging on to the standby
Error 1034 for archive log file 1 to 'beijing'
ARC4: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/app/oracle/diag/rdbms/tianjin/joe/trace/joe_nsa2_45547.trc:
ORA-01034: ORACLE not available
Error 1034 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'beijing'. Error is 1034.
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
Sun Jan 10 16:26:45 2016
Starting background process CJQ0
Sun Jan 10 16:26:45 2016
CJQ0 started with pid=35, OS id=45571
Setting Resource Manager plan SCHEDULER[0x318F]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Jan 10 16:26:48 2016
Starting background process VKRM
Sun Jan 10 16:26:48 2016
VKRM started with pid=34, OS id=45577
Sun Jan 10 16:27:32 2016

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

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=beijing_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=db02)(USER=oracle))))

  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: 10-JAN-2016 16:27:32
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

 

3.4 启动BEIJING

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size            2230952 bytes
Variable Size          243271000 bytes
Database Buffers      373293056 bytes
Redo Buffers            7532544 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

SQL>  ALTER DATABASE OPEN;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


DGMGRL>  show configuration;

Configuration - joedg

  Protection Mode: MaxPerformance
  Databases:
    tianjin - Primary database
    beijing - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

说明已经成功

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
存储 Oracle 关系型数据库
Dataguard Broker的配置
修改参数 参数DG_BROKER_START设置为true
|
消息中间件 算法 Kafka
Kafka高可用——replica分配方式
Kafka的Replica 概念 kafka的replica指的是消息的备份,为了保证kafka的高可用(当leader节点挂了之后,kafka依然能提供服务)kafka提供了备份的功能。
1703 0
|
Oracle 关系型数据库 Linux
|
监控 数据库 关系型数据库
|
Oracle 网络协议 关系型数据库
|
SQL Oracle 关系型数据库