Query to find Trace Enabled Programs in Oracle EBS

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

Query to check Excluded and Included Concurrent Program

SELECT a.include_flag, a.queue_application_id , c.user_concurrent_queue_name,
       d.user_concurrent_program_name
  FROM applsys.fnd_concurrent_queue_content a,
       applsys.fnd_concurrent_programs b,
       apps.fnd_concurrent_queues_vl c,
       fnd_concurrent_programs_tl d
 WHERE type_id = b.concurrent_program_id
  AND c.concurrent_queue_id = a.concurrent_queue_id
   AND b.concurrent_program_id = d.concurrent_program_id
   AND d.user_concurrent_program_name LIKE '%Cree%'

Query to check average run time of concurrent Program (Oracle EBS)

SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"    
  FROM applsys.fnd_concurrent_programs_tl  tl,
       applsys.fnd_concurrent_requests     rq
 WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'Cree - (Wo) Camstar Interface for WO and BOM Details'  -- <change it>
   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date
 ORDER BY rq.request_id DESC;

Query to identify concurrent_request_run_by_which_concurrent_manager (Oracle EBS)

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (fcqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (fcqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'

Shell script to Automate Alert log errors on daily basis (Mailing ORA- errors to DBA )

#!/bin/ksh
ORACLE_HOME=/p01/prod/db/tech_st/11.2.0.4
export ORACLE_HOME
ORACLE_SID=PROD
export ORACLE_SID
echo $ORACLE_HOME
echo $ORACLE_SID
x=$(date +%Y_%m_%d_%H%M%S)
/p01/prod/db/tech_st/11.2.0.4/bin/sqlplus -s / <<EOF
sho parameter db_name
exit;
EOF
if [[ $? -eq 0 ]]; then
#mailx -s "PROD instance is  up !!! "  xyz@gmail.com</tmp
echo "PROD instance is  up !!! "
else
mailx -s "PROD instance is not up !! " xyz@gmail.com</tmp
fi

cd /p01/prod/db/tech_st/11.2.0.4/admin/PROD_scdoraprd/diag/rdbms/prod/PROD/trace/
rm alrt_tmp.log
touch alrt_tmp.log
grep -i ORA- alert_PROD.log  >> alrt_tmp.log

mv alert_PROD.log   alert_PROD.log$x

if [[ $? -eq 0 ]]; then
mailx -s "Alert log reported errors in PROD !!! "  xyz@gmail.com<alrt_tmp.log
fi

Shell script to Automate AWR report (to mail Oracle awr performance reports daily for peak periods )

Cat daily_awr_gen.sh

#!/bin/ksh
ORACLE_HOME=/app/11.2.0/prdobi
export ORACLE_HOME
ORACLE_SID=prodobi
export ORACLE_SID

/app/11.2.0/prdobi/bin/sqlplus -s / as sysdba << EOF

create or replace function snap_values (p_snap_range  varchar2)
return number
as
l_min_snap_id number;
l_max_snap_id number;
begin
SELECT min(snap_id),max(snap_id)  into l_min_snap_id,l_max_snap_id
FROM dba_hist_snapshot
WHERE trunc(begin_interval_time )= trunc(SYSDATE )
and trunc(end_interval_time) = trunc(SYSDATE )
and (to_char(begin_interval_time,'hh24') >= 10 and to_char(begin_interval_time,'hh24') <=16)
order by begin_interval_time;
if p_snap_range='MIN'  then
return l_min_snap_id;
elsif p_snap_range='MAX' then
return l_max_snap_id;
else
return null;
end if;
exception when others then
return null;
end;


EOF
echo $dbid
echo $db_name
echo $inst_num
echo $inst_name
echo $num_days

#repnam='awrrpt_'$inst_num'_'$inst_name'.html'

rm -rf awr_report_*.*

/app/11.2.0/prdobi/bin/sqlplus -s / as sysdba << EOF
    define report_type=html
    define num_days=1
    @/home/obiee/snu/a.sql
    @?/rdbms/admin/awrrpt
EOF

if [[ $? -eq 0 ]]; then
#mailx -s "AWR report for PRODOBIEE for Today !!! " -a awr_report_today.*  giriraj_inja@cree.com</tmp
mutt -s "AWR Report on PRODOBIEE for today " xyz@gmail.com -a awr_report*.*</tmp
else
mailx -s "PRODOBIEE instance is not up !! " xyz@gmail.com</tmp
fi


cat a.sql
set feedback off
set heading off
    define report_type=html
    define num_days=1
    define report_name=awr_report_today.html

spool /home/obiee/snu/b.sql
select 'define begin_snap=' || snap_values('MIN') from dual;
select 'define end_snap=' || snap_values('MAX') from dual;
spool off
@b.sql

cat b.sql  (This gets generated from a.sql automatically)

define begin_snap=13507                                                         

define end_snap=13509             

Recovering Standby Database from corrupt data file

Found the following errors in alert logfile of standby database

ORA-10567: Redo is inconsistent with data block (file# 11, block# 83962, file offset is 687816704 bytes) 
ORA-10564: tablespace SYSAUX 
ORA-01110: data file 11: '/p02/prod/proddata/sysaux01.dbf' 
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 13366 

We realized that file no 11 of sysaux tablespace was corrupted

So we checked the health status of the object on Primary for the same by querying and found that they were healthy

SELECT owner, segment_name, segment_type, tablespace_name 
FROM dba_extents 
WHERE file_id = 11 
and 83962 between block_id AND block_id + blocks - 1; 

SQL>select owner,object_name,object_type,subobject_name,status,temporary, object_id,data_object_id from dba_objects where object_id = 13366; 

Primary database:

alter tablespace sysaux begin backup;

Standby database:

Verify the archive gap on standby database with following query

SELECT SEQUENCE#, applied FROM V$ARCHIVED_LOG order BY sequence# desc;

Shut immediate 

  Primary database:

scp /p02/prod/proddata/sysaux01.dbf  prod@scdebsdbrpt01:/p02/prod/proddata/sysaux01.dbf 

alter tablespace sysaux end backup;

Standby database :

Startup nomount pfile='$ORACLE_HOME/dbs/initprod.ora';

Alter database mount standby database;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

SELECT database_role, open_mode FROM v$database;

Following Queries can be used to find the archive gap between Primary and standby database:

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES'

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc

SELECT SEQUENCE#,applied FROM V$ARCHIVED_LOG order by SEQUENCE# desc

Check for the MRP process status which is responsible for applying the logs with below query :

select process "MRP PROCESS Status" ,status,sequence#

  from v$managed_standby where PROCESS like '%MRP%';

Check for Log seq# applied on PRIMARY vs Standby Database

select  almax "Last Seq Received from PROD", lhmax "Last Seq Applied on PRODRPT"

from (select thread# thrd, max(sequence#) almax

      from v$archived_log

      where resetlogs_change#=(select resetlogs_change# from v$database)

      group by thread#) al,

     (select thread# thrd, max(sequence#) lhmax

      from v$log_history

      where first_time=(select max(first_time) from v$log_history)

      group by thread#) lh

where al.thrd = lh.thrd;