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!
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!
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.
2. Move to end of last block
3. Save and compress content to tape
4. Rewind
2. Move to block from which you want to restore (0,1,2,3 etc.- number of block)
3. Restore from tape to current directory
Cheers!
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. Rewindmt -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 tapemt -f /dev/st0 eject
Restore from tape
1. Rewindmt -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:- Do not run when archive_aud$ database job is active
- Run only from one node (if using RAC)
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 completeKeep 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.shBecause 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:- AUDIT_DDL_LOG table which will hold DDL information that were processed
- AUDIT_DDL_LOG_ARCH table that will hold archive DDL information
- Database trigger which will fill AUDIT_DDL_LOG table
- Procedure ARCHIVE_AUDIT_DDL_LOG which will move records from AUDIT_DDL_LOG to AUDIT_DDL_LOG_ARCH table
- 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:
- Add another table whose records will be filled with similar trigger which fire "BEFORE DDL"
- Difference (SQL "MINUS") between those two tables would show unsuccessful DDL operations
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:
Here is the output:
Then mine focus to look in "state directory and see dates of files:
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:
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!
- 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.
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_targetMain 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/stateIf 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.- 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.
- And then only on one node, force a collection to be submitted to Metalink
$ORACLE_HOME/ccr/bin/emCCR collect
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:- 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;
- stop the Scheduler and remove the crontab entry
$ORACLE_HOME/ccr/bin/deployPackages -d $ORACLE_HOME/ccr/inventory/core.jar
- Delete the ccr directories on all nodes
rm -rf $ORACLE_HOME/ccr
- This step is optional, but I strongly advise it in that moment...download the latest OCM from My Oracle Support dashboard.
- As oracle user, extract the zip file into $ORACLE_HOME. This will create new $ORACLE_HOME/ccr directory
- 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
- Instrument the database, only on node1
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s $ORACLE_SID
- Force a collection and upload, from each node
$ORACLE_HOME/ccr/bin/emCCR collect
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.
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:
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:
On "pcdamir" (where common share is defined) edit tnsnames.ora and sqlnet.ora and put in the end following entries:
tnsnames.ora
Now let us create c:\tns_private directory, tnsnames_private.ora file in it.
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:
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!
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:- existence of TNS_ADMIN environment variable (Linux or Windows), which represent pointer to directory where network configuration files are located
- 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 asTNS_ADMIN=\\pcdamir\tns_shareIn 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.orasqlnet.ora
IFILE=c:\tns_private\sqlnet_private.oraPlaced 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)
- create directory c:\tns_private
- In that directory place (even empty ones!) tnsnames_private.ora and sqlnet_private.ora files
- 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: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>
- sql from blocker session
- sql from waiter session
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>sqlAnd, 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:45Just 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!