enq: HW - contention and latch: enqueue hash chains

An application uses serialized java objects to stored in the database to overcome the application server failovers. The java objects are stored as BLOB. Application has been using the same session storing mechanism for number of years (7+ years) and gone through the upgrades of Oracle versions 10.2 -> 11.1 -> 11.2 without any performance regress. During the inception (with a 10.2) high wait times on enq: HW - contention were encountered which was remedied with the use of the event 44951 (refer 740075.1)
However during a test, involving large number of concurrent application sessions (x10 baseline) the test system encountered high enq: HW - contention even though event 44951 has been set with level 1024.

Following actions were tried in order to reducing the number of and time spent on HW - contention event.
1. Large initial extent size for the lob segment.

  STORE AS "LOB_SEG"
  (
    ...(INITIAL 5368709120 NEXT 134217728 ...
  );

2. Large value for next extent, ideally this should be greater than the (average size of the LOB inserted x number of concurrent inserts).

STORE AS "LOB_SEG"
  (
    ...INITIAL 5368709120 NEXT 134217728 ...
  );

3. Tablespace with uniform extent allocation and extent size is greater than the (average size of the LOB inserted x number of concurrent inserts).
4. Large chunk size for lob segment and tablespace block size equal to the chunk size. Lob segment was placed in a tablespace with a block size of 32K and chunk size 32K was used for the lob semgment.

  STORE AS SECUREFILE "LOB_SEG"
  (
    TABLESPACE "TBS32K" CHUNK 32768
  );

However these actions only slightly reduced the HW - contention wait events and the performance was not satisfactory. At this point it was decided to use the secure file for the lob segments as it was considered better in performance compared to basic file.



Investigating secure file related issues came across the following MOS note (1532311.1) which mentioned high waits on buffer busy waits and enq: TX - contention when there are frequent updates on secure file lobs. Solution for this was to increase the secure file concurrency estimate hidden parameter (_securefiles_concurrency_estimate). However instead of relying on a parameter that depends on the concurrency, the application was modified by replacing the statement sequence of (insert/update) with an (insert/delete/insert).
Running the same test as earlier resulted in high latch: enqueue hash chain waits.

This wait event was as a result of bug 13775960 which results high enqueue hash chains for concurrent inserts on secure files (refer 13775960.8). Luckily there's patch for the bug (13775960 which supersede the patch 13395403) and once applied the enqueue hash chain wait events were resolved.
Even though Oracle documents says that secure file out perform the basic files as in this case the secure file themselves pose some issues of their own which must be tested against.

Useful metalink notes
Bug 13775960 - "enqueue hash chains" latch contention for delete/insert Securefile workload [ID 13775960.8]
Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation [ID 1532311.1]
Bug 2530125 - Hang possible with "enqueue hash chains" latch held during deadlock detection [ID 2530125.8]
Bug 13395403 - "enqueue hash chains" latch contention on Securefile blob DMLs - superseded [ID 13395403.8]
'enq HW - contention' For Busy LOB Segment [ID 740075.1]
How To Analyze the Wait Statistic: 'enq: HW - contention' [ID 419348.1]

Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2

This post list the steps of upgrading from 12.1.0.1 to 12.1.0.2 for single instance database in a data guard configuration (physical standby). The single instance databases are non-CDB. When upgrading databases in a data guard configuration the upgrade process is initiated on the standby site by upgrading the standby database software first. Upgrade of database software to 12.1.0.2 is done as an out of place upgrade (oppose to in-place upgrade). As such the database software could be installed in a different oracle home while the redo apply is taking place. Installing 12.1.0.2 database software is identical to that of 12.1.0.1 and there no new steps to be carried out.
Once the database software is installed copy the spfile , initfile, oracle password file, tnsnames.ora and listener.ora file from the 12.1.0.1 oracle home into the 12.1.0.2 oracle home. In this case the 12.1.0.1 oracle home is /opt/app/oracle/product/12.1.0/dbhome_1 and 12.1.0.2 oracle home is /opt/app/oracle/product/12.1.0/dbhome_2

echo $ORACLE_HOME
/opt/app/oracle/product/12.1.0/dbhome_1  ## 12.1.0.1 ORALCE HOME 
cd $ORACLE_HOME/dbs
$ cp spfileent12c1s.ora initent12c1s.ora orapwent12c1s ../../dbhome_2/dbs/
$ cd ../network/admin/
$ cp tnsnames.ora listener.ora ../../../dbhome_2/network/admin/

Open the listener.ora file that is in the new ORACLE_HOME/network/admin and edit the static listener entries to reflect the new oracle home path. These static listener entries were created as part of the data guard configuration.

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = ent12c1)
                (SID_NAME = ent12c1)
                (ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
        )
)

Defer the redo transport on the primary until the standby is mounted using the new 12.1.0.2 oracle home.

SQL> alter system set log_archive_dest_state_2='defer';

and cancel the redo apply on the standby

SQL> alter database recover managed standby database cancel;

Modify the /etc/oratab to reflect the new oracle association with the standby instance

cat /etc/oratab
ent12c1s:/opt/app/oracle/product/12.1.0/dbhome_2:N

Stop the listener started out of the 12.1.0.1 home.
Modify the environment variables so that they are pointed to new 12.1.0.2 oracle home (ORACLE_HOME,PATH and etc). Start the listener using the 12.1.0.2 oracle home. Verify that listener is started from the new home

$ lsnrctl start

Starting /opt/app/oracle/product/12.1.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/app/oracle/product/12.1.0/dbhome_2/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/hpc5/listener/alert/log.xml
...
Services Summary...
Service "ent12c1s" has 1 instance(s).
  Instance "ent12c1s", status UNKNOWN, has 1 handler(s) for this service..

Mount the standby database using the 12.1.0.2 oracle home and start the redo apply

SQL> startup mount;
SQL> alter database recover managed standby database disconnect;

Verify from the alert log that database is started using 12.1.0.2 oracle home parameter file.

ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2
System name:    Linux
Node name:      hpc5.domain.net
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:        x86_64
Using parameter settings in server-side spfile /opt/app/oracle/product/12.1.0/dbhome_2/dbs/spfileent12c1s.ora

Enable the redo transport on the primary

alter system set log_archive_dest_state_2='enable'

This conclude the upgrade activity on the standby. The standby database instance will be upgrade once the redo generated during the primary database upgrade is transported and applied on to the standby.



Although the setup used on this post consists of a data guard configuration the primary site steps are valid for single instances without data guard configuration as well. The software upgrade on the primary is done as an out of place upgrade. Once the 12.1.0.2 software is installed run the preupgrade script from the new home.

cd /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin
SQL> @preupgrd.sql


Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in ENT12C1...
***************************************************************************


      ************************************************************

                 ====>> ERRORS FOUND for ENT12C1 <<====

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.


 1) Check Tag:    PURGE_RECYCLEBIN
    Check Summary: Check that recycle bin is empty prior to upgrade
    Fixup Summary:
     "The recycle bin will be purged."

            You MUST resolve the above error prior to upgrade

      ************************************************************

      ************************************************************

              ====>> PRE-UPGRADE RESULTS for ENT12C1 <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/postupgrade_fixups.sql

      ************************************************************

***************************************************************************
Pre-Upgrade Checks in ENT12C1 Completed.
***************************************************************************

***************************************************************************

As instructed on the output run the fixup sql on the primary database

@/opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/preupgrade_fixups.sql

Run pre upgrade sql again and check of errors and warnings. If there are any errors of warnings fix them before continuing with DBUA. To being the upgrade run DBUA from the 12.1.0.2 home.
Select the database upgrade option.

Select the source oracle home and the instance to upgrade.

The DBUA detects the data guard configuration is in place and prompt to sync the standby database. This is not an error but an information provided by the DBUA. There should not be any archive gaps prior to the primary upgrade.

The network configuration page did not detect the listener running out of the 12.1.0.1 home. Because of this when the upgrade finished the redo transport failed as there was no tnsnames.ora file in the 12.1.0.2 home and standby was unable to fetch the archive logs as the listener was running out of the old home. If during the upgrade no listener is detected similar to below, manually copy the listener.ora and tnsnames.ora files to the 12.1.0.2 home and edit the oracle home entry for static listener registration.

Take a backup before the upgrade or allow DBUA to take a backup as part of the upgrade process.

Summary and upgrade

Upgrade results

Once the upgrade is completed set the environment variables to reflect the new oracle home.
Also run the post upgrade script as suggested by the pre upgrade sql

@/opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/postupgrade_fixups.sql

Verify listener is running out of the new home. If as mentioned earlier listener.ora and tnsnames.ora files are not moved to the new oracle home, move them manually to the 12.1.0.2 home. Once moved stop the listener and start it to run out of the new 12.1.0.2 oracle home.
To validate the data guard configuration is working carry out few log file switches and verify they are received at the standby and applied.
Once tested and satisfied with the upgrade, update the compatible initialization parameter to 12.1.0.2. It is not possible to downgrade the database once this has been set. In this case the standby was the first to be set compatible = 12.1.0.2 followed by the primary. Read data guard admin guide for exact steps.
This conclude the upgrade from 12.1.0.1 to 12.1.0.2 This is not an extensive "how to" guide but only the highlights. For complete information refer the oracle upgrade guide and the following metalink notes.

Useful metalink notes
Oracle 12cR1 Upgrade Companion [ID 1462240.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) [ID 1520299.1]
Actions For DST Updates When Upgrading To Or Applying The 12.1.0.2 Patchset [ID 1665676.1]

Related Posts
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Grid Infrastructure
Upgrading RAC from 12.1.0.1 to 12.1.0.2 - Grid Infrastructure
Upgrade from 11.1.0.7 to 11.2.0.4 (Clusterware, ASM & RAC)
Upgrading from 10.2.0.4 to 10.2.0.5 (Clusterware, RAC, ASM)
Upgrade from 10.2.0.5 to 11.2.0.3 (Clusterware, RAC, ASM)
Upgrade from 11.1.0.7 to 11.2.0.3 (Clusterware, ASM & RAC)
Upgrading from 11.1.0.7 to 11.2.0.3 with Transient Logical Standby
Upgrading from 11.2.0.1 to 11.2.0.3 with in-place upgrade for RAC
In-place upgrade from 11.2.0.2 to 11.2.0.3
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 1
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 2
Upgrading from 11gR2 (11.2.0.3) to 12c (12.1.0.1) Grid Infrastructure

RMAN-05609: Must specify a username for target connection when using active duplicate

In 11gR2 it was possible to run an active duplication command for data guard and cloning of DB via duplication without explicitly specifying a username for the target instance.
But in 12c1 this would lead to RMAN-05609: Must specify a username for target connection when using active duplicate error.

$ rman target / auxiliary sys/ent12c1db@ent12c1stns

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Aug 4 13:08:03 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ENT12C1 (DBID=209099011)
connected to auxiliary database: ENT12C1S (not mounted)

RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'ent12c1','ent12c1s','ENT12C1','ENT12C1S'
4> set db_unique_name='ent12c1s'
5> set db_file_name_convert='/data/oradata/ENT12C1','/opt/app/oracle/oradata/ENT12C1S'
6> set log_file_name_convert='/data/oradata/ENT12C1','/opt/app/oracle/oradata/ENT12C1S','/data/flash_recovery/ENT12C1','/opt/app/oracle/fast_recovery_area/ENT12C1S'
7> set control_files='/opt/app/oracle/oradata/ENT12C1S','/opt/app/oracle/fast_recovery_area/ENT12C1S'
8> set db_create_file_dest='/opt/app/oracle/oradata'
9> set db_recovery_file_dest='/opt/app/oracle/fast_recovery_area'
10> set log_archive_max_processes='10'
11> set fal_client='ENT12C1STNS'
12> set fal_server='ENT12C1TNS'
13> set log_archive_dest_2='service=ENT12C1TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent12c1'
14> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent12c1s';

Starting Duplicate Db at 04-AUG-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/04/2014 13:08:11
RMAN-05501: aborting duplication of target database
RMAN-05609: Must specify a username for target connection when using active duplicate

Solution is to include the username and password for the target instance as well.

$ rman target sys/ent12c1 auxiliary sys/ent12c1@ent12c1stns

This is different to how duplication was done on 11gR2 as such may require changes to duplication scripts when used with 12c.