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!!










Comments

Post a Comment

Popular posts from this blog

How to do "Multiplexing of Control Files" through spfile ( server parameter file) in Oracle 11g

Image
Step 1)  To perform "Multiplexing of Control Files" through spfile ( server parameter file)" first we need to create spfile from pfile. Example to create spfile from pfile: -                                       Step 2) Copy the control files in physical location from 'cp' command. Note: - At present, I have 2 control files,  I will add one more Control files in the same location. Example: -  Now you can see from above output control04.ctl file has been physically added successfully under /u01/app/oracle/oradata/prod/control04.ctl location. Step 3)  Now you need to set the same through alter command. a)  first shut down the database                                          b)  Startup (Start the database through spfile)                                                                         Step 4) Now through SQL prompt alter the changes. Example: - Step 5) Now shutdown and start the database again and check
Read more

How to Add, Resize, Drop and Rename of a datafile in Tablespace

Image
                                       How to Add, Resize, Drop and Rename a datafile in Tablespace In order to create a data file first, need to create a tablespace, To know how to create a tablespace  Click here To demonstrate, I will use USERS tablespace to Add,  Resize, Drop and Rename a data file. First, we can check how many data files are present under USERS tablespace through below command. Command: - select name from v$datafile; We can see from above output only a single data file is present under USERS tablespace. Addition of Datafile: - Command to add a datafile: - alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf' size 50m; The new data file has been added to my USERS tablespace. Resize of a Datafile: - Command to resize the datafile: - alter database datafile '/u01/app/oracle/oradata/prod/users02.dbf' resize 20m; Renaming of a Datafile: - We have a few steps to rename a data file: -
Read more

Oracle "Redo log Groups addition/deletion" | Karan Rajpoot

Image
                    Oracle "Redo log Groups addition/deletion" Step 1) To add redo log group first we will check the log groups, members details, location, and size. Example: - From the above output, we can see we have 3 groups and inside every group, we have 1 member each of size 50 MB and the location is /u01/app/oracle/oradata/karan/ Step 2) Now we will add one group and check the status of that group. Example: - Now again we can check the details of log groups and member. Now the 4th group and a member of 50 MB has been added successfully. Step 3) Now I will add one more member to the 4th log group. Example: - We can see from the above output that in group 4, now 2 members are there. Steps to drop the latest added group and member Note: - We cannot drop CURRENT members as shown in the above output, So I will add a new member in group 3 and then I will drop that member. Step 1)  Addition of new
Read more