This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

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.
/* ---------------------------------------------------------------------------

           Copyright(C) 1995-2020 Vadas savjetovanje d.o.o.


 Filename: sbr.sql (show_blocked_records)
 CR/TR#  : 
 Purpose : Find blocking records by locked session for RAC and non RAC environment  
           Resulting SQL shows rows which are locked and their release will continue 
           running blocking session.
           
           Excellent way to find hot records (hot 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       
           
           Lock types can be retrieved by :
           SELECT type, name, is_user, description FROM V$LOCK_TYPE ORDER BY is_user DESC, type;                       

 Changes (DD.MM.YYYY Name, CR/TR#):
          12.07.2010 Damir Vadas
                     added support for current waiter sql statement and 
          19.08.2010 Damir Vadas
                     added exception block for no_data_found case
                     added "substr" for l_sql parse
          01.09.2010 Damir Vadas
                     fixed bug blockers and waiters to be 11g compatible
          03.09.2010 Damir Vadas
                     added "Locked object is not table" for "starnge" locks in 11g
          20.09.2010 Damir Vadas
                     Reunited logic for script/procedure (change value for b_is_script)
          19.10.2010 Damir Vadas
                     Added "l_sql := '';  -- not null !"
          09.09.2013 Damir Vadas
                     Adopted to write logs to tools.blocker_logs table ...
                      this why procedure becomes AUTONOMOUS_TRANSACTION
                      blocking session is if last for at least 120 seconds
                      added "p_write_to_table" parameter
                             true  write blocking information to tools.blocker_logs table
                             false do not write
                     removed all VARCHAR2 and introduced CLOB (s_mail_text, l_sql)
          22.01.2014 Damir Vadas
                     20=>30 (line rpad(rec_waiters.waiter_user||' (' || rec_waiters.inst_id||' '||rec_waiters.sid||','||rec_waiters.serial||')',20,' ')||)
          31.03.2014 Damir Vadas
                     Fixed select statement which was run against indexes                     
          23.08.2017 Damir Vadas                     
                     added @sash support for quick investgation and ctime for it
          28.11.2017 Damir Vadas              
                     now is all faster!          
                        removed gv$sqlarea query
                        added hint /*+ CPU_COSTING 
                        SELECT
                               LPAD(' ', (level-1)*2, chr(9)) || NVL(s.username, '(oracle)')  || ' (@sash '|| s.inst_id || ' '|| s.sid || ' ' ||s.serial# || ' '|| ceil (s.LAST_CALL_ET/60) ||')',
                               s.status,
                               s.module,
                               blocking_session
                        FROM   gv$session s
                        WHERE  1=1
                          AND  level > 1
                           OR     EXISTS (SELECT 1 FROM gv$session WHERE  blocking_session = s.sid AND inst_id=s.inst_id)
                        CONNECT BY PRIOR s.sid = s.blocking_session
                        START WITH s.blocking_session IS NULL;                        
          20.08.2020 Damir Vadas              
                     Change lock output
                     added hint INDEX(@SEL$11 C I_CDEF2)
                     redisned the whole code
--------------------------------------------------------------------------- */ 

@sqlplus_default;

set serveroutput on size unlimited;
SET FEEDBACK OFF

declare
  const_nr_cols CONSTANT PLS_INTEGER := 3;
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
  --  
  -- blockers ....
  CURSOR c_blockers IS
    SELECT level,
           gvs.inst_id, 
           gvs.sid,
           gvs.serial#,
           gvs.username blocker_user,
           LPAD(' ', (level-1)*2, chr(9)) || NVL(gvs.username, '(oracle)')  || ' (@si '|| gvs.inst_id || ' '|| gvs.sid || ' ' ||gvs.serial# || ' ' || ceil (gvs.LAST_CALL_ET/60) ||')   ' || gvs.status || ' ,' ||lpad(to_char(nvl(gvs.module,'?')),15,' ') BLOCK_TREE,
           gvs.status,
           gvs.blocking_session
    FROM   gv$session gvs
    WHERE  1=1    
      AND  level > 1
       OR     EXISTS (SELECT 1 FROM gv$session WHERE  blocking_session = gvs.sid AND inst_id=gvs.inst_id)
    CONNECT BY PRIOR gvs.sid = gvs.blocking_session
    START WITH gvs.blocking_session IS NULL
  ;
  -- blocked records (this is allways one object with one locked row)
  CURSOR c_blocked_objects (p_inst_id IN NUMBER, p_sid IN NUMBER, p_serial# IN NUMBER) IS
    SELECT do.owner,
           do.object_name,
           do.object_type,
           dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) RID,
           s.ROW_WAIT_OBJ# ROW_WAIT_OBJ,
           s.event
    FROM gv$session s
        ,dba_objects do
    WHERE inst_id=p_inst_id
      AND sid=p_sid
      AND serial#=p_serial#
      AND s.ROW_WAIT_OBJ# = do.OBJECT_ID
  ;
  
  CURSOR c_blocked_objects2 (p_inst_id IN NUMBER, p_sid IN NUMBER, p_serial# IN NUMBER) IS
    SELECT do.owner,
           do.object_name,
           do.object_type
    FROM gv$session s
         ,dba_objects do
    WHERE blocking_instance=p_inst_id
      AND BLOCKING_SESSION=p_sid
      AND serial#=p_serial#
      AND s.ROW_WAIT_OBJ# = -1
      AND DO.OBJECT_ID = P2
  ;

  -- columns which user want to see (pk keys are excluded because they are shown by default!)
  CURSOR c_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 EXISTS (SELECT 1
                                  FROM all_cons_columns b JOIN all_constraints a ON (b.owner = a.owner AND a.constraint_name = b.constraint_name)
                                  WHERE 1 = 1 AND a.constraint_type = 'P' AND a.OWNER = cp_owner AND a.table_name = cp_table
                               )
                   ORDER BY column_id
                  )
     SELECT /*+ INDEX(@SEL$11 C I_CDEF2) */ q.column_name
       FROM q
      WHERE ROWNUM <= cc_nr_cols;
 
  -- pk_key columns (always shown in the front)
  CURSOR c_pkeys (cp_owner IN VARCHAR, cp_table IN VARCHAR) IS 
      SELECT b.column_name column_name_pk
        FROM all_cons_columns b JOIN all_constraints a ON (b.owner = a.owner AND a.constraint_name = b.constraint_name)
       WHERE 1=1 
         AND a.constraint_type='P' AND a.OWNER = cp_owner AND a.table_name = cp_table
      ORDER BY position ;  
  --
  --stmt  VARCHAR2 (4000);
  -- for showing current waiter sql
  --l_sql VARCHAR2 (4000); 
  FUNCTION add_indent_char RETURN CHAR
  IS
  BEGIN
    RETURN chr(9);
  END;
  --
  PROCEDURE add_message_line (p_text VARCHAR2, p_level IN NUMBER) 
  IS
  BEGIN
    dbms_output.put_line (LPAD(' ', (p_level-1)*2, add_indent_char) || p_text);
  END;
  --
  FUNCTION get_curent_sql_stmt (p_inst_id IN NUMBER, 
                                p_sid     IN NUMBER, 
                                p_serial# IN NUMBER
                               ) RETURN       VARCHAR2
  IS
    l_retval varchar2 (4000);
  BEGIN
    -- current sql statement support
    BEGIN
      SELECT 
        CASE 
          WHEN sql_fulltext is null then
            CASE 
              WHEN s.sql_id is null then
                '@sql_id2 '|| p_inst_id ||' '||s.prev_sql_id|| '*/'|| s.PREV_HASH_VALUE  
              ELSE
                '@sql_id2 '|| p_inst_id ||' '||s.sql_id|| '/'|| t.PLAN_HASH_VALUE
            END 
          ELSE
            CASE 
              WHEN s.sql_id is null then
                '@sql_id2 '|| p_inst_id ||' '||s.prev_sql_id|| '*/'|| s.PREV_HASH_VALUE  
              ELSE
                '@sql_id2 '|| p_inst_id ||' '||s.sql_id|| '/'|| t.PLAN_HASH_VALUE
            END || ', ' || dbms_lob.substr(sql_fulltext,80,1) 
          END sql_data
        INTO l_retval 
      FROM gv$sqlarea t, 
           gv$session s 
      WHERE 1=1 AND
           t.address (+)= s.sql_address AND 
           t.hash_value (+)= s.sql_hash_value AND 
           s.inst_id=p_inst_id AND
           s.SID = p_sid AND s.serial#=p_serial# 
      ;
    EXCEPTION
      WHEN OTHERS THEN 
        l_retval := SQLERRM;
    END;
    return l_retval;
  END;
  --
  FUNCTION create_SELECT_stmt (p_owner       VARCHAR2, 
                               p_object_name VARCHAR2,
                               p_row_id      rowid, --VARCHAR2,
                               p_event       VARCHAR2,
                               p_blocking_session NUMBER,
                               p_nr_cols     NUMBER DEFAULT const_nr_cols
                              ) RETURN       VARCHAR2
  IS
   l_retval VARCHAR2(4000);
  BEGIN
    CASE 
      WHEN p_blocking_session is null THEN
        l_retval := ' -- TOP BLOCKER --';
      ELSE      
        l_retval := 'SELECT ';
        -- place pks cols in the front of select
        FOR rec_pkeys IN c_pkeys (p_owner, p_object_name) LOOP
          l_retval := l_retval || rec_pkeys.column_name_pk ||' , '; 
        END LOOP;  
        -- then show other "c_nr_cols" number of cols (pks are not counted)
        FOR rec_cols IN c_cols (p_owner, p_object_name, const_nr_cols) LOOP
          l_retval := l_retval ||  rec_cols.column_name || ' , ' ;
        END LOOP;
        -- remove last added " , "
        IF SUBSTR (l_retval,LENGTH(l_retval)-2)=' , ' THEN
          l_retval  :=  SUBSTR (l_retval,1, (LENGTH(l_retval)-3));
        END IF;
        -- 1.3
        -- when no data is returned - no index!!!
        CASE 
          WHEN l_retval = 'SELECT ' THEN
            l_retval := 'SELECT * FROM ' || p_owner ||'.'|| p_object_name ;
            -- get involved rowid for this session (there is always one blocking row!)
            l_retval := l_retval || ' WHERE rowid = ' || ''''||  p_row_id || '''' || ' ;';
            l_retval := l_retval || add_indent_char || '('|| p_event ||')';
            --WHEN p_event ='enq: TX - row lock contention' THEN
            --l_retval := 'Problem is NOT in INDEX: ' || p_owner ||'.' || p_object_name;      
          ELSE
            null;
        END CASE;
    END CASE;
    return l_retval;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN SQLERRM;
  END; 
BEGIN 
  add_message_line (chr(9),0);
  dbms_utility.db_version(db_ver,db_ver2);
  add_message_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')', 0);
  add_message_line (chr(9),0);
  FOR rec_blockers IN c_blockers LOOP
    add_message_line(rec_blockers.BLOCK_TREE, 0);
    add_message_line('('||get_curent_sql_stmt (rec_blockers.inst_id, rec_blockers.sid, rec_blockers.serial#)||')',rec_blockers.level+1);
    FOR rec_blocked_objects IN c_blocked_objects (rec_blockers.inst_id,rec_blockers.sid,rec_blockers.serial#) LOOP
      add_message_line(create_SELECT_stmt (rec_blocked_objects.owner, rec_blocked_objects.object_name,rec_blocked_objects.RID,rec_blocked_objects.event,rec_blockers.blocking_session),rec_blockers.level+1);
    END LOOP; 
  END LOOP;
END;
/

SET FEEDBACK ON
The result is:
SQL>@sbr
	
Oracle version: 12.2.0.1.0 (12.2.0.1)
	
SYS (@si 1 426 49238 58)   INACTIVE ,	    SQL*Plus
	 (@sql_id2 1 2fyw2psbd23vx*/382799741)
	 SYS (@si 1 244 37701 58)   ACTIVE ,	   SQL*Plus
			 (@sql_id2 1 2fyw2psbd23vx/1783341450, update t1 set c1='A1' where c1='x')
			 SELECT * FROM SYS.T1 WHERE rowid = 'AA8YwSAABAAAAlpAAA' ;	(enq: TX - row lock contention)
			 SYS (@si 1 250 18808 57)   ACTIVE ,	   SQL*Plus
					 (@sql_id2 1 6n5w8gmumgu19/1783341450, update t1 set c1='x' where c1='A2')
					 SELECT * FROM SYS.T1 WHERE rowid = 'AA8YwSAABAAAAlpAAB' ;	(enq: TX - row lock contention)
Elapsed: 00:00:03.22
SQL>SELECT * FROM SYS.T1 WHERE rowid = 'AA8YwSAABAAAAlpAAA';

C1
----------
x

1 row selected.
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