TIP #23 : How to find which Oracle inventory is for which Oracle home.

Using correct inventory when you try to apply a patch or installing new software is vital.In order to find which existing Oracle inventory is for which Oracle home , follow the following steps :

- Find orainst.loc (it is in /etc or in /opt/var/oracle) . This file determines inventory location.
- Find inventory.xml file in {inventory_location}/ContentsXML.This file list all Oracle homes which this inventory has.
- This gives you better idea whether or not the existing inventory is the right inventory for Oralce home.

TIP #22 : Statistics gathering in Oracle 10g.

In 10g there is Auto stats gathering job which is called GATHER_STATS_JOB.This job is scheduled in dba_scheduler_jobs and is in the charge of gathering statistics automatically.
For having updated statistics, the following conditions should be met.
1. GATHER_STATS_JOB should be enabled.
2. statistics_level should be TYPICAL or ALL.

Monitoring/Nomonitoring is deprecated in 10g and as long as statistics_level is not BASIC, Oracle will track modifications.

In 10g , if optimizer_dynamic_sampling is 2 or higher, it means that if Oracle run query against table without any stats, it dynamically tries to gather stats on that.

TIP #21: Shrink datafiles

Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

--- to shrink datafiles:

select bytes/1024/1024 real_size,ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) shrinked_size,
bytes/1024/1024-ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) released_size
,'alter database datafile '|| ''''||file_name||'''' || ' resize ' || ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) || ' m;' cmd
from
dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b
where
tablespace_name='&ts_name'
and
a.file_id = b.file_id(+)
and ceil(blocks*&DB_BLOCK_SIZE/1024/1024)- ceil((nvl(hwm,1)* &DB_BLOCK_SIZE)/1024/1024 ) > 0;


--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.

select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;

TIP #20: Using asmcmd

ASMCMD is command line tool provided by Oracle for managing ASM.
This tool is handy.
Please be aware that this tool does not work unless ORACLE_SID points to ASM instance.Therefore make sure to set ORACLE_SID (which would be ASM instance name) before using this tool.

TIP #19: handy queries for Stream administration (Apply process)

Handy queries for apply administration of stream.

--- Info about apply process
select * from dba_apply;

SELECT APPLY_NAME,QUEUE_NAME,RULE_SET_NAME,DECODE(APPLY_CAPTURED,'YES', 'Captured','NO', 'User-Enqueued') APPLY_CAPTURED,STATUS
FROM DBA_APPLY;

--- Find out apply process status
select apply_name,status
from dba_apply;
exec dbms_apply_adm.start_apply(apply_name=>'&apply_name');
exec dbms_apply_adm.stop_apply(apply_name=>'&apply_name');

--- Apply parameter

SELECT PARAMETER,VALUE,SET_BY_USER
FROM DBA_APPLY_PARAMETERS
WHERE APPLY_NAME = '&apply_parameter';

BEGIN

DBMS_APPLY_ADM.SET_PARAMETER(apply_name => '&apply_parameter',parameter => 'commit_serialization',value => 'none');
END;
/
-- Determine any instantiation
-- Instantiation SCN is SCN that apply process discard any SCN lower than or equal and commit any SCN higher than that.

SELECT SOURCE_DATABASE,SOURCE_OBJECT_OWNER,SOURCE_OBJECT_NAME,
INSTANTIATION_SCN
FROM DBA_APPLY_INSTANTIATED_OBJECTS;

--- Find out about any handler -- Any DML handler
SELECT OBJECT_OWNER,OBJECT_NAME,OPERATION_NAME,USER_PROCEDURE,
DECODE(ERROR_HANDLER,'Y', 'Error','N', 'DML') ERROR_HANDLER
FROM DBA_APPLY_DML_HANDLERS
WHERE APPLY_DATABASE_LINK IS NULLORDER BY OBJECT_OWNER, OBJECT_NAME, ERROR_HANDLER;

-- Any DDL/Message handler
SELECT APPLY_NAME, DDL_HANDLER, MESSAGE_HANDLER FROM DBA_APPLY;

-- Using key substitution for any table
-- Key substitution is useful when there is no PK for distinguishing rows.
SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK
FROM DBA_APPLY_KEY_COLUMNS
ORDER BY APPLY_DATABASE_LINK, OBJECT_OWNER, OBJECT_NAME;

-- Update conflict handler
SELECT OBJECT_OWNER,OBJECT_NAME,METHOD_NAME,RESOLUTION_COLUMN,
COLUMN_NAME
FROM DBA_APPLY_CONFLICT_COLUMNS
ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;

-- Reader servers
-- In parallel servers, reader server dequeues data and translates them to transactions,It also checks dependencies, Delivers all data to coordinator
SELECT DECODE(ap.APPLY_CAPTURED,'YES','Captured LCRS','NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,r.STATE,r.TOTAL_MESSAGES_DEQUEUED
FROM V$STREAMS_APPLY_READER r, V$SESSION s, DBA_APPLY ap
WHERE r.APPLY_NAME = '&apply_name' AND r.SID = s.SID AND r.SERIAL# = s.SERIAL# AND r.APPLY_NAME = ap.APPLY_NAME;

-- Check out latency
-- Capture to dequeue latency
-- Latency : =(Dequeue_time in dest database - event creation in source DB)
-- Creation: = Time when redo log generated in source database / time user-message enqueued
-- Message number = MSG number which was dequeued.
SELECT (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,DEQUEUED_MESSAGE_NUMBERFROM V$STREAMS_APPLY_READERWHERE APPLY_NAME = '&apply_name';

--- Capture to apply latency
SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') "Event Creation",TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",HWM_MESSAGE_NUMBER "Applied Message Number"
FROM V$STREAMS_APPLY_COORDINATOR
WHERE APPLY_NAME = '&apply_name';
OR
SELECT (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')"Event Creation",TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",APPLIED_MESSAGE_NUMBER "Applied Message Number"

FROM DBA_APPLY_PROGRESSWHERE APPLY_NAME = '&apply_name';

--- Apply coordinator
select * from v$streams_apply_coordinator;

------ Effectiveness of parallelism in apply process.
SELECT COUNT(SERVER_ID) "Effective Parallelism"
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'STRMADMIN_ORA9IPR_US_ORAC' ANDTOTAL_MESSAGES_APPLIED > 0;
-- How many message applied by each apply process

SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'STRMADMIN_ORA9IPR_US_ORAC'ORDER BY SERVER_ID;
--- Find apply error -- Find out apply error reason, two options : reexecute transaction, delete transaction.
select * from apply_error;
-- Find out progpagation rule set
SELECT RULE_SET_OWNER, RULE_SET_NAMEFROM DBA_applyWHERE apply_NAME = '&apply_name';

select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,

TABLE_OWNER'.'TABLE_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_TABLE_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
SCHEMA_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
null,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name);

-- Which DML/DDL rules the capture process is capturing
select * from "DBA_STREAMS_TABLE_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name and apply_name='&apply_name');
-- Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='APPLY' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name and apply_name='&apply_name');
-- Which global rules the capture process is capturing
select * from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_apply b where a.rule_set_name=b.rule_set_name and apply_name='&apply_name');

TIP #18: handy queries for Stream administration (Propagation process)

Basically in propagation, two views are important : DBA_QUEUE_SCHEDULES and
DBA_PROPAGATION.
The followings are handy statements for propagation administration of stream

--- Disable propagation
begin
dbms_aqadm.disable_propagation_schedule('STRMADMIN.STREAMS_QUEUE', 'OEMREP.US.ORACLE.COM');
end;
/
--- Enable propagation
begin
dbms_aqadm.enable_propagation_schedule('&source_queue_name_with_owner', '&database_link');
end;
/
-- Info about propagation
select * from dba_propagation;

-- Find out source and destination propagation
SELECT p.SOURCE_QUEUE_OWNER '.'p.SOURCE_QUEUE_NAME
'@'g.GLOBAL_NAME "Source Queue",p.DESTINATION_QUEUE_OWNER '.'p.DESTINATION_QUEUE_NAME '@'p.DESTINATION_DBLINK "Destination Queue"FROM DBA_PROPAGATION p, GLOBAL_NAME g;

-- Find propagation parameters
SELECT s.*FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION pWHERE p.PROPAGATION_NAME = 'STRMADMIN_PROPAGATE'AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;

---- How to change parameters
-- Propagation job sets to 15min to propagates events every 15 minutes
-- Each propagation lasting max 300 second
-- 25 second wait before new events in a completely propagated queue are propagated
BEGIN
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(queue_name => '&source_queue_name',destination => '&database_link_name',duration => 300,next_time => 'SYSDATE + 900/86400',latency => 25);
END;
/

-- Find out progpagation rule set
SELECT RULE_SET_OWNER, RULE_SET_NAMEFROM DBA_PROPAGATIONWHERE PROPAGATION_NAME = '&propagation_name';

select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
TABLE_OWNER'.'TABLE_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_TABLE_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name)union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
SCHEMA_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name)union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
null,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name);

-- Which DML/DDL rules the capture process is capturing
select * from "DBA_STREAMS_TABLE_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');

-- Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES" where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');

-- Which global rules the capture process is capturing
select *

from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');

------ More about propagation job
SELECT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,s.PROPAGATION_WINDOW,s.NEXT_TIME,s.LATENCY,

DECODE(s.SCHEDULE_DISABLED,'Y', 'Disabled','N', 'Enabled') SCHEDULE_DISABLED,s.PROCESS_NAME,s.FAILURES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = '&propagation_name'
AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;

-------- Total number of bytes which was propagated.
SELECT s.TOTAL_TIME_IN_sec, s.TOTAL_NUMBER, s.TOTAL_BYTES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = '&propagation_name'
AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;

TIP #17 : Handy queries for Stream administration (Capture process)

I found the following sql statements handy for capture administration in Stream :

-- If these views have no information, it might be because of capture process status .
-- Check capture status
select * from dba_capture;
exec dbms_capture_adm.stop_capturecapture_name=>'&CAPTURE_NAME');
exec dbms_capture_adm.start_capturecapture_name=>'&CAPTURE_NAME');

-- To check captured SCN and applied SCN
-- Applied SCN is the most recent SCN which was dequeued with relevent apply process.
-- This SCN is important because all redo logs should be kept until apply process.
select * from dba_capture;

-- To view parameter :
select * from dba_capture_parameters;

-- To change parameter :
-- Value of parameter always should be Varchar2 even if the value parameter is number.
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name => 'STRMADMIN_CAPTURE', parameter => '_CHECKPOINT_FREQUENCY',value=>1);
DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name => 'STRMADMIN_CAPTURE', parameter => '_SGA_SIZE',value=>20000000);
DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name => 'STRMADMIN_CAPTURE', parameter => 'PARALLELISM',value => '3' );
END;
/

--- Changing _SGA_SIZE may cause ORA-01341: LogMiner out-of-memory problem.Be careful of this value.Its default value is 10MB.
-- To view statistics about capture process :

select * from V$STREAMS_CAPTURE;

-- To find any error relates to capture process
-- Check alter log.

-- Enable trace level of capture process :
select * from dba_capture_parameters;
exec dbms_capture_adm.set_parameter('&capture_name','trace_level','2');

-- To find out which rules capture process is capturing :
-- Brief capture rules :
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,

TABLE_OWNER'.'TABLE_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_TABLE_RULES"
where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,

SCHEMA_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name)
union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,

null,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name);

-- Which ruleset is working for capture process
select rule_set_name from dba_capture where capture_name='&capture_name';

-- Which rules are in ruleset name
select * from "DBA_RULE_SET_RULES" where rule_set_name='&rule_set_name';

-- Which DML/DDL rules the capture process is capturing
select * from "DBA_STREAMS_TABLE_RULES" where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name and capture_name='&capture_name');

-- Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES" where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name and capture_name='&capture_name');

-- Which global rules the capture process is capturing
select * from "DBA_STREAMS_GLOBAL_RULES" where streams_type='CAPTURE' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_capture b where a.rule_set_name=b.rule_set_name and capture_name='&capture_name');

---Capture process latency
--- Latency_second is the number of seconds between when an event was recorded in the redo log and when the event was enqueued by the capture process.

--- The event creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information for the most recently enqueued event.
--- The enqueue time, which is when the capture process enqueued the event into its queue.

SELECT (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS,TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,ENQUEUE_MESSAGE_NUMBERFROM V$STREAMS_CAPTUREWHERE CAPTURE_NAME = '&capture_name';

--- The redo log scanning latency, the number of seconds between the creation time of the most recent redo log event scanned by a capture process and the current time.
--- The seconds since last recorded status, which is the number of seconds since a capture process last recorded its status--- The current capture process time, which is the latest time when the capture process recorded its status.
--- The event creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information for the most recently captured event.

SELECT ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIMEFROM V$STREAMS_CAPTUREWHERE CAPTURE_NAME = '&capture_name';


TIP #16 : Oracle is 32bit or 64bit

Determining whether installed oracle is 32bit or 64bit is easy task.However, it is more difficult if Oracle_home has not any database.
In this case for determining oracle bit number, try one of the following options :

1. Use file command
  • cd $ORACLE_HOME/bin
  • file oracle
  • if it does not show anything, Oracle is 32-bit. If it is 64bit, it shows 64bit tag

2. svrmgrl

  • cd $ORACLE_HOME/bin
  • svrmgrl
  • If it is 64bit, it is presented in svrmgrl message.If nothing appreas, it is 64bit
  • This works only for 8i.

3. Do not use sqlplus

  • When no database has not been already installed, sqlplus will not give you correct answer.
  • However, if database has already existed for ORACLE_HOME,sqlplus message works the same as svrmgrl.

TIP #15: CPU patch for 32-bit Oracle home on 64-bit platform

As you know, you can install 32-bit Oracle on 64-bit platform.So far so good.The main question is that how to apply CPU patch for this specific combination.
Regarding to metalink patch link, you only require to choose OS platform. (for example :HP-UX 64bit or HP-UX 32bit).
However, I figured out that Oracle32-bit can not be patched up on 64-bit OS if CPU patch is for 64-bit platform. As practical example, I tried to apply Oct2006 CPU patch to 32-bit Oracle home on 64-bit HP box.CPU Patch for 64-bit HP did not work (Many errors : Could not find $ORACLE_HOME/lib64) while CPU Patch for 32-bit HP worked properly !

Therefore, In the selection of proper CPU patch, 32-bit or 64-bit of Oracle home should be considered.(Do not fool with platform selection box in metalink)