How to Upgrade R12.1.3 to R12.2

What’s coming in Oracle E-Business Suite 12.2?


I will try to analyze architectural overview of the latest updates, installation and upgrade options, new configuration options, and new tools for hot-cloning and automated “lights out” cloning in R-12.2

Oracle E-Business Suite 12.1 Architecture

[Image: R12.1architecture-300x160.png]
[Image: R12.1architecture2-300x202.png]


Oracle E-Business Suite 12.2 Architecture

Release 12.2 will replace Oracle Containers for Java (OC4J) 


10g with WebLogic Server 11g

Release 12.2 Database tier will run RDBMS 11gR2 to 


support online patching.


[Image: R12.2architecture1-300x153.png]
[Image: R12.2architecture2-300x159.png]

There will be number of updates coming in release R12.2, from a technology stack perspective, EBS 12.2 will be notable for two things:
1-Replacing Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g
EBS 12.2 will use WebLogic Server from Fusion Middleware 11g in place of OC4J 10g as part of the release’s internal technology stack. Other additional new Fusion Middleware 11g components used will include WebLogic JSP and UIX 11g 


2-Online Patching support via 11gR2 Edition-Based Redefinition. EBS 12.2 will use the 11gR2 Database’s Edition-Based Redefinition features to provide support for Online Patching. Edition-Based Redefinition is really exciting new technology.


Upgrade R12.1.3 to R12.2 Steps:


1. List of recommended pre-upgrade activities:-


• Purge all old data that is no longer needed prior to the upgrade.
• Flush all the interfaces, such as Auto Invoice, Journal Entry Import, Order Import etc.
• Drop MRC Schema if it still exists.
• Gather the schema statistics (with GATHER_AUTO option for all schemas) close to the start of the downtime. Use FND_STATS or Gather Statistics concurrent programs.

• Gather Fixed Object and Dictionary Statistics.



Purge old data:-

Purge all old data that is no longer needed prior to the upgrade.
This can be quite a lengthy process, and you may need to resolve issues around performance, execution frequency and the periods to be purged in each execution. So this should be started as soon as possible.
There are over 100 standard purge programs in 11i. You can use OAM to administrate, configure, initiate and monitor purge programs.

System Administrator >Oracle Applications Manager >Purging/Critical Activities.


Purge interface data


Flush all the interfaces, such as Auto invoice, Journal entry import, and order import.



Fixed Object and Dictionary Statistics

These should have been gathered and be correct/up to date on the pre-upgrade environment.

Drop MRC Schema

All programs and reports now use the APPS schema. The MRC_APPS schema is no longer needed, so dropping it frees space and reduces processing overhead during the upgrade.
You can drop this schema prior to the Release 12 upgrade if it still exists. It should have already been removed on upgrade to 11.5.10.



Removing Unnecessary Workloads / Overheads

To reduce the workload you should do the following:

• Disable any custom triggers and business events.
• Disable auditing if enabled. The “Oracle E-Business Suite Upgrade Guide” states that the Oracle AOL Audit Trail should be disabled before upgrade, anyway.
• Review and disable all debug and logging that has been enabled using profiles. Do this at all levels (e.g. site, responsibility, user level.).
• If possible, run in noarchivelog mode.
• Disable flashback DB.
• Remove TDE (Transparent Data Encryption) from high volume tables.
• Consider running AutoConfig in parallel on a multi-node system.


Use TUMS (The Upgrade Manual Script)
 to avoid running tasks not relevant to your system
The TUMS report lists tasks that you can omit from the upgrade because they do not apply to your system (for example, a task required for a product that you do not use or a patch that you have previously applied). TUMS is delivered in a patch (7705743), which supplies the scripts you need to examine your system and create the report. We strongly recommend you create and review the TUMS report before you begin the upgrade.
Download and apply patch 7705743, then run the script adtums.sql to generate the report tumsr12.html.


The tumsr12.htm report lists the steps (identified by the TUMS step key in this book) that do not apply to your installation. You may safely ignore any steps listed in this report.


How to Upload & Download Workflow (wft) Files in R12



WFLOAD Utility :-

 oracle has provided WFLOAD  for workflow files. Below are the available options -

To upgrade      - WFLOAD apps/pwd 0 Y UPGRADE filename.wft

To upload        - WFLOAD apps/pwd 0 Y UPLOAD filename.wft

To force           - WFLOAD apps/pwd 0 Y FORCE filename.wft

To download   - WFLOAD apps/pwd 0 Y DOWNLOAD filename.wft 

WorkflowDOWNLOADWFLOAD apps/$passwd 0 Y DOWNLOAD XXWFITEMTYPE.wft
UPGRADEWFLOAD apps/$pwd 0 Y UPGRADE xxwfitemtype.wft
FORCEWFLOAD apps/$pwd 0 Y FORCE xxwfitemtype.wft


Log location same place 
Use pwd command and find log file in same location.

Location wft files are stored in server:-
 $PRODUCT_TOP/patch/115/import/<LANG> directory.

Standard Concurrent Program – Run the program Workflow Definitions Loader using sysadmin responsibility (you can add this program to any of responsibility and run from there).
This program has 3 parameters as below

1   Mode – Upload/Download

2  File - /staging/sandeep/Project/PAPROWF.wft 
                  (wft file name with Full Path. Make sure file is available at given path for UPLOAD Mode)

3  Item Type – Item type Name for above file (This can be get from workflow builder. Open your wft file in workflow builder and create any process. The first attribute will be Item Type Name)

How to set MAX_DUMP_FILE_SIZE UNLIMITED

The size of the trace file in Oracle determined by the parameter is max_dump_file_size. If you set the value of this parameter to UNLIMITED the trace file will occupy the total available size in the file system if it is required. You can limit the value in session or system level by changing the value of the max_dump_file_size.


SQL> show parameter max_dump_file_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
max_dump_file_size                   string      unlimited

SQL> alter session set max_dump_file_size='200m';

Session altered.


SQL> show parameter max_dump_file_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
max_dump_file_size                   string      200M

SQL> alter system set max_dump_file_size='200m';

System altered.

Revert the value to the unlimited value.

SQL> alter system set max_dump_file_size=unlimited;

System altered.

MAX_DUMP_FILE_SIZE

VersionTypeDefaultSession
Modifiable
System
Modifiable
7.3.4Integer10000NoNo
8.0.4Integer10000NoNo
8.0.5Integer10000NoNo
8.0.6Integer10000NoNo
8.1.5Integer10000NoNo
8.1.6StringUNLIMITEDYesImmediate
8.1.7StringUNLIMITEDYesImmediate
9.0.1StringUNLIMITEDYesImmediate
9.2.0StringUNLIMITEDYesImmediate
This parameter specifies the maximum size of a dump file. The size is specified in operating system blocks.

The parameter can be set in the initialisation file e.g.
    max_dump_file_size = 10000

This limits the trace file to 10000 operating system blocks. If the size of an operating system block is 512 bytes then the trace file is limited to 5mb.
In Oracle 8.1.6 and above the default value of this parameter is UNLIMITED. If the specified size has a suffix of "K" e.g. 1000K then this is the maximum file size in kilobytes; if the specified size has a suffix of "M" e.g. 1M then this is the maximum file size in megabytes. If neither a K or M suffix is specified then it still represents the maximum file size in operating system blocks.
The operating system block size is determined by the constant SSTPBLCK (ssparams.h) which represents the minimum physical block size at device level This varies by platform.
Values include

Operating
System
Size
in Bytes
NT/2000512
Solaris512
HP/UX1024
Tru 64 Unix1024

In Oracle 8.1.6 and above this parameter can be modified at system level e.g.
    ALTER SYSTEM SET max_dump_file_size = 10000;

In Oracle 8.1.6 and above it can also be modified at session level e.g.
    ALTER SESSION SET max_dump_file_size = 10000;

In Oracle 8.1.6 and above this parameter can also be set to UNLIMITED (the default value e.g.
    ALTER SESSION SET max_dump_file_size = UNLIMITED;

Trace file size can also be set to UNLIMITED using the ORADEBUG UNLIMIT command

How to compile Oracle Apps 11i/R12 Forms

How to compile Oracle Apps 11i Forms
============================

Check whether the forms that you want to generate are not in use at the time you are generating them.
 If they are in use when you generate the fmx files, the forms client session terminates.

1) Login to the Forms server node as applmgr and run .env file to set the applications environment.
2) Change directory to $AU_TOP/forms/US.
3) Use the “f60gen” command to generate the fmx files for the fmb files.
4) Issue the following command

$ f60gen module=<formname>.fmb userid=apps/<apps_pwd> output_file=/forms/US/<formname>.fmx

How to compile Oracle Apps R12 Forms
=============================

1) Log into the forms tier.
2) Set the applications environment
3) Ensure that the $FORMS_PATH includes $AU_TOP/resource and $AU_TOP/resource/stub, for example

echo $FORMS_PATH

/u01/oracle/DEV/apps/apps_st/appl/au/12.0.0/resource:
/u01/oracle/DEV/apps/apps_st/appl/au/12.0.0/resource/stub

4) Compile the form

a)- If you are using forms customizations (CUily: verdana,geneva;">1) Log into the forms tier.
2) Set the applications environment
3) Ensure that the $FORMS_PATH includes $AU_TOP/resource and $AU_TOP/resource/stub, for example

echo $FORMS_PATH

/u01/oracle/DEV/apps/apps_st/appl/au/12.0.0/resource:
/u01/oracle/DEV/apps/apps_st/appl/au/12.0.0/resource/stub

4) Compile the form

a)- If you are using forms customizations (CUSTOM.pll) then run the command below to compile the form.

frmcmp_batch.sh module=<path to fmb file> userid=APPS/APPS output_file=
<full path to fmx output file> module_type=form compile_all=special

For eg:-

frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/
XXX.fmb userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/
12.0.0/forms/US/XXX.fmx module_type=form compile_all=special

b) If you are NOT using forms customizations, then run the command below to compile the form.

frmcmp_batch.sh module=<path to fmb file> userid=APPS/APPS output_file=
<full path to fmx output file> module_type=form

For example..

frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/
XXX.fmb userid=APPS/APPS  output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/
12.0.0/forms/US/XXX.fmx module_type=form


What is SCAN in RAC

What is SCAN in Oracle 11g R2 RAC?


Single client access name (SCAN) is meant to facilitate single name for all Oracle clients to connect to the cluster database, irrespective of number of nodes and node location. Until now, we have to keep adding multiple address records in all clients tnsnames.ora, when a new node gets added to or deleted from the cluster.

Other Hand

Oracle 11g R1 RAC, client connection to RAC Cluster will use each node VIP (virtual IP) address. So each time a node is added to the cluster, the client tnsnames.ora file needs to be updated with the new VIP addition.
Oracle 11g R2 RAC introduces SCAN (Single Client Access Name) to handle that problem. SCAN provides single name from clients to access the database running in the cluster.
This is also very beneficial for client connecting using JDBC thin URLs and EZCONNECT to cluster with single hostname.
SCAN components in a cluster:
  • SCAN Name
  • SCAN IP, Oracle recommends 3 IP addresses for SCAN for a cluster
  • SCAN Listener
SCAN Name will represent the cluster in the network. SCAN Name needs to be registered in DNS and resolved using round-robin algorithm. To test the round-robin algorithm, perform nslookup on the SCAN Name. The DNS server should return those 3 set of IP Address in different order each time
Note: SCAN feature will not work with ip addresses resolved using /etc/hosts file. You would only get SCAN components online on one of the node in the cluster.

Updating SCAN Configuration in RAC

I have a 2 node Linux RAC setup on virtual box. I initially configure the SCAN using /etc/hosts file and that’s why I only see the SCAN Listener only up in one of the nodes. If that node fail, my client would not be able to connect to the database in the cluster.
So to have SCAN Listener running on more than one node, I asked my DNS admin to register the SCAN Name in the DNS server with the round-robin algorithm.
To test the round-robin algorithm, I did an nslookup command twice.
C:\Users>nslookup lasrac-scan
Server: dns.localdomain.com
Address: 172.28.85.42
Name: lasrac-scan.localdomain.com
Addresses: 172.28.172.18
172.28.172.17
172.28.172.19
Doing it again…
C:\Users>nslookup lasrac-scan
Server: dns.localdomain.com
Address: 172.28.85.42
Name: lasrac-scan.localdomain.com
Addresses: 172.28.172.17
172.28.172.19
172.28.172.18
See that ip address .18 appeared as the first ip address and then become the last (second nslookup output)
Going back to one of the node
Check the SCAN IP config
[root@lasrac01 ~]# srvctl config scan
SCAN name: lasrac-scan, Network: 1/172.28.172.0/255.255.252.0/eth0
SCAN VIP name: scan1, IP: /lasrac-scan.localdomain.com/172.28.172.17
To add the SCAN IP to the cluster
[root@lasrac01 ~]# srvctl modify scan -n lasrac-scan
Check the SCAN IP config again
[root@lasrac01 ~]# srvctl config scan
SCAN name: lasrac-scan, Network: 1/172.28.172.0/255.255.252.0/eth0
SCAN VIP name: scan1, IP: /lasrac-scan.localdomain.com/172.28.172.17
SCAN VIP name: scan2, IP: /lasrac-scan.localdomain.com/172.28.172.19
SCAN VIP name: scan3, IP: /lasrac-scan.localdomain.com/172.28.172.18
You have 3 SCAN IP now…this is good.
Check the status of SCAN IP
[root@lasrac01 ~]# srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node lasrac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node lasrac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node lasrac01
Now we need to update the SCAN Listener config too.
Check SCAN Listener Config
-bash-3.2$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Modify SCAN Listener config
[root@lasrac01 ~]# srvctl modify scan_listener -u
Check the SCAN Listener again
[root@lasrac01 ~]# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
You get 3 SCAN Listener now

Output Post Processor (OPP) in R12


Restart/Activate  OPP.

At least one OPP process active in the system. The concurrent processing uses the Output Post Processor (OPP) to enforce post-processing actions for concurrent requests. For example, post-processing action is that used in publishing concurrent requests with XML Publisher.

Actually when you have submit a request with XML Publisher template specified as a layout for the concurrent request output. Once finishes the concurrent manager concurrent program, it will contact the OPP to apply the XML Publisher template and create the final output

To activate the OPP follow these steps

The Profile Option "Concurrent: GSM Enabled" must be set to Y

Then:
1. Login to Apps with sysadmin responsibility
2. Navigate to: Concurrent -> Managers -> Define
3. Query for
Manager = 'Output Post Processor%'
or Short Name = FNDCPOPP
4. Check the checkbox "Enable" .
5. Click on 'Work Shifts button
6. see Work Shift of the OPP and
Set Processes = 1
and Parameters = oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
and Sleep Second = 30
7. Save



8. Navigate to: Concurrent -> Managers -> Administer
9. Make sure the Output Post Processor Status is not in 'Deactivate'
Set Status to 'Activate' if necessary.




10. Stop and Start the Concurrent Managers using
adcmctl.sh


How to restart OOP

If you want to check if the OPP is running execute the below command

SQL> select CONCURRENT_QUEUE_NAME,MAX_PROCESSES,RUNNING_PROCESSES,MIN_PROCESSES,TARGET_PROCESSES from apps.FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME='FNDCPOPP';

CONCURRENT_QUEUE_NAME
------------------------------------------------------------------------------------------
MAX_PROCESSES RUNNING_PROCESSES MIN_PROCESSES TARGET_PROCESSES
------------- ----------------- ------------- ----------------
FNDCPOPP
2 2 2 2

Also Using System Administrator responsibility navigate to

Concurrent-->Manager-->Administer ->query for the Output Post Processor

The Actual should equal to the Target processes (eg: Actual=2 Target =2)

Alternatively you can click on Restart to restart the service.


Output Post Processor Actual Process Is 3 And Target Process Is 4

Version:-

Oracle Concurrent Processing - Version 12.1.1 to 12.2 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms:-

Actual and Target process values are supposed to be same for any concurrent, but for Output Post Processor Actual is 3 and Target is 4

Cause:-

Internal Concurrent manager could not create missing process.

Solution:-

1. Shutdown all the concurrent processes and managers.
2. Verify that no FNDLIBR process is running on (ps -ef | grep FNDLIBR).
3. Please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager. NOTE: Regular use of the CP Analyzer in Note 1411723.1 can provide an Immediate Analysis and Output of a Concurrent Environment.
4. Restart concurrent managers.
5. Test the issue 

opatch

How to apply Database Patches

Database patches are of various kinds like,But in broad there are II types:

I)Patchset:

To upgrade to higher version we use Database patchset.i.e For Upgrading from 10.2.0.1.0 to 10.2.0.4.0 we have to use Patchset.

II)Patchset Updates:
Patchset Updates are mainly divided into two types:

1)CPU(Critical Patch Update):
Critical Patch Update, quarterly delivered by Oracle to fix security issues.

2)PSU(Patch Set Update):
PatchSet Update, also quarterly delivered by Oracle,it includes CPU and a bunch of other one-off patches.

PSUs have been introduced for the first time on Oct-2009,We can use them, and stop to apply CPUs. PSUs, as CPUs, are cumulatives, no need to apply all of them, just apply the last one should be enough. One-off patch is single fix, to fix a particular issue.

We need to have metalink(My Oracle Support)Account to download this patch and applying instructions will be there in readme file of Patch.

1)How to apply Patchsets:
-------------------------------------------------


Patchsets are applied via OUI(Oracle Universal Installer).Patchsets are usually applied to upgrade oracle version
Eg: When we want to upgrade from Base release 10.2.0.1.0 to 10.2.0.4.0 for more useful feature and to avoid bugs,We have to use Patchset 10.2.0.4.0.So Patchsets change the version number.


2)How to apply Patch set updates or Critical Patch Updates:
--------------------------------------------------------------------------


PSUs(PatchSet Updates) or CPUs(Critical Patch Updates)are applied via opatch utility.
opatch is an interim utility for applying Database interim patches which will fix the bug in the release. For making use of the opatch utility you have to download the opatch recent version and apply as follow:


Downloading and installing the latesh Opatch version:
------------------------------------------------------

Below are the steps for downloading and installing the latest opatch version.opatch is very much useful for applying the database patches to fix various bugs and it is very much important to have the latest version.

1) Please download the latest OPatch version from My Oracle Support (MOS)

a) Click on the "Patches & Updates" tab

b) In the "Patch Name or Number" field type 6880880

c) In the "Platform" field select the relevant platform

d) Click the Search button.

e) Select the patch that corresponds to the Oracle release installed:

6880880 Universal Installer: Patch OPatch 11.2

f) Click the Download button
Once the above task is done copy the patch to $ORACLE_HOME directory and move the previous OPatch directory to separate directory in OS.We can use winscp or ftp for copying this patch from MOS to windows and
than windows to linux Box.

Now,unzip this patch zip file.The files will be extracting as shown below:

Eg:
-----
$ cd $ORACLE_HOME
(If an OPatch directory already exist then move it)
$ mv Opatch /TEST/OPatch_100057
$cd $ORACLE_HOME
$ unzip p6880880_101000_LINUX64.zip
Archive: p6880880_101000_LINUX64.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/README.txt
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/bt1.txt
inflating: OPatch/docs/bt2.txt
inflating: OPatch/docs/tmp
inflating: OPatch/emdpatch.pl
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl

[oracle@kor300949lx1 OPatch]$ opatch version
Invoking OPatch 11.2.0.1.5

OPatch Version: 11.2.0.1.5

OPatch succeeded.

[oracle@kor300949lx1 OPatch]$ opatch apply /u01/app/8264365
Invoking OPatch 11.2.0.1.5

Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-05-16_17-18-01PM.log

Patch history file: /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Here is your opatch latest version ready,Let us what all it consists of:

Go to the below mentioned path for all opatch commands description.

$ cd $ORACLE_HOME/OPatch/docs/
$ vi Users_Guide.txt

In general for applying any patch,We have to use the below command:
$cd $ORACLE_HOME/OPatch
$opatch apply /u01/Patch_dir/

Where :/u01/Patch_dir: The path where you have kept your Patch in the OS.

Hope the above post helps in understanding Database patches completely.

********************************************************************************************
*********************************************************************************************



How to apply oracle database patch (OPatch) without downtime.

====================================================

To apply Opatch
coditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches.
in single instance its not possible.
but for RAC instance its possible.
as in RAC there will be two seperate oracle home and two seperate instances running once instance on each oracle_home

use this command:

opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME


when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.


steps before applying patch:
----------------------------

1) check the database status.
wch_db.sql
-----------
select name,
open_mode,
database_name,
created,
log_mode,
platform_name
from v$database;



2) Check the object's invalid.

user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

3) Take backup of invalid's

create table bk_inv_ as select * from dba_objects
where status='INVALID';

4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and
download the latest version and uncompress
in $ORACLE_HOME.

5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc

inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba

if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME

inventory must point to the current $ORACLE_HOME which is getting patched.

6) check free space on $ORACLE_HOME
df -h $ORACLE_HOME

7) chek the utilities like
which ld
which ar
which make
etc as per readme file.

8) unzip the patch
unzip -d /loc_2_unzip p.zip

9) Go the patch directory
cd /loc_2_unzip/patch_number

10) Bring down the listner.
cd $ORACLE_HOME/bin
lsnrctl stop

11) Bring down the database
Shutdown immediate.

12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin

13) Start the patch
opatch napply -skip_subset -skip_duplicate

for RAC database then database can be up
as it may be having more then one instance
so you can bring down one instance and listener and apply the patch and open it
and then do the same on another node.
like this db will be up and no user will face issue in outage also.

to apply opatch in RAC instance
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and
-oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.

--------------------------------------------------------
. All-Node Patch

. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3

. (no downtime)

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3
-------------------------------------------------------------

14) Once patch installation is completed need to do post patching steps.

a) starup the instance
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log

c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup

If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
alter database set cluster database=true scope=spfile;

restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows



user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

if any new invalids seen then again take backup of invalid objects and compile it.

create table bk_inv_ as select * from dba_objects
where status='INVALID';

@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.


16) Confirm that patch has been applied successfully or not at db level also.


post_patch.sql
--------------
col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;


================================================================================
HOW TO APPLY PATCH on a DATABASE
================================================================================
1) Make a note of the output of below command:
ps -ef|grep pmon
ps -ef|grep tns
2) Login to database and take the snaps of all the invalid objects as mention below:
spool invalid_Object_pre.log
set linesize 256
set pagesize 256
col object_name for a30
select name from v$database;
select owner, object_name, object_type from dba_objects where status <>'VALID' order by owner, object_name;
spool off
3) Set OEM Agent blackouts on the server as follows :
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin
b) ./emctl start blackout servername_date_time_maint-nodelevel
4) Stop OEM Agent on the database server as follows:
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin/
b) ./emctl stop agent

5) Shutdown all the databases under this ORACLE_HOME with below command;

SHUTDOWN IMMEDIATE

6) stop all the LISTENER under this ORACLE_HOME
7) Cross-Check the database and Listener are down with below command:
ps -ef|grep pmon
ps -ef|grep tns
8)Take the Backup of global inventory (oraInventory) and local inventory as menetion below:
To find global inventory
cat /etc/oraInst.loc
or
cat /var/opt/oracle/oraInst.loc
go to that path and cd ..
Ex:
tar -cvf oraInventory_today'sdate oraInventory
Each ORACLE_HOME has its own inventory, its called local inventory
Ex:
cd /opt/oracle/product/10.2.0.4
tar -cvf inventory_today'sdate inventory
9)Go to below Location:
uname -a (Find the server details)
Download the patch from Oracle site to ORACLE_HOME

To know the bit (whether 64 or 32) use the following command
$ getconf KERNEL_BITS or getconf -a | grep KERNEL

unzip p9352224_92080_AIX64-5L.zip
10)Apply the Patch as mention below:
fuser -cu $ORACLE_HOME
(A) export PATH=$PATH:$ORACLE_HOME/OPatch
export TMP=$ORACLE_HOME/tmp
export TMPDIR=$TMP
export JDK_HOME=$ORACLE_HOME/jdk -- for upto 9i
export PATH=$PATH:$JDK_HOME/bin:. -- -- for upto 9i
(B) Check the which opatch version and opatch is pointing to, as mention below:
opatch version (It should 1.0.0.0.57 or higher)
which opatch (It Should be /prod01/app/oracle/product/9.2/OPatch/opatch)
(C) Apply the patch with below command:
opatch apply or opatch napply -skip_subset -skip_duplicate
11) Crosscheck the Patch is applied or not with below command:
opatch lsinventory
12) startup the Database
STARTUP
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
CONNECT / AS SYSDBA
STARTUP
@catbundle.sql psu apply
shutdown immediate
startup
++++ REPEAT the same steps for all databases. ++++++
IMP : Check the status of compoents after patching :
select COMP_NAME,STATUS from dba_registry;

13) Crosscheck the Patch is register in the database with below command:
SELECT * from REGISTRY$HISTORY;
14) Cross check the invalid objects as mention below:
spool invalid_Object_Post.log
set linesize 256
set pagesize 256
col object_name for a30
select name from v$database;
select owner, object_name, object_type from dba_objects where status <>'VALID' order by owner, object_name;
spool off
15) If the Output of invalid_Object_Post.log >(greater) than the output of invalid_Object_Pre.log then execute the below cmd:
@$ORACLE_HOME/rdbms/admin/utlrp.sql;
select owner, object_name,object_type from dba_objects where status <>'VALID' order by owner, object_name;
16) start the all listeners
17) Start OEM Agent on the database server as follows:
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin/
b) ./emctl start agent
18) Remove OEM Agent blackouts on the server as follows:
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin/
b) ./emctl stop blackout servername_date_time_maint-nodelevel.
19) Logout from the Server.
**********************************END*************************************


DBA Scripts


           

-- INSTANCE RELATED QURIES --


select instance_name,instance_number,status from v$instance;


select banner from v$version;


desc fnd_product_groups


select release_name,last_update_date from fnd_product_groups;


select * from v$parameter


select name from v$parameter where name like '%out%'


-- PATCHING RELATED --


select * from dba_objects;


select count(*) from dba_objects where status ='INVALID';


select * from dba_objects where status='INVALID'


select count(*) from dba_objects where owner = 'APPS' and status ='INVALID';


select * from dba_objects where status='INVALID';


select bug_number,last_update_date from ad_bugs where bug_number='13374062'


SQL> select bug_number,last_update_date from ad_bugs where bug_number='13006289';        

BUG_NUMBER                     LAST_UPDATE_DATE

------------------------------ ------------------

13006289                       05-MAR-14



select patch_name, last_update_date from ad_applied_patches where patch_name='13374062'


select release_name,last_update_date,creation_date from fnd_product_groups


select  bug_number,last_update_date from ad_bugs where trunc(LAST_UPDATE_DATE) >= '13-JUN-2011'


select  PATCH_NAME,last_update_date from ad_applied_patches where trunc(LAST_UPDATE_DATE) >= '08-JUN-2011'


-- USER ACCOUNT RELATED --


select * from dba_users where username='APPS';


select username,account_status from dba_users where username='RAC_ACCNT';


select user_id,user_name,session_number from fnd_user where user_name=''


select username,password,account_status,lock_date from dba_users where username='APPS'


-- TABLE SPACES and GRANTS Related --


select * from dba_tablespaces


select * from dba_temp_files


select tablespace_name from dba_tablespaces where tablespace_name='READONLY';


select username,DEFAULT_TABLESPACE from dba_users where username='ROAPPS'


revoke  select on  HR.AME_ACTION_TYPES_TL from ROAPPS


-- DATABASE  LOCKS --


select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' )  is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;


no rows selected


desc dba_locks


SQL> desc dba_locks

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SESSION_ID                                         NUMBER

 LOCK_TYPE                                          VARCHAR2(26)

 MODE_HELD                                          VARCHAR2(40)

 MODE_REQUESTED                                     VARCHAR2(40)

 LOCK_ID1                                           VARCHAR2(40)

 LOCK_ID2                                           VARCHAR2(40)

 LAST_CONVERT                                       NUMBER

 BLOCKING_OTHERS                                    VARCHAR2(40)


desc v$locked_objects ;


desc dba_blockers


select holding_session from dba_blockers


select session_id,lock_type from dba_locks


select * from dba_ddl_locks


select * from v$lock where block<>0;


select * from v$session where sid =820;


select * from V$sqltext where  hash_value='3533547372' order by piece


--For checking what SQL, CM program is executing--


select * from fnd_concurrent_requests where request_id='639447';


--we need to take oracle_process_id  from above , then  SPID is oracle process id from the above query


select * from v$process where spid='15229';


--Take paddr value from the above query--


select * from v$session where paddr='00000000DC48C2D0';


--Take SQL_HASH_VALUE from the above query--


select * from V$sqltext where  hash_value='2556871103' order by piece;


-- COMPILE APPS SCHEMA --


EXEC DBMS_UTILITY.compile_schema(schema => 'APPS');


-- APP Users Responsibilities --


Select fu.user_id

,fu.user_name

,frt.RESPONSIBILITY_NAME

,fur.start_date Start_date

,fur.end_date End_Date

From fnd_user fu

,apps.FND_USER_RESP_GROUPS_ALL fur

,FND_RESPONSIBILITY_TL frt

Where fu.user_name= 'VIKRAM_MEHTA'

and fu.user_id=fur.user_id

and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID

                  

-- GATHER SCHEMA STATISTICS on TABLES --


exec dbms_stats.gather_table_stats(SYS, 'GL_JE_LINES', cascade => TRUE);


exec sys.dbms_stats.gather_table_stats(GL, 'GL.GL_JE_LINES', cascade => TRUE);


exec sys.dbms_stats.gather_table_stats('GL','GL_JE_LINES',ESTIMATE_PERCENT=>100,CASCADE=>True);


analyze table gl.gl_je_lines estimate statistics sample 100 percent for table for all indexed columns for all indexes


analyze table gl.gl_balances estimate statistics sample 100 percent for table for all indexed columns for all indexes


select table_name,column_name,num_distinct,last_analyzed,num_buckets from user_tab_cols where table_name like ('%GL_JE_LINES%');


select table_name,index_name,num_rows,last_analyzed from user_indexes where table_name like ('%GL_JE_LINES%');    

                  

-- TEMPLATES --


select * from xdo_lobs where LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'XXWINCEADR%'             


select * from xdo_lobs where 1=1-- LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'PO_STANDARD_PO%'


-- OTHERS --

                 

select * from dba_ddl_locks

where name like 'XXWIN_HCM%'


select * from v$lock


select * from v$session_wait


select * from fnd_concurrent_requests


select request_id, request_date,logfile_name from fnd_concurrent_requests where request_id='744042'


select * from fnd_nodes


--To see employee name for the persons column (To add employee to the user)--


select * from per_all_people_f  where FULL_NAME like '%vic%'


select * from apps.fnd_lookup_values where lookup_type='XXWIN_SFTP_DETAILS'

              

select * from v$recover_file

---- To SEE what parameters  assigned for CUSTOM CONCURRENT PROGRAMS----


select * from FND_DESCR_FLEX_COL_USAGE_VL

where END_USER_COLUMN_NAME = 'p_debug_flag'


select * from fnd_concurrent_programs where concurrent_program_id=55455


select * from  xxwin_datafile_validation_tab where data_file_name like 'UKJNL%'

----Table Space Query -----

SELECT

    d.status                                            status

  , d.tablespace_name                                   name

  , d.contents                                          type

  , d.extent_management                                 extent_mgt

  , d.segment_space_management                          segment_mgt

  , NVL(a.bytes, 0)                                     ts_size

  , NVL(a.bytes - NVL(f.bytes, 0), 0)                   used

  -- , NVL(f.bytes, 0)                                     free

  , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

FROM

    sys.dba_tablespaces d

  , ( select tablespace_name, sum(bytes) bytes

      from dba_data_files

      group by tablespace_name

    ) a

  , ( select tablespace_name, sum(bytes) bytes

      from dba_free_space

      group by tablespace_name

    ) f

WHERE

      d.tablespace_name = a.tablespace_name(+)

  AND d.tablespace_name = f.tablespace_name(+)

  AND NOT (

    d.extent_management like 'LOCAL'

    AND

    d.contents like 'TEMPORARY'

  )

and NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)>70

UNION ALL

SELECT

    d.status                         status

  , d.tablespace_name                name

  , d.contents                       type

  , d.extent_management              extent_mgt

  , d.segment_space_management       segment_mgt

  , NVL(a.bytes, 0)                  ts_size

  , NVL(t.bytes, 0)                  used

  , NVL(t.bytes / a.bytes * 100, 0)  pct_used

FROM

    sys.dba_tablespaces d

  , ( select tablespace_name, sum(bytes) bytes

      from dba_temp_files

      group by tablespace_name

    ) a

  , ( select tablespace_name, sum(bytes_cached) bytes

      from v$temp_extent_pool

      group by tablespace_name

    ) t

WHERE

      d.tablespace_name = a.tablespace_name(+)

  AND d.tablespace_name = t.tablespace_name(+)

  AND d.extent_management like 'LOCAL'

  AND d.contents like 'TEMPORARY'

  AND NVL(t.bytes / a.bytes * 100, 0)>70



select * from dba_temp_files


Checks to be performed while Concurrent program is running.


1)    You need to first get the oracle session id by running the below sql with appropriate request_id.


select * from fnd_concurrent_requests where request_id='656309'


2)    Oracle session id will be the input to the below query to find out the sql_id and SID.


select * from gv$session where AUDSID='2659367'


3)    To find out the latest sql running , you need to input sql_id from the above query into the below query.


select * from gv$sqltext where sql_id='3sxmjmxtz9mt8' order by piece


4)    Once the records in the table XXWIN_HCM_EMP_MASTER_STG_TAB  is populated completely ( total records should be 196601) it will start writing into the file. You can get this confirmed by running the query in step 3 and the output should be below which means it writing to the file.


SELECT * FROM XXWIN_HCM_EMP_MASTER_STG_TAB WHERE EXTRACT_ID = :B

AND REQUEST_ID=:B1


5)    To check the number of records processed, you need to run the below query.


select count(*) from xxwin.XXWIN_HCM_EMP_MASTER_STG_TAB  where status not like  'P'


6)    Keep an eye on the file created in the interface_home outgoing directory. The file size should be gradually increasing.


-rw-r--r-- 1 ortwncti dbtwncti 112573157 Nov 24 16:23 INTHCM1_20111124121142_00001.dat


select count(*) from PO_LINE_LOCATIONS_ALL


select count(*) from po_headers_interface


select count(*) from po_lines_interface


select count(*) from po_distributions_interface



select username,default_tablespace,account_status from dba_users

 where default_tablespace='SYSAUX';


select * from DBA_HIST_WR_CONTROL;


select min(snap_id),MAX(snap_id) from dba_hist_snapshot;


Select * from v$sysaux_occupants;

This is to find the work flow inbound and outbound service names and mailer user names.

select c.component_id, c.component_name, c.component_status,

p.parameter_id, p.parameter_name, v.parameter_value value

from fnd_svc_comp_param_vals_v v,

fnd_svc_comp_params_b p,

fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER', 'ACCOUNT', 'REPLYTO')

order by c.COMPONENT_ID;


To see the Login url from OS level.

grep -i login $CONTEXT_FILE




Important Quearies to get the SID from PID:

select * from v$session, v$process

where addr=paddr and

v$process.spid  in ('1178','1129')


To Query users:


select user_name,creation_date

from fnd_user

where end_date is null

and user_name like '%\_%' escape '\'



select trunc(start_time) "Date", count( distinct user_id) "Number of users logged in",

count( distinct user_id)*100/2503 "% of total users"

from fnd_logins

where user_id not in (1162,0)

group by trunc(start_time)


Blocking Sessions Query

check for blocking sessions...

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username ||
'@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;


To check lock on PO table’s and AP table’s

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action from v$locked_object,dba_objects,v$session

where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id

and SESSION_ID=sid;


To Check that the program has to be run with which responsibility.

SELECT responsibility_name
  FROM fnd_responsibility_tl a,
       fnd_responsibility c,                      
       fnd_request_group_units d,
       fnd_concurrent_programs_tl b
 WHERE     a.responsibility_id = c.responsibility_id
       AND c.request_group_id = d.request_group_id
       AND b.concurrent_program_id = d.request_unit_id
       AND UPPER (b.USER_CONCURRENT_PROGRAM_NAME) =
              UPPER ('Qtel CI TO IRB Interface Program');

Revoke Exceptional Privileges from a DB User.


SELECT 'revoke insert,update,delete '||owner||'.'||object_name||' from xxwin;' FROM dba_objects WHERE object_type IN ('TABLE') and owner in ('APPS');

To See responsibilities of a User:

Select fu.user_id

                          ,fu.user_name

                          ,frt.RESPONSIBILITY_NAME

                   From fnd_user fu

                        ,FND_USER_RESP_GROUPS_ALL fur

                        ,FND_RESPONSIBILITY_TL frt

                   Where fu.user_id= 1218

                   and fu.user_id=fur.user_id

                   and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID


To Find out the DB patch list:

$ cd $ORACLE_HOME/OPatch

$ optach lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc >> dbpatchlist.txt

$ vi patch.list

To find out size of the Database:

select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;


Lock on Table:

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from 
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, 
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id


TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:

set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';


Lock on sessions:

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action from v$locked_object,dba_objects,v$session
where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id
and SESSION_ID=sid;


Find out SID from OS PID:


select * from v$session where paddr in (select addr from v$process where spid =31665)


To Check for the SYSTEM process Associted with Concurrent Request

select distinct s.inst_id,S.TYPE,substr(''''||S.SID||','||S.SERIAL#||'''',1,15) SI_SE#, p.spid,s.process client_process,
 s.MACHINE, s.TERMINAL,P.PID, G.NAME,P.PROGRAM,s.module,S.USERNAME,q.module qmodule ,s.command,s.osuser,
 q.hash_value, substr(q.sql_text,1,30) SQL_TEXT,w.EVENT,W.WAIT_TIME,w.SECONDS_IN_WAIT,
 --,w.p1,w.p2,W.STATE,
 s.logon_time,t.start_time,
 t.used_ublk, t.log_io,t.phy_io,t.cr_change,q.EXECUTIONS,
 q.PLAN_HASH_VALUE, q.CHILD_NUMBER,
.LOGON_TIME,s.last_call_et, s.status
9 from gV$SESSION S, gV$SQL Q, gV$PROCESS P, gV$BGPROCESS G, gv$session_wait w,gV$TRANSACTION T
 where S.SQL_ADDRESS = Q.ADDRESS (+) and S.SQL_HASH_VALUE = Q.HASH_VALUE (+)
 and S.PADDR = P.ADDR
 and S.PADDR = G.PADDR (+)
 and s.sid=w.sid (+)
 AND S.SADDR = T.SES_ADDR (+)
 and s.process in ('&client_process_list')


To Check Business Events Exist or not:

select guid from wf_events where name = 'xops.oracle.apps.gmd.rerollandcutbacks'

select *

from WF_BPEL_QTAB

where q_name = 'xops.oracle.apps.gmd.rerollandcutbacks'


To find out SPID:

SELECT s.sid,       p.spid,       s.osuser,       s.program FROM   v$process p,

v$session s WHERE  p.addr = s.paddr

alter system kill session ’134,47747′ immediate;


SQL> select username,sid,serial#,terminal from v$session;


WFUPLOAD  and WFDOWNLOAD


WFLOAD apps/X4wNG4rg 0 Y DOWNLOAD XXWIN_POWFRQAG.wft XXWINPOR


WFLOAD apps/X4wNG4rg 0 Y UPLOAD XXWIN_POWFRQAG.wft XXWINPOR

WFLOAD apps/X4wNG4rg 0 Y FORCE XXWIN_POWFRQAG.wft

select * from fnd_application_tl


Solution For the REP 3000, Report Cache Errors.


1- Shutdown the Concurrent Manager.
2- Manually delete all the temporary files under the /Base/logs/ora/10.1.2/reports/cache 
3- Startup up the Concurrent Manager. 


To Fix this Issue permanently.

Intermittent Oracle Reports REP-0069: REP-57054: Error (Doc ID 1237834.1). 

note 1237834.1 and 1322704.1 mentioned in the support SR and come up the below action plan.


Action Plan
========
1. Stop the MT services
2. Review the patch readme and apply the patch 11669923, 11776182
3. change the cachesize parameter value from 50 to 0 within the file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf also add the <property name="noVoidedOutputError" value="yes"/>
<property name="cacheSize" value="50"/>
to 
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
4. Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to $FND_TOP/admin/template/custom (ensure the file is not there in the custom directory before copy the file.
5. Add <property name="noVoidedOutputError" value="yes"/> to the $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp and also change the cachesize value to 0.

in this file under the <cache class="oracle.reports.cache.RWCache"> section
ie:
<cache class="oracle.reports.cache.RWCache">
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
<property name="cacheDir"
value="%s_logs_dir%/ora/10.1.2/reports/cache"/>
<!--property name="maxCacheFileNumber" value="max number of cache files"/-->
<!--property name="ignoreParameters" value="parameter names to be
ignored in constructing cache key, separated by comma ','"/-->
</cache>

6. Start the MT services


Huge Pages on the Server:


The host is up and huge page looks good now:

[root@auohswnct08 ~]# cat /proc/meminfo |grep -i huge
HugePages_Total: 8100
HugePages_Free: 6825
HugePages_Rsvd: 6750
Hugepagesize: 2048 kB
[root@auohswnct08 ~]# sysctl -a |grep -i huge
vm.hugetlb_shm_group = 0
vm.nr_hugepages = 8100



The host is hung in console after starting using xm create :

------------------------------------------------------------------------------------------------------------------------------------------------------------------

[root@auod09m2al141r23 27118_auohswnct08]# xm create -c vm.cfg
Using config file "./vm.cfg".

Started domain 27118_auohswnct08 (id=13)
[root@auod09m2al141r23 27118_auohswnct08]# xm console 27118_auohswnct08

------------------------------------------------------------------------------------------------------------------------------------------------------------------

We tried booting from the snapshot of Mar 24 - which is the only available snapshot in this DOM0 , but did not help.

Tried starting the host with the huge page entry commented out as the host was rebooted after the huge page changes, but no luck.

Checking further with Maint SA


Remove the hold of concurrent requests from back end:


SQL>  @CMGR_del_hold

SQL> update apps.fnd_concurrent_requests

   2  set hold_flag = 'N'

   3  WHERE

   4  nvl(hold_flag,'N') = 'Y' and phase_code = 'P' and

   5  request_id not in (SELECT request_id FROM apps.tmp_program_on_hold) ;


Workflow  Quries:

SELECT COUNT (*), machine, process

    FROM gv$session

   WHERE program LIKE 'JDBC%'

GROUP BY machine, process

ORDER BY 2 ASC


Workflow processed Query


select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE; 





imap services are fine
==============

spawn telnet auohswnct09.oracleoutsourcing.com 143
Trying 141.146.174.10...
Connected to auohswnct09.oracleoutsourcing.com (141.146.174.10).
Escape character is '^]'.
* OK Dovecot ready.
1 login wfpwncti wfpwncti
1 OK Logged in.
1 select INBOX
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk \*)] Flags permitted.
* 1 EXISTS
* 0 RECENT
* OK [UNSEEN 1] First unseen.
* OK [UIDVALIDITY 1300614072] UIDs valid
* OK [UIDNEXT 172901] Predicted next UID
1 OK [READ-WRITE] Select completed.
1 select PROCESS
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 1229 EXISTS
* 1229 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 1376] Predicted next UID

1 OK [READ-WRITE] Select completed.
1 select DISCARD
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 214 EXISTS
* 214 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 407] Predicted next UID
1 OK [READ-WRITE] Select completed.
IMAP Validation Success



The messages are processed successfully
==========
SQL> select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE;

MSG_STATE COUNT(*)
---------------- ----------
PROCESSED 7650



No messages are stuck in out bound queue
=================
SQL> select count(*) from applsys.wf_notification_out where state=0;

COUNT(*)
----------
0


Workflow Quries for JDBC Connections:

select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1800
and status = 'ACTIVE'
order by last_call_et asc



select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1800
and s.status = 'ACTIVE'


To Find out the Report Builder Version through command line:


Findings
========
$ rwrun help=yes | grep Release
REP-0503: You did not specify the name of a report.
Report Builder: Release 10.1.2.3.0 - Production on Fri Nov 2 02:20:56 2012
$ pwd
/pwncti/product/1012/OPatch
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc


OPatch succeeded.
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 11669923

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...