Wednesday, November 13, 2013

How To Check User Login Information In The Past


To get client machine name:

To get timeframe
SELECT *   FROM DBA_HIST_ACTIVE_SESS_HISTORY where sql_exec_start  > sysdate -7  order by sql_exec_start desc

To get User name

SELECT  user_id  FROM DBA_HIST_ACTIVE_SESS_HISTORY where sql_exec_start  > sysdate -7  order by sql_exec_start desc

user user_id to get name from dba_users

Tuesday, November 12, 2013

Output v$flash_recovery_area_usage is incorrect

sometimes we clean recovery area manually , delete archive logs, old files ourselves
the output v$flash_recovery_area_usage can be  incorrect.

Use rman to sync the data dictionary

RMAN>CROSSCHECK ARCHIVELOG ALL;   (crosscheck won't delete, just mark them)

RMAN>Delete expired backup;     (will delete items which crosscheck fail )
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

RMAN> list backup    (it won't list data copies )
RMAN> list datafilecopy all;
RMAN> crosscheck datafilecopy all;
RMAN> delete datafilecopy all;
RMAN>delete datafilecopy tag=test_copy;

RMAN>Delete archivelog all completed before  'SYSDATE-7';
use exec dbms_backup_restore.refreshagedfiles ; 
to refresh

Thursday, October 24, 2013

Email SMTP Host DNS MX Record

Sometimes the mail gateway is configured as MX record
You can't get any IP from nslookup from
However you can use dig and find hostnames behind MX record
dig  mx

How it works ,refer wiki

Wednesday, October 09, 2013

How To Find Who is Bulk Loading on Oracle WebCenter Content

You always need to face performance issues. Sometimes they are from user side. When they pick a big folder and throw into WebContent, it generates lots of traffic and load on WCC archiver/indexer

How do we know the volume and username of bulk loading
Below sql is to tell last 1 hour information. You can change timeframe to know different period

select extract(day from (h.realactiondate)) dy, extract(hour from (h.realactiondate)) hr, h.duser, count(h.did)
  from cms_dochist h, docmeta d
  where h.did = d.did
  and h.daction = 'Checkin'
  and d.xconsumptionserver = 'TEST'
  and h.realactiondate sysdate -1/24 and sysdate
  group by extract(day from (h.realactiondate)), extract(hour from (h.realactiondate)), h.duser
   having count(h.did) > 100
  order by 1,2, 4 desc

Monday, July 15, 2013

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/' 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

Sunday, June 23, 2013

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


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.

Wednesday, June 19, 2013

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  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 

Tuesday, June 18, 2013

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)

Monday, June 17, 2013

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

Thursday, June 13, 2013

Listener Can't start Due to CRS Issue

Sometimes we can't start listeners, see this error in logs:

NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging
 NL-08002: Diagnosability  context creation failed

its due to crs is not running fine.
shutdown crs, then we can start

Thursday, June 06, 2013

Some Examples of TCPDUMP

These are just examples of tcpdump for reference:

tcpdump -i bond0 -x -s0 -w tcp_dump.log host

tcpdump dst and src not and src host-03 or src host15-03 or src host16-03 or src host03-03 or src host04-03 or src host05-03  -w  /admin/TCPDUMP_dst_host280-01

tcpdump dst

Tuesday, June 04, 2013

Examples To Get Definition of DB Objects

Normal DB objects we can get definition from  dbms_metadata.get_ddl():

SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI') FROM dual;

SELECT dbms_metadata.get_ddl('TABLESPACE', 'TEST_DATA') FROM dual;

select  dbms_metadata.get_ddl('INDEX','TEST') from dual;

 select  dbms_metadata.get_ddl('DB_LINK','TEST.COM') from dual;

 select  dbms_metadata.get_ddl('USER','TEST') from dual;

select  dbms_metadata.get_ddl('PROCEDURE','TEST_STRUCTURE') from dual;

select  dbms_metadata.get_ddl('FUNCTION','TEST_FUNC') from dual;

select  dbms_metadata.get_ddl('TABLE','TEST' ) from dual;

For  Oracle TEXT, we need to use specific package:
set long 500000
set pages 999
select ctx_report.create_index_script('FT_TESTTEXT2') from dual;

set long 50000
set pages 9999
select dbms_metadata.get_ddl('TABLE', 'DR$FT_TESTTEXT2MI') from dual;
select dbms_metadata.get_ddl('TABLE', 'DR$FT_TESTTEXT2$I') from dual;

Monday, June 03, 2013

How To Run Oracle Text Query From Sqlplus

Sometimes we need to debug some Text queries, we need to run them on sqlplus
Here are some examples:

set serveroutput on;
 ALTER SESSION SET EVENTS '30579 trace name context forever, level 2';

 text_result clob;
dbms_lob.createtemporary(text_result, true, DBMS_LOB.CALL);
 ctx_query.result_set('TEST_IDCTEXT2', 'computer',
 '<ctx_result_set_descriptor><count exact="false"/><group
 sdata="sdDrillDown"><count exact="true"/></group><hitlist start_hit_num="1"
 end_hit_num="2" order="dInDate Desc"><sdata name="dID"/><sdata
 name="sddDocName"/><rowid /></hitlist></ctx_result_set_descriptor>',

SELECT DDOCNAME  FROM idctext2 WHERE CONTAINS(otsmeta,  '((((DEFINESCORE((henry), RELEVANCE * .01)) ))) * 10.0 * 10.0')>0;

SELECT DDOCNAME  FROM idctext2 WHERE CONTAINS(otsmeta,  '((((DEFINESCORE((test), RELEVANCE * .1)) )))  and  (((((EMPL%) WITHIN
dDocAccount)) or (((CATEST1%) WITHIN dDocAccount)) or (((GIT/GLOBAL#0023IT#0023ONLY%) WITHIN dDocAccount)) or (((GIT/ALL%) WITHIN dDocAccount)) or (((idcnull) WITHIN dDocAccount)))) ')>0;

Sunday, June 02, 2013

Check Flashback Space Usage

A simple sql to show the percentage


How To Check Cardinality Of A Table

There are 2 db views you can get  cardinality  information

Check Num_Distinct and Density

The "Density" = "Num_Distinct" / "Total Number of Rows"

Please also pay attention when is the last analyzed time in the view.

Add Hidden Init DB Parameters

You need to add double quote for the parameters. examples:

alter system set "_external_scn_logging_threshold_seconds"=600 scope=spfile sid='*'; 
alter system set "_external_scn_rejection_delta_threshold_minutes"=10080 scope=both sid='*';

Tuesday, January 01, 2013

Can't Talk to WebLogic Admin Server After Enabling SSL

When we enable SSL for WebLogic Admin server login for security reasons,  we may have issues when other nodes can't talk to Admin server.

Thus  any changes on console can't populate to other nodes in Weblogic cluster.

We used to create a new jdbc datasource on weblogic console,  the jdbc config will not be copied to rest of nodes due to this issue.

And you will see such error on weblogic .out files

<Jan 1, 2013 6:11:38 PM CST> <Warning> <oracle.jps.idmgmt> <JPS-01520> <Cannot initialize identity store, cause: javax.naming.ServiceUnavailableException:; socket closed.>

<Warning> <JMX> <BEA-149510> <Unable to establish JMX Connectivity with the Adminstration Server AdminServer at <JMXServiceURL:null>.>

How to fix:
In Domain home
finds  ADMIN_URL="" 
which it should be ADMIN_URL="" 

UCM Service Can't Start Due To Jdbc Temp Table

UCM service can't start and stay in "Admin" mode in WebLogic Console
From weblogic .out file ,we can see such error

java.sql.SQLException: !csJdbcTempTableExists,USERSTemp,Users
        at intradoc.jdbc.JdbcWorkspace.alterTable(
        at intradoc.server.datastoredesign.DataDesignGenerator.alterTable(
        at intradoc.server.datastoredesign.DataDesignGenerator.generateTable(
        at intradoc.server.datastoredesign.DataDesignInstall.configTableForComponents(
        at intradoc.server.IdcExtendedLoader.dataStoreDesignConfigTables(
        at intradoc.server.IdcExtendedLoader.performDatabaseUpgradeTasks(

It's quite possible there was hiccup when we create weblogic DataSource or mis-communication  somewhere among UCM cluster. The temp was not deleted after test

we need to drop it manually on DB end to fix the issue