Monday, December 29, 2014

Get Failed ODI Messages

The following script is to get some information of failed package when we run it in ODI. This information should be saved by an ODI variable and we can pass it to ETL process and to capture the error messages sent out by the ODI server. These messages are also good in case we want to show it in the alert email and we don't need to open ODI Operator.

--Create a Global Variable called “ GET_ERROR_MSG” and paste the following codes
SELECT ' The  '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM <%=odiRef.getObjectName("SNP_SESSION")%> SS,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> SSS,
<%=odiRef.getObjectName("SNP_EXP_TXT")%> SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
AND SNET.TXT_ORD=0
AND SS.PARENT_SESS_NO ='<%=odiRef.getPrevStepLog("SESS_NO")%>'

Get Oracle Database basic information

1. Name/ Versions



set heading off
set verify off
column today NEW_VALUE p_currdate noprint 
select TO_CHAR(SYSDATE,'fmMonth ddth, yyyy') today from   dual; 
clear breaks
clear computes
clear columns 
set heading off
column name heading '' justify center format a74 
select   'Database Name (SID): ' || name "name" from     v$database; 
prompt
prompt
prompt Version/SGA Information: 
set heading off
select * from v$version; 
column sgatot justify left format 9,999,999,999 
select   'Total System Global Area as of &p_currdate.:' hd1,          sum(value) sgatot, 'bytes' hd2 from     v$sga; 

  2. Tablespace/ Data files

prompt
prompt
prompt Tablespaces and Datafiles: 
clear breaks
clear computes
clear columns 
set heading on
column tablespace_name heading 'Tablespace' justify left format a15
column file_id heading 'File|ID' justify center format 9999
column file_name heading 'Datafile' justify center format a60 word_wrapped
column size Heading 'Size|in MB.' justify center format 9,99990.99 
break on tablespace_name skip 1 on report skip 2
compute sum label 'TS SIZE:' of size on tablespace_name
compute sum label 'DB SIZE:' of size on report 
select tablespace_name,        file_id,        file_name,       
bytes/1024/1024 "size" from dba_data_files order by tablespace_name, file_id, file_name; 

3. Space Usage

prompt Space usage (as of &p_currdate.):
prompt 
clear breaks
clear computes
clear columns 
set heading on
column tspace heading 'Tablespace' justify left format a20 truncated
column tot_ts_size heading 'Size|in MB.' justify left format 9,99999999990
column free_ts_size heading 'Used|in MB.' justify right format 9,9999999990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6 
break on report
compute sum label 'Totals:' of tot_ts_size free_ts_size on report 
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"

SELECT df.tablespace_name tspace,
       df.bytes/(1024*1024) tot_ts_size,
       sum(fs.bytes)/(1024*1024) free_ts_size,
       round(sum(fs.bytes)*100/df.bytes) ts_pct,
       round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
     (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;


4. Redo Log Files

prompt
prompt
prompt Online Redo Logfiles: 
clear breaks
clear computes
clear columns 
column member heading 'Logfile' justify center format a60 word_wrapped
column group heading 'Group|Number' justify center format 9999
column size heading 'Size|in MB.' justify center format 999990.99 
select f.member "member",        f.group# "group",        l.bytes/1024/1024 "size" from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;


5. Control Files

prompt
prompt
prompt Control files: 
clear breaks
clear computes
clear columns 
column name heading 'File Name' format a60 word_wrapped 
select name from v$controlfile; 


6. Rollback Segments

prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.): 
clear breaks
clear computes
clear columns 
set heading on
column tablespace_name heading 'Tablespace' justify left format a15 truncated
column segment_name heading 'Seg|Name' justify center format a7
column status heading 'Status' justify center format a8
column initial_extent heading 'Initial|(in M)' justify center format 99990.9
column next_extent heading 'Next|(in M)' justify center format 99990.9
column min_extents heading 'Min|Ext' justify center format 99990
column max_extents heading 'Max|Ext' justify center format 999999999990
column pct_increase heading 'Pct|Inc' justify center format 99990
column rbsize heading 'Curr Size|(in M)' justify left format 9,99990 
break on tablespace_name skip 1 on report skip 2 
select    r.tablespace_name,           r.segment_name,           r.status,   
 r.initial_extent/1024/1024 "initial_extent", 
 r.next_extent/1024/1024 "next_extent",
 r.min_extents,           r.max_extents,     
 r.pct_increase,     
sum(e.bytes)/1024/1024 "rbsize"
from   dba_rollback_segs r, dba_extents e
 where     e.segment_name = r.segment_name
 group by  r.tablespace_name, r.segment_name, r.status,
           r.initial_extent/1024, r.next_extent/1024,
           r.min_extents, r.max_extents, r.pct_increase;


7. Parameters

prompt
prompt Parameters (non-defaults): 
clear breaks
clear computes
clear columns 
column name heading 'Name' format a35 word_wrapped
column pvalue heading 'Value' format a50 word_wrapped 
select name, rtrim(value) "pvalue" from v$parameter order by name; 


8. User Details


prompt
prompt user details : 
clear breaks
clear computes
clear columns 
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;
spool off