Datafile Creation Error On Standby When Primary Is On ASM

The issues happen when you add datafiles on primary which is on ASM (OMF).
MRP aborted on standby due to below error while your  standby db is on filesystem (not ASM)
The standby complains it can't create datafile even though  standby_file_management='auto' is in place

 Error like in alert logs are :
ORA-01119: error in creating database file '+data'
ORA-17502: ksfdcre:4 Failed to create file +data
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
File #56 added to control file as 'UNNAMED00056'.

First we need to make MRP work and narrow the gap,so we need to manually create datafile 'UNNAMED00056' on standby

• use sql select file#,name from v$datafile where name like '%UNNAMED%';  to check how many we have
• use sql alter database to create a new datafile to replace old one.  ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/11.2.0.3/test/dbs/UNNAMED00056' as '/u111/oradata/test/test_05.dbf' size 10g
•  Then you can restart MRP to continue recovery process

Second we need to prevent this error in the future

• Make sure  standby_file_management='auto' is in place
• In some notes, you may see db_file_create_dest, however it's obsolete after 11gR2
• The good parameter we need to set is DB_FILE_NAME_CONVERT
• Example is alter system set DB_FILE_NAME_CONVERT = '+DATA/test/datafile','/u111/oradata/test' scope=spfile sid='*'; 
• This parameter can't set dynamic, must be set on spfile

How To Identify Related DeadLock Sqls in LMD Trace file

After oracle db 11gR2, the dead lock sql information will be collected in LMD trace file for RAC
You will see such infor in alert logs

2013-05-17 13:00:45.065000 -05:00
Global Enqueue Services Deadlock detected. More info in file
/test/oradiag/diag/rdbms/testadc/test1/trace/test_lmd0_1825.trc

This trc file can be appended to record many days deadlock infor before db bounce

There are quite many infor in the trc file,quite a few sql in the trc. Sometimes you are confused by what are related sql for the deadlocks. If you are not checking carefully, you may end up put unrelated sql into your conclusions. here are important parts to look

Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.6de0] :
BLOCKED 0x44ef30548 5 wq 2 cvtops x1 TX 0x200005.0x110998(ext 0x6,0x0)[7B000-0001-000006E1] inst 1
BLOCKER 0x45144f800 5 wq 1 cvtops x28 TX 0x200005.0x110998(ext 0x6,0x0)[7D000-0003-00000009] inst 3
BLOCKED 0x44e1a78a0 5 wq 2 cvtops x1 TX 0x10017.0x2ebd13(ext 0x1,0x0)[7D000-0003-00000009] inst 3
BLOCKER 0x45164caf0 5 wq 1 cvtops x28 TX 0x10017.0x2ebd13(ext 0x1,0x0)[8F000-0001-00000010] inst 1
BLOCKED 0x44ef308e8 5 wq 2 cvtops x1 TX 0x200005.0x110998(ext 0x6,0x0)[8F000-0001-00000010] inst 1
BLOCKER 0x44ef30548 5 wq 2 cvtops x1 TX 0x200005.0x110998(ext 0x6,0x0)[7B000-0001-000006E1] inst 1 


The 3rd column indicates it TX mode 5 lock
The last column indicates which rac node
 
search trc file for 0x44ef30548 0x45144f800 0x45164caf0 0x44ef308e8 
record the sql you find, they are related deadlock sql
Sometimes you will see same sql for all of the deadlocks. That's because you have many midtiers which are doing same job at the same time and may cause conflicts if applications are not well designed.

Oracle WebCenter Content Batch Monitor Sql

If you happen to use Oracle WebCenter Content and use it's replication technology , one of key monitors will be on indexer/archiver
The batch information of replication indicates usage pattern, ucm performace .....etc
A few sql to share how to monitor batches in Oracle WebCenter Content.
Obviously you can twist them with different time period.

How many batches per day

select count( distinct  substr(a.dbatchfile, 1, instr(a.dbatchfile, '/') - 1) ) num_batchfile  from archivehistory a where   dactiondate > sysdate - 1 

Total size of batches per day

select         sum(d.dfilesize)/1024/1024  totalsize_MB
  from archivehistory a, documents d
  where a.did = d.did
   and d.disprimary = 1
   and a.dactiondate > sysdate - 1

 Average size of batches per day

select
(
select  sum(d.dfilesize)/1024/1024  totalsize_MB
  from archivehistory a, documents d
  where a.did = d.did
   and d.disprimary = 1
   and a.dactiondate > sysdate - 1
   )
/
  (
   select count( distinct  substr(a.dbatchfile, 1, instr(a.dbatchfile, '/') - 1) ) num_batchfile  from archivehistory a where   dactiondate > sysdate - 1 
    )  avg_size_MB from dual 

How To Check RAC Interconnect is uing UDP or RDS

A simple to know is to use skgxpinfo  under CRS Home

CRS % skgxpinfo -v
Oracle RDS/IP (generic)

CRS % skgxpinfo -v
Oracle UDP/IP (generic)

A Sql to find Long Idle Sessions on DB

Just share a  sql below is about to find jdbc connections with idle for 8 days(28800/3600)

select username,sid,serial#, (sysdate - prev_exec_start)*24*3600 last_call_during  from v$session where program like '%JDBC%' and event ='SQL*Net message from client'
and  status = 'INACTIVE' and (sysdate - prev_exec_start)*24*3600 > 28800