Wednesday, July 18, 2012

Oracle RAC - OCR Backups

The backups are taken on the OCR Master node. The OCR Master can change over time. 1. The default OCR master is always the first node that's started in the cluster. 2. When OCR master (crsd.bin process) stops or restarts for whatever reason, the crsd.bin on surviving node with lowest node number will become new OCR master.

Monday, July 2, 2012

Object and Tablespace I/O

The V$SEGMENT_STATISTICS view can be used to gather the statistics you need on access patterns of database segments. To find the database segments that incur the most I/O, use a query similar to the following: select owner,object_name,tablespace_name,sum(value) as total_io_operations from v$segment_statistics where statistic_name in ('physical reads','physical reads direct', 'physical writes','physical writes direct') group by owner,object_name,tablespace_name order by total_io_operations;

Wednesday, March 7, 2012

SQL Query Optimizer



SQL Query Optimizer

Very interesting reading if you want to know how Oracle Processes the SQL statements and deliver the results back.

http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i21299

Monday, January 23, 2012

SQL to find RMAN Backup Duration



select TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') Start_Time,
TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') End_Time , INPUT_TYPE, round(ELAPSED_SECONDS/60) MINUTES
from v$rman_backup_job_details order by Start_Time asc
/

START_TIME END_TIME INPUT_TYPE MINUTES
------------------------------ ------------------------------ ------------- ----------
2012-01-02 01:00:24 2012-01-02 01:43:34 DB INCR 43
2012-01-02 18:01:07 2012-01-02 19:10:20 ARCHIVELOG 69
2012-01-06 14:52:48 2012-01-06 20:11:54 DB FULL 319