Step-By-Step Configuration Of Data Guard Broker in Oracle 11g

As  we  have already  discuss  about  the Data Guard Broker  and its  benefits  in  earlier post . Here we will configure the data Guard Broker . Here are the steps :

Primary Databse   =  Noida
Standby Database =  Delhi

Step 1 :  Check the Data Guard Broker process 
SQL> sho parameter dg_broker
NAME                                   TYPE             VALUE
-----------------                    ----------          ----------
dg_broker_start                boolean          FALSE

Step 2  : Start the Data Guard Broker Process on Primary database   
SQL>alter system set dg_broker_start=true scope=both;
System altered.

Step 3 : Check DG_BROKER on standby database and start it 
SQL> sho parameter dg_broker
NAME                                    TYPE             VALUE
-----------------                      ----------         ----------
dg_broker_start                  boolean         FALSE

SQL>alter system set dg_broker_start=true scope=both ;
System altered.

Step 4 :   Edit the listener.ora file

Edit the listener.ora file which includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME in both primary and standby database . To set the value, lets check the db_domain value .

SQL> show parameter db_domain
NAME                              TYPE               VALUE
--------------                  -----------         --------------
db_domain                      string

Since the value of db_domain  is null so the the value of  GLOBAL_DBNAME = NOIDA_DGMGRL for primary database and for standby  GLOBAL_DBNAME = DELHI_DGMGRL. The primary listener.ora file  is as 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = noida_DGMGRL)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = noida)
    )
  )
Similarly, edit the listener.ora  file on standby database .

Step 5 : Configure the Data Guard Configuration 
C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxxx@noida
Connected.

DGMGRL> create configuration 'dgnoida'
> as primary database is 'noida'
> connect identifier is noida ;
Configuration "dgnoida" created with primary database "noida" .

Once the configuration is created then check the status of configuration .

DGMGRL> show configuration
Configuration            - dgnoida
Protection Mode       : MaxPerformance
Databases                : noida - Primary database
Fast-Start Failover    : DISABLED
Configuration Status : DISABLED

Step  6 :  Add standby database to the data broker configuration 
DGMGRL>  add database 'delhi' as
> connect identifier is delhi
> maintained as physical ;
Database "delhi" added

DGMGRL> show configuration
Configuration             -  dgnoida
Protection Mode         :  MaxPerformance
Databases                   :  noida - Primary database
                                    : delhi - Physical standby database
Fast-Start Failover      :  DISABLED
Configuration Status    :  DISABLED

Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration              - dgnoida
Protection Mode          : MaxPerformance
Databases                   : noida - Primary database
                                   : delhi - Physical standby database
Fast-Start Failover       : DISABLED
Configuration Status    : SUCCESS

Step 8 : View the Primary and Standby database properties 

DGMGRL> show database verbose noida
Database                 - noida
Role                        : PRIMARY
Intended State         : TRANSPORT-ON
Instance(s)              :  noida
Properties:
DGConnectIdentifier                = 'noida'
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      = '4'
LogArchiveMinSucceedDest  = '1'
DbFileNameConvert              = ''
LogFileNameConvert             = ''
FastStartFailoverTarget          = ''
StatusReport                          = '(monitor)'
InconsistentProperties            = '(monitor)'
InconsistentLogXptProps       = '(monitor)'
SendQEntries                        = '(monitor)'
LogXptStatus                        = '(monitor)'
RecvQEntries                        = '(monitor)'
HostName                            = 'TECH-199'
SidName                              = 'noida'
StaticConnectIdentifier          = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-199)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=noida_DGMGRL)(INSTANCE_NAME=noida)(SERVER=DEDICATED)))'
StandbyArchiveLocation       = 'D:\archive\'
AlternateLocation                 = ''
LogArchiveTrace                 = '0'
LogArchiveFormat               = 'ARC%S_%R.%T'
TopWaitEvents                    = '(monitor)'
Database Status                   = SUCCESS

DGMGRL> show database verbose delhi

Database                    - delhi
Role:                             PHYSICAL STANDBY
Intended State            :  APPLY-ON
  Transport Lag          :   0 seconds
  Apply Lag               :   0 seconds
  Real Time Query     :   ON
  Instance(s)              :   delhi
  Properties:
    DGConnectIdentifier                = 'delhi'
    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             = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\, E:\oracle\, D:\app\stand\oradata\'
    LogFileNameConvert           = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\'
    FastStartFailoverTarget        = ''
    StatusReport                        = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps    = '(monitor)'
    SendQEntries                      = '(monitor)'
    LogXptStatus                      = '(monitor)'
    RecvQEntries                     = '(monitor)'
    HostName                          = 'TECH-284'
    SidName                            = 'delhi'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-284)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'D:\app\stand\archive\'
    AlternateLocation                    = ''
    LogArchiveTrace                    = '0'
    LogArchiveFormat                  = 'ARC%S_%R.%T'
    TopWaitEvents                       = '(monitor)'
Database Status                       :   SUCCESS

DGMGRL>


Enjoy        :-) 

ORA-16191: Primary log shipping client not logged on standby

Once I have changed the password of the primary database and find everything is working fine. Logs are applied on standby and sychronization between primary and standby database is fine . On next day when i restarted my standby database and find that the redo logs are not applying on the standby database . When I checked the alert log file then found the below error message.

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files.
 returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC0]: Error 16191 connecting to noida for fetching gap sequence
Errors in file d:\app\stand\diag\diag\rdbms\delhi\delhi\trace\delhi_arc0_2308.trc:
ORA-16191: Primary log shipping client not logged on standby
Errors in file d:\app\stand\diag\diag\rdbms\delhi\delhi\trace\delhi_arc0_2308.trc:
ORA-16191: Primary log shipping client not logged on standby
Dictionary check complete

After some troubleshooting and googling, I came to conclusion that this error occurs because of the invalid user credentials while attempts to ship redo to standby database .

To solve this issue we have copy the primary password file to standby database and rename the password file. Once the password-file is copied  and renamed on  standby database then stop and resatrt the recovery to make password-file in use. The below command will stop and restart the recovery .

1.) Log into standby database and stop the recovery as
SQL> alter database recover managed standby database cancel;

2.)  Now restart the recovery as
SQL>alter database recover managed standby database disconnect from session ;

Perform a log switch on the primary database and check the archive sequence ,archive destination  and alert logfile. 

Enjoy    :-) 

Restrict A User From Being Dropped

Sometimes  we have to  put  the  restriction  on  those  user which  is  having DBA role even though they can't drop a particular schemas . In such scenario's we have to create the triggers to restrict the user   before  the drop command . Below script  will restrict the DBA's  to drop the particular schemas . Here is the triggers.

SQL> Create Or Replace Trigger  TrgDropUserRestrict
Before Drop On Database
Declare
        Begin If Ora_Dict_Obj_Name In ('SCOTT','OUTLN')    Then 
 Raise_Application_Error(-20001,'Cannot Drop User '||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !');  
        End If;
End;
/

Enjoy        :-)