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        :-)

Script to Query all table row counts

Once I have to calculate the no. of rows of  all tables in a schema. It seems quite tedious to count rows of all the tables one by one  as  :
SQL>select count(*) from table_name;


The second method is to export the schemas virtually by using the parameter estimate of Datapump. Using this method,we can check the logfile for tables row counts . Even this method is not so efficient because if the schemas size is large then we will take long time. We can export the schemas as
C:\> expdp system/xxxx@noida directory=dpump  schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCK

Another option to find table row counts is to use the pl/sql scripts .I found this script from http://www.dba-village.com  and is quite efficient and useful. Here is the below script to check the no. of rows in tables. 

create or replace function table_count (i_table_name varchar2)
return number
as 
 t_cnt number default 0;
begin
 execute immediate 'select count(*) from '||i_table_name into t_cnt;
 return t_cnt;
end;
/
show errors
select table_name, table_count(table_name) from user_tables;
drop function table_count;
Hence, this script allows for counting rows in tables without the need for sqlplus or temporary script files.

If we want to check the empty tables i.e; table not having in any rows can be found from the below scripts :

set termout off

col sql for a120

spool 'C:\checkempty_temp.sql'

set pages 0 feed off echo off

select 

'select '''||owner ||''' owner,'''||table_name||''' table_name  '||chr(10)||

'FROM '||owner||'.'||table_name||' where rownum<2 having count(*)=0; ' sql

from all_tables

where owner not in ('SYS','SYSTEM','OUTLN','WMSYS') and substr(owner,1,4)<>'OPS$'

--and (blocks>0 or last_analyzed is null)

--and owner='XXXX'

--and blocks=0

order by 1;

spool off

set termout on pages 100

@"c:\checkempty_temp.sql"

Enjoy   :-)

WISH YOU ALL A VERY VERY HAPPY DIWALI

May the light that we celebrate at Diwali show us the way and lead us together on the path of peace and social harmony.

           "WISH YOU ALL A VERY HAPPY DIWALI" 

May millions of lamps illuminate your  life with endless joy,prosperity,health & wealth forever.

  

HAVE FUN AND ENJOY DIWALI    :-)