Saturday, 29 March 2014

Redo log files Generated per day/hour scripts


Purpose
Command
How to pass parameter in  a shell script
EFTFILE=`sqlplus -s au_check/$PASS
<<
SQL_END
set pagesize 0
set heading off
set feed off
SELECT 'o'||
     SUBSTR(a.argument_text,1,length(a.argument_text)) ||
      '.out'
 FROM applsys.fnd_concurrent_requests a
WHERE request_id = $1
/
exit;
SQL_END`;
Formatting sqlplus output
set linesize 1000
column profile_option_value heading "PROFILE|OPTION VALUE" format a50
column profile_option_name format a50 heading "PROFILE|OPTION NAME"
column profile_option_id heading "PROFILE|OPTION ID"
select distinct profile_option_value,level_id,profile_option_id
from fnd_profile_option_values
where profile_option_value like '%&profile_value%'  and level_id=10007
order by profile_option_id
/

PROFILE                                                          PROFILE
OPTION VALUE                                             LEVEL_ID  OPTION ID
--------------------------------------------------           ----------      ----------
http://sitename.domainname.com/pls/envname         10007       2353
Finding Redo log Gap
On primary database: select thread#,max(sequence#)
from v$archived_log group by thread#;
           
On standby database:
select thread#,max(sequence#),applied  from v$archived_log
where applied='YES' group by thread#,applied;


An alternative to running the two commands above is to run the following command on the standby database:

select * from v$dataguard_stats where name = 'apply lag';
Redo log Details generated Per Day
select  RedoCount,round(Redo_Size_GB+0.5),sysdate - 30 From_Date, sysdate To_Date
from (
SELECT COUNT (*) RedoCount,TRUNC(first_time) "Date",
ROUND(SUM(blocks*block_size)/1024/1024/1024,2) Redo_Size_GB
from v$archived_log
where (TRUNC(first_time) > sysdate - 30)
AND standby_dest = 'NO'
GROUP BY TRUNC(first_time)
order by 3 desc)
where rownum<=1;
Redo log Details generated Per hour
select round(avg(Redo_COUNT_Per_Hour)) Avg_Redo_COUNT_Per_Hour,
max(Redo_COUNT_Per_Hour) max_Redo_COUNT_Per_Hour,
ROUND(avg(Amt_Redo_GB_per_Hour),2) avg_Amt_Redo_GB_per_Hour,
max(Amt_Redo_GB_per_Hour) Max_Amt_Redo_GB_per_Hour
from
(
SELECT TO_CHAR(FIRST_TIME,'HH24 DD-MON-YY') "Hour / Day",
   COUNT(*) Redo_COUNT_Per_Hour,
      ROUND(SUM(blocks*block_size)/1024/1024/1024,2) Amt_Redo_GB_per_Hour
FROM v$archived_log
WHERE
(TRUNC(first_time) > sysdate - 30)
-- TO_CHAR(FIRST_TIME,'DDMMYY') > '301112'
AND standby_dest = 'NO'
GROUP BY TO_CHAR(FIRST_TIME,'HH24 DD-MON-YY');

Six Sigma Yellow Belt Trainig

For a change, I'm attending Six Sigma Yellow Belt Training sponsored by my Organization.

Interesting to see in BI Analytics they use the same
  • Cause & Effect Diagrams or FishBorne Diagram
  • Pareto Analysis

Oracle Financial Analytics and Oracle Procurement & Spend Analytics

Attended this excellent session on Business Analytics from Andy G and Madhur Hemnani

http://www.oracle.com/us/dm/251133-emeafs13055705mpp007-se-2065294.html