Script to gather the Apache, WLS and Apps log files

#####################################################################################
#### Start of script for EBS 12.1.x
#####################################################################################
(
# pick up files which have been modified
HowManyDaysOld=3
echo "Picking up files which have been modified in the last ${HowManyDaysOld} days"
set -x
find $LOG_HOME/ora/10.1.3 -type f -mtime -${HowManyDaysOld} > m.tmp
find $LOG_HOME/appl/admin -type f -mtime -${HowManyDaysOld} >> m.tmp
find $LOG_HOME/appl/rgf -type f -mtime -${HowManyDaysOld} >> m.tmp
zip -r AppsLogFiles_`hostname`_`date '+%m%d%y'`.zip -@ < m.tmp
rm m.tmp
) 2>&1 | tee mzLogZip.out
#####################################################################################
#### End of script
#####################################################################################

Script to List Materialized Views Dependent on Specific Tables

This script will report on any materialized views that are dependent on a specific table.

REM LOCATION:   Object Management\Materialized Views and Materialized View Logs
REM FUNCTION:   Find dependent tables for a given MView
REM TESTED ON:  10.2.0.3, 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   dbms_metadata
REM
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
SET serveroutput on
SET feedback off

DECLARE
   v_output   VARCHAR2 (2000);
   v_owner    VARCHAR2 (30);
   v_table    VARCHAR2 (30);
BEGIN
   v_owner := UPPER ('&&ENTER_OWNER_NAME');
   v_table := UPPER ('&&ENTER_TABLE_NAME');
   dbms_mview.get_mv_dependencies (v_owner || '.' || v_table, v_output);
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line
      ('Materialized Views Dependent on table      &&ENTER_OWNER_NAME..&&ENTER_TABLE_NAME'
      );
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line (v_output);
END;
/

Sample Output

 

Materialized Views Dependent on table      SCOTT.EMP

"SCOTT"."MV_TEST","SCOTT"."MV_EMP" 

or

select * from dba_dependencies where referenced_name='MSC_TRADING_PARTNERS' and TYPE='MATERIALIZED VIEW'; 

Refresh Snapshot Process Doesn't Start Because of Concurrent Manager Unavailability

When launching Collections, the Planning Data Pull fails with the following error: "Refresh Snapshot process could not get started after waiting for X minutes because of Concurrent Manager Unavailability". This can happen in a centralized instance when dblink information has been populated in msc_apps_instances inadvertently. When dblink information is populated, the Planning Data Pull tries to launch the refresh collection snapshot using the dblink information available, but it fails to find a connected instance, and finally times out.
To resolve, run the following sql:

SELECT instance_id,instance_code, 
nvl(a2m_dblink,'Null'),nvl(m2a_dblink,'Null') 
FROM msc_apps_instances ;

If the above sql returns a value for a2mdbling and m2adblink other than the value 'Null', then the setup is wrong, and you need to do the following:

1. Delete the records from the table MRP_AP_APPS_INSTANCES_ALL as follows:
   

   DELETE
   FROM mrp_ap_apps_instances_all
   WHERE instance_code = '&instance_code'; 

   Commit;

2. Go into the Advanced Supply Chain Planning Administrator responsibility.
   
3. Navigate to Admin > Instances.
   
4. Remove the dblink information. This will change the record in msc_apps_instances, mrp_ap_apps_instances_all and mrp_ap_apps_instance.
   
5. Confirm whether the dblink information has correctly populated as Null in the msc_apps_instances using the following sql:
   

   SELECT instance_id,instance_code,
   nvl (a2m_dblink,'Null'),nvl (m2a_dblink,'Null')
   FROM msc_apps_instances;

How to identify if a port is listening on a unix server

We often get into a situation wherein we have to check if a particular port is being used by some process.
 
Command

netstat -an | grep [port number] | grep LISTEN

Command to identify the OS process associated with a port

lsof -i tcp:[port number]

How You Can Reset The Sequence Number For The REQUEST_ID

goal: How You Can Reset The Sequence Number For The REQUEST_ID

fact: Oracle Application Object Library





fix:

A-Please ensure that you have a current system backup prior to performing this 
process.

1-Log in to SQLPLUS as apps/'apps password'.(If it doesn't work Use with SYSTEM user from DB node)

 2-SQL> drop sequence fnd_concurrent_requests_s; 

3-SQL> create sequence fnd_concurrent_requests_s 
  start with xxxx; 
 
4-Check the sequences again. 
    
     SQL> select max(request_id) from fnd_concurrent_requests;  
     from system.dual;    
                     
B-The value from step 4 should be bigger than value from step 3.

Archive generation over last one month

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23",
COUNT (*) TOTAL
from
v$log_history
where to_date(first_time) > sysdate -30
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

Script - Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)

Script - Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.0 and later
Information in this document applies to any platform.

GOAL

 This document is intended to check the current undo configuration and provides recommendation based on the previous workload.AUTION

  

This sample code is provided for diagnosis and troubleshooting purposes only, and is NOT supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

There are different scripts for database versions post 10g and prior to 10g. Ensure to execute the correct one to get the proper recommendation. Ensure to execute the script as SYS user.

SOLUTION

For database version 10g and above, use the following script. Ensure to execute the same as SYSDBA.

RDBMS version 10g and above:

SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

DECLARE
    v_analyse_start_time    DATE := SYSDATE - 7;
    v_analyse_end_time      DATE := SYSDATE;
    v_cur_dt                DATE;
    v_undo_info_ret         BOOLEAN;
    v_cur_undo_mb           NUMBER;
    v_undo_tbs_name         VARCHAR2(100);
    v_undo_tbs_size         NUMBER;
    v_undo_autoext          BOOLEAN;
    v_undo_retention        NUMBER(5);
    v_undo_guarantee        BOOLEAN;
    v_instance_number       NUMBER;
    v_undo_advisor_advice   VARCHAR2(100);
    v_undo_health_ret       NUMBER;
    v_problem               VARCHAR2(1000);
    v_recommendation        VARCHAR2(1000);
    v_rationale             VARCHAR2(1000);
    v_retention             NUMBER;
    v_utbsize               NUMBER;
    v_best_retention        NUMBER;
    v_longest_query         NUMBER;
    v_required_retention    NUMBER;
BEGIN
    select sysdate into v_cur_dt from dual;
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

    v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
    select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;

    DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
    DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
    DBMS_OUTPUT.PUT_LINE('End Time   : ' || v_analyse_end_time);
  
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
    DBMS_OUTPUT.PUT_LINE('--------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
    IF V_UNDO_AUTOEXT THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
    ELSE
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
    END IF;
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);

    IF v_undo_guarantee THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
    ELSE
        dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
    END IF;
    DBMS_OUTPUT.PUT_LINE(CHR(9));

    SELECT instance_number INTO v_instance_number FROM V$INSTANCE;

    DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
    DBMS_OUTPUT.PUT_LINE('---------------------------');

    v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
    DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
    DBMS_OUTPUT.PUT_LINE('-------------------------');

    v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
    IF v_undo_health_ret > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Minimum Recommendation           : ' || v_recommendation);
        DBMS_OUTPUT.PUT_LINE('Rationale                        : ' || v_rationale);
        DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
    END IF;
  
    SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
    SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
    SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');

END;
/

Sample Output

- Undo Analysis started at : 30/08/2013 11:08:40 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 23/08/2013 11:08:40
End Time   : 30/08/2013 11:08:40

Current Undo Configuration
--------------------------
Current undo tablespace                                 : UNDOTBS2
Current undo tablespace size (datafile size now)        : 20M
Current undo tablespace size (consider autoextend)      : 20M
AUTOEXTEND for undo tablespace is                       : ON
Current undo retention                                  : 900
UNDO GUARANTEE is set to                                : FALSE

Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 26 MB

Undo Space Recommendation
-------------------------
Minimum Recommendation           : Size undo tablespace to 26 MB
Rationale                        : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 26M

Retention Recommendation
------------------------
The best possible retention with current configuration is    : 9171 Seconds
The longest running query ran for                            : 2543 Seconds
The undo retention required to avoid errors is               : 2543 Seconds

PL/SQL procedure successfully completed.