Archive

Author Archive

Common Database Wait Events

Below is the document containing the most common database waits seen during the day to day activity of an Oracle DBA. It contains the possible causes and different ways to alleviate the wait.

Common Database waits

Happy reading!

-Anand

Categories: Oracle Database Tags:

Log file location in Oracle application 11i

Below is the ready reckoner for the location of various log files in Oracle apps 11i. This is very much helpful when troubleshooting any issue

  • Database Tier
    • Alert Log File – $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log
    • Trace file – $ORACLE_HOME/admin/SID_Hostname/udump
    • Autoconfig Log file – $ORACLE_HOME/appsutil/log/SID_Hostname/DDMMTime/adconfig.log
  • Application Tier
    • Autoconfig Log file             – $APPL_TOP/admin/SID_Hostname/log/DDMMTime/adconfig.log
    • Start/Stop script log files  – $COMMON_TOP/admin/log/CONTEXT_NAME/<mmddhhmi.log>
    • Concurrent log file              – $APPLCSF/$APPLLOG
    • Patch Log file                      – $APPL_TOP/admin/<SID>/log
    • Worker Log file                   – $APPL_TOP/admin/<SID>/log
    • Apache Log file                   – $IAS_ORACLE_HOME/Apache/Apache/logs/
    • Jserv log File                       – $IAS_ORACLE_HOME/Apache/Jserv/logs/
    • JVM Log File                       – $IAS_ORACLE_HOME/Apache/Jserv/logs/jvm
    • ICM log file                          – $APPLCSF/$APPLLOG/
    • Workflow Mailer log file      – $APPLCSF/$APPLLOG/FNDCPGSC*.txt
Categories: Oracle Apps Tags: ,

Microsoft AD and OID synchronization not happening

Support team reported an issue where they were not able to see some of the new users created in AD (Active Directory) flowing to OID (Oracle Internet Directory)
I started looking into the issue. I checked ODI synchronization services (odisrv process)

Logged into the infra tier and ran the command

$ $ORACLE_HOME/ldap/bin/ldapcheck

Checking Oracle Internet Directory Processes ...ALL

Process oidmon is Alive as PID 30596
Process oidldapd is Alive as PID 30603
Process oidldapd is Alive as PID 30609
Not Running ---- Process oidrepld
Process odisrv is Alive as PID 17381

then checked the ldap synchronization log ($ORACLE_HOME/ldap/oid/log/_.aud
and this showed last successful synchronization happened on 23rd Feb 2015
No Errors were seen in the ldap trace file, oidsrv log or oidmon log.
I looked into the highest COMMITTED USN in the AD

$ ldapsearch -h  -b "" -s base "objectclass=*" highestcommittedusn
highestCommittedUSN=464557938

And searched for highest committed USN from the DIP profile

$ ldapsearch -h  -p  -D "cn=orcladmin" -w  -b "cn=subscriber profile,cn=changelog subscriber,cn=oracle internet directory" -s sub "objectclass=*" > /tmp/PROD_DIP_Profile.txt

$ grep -i "orclodipcondirlastappliedchgnum" /tmp/PROD_DIP_Profile1.txt
$ orclodipcondirlastappliedchgnum=0
orclodipcondirlastappliedchgnum=455736457
orclodipcondirlastappliedchgnum=0
orclodipcondirlastappliedchgnum=0
orclodipcondirlastappliedchgnum=0
orclodipcondirlastappliedchgnum=0
orclodipcondirlastappliedchgnum=0
orclodipcondirlastappliedchgnum=0

So there was huge difference between the highestCommittedUSN between AD and that in the DIPprofile.
This is the reason Synchronization got stopped.

I followed below steps to make this synchronization work again.
1) Stop the odisrv process
$ oidctl connect= server=odisrv instance=1 stop
2) Disable the DIP profile
– Set the DISPLAY
– Open a VNC sessions and run the below command
$ORACLE_HOME/bin/oidadmin

Login to oid console with user as “cn=orcladmin”
Go to Integration Server
Select the profile which should be something like <OID_SID>_PortalImport
Edit the same and make it “Disable”

picture1

 

 

 

 

 

 

 

 




You can check this by running the below command on Infra Node.
ldapsearch –h [oid_hostname with fully qualified domain] –p 389 –D “cn=orcladmin” –w –b “orclodipAgentName=xxxx_PortalImport,cn=subscriber profile ,cn=changelog subscriber,cn=oracle internet directory” –s base “objectclass=*” orclodipagentcontrol

3) update the Highest committed USN in the DIP profile with that obtained from AD
— Now again edit the same profile and go into ‘Status’ Tab
— Update the value against “Last Applied Change Number” with the value obtained above from AD

picture2

 

 

 

 

 

 

 

4) Enable the profile again
— Edit the Profile and make it enable

5) Move the old and existing odisrv log, odimon log, synchronization log ($ORACLE_INFRA_HOME/ldap/oid/log/*.aud and *.trc)
6) Start the odisrv process

$oidctl connect==[tns_alias] server=odisrv instance=1 configset=1 flags=”host=[oid_hostname with fully qualified domain] port=389″ start
7) Run “ldapcheck” command to see the odisrv process.

$ORACLE_HOME/ldap/bin/ldapcheck

Process oidmon is Alive as PID 28339
Process oidldapd is Alive as PID 28347
Process oidldapd is Alive as PID 28361
Not Running ---- Process oidrepld
Process odisrv is Alive as PID 28348

Now again search for Highest Committed USN in AD and in DIP profile and it matches.

This resolved the issue and synchronization resumed between Microsoft AD and OID.

File o7725967.tmp creation for FND_FILE failed

February 9, 2015 Leave a comment

Today support team sought a help for the issue they were encountering in the TEST instance.

Error as given by the support team


ErrBufORA-20100: File o7725967.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.

I tried a quick test to see if there is any issue with utl_file_dir


SQL>exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
BEGIN FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST'); END;

*
ERROR at line 1:
ORA-20100: File o7725970.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512: at "APPS.FND_FILE", line 517
ORA-06512: at line 1

Above test threw an error. Then I verified 2 more things

1) APPLPTMP directory should be the first one appearing when UTL_FILE_DIR parameter is queried – this looked Ok


SQL>show parameter utl_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /d05/apptest/common/temp, /usr
/tmp, /d12/oratest/product/9.2.
0/appsutil/outbound/TEST, /d05/
apptest/11.5/xxbb/4.0.0/edi/ou
t, /d05/appltest/11.5/xxdd/4.0.
0/US/utl_file_dir, /d05/appltest
t/common/admin/log/TEST_host_name
, /d05/appltest/common/admi
n/out/TEST_host_name, /d05/ap
pltest/common/admin/log/TEST_host_name,
/d12/oratest/product/
9.2.0/ccr/hosts/host_name/st
ate, /d05/appltest/11.5/xxcal/s
mart, /d05/appltest/11.5/xxcal/
bartender

2)Missing permissions on APPLPTMP directory – I found that the permission was not correct. I gave the permission (777) to the directory and this resolved the issue.

To confirm, I ran the same procedure again and it returned the expected result.


SQL>exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15

Other probable causes of error

3)APPLPTMP directory is missing &

4)APPLPTMP directory pointing to wrong location

 

Hope this helps.

Categories: Oracle Apps

PASSWORD column NULL in DBA_USERS from 11g onwards

December 16, 2014 Leave a comment

I got a request from support team to reset the DBA user password as they were getting some error due to password expiration.
However support person was not aware of the password.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
  2  from dba_users
  3  where USERNAME = 'PEOPLE';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRY_DATE
---------------
PEOPLE
EXPIRED
03-NOV-14

Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user’s password and restore it to its original value without actually knowing it.
The command:

ALTER USER IDENTIFIED BY VALUES ‘F28740221A2D9A70’;

it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null … so where are we supposed to take this hashed value from?.

SQL> SELECT USERNAME, PASSWORD
2 FROM DBA_USERS
3 WHERE USERNAME='PEOPLE';

USERNAME PASSWORD
--------------- ------------------------------
PEOPLE 

Starting with Oracle 11g, there is a view called SYS.USER$ which stores the PASSWORD, the way it was stored prior to 11g.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you login with “SYS” account, you can still apply the conventional (prior to 11g method) to temporarily reset the password, and still have access to the hashed password.

SQL> SELECT NAME, PASSWORD
FROM SYS.USER$
WHERE NAME = 'PEOPLE'   2    3  ;

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
PEOPLE
F28740221A2D9A70

And the command used to reset the password

SQL> alter user PEOPLE identified by values 'F28740221A2D9A70';

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
  2  from dba_users
  3  where USERNAME = 'PEOPLE';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRY_DATE
---------------
PEOPLE
OPEN
14-JUN-15
Categories: Oracle Database Tags: ,

How to change “orcladmin” user password when it is expired

December 9, 2014 Leave a comment
  • Login to $ORACLE_HOME/bin (Infra Home)
    • Make sure vnc process is running and DISPLAY variable is configured to the VNC port
  • export DISPLAY=<server_name>:<port_no>
  • Invoke the script -oidamin
    • $ ./oidadmin
  • Now login to the GUI (onVNC) with the following credential
    • User = “cn=orcladmin”
    • Password = default it is the same as that of “ias_admin”
  • Once logged in, navigate as below
  • Entry Management
    • cn=OracleContext
      • dc=com
      • dc=abc <domain_name>
        • cn=Users
        • cn=<orcladmin>
  • Click on cn=<orcladmin>
  • Scroll to the bottom in “Properties” tab
  • Look for the attribute “userpassword”
  • Update the value with the password and then click on “Apply”
Categories: Oracle Portal Tags:

Usage of various pgp command

January 30, 2014 Leave a comment

Recently I got a request from a bank where the customer transmits some of the files. Bank was upgrading and changing their target server and hence we need to accommodate certain changes on our side to do the testing before the same is done in production environment.

Command to list the existing Key IDs on the keyring


appluser.DEV.pdc-xyz-ibm> /usr/local/bin/pgp -kv
Pretty Good Privacy(tm) Version 6.5.8
(c) 1999 Network Associates Inc.
Uses the RSAREF(tm) Toolkit, which is copyright RSA Data Security, Inc.
Export of this software may be restricted by the U.S. government.
Type bits      keyID      Date       User ID
RSA  1024      ********** 2009/07/02 *** DEFAULT SIGNING KEY ***
                                     XY_ABCBANK_TEST
RSA  1024      ********** 2009/07/06 abc pqr <a href="mailto:abc.pqr@domain.com">abc.pqr@domain.com</a>>
RSA  2048/2048 ********** 2012/02/21 expires 2014/05/01
                                      File_Transfer_Services <<a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>
RSA  2048/2048 ********** 2009/03/04 *** KEY EXPIRED ***
                                      ABCBANK_ECS_QA_2011 <<a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>
4 matching keys found.

Bank sends the new public key from their TEST/QA server. This is done so as to do point to point testing from our DEV/QA to Bank DEV/QA server.

Once the new public key is recived,this needs to be added into the keyring using below command


/usr/local/bin/pgp -ka <BANK_PUBLIC_KEY.ASC>
Pretty Good Privacy(tm) Version 6.5.8
(c) 1999 Network Associates Inc.
Uses the RSAREF(tm) Toolkit, which is copyright RSA Data Security, Inc.
Export of this software may be restricted by the U.S. government.
Looking for new keys...
RSA  2048/2048 ************ 2012/02/21 File_Transfer_Services <<a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>
sig?           ************             (Unknown signator, can't be checked)

keyfile contains 1 new keys. Add these keys to keyring ? (Y/n) Y
New userid: "File_Transfer_Services <a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>".
New signature from keyID ********* on userid File_Transfer_Services <a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>

Keyfile contains:
   1 new key(s)
   1 new signatures(s)
   1 new user ID(s)
Summary of changes :

New userid: "File_Transfer_Services <a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>".
New signature from keyID ********** on userid File_Transfer_Services <a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>

Added :
   1 new key(s)
   1 new signatures(s)
   1 new user ID(s)

Comand to extract the key for the user XY_ABCBANK_TEST (copies ID’s key to keyfile from keyring )

XY_ABCBANK_TEST is the user ID signing the encrypted file


/usr/local/bin/pgp -kx XY_ABCBANK_TEST /tmp/pgp_key_XY_Nov2013.asc

Getting a warning message while encrypting and signing the file


Key for user ID: File_Transfer_Services <<a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>
2048-bit RSA key, Key ID ******, created 2012/02/21, expires 2014/05/01
WARNING:  Because this public key is not certified with a trusted
signature, it is not known with high confidence that this public key
actually belongs to: "File_Transfer_Services <<a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>".

Now to overcome the error, we need to sign the public key received from 3rd party using our secret key on key ring

pgp -ks <HER_USERID>-u <YOUR_USERID>keyring


appluser.DEV.pdc-xyz-ibm> pgp -ks File_Transfer_Services -u XY_ABCBANK_TEST_2013
Pretty Good Privacy(tm) Version 6.5.8
(c) 1999 Network Associates Inc.
Uses the RSAREF(tm) Toolkit, which is copyright RSA Data Security, Inc.
Export of this software may be restricted by the U.S. government.
Key for user ID: File_Transfer_Services <<a href="mailto:connectivity.services.engineering@abc.com">connectivity.services.engineering@abc.com</a>>
2048-bit RSA key, Key ID ***********, created 2012/02/21, expires 2014/05/01
          Key fingerprint =  XY PQ 86 14 PO E9 9F 11  9Q E7 2G 80 69 75 U8 37  F6 8S 7C C3
READ CAREFULLY:  Based on your own direct first-hand knowledge, are
you absolutely certain that you are prepared to solemnly certify that
the above public key actually belongs to the user specified by the
above user ID (y/N)? y

You need a pass phrase to unlock your secret key.
Key for user ID "XY_ABCBANK_TEST_2013"
Key does not have a passphrase.

Attach a regular expression to this signature, or
press enter for none:
appluser.DEV.pdc-xyz-ibm>

Reference URL for other various PGP command summary

http://www.dsj.net/pgp/pgphelp.html

http://www.cs.wm.edu/pgp.html

-Anand

Categories: Oracle Apps

adstpall.sh on Web tier and giving “Timeout specified in context file: 100 second(s)” error

January 30, 2014 Leave a comment

Today our development team complained of ‘Development’ instance of Oracle 11 Ebiz is not accessible. Even the login page is not loading.

I tried to login and got the login page but later on after keying the login credential, it was taking extremely long time to open and later on threw ‘page not found’ error. I thought of bouncing apache.

When I tried to bounce apache, I got below error.


ORACLE error 604 in afpodbinit

Cause:  afpodbinit failed due to ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "APPS.FND_GLOBAL", line 101
ORA-06512: at "APPS.FND_GLOBAL",.

The SQL statement being executed at the time of the error was: begin fnd_global.bless_next_init('FND_PERMIT_0000'); fnd_global.initialize(:session_id, :user_id, :resp_id, :resp_appl_id, :security_group_id, :site_id, :login_id, :conc_login_id, :prog_appl_id, :conc_program_id, :conc_request_id, :conc_priority_request, :form_id, :form_application_id, :conc_process_id, :conc_queue_id, :queue_appl_id, :server_id); fnd_profile.put('ORG_ID', :org_id); fnd_profile.put('MFG_ORGANIZATION_ID', :mfg_org_id); fnd_profile.put('MFG_CHART_OF_ACCOUNTS_ID', :coa); fnd_profile.put('APPS_MAINTENANCE_MODE', :amm); end; and was executed from the file &ERRFILE.

Apache Web Server Listener :httpd ( pid 17197 ) is running.
Stopping Apache Web Server Listener (dedicated HTTP) ...

Oracle error -6508: ORA-01013: user requested cancel of current operation
ORA-06508: PL/SQL: could not find program unit being called
has been detected in FND_GLOBAL.INITIALIZE.

Oracle error -6508: ORA-01013: user requested cancel of current operation
ORA-06508: PL/SQL: could not find program unit being called
has been detected in FND_GLOBAL.INITIALIZE.
Apache Web Server Listener (PLSQL) :httpd ( pid 17521 ) is running.
Stopping Apache Web Server Listener (dedicated PLSQL) ...
Oracle error -6508: ORA-01013: user requested cancel of current operation
ORA-06508: PL/SQL: could not find program unit being called
has been detected in FND_GLOBAL.INITIALIZE.

adapcctl.sh: exiting with status 0

Later on when I tried to run adstpall.sh on Web tier, I got following in the error.


Setting service Oracle Apache Server DEV_pdc-al-lnx12 to mode 3
Executing service control script:
/d01/wfmdev/common/admin/scripts/DEV_pdc-al-lnx12/adapcctl.sh stop
Timeout specified in context file: 100 second(s)

script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception

adapcctl.sh version 115.50

Later on when I looked into the database, I got following latches/library pin waits and hence decided to go for database bounce.


    1    71 library cache lock            JDBC Thin Client     APPS        ############ 5.0440E+17        302   1117
    1    87 library cache lock               ?  @pdc-al-lnx12  APPS        ############ 5.0440E+17        302   1084
                                          (TNS V1-V3)

    1    88 library cache lock                                 APPS        ############ 5.0440E+17        302    817
    1   104 library cache lock            JDBC Thin Client     APPS        ############ 5.0440E+17        302    520
    1   132 library cache lock                                 APPS        ############ 5.0440E+17        302   1169
    1   150 library cache lock               ?  @pdc-al-lnx12  APPS        ############ 5.0440E+17        302    817
                                          (TNS V1-V3)

    1   164 library cache lock            JDBC Thin Client     APPS        ############ 5.0440E+17        302   1117
    1   233 library cache lock               ?  @pdc-al-lnx12  APPS        ############ 5.0440E+17        302    189
                                          (TNS V1-V3)

    1   142 library cache pin                ?  @pdc-al-lnx12  APPS        ############ 5.0440E+17        302    219
                                          (TNS V1-V3)

    1   171 library cache pin                ?  @pdc-al-lnx12  APPS        ############ 5.0440E+17        202     11

DB bounce helped to resolve the issue. ‘adstpall.sh’ and ‘adstrtal.sh’ executed successfully. Application was very much accessible.

-Anand

 

Categories: Oracle Apps

ORA-01017 error while accessing DB link from 9i to 11g

Today I faced a strange issue. Support team needed to extract some data from PeopleSoft database (11.2.0.1) via oracle ERP database (9.2.0.6). A database links already exists between the 2 databases but when SELECT query is fired, we were getting below error.

SQL>SELECT * FROM <a href="mailto:apps.PS_ORACLE_DATA_AL@PEOPLESOFT.WORLD">PS_ORACLE_DATA_AL</a>;
SELECT * FROM <a href="mailto:apps.PS_ORACLE_DATA_AL@PEOPLESOFT.WORLD">PS_ORACLE_DATA_AL</a>
                                     *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from PEOPLESOFT.WORLD

Elapsed: 00:00:01.62

and I was pretty sure that the login credential for the remote database is perfectly correct. Even I was able to connect remote DB using the same credential.

SQL>conn <a href="mailto:EMP_EXTR@Remote_DB">EMP_EXTR@Remote_DB</a>
Enter password: *********
Connected.
and ses.sid=(select sid from v$mystat where rownum=1)
                             *
ERROR at line 7:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.57
SQL>show user
USER is "EMP_EXTR"
SQL>

I tried dropping and then recreating the DB link but still got the same error. I searched the error in Metalink got one direct HIT – Note ID 473716.1.

This asks to create the database link with ‘double quotes’ around the password (IDENTIFIED by clause). I dropped and recreated the database link as advised. This is due to the fact that user passwords are case-sensitive by default from 11gR1 onwards.

This resolved the error.

SQL>select sysdate from <a href="mailto:dual@PEOPLESOFT.WORLD">dual@PEOPLESOFT.WORLD</a>;

SYSDATE
---------
25-JUL-13

Elapsed: 00:00:00.73
SQL>

-Anand

 

Categories: Oracle Database

Understanding Database locks using v$lock

Many times I have seen either user or development team complaining of their DML statements running long or hung. And it is most of the time perceived as caused by slow database.

But every time, I don’t see any contention from database side. It is the database lock that has been causing the ‘hung’ behaviour. There are ways to investigate the database locks and the associated details.

I run below query on V$LOCK and see the following details

<pre>SQL> SELECT sid,
                                TYPE,
                                DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
        DECODE( request, 0, 'NO', 'YES' ) WAITER,
        decode(LMODE,1,'    ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') lmode,
                                 decode(REQUEST,1,'    ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') request,
                                TRUNC(CTIME/60) MIN ,
                                ID1,
                                ID2,
        block
                        FROM  v$lock
      where request > 0 OR block =1;</pre>
       SID TY BLO WAI LMOD REQU        MIN        ID1        ID2      BLOCK
---------- -- --- --- ---- ---- ---------- ---------- ---------- ----------
       859 TX NO  YES NONE X             0    6094884    1244082          0
      1839 TX NO  YES NONE X             0    5242904    1939567          0
      1926 TX YES NO  X    NONE          1    5242904    1939567          1
      3082 TX YES NO  X    NONE          0    6094884    1244082          1

Any session with block=1 is the blocker. Here session with SID = 1926 is blocking other session as it has block=1. Now we need to find this blocker session is blocking which other sessions. To find this, we need to compare the ID1 and ID2 column value. Here we can see ID1 and ID2 for SID=1926 is matching the ID1 and ID2 of SID=1839 . Hence SID=1839 is the blocked session. Any session which is blocked will not be able to process their request and hence REQUEST column for those blocked session will have value > 0.

Let us describe V$LOCK to understand other interested columns.

SQL> desc v$lock
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 KADDR                                                          RAW(8)
 SID                                                            NUMBER
 TYPE                                                           VARCHAR2(2)
 ID1                                                            NUMBER
 ID2                                                            NUMBER
 LMODE                                                          NUMBER
 REQUEST                                                        NUMBER
 CTIME                                                          NUMBER
 BLOCK                                                          NUMBER

ID1 and ID2 – These are rollback segment and transaction table entries respectively. When a blocker session acquired the lock, it posted an entry for rollback segment and transaction table. And when another session (blocked sessions) tried to read the same block, Oracle tried to generate the “consistent Read” image by reading the rollback segment but since there was an exclusive lock by the ‘blocker’ session, it has to wait. Therefore we same value of ID1 and ID2 for the blocker and the blocked session.

LMODE – This is the mode of lock acquired. Value ranges from 1 t0 6. 1 (null), 2 (row share) , 3 (row exclusive) , 4 (share) , 5 (share row exclusive) and 6 (exclusive). ‘Exclusive’ is the most restrictive kind of lock.

TYPE – This tells us the kind of lock put on the table where the data is locked. If a session has to change the data in a table, it has to acquire ‘transactional lock (TX)’

REQUEST – This column represents the lock mode requested by the blocking session. Any session which is being blocked will always some value updated against this column. In the query output shown above, we can see all the blocked sessions have a value of 5 against the REQUEST column. This means, all these sessions have requested for a lock mode 5 and waiting for the lock being held by the blocker session.

If the ‘Type’ of lock is ‘TM’ – which is DML level lock and generally acquired in row exclusive (LMODE = 3) mode. This will not let any DDL happening on the locked table. When the TYPE = TM, ID1 corresponds to the object ID. We can query dba_objects and find the object name and type against the given object ID.

We can find the row which is being blocked by the blocker session using v$session.

  • First we need to find value of 4 columns from v$session – 1)ROW_WAIT_OBJ# 2)ROW_WAIT_FILE# 3)ROW_WAIT_BLOCK# & 4)ROW_WAIT_ROW#
  • Then using these 4 values and using DBMS_ROWID packge, we can generate the ROWID of the blocked row
    • select dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) where sid = <BLOCKED_SESSION>

Good understanding of database lock helps in troubleshooting and investigating the same. Hope it helps to you as well.

-Anand

Categories: Oracle Database