Oracle database Useful Redo Log Scripts

Oracle 10g documentation
Oracle 11g Release 1 documentation
Oracle 11g Release 2 documentation

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');