Friday, November 25, 2011

"log file sync" while performing COMMIT and ROLLBACK


When the performance of the database go slow then we go for the ADDM and looks at the symptoms and immediately starts changing the system to fix those symptoms. In my case , i found that the "commit and rollback" has major impact on the slowness of the database . The finding points says  : 
Waits on event “log file sync” while performing COMMIT and ROLLBACK operations were consuming significant database time.

The Recommended action suggest is

Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions. 

Since,the application is committing too ,so we have to reduce the frequent commit . The reason for the log sync waits occur when sessions wait for redo data to be written to disk. Typically this is caused by slow writes or committing too frequently in the application. Checking the "user commits" section in the AWR report can reveal if the issue is related to frequent committing.  

The following  tips may help to reduce log file sync when writes are slow :

  • Tune  LGWR  to get good  throughput to disk . eg: Do not put redo logs on RAID 5 .
  • If there are lots of short duration transactions, see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit must confirmed that the relevant REDO is on disk before it can complete. Although commits can be "piggybacked" by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
  • Check the size of the log buffer size 
  • Check the estimated size of the redo log file . Also check how fast the redo logfile is switching 
  • Check if any of the processing can use the COMMIT NO-WAIT option  

In 11g the commit_write  parameter is deprecated . It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters. It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters.For more check the meta-link [857576.1]

As in my case i have perform the following steps and find that the performance of the database has increased and the impact of "COMMIT and ROLLBACK"  is too low. Here are steps

1.) Estimate the size of the Redolog file :  The value for optimal_logfile_size is expressed in MB. This value changes frequently based on the DML load on your database.Use the below query to calculate the estimated size of the redo logfile .

SQL> select   TARGET_MTTR "trgt_mttr",  ESTIMATED_MTTR "est_mttr",  WRITES_MTTR  "wrt_mttr",  WRITES_LOGFILE_SIZE  "wrt_lg_size", OPTIMAL_LOGFILE_SIZE  "opt_lg_size"  from    v$instance_recovery ;

 trgt_mttr     est_mttr     wrt_mttr    wrt_lg_size     opt_lg_size
----------     ----------     --------       ------------     -----------
         0           227          0                    52                300


After this i find that redolog file switch after every 3-4 minutes. Hence, I have increased the size of the redo logfile to 300 MB and the check the log switch. I found that the redo log file switches after approax every 20min . 

Since ,I have set the sga_target ,therefore cannot change the log buffer size. In my case my redolog file is approax 5.5MB . Some expert also suggest to increase the size of log buffer to 10MB or more to reduce the impact of the "commit and rollback ". Though , I have  not tested this and i will come later on this .  

This is what the workaround , i have perform to reduce the "commit and rollback" impact .


Enjoy    :-) 


Saturday, November 19, 2011

Configuration of Snapshot Standby Database in Oracle 11g


Snapshot Standby is a new features introduced in Oracle 11g. A snapshot standby database is a type of updatable standby database that provides full data protection for a primary database. A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

The main benefits of snapshot standby database is that we can convert the physical standby database into a read-write real time clone of the production database and we can then temporarily use this environment for carrying out any kind of  development testing, QA type work or to test the impact of a proposed production change on an application. 

The best part of this snapshot features is that the Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.All the features of the flashback  are inherent in the snapshot standby.

Here we will configure the snapshot standby database

Step 1 : Create the physical standby database 
Create the physical standby database .

Step 2:  Enable Flashack Parameter 

SQL>  alter system set db_recovery_file_dest_size=4G  scope=both  ; 
System altered.

SQL> alter system set db_recovery_file_dest='D:\standby\fra\'  scope=both ; 
System altered.

SQL> show  parameter  db_recovery
NAME                                            TYPE                   VALUE
-----------------------------      -----------      -------------------------
db_recovery_file_dest                string                  D:\standby\fra\
db_recovery_file_dest_size         big integer              4G

Step 3  :  Stop the media recovery process 
SQL> alter database recover managed standby database cancel;
Database altered.

Step 4 : Ensure that the database is mounted, but not open.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount

Step 5  :  Create guaranteed restore point
SQL> create restore point snapshot_rspt guarantee flashback database;
Restore point created.

Step 6  :  Perform the conversion to snapshot standby database
SQL> alter database convert to snapshot standby ;
Database altered.

SQL> select name,open_mode,database_role from v$database;
NAME           OPEN_MODE            DATABASE_ROLE
---------        --------------        --------------------------
NOIDA           MOUNTED              SNAPSHOT STANDBY

SQL> alter database open;
Database altered.

SQL> select name,db_unique_name ,open_mode,database_role from v$database;
NAME      DB_UNIQUE_NAME    OPEN_MODE          DATABASE_ROLE
-------   ----------------------    ---------------      ------------------------
NOIDA         gurgoan                  READ WRITE        SNAPSHOT STANDBY

Since the database is in read-write mode , so we can make some changes or even change the parameter say tunning parameter and check there performance after converting to physical standby and further flashback the whole changes.

SQL> select name,guarantee_flashback_database  from  v$restore_point;
NAME                                                                                            GUA
-----------------------------------------------------------------      -------
SNAPSHOT_STANDBY_REQUIRED_11/18/2011 20:41:01            YES
SNAPSHOT_RSPT                                                                 YES

While the original physical standby database has been now converted to snapshot database, some changes are happening on the Primary database and those changes are shipped to the standby site but not yet applied. They will accumulate on the standby site and will be applied after the snapshot standby database is converted back to a physical standby database.

Step  7  :  Convert snapshot standby to physical standby 
SQL> shut immediate 
SQL> startup mount

SQL> alter database convert to physical standby ;
Database altered.

SQL>shut immediate
SQL> startup mount

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name,open_mode,db_unique_name,database_role from v$database;
NAME      OPEN_MODE        DB_UNIQUE_NAME      DATABASE_ROLE
-------    --------------     -----------------------    ----------------------
NOIDA    READ ONLY          gurgoan                     PHYSICAL STANDBY

SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

Hence, we finally back to physical standby database.


Enjoy    :-) 


Thursday, November 17, 2011

ORA-16606: unable to find property


This error occurs because the apply service state is inconsistent or the named property does not exist . In my case, when i configured the data broker and is working fine. Whenever, i fire the below the command the error occurs as .

DGMGRL> show configuration verbose noida
ORA-16606: unable to find property "noida"

While using the below command it is working fine.

DGMGRL> show configuration verbose

Configuration            - dgnoida
Protection Mode       : MaxPerformance
Databases                 : noida - Primary database
                                : delhi - Physical standby database
Fast-Start Failover   : DISABLED
Configuration Status : SUCCESS


To solve this problem , I crosscheck the archive dest and state of primary database and find that the state of status of log_archive_dest_state_2 is deffered . I enable the dest state and then  disable the configuration and later enable the configuration .Now it's working fine.

DGMGRL> disable configuration
Disabled.
DGMGRL> enable configuration
Enabled.

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


Enjoy    :-) 


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