Data Guard Broker Configuration (DGMGRL Utility)

-

Data Guard Broker Configuration (DGMGRL Utility)

Data Guard broker configuration using the command line dgmgrl interface,  Below are the step by step procedure tested & verified in our Test ENV

Primary Database: PR
Standby Database: STD

Set the below parameter as on both Primary as well as Standby database start the Data Guard Broker process

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.

Edit the listener.ora on both nodes to add a static entry for DGMGRL

This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

$ cat $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (GLOBAL_DBNAME = pr_dgmgrl)
      (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

Create the configuration

[oracle@pr ~]$ dgmgrl
DGMGRL> connect sys/xxx;
Connected

DGMGRL> create configuration 'PRDG' AS PRIMARY DATABASE IS 'pr' Connect identifier is 'pr';
Configuration "PRDG" created with primary database "pr"

Add the Standby database to the configuration

DGMGRL> add database 'std' as connect identifier is 'std';
Database "std" added

DGMGRL> show configuration

Configuration - PRDG
  Protection Mode: MaxPerformance
  Databases:
    pr  - Primary database
    std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Enable the configuration

DGMGRL> enable configuration
Enabled

DGMGRL> show configuration
Configuration - PRDG
  Protection Mode: MaxPerformance
  Databases:
    pr  - Primary database
    std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
View the Standby and Primary database properties

DGMGRL> show database pr

Database - pr
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    pr
Database Status:
SUCCESS

DGMGRL> show database std

Database - std
  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:      1.92 MByte/s
  Real Time Query: OFF
  Instance(s):
    std
Database Status:
SUCCESS

DGMGRL> show database verbose pr

Database - pr
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    pr
    Properties:
    DGConnectIdentifier             = 'pr'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u02/oradata/redo, /oradata/redo'
    LogFileNameConvert              = '/oradata/redo, /u02/oradata/redo/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'pr'
    StaticConnectIdentifier         =   '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pr_DGMGRL)(INSTANCE_NAME=pr)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area/PR/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS

DGMGRL> show database verbose std

Database - std
  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:      1.92 MByte/s
  Real Time Query: OFF
  Instance(s):
    std
    Properties:
    DGConnectIdentifier             = 'std'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/oradata/redo, /u02/oradata/redo'
    LogFileNameConvert              = '/oradata/redo/, /u02/oradata/redo/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'std'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=std_DGMGRL)(INSTANCE_NAME=std)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u02/oradata/std/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS
Change the properties of a configured database

DGMGRL> edit database 'pr' set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> show database pr statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database std statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
DGMGRL> show database pr InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE


On Standby_Database – Configuration


DGMGRL> show configuration
not logged on

DGMGRL> connect sys/sys@std
Connected.

DGMGRL> show configuration
Configuration - PRDG
  Protection Mode: MaxPerformance
  Databases:
    pr  - Primary database
    std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database pr
Database - pr
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    pr
Database Status:
SUCCESS

DGMGRL> show database std
Database - std
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      1.92 MByte/s
  Real Time Query: OFF
  Instance(s):
    std
Database Status:
SUCCESS

Enable the configuration

DGMGRL> enable database std
Enabled.

DGMGRL> show database std
Database - std
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      1.89 MByte/s
  Real Time Query: OFF
  Instance(s):
    std
Database Status:
SUCCESS

DGMGRL> edit database 'std' set state='APPLY-OFF';
Succeeded.
DGMGRL> edit database 'std' set state='APPLY-ON';


Hence done the configuration of the Data Guard Broker


If there is any clarification, Please revert me back 24/7 - E-mail: sayeed202@gmail.com



Comments

Post a Comment

Popular posts from this blog

RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2

-
RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2 Testing Various RAC Failure scenario like: 1 - Node Failure 2 - DB Instance Failure 3 - ASM Instance Failure 4 - Listener failure 5 - Public Network Failure 6 - Private Network Failure 7 - OCR & Voting Disk Failure 8 - ASM Disk Failure ============== 1 - Node Failure ============== It can be planned, unplanned or all the nodes it could be any scenario Let us start the workload & shutdown the node The expected result will be: Resources will go offline Instance recovery will be performed Node vip & scan vip fail to surviving node Scan listener will fail over surviving node Client connection are moved to surviving instance Now shutdown Node 1 in OS Level intentionally or due to some reason the node 1 is shutdown #Shutdown –h now Check the node 2 status [oracle@test-rac2 ~]$ crs_stat -t Name           Type           Target    State     Host ----------------------------
Read more

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

-
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' Error occur while restoring the database from the backup of RMAN RMAN> alter database open resetlogs; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/03/2017 16:05:14 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' To resolve this error fallow the below procedure – Hopefully you will succeed If you’re primary database is flashback & it has the standby – Fallow below procedure RMAN> run { 2> recover database; 3> } ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter datab
Read more

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)

-
Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4) Export on 12c (12.1.0.2)   – Tested in our env : 10.20.0.82 ==================== SQL> select instance_name,version from v$instance; INSTANCE_NAME    VERSION ---------------- ----------------- ora12c           12.1.0.2.0 SQL> conn sys/sys as sysdba Connected. SQL> conn hassan/( password ); Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST SQL> select * from test; COLUMN1              NAME -------------------- -------------------- 1                    abc 2                    xyz [oracle@ora12c u02]$ mkdir dump [oracle@ora12c u02]$ chmod -R 777 dump/ [oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle.  All
Read more