Thursday, November 17, 2011

How to Drop Data Guard Configuration in oracle 11g


Once while configuring the dataguard broker, i faced the ORA-16625 and ORA-16501 . This error occurs beacuse the broker rejects an operation requested by the client when the database required to execute that operation was not reachable from the database where the request was made. If the request modifies the configuration, the request must be processed by the copy of the broker running on an instance of the primary database.

Few days ago i configured the standby database "RED" and broker, later drop it . Next time while configuring the data broker the error occurs as 

DGMGRL> create configuration 'dgnoida'
> as primary database is 'noida'
> connect identifier is 'noida';
Error: ORA-16501: the Data Guard broker operation failed
Error: ORA-16625: cannot reach database "red"
Failed.

To solve this issue, I have remove the data guard broker configuration and then created the dataguard broker. The steps to drop the configuration are as follows :

Step 1 : Stop the standby data guard broker process  
( On Standby )

SQL>show parameter dg_broker
NAME                               TYPE                          VALUE
------------------------   -----------     ----------------------------------------------
dg_broker_config_file1        string             C:\APP\NEERAJS\PRODUCT\11.2.0\
                                                                DBHOME_1\DATABASE\DR1NOIDA.DAT
dg_broker_config_file2       string              C:\APP\NEERAJS\PRODUCT\11.2.0\
                                                                DBHOME_1\DATABASE\DR2NOIDA.DAT
dg_broker_start                  boolean                        True

SQL> alter system set dg_broker_start=false;
System altered.

Step 2 : Diable the archive log state 
(On Primary )
SQL> select dest_id,destination,status from v$archive_dest where target='STANDBY';
DEST_ID      DESTINATION     STATUS
--------       ---------------     ----------
   2                delhi                 VALID

SQL> alter system set log_archive_dest_state_2=defer ;
System altered.

SQL> select dest_id,destination,status from v$archive_dest where target='STANDBY';

DEST_ID      DESTINATION     STATUS
--------       ---------------     ----------
   2                delhi                 DEFERRED


Step 3 : On both system rename or drop the metadata files

SQL> show parameter dg_broker

NAME                               TYPE                          VALUE
------------------------   -----------     ----------------------------------------------
dg_broker_config_file1        string             C:\APP\NEERAJS\PRODUCT\11.2.0\
                                                                DBHOME_1\DATABASE\DR1NOIDA.DAT
dg_broker_config_file2       string              C:\APP\NEERAJS\PRODUCT\11.2.0\
                                                                DBHOME_1\DATABASE\DR2NOIDA.DAT
dg_broker_start                  boolean                        False


Delete or rename the file DR1NOIDA.DAT and DR@NOIDA.DAT .


Enjoy       :-) 


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

Wednesday, November 9, 2011

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