1. 首页
  2. IT资讯

配置Broker管理DataGuard

【前言】管理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)中配置附加的所有数据库,所有的备库通过主库发出的命令进行变更;

配置Broker管理DataGuard

二、创建和启用Broker

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

主库备库
ServiceBEIJINGTIANJIN

【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 dmonoracle 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 dmonoracle 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 databaseORA-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 2015ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWNALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 35617] (joe)Tue Dec 01 06:27:29 2015Thread 1 advanced to log sequence 88 (LGWR switch) Current log# 3 seq# 88 mem# 0: /u01/app/oracle/oradata/joe/redo03.logTue Dec 01 06:27:29 2015Stopping background process CJQ0Stopping background process QMNCAll dispatchers and shared servers shutdownCLOSE: 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 identifiedSwitchover End-Of-Redo Log thread 1 sequence 88 has been fixedSwitchover: Primary highest seen SCN set to 0x0.0x18229eARCH: Noswitch archival of thread 1, sequence 88ARCH: End-Of-Redo Branch archival of thread 1 sequence 88ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_1ARCH: Standby redo logfile selected for thread 1 sequence 88 for destination LOG_ARCHIVE_DEST_2Archived Log entry 149 added for thread 1 sequence 88 ID 0xab7867f0 dest 1:ARCH: Archiving is disabled due to current logfile archivalPrimary will check for some target standby to have received alls redoFinal check for a synchronized target standby. Check will be made once.LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover targetActive, synchronized target has been identifiedTarget has also received all redoBackup controlfile written to trace file /u01/app/oracle/diag/rdbms/beijing/joe/trace/joe_rsm0_35617.trcClearing 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 logfilesUse the following SQL commands on the standby database to createstandby 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 recoverySwitchover: Primary controlfile converted to standby controlfile succesfully.Switchover: Complete – Database shutdown requiredCompleted: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWNTue Dec 01 06:27:41 2015Performing implicit shutdown abort due to switchover to physical standbyShutting down instance (abort)License high water mark = 8USER (ospid: 38026): terminating the instanceInstance terminated by USER, pid = 38026Tue Dec 01 06:27:42 2015Instance shutdown completeORA-1092 : opitsk aborting process

TIANJIN数据库的日志

Error 12537 received logging on to the standbyPING[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 continuingORACLE 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 beginningDictionary check completeVerifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryDatabase Characterset is WE8MSWIN1252Starting background process SMCOSun Jan 10 16:26:34 2016SMCO 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.logSun Jan 10 16:26:37 2016Starting background process QMNCSun Jan 10 16:26:38 2016QMNC started with pid=27, OS id=45553 LOGSTDBY: Validating controlfile with logical metadataLOGSTDBY: Validation completeCompleted: ALTER DATABASE OPENALTER 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 PROCESSESALTER SYSTEM SET log_archive_dest_state_2=’ENABLE’ SCOPE=BOTH;ALTER SYSTEM ARCHIVE LOGSun Jan 10 16:26:39 2016ARC4 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.logARCH: 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 standbyARCH: Error 1034 Creating archive log file to ‘beijing’Error 1034 received logging on to the standbyError 1034 for archive log file 1 to ‘beijing’ARC4: Archival startedARC2: STARTING ARCH PROCESSES COMPLETEErrors in file /u01/app/oracle/diag/rdbms/tianjin/joe/trace/joe_nsa2_45547.trc:ORA-01034: ORACLE not availableError 1034 received logging on to the standbyPING[ARC2]: Heartbeat failed to connect to standby ‘beijing’. Error is 1034.Shutting down archive processesARCH shutting downARC4: Archival stoppedSun Jan 10 16:26:45 2016Starting background process CJQ0Sun Jan 10 16:26:45 2016CJQ0 started with pid=35, OS id=45571 Setting Resource Manager plan SCHEDULER[0x318F]:DEFAULT_MAINTENANCE_PLAN via scheduler windowSetting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameterSun Jan 10 16:26:48 2016Starting background process VKRMSun Jan 10 16:26:48 2016VKRM 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 bytesFixed Size 2230952 bytesVariable Size 243271000 bytesDatabase Buffers 373293056 bytesRedo Buffers 7532544 bytesDatabase 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

说明已经成功

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29578568/viewspace-2136904/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195099.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code