Saturday, June 26, 2010

Apex 4.0 is released

After really huge interest for this new version, Oracle Application Express 4.0 is finally published.




The new version can be downloaded from here.

Hope you'll find more joy and excitement working with it because it offers really many new features...

Cheers!

Monday, June 21, 2010

Linux tape commands (DBA summary)

Every DBA sooner or later will have a chance to work with tape drives. For that Linux has several commands.

Because I always search Internet for some command, this post is made just to be mine backup comprehensive place for all important tape commands in Linux.

Save to tape

1. Rewind
mt -f /dev/nst0 rewind

2. Move to end of last block
mt -f /dev/st0 eod

3. Save and compress content to tape
tar cvf /dev/nst0 ./bg*.gz

4. Rewind
mt -f /dev/nst0 rewind

Take the tape to treasury

Eject tape
mt -f /dev/st0 eject

Restore from tape

1. Rewind
mt -f /dev/nst0 rewind

2. Move to block from which you want to restore (0,1,2,3 etc.- number of block)
mt -f /dev/nst0 fsf 1 
"1" is second block, because it starts from 0.

3. Restore from tape to current directory
tar xvf /dev/nst0

The end

Maybe someone find them useful too.

Cheers!

Wednesday, June 16, 2010

Auditing database


In mine previous article Audit DDL operations in database I have explained how to implement custom DDL auditing on database.

Much wider approach is implementation of original Oracle auditing. Oracle auditing is very wide action that can monitor any kind of database operation. The basic idea is to define objects and levels of auditing and then record the information to plain oracle table aud$, placed in sys schema. There are many official Oracle documents that explains how to setup auditing very briefly, so I'll skip that part.

But, regardless this is pretty straight path implementation, in this post I'll explain some not so obvious problems and paths how to solve them.

Move sys.aud$ from SYSTEM tablespace

Because sys.aud$ table will have huge number of inserts, (it's grow may rise from 100MB - 1 GB per day) it is advisable to place this table outside SYSTEM tablespace, where it initially resides.

With this action DBA prevent huge fragmentation of very vital tablespace-SYSTEM.

Detailed steps that describe this action can be found on Oracle Metalink Note 1019377.6 - "Script to move SYS.AUD$ table out of SYSTEM tablespace"

aud$_arch table (archive table for for sys.aud$ records)

Another reasonable approach is to move old auditing data from sys.aud$ to some archive table, which will not be in sys schema. This is more then recommended step to avoid holding to many data in original sys.aud$ table and because content of aud$_arch table may be easily exported and then purged.

To achieve that, create a copy of original sys.aud$ table in non sys schema as:
create table tools.aud$_arch tablespace TOOLS as (select * from sys.aud$ where 1=0);
As you see aud$_arch table is initially created in tools schema. If you use sys.aud$ table then expdp is not an option but old fashion exp. Because truncate is much faster then delete, this operation can not be done on sys.aud$ table.

Placing aud$_arch table in TOOLS tablespace you achieve the same benefit as with sys.aud$ table!

This archive table (aud$_arch) will be filled with additional procedure, which should be run on daily basis:
CREATE OR REPLACE PROCEDURE archive_aud$ (p_days number)
   authid current_user
IS
  purge_date date;
BEGIN
  purge_date := sysdate-p_days;
  insert into aud$_arch (select * from sys.aud$ a where a.ntimestamp# < purge_date);
  delete from sys.aud$ where ntimestamp# < purge_date;
  commit;
EXCEPTION
  WHEN OTHERS THEN
    rollback;
END archive_aud$;
/
With p_days parameter you may define number of days to be included in archiving. If you place this procedure in Oracle database job on daily basis, p_days should be 1. Otherwise define as you need.

Create directory (for expdp action)

If you'll use expdp method, then you probably know that you must define directory where export data will be saved.
conn / as sysdba
create or replace directory AUDITING as 'your path...';
grant read, write on directory AUDITING to tools;
I do not want to discuss why expdp is better option for me but just to mention that for ordinary exp you do not need this step.

OS part

From Linux side, final part is to call bash script, whose primary task is to export tools.aud$_arch records to flat file and then to truncate it's content. This is done through this script:
#!/bin/bash

DATUM=`date '+%Y%m%d'`; export DATUM
VRIJEME=`date '+%H%M%S'`; export VRIJEME

# assign Oracle environment 
. /home/oracle/PROD.env 

SQL_LOG_FILE="AUD_ARCH_${DATUM}-${VRIJEME}.log"

EXPORT_DIR=`echo "select 'xX '||DIRECTORY_PATH from dba_directories where owner='SYS' and DIRECTORY_NAME='AUDITING';" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
echo "export dir=" ${EXPORT_DIR} >> ${SQL_LOG_FILE}
echo ' ' >> ${SQL_LOG_FILE}

DATE_FROM=`echo "select 'xX '||MIN(ntimestamp#) from tools.aud\\$_arch;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
echo "date from=" ${DATE_FROM} >> ${SQL_LOG_FILE}
echo ' ' >> ${SQL_LOG_FILE}

DATE_TO=`echo "select 'xX '||MAX(ntimestamp#) from tools.aud\\$_arch;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
echo "date to=" ${DATE_TO} >> ${SQL_LOG_FILE}
echo ' ' >> ${SQL_LOG_FILE}

TAR_FILE="${EXPORT_DIR}/tools_aud_arch_${DATE_FROM}-${DATE_TO}.tgz"

#Version for expdp
DUMP_FILE="tools_aud_arch_${DATE_FROM}-${DATE_TO}.dmp"
LOG_FILE="tools_aud_arch_${DATE_FROM}-${DATE_TO}.log"
expdp \"/ as sysdba\" TABLES='TOOLS.AUD$_ARCH' CONTENT=ALL DIRECTORY=AUDITING DUMPFILE=${DUMP_FILE} LOGFILE=${LOG_FILE}
retvalue=$?

echo "expdp result:" $retvalue >> ${SQL_LOG_FILE}
echo "expdp result:" $retvalue

if [ $retvalue -ne 0 ]; then
  echo "dump has errors-tar will not be performed!" >> ${SQL_LOG_FILE}
  echo "dump has errors-tar will not be performed!" 
  exit $retvalue
fi

sqlplus -s "/ as sysdba"<EOF!
spool ${SQL_LOG_FILE} append
PROMPT truncate table aud\$_arch ...
truncate table TOOLS.AUD\$_ARCH drop storage;
PROMPT truncate complete
spool off;
exit
EOF!

tar czf ${TAR_FILE} ${EXPORT_DIR}/${LOG_FILE} ${EXPORT_DIR}/${DUMP_FILE}
retvalue=$?
if [ $retvalue -eq 0 ]; then
  chmod 444 ${TAR_FILE}
  rm -f ${LOG_FILE} ${DUMP_FILE}
  echo "Process completed succesfully!"
else
  echo "tar failed with retval=$retvalue"
fi 
Two important things:
  1. Do not run when archive_aud$ database job is active
  2. Run only from one node (if using RAC)
This script, according AUDITING directory definition, defines all other parameters automatically. After exporting data, it truncate table aud$_arch and then tar file to reduce size on file system. Finally it set RO attributes to created tar file to ensure accidental deletes or changes.

Script create log files in same directory as AUDITING defined, so you can monitor its execution:
export dir= /oracle/export/sys_aud
 
date from= 19.05.10
 
date to= 14.06.10
 
expdp result: 0
truncate table aud$_arch ...

Table truncated.

truncate complete
Keep in mind that "expdp result: 0" mean that expdp command finished regularly, not number of exported records!

To automatize the whole process, place it's execution in crontab, on 14 days window policy:
# export tools.aud$_arch table to file and purging records
0 4 1,15 * * /oracle/export/sys_aud/export_aud_arch.sh
Because exports may be initially pretty big (~ 5-10GB and even larger-depends on auditing level and window period) it is recommended to ensure larger storage for that operation.

Import

Lately, if you want to analyze some suspicion activities in database, easy import data with impdp to aud$_arch table. Do not forget to export current data from aud$_arch to file (by manually call export_aud_arch.sh) before import any data into table.

The End

For those who want advanced features in auditing, they can refer to Oracle Metalink Note 731908.1 - "New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information".

Cheers!

Monday, June 14, 2010

Audit DDL operations in database


DDL operations present real danger for any database.

Catastrophic commands like "drop trigger" or "drop table" are noticed immediately if they succeed to complete. But some other operations like altering/adding/dropping indexes or altering tables, are not easy to determine immediately after they happened. The worse of all is that it's circumstances are not obvious until they comes into play like downgrade database performance so much that the whole system suffer.

Because DDL are usually performed through scripts where many commands are executed sequentially, if you do not have straight error level checking through whenever sqlerror sqlplus directive, some errors that happened might not be catch and stays hidden with poor chance to notice them.

If beside DBA, there are many other users that has rights to perform any DDL operation, it is more then welcome to have history of such an activity. This is the best way to understand quickly what went wrong.

For such a cases DDL monitoring is right approach.

The solution

The solution is to implement DDL monitoring tool that will record all successful DDL operations in the database. This done through several parts:
  1. AUDIT_DDL_LOG table which will hold DDL information that were processed
  2. AUDIT_DDL_LOG_ARCH table that will hold archive DDL information
  3. Database trigger which will fill AUDIT_DDL_LOG table
  4. Procedure ARCHIVE_AUDIT_DDL_LOG which will move records from AUDIT_DDL_LOG to AUDIT_DDL_LOG_ARCH table
  5. Database job which will fire procedure ARCHIVE_AUDIT_DDL_LOG

AUDIT_DDL_LOG and AUDIT_DDL_LOG_ARCH tables

Table structure is:
CREATE SEQUENCE AUDIT_DDL_LOG_SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  CYCLE
  CACHE 100
  ;

CREATE TABLE AUDIT_DDL_LOG (
  DDL_DATE     DATE,
  USER_NAME    VARCHAR2(30 BYTE),
  SESSION_ID   NUMBER(8),
  SID          NUMBER(8),
  DDL_TYPE     VARCHAR2(30 BYTE),
  OBJECT_TYPE  VARCHAR2(18 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(128 BYTE),
  ID           INTEGER
)
TABLESPACE TOOLS
PCTUSED    0
PCTFREE    0
;

CREATE UNIQUE INDEX AUDIT_DDL_LOG_UQX ON AUDIT_DDL_LOG
  (ID)
LOGGING
TABLESPACE TOOLS
PCTFREE    0
INITRANS   2
MAXTRANS   255
;


ALTER TABLE AUDIT_DDL_LOG ADD (
  CONSTRAINT AUDIT_DDL_LOG_PK
  PRIMARY KEY (ID)
  USING INDEX AUDIT_DDL_LOG_UQX)
;

-- GRANT SELECT ON AUDIT_DDL_LOG TO POWER_USER;
With granting select to POWER_USER role (now is commented), DBA allow that any user that own that role might look in the table for content that interest him/her. Because POWER_ROLE has user that belong to developers, DBA or any non "end user" group, he/she can alone check the result of scripts that was run under his session.

Because AUDIT_DDL_LOG is supposed to be table that holds current data, in a case of to many DDL's it might suffer of size problem.
This is why on regular time base I move old records to AUDIT_DDL_LOG_ARCH table.
CREATE TABLE AUDIT_DDL_LOG_ARCH(
  DDL_DATE     DATE,
  USER_NAME    VARCHAR2(30 BYTE),
  SESSION_ID   NUMBER(8),
  SID          NUMBER(8),
  DDL_TYPE     VARCHAR2(30 BYTE),
  OBJECT_TYPE  VARCHAR2(18 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(128 BYTE),
  ID           INTEGER
)
TABLESPACE TOOLS
PCTUSED    0
PCTFREE    0
COMPRESS 
;
Frequency of deleting records from AUDIT_DDL_LOG_ARCH (too old DDL are really not important) is totally customer based policy and have to be defined as it needs.

Database trigger

Because this trigger is fired when DDL statement completes (AFTER DDL), it's primary task is to fill AUDIT_DDL_LOG table with information about execution. If DDL statement fail, trigger will not be fired.
This trigger is intentionally placed in tools schema (not in sys!) because when you export/import database, sys objects are gone. However this always withdrawn additional security settings for owner.
CREATE OR REPLACE TRIGGER TOOLS.audit_ddl_trigger
/* $Header: audit_ddl_trigger.tgb 1.00 10/01/2007 14:54 damirv $ */

/*-------------------------------------------------------------------------------------------------------------------- 
 NAME    : audit_ddl_trigger
 PURPOSE : Log any DDL statement in "audit_ddl_log" table

 Date    : 01.10.2007.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : 

 Changes (DD.MM.YYYY, Name, CR/TR#):
-------------------------------------------------------------------------------------------------------------------- */
AFTER DDL
ON DATABASE
DECLARE
  s_sess audit_ddl_log.session_id%TYPE;
  s_sid audit_ddl_log.sid%TYPE;
  s_seq INTEGER;
BEGIN
  IF (ora_dict_obj_name != 'ALRT_PATH1' AND
      ora_dict_obj_name NOT LIKE 'EUL4%' AND
      ora_dict_obj_name NOT LIKE 'GL_INTERFACE%' AND
      ora_dict_obj_name NOT LIKE 'GL_POSTING%' AND
      ora_dict_obj_name NOT LIKE 'QUEST%' AND
      ora_dict_obj_name NOT LIKE 'SYS_TEMP%'  AND
      ora_dict_obj_name NOT LIKE 'WF_LOCAL%' AND
      ora_dict_obj_name NOT LIKE 'WF_UR_%' AND
      ora_dict_obj_name NOT LIKE 'ORA_TEMP%'
      ) THEN
    s_sess := sys_context('USERENV','SESSIONID');
    select sid into s_sid from v$session where audsid = s_sess;
    select AUDIT_DDL_LOG_SEQ.nextval into s_seq from dual;
    insert into audit_ddl_log (
      ddl_date,
      user_name,
      session_id,
      sid,
      ddl_type,
      object_type,
      owner,
      object_name,
      id
    )
    VALUES (
      sysdate,
      ora_login_user,
      s_sess,
      s_sid,
      ora_sysevent,
      ora_dict_obj_type,
      ora_dict_obj_owner,
      ora_dict_obj_name,
      s_seq
    );
  END IF;
EXCEPTION
  WHEN others THEN
    null;
END;
/
In this database trigger I have excluded several objects that are used in Oracle eBS 11.5.10.x environment (temp objects) which logging was not important to me.

Procedure ARCHIVE_AUDIT_DDL_LOG

In mine case I allays wanted to have at least 14 days in main AUDIT_DDL_LOG table. With p_retention parameter you can adjust this. Keep in mind that for p_retention=0 all data will be moved to AUDIT_DDL_LOG_ARCH table.
CREATE OR REPLACE PROCEDURE archive_audit_ddl_log (p_retention in number default 14) IS
/*---------------------------------------------------------------------------------- 
 NAME    : archive_audit_ddl_log.prc
 PURPOSE : Move records from audit_ddl_log to audit_ddl_log_arch table

 Date    : 01.10.2007.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : p_retention define how much data stays in audit_ddl_log table

 Changes (DD.MM.YYYY, Name, CR/TR#):
-----------------------------------------------------------------------------------*/
DECLARE
  l_date date;
BEGIN
  l_date := sysdate-p_retention;
  insert into audit_ddl_log_arch (select * from AUDIT_DDL_LOG where DDL_DATE < l_date);
  delete from audit_ddl_log where DDL_DATE < l_date;
  commit;
EXCEPTION
  WHEN OTHERS THEN
    rollback;
END archive_audit_ddl_log;
/

Database job

The best way is to place archive_audit_ddl_log procedure in job which fires once a day. In this case job is fired every day at 1:01 AM.
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'archive_audit_ddl_log;'
,next_date => to_date('02.10.2007 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1+1/1440)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
Owner of this job should not be in any circumstance sys. There is no reason for that. In mine case I use for many tasks like that TOOLS schema.

The result

Here is part of AUDIT_DDL_LOG table content what is result of our trigger:

The end

As you see, this monitoring tool is pretty straight forward for implementation as well for use. From mine experience, something like that is worth to be implemented on any production database.

If ypu want to monitor unsuccessful DDL statements (those which were not completed because of any kind of error), then one way to achieve is to do this:
  1. Add another table whose records will be filled with similar trigger which fire "BEFORE DDL"
  2. Difference (SQL "MINUS") between those two tables would show unsuccessful DDL operations
Cheers!

Thursday, June 10, 2010

Oracle Configuration Manager (OCM)-incomplete database recover problem

The Oracle configuration manager (OCM) centralizes configuration information based on your Oracle technology stack. Oracle uses secure access to your configuration information to help you achieve problem avoidance, faster problem resolution, better system stability, and easier management of your Oracle systems. It's benefits are:
  • Faster problem resolution from integrating your configuration information into the service request flow providing Oracle Support the information they need real-time to resolve your problem quickly and efficiently.
  • Improved systems stability delivered through proactive advice & health checks driven by Oracle best practices and personalized to your system configuration.
  • Simplified configuration management from a single, comprehensive and personalized dashboard of configurations, projects and inventory.
To make that feature alive you have to have install portion of software (download from Oracle Metalink, current version is 10.3.3) on your ORACLE_HOME where database resides. OCM doesn't effect in any way work of database or host where installed.

The problem

After successful installation and period of correct use, I was forced to make incomplete (PIT) recover in database where OCM was active. Very soon after, on my Oracle Meatalink dashboard, I found out problems with OCM. The problem was that data collection was sent 2 weeks ago.


The analyze

Analyze part for that enclose three commands, which are here extracted because better readability:
$ORACLE_HOME/ccr/bin/emCCR status
$ORACLE_HOME/ccr/bin/emCCR collect
$ORACLE_HOME/ccr/bin/emCCR disable_target
Main point of running "$ORACLE_HOME/ccr/bin/emCCR disable_target" is not to disable any target but to see which targets are now active, so this is why you have to press ENTER to exit last command without changes.
Here is the output:
ORACLE_HOME/ccr/bin/emCCR status
[oracle PROD4@server4 ~]$ $ORACLE_HOME/ccr/bin/emCCR status
Oracle Configuration Manager - Release: 10.3.2.0.0 - Production
Copyright (c) 2005, 2009, Oracle and/or its affiliates.  All rights reserved.
------------------------------------------------------------------
Start Date               27-Apr-2010 14:15:03
Last Collection Time     17-May-2010 14:10:00
Next Collection Time     18-May-2010 14:10:00
Collection Frequency     Daily at 14:10
Collection Status        idle
Log Directory            /u01/PROD/proddb/10.2.0/ccr/hosts/server4/log
Registered At            26-Jan-2010 14:10:39
Automatic Update         On
Collector Mode           Connected
[oracle PROD4@server4 ~]$

========================================================================================
$ORACLE_HOME/ccr/bin/emCCR collect
[oracle PROD4@server4 ~]$ $ORACLE_HOME/ccr/bin/emCCR collect
Oracle Configuration Manager - Release: 10.3.2.0.0 - Production
Copyright (c) 2005, 2009, Oracle and/or its affiliates.  All rights reserved.
------------------------------------------------------------------
Collection and upload done.
[oracle PROD4@server4 ~]$

========================================================================================
$ORACLE_HOME/ccr/bin/emCCR disable_target
[oracle PROD4@server4 ~]$ $ORACLE_HOME/ccr/bin/emCCR disable_target
Oracle Configuration Manager - Release: 10.3.2.0.0 - Production
Copyright (c) 2005, 2009, Oracle and/or its affiliates.  All rights reserved.
------------------------------------------------------------------
No.        Category                     Target Name
0          Cluster                      CRSPROD
1          Host                         server4
2          Database Instance            PROD_CRSPROD_PROD4
3          Database Instance            PROD_CRSPROD_PROD3
4          Database Instance            PROD_CRSPROD_PROD2
5          Database Instance            PROD_CRSPROD_PROD1
6          Oracle Home                  OraPROD10g_home
7          Listener                     LISTENER_PROD4_ADMIN_server4
8          Listener                     LISTENER_PROD_IISPRODRAC4_server4
9          Oracle Configuration Manager Oracle Configuration Manager
10         Cluster Database             PROD_CRSPROD

Press Enter to exit the command.
Use Comma to separate multiple target numbers.
Enter the number(s) corresponding to the target(s) you wish to disable:

[oracle PROD4@server4 ~]$
Everything looks OK.

Then mine focus to look in "state directory and see dates of files:
ls -alt $ORACLE_HOME/ccr/state
If collectors are active file creation dates on any of files should be up to date .... and they were!

So I checked another thing, $ORACLE_HOME/ccr/hosts/* directories from all 4 nodes involved in RAC. Under this dirs you have to find hostname directory which holds all data in that $ORACLE_HOME. The most interesting part is log directory, where I found in sched.log, next content:
2010-04-22 23:30:04: Oracle Configuration Manager successfully started.
2010-05-26 14:05:11, [ERROR]: Failure while performing scheduled collection directive. Collector returned with following error: Error encountered attempting to upload to Oracle Configuration Manager content receiver
Error sending data [https://ccr.oracle.com]
As I said that my PIT recover was on 22th of May, I connect these two events in correlation. So, cause is confirmed.... now let me solve the damage.

The unsuccessful solution

In situations when emCCR is not working properly (I have such an situations before) mine approach was to re-instrument OCR. This is done through 2 straight forward steps.
  1. On all database instances for collections:


    cd $ORACLE_HOME/ccr/admin/scripts 
    ./installCCRSQL.sh collectconfig -s $ORACLE_SID
    
    Successfully installed collectconfig in the database with SID=PROD1.
    Successfully installed collectconfig in the database with SID=PROD2.
    Successfully installed collectconfig in the database with SID=PROD3.
    Successfully installed collectconfig in the database with SID=PROD4.
    
  2. And then only on one node, force a collection to be submitted to Metalink


    $ORACLE_HOME/ccr/bin/emCCR collect 
    
After 2 days of waiting, I realize that this approach didn't give me expected result, mine dashboard was still showing errors according old collection date.

The solution

Reading through OCR manuals I found out that drop/recreate approach would cause no problems in any data or performance, so this was mine solution. here are steps:
  1. as SYSDBA, from one node, remove the Oracle Configuration Manager user and the associated objects from the database
    cd $ORACLE_HOME/ccr/bin
    ./emCCR stop
    SQL> @/$ORACLE_HOME/ccr/admin/scripts/dropocm.sql;
    
  2. stop the Scheduler and remove the crontab entry
    $ORACLE_HOME/ccr/bin/deployPackages -d $ORACLE_HOME/ccr/inventory/core.jar
    
  3. Delete the ccr directories on all nodes
    rm -rf $ORACLE_HOME/ccr
    
  4. This step is optional, but I strongly advise it in that moment...download the latest OCM from My Oracle Support dashboard.
  5. As oracle user, extract the zip file into $ORACLE_HOME. This will create new $ORACLE_HOME/ccr directory
  6. Install OCM on all nodes as

    $ORACLE_HOME/ccr/bin/setupCCR -s CSI MOSaccount
    
    example

    $ORACLE_HOME/ccr/bin/setupCCR -s 12345678 your_mail@your_company.hr
    
  7. Instrument the database, only on node1

    $ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s $ORACLE_SID
    
  8. Force a collection and upload, from each node

    $ORACLE_HOME/ccr/bin/emCCR collect
    
And after some time (2 days or less) you'll see that OCM is collecting data as before.

The End

Why is incoplete recover a problem, was it only on this database version (10.2.0.4 PSU 3) ... OCM version ( I was on 10.3.2 before) ... I do not know.

But as recreating OCM is not harmful operation in any way, this is OK step if you are in trouble that nothing else helps or fixing some bugs inside OCM.

Cheers!

Wednesday, June 2, 2010

TNS_ADMIN, IFILE - replace oracle names server for free!

Oracle names server until several years ago, represented very nice and handy Oracle service which allows DBA to centralize and share any of Oracle service names infrastructure around the network.
Even thought there was some other benefits, onames server was not free and after all represented another service in infrastructure which DBA has to maintain. But benefit was more then obvious-centralized management of all oracle naming services so that change in just one place reflect changes to all clients immediately. For instance, if you move database from one server to another, DBA have to changed just one entry with namesctl and all users would access moved database like nothing happened. Pretty cool especially if your database is accessed by several hundreds of clients!
Because I do not want so to evoke past times, I want to share my experience how to achieve similar functionality but with no extra servers, payment or installation.

The Solution

Mine solution is based on two important resources that Oracle supports:
  1. existence of TNS_ADMIN environment variable (Linux or Windows), which represent pointer to directory where network configuration files are located
  2. existence of ifile entry in any oracle configuration file

TNS_ADMIN

To implement common TNS_ADMIN, you must create directory on some server that any client can access from the network. In that directory set read rights (to ensure unexpected modifications). If you look in previous picture, DBA would use the same server where Oracle names server was planned to be.
Let us suppose that this server has "pcdamir" name and created shared dir name is "tns_share". In that directory (\\pcdamir\tns_share) place tnsnames.ora and sqlnet.ora files that should be readable and valid to all the clients in network. Here is an example of one correct listing, looking from clients side:
E:\>dir \\pcdamir\tns_share
 Volume in drive \\pcdamir\tns_share has no label.
 Volume Serial Number is 084F-8D27

 Directory of \\pcdamir\tns_share

02.06.2010.  21:02    <dir>          .
02.06.2010.  21:02    <dir>          ..
17.05.2010.  21:35               231 sqlnet.ora
05.05.2010.  20:24               499 tnsnames.ora
               2 File(s)            730 bytes
               2 Dir(s)  11.702.665.216 bytes free

E:\>
Next step is to define on all client computers (just once!) TNS_ADMIN environment variable as
TNS_ADMIN=\\pcdamir\tns_share
In Windows it should be best to do that through system variables (so any user on that PC can use this settings) or in Linux as part of ".bash_profile" file.
With TNS_ADMIN you ensure that all clients will look in "\\pcdamir\tns_share" directory when looking for any Oracle network configuration entry.

Check from client side existence of TNS_ADMIN variable. Here is an example for Windows clients:
E:\>echo %TNS_ADMIN%
\\pcdamir\tns_share
E:\>

IFILE

If your all clients can use the same network settings, then you can forget about ifile option and you can start testing your "onames replacement". However, from mine experience, it is almost impossible to satisfy all the clients in network with same tnsnames.ora or sqlnet.ora configuration files. These unsatisfied clients are usually advanced users (developers, DBAs, IT managers etc.) which know what configuration files are and in many cases represent a fewer number of clients. Here is when ifile comes to play.
On "pcdamir" (where common share is defined) edit tnsnames.ora and sqlnet.ora and put in the end following entries:
tnsnames.ora
IFILE=c:\tns_private\tnsnames_private.ora
sqlnet.ora
IFILE=c:\tns_private\sqlnet_private.ora
Placed ifile represent soem kind of pointer to other file where some additional definition may be found. That definition may be:
  • new entry (for instance new tns_name which is needed only for that pc)
  • replacement for existing entry (tns_name with same value but different definition if user on this PC wants to have different value against all others)
As you see usage is pretty straightforward and allow any combination that you might need. As we said previously, that these entries are really needed for advanced users, so DBA should only sent to them some notes like:
  1. create directory c:\tns_private
  2. In that directory place (even empty ones!) tnsnames_private.ora and sqlnet_private.ora files
  3. In these files place entries that would satisfy your need for different configuration against other (common)

The test

Let us show on tnsnames.ora entries how it works. Let us suppose that in common tnsnames.ora we have content:
# tnsnames.ora Network Configuration File: c:\oracle\product\11.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE=
  (DESCRIPTION=
     (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)
     )
    (CONNECT_DATA= (SID = XE)
    )
  )

IFILE=c:\tns_private\tnsnames_private.ora
So if you try to tnsping xe, you'll get result:
E:\>tnsping xe

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-LIP-2010 21:44:45

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\pcdamir\tns_share\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)) (CONNECT_DATA= (SID = XE)))
OK (10 msec)

E:\>
As you see in line 8, there should be "\\pcdamir\tns_share" reference which shows that TNS_ADMIN has correct value that shows valid common network directory.
Now let us create c:\tns_private directory, tnsnames_private.ora file in it.
C:\tns_private>dir
 Volume in drive C has no label.
 Volume Serial Number is 084F-8D27

 Directory of C:\tns_private

02.06.2010.  21:53    <dir>          .
02.06.2010.  21:53    <dir>          ..
02.06.2010.  21:54               290 tnsnames_private.ora
               1 File(s)            290 bytes
               2 Dir(s)  11.702.571.008 bytes free

C:\tns_private>
where content of tnsnames_private.ora is:
C:\tns_private>more tnsnames_private.ora
# tnsnames.ora Network Configuration File: c:\oracle\product\11.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE.MY_DOMAIN.HR=
  (DESCRIPTION=
     (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)
     )
    (CONNECT_DATA= (SID = XE)
    )
  )

C:\tns_private>
As you see I have added the same instance with different tns name (I have to because of my other NAMES.DEFAULT_DOMAIN settings). And this entry is also valid:
C:\tns_private>tnsping XE.MY_DOMAIN.HR

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-LIP-2010 22:01:13

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\pcdamir\tns_share\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)) (CONNECT_DATA= (SID = XE)))
OK (20 msec)

C:\tns_private>

The best thing is when you need to change some setting from common tnsnames.ora, then you just add in your tnsnames_private.ora file. Because this file is executed after all definition in tnsnames.ora fiel (this is why I said to place at the end), your new entry should be that one that win. Here is an example for one tnsnames_private.ora file:
# tnsnames.ora Network Configuration File: c:\oracle\product\11.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE=
  (DESCRIPTION=
     (ADDRESS= (PROTOCOL = TCP)(HOST = serverx)(PORT = 1521)
     )
    (CONNECT_DATA= (SID = XE)
    )
  )
and the result is:
C:\tns_private>tnsping XE

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-LIP-2010 22:01:13

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\pcdamir\tns_share\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL = TCP)(HOST = serverx)(PORT = 1521)) (CONNECT_DATA= (SID = XE)))
OK (0 msec)

C:\tns_private>
As you see now your client see totally different instance then all other clients in network and the best of all you do not interfere with their settings at all!

The End

Regardless I have shown examples only for tnsnames.ora entries, the same situation is with sqlnet.ora file. This si also very handsome if on some client pc db is installed and you need special settings in one moment.

Another thing. If you want ensure that your entries in common tnsnames.ora or sqlnet.ora configuration files has absolute priority over any client entry, place ifile definition part in them at the beginning (not at the end)!

Cheers!

Tuesday, June 1, 2010

Blocking session ... show table records that are blocked


Another part of "blocking session" issues are blocking session that occur undetected. If you haven't implemented proactive blocking session monitoring then this event is 100% probable to happened sooner or later.

In mine previous two post on blocking session theme (Blocking session ... detect, unblock, notify and Blocking session ... detect, unblock, notify (Part II)) I have explained how to handle that problem in praxis.

Because blocking session present normal state when action in one session prevent execution of other session(s), deeper investigation on cause of this subject should be very common task of all DBA's and developers as well. After making all preventing steps in proactive reaction, last step is to find the root cause of blocking issues.

Because of mine luck, that from past experience, I wasn't involved in any serious locking "situations" where I could do something special, Oracle EBS (not locking at all because it has no foreign keys-app level of relation, smaller ERP's (where transactions was to short to see some problems at this level), this is the main reason why blocking session on record level was not part of my experience and interest. Frankly, there was one system, where I was working as DBA, where blocking sessions occur very frequently and cause big problems, but there the problem was totally wrong design where one table has records that were too much popular and this is find out very soon so no other investigation was not needed!

So I was really surprised when a friend of mine, Mirko, come to me with question: "How to see which table record is blocked by other session?". And this is the moment when this theme was born...

The problem

Test will be on table EMP. Just for easier reference let me show it's content.
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7698 BLAKE      MANAGER         7839 01.05.81       2850                    30
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7788 SCOTT      ANALYST         7566 09.12.82       3000                    20
      7902 FORD       ANALYST         7566 03.12.81       3000                    20
      7369 SMITH      CLERK           7902 17.12.80        800                    20
      7499 ALLEN      SALESMAN        7698 20.02.81       1600        300         30
      7521 WARD       SALESMAN        7698 22.02.81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28.09.81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08.09.81       1500          0         30
      7876 ADAMS      CLERK           7788 12.01.83       1100                    20
      7900 JAMES      CLERK           7698 03.12.81        950                    30
      7934 MILLER     CLERK           7782 23.01.82       1300                    10

14 rows selected.

SQL>
Suppose user1 make update statement like:
USER1@db1> @my_sid

USER                           INSTANCE            SID    SERIAL#
------------------------------ ------------ ---------- ----------
USER1                          db1                 307        292

USER1@db1> update emp set ename = '????' ;

14 rows updated.

USER1@db1> 
When user2 try to execute his update statement, he will be block:
USER2@db1> @my_sid

USER                           INSTANCE            SID    SERIAL#
------------------------------ ------------ ---------- ----------
USER2                          db1                 308        890

USER2@db1> update emp set ename = 'NEW_NAME' where empno= 7654;
running sb.sql (mentioned in one of mine previous topic) you notify that session 307 (blocker) is blocking other session 308 (waiter) :
DAMIRV@db1> @sb
Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker         Inst  SID     Serial      [sec]   Lock Type         Status       Module
------------------------------------------------------------------------------------------------------------------------
1. USER1         1     307         292       48   Transaction      INACTIVE       SQL*Plus
        USER2       1   308           890       42     Exclusive       INACTIVE        SQL*Plus

To kill first from the list, perform:

NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '307,292' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '307,292' IMMEDIATE;


RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''307,292'''' IMMEDIATE''; end; '
,instance=>1
);
  commit;
end;
/

PL/SQL procedure successfully completed.

DAMIRV@db1>
Regarding involved commands you may find:
  1. sql from blocker session
  2. sql from waiter session
by retrieving sql_text using info from gv$session or gv$active_session_history views.

Because case nr. 1) is harder to get (remember that commands in blocker session can continue to execute after "blocking statement" has been executed...so this sql what cause block is gone), here is an example for second case (find waiter sql).
DAMIRV@db1> @si 1 308
Instance ID = 1
SID         = 308
Current SQL....have result if session is active....

SQL_TEXT                                                                IID HASH_VALUE OPTIMIZER_COST
---------------------------------------------------------------------- ---- ---------- --------------
update emp set ename = 'NEW_NAME' where empno= 7654                       1  229437123              1

1 row selected.

Binding values....

no rows selected

Previous SQL....

SQL_TEXT                                                                IID HASH_VALUE OPTIMIZER_COST
---------------------------------------------------------------------- ---- ---------- --------------
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;                      1 1029988163              0

1 row selected.

 IID SID_SERIAL      AUDSID  ORAPID DBPID  APPPID     USERNAME   MODULE        ACTION          STATUS
---- ----------- ---------- ------- ------ ---------- ---------- ------------- --------------- --------
   1   308,  890     205187      32 356    1640:2780  USER2      SQL*Plus                      ACTIVE

1 row selected.

DAMIRV@db1>sql
And, in this case, the result, which record is blocking this session is obvious! So by killing the session 307 on instance 1 would give green light for our waiter session to continue running.

But as I said in real life situation may be (and always is by Murphy) different and worse. Let us make situation where user1 (blocker) perform:
update emp set ename='123' where empno=7654;
and then with user2 (which will become a "waiter") execute
update emp set ename='xxx';
Suppose also that in the meantime, in the blocker session some others sqls are performed (as they are in real life when you run PL/SQL block). In that situation there is no way to find out rows that originally cause the block in previously described way because blocker sql has been changed, and through waiter sql we cannot determine which row is blocking it because we are updating many of them.

The solution

For that we should use another columns from gv$session (row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#) and arrange that with some foreign tables to get more readable output. The result is sbr.sql, which expose problems on record level.
/* ---------------------------------------------------------------------------
 Filename: sbr.sql
 CR/TR#  : 
 Purpose : Find blocking records by locked session for RAC and non RAC environement  
           Resulting SQL shows rows which record is locked preventing from running blocked 
           session.
           
           Excellent way to find hotspot records (tables)
           
           
 Date    : 19.05.2010.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g/11g (should work on lower versions as well)
           
           "c_nr_cols" define number of columns (without pk columns!) of generated sql 
                       change it to your needs           

 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */
declare
  c_nr_cols CONSTANT PLS_INTEGER := 3;
    
  -- blockers ....
  CURSOR cur_blockers IS
      SELECT DISTINCT 
         username blocker_user, 
         gvb.sid, 
         gvs.serial# serial, 
         gvb.inst_id, 
         gvb.ctime ,
         gvs.status STATUS,      
         module, 
         action
       FROM gv$lock gvb, gv$lock gvw, gv$session gvs
      WHERE (gvb.id1, gvb.id2) in (
                                   SELECT id1, id2 FROM gv$lock WHERE request=0
                                   INTERSECT
                                   SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                  )
        AND gvb.id1=gvw.id1
        AND gvb.id2=gvw.id2
        AND gvb.request=0
        AND gvw.lmode=0
        AND gvb.sid=gvs.sid
        AND gvb.inst_id=gvs.inst_id
      ORDER BY CTIME desc
  ;
  
  --waiters
  CURSOR cur_w IS
      SELECT  gvw.sid waiter_sid,
              gvw.inst_id waiter_inst_id
      FROM gv$lock gvb, gv$lock gvw, gv$session gvs
      WHERE (gvb.id1, gvb.id2) in (
                                   SELECT id1, id2 FROM gv$lock WHERE request=0
                                   INTERSECT
                                   SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                  )
        AND gvb.id1=gvw.id1
        AND gvb.id2=gvw.id2
        AND gvb.request=0
        AND gvw.lmode=0
        AND gvb.sid=gvs.sid
        AND gvb.inst_id=gvs.inst_id
    ORDER BY gvb.CTIME desc
  ;
   
  CURSOR cur_waiters (cp_blocker_waiter_sid gv$lock.sid%TYPE, cp_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
      SELECT /* ordered */ 
        username waiter_user, 
        gvw.sid, 
        gvs.serial# serial, 
        gvw.inst_id, 
        gvw.ctime , 
        gvs.status STATUS,
        module,
        decode(gvw.request, 
                           0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-'
        ) lock_req,
        action
      FROM gv$lock gvb, gv$lock gvw, gv$session gvs
      WHERE (gvb.id1, gvb.id2) in (
                                    SELECT id1, id2 FROM gv$lock WHERE request=0
                                    INTERSECT
                                    SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                   )
        AND gvb.id1=gvw.id1
        AND gvb.id2=gvw.id2
        AND gvb.request=0
        AND gvw.lmode=0
        AND gvw.sid=gvs.sid
        AND gvw.inst_id=gvs.inst_id
        AND gvw.sid=cp_blocker_waiter_sid
        AND gvw.inst_id=cp_blockers_waiter_inst_id
      ORDER BY CTIME desc
    ;
  
  
  -- blocked records 
  CURSOR cur_blocked_objects (cp_inst_id IN PLS_INTEGER, cp_sid IN PLS_INTEGER) IS  
      SELECT do.owner,
             do.object_name,
             dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) RID
      FROM gv$session s
          ,dba_objects do
      WHERE inst_id=cp_inst_id 
        AND sid=cp_sid
        AND s.ROW_WAIT_OBJ# = do.OBJECT_ID
  ;
  
  -- columns which user want to see (pk keys are excluded because they are shown by default!)
  CURSOR cur_cols (cp_owner IN VARCHAR2, cp_table IN VARCHAR2, cc_nr_cols IN PLS_INTEGER) IS  
      WITH q as (
                  SELECT column_name
                    FROM all_tab_columns
                   WHERE owner= cp_owner 
                     AND table_name = cp_table     
                     AND column_name NOT IN ( SELECT b.column_name column_name_pk
                                                FROM all_constraints a, 
                                                     all_cons_columns b
                                               WHERE a.OWNER = cp_owner
                                                 AND a.table_name = cp_table
                                                 AND a.constraint_type='P'
                                                 AND a.constraint_name=b.constraint_name
                                                   
                                             )     
                  ORDER BY column_id
                )
      SELECT q.column_name
        FROM q
       WHERE rownum<=cc_nr_cols
  ;
  
  -- pk_key columns (always shown in the front)
  CURSOR cur_pkeys (cp_owner IN VARCHAR, cp_table IN VARCHAR) IS 
      SELECT b.column_name column_name_pk
        FROM all_constraints a, 
             all_cons_columns b
       WHERE a.OWNER = cp_owner
         AND a.table_name = cp_table
         AND a.constraint_type='P'
         AND a.constraint_name=b.constraint_name
      ORDER BY position
  ;  
  
  stmt  VARCHAR2 (4000);   
BEGIN 
  FOR rec_blockers IN cur_blockers LOOP
    dbms_output.put_line(rec_blockers.blocker_user||' ('||rec_blockers.inst_id|| ' '''||rec_blockers.sid||','||rec_blockers.serial||''')');
    FOR rec_w in cur_w LOOP
      FOR rec_waiters IN cur_waiters (rec_w.waiter_sid,rec_w.waiter_inst_id) LOOP 
        dbms_output.put_line (lpad(chr(9),9)|| 
                              rpad(rec_waiters.waiter_user,10,' ')||
                              rpad(to_char(rec_waiters.inst_id),4,' ')||'  '||
                              rpad(to_char(rec_waiters.sid||','||to_char(rec_waiters.serial)),11,' ')||'  '|| 
                              lpad(to_char(rec_waiters.ctime),6,' ')||' sec   '|| 
                              lpad(to_char(rec_waiters.lock_req),13,' ')||' '|| 
                              lpad(rec_waiters.status,14,' ')|| ' '|| 
                              lpad(to_char(nvl(rec_waiters.module,'?')),15,' ')
                              );      
        FOR rec_blocked_objects IN cur_blocked_objects (rec_waiters.inst_id,rec_waiters.sid) LOOP
          stmt := 'SELECT ';
         
          -- place pks cols in the front of select
          FOR rec_pkeys IN cur_pkeys (rec_blocked_objects.owner, rec_blocked_objects.object_name) LOOP
            stmt := stmt || rec_pkeys.column_name_pk ||' , '; 
          END LOOP;  

          -- then show other "c_nr_cols" number of cols (pks are not counted)
          FOR rec_cols IN cur_cols (rec_blocked_objects.owner, rec_blocked_objects.object_name, c_nr_cols) LOOP
            stmt := stmt ||  rec_cols.column_name || ' , ' ;
          END LOOP;
          IF SUBSTR (stmt,LENGTH(stmt)-2)=' , ' THEN
            stmt  :=  SUBSTR (stmt,1, (LENGTH(stmt)-3));
          END IF;
          stmt := stmt || ' FROM ' || rec_blocked_objects.owner ||'.'|| rec_blocked_objects.object_name ;
          
          -- get involved rowid for this session (there is always one blocking row!)        
          stmt := stmt || ' WHERE rowid = ' || ''''||  rec_blocked_objects.RID || '''' || ' ;';
          dbms_output.put_line(chr(9)||chr(9)|| stmt);
        END LOOP;   
      END LOOP;
    END LOOP;
  END LOOP;
END; 
/
The result is:
DAMIRV@db1> @sbr
USER1 (1 '307,292')
        USER2     1     308,890         105 sec       Exclusive         ACTIVE        SQL*Plus
                SELECT EMPNO , ENAME , JOB , MGR FROM DAMIRV.EMP WHERE rowid = 'AAAPMQAALAAEGHUAAJ' ;

PL/SQL procedure successfully completed.
Based on blocking information from database views, script dynamically generate sql statement whose result shows which record in "waiter" sessions are waiting for release from blocking session action (commit or rollback). The only constant in script is c_nr_cols, which represent number of columns which will be included in generated sql result. Default is 3, but this exclude pk columns, which are always shown at the beginning of generated sql. In fact, script use data from gv$session, view which can help identifying records rowids of blocked sessions.

If you run generated sql result, you see records that are waiting to be "unlocked". Notice that for each session there is only one record:
DAMIRV@db1> SELECT EMPNO , ENAME , JOB , MGR FROM DAMIRV.EMP WHERE rowid = 'AAAPMQAALAAEGHUAAJ' ;

     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7654 MARTIN     SALESMAN        7698

1 row selected.

DAMIRV@db1>
Let us check that these results are correct. Let us see records by rowids:
DAMIRV@db1> select empno, ename, job, rowid
  2  from emp
  3  where rowid in ('AAAPMQAALAAEGHUAAJ');

     EMPNO ENAME      JOB       ROWID
---------- ---------- --------- ------------------
      7654 MARTIN     SALESMAN  AAAPMQAALAAEGHUAAJ

1 row selected.

DAMIRV@db1>
Then by pk values:
DAMIRV@db1> select empno, ename, job, rowid
  2  from emp
  3  where empno = 7654;

     EMPNO ENAME      JOB       ROWID
---------- ---------- --------- ------------------
      7654 MARTIN     SALESMAN  AAAPMQAALAAEGHUAAJ

1 row selected.

DAMIRV@db1> 
As you can see these queries generate the same outputs!

Because blocking session was updating all the records in EMP table (ENAME column), this is the record that really represent blocked record (were unable to be processed in waiter session). Of course there were some other ways to see the blocking row but shown method present "out of the box" solution for any case.

To show complexity of the problem, here is one "real life" output.
Oracle version: 10.2.0.4.0 (10.2.0.3.0)
Blocker         Inst  SID     Serial      [sec]   Lock Type         Status       Module
------------------------------------------------------------------------------------------------------------------------
  1. PETR          2     648       18149     1295   Transaction      INACTIVE              ?
            BIBE        2   651         14393      551   Share-Table       INACTIVE               ?
            SAIG        4   732         16425       67     Exclusive       INACTIVE               ?
            TAB         2   736          6976       11   Share-Table       INACTIVE               ?
            ANRU        2   682          4379       38     Exclusive       INACTIVE               ?
            BORO        2   725          4665       31   Share-Table       INACTIVE               ?
  2. BORO          2     725        4665      150   Transaction        ACTIVE              ?
            BIBE        2   651         14393      552   Share-Table         ACTIVE               ?
            TAB         2   736          6976       12   Share-Table         ACTIVE               ?
            ANRU        2   682          4379       38     Exclusive         ACTIVE               ?
            SAIG        4   732         16425       68     Exclusive         ACTIVE               ?
            BORO        2   725          4665       31   Share-Table         ACTIVE               ?
  3. SAIG          4     732       16425       66   Transaction        ACTIVE              ?
            BIBE        2   651         14393      552   Share-Table         ACTIVE               ?
            SAIG        4   732         16425       68     Exclusive         ACTIVE               ?
            TAB         2   736          6976       12   Share-Table         ACTIVE               ?
            ANRU        2   682          4379       38     Exclusive         ACTIVE               ?
            BORO        2   725          4665       31   Share-Table         ACTIVE               ?
    
PETR (2 '648,18149')
            BIBE      2     651,14393       554 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
            SAIG      4     732,16425        70 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            TAB       2     736,6976         27 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_BROJ_DOK WHERE rowid = 'AAAT6/AALAAAcduAAA' ;
            ANRU      2     682,4379         53 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            BORO      2     725,4665         58 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ; BORO (2 '725,4665')
            BIBE      2     651,14393       579 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
            SAIG      4     732,16425        94 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            TAB       2     736,6976         51 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_BROJ_DOK WHERE rowid = 'AAAT6/AALAAAcduAAA' ;
            ANRU      2     682,4379         77 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            BORO      2     725,4665         84 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ;
            ILBA      1     751,4084          4 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
SAIG (4 '732,16425')            BIBE      2     651,14393       616 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.M_DATDOK_I WHERE rowid = 'AAAUTHAALAABZFrAAA' ;
            SAIG      4     732,16425       131 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            ANRU      2     682,4379        114 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            ILBA      1     751,4084         39 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , DATDO , PERIOD_Z , STATUS FROM TAB.G_GODINE WHERE rowid = 'AAAT3TAAHAAAdS/AAC' ;
            BORO      2     725,4665        131 sec     Share-Table         ACTIVE               ?
        SELECT  FROM TAB.SCP_PK WHERE rowid = 'AAAT7GAALAAEP0CAAA' ;
            IVBD      1     642,6661         52 sec       Exclusive         ACTIVE               ?
        SELECT GODINA , RBR , DATKNJ FROM TAB.P_DNEVNIK WHERE rowid = 'AAAUwzAAHAABxFoAAA' ;
            TAB       2     736,6976         27 sec     Share-Table         ACTIVE               ?

    
To kill first from the list, perform: 
    
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '648,18149' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '648,18149' IMMEDIATE;
    
    
RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM DISCONNECT SESSION ''''648,18149'''' IMMEDIATE''; end; '
,instance=>2
);
  commit;
end;
/

This is automated message so, do not reply to this mail.
Regards,
Your auto DBA d.o.o.

Zagreb, 01.06.2010 08:50:45
Just to mention that for delete statements sbr.sql cannot generate blocked records!

The end

Using this approach in blocking session issues, lead you to find "hot spot" tables or "hot spot" records in tables that present critical resources for your applications.

On Oracle Metalink you may find interesting document ID 15476.1-"FAQ about Detecting and Resolving Locking Conflicts" which widely covers locking subjects and presents comprehensive knowledge for studding this issue deeper.
Just for your notes, ID 729727.1-"Detecting blocking Locks in 10.2 and above", which shows a way to find locking issues, is not working on Oracle 11.2, what is not in mine cases.

Now I really think that blocking session theme is covered from all points of view. If you find something that is still not, please speak out and I'll try to present the solution.

Until then...as allways...

Cheers!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign