WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=61

Row Cache Lock Wait

Definition
In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out is when we see the row cache lock wait.

Analysis
Each row cache lock will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. In this sample select from v$rowcache you can find the enqueue types and the type of activity being performed within the dictionary cache.

PARAMETER COUNT GETS GETMISSES MODIFICATIONS
--------------------- ----- ------------ ---------- -------------
dc_free_extents 0 0 0 0
dc_used_extents 0 0 0 0
dc_segments 5927 131379921 4142831 693734
dc_tablespaces 22 188609668 2436 0
dc_tablespace_quotas 12 22779303 3843 0
dc_files 0 165961 22493 21
dc_users 19 145681559 2078 21
dc_rollback_segments 67 3906307 66 232
dc_objects 1927 70725250 2247804 74803
dc_sequences 4 142714 1599 142714



Common Enqueue Types

The tuning of the row cache lock wait is dependant upon the activity for each of the enqueue types. Of these, the most common are:

DC_SEQUENCES, this row cache lock wait may occur during the use of sequences. Tune by checking sequences to see if they have the cache option specified and if that cache value is reflective of the anticipated simultaneous inserts by the application.
DC_USED_EXTENTS and DC_FREE_EXTENTS, this row cache lock wait may occur during space management operations where tablespaces are fragmented or have inadequate extent sizes. Tune by checking whether tablespaces are fragmented, extent sizes are too small, or tablespaces are managed manually.
DC_TABLESPACES, this row cache lock wait may occur during the allocation of new extents. If extent sizes are set too low the application may frequently request new extents which could cause contention. Tune by checking for rapidly increasing number of extents.
DC_OBJECTS, this row cache lock wait may occur during the recompilation of objects. If object compiles are occurring this can require an exclusive lock which will block other activity. Tune by examining invalid objects and dependencies.

The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. Checking activity within the v$rowcache view is a good place to start for understanding this relationship as tuning can only be accomplished with analysis of the enqueue type. If a trace file is available you may also see the following error of “>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<”. Also realize that the row cache lock wait event may appear more frequently when using RAC. This is because the library cache and the row cache are global in RAC—causing the row cache lock wait to be more pronounced.

 Trace File Dump Analysis

Sequence values are cached in instance memory and by default 20 values are cached. As instance cache is transient, loss of an instance can result in loss of cached sequence values. Permanent record of highest possible value from any instance is kept track in SEQ$ table. SQLs accessing this sequence within an instance will access instance SGA. As each instance caches its own sequence values it is highly likely that SQLs accessing this sequence from different instance will create gaps in sequence values. Common knee-jerk reaction to this issue is to set nocache or cache 1 for these sequences. In a single instance environment, this approach will backfire due to massive updates to SEQ$ tables, buffer busy waits, latch free waits etc. In a RAC environment, this issue is magnified and almost hangs the instance. I had the privilege of working with a client to resolve one of their performance issues. Problem Instances were hung. It was not possible to login to the database. Many existing connections are working fine though. We were lucky enough that one of the DBAs had active connection to the database. So, we took systemstate dump from that session to see whey there is an hang (or slowness). Alert log was printing following lines at this time frame. So, we know that there is a problem with row cache enqueue. Wed Feb 18 06:31:31 2008 >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=45
Wed Feb 18 08:59:31 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=44
Wed Feb 18 08:59:31 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=43
Wed Feb 18 09:01:00 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=46
Wed Feb 18 09:01:00 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=27
systemstate dump

We took a systemstate dump with following command.

alter session set events 'immediate trace name systemstate level 4';

I won’t bore you with all the details. But let us review just necessary sections of the systemstate dump file. We know that processes were waiting for row cache enqueue and so I searched for row cache enqueue in the trace file. From the trace file, we see that many sessions are holding NULL mode row cache enqueue and requesting X mode.

row cache enqueue: session: 6126dfea0, mode: N, request: X
row cache parent object: address=65f5a2ce8 cid=13(dc_sequences) <<<<<< hash=5a31f94b typ=9 transaction=0 flags=00000000 own=65f5a2dc8[65f5a2dc8,65f5a2dc8] wat=65f5a2dd8[63c841c18,63c841e48] mode=N status=-/-/-/-/-/-/-/-/- request=X release=FALSE flags=2 instance lock id=QN 0010b020 00000000 <<<<<<< data= 0010b020 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Of course, cid=13 indicating that it is for a sequence [ dc_sequence is the row cache area with cache_id=13]. It is also visible that row cache enqueue is maped to instance lock with QN as type and managed by Global enqueue Services. Wait is for that row cache enqueue lock protecting dc_sequences. We need to identify specific sequence though. Looking for state objects in the same session shows one sequence. owners: SO: 63e1d04b8, type: 46, owner: 63c29edb8, flag: INIT/-/-/0x00 Sequence State Object: kdnssflg = kdnsshiw = INACTIVE kdnsssqn = 0x64cdeba58 KGL Sequence Object #1093664: <<<<<<<<< kdnsqflg = kqdsnflg = KQDSNNOC kdnsqcus = 0 kdnsqisp INACTIVE Increment = +1 Minvalue = +1 Maxvalue = +999999999999999999999999999 Cachesize = +0 Highwater = +323649926 Nextvalue = ############################### Clearly above section shows that these lines are for sequence with object id 1093664. Querying dba_objects for that object_id shows sequence_name. select owner, object_name , object_type, created from dba_objects where object_id=1093664 SQL> /

OWNER OBJECT_NAME OBJECT_TYPE CREATED
------------- ----------- ------------ ----------
SCOTT STG__SEQ SEQUENCE 12-OCT-07
There is also library cache object accessing that sequence, confirming our analysis.

SO: 640cc9230, type: 51, owner: 6136d45b8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=640cc9230 handle=64cdf5868 mode=N
call pin=63c28f6f8 session pin=0
htl=640cc92a0[643a01130,643a01130] htb=643a01130
user=6136d45b8 session=6136d45b8 count=1 flags=[00] savepoint=58
LIBRARY OBJECT HANDLE: handle=64cdf5868
name=SELECT SCOTT.STG__SEQ.NEXTVAL FROM DUAL
hash=5dbaf014 timestamp=02-18-2008 08:00:46
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/[12010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=20
lwt=64cdf5898[64cdf5898,64cdf5898] ltm=64cdf58a8[64cdf58a8,64cdf58a8]
pwt=64cdf58c8[64cdf58c8,64cdf58c8] ptm=64cdf5958[64cdf5958,64cdf5958]
ref=64cdf5878[64cdf5878, 64cdf5878] lnd=64cdf5970[64cdb02f8,64ce05748]
LIBRARY OBJECT: object=64cdf4b40
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
Sequence cache

In summary, we know that sessions were waiting for row cache enqueues with QN type. That row cache enqueue was protecting a parent row cache entry for dc_sequences. SO [State Objects] for both sequences and cursors accessing that sequence is also exists in the systemstate dump file.

Let’s query properties of that sequence.


SQL> select * from dba_sequences where sequence_name='STG__SEQ';

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE
------------------------------ ------------------------------ ----------
MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------- ------------ - - ---------- -----------
SCOTT STG__SEQ 1
1.0000E+27 1 N N 0 338585872
Evidently cache size is set to 0. High last_number also indicates that this is an heavily accessed sequence.

Essentially, keeping sequence with nocache in RAC for highly accessed sequences is pretty bad idea. Login to the database accesses sequences too, so if row cache enqueues are not available, then logins will hang. This is why existing connections were working fine, but new logins were simply hung. We are able to pinpoint all these using systemstate dump.

Configuring BPEL Security Provider as OID

Following are the steps to configure BPEL Security Providers as OID.
By default the security provider for the BPEL will be Jazn. So if we need to modify the security provider to OID, following steps can be followed. This steps was tested and applied on the Oracle SOA Advanced Installation and not for the Enterprise Deployment Topology.

STEP1: Open a command prompt and Move to the following path
$ORACLE_HOME\bpel\system\services\install\ant-tasks

STEP2: Set the following homes
SET ORACLE_HOME=D:\Oracle\OracleAS\OracleSOANEw ( This is a sample path)
SET JAVA_HOME=$ORACLE_HOME\jdk
SET ANT_HOME= $ORACLE_HOME\ant

STEP3: If a custom realm is used and if the same realmName is used in searchbase this should be removed.
Ex: Suppose we have a Realm with dn dc=amaf, dc=ae and also we have another OU Ou=amaf, dc=amaf, dc=ae And if both are present in the user searchbase, then it is better to remove the other from the searchbase, else the below step configure_oid will fail.
Means : Remove OU=amaf,dc=amaf,dc=ae from OID searchbase
This can be done by editing orclcommonuserbase field of cn=Common,cn=Products,cn=OracleContext,dc=amaf,dc=ae

STEP4: From Enterprise Manager, modify the security provider for the oc4j_soa instance
OC4J: oc4j_soa \ Administration \ Security \ Identity Management \
Add the Internet Directory Host and Port
STEP5: Restart oc4j_soa Instanace

STEP6:Execute the configure_oid from the below path,
$ORACLE_HOME\bpel\system\services\install\ant-tasks

Syntax: "./configure_oid.bat

Ex: configure_oid orcladmin oracle123 389 false amaf seedAllUsers oc4jadmin oracle123 oc4j_soa

Above script will load BPEL users and demo users to OID and also update below xml files :

After executing the above script please note the following points
1) Some Users may fail to load to oid because of the password policy.So this can be done by modifying the password in the ldif file to matching password policy and load the users using the below command
ldapmodify -h test-app-01 -p 389 -D cn=orcladmin -w oracle123 -f $ORACLE_HOME\bpel\system\services\config\ldap\user.ldif

2) The above script also suppose to modify the Jazn.xml on oc4j_soa instance. But this script modifies the jazn.xml of the home instance. So as a workaround, we need to copy the content of jazn.xml of the Home instance and paste the same in jazn.xml of the oc4j_soa
3) Makesure in the jazn.xml contain Oracle Internet Directory Hostname and port.

jazn.xml will be in the following folder:
$ORACLE_HOME\j2ee\oc4j_soa\config

Above script also modifies the below xml files.
[java] Adding jaas-mode attribute to hw_services orion-application.xml
[java] Adding jaas-mode attribute to orabpel orion-application.xml

STEP7: Restart oc4j_soa Instanace
STEP8: Try to login to bpel console using an user in the OID

How To Drop, Create And Recreate DB Control In A 10g Database

Subject: How To Drop, Create And Recreate DB Control In A 10g Database
Doc ID: 278100.1 Type: BULLETIN
Modified Date : 08-SEP-2008 Status: PUBLISHED

In this Document
Purpose
Scope and Application
How To Drop, Create And Recreate DB Control In A 10g Database
DB Control options:
A. Delete DB Control Objects:
B. Create DB Control Objects
C. Recreate/ReConfig DB Control
References



--------------------------------------------------------------------------------



Applies to:
Enterprise Manager Grid Control - Version: 10.1.0.2 to 11.1.0.0
Information in this document applies to any platform.
DBConsole
Purpose
This article provides detailed instructions on how to 1) create, 2) drop and 3)recreate the repository and configuration files for the DB Control application used to manage a single 10g Database.


Scope and Application
The steps in this article are written for a DBA or System Administrator who needs to create, drop or reconfigure the DB Control Application.

The format of the document will include steps for both 10.1 and 10.2 EMCA because the commands changed between the two releases.

For detailed instructions on DB Control 10.2 for RAC, please consult also:
Note 395162.1 How to manage DB Control 10.2 for RAC Database with emca

How To Drop, Create And Recreate DB Control In A 10g Database
DB Control options:


A. Delete DB Control Objects:
Option 1. Delete DB Control Configuration Files using EMCA scripts
Option 2. Delete DB Control Configuration Files Manually:
Option 3. Delete DB Control Repository Objects using RepManager
Option 4. Delete DB Control Repository Objects Manually
Option 5. Delete DB Control Configuration Files and Repository Objects using EMCA


Option 1. Delete DB Control Configuration Files using EMCA scripts:
For DB Control 10.1.x, run the command: /bin/emca -x For DB Control 10.2.x, run the command: bin/emca -deconfig dbcontrol db



Important Note: the option -deconfig removes the EM jobs from Scheduling before the DB Control is deconfigured. Otherwise the repository, which is not dropped, could be corrupted or inconsistent if EM jobs were scheduled as the time the DB Control is dropped. It could also cause some errors during the DB Control deletion.

Option 2. Delete DB Control Configuration Files Manually:

Remove the following directories from your filesystem:
/
/oc4j/j2ee/OC4J_DBConsole__



NOTE:
On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it

Alternatively on Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete '

- where is the DB Control service name (typically: OracleDBConsole)

Also available from Microsoft is the delsrv.exe command. (Free download from Microsoft)



Option 3. Delete DB Control Repository using RepManager:
This option is not as complete as the other options. You may find that dropping the repository using the commandline options is a better solution. Also note, RepManager is not used to create a DB Control Repository.
In both 10g R1 and R2 run: /sysman/admin/emdrep/bin/RepManager -action drop


Warning: this command puts the database in Quiesce Mode.
Please consult the Note 375946.1 Running EMCA Results in Database quiesce And No
New Connections or Operations Can Be Performed During the DB Control Repository Creation



Option 4. Delete DB Control Repository Objects Manually Step 1: Drop AQ related objects in the SYSMAN schemaLogon SQLPLUS as user SYSMANSQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE);Step 2: Drop the DB Control Repository ObjectsLogon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP RESTRICT;SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;SQL> EXEC sysman.setEMUserContext('',5);SQL> REVOKE dba FROM sysman;SQL> DECLARECURSOR c1 ISSELECT owner, synonym_name nameFROM dba_synonymsWHERE table_owner = 'SYSMAN';BEGINFOR r1 IN c1 LOOPIF r1.owner = 'PUBLIC' THENEXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;ELSEEXECUTE
IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;END IF;END LOOP;END;/SQL> DROP USER mgmt_view CASCADE;SQL> DROP ROLE mgmt_user;SQL> DROP USER sysman CASCADE;SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Note: The above will completely delete the DB Control repository from the database; under certain circumstances (e.g. you want to recreate the repository later on) the following statements may be sufficient to remove the repository:

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:SQL> drop user sysman cascade;SQL> drop role MGMT_USER;SQL> drop user MGMT_VIEW cascade;SQL> drop public synonym MGMT_TARGET_BLACKOUTS;SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Option 5. Delete DB Control Configuration Files and Repository Objects using EMCA

For DB Control 10.1.x, dropping both the configuration files and the repository objects is a two step process. Run the following two commands:
/bin/emca -x /sysman/admin/emdrep/bin/RepManager -action drop

For DB Control 10.2.x, both configuration files and repository objects can be deleted with a single command. Run the command:
/bin/emca -deconfig dbcontrol db -repos drop


Warning: this command puts the database in Quiesce Mode.
Please consult the Note 375946.1 Running EMCA Results in Database quiesce And No
New Connections or Operations Can Be Performed During the DB Control Repository Creation


B. Create DB Control Objects
Option 1. Create the DB Control Configuration Files
Option 2. Create the DB Control Repository Objects and Configuration Files


Option 1. Create the DB Control Configuration Files
To create only the DB Control configuration files, skipping the repository creation (this would be done for instance if you dropped only the files and left the repository in place):
For DB Control 10.1.x, run the command: /bin/emca -rFor DB Control 10.2.x, run the command: /bin/emca -config dbcontrol db


Option 2. Create the DB Control Repository Objects and Configuration Files
For DB Control 10.1.x, run the command: /bin/emcaFor DB Control 10.2.x, run the command: bin/emca -config dbcontrol db -repos create


Warning: this command puts the database in Quiesce Mode.
Please consult the Note 375946.1 Running EMCA Results in Database quiesce And No
New Connections or Operations Can Be Performed During the DB Control Repository Creation


C. Recreate/ReConfig DB Control
Option 1. Recreate the DB Control Configuration Files only (leave Repository intact)
Option 2. Recreate the DB Control Configuration Files and Repository

In 10.2, the EMCA commands can be used to reconfigure the existing installs without removing them first.

Option 1. Recreate the DB Control Configuration Files only (leave Repository intact):
For DB Control 10.2.x, run the command:
/bin/emca -config dbcontrol db

Option 2. Recreate the DB Control Configuration Files and Repository
For DB Control 10.2.x, run the command:
/bin/emca -config dbcontrol db -repos recreate


Warning: this command puts the database in Quiesce Mode.
Please consult the Note 375946.1 Running EMCA Results in Database quiesce And No
New Connections or Operations Can Be Performed During the DB Control Repository Creation

For additional information on EMCA commandline options, please see the Oracle Enterprise Manager 10g Advanced Configuration Guide or see Note 330130.1 Overview Of The EMCA Commands Available for DB Control 10.2

How to get the Portal Schema Password

Subject: How to get the Portal Schema Password of the Infrastructure database
Doc ID: 198800.1 Type: HOWTO
Modified Date : 16-JUN-2009 Status: PUBLISHED


Checked for relevance on 05-Apr-2007

Problem Description
-------------------
How to get the Portal schema password of the Infrastructure database?


Solution Description
--------------------
1. Start up the Oracle Internet Directory administration tool from
infrastructure machine.

At Unix prompt> $ORACLE_HOME/bin/oidadmin
Or
Windows: Start -> Programs -> 9iAS midtier home -> Integrated Management Tools -> Oracle Directory Manager

1.1. If you are using it for the first time, the following message will be
displayed:
"To use this too you must first connect to a Server.
Click on the OK Button to select a Server..."

1.2. Click the OK button.

1.3. Click on the Add button and type in the following:
Servername:
Port : 4032 (the default port for OID - without SSL).

Click on OK... and Select button.

2. To gain access to the Oracle Directory Manager Connect
User : cn=orcladmin
Password:
Server : host running Oracle Internet Directory and port number
where Oracle Internet Directory is listening

The default port for Oracle Internet Directory is 4032(without SSL)

3. Navigate the Single Sign-On schema (orasso)or Portal schema entry using the administration
tool (9.0.2.x).
=> cn=orcladmin@OID_hostname:OID_port
(for example: cn=orcladmin@infra.acme.com:4032)
=> Entry Management
=> cn=OracleContext
=> cn=Products
=> cn=IAS
=> cn=Infrastructure Databases
=> ReferenceName=:
=> ResourceName=Portal

->For Portal 9.0.4.x, 10.1.2.x and 10.1.4.x this is under:
=> cn=orcladmin@OID_hostname:OID_port
(for example: cn=orcladmin@infra.acme.com:4032)
=> Entry Management
=> cn=OracleContext
=> cn=Products
=> cn=IAS
=> cn=IAS Infrastructure Databases
=> orclReferenceName=asdb.
=> OrclResourceName=PORTAL

Navigate backwards to that in oidadmin gui to look for the orclpasswordattribute.

->Portal 10.1.4.x. You will need to use the ldapsearch command line utility to retrieve the password.
The syntax is similar to this:
ldapsearch -h -p -D cn=orcladmin -w -b "cn=IAS,cn=Products,cn=OracleContext" -s sub -v OrclresourceName=PORTAL| grep orclpasswordattribute

4. Click the above entry and look for the orclpasswordattribute attribute value
on the right panel. This value is the password for the Portal schema.

Oracle Maximum Availability Architecture (MAA)

Oracle Maximum Availability Architecture (MAA): "Oracle Maximum Availability Architecture"