Wednesday, November 17, 2010

Trace for database



-- These may be used to start tracing at database level
EXECUTE dbms_monitor.database_trace_enable;
EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE);
EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE);

-- This may be used to start tracing at instance level
EXECUTE dbms_monitor.database_trace_enable (instance_name=>’RAC1);

-- All outstanding traces can be displayed in an Oracle Enterprise Manager report or
with the DBA_ENABLED_TRACES or v$client_stats views.

-- In the DBA_ENABLED_TRACES view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether
the trace is enabled for client identifier, session, service, database, or a
combination of service, module, and action.


Hope this helps. Regards Rupam

Trace levels

# Oracle Trace  Levels

     0 - No trace. Like switching sql_trace off.
     2 - The equivalent of regular sql_trace.
     1 (SQL_TRACE)
     4 (SQL_TRACE+BINDS)
     8 (SQL_TRACE+WAITS)
     12(SQL_TRACE+BINDS+WAITS)

Hope this helps. Regards Rupam

Friday, November 12, 2010

Oracle Database Backup Report - v$rman_backup_job_details


/*
-- rman - Query using V$RMAN_BACKUP_JOB_DETAILS is taking too long to execute both in SQL*Plus and also in DATABASE CONSOLE.
-- doc : 420200.1
*/
Steps :-
1. sqlplus as sysdba
2. execute following procedure

Sql> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); # deletes the statistics on the fixed object.
Sql> exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');   # lock that object so that statistics will not be collected in future.
  
Hope this helps. Regards Rupam

Wednesday, November 10, 2010

Database usage

Database feature usage report 

Method 1
1. Login to oem
2. Select database
3. Select server tab
4. Select database Feature Usage View

Method 2
1. sqlplus as system
2. execute following sql statement
SELECT output
FROM TABLE(dbms_feature_usage_report.display_text);


Hope this helps. Regards Rupam

Friday, November 05, 2010

List CPU patch applied to database

List CPU patch applied to database 

1. sqlplus  as system
2. execute the following sql statement
  set linesize 90 
 
  col action format a7
  col version format a10
  col id format 99999
  col when format a20

  select d.name,to_char(r.action_time,'YYYY/MM/DD.hh24:mi')    when,r.action,r.version,r.id,r.bundle_series
  from v$database d left outer join dba_registry_history r
  on r.bundle_series in ('CPU','PSU');


Hope this helps. Regards Rupam