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