Tuesday, November 26, 2013

Enabling Trace for reports in R12

This blog explains how to enable a trace for concurrent programs that run rdf reports to generate output.


Enabling Trace for reports in R12

Enable trace, in the report, so the command used to submit it will appear in the log file.
Using the System Administrator responsibility navigate to the Define Concurrent Program Screen and query the report. Check the box next to Enable Trace.
Submit the report from the Concurrent Manager and view the log file for the NLS_LANG Parameter and the command used to run the report.

Example:
A.Submit Concurrent program from "Submit New Request"
B.When the request completes click on the View Log button.
Copy the lines between -- Start of Reports Command --
                              and -- End of Reports Command --


Arguments
------------
p_mag_assign_action_id='882414262'
------------


 -- Start of Reports Command --
/u01/oracle/test/inst/apps/test_host/ora/10.1.2/bin/appsrwrun.sh
P_CONC_REQUEST_ID=1340141
p_mag_assign_action_id='882414262'
report=/u01/oracle/test/apps/apps_st/appl/pip/12.0.0/reports/US/TEST.rdf
batch=yes
destype=file
desname=/u01/oracle/test/inst/apps/TEST_host/logs/appl/conc/out/o1340141.out
desformat=XML
 -- End of Reports Command --


Verify the Forms CATCHTERM variable and be sure it is set to 0. If the CATCHTERM variable is not set to 0 (zero) a core file will not be created.
Example:
echo $FORMS_CATCHTERM
export FORMS60_CATCHTERM=0

Verify your DISPLAY setting: echo $DISPLAY
If DISPLAY is not set: export DISPLAY=

Convert to REP to prevent any compilation issue as below
 REP-1247: The report contains uncompiled PL/SQL.
 $ORACLE_HOME/bin/rwconverter.sh userid=apps/password@apps source=/u01/oracle/test/apps/apps_st/appl/pip/12.0.0/reports/US/HBPY_PDF_PAY_SLIP.rdf stype=RDFFILE dtype=RDFFILE compile_all=yes batch=yes

Run the report from the command line with TRACE Option as follows:

$INST_TOP/ora/10.1.2/bin/appsrwrun.sh userid=apps/appsnft@hrcmnft p_mag_assign_action_id='882414262' report=/home/oracle/onlinepdf.rep batch=yes destype=file desname=/home/hrcmpll/payslip.out desformat=XML traceopts=trace_all tracefile=/home/hrcmpll/payslip.trc tracemode=trace_replace

Here default is trace_all (Log all possible trace information in the trace file)

TRACEFILE= Any valid file name including the full path to the file.

TRACEMODE= TRACE_APPEND Adds the new information to the end of the file and TRACE_REPLACE Overwrites the file

Wednesday, November 20, 2013

Anatomy of adpatch for ebs applicaitions

Adpatch Explained

Brief
As an application DBA we apply patches regularly on EBS environments and these patches might include one-off fixes,mini packs, family packs,etc., These patches might fix the exiting issue but it might be again vulnerable for new bug. So evry patch should be thoroughly tested and then should be moved to PROD.

But many of the DBA's are not aware of the structure of adpatch, its metadata and minimum requirements for the patch to be applied.

Below are the minimum files available in patch zip file and these are mandatory for applying any adpatch.

1) b<bug_number>.ldt - This file contains the bug metadata information and is used to  upload the bug metadata to the database after applying the patch and is based on afumsaru.lct
2) f<bug_number>.ldt - This file is used to upload the details of file versions changed or new files plugged into ebs as part of patching
3) marker1.txt  - Contains the name of patch zip file
4) patch_metadata.xml - This file contains information of patch contents and action steps to be performed. This is the master copy that will be used by adpatch to apply the patch
5) README.txt/README.html - These contain the instructions of applying adpatch to application
6) u<bug_number>.drv - This is the unified driver which contains various instructions of copy files,compiling objects,forms,etc.,
7) j<bug_number>_per.zip - This zip file may be included in Java patches. A Zipped Resource Unit or ZRU. jcopy command is used to unified driver to apply this j driver to ebs
8) ad_apply_patch.xml  - This is invoked from patch_metadata.xml and contains details of driver to be applied
9) product top files - contains tops with various fixes like updated fmb,packages.ldt's,etc.,

Detailed explanation on unified driver and its contents will be posted soon

Inputs are most appreciated.. :)

Monday, November 18, 2013

Concurrent Request completed with "sqlldr : Permission denied" error

Issue

Arguments
------------
/u01/files_in/BANK_SORT.pdv
------------
sqlldr: Permission denied
usdsop: exec failed during spawnusdsop cannot write to pipe

Cause: usdsop attempted to write data to another process using a pipe, but an error occurred during the write.

Action: Check the other process for abrupt termination. Check that you have sufficient resource: Broken pipe
usdsop cannot write to pipe

Cause: usdsop attempted to write data to another process using a pipe, but an error occurred during the write.

Action: Check the other process for abrupt termination. Check that you have sufficient resource
Concurrent Manager encountered an error while running SQL*Loader for your concurrent request 1336114.

Review your concurrent request log file for more detailed information.


+---------------------------------------------------------------------------+

No completion options were requested.

Solution:

1. Check the permission for sqlldr file in (ORACLE_HOME) ie) 10.1.2 ORACLE_HOME/bin directory.
2. Give executable permission to sqlldr file in $ORACLE_HOME/bin
chmod 755 $ORACLE_HOME/bin/sqlldr

2. Check the ORACLE_HOME path is set correctly for apps.

Saturday, November 9, 2013

Useful Application & Database Scripts

Useful Application & Database Scripts

Script Name: Elapsed Time History of Concurrent Requests in a month

Script Details

SELECT pt.user_concurrent_program_name
       user_conc_program_name,
       r.priority,
       Count(*),
       Round(Avg(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       AVGG,
       Round(Max(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       MAXI,
       Round(Min(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       MINI,
       p.concurrent_program_name
       concurrent_program_name
FROM   apps.fnd_concurrent_programs p,
       apps.fnd_concurrent_programs_tl pt,
       apps.fnd_concurrent_requests r
WHERE  r.concurrent_program_id = p.concurrent_program_id
       AND r.program_application_id = p.application_id
       AND r.concurrent_program_id = pt.concurrent_program_id
       AND r.program_application_id = pt.application_id
       AND r.status_code = 'C'
       AND r.actual_start_date IS NOT NULL
       AND To_char(actual_completion_date, 'MON') = '&1'
GROUP  BY pt.user_concurrent_program_name,
          r.priority,
          p.concurrent_program_name
ORDER  BY Count(*) DESC

Script Name: Current Logged on User count in EBS

Script Details

SELECT DISTINCT icx.session_id,
                  icx.user_id,
                  fu.user_name,
                  fu.description
    FROM icx_sessions icx, fnd_user fu
   WHERE     disabled_flag != 'Y'
         AND icx.pseudo_flag = 'N'
         AND (last_connect + 
              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
                      NULL, limit_time,
                      0   , limit_time,
                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
         AND icx.counter < limit_connects
         AND icx.user_id = fu.user_id;

Will add more..

adclonectx.pl error: ensure that Classpath is set properly

Please ensure that Classpath is set properly

Issue

Running adclonectx.pl from dbserver will throw below error

oracle@myserver:/u01/oracle/VIS/11.2.0/appsutil/bin $ adclonectx.pl context_file=/u01/oracle/VIS/11.2.0/appsutil/HRMSTU1_hboasmkdb.xml             <

                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adclonectx Version 120.23.12010000.1
Please ensure that Classpath is set properly

Solution

Recovery of Missing undo datafile from coldbackup

Recovery of lost Undo Datafile
Hi All,

This blog is about strange experience I had during the restoration of database from cold backup.
I am working on R12 Upgrade project and as part of DBA Upgrade activity I took cold backup of complete database and application at the initial state of upgrade. Customer has provided us cifs file system to store our backups and it is somewhat different to the normal unix file system. This file system is not case sensitive and it is replacing the files with the same name but with different case and thus resulting in some loss of files. Luckily/Unlucky in my current database I have UNDO Tablespace with 3 data files and out of these two data files have same name with different case and thus when I took a complete cold backup of database resulting in loss of data file.

Now I had to use this backup for building another database and during recovery I found that one of the data files and when I investigated I could identify that the undo data file 1 is replaced by undo data file 2. So, As this is an undo data file I know that it doesn't contain user/system data(database shutdown is graceful) and losing it doesn't lose any of our data. Thus this gave us scenario of recovering database due to loss of undo data file.
This cold backup can be helpful only if the database is shutdown gracefully else the backup would be vane and I need to find another source of backup for restoration. As this is cold backup I could go ahead in recreating the lost undo data file and recover the database successfully.

Below is the procedure followed to restore the UNDO tablespace:
1) Verified the data files in backup location and showed that both files are existing
                [oratacrw@aoccol-101 CRP1Backup]$ pwd
                /mnt/R12_Backups/CRP1Backup
                [oratacrw@aoccol-101 CRP1Backup]$ ls -lrt APPS_UNDOTS1_2.DBF
                -rwxrwSrwt  1 root root 2097160192 Aug 26 11:02 APPS_UNDOTS1_2.DBF
                [oratacrw@aoccol-101 CRP1Backup]$ ls -lrt APPS_UNDOTS1_2*
                -rwxrwxrwx  1 oratacrw dba 2097160192 Aug 26 11:02 APPS_UNDOTS1_2.dbf

2) Copy all the data files from backup location to data files location

3) Modified the init parameters as per new db_name and also changed below parameters for disabling auto undo management.

                UNDO_MANAGEMENT=MANUAL
                The above setting makes database to use SYSTEM as undo tablespace instead of using actual undo tablespace(by default)

4) Create spfile from modified pfile;
                SQL> create spfile from pfile;     

5) Recreate control file manually and shutdown the database.
6) Mount the database in restricted mode.
                SQL> STARTUP RESTRICT MOUNT
                ORACLE instance started.

                Total System Global Area 1073741824 bytes
                Fixed Size                  2089336 bytes
                Variable Size             427822728 bytes
                Database Buffers          629145600 bytes
                Redo Buffers               14684160 bytes
                Database mounted.

7) Offline drop the missing undo data file from database and open the database.
                SQL> ALTER DATABASE DATAFILE '/u02/tacprefdata/APPS_UNDOTS1_01.dbf' OFFLINE DROP;
                Database altered.

                SQL> alter database open;
                Database altered.

8) Drop complete undo tablespace including datafiles.
                SQL> DROP TABLESPACE APPS_UNDOTS1 INCLUDING CONTENTS;
                Tablespace dropped.
               
9) Recreate the undo tablespace with required datafiles.
                SQL> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE '/u02/tacprefdata/APPS_UNDOTS1_01.dbf' size 2G REUSE;
                Tablespace created.
10) shutdown the database and modify UNDO_MANAGEMENT parameter to "AUTO" and UNDO_TABLESPACE to "new undo ts"              
                SQL> shutdown immediate;
                Database closed.
                Database dismounted.
                ORACLE instance shut down.
               
                Init parameters
               
                undo_management = AUTO
                undo_tablespace = APPS_UNDOTS1
               
11) Recreate spfile and bring up the database
               
                SQL> create spfile from pfile;
                File created.

                SQL> startup
                ORACLE instance started.

                Total System Global Area 1073741824 bytes
                Fixed Size                  2089336 bytes
                Variable Size             427822728 bytes
                Database Buffers          629145600 bytes
                Redo Buffers               14684160 bytes
                Database mounted.
                Database opened
               
                SQL> sho parameter undo

                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ------------------------------
                undo_management                      string      AUTO
                undo_retention                       integer     900
                undo_tablespace                      string      APPS_UNDOTS1
               
Now the database is up and completely operational.


Thank you.