How To Configure Step By Step Oracle DGMGRL Utility | Karan Rajpoot

In my previous blog, We saw the configuration steps of the data guard setup. In this blog, we will see the step by step Oracle DGMGRL Utility Configuration.

let’s start the step by step Oracle DGMGRL Utility configuration.

Step 1) First we should check the DB name, DB unique name, open_mode, database_role of both the servers.

Primary Server: –

SQL>  select name, db_unique_name,open_mode,database_role, flashback_on from v$database;

NAME   DB_UNIQUE_NAME OPEN_MODE       DATABASE_ROLE    FLASHBACK_ON
——— —————————— ——————– —————- ——————
PROD   prod READ WRITE       PRIMARY        YES

Standby Server: –

SQL> select name, db_unique_name,open_mode,database_role, flashback_on from v$database;

NAME   DB_UNIQUE_NAME OPEN_MODE       DATABASE_ROLE    FLASHBACK_ON
——— —————————— ——————– —————- ——————
PROD   dr MOUNTED       PHYSICAL STANDBY YES

Step 2) Now test the connectivity of both the servers. Both should be ping to each other.

Primary to Standby: –

[oracle@prod admin]$ ping 192.168.2.21
PING 192.168.2.21 (192.168.2.21) 56(84) bytes of data.
64 bytes from 192.168.2.21: icmp_seq=1 ttl=64 time=1.26 ms
^C
— 192.168.2.21 ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 2734ms

rtt min/avg/max/mdev = 0.874/1.588/2.626/0.752 ms

Standby to Primary: –

[oracle@dr admin]$ ping 192.168.2.20
PING 192.168.2.20 (192.168.2.20) 56(84) bytes of data.
64 bytes from 192.168.2.20: icmp_seq=1 ttl=64 time=0.953 ms

— 192.168.2.20 ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 2616ms

rtt min/avg/max/mdev = 0.953/1.039/1.191/0.110 ms

Step 3) Now set the dg_broker_start parameter as enable on both the servers.

Primary Server: –

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

System altered.

Standby Server: –

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

System altered.

Step 4) Now configure the listener and tnsnames services for both the servers.

Note: – You can use netca or netmgr GUI tools to create the services.

Primary Side: –

Listener File Output: –

[oracle@prod admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod.radical.com)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod)
          (SID_NAME=prod)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=dr)
          (SID_NAME=dr)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )
Tnsnames File Output: –
[oracle@prod admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )
DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dr)
    )
  )
Standby Side: –
 
Listener File Output: –
[oracle@dr admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dr.radical.com)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod)
          (SID_NAME=prod)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=dr)
          (SID_NAME=dr)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )
Tnsnames File Output: –
 
[oracle@dr admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dr)
    )
  )
PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )
Step 5) Use the dgmgrl utility to connect to the primary server.
[oracle@prod admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/sys@prod
Connected.
Step 6) Now configure the broker service on the primary server.
 
DGMGRL> create configuration ‘broker’ as primary database is prod connect identifier is prod;
Configuration “broker” created with primary database “prod”
Step 7) Now add the standby database in the above configuration.
 
DGMGRL> add database ‘dr’ as connect identifier is dr maintained as physical;
Database “dr” added
Note: – help add command will show the syntax of how to add the standby database in the above configuration.
DGMGRL> help add
Adds a standby database to the broker configuration
Syntax:
  ADD DATABASE <database name>
    [AS CONNECT IDENTIFIER IS <connect identifier>]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];
Step 8) Now you need to enable the configuration.
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration – broker
  Protection Mode: MaxPerformance
  Databases:
    prod – Primary database
    dr   – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
We can see from the above output our broker configuration has been enabled.
Step 9) Now we need to set the below property 
 
These below two properties are used to sync the log file between primary and standby.
 
DGMGRL> edit database prod set property logxptmode= ‘sync’;
Property “logxptmode” updated
DGMGRL> edit database dr set property logxptmode= ‘sync’;
Property “logxptmode” updated
These below properties are used when there will be failover activity will perform between primary and standby server.
DGMGRL> edit database prod set property faststartfailovertarget= ‘dr’;
Property “faststartfailovertarget” updated
DGMGRL> edit database dr set property faststartfailovertarget= ‘prod’;
Property “faststartfailovertarget” updated
DGMGRL> edit configuration set property faststartfailoverthreshold= ’10’;
Property “faststartfailoverthreshold” updated
Step 10) If you want to check the detailed information then you can use below commands.
1) show instance prod: – It will give the status of the primary Instance.
DGMGRL> show instance prod;
Instance ‘prod’ of database ‘prod’
Instance Status:
SUCCESS
2) show instance dr: – It will give the status of the standby Instance.
DGMGRL> show instance dr;
Instance ‘dr’ of database ‘dr’
Instance Status:
SUCCESS
3) show database verbose prod: – It will give the detailed logs of the primary server.
DGMGRL> show database verbose prod;
Database – prod
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    prod
  Properties:
    DGConnectIdentifier             = ‘prod’
    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          = ‘4’
    LogArchiveMinSucceedDest        = ‘1’
    DbFileNameConvert               = ‘ ‘
    LogFileNameConvert              = ‘ ‘
    FastStartFailoverTarget         = ‘dr’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    ApplyLagThreshold               = ‘0’
    TransportLagThreshold           = ‘0’
    TransportDisconnectedThreshold  = ’30’
    SidName                         = ‘prod’
    StaticConnectIdentifier         = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))’
    StandbyArchiveLocation          = ‘USE_DB_RECOVERY_FILE_DEST’
    AlternateLocation               = ”
    LogArchiveTrace                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.dbf’
    TopWaitEvents                   = ‘(monitor)’
Database Status:
SUCCESS
4) show database verbose dr: –It will give the detailed logs of the standby server.
DGMGRL> show database verbose dr;
Database – dr
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    dr
  Properties:
    DGConnectIdentifier             = ‘dr’
    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          = ‘4’
    LogArchiveMinSucceedDest        = ‘1’
    DbFileNameConvert               = ‘prod, dr’
    LogFileNameConvert              = ‘prod, dr’
    FastStartFailoverTarget         = ‘prod’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    ApplyLagThreshold               = ‘0’
    TransportLagThreshold           = ‘0’
    TransportDisconnectedThreshold  = ’30’
    SidName                         = ‘dr’
    StaticConnectIdentifier         = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dr.radical.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dr_DGMGRL)(INSTANCE_NAME=dr)(SERVER=DEDICATED)))’
    StandbyArchiveLocation          = ‘USE_DB_RECOVERY_FILE_DEST’
    AlternateLocation               = ”
    LogArchiveTrace                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.dbf’
    TopWaitEvents                   = ‘(monitor)’
Database Status:
SUCCESS
We have successfully completed the step by step Oracle DGMGRL Utility.
Hope this blog will help you!!

If this article is useful for you please comment, share with your friends.

In case of any issues please send your query to below comment section.

For more articles please do visit below links: –

This Post Has One Comment

Leave a Reply

Close Menu