- Introduction:
Command-Line (DGMGRL) & GUI (Enterprise Manager) – Can be managed using DGMGRL (Data Guard Broker command-line) or Oracle Enterprise Manager Cloud Control. This guide will walk you through the Data Guard Broker step-by-step.
1. Oracle Database Enterprise Edition (Standard Edition does not support Data Guard).
2. Same Database Version & Patch Level on both primary and standby.
3. Oracle Data Guard Feature Installed on both systems.
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
Primary
|
SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="orcldr", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldr" net_timeout=30, valid_for=(online_logfile,all_roles) SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both; System altered. |
|
SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="orcldc", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldc" net_timeout=30, valid_for=(online_logfile,all_roles) SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both; System altered. |
2. Set dg_broker_start parameter true in both Primary & Standby.
Primary
|
[oracle@source ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 11:32:27 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 SQL> SQL> def DEFINE _DATE = "23-JAN-25" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0" (CHAR) DEFINE _O_RELEASE = "1924000000" (CHAR) SQL> SQL> show parameter dg_broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr1ORCLDC.dat dg_broker_config_file2 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr2ORCLDC.dat dg_broker_start boolean FALSE SQL> SQL> alter system set dg_broker_start=true scope=both sid='*'; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 [oracle@source ~]$ |
|
[oracle@target~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 11:32:10 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 SQL> def DEFINE _DATE = "23-JAN-25" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcldr" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0" (CHAR) DEFINE _O_RELEASE = "1924000000" (CHAR) SQL> SQL> show parameter dg_broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr1ORCLDR.dat dg_broker_config_file2 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr2ORCLDR.dat dg_broker_start boolean FALSE SQL> SQL> alter database recover managed standby database cancel; Database altered. SQL> alter system set dg_broker_start=true scope=both sid='*'; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE |
3. Now add configuration for DG Broker. We will do the same on Primary database.
|
[oracle@source ~]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 11:55:25 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "ORCLDC" Connected as SYSDG. DGMGRL> show configuration ORA-16596: member not part of the Oracle Data Guard broker configuration Configuration details cannot be determined by DGMGRL DGMGRL> DGMGRL> create configuration ORCLDG as primary database is ORCLDC connect identifier is ORCLDC; Configuration "orcldg" created with primary database "orcldc" DGMGRL> DGMGRL> show configuration Configuration - orcldg Protection Mode: MaxPerformance Members: orcldc - Primary database Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL> add database ORCLDR as connect identifier is ORCLDR; Database "orcldr" added DGMGRL> DGMGRL> show configuration Configuration - orcldg Protection Mode: MaxPerformance Members: orcldc - Primary database orcldr - Physical standby database Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL> show configuration Configuration - orcldg Protection Mode: MaxPerformance Members: orcldc - Primary database orcldr - Physical standby database Warning: ORA-16853: apply lag has exceeded specified threshold Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 3 seconds ago) DGMGRL> exit |
4. Now set Archive dest 2 on both Primary & Standby and check sync.
Primary
|
SQL> alter system set log_archive_dest_2='SERVICE=ORCLDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR'; System altered. SQL> alter system switch logfile; System altered. SQL> / System altered. |
|
SQL> alter system set log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'; System altered. SQL> alter database recover managed standby database disconnect from session; alter database recover managed standby database disconnect from session * ERROR at line 1: ORA-01153: an incompatible media recovery is active SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%'; PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- ------------ ---------- ---------- ---------- MRP0 APPLYING_LOG 1 52 514 SQL> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp, round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a, (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b, (select db_unique_name,database_role from v$database) d where a.thread#=b.thread#; DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS ------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- -------------- ORCLDR PHYSICAL STANDBY 1 57 55 2 23-JAN-2025 12:04:11 11.37 682 SQL>/ DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS ------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- -------------- ORCLDR PHYSICAL STANDBY 1 58 58 0 23-JAN-2025 12:30:15 13.72 823 |
5. Now check DG broker status on both Primary & Standby.
Primary
|
[oracle@source ~]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 12:00:27 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "ORCLDC" Connected as SYSDG. DGMGRL> show configuration Configuration - orcldg Protection Mode: MaxPerformance Members: orcldc - Primary database orcldr - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 43 seconds ago) DGMGRL> DGMGRL> DGMGRL> show database orcldc Database - orcldc Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): orcldc Database Status: SUCCESS DGMGRL> show database orcldr Database - orcldr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 7.00 KByte/s Real Time Query: OFF Instance(s): orcldr Database Status: SUCCESS DGMGRL> exit |
|
[oracle@target ~]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 12:00:09 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "ORCLDR" Connected as SYSDG. DGMGRL> show configuration Configuration - orcldg Protection Mode: MaxPerformance Members: orcldc - Primary database orcldr - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 30 seconds ago) DGMGRL> show database orcldc Database - orcldc Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): orcldc Database Status: SUCCESS DGMGRL> show database orcldr Database - orcldr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 5.00 KByte/s Real Time Query: OFF Instance(s): orcldr Database Status: SUCCESS DGMGRL> exit |
Thanks for visiting!!
0 comments:
Post a Comment