Solving Waits on "enq: TM - contention"

Symptoms
High "enq: TM - contention"  lock waits   on insert  statments

Cause :
Waits on "enq: TM - contention" indicate there are unindexed foreign key constraints

Solution:
Create  index  on Foreignk Key columns .Following script will find  Unindexed Foreign Key Constraints

SELECT * FROM (
SELECT c.owner,c.table_name, cc.column_name, cc.position column_position
FROM DBA_constraints c, DBA_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
and c.owner not in ('SYS','SYSMAN','SYSTEM')
MINUS
SELECT i.owner,i.table_name, ic.column_name, ic.column_position
FROM DBA_indexes i, DBA_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position
/


Reproduce :

create table departments
(
dept_no number,
dept_name varchar2(100),
CONSTRAINT dep_no_pk PRIMARY KEY (dept_no)
);

insert into departments values (1,'Department 1');
insert into departments values (2,'Department 2');
insert into departments values (3,'Department 3');
commit;

create table employees (
emp_id number,
emp_name varchar2(100),
emp_dept_no number,
CONSTRAINT fk_departments FOREIGN KEY (emp_dept_no) REFERENCES departments(dept_no) ON DELETE CASCADE
);

insert into employees values (1,'Serdar Turgut',1);
insert into employees values (2,'Erkan saka',2);
insert into employees values (3,'Metin Yavas',2);
insert into employees values (4,'Abdullah Ongul',3);
commit;

SESSION 1:delete departments where dept_no=1 ;
SESSION 2:delete departments where dept_no=2 ;
SESSION 3:insert into departments values (4,'Dept 4 ');

column type format a4
column lmod format a4
column request format 999
SELECT l.sid, s.blocking_session blocker, SUBSTR(s.event,1,20),
l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('SERDAR')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

SID BLOCKER SUBSTR(S.EVENT,1,20) TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
-------- --------- -------------------- ---- --------- ------- ------------------------------ -----
4859 SQL*Net message from TM 3 0 DEPARTMENTS TABLE
4859 SQL*Net message from TM 3 0 EMPLOYEES TABLE
4859 SQL*Net message from TX 6 0
4902 4909 enq: TM - contention TM 0 2 EMPLOYEES TABLE
4902 4909 enq: TM - contention TM 3 0 DEPARTMENTS TABLE
4909 4859 enq: TM - contention TM 0 5 EMPLOYEES TABLE
4909 4859 enq: TM - contention TM 3 0 DEPARTMENTS TABLE


See also:All Oracle enqueue waits

ORA-1000 maximum open cursors exceeded

Cause:
Program attempted to open too many cursors.

Explanation:
Cursors are resources explicitly opened by application code, and in recursive operations by Oracle code. The init.ora controls the maximum number of concurrent cursors a session may have open at any point in time. An attempt to open an additional cursor results in the ORA-1000 "maximum open cursors exceeded" error.

Diagnose:

• Check  the value of the OPEN_CURSORS parameter
• Check  the open curdor  for  a session
  Select  * from v$open_cursor where  sid =xxxx
• it is possible to get the user session to generate a trace file when the error occurs
  event="1000 trace name errorstack level 3"

Action:

• Modify the program to use fewer cursors
• or  increase the value of  OPEN_CURSORS (This  needs  restart of database )

Transaction recovery: lock conflict caught and ignored

Symptoms:
I have  many  "Transaction recovery: lock conflict caught and ignored"  in alert.log
and Also I have  ORA-01555  errors

Cause
One  long transactions  interreputed  and  try  to recover so  UNDORBS is full

Solution

• I have  checked  according  to   Oracle rollback - undo monitoring tips
• Then I have  added  datafile to UNDORBS in order  not  to have  the errors  until transaction recovery
• I have  checked   but  the transaction recovery  is  very  slow
  
  select
  CPUTIME,cputime/3600,UNDOBLOCKSDONE ,UNDOBLOCKSTOTAL ,
  CPUTIME/UNDOBLOCKSDONE*UNDOBLOCKSTOTAL/60/60
  from GV$FAST_START_TRANSACTIONS;
• I have  set   FAST_START_PARALLEL_ROLLBACK  parameter  in order to speed  the  recovery 
  
  ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH;
• After  recovery  everything is  fine
  

How to Clean Up After a Failed 10g or 11.1 Oracle CRS(Clusterware Installation)

1. Runing  following  scripts  from any nodes   should be sufficent to clean up your CRS install
   
   $CRS_HOME/install/rootdelete.sh
   $CRS_HOME/install/rootdeinstall.sh
   
   Rootdelete.sh accepts options like nosharedvar/sharedvar, and nosharedhome/sharedhome.
   
   If for some reason you have to manually remove the install due to problemswith the scripts, continue to step 2:
2. Stop the Nodeapps on all nodes (e.x santaro,pandora )
   srvctl stop nodeapps -n  santarosrvctl stop nodeapps -n pandora
3. Prevent CRS from starting when the node boots. To do this issue the following as root:
   
   Sun:
   rm /etc/init.d/init.cssd
   rm /etc/init.d/init.crs
   rm /etc/init.d/init.crsd
   rm /etc/init.d/init.evmd
   rm /etc/rc3.d/K96init.crs
   rm /etc/rc3.d/S96init.crs
   rm -Rf /var/opt/oracle/scls_scr
   rm -Rf /var/opt/oracle/oprocd
   rm /etc/inittab.crs
   cp /etc/inittab.orig /etc/inittab
   
   Linux:
   
   rm /etc/oracle/*
   rm -f /etc/init.d/init.cssd
   rm -f /etc/init.d/init.crs
   rm -f /etc/init.d/init.crsd
   rm -f /etc/init.d/init.evmd
   rm -f /etc/rc2.d/K96init.crs
   rm -f /etc/rc2.d/S96init.crs
   rm -f /etc/rc3.d/K96init.crs
   rm -f /etc/rc3.d/S96init.crs
   rm -f /etc/rc5.d/K96init.crs
   rm -f /etc/rc5.d/S96init.crs
   rm -Rf /etc/oracle/scls_scr
   rm -f /etc/inittab.crs
   cp /etc/inittab.orig /etc/inittab
   
   
   HP-UX:
   
   rm /sbin/init.d/init.cssd
   rm /sbin/init.d/init.crs
   rm /sbin/init.d/init.crsd
   rm /sbin/init.d/init.evmd
   rm /sbin/rc2.d/K960init.crs
   rm /sbin/rc2.d/K001init.crs
   rm /sbin/rc3.d/K960init.crs
   rm /sbin/rc3.d/S960init.crs
   rm -Rf /var/opt/oracle/scls_scr
   rm -Rf /var/opt/oracle/oprocd
   rm /etc/inittab.crs
   cp /etc/inittab.orig /etc/inittab
   
   HP Tru64:
   
   rm /sbin/init.d/init.cssd
   rm /sbin/init.d/init.crs
   rm /sbin/init.d/init.crsd
   rm /sbin/init.d/init.evmd
   rm /sbin/rc3.d/K96init.crs
   rm /sbin/rc3.d/S96init.crs
   rm -Rf /var/opt/oracle/scls_scr
   rm -Rf /var/opt/oracle/oprocd
   rm /etc/inittab.crs
   cp /etc/inittab.orig /etc/inittab
   
   IBM AIX:
   
   
   
   
   rm /etc/init.cssd
   rm /etc/init.crs
   rm /etc/init.crsd
   rm /etc/init.evmd
   rm /etc/rc.d/rc2.d/K96init.crs
   rm /etc/rc.d/rc2.d/S96init.crs
   rm -Rf /etc/oracle/scls_scr
   rm -Rf /etc/oracle/oprocd
   rm /etc/inittab.crs
   cp /etc/inittab.orig /etc/inittab
4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot the node:
   
   ps -ef |grep crs
   ps -ef|grep  evm
   ps -eaf|grep  css
   
   Do not kill any OS processes, for example icssvr_daemon process !
5. If there is no other Oracle software running (like listeners, DB's, etc...), you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:
   
   rm -f /var/tmp/.oracle/*
   or
   rm -f /tmp/.oracle/*
6. Remove the ocr.loc ( Usually the ocr.loc can be found at /etc/oracle)
7. De-install the CRS home in the Oracle Universal Installer
8. Remove the CRS install location.
   rm -rf /*
9. Clean out the OCR and Voting Files with dd commands
   
   for i in 1 2
   
   do
     dd if=/dev/zero of=/dev/ocrdisk$i bs=8192 count=25000
   done
   
   for i in 1 2 3
   do
     dd if=/dev/zero of=/dev/votedisk$i bs=8192 count=25000
   done
   
   If you placed the OCR and voting disk on a shared filesystem, remove them.
   If you are removing the RDBMS installation, also clean out any ASM disks if they have already been used.
10. The /tmp/CVU* dir should be cleaned also to avoid the cluvfy misreporting.
    rm -rf  /tmp/CVU*
11. reboot the node before starting the next install.
    
    
    
     

How to debug racgvip ( or a resource in a rac )

I have problem with  VIP start   (santaro,pandora  are two 

santaro@cbdst:/cbdsthome/crshome/log/santaro/racg$ crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora....ora.gsd application ONLINE ONLINE pandora
ora....ora.ons application ONLINE ONLINE pandora
ora....ora.vip application ONLINE OFFLINE
ora....aro.gsd application ONLINE ONLINE santaro
ora....aro.ons application ONLINE ONLINE santaro
ora....aro.vip application ONLINE OFFLINE

crs_stat ora.santaro.vip

NAME=ora.santaro.vip
TYPE=application
TARGET=ONLINE

STATE=OFFLINE

I want to debug  $ORA_CRS_HOME/bin/racgvip which starts the VIP .There  are  two way 

1. uncommenting the environment variable _USR_ORA_DEBUG=1 in the script $CRS_HOME/bin/racgvip
2. By  crsctl  command as  root  user
   
   
   --Open debuging for ora.santaro.vip resource  by root
   crsctl debug log res "ora.santaro.vip:5"  
   
   --start nodeapps This will create a log for VIP starting problem for 10.2 and above version in directory $CRS_HOME/log/{hostname}/racg/*vip.log  analyze  the log
   srvctl start nodeapps -n santaro
   
   --Turn off  debuging  by root
   
   
   crsctl debug log res "ora.santaro.vip:0"

Oracle rollback - undo monitoring tips

How much time need When a long, running transaction has been rolled back

• In Oracle 9i Database and below, you can issue the query which returns the number of undo records used by the current transaction, and if executed repeatedly, will show continuously reduced values because the rollback process will release the undo records as it progresses. You can then calculate the rate by taking snapshots for an interval and then extrapolate the result to estimate the finishing time.
  
  SELECT USED_UREC FROM V$TRANSACTION;
• Also  we  can use  v$session_longops OPNAME column ='Transaction Rollback'
  select time_remaining
  from v$session_longops
  where sid = ;
• v$fast_start_transactions contains the information about the progress of the transactions that the Oracle server is recovering. It also contains information about transactions that the Oracle server has recovered. For transactions that the Oracle server is recovering, the STATE is RECOVERING. For transactions that the Oracle server has recovered, the STATE is RECOVERED. Only limited historical information is kept in this view, and small (in terms of undo blocks) transactions are not tracked.
  
  
  SELECT state,undoblocksdone,undoblockstotal,cputime,rcvservers
  FROM v$fast_start_transactions;
• v$fast_start_servers provides information about all the recovery servers performing, or that have performed parallel transaction recovery.If rcvservers column of the above  query   =1  means  transaction is being rolled back serially by SMON process.If >1  then you can select detailed  information     from  v$fast_start_servers  view 
• if rcserver=1  means  FAST_START_PARALLEL_ROLLBACK  is  disabled   and  recovery is  done  by one process .we  can  change  this parameter online  in order  to speed the  recovery
  
  alter system set fast_start_parallel_rollback = HIGH;
  
  FALSE indicates that parallel rollback is disabled
  
  LOW limits the number of rollback processes to 2 * CPU_COUNT
  HIGH limits the number of rollback processes to 4 * CPU_COUNT
  
  Also be careful while setting  fast_start_parallel_rollback   because it can spwan 4*CPU_COUNT process  and  rise  the  cpu usage %100
  
  
  
  
  
  

How to skip Orphan Transactions in Golden Gate

• when  golden gate  extract  is  restarted  It will  start to  scan at the recovery checkpoint  archive

This  means  that there is  an open transaction  in 1013793 archive

GGSCI  2> info E_CBDS showch

EXTRACT E_CBDS Last Started 2010-05-03 03:07 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Redo Logs
2010-05-04 11:59:26 Thread 1, Seqno 1013813, RBA 542140976

Current Checkpoint Detail:

Read Checkpoint #1
Oracle RAC Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 1013526
RBA: 13032976
Timestamp: 2010-05-03 03:02:53.000000
SCN: 48.3433707770 (209592137978)
Redo File: /cbdspredo02/redo_1_02.dbf

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Thread #: 1
Sequence #: 1013793
RBA: 529800208
Timestamp: 2010-05-04 09:41:20.000000
SCN: 48.3631197264 (209789627472)
Redo File: Not Avaliable

• I checked the  open transactions  in Golden Gate The oldest transaction is  688.6.2315775
  
  GGSCI (kamino) 7> send E_CBDS showtrans
  
  Sending SHOWTRANS request to EXTRACT E_CBDS ...
  Oldest redo log files necessary to restart Extract are:
  Redo Thread 1, Redo Log Sequence Number 1013634, SCN 48.3548506190 (209706936398), RBA 482658832
  
  ------------------------------------------------------------
  XID: 688.6.2315775
  Items: 62523375
  Extract: E_CBDS
  Redo Thread: 1
  Start Time: 2010-05-03:22:25:21
  SCN: 48.3548506190 (209706936398)
  Redo Seq: 1013634
  Redo RBA: 482658832
  Status: Running
  
  ....
  ....
• I checked all the transaction from database  but  the  oldest transaction stared   05/04/10 09:35:06  so  transaction XID: 688.6.2315775   is  mismatched  anyway 
  
  SQL>SELECT s.inst_id,
  
  s.sid,s.serial#,t.start_time,
  s.status
  FROM GV$session s, GV$transaction t, GV$rollstat r
  WHERE s.saddr=t.ses_addr
  and t.xidusn=r.usn
  and s.inst_id=t.inst_id
  and t.inst_id=r.inst_id
  order by t.start_time;
  
  1 10689 2949 05/04/10 09:35:06 INACTIVE
  
  2 10657 2782 05/04/10 09:44:40 ACTIVE
  2 10819 4957 05/04/10 09:49:20 INACTIVE
  2 10054 65352 05/04/10 09:50:22 ACTIVE
  2 10626 2361 05/04/10 09:55:16 ACTIVE
  2 10624 5782 05/04/10 10:00:15 ACTIVE
• I will skip this transaction BUT BE CAREFULL this  means  inconsistency   with the target replication side Before  doing that I will  analyze  the transaction  XID: 688.6.2315775 in archive   1013634 with logminer (Refer  to  this Logminer  article  for  detail).I will  replicate  this transaction manullay to the  target  database
  
  begin
  
  sys.dbms_logmnr.add_logfile (logfilename => 'arch_1013634_1_461937508.arc',options=>sys.dbms_logmnr.new);
  sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
  end;
  /
  
  create table serdar.logminer_table as select * from V$LOGMNR_CONTENTS;
  
  --XID: 688.6.2315775
  select * from serdar.logminer_table where xidusn=688 and xidslt=6 and xidsqn=2315775;
• Then last step I will  first  dump and  skip the transaction
  
  GGSCI  1>send E_CBDS showtrans 688.6.2315775  file tran_688.6.2315775.dmp detail
  
  GGSCI  2> SEND EXTRACT E_CBDS , SKIPTRANS 688.6.2315775 THREAD 1
  
  
  Sending SKIPTRANS request to EXTRACT E_CBDS ...
  
  Are you sure you sure you want to skip transaction [XID 688.6.2315775, Redo Thread 1, Start Time 2010-05-03:22:25:21, SCN 48.3548506190 (209706936398)]? (y/n)y
  Sending SKIPTRANS request to EXTRACT E_CBDS ...
  Transaction [XID 688.6.2315775, Redo Thread 1, Start Time 2010-05-03:22:25:21, SCN 48.3548506190 (209706936398)] skipped.
  
  
  
  
  
   

How To Find Optimal Index Key COMPRESS Level For Indexes

Key compression on an index eliminates repeated occurrence of key column values.The amount of compression is dependent on repeated occurrences.The COMPRESS integer specifies the number of prefix columns to compress (up to the number of primary-key columns minus 1.)


Run the following SQL and select the COMPRESS level that shows the greatest percentage of savings:


analyze index {INDEX_NAME} validate structure;


select opt_cmpr_count, opt_cmpr_pctsave from index_stats where name = '{INDEX_NAME}';

To compress  the index

alter index  {INDEX_NAME} rebuild online compress  N;

How do I check How much Oracle licence needed for my database

We can  check HIGHWATER values from v$licence  (for  RAC gv$licence ).we  can use   whether cpu or  user  part  of  the  following  query   dependening  on    we  have  cpu  licence or named user licence.

select
 sessions_current,sessions_highwater,
 cpu_count_current,cpu_core_count_current,
 cpu_count_highwater,cpu_core_count_highwater
from V$LICENSE;

Note1: that  Oracle  apply diffrent  core/cpu conversion for  diffrent type of  machines.
(http://www.oracle.com/corporate/contracts/library/processor-core-factor-table.pdf)

Note2:In order  to prevent  limit overflow for  session license_max_sessions,license_max_users init.ora parameters

Related  Topics
How to find usage of Advisors in Oracle (DBA_ADVISOR_USAGE)
How To Verify Which Database Options are Used in Oracle

How to find usage of Advisors in Oracle (DBA_ADVISOR_USAGE)

There are views named dba_feature_usage_statistics and dba_advisor_usage which record usage of the new features. These reports indicate that the user needs to purchase the license for the additional products. In other words, usage of features like AWR, ASH and ADDM is recorded in the database

DBA_ADVISOR_USAGE displays the usage information for each type of advisor in the database

SQL>select u.ADVISOR_ID,d.ADVISOR_NAME,u.LAST_EXEC_TIME ,u.NUM_EXECS 
from DBA_ADVISOR_USAGE u,DBA_ADVISOR_DEFINITIONS d 
where u.ADVISOR_ID=d.ADVISOR_ID;

ADVISOR_ID ADVISOR_NAME LAST_EXEC_ NUM_EXECS
---------- ------------------------------ ---------- ---------
1 ADDM                              19/06/2008 2
2 SQL Access Advisor      22/07/2006 0
3 Undo Advisor                   22/07/2006 0
4 SQL Tuning Advisor        22/07/2006 0
5 Segment Advisor             28/04/2010 9436
6 SQL Workload Manager 22/07/2006 0
7 Tune MView                      22/07/2006 0


Related Topics
How do I check How much Oracle licence needed for my database
How To Verify Which Database Options are Used in Oracle