Sunday, March 3, 2019

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 configuration of DGMGRL Oracle utility.


let's start the 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
64 bytes from 192.168.2.21: icmp_seq=2 ttl=64 time=2.62 ms
64 bytes from 192.168.2.21: icmp_seq=3 ttl=64 time=0.874 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
64 bytes from 192.168.2.20: icmp_seq=2 ttl=64 time=0.974 ms
64 bytes from 192.168.2.20: icmp_seq=3 ttl=64 time=1.19 ms
^C
--- 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



Thank You!!























No comments:

Post a Comment