Oracle AppsDBA

Retrieve conc request id and database sid from os pid with the following query

2013-04-25T15:01:00.004+05:30

Retrieve conc request id and database sid from os pid with the following query

select request_id, p.spid,s.* from apps.fnd_concurrent_requests r , v$process p , v$session s
where r.request_id =
(select request_id from fnd_concurrent_requests fcr
where fcr.oracle_process_id=&OS_PID
and trunc(fcr.request_date)=trunc(sysdate))
and p.SPID = r.ORACLE_PROCESS_ID
and p.ADDR = s.PADDR;

You are welcome to leave a comment....

Scripts for Workflow notification mailer

2013-04-25T14:59:00.002+05:30

Scripts for Workflow notification mailer

Workflow: To see failed, open notifications
===============================================
SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'
GROUP BY message_type;

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications

WHERE STATUS='OPEN'

AND mail_status = 'MAIL'

GROUP BY message_name;

Sql> SELECT * FROM wf_notifications

WHERE STATUS='OPEN'

AND mail_status = 'SENT'

ORDER BY begin_date DESC

You are welcome to leave a comment....

Script to get all Concurrent Requests pending with CRM

2013-04-25T14:58:00.002+05:30

Script to get all Concurrent Requests pending with CRM

select
request_id reqid,
to_char(req.requested_start_date, 'DD-Mon HH24:MI:SS') rsdate,
decode(status_code,'Q', 'Standby', 'F', 'Scheduled', 'T', 'Terminating', 'I', 'Normal', 'R', 'Normal', 'C', 'Normal', 'Other') status_code,
user_name,
req.concurrent_queue_name conc_que,
user_concurrent_program_name pname2
from
fnd_concurrent_worker_requests req,
fnd_concurrent_queues que,
fnd_user usr
where
phase_code = 'P'
and
que.concurrent_queue_name = 'Conflict_Resolution_Manager'
and
que.application_id= req.queue_application_id
and
que.concurrent_queue_id = req.concurrent_queue_id
and
req.requested_by = usr.user_id
and
requested_start_date <= sysdate
and
concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by request_id, requested_start_date ;

You are welcome to leave a comment....

The script below will return any error in Oracle Concurrent Request for 7 days

2013-04-25T14:57:00.001+05:30

The script below will return any error in Oracle Concurrent Request for 7 days

SELECT a.request_id "Req Id"

,a.phase_code,a.status_code

, actual_start_date

, actual_completion_date

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

AND a.status_code = 'E'

AND a.phase_code = 'C'

AND actual_start_date > sysdate - 7

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND ctl.LANGUAGE = 'US'

ORDER BY 5 DESC;

You are welcome to leave a comment....

Long Running Oracle Applications Jobs

2013-04-25T14:56:00.001+05:30

Long Running Oracle Applications Jobs

SELECT a.request_id "Req Id"

,a.phase_code,a.status_code

, actual_start_date

, actual_completion_date

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C') avg_mins

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

--and a.phase_code in ('I','P','R','T')

AND a.phase_code = 'C'

AND actual_start_date > sysdate - 20

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND ctl.LANGUAGE = 'US'

AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10

AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >

( SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C'

ORDER BY 5 DESC;

You are welcome to leave a comment....

ORACLE SERVER ARCHITECTURE

2013-04-18T14:48:00.001+05:30

ORACLE SERVER ARCHITECTURE

You are welcome to leave a comment....

Profile Option Values for Oracle DB User

2013-04-18T14:45:00.001+05:30

Profile Option Values for Oracle DB User

Oracle DB User Profile Option Values
FAILED_LOGIN_ATTEMPTS	Means for how many times you can try to login.. After these many sequential unsuccessful tris to login, the user account will be locked for the days specified in PASSWORD_LOCK_TIME. (i.e. LOCKED (TIMED)).
PASSWORD_LOCK_TIME	for this much amount of time, your password wil be locked, if your account is locked because you are failed to login successfully .
PASSWORD_GRACE_TIME	the number of days, after your password expires. During that time, user can use that password, but reminded to change password.
PASSWORD_LIFE_TIME	the number of days, the password can remain in force.
All the possible values are decided by below mentioned parameters
OPEN	you can use this acccount to login.
EXPIRED	Account is expired as you have tried to unsuccessfully login for 3 times.
EXPIRED(GRACE)	Means, account is expried but grace is given to change the password.
LOCKED(TIMED)	Account is locked as you have tried to unsuccessfully login for FAILED_LOGIN_ATTEMPTS times.
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED

You are welcome to leave a comment....

Internal login flow in Oracle Applications

2013-04-18T14:38:00.004+05:30

Internal login flow in Oracle Application

1.DB connection is established using APPLSYSPUB user. This user “APPLSYSPUB” is having access to fnd_user view and a small set of other public tables needed to establish the initial connection.

2.Once DB connection is established FND_USER table is used to get ENCRYPTED_FOUNDATION_PASSWORD.

3.Using GUEST/ORACLE password combination and ENCRYPTED_FOUNDATION_PASSWORD string obtained from 2 above, we get Apps schema password.

4.Internally it tries to connect to Apps schema with the password retrieved in step 3 above. If the connection fails, then

   a)The GUEST username password is incorrect

   b)The FNDNAM (APPS) environment variable is set incorrectly.

   c)Some other problem prevented a connection

5 Using Apps password obtained in Step 3 and ENCRYPTED_USER_PASSWORD string from FND_USER table, password for Application user (e.g. SYSADMIN) is obtained.

6. The Application user password obtained in Step 5 is compared to the application user password entered by user in login screen. If both passwords match then user is allowed to get into self-service.

7. A list of responsibilities assigned to that user is shown. The user picks one of the responsibilities listed.

8. Each responsibility has a data group and application defined for it.Together, the data group and application define the Oracle account the responsibility uses.
You are welcome to leave a comment....

Identify The Nodes with Context Parameters

2013-04-18T14:35:00.001+05:30

Identify The Nodes with Context Parameters

Context File Parameter	Description
s_isDB	Identifies node as a Database Node for autoconfig to create control scripts.
s_isAdmin	Identifies node as an Admin Node for autoconfig to create control scripts.
s_isWeb	Identifies node as a Web Node for autoconfig to create control scripts.
s_isForms	Identifies node as a Forms Node for autoconfig to create control scripts.
s_isConc	Identifies node as a Concurrent Processing Node for autoconfig to create control scripts.
s_isAdadmin	Identifies node’s APPL_TOP as being used to support the Oracle Applications system.
s_isAdWeb	Identifies node’s APPL_TOP as being used to support Web services.
s_isAdForms	Identifies node’s APPL_TOP as being used to support Forms services.
s_isAdConc	Identifies node’s APPL_TOP as being used to support Concurrent Processing services.

You are welcome to leave a comment....

Basic Layers of EBS R12

2013-04-18T14:31:00.003+05:30

Basic Layers of EBS R12

You are welcome to leave a comment....

Life Cycle of Concurrent Request

2013-04-18T14:29:00.001+05:30

Life Cycle of the Concurrent Request

You are welcome to leave a comment....

How to recover and open the database if the archive log required for recovery is missing

2013-04-17T12:46:00.000+05:30

How to recover and open the database if the archive log required for recovery is missing

Last week, I had one interesting recovery scenario at client side; we had to recover one of our development databases from old backup.
As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.

It was critical for us to recover database because of some project deadline.

Error:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’

After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.

We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.

Alert log shows below error

Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340

To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.

As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.

You are welcome to leave a comment....

Getting Tech Stack Information on 11i and R12

2013-04-17T12:34:00.002+05:30

Getting Tech Stack Information on 11i and R12

We have to collect this information whenever boss ask :-). In Oracle Apps 11i and R12 instances we have txkInventory.pl and TXKScript.pl which can help us to get this information.
As applmgr user execute below command:
perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPL_TOP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$OA_HTML/apps_techinfo.html

As Oracle user execute below command:
perl $ORACLE_HOME/appsutil/bin/TXKScript.pl -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$ORACLE_HOME/appsutil/temp/oracle_techdb.html.

You are welcome to leave a comment....

Installing VMware with Oracle Enterprise linux 64 bit

2013-04-17T12:20:00.001+05:30

Installing VMware with Oracle Enterprise linux 64 bit

In this post am explaining the how to install the VMware. Below is the documentation for the same. If you want the screen shots for the same you can find My Previous post. I have put screens for installing VMware on Windows 7 and Oracle Enterprise linux over and above VM. Here are the explaination of all figures.

Fig 1 : This is the first screen which will see after you click on installation file for VMware 2.0.1, which I downloaded from http://www.vmware.com/download/server.
Fig 2 : It ask for location. I installed it on my USB external drive so I have it as F drive.
Fig 3 : Now Enter FQDN and change VM Storage Path too.
Fig 4 : Installation in Progress.
Fig 5 : Once the installation is done, you will have an icon for VMware to connect. Just double click on it and make sure you added hostname.domainname in the windows/system32/driver/etc/hosts file. And in IE trusted domain too. Make sure you have username and password for the user you installed on windows session. As it takes Windows username and password for access. Once you are in we have to create Virtual Machine. This is the first screen where you have to give VM name. I gave it RAC1.
Fig 6 : Select Guest operating system, I selected Linux and in version other linux 64 bit.
Fig 7 : Give Memory size, I gave it 1024 MB.
Fig 8 : Create VM disk, I have size of 10GB for this.
Fig 9 : Network Connection keep default.
Fig 10 : Select the place where you will have your Guest OS. As I have DVD for the same I selected E drive.
Fig 11 : I didn't added Floppy Drive and Added USB Controller.
Fig 12 : That's the Final screen and select Finish.
Fig 13 : Once you login now you will see RAC1 (VM), but you have to install some add in for VM connection, so look at the screen and install it.
Fig 14 : Add-in getting installed.
Fig 15 : Once you power on the VM and click on that, make sure you have DVD in your Laptop, as it will going to install OS now.
Fig 16 : Select yes on this message.
Fig 17 : Keep selection as shown in fig.
Fig 18 : Select yes on this figure.
Fig 19 : Add hostname and IP manually.
Fig 20 : Add Root Password.
Fig 21 : Click next to start installation.
Fig 22 : Progress and you are done with installation. It will ask for reboot and some more question to answer and you are done with installation.

You are welcome to leave a comment....

Installing VMware with Oracle Enterprise Linux 64 bit

2013-04-17T12:11:00.002+05:30

Installing VMware with Oracle Enterprise Linux 64 bit

Step by step Screens for Installing VMware with Oracle.

In this post am explaining the how to install the VMware with screen shots.If you want the Documentation for the same you can find My Next post. I have put screens for installing VMware on Windows 7 and Oracle Enterprise linux over and above VM. Here are the explaination of all figures.

For Step by step process please follow the my next Post... Next

You are welcome to leave a comment....

Finding XML Publisher Version in R12

2013-04-17T11:41:00.001+05:30

Finding XML Publisher Version in R12

1) The following SQL statement will show which patches and thus with version of Oracle XML Publisher is installed on an Oracle E-Business Suite Release 12.0 and 12.1 environment:

SELECT DECODE (bug_number, '4440000', '5.6.3') PATCH, bug_number
FROM ad_bugs WHERE bug_number IN('4440000');
PATCH BUG_NUMBER
----- ------------------------------
5.6.3 4440000

2) The version of a specific Java Class has a one to one relationship with the currently installed version of Oracle XML Publisher. This method is suitable for both Oracle E-Business Suite 11 and Release 12.

$ adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class:
$Header MetaInfo.java 120.6 2006/08/17 01:25:30 bgkim noship $

Metainfo.class XML Publisher Patch Reference Included in ... R12
120.6 5.6.3 4440000 Note 422508.1

OPP jobs failing with time out

2013-04-17T11:39:00.001+05:30

OPP jobs failing with time out

Log file : The Output Post-processor is running but has not picked up this request. No further attempts will be made to post-process this request, and the request will be marked with Warning status.Setting the profile option Concurrent: OPP Response Timeout to a higher value may be necessary.

Solution:
Increase the value of following profiles:
Concurrent:OPP Process Timeout
Concurrent:OPP Response Timeout

Session Time out Setting in R12

2013-04-17T11:22:00.001+05:30

Session Time out Setting in R12

Many occassions we have seen end users reporting session timed out even though they were working on. This is for sure its an issue from client end and JRE version which client is running on is the culprit for the same. Try to have the same version of JRE which is running on your server or latest one.
Even if this doesn't help try at client end following setting with running JRE:
1. Navigate to: Start > Control Panel > Java > Advanced > Java Plug-in
2. De-select the option "Enable the next-generation Java Plug-in "
3 Restart the browser
4-Retest the issue

Next if you want to increase timeout setting from server end. Do the following:
1. Change profile option ICX: Session Timeout to 60 min from 30, which is default.
2. change s_oc4j_sesstimeout value to 60 again in context file and run adconfig.
3. Bounce the apps services and check.

RW-50015 : Installation Error On Oracle EBS R12

2013-04-04T16:50:00.004+05:30

RW-50015 : Installation Error On Oracle EBS R12

RW-50015: Error: - HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button.

the Rapid Install window will show many errors for HTTP.

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: PROD_ebs.ebs.sandiego.com

    -------------------+--------------------+---------+---------
    ias-component | process-type | pid | status
    -------------------+--------------------+---------+---------
    OC4J | oafm | 21168 | Alive
    OC4J | forms | 21078 | Alive
    OC4J | oacore | 20943 | Alive
    HTTP_Server | HTTP_Server | N/A | Down

Download and apply the patch 6078836 from OracleMetaLink to fix an issue with the Oracle HTTP Server bundled with the E-Business Suite technology stack. We unzip the patch:

    [oracle@ebs oracle]$ unzip p6078836_101330_LINUX.zip
    Archive: p6078836_101330_LINUX.zip
    creating: 6078836/
    inflating: 6078836/libdb.so.2
    inflating: 6078836/README.txt

    [oracle@ebs oracle]$ cd 6078836/
    [oracle@ebs 6078836]$ ls
    libdb.so.2 README.txt

    Then we copy to /usr/lib.

    [root@ebs 6078836] $ cp libdb.so.2 /usr/lib

    [root@ebs lib]# cd /usr/lib

    [root@ebs lib]# pwd
    /usr/lib

    [root@ebs lib]# ls libdb.so*
    libdb.so libdb.so.2


Processes in Instance: PROD_ebs.ebs.sandiego.com

    -------------------+--------------------+---------+---------
    ias-component | process-type | pid | status
    -------------------+--------------------+---------+---------
    OC4J | oafm | 21168 | Alive
    OC4J | forms | 21078 | Alive
    OC4J | oacore | 20943 | Alive
    HTTP_Server | HTTP_Server | 28519 | Alive .

We then re-start rapidwiz

You are welcome to leave a comment.

Oracle Apps R12 (12.1.1) Installation Issue – RW-50004 , RW-50010

2013-04-04T16:35:00.000+05:30

Oracle Apps R12 (12.1.1) Installation Issue – RW-50004 , RW-50010

Technology Stack changes in 12.1.1

1. Default database version is 11g (10g in 12.0.X ; 9i in 11i)

2. Default forms and report version is 10.1.2.3 (10.1.2.2 in 12.0.4 ; 8.0.6 in 11i)

3. Default Web Server version is 10.1.3.4 (10.1.3.0.X in 12.0.X ; 9i/1.0.2.2.2 in 11i).

Disk Space for 12.1.1

Vision Database 194 GB

Application Tier 30 GB

Installation Issue Oracle Apps Installation is 5 step process

Step1 & Step 2 are database tier related

Step 1 – 0-20% - Oracle Database Software Installation including registration of DB ORACLE_HOME in inventory. To know more about inventory click here

Step 2 - 20-40% - Oracle Database creation including copy of data files from staging area to data top

Install log file for 0-40% is located at $INSTALL_BASE/ db/ tech_st/ 11.1.0/appsutil/ log/<context_name>/MMDDHHMM.log

Step 3, 4 and 5 are application tier related

Step 3 – 40-60% – Oracle Technology Stack Installation (10.1.2 and 10.1.3 ORACLE HOME) for Application Tier

Step 4 – 60-80% – APPL_TOP and PRODUCT Top setup

Step 5- 80-100% – Configuration, Startup and Checks

Install log file for 60-100% is located at $INSTALL_BASE/ inst/ apps/ <context_name>/ logs/ MMDDHHMM.log

At around 37% Installer failed with message in log file at $Install_Base/db/ tech_st/ 11.1.0/ appsutil/ log/ $SID_HOSTNAME/ MMDDHHMM.log -> RW-50010: Error: – script has returned an error RW-50004: Error code received when running external process. Check log file for details.

after several unsuccessful attempts I looked at /var/log/messages and found messages like

Out of Memory: Killed process 2592 (ovisr12) .

OOM-KILLER - The OOM killer, a feature enabled by default (on Linux) to protect system from hang by terminating processes using high memory.

If kernel cannot find memory to allocate when it’s needed, it puts in-use user data pages on the swap-out queue, to be swapped out. If the Virtual Memory (VM) cannot allocate memory and cannot swap out in-use memory, the Out-of-memory killer may begin killing current user-space processes. It will sacrifice one or more processes in order to free up memory for the system when all else fails.

Solution :

Turn oom-killer off:

# echo “0″ > /proc/sys/vm/oom-kill (turn off)

# echo “1″ > /proc/sys/vm/oom-kill (turn on) More information hereThis issue is also reported in Metalink Note # 460790.1

Other possible reasons for RW-50004/RW-50010

1. If RW-50004/10 @ ORACLE_HOME registration with inventory (DB_HOME or Oracle Application Server 10.1.2 & 10.1.3 HOME); check privileges to oraInventory location

If this is re-install (from previous failed installation), check ORACLE_HOME is not already registered in Inventory>

2. Corrupt media file – For error like AC-00023: Error: Exception: java. io. File NotFound Exception with RW-50004/10
You are welcome to leave a comment.

Database Server Upgrade/Downgrade Compatibility Matrix

2013-03-22T15:53:00.004+05:30

Database Server Upgrade/Downgrade Compatibility Matrix

Upgrade Compatibility Matrix for Upgrading to 11.2.x

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

Source Database	Destination Database
9.2.0.8 (or higher)	11.2.x
10.1.0.5 (or higher)	11.2.x
10.2.0.2 (or higher)	11.2.x
11.1.0.6 (or higher)	11.2.x

The following database version will require an indirect upgrade path.

Source Database	Upgrade Path	Destination Database
7.3.3 (or lower)	=> 7.3.4.0 => 9.2.0.8 =>	11.2.x
8.0.5 (or lower)	=> 8.0.6.x => 9.2.0.8 =>	11.2.x
8.1.7 (or lower)	=> 8.1.7.4 => 10.2.0.4 =>	11.2.x
9.0.1.3 (or lower)	=> 9.0.1.4 => 10.2.0.4 =>	11.2.x
9.2.0.7 (or lower)	=> 9.2.0.8 =>	11.2.x

Upgrade Compatibility Matrix for Upgrading to 11.1.x

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

Source Database	Destination Database
9.2.0.4 (or higher)	11.1.x
10.1.0.2 (or higher)	11.1.x
10.2.0.1 (or higher)	11.1.x

The following database version will require an indirect upgrade path.

Source Database	Upgrade Path	Destination Database
7.3.3 (or lower)	=> 7.3.4.0 => 9.2.0.8 =>	11.1.x
8.0.5 (or lower)	=> 8.0.6.x => 9.2.0.8 =>	11.1.x
8.1.7 (or lower)	=> 8.1.7.4 => 9.2.0.8 =>	11.1.x
9.0.1.3 (or lower)	=> 9.0.1.4 => 9.2.0.8 =>	11.1.x
9.2.0.3 (or lower)	=> 9.2.0.4.0 =>	11.1.x

Upgrade Compatibility Matrix for Upgrading to 10.2.x

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

Source Database	Destination Database
8.1.7.4 (or higher)	10.2.x
9.0.1.4 (or higher)	10.2.x
9.2.0.4 (or higher)	10.2.x
10.1.0.2 (or higher)	10.2.x

The following database version will require an indirect upgrade path.

Source Database	Upgrade Path	Destination Database
7.3.3 (or lower)	=> 7.3.4 => 8.1.7 =>8.1.7.4 =>	10.2.x
7.3.4 (or lower)	=>8.1.7 => 8.1.7.4 =>	10.2.x
8.0.n (or lower)	=>8.1.7 => 8.1.7.4 =>	10.2.x
8.1.n (or lower)	=>8.1.7 => 8.1.7.4 =>	10.2.x

For more info refer Oracle Note ID: 551141.1

Gather Schema Statistics" program error with Locks and Duplicate columns

2013-03-22T15:53:00.000+05:30

Gather Schema Statistics" program error with Locks and Duplicate columns

Error

*******

**Starts**14-APR-2012 23:58:43

**Ends**15-APR-2012 05:34:48

ORA-0000: normal, successful completion

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

Start of log messages from FND_FILE

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

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 24 internal_flag= NOBACKUP

stats on table AQ$_WF_CONTROL_P is locked

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

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

End of log messages from FND_FILE

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

Successfully resubmitted concurrent program FNDGSCST with request ID 35759846 to start at 22-APR-2012 00:00:00 (ROUTINE=AFPSRS)

Solution

***********

1. Please follow Doc ID 375351.1 to unlock the tables:

AQ$_WF_CONTROL_P

FND_CP_GSM_IPC_AQTBL

FND_SOA_JMS_IN

FND_SOA_JMS_OUT

From DB node run below to unlock tables

exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');

exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');

exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');

exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_OUT');

2. Please perform the following action plan from Doc ID 781813.1 :

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

SQL> create table FND_HISTOGRAM_COLS_BKP as select * from FND_HISTOGRAM_COLS;

-- identify duplicate rows

select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and column_name = '&COLUMN_NAME'

and rownum=1;

-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in

(

select hc.table_name, hc.column_name

from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

where hc.table_name ='&TABLE_NAME'

and hc.table_name= tc.table_name (+)

and hc.column_name = tc.column_name (+)

and tc.column_name is null

);

3. Run "Gather Schema Statistics" program again.

Data Guard Status Check Queries

2013-03-22T15:51:00.002+05:30

Data Guard Status Check Queries

1. Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

2. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.

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

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

3. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

4. Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

5. To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view.

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

6. The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

7. Determining Which Log Files Were Not Received by the Standby Site.

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

8.If a delayed apply has been specified or an archive log is missing then switchover may take longer than expected.

Check v$managed_standby

select process, status, sequence# from v$managed_standby;

OR alternatively:

select name, applied from v$archived_log;

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp Manul forms deployment script error

2013-03-22T15:50:00.002+05:30

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp Manul forms deployment script error

Error

-----

Errors encountered running /erpchxd2/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkCfgOC4JApp.pl

...

Command error: <rc> = 32512, <command> = /erpchxd2/apps/tech_st/10.1.3/opmn/bin/opmnctl start

Solution

--------

1. Backup file $IAS_ORACLE_HOME/opmn/bin/opmnctl

2. Replace ORACLE_HOME entry with $ORA_CONFIG_HOME/10.1.3 in opmnctl file

example: ORACLE_HOME=/sid/product/1013; export ORACLE_HOME replace with ORACLE_HOME=/sid/inst/apps/SID_hostname/ora/1013;export ORACLE_HOME

Run the forms deployment script again

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp

If the error again

3. Backup file $FND_TOP/patch/115/bin/txkCfgOC4JApp.pl

4. Replace record $proc ->run ( { command => { text => "$oh/opmn/bin/opmnctl", with

$proc ->run ( { command => { text => "/<BASE_PATH>/<SID>/inst/apps/<SID>_<hostname>/admin/scripts/adopmnctl.sh",

Run the forms deployment again

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp

5. Once after completing the deployment , revert the changes

APPLICATIONS AND WEB BROWSER CACHE CLEAR

2013-03-22T15:49:00.002+05:30

APPLICATIONS AND WEB BROWSER CACHE CLEAR

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

Apache / iAS Cache clear for 11.5.9 or 11.5.10.x

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

- shutdown iAS server

- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory

- backup the directory _pages and delete its contents by running for instance:

rm -rf $COMMON_TOP/_pages/*

- for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory

- restart iAS server

Cache clear on middle tier for R12

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

Note: Clearing _pages directory is no longer a recommended solution.

Clearing the _pages in R12 creates blank login page issue, as in R12 the jsp files does not get compiled automatically.

From Forntend

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

- go to "Functional Administrator" responsibility

- select Core Services => Caching Framework => Global Configuration => Clear cache

From backend

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

- adopmnctl.sh stopall

- rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*

- rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*

- rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

- adopmnctl.sh startall

Cabo

-----

Images and style sheets can be corrupted or out of sync in the cabo caches,you may need to clear the related directories after backup:

- $OA_HTML/cabo/images/cache

- $OA_HTML/cabo/styles/cache

For Portal

----------

- go to the url: http://<host.domain>:<port>/pls/admin_/gateway.htm or cache.htm

- click on "Cache Settings" option and note the "Cache Directory"

- go to this directory and delete all the cache files in the directory and sub directories

WebADI / BNE cache

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

For instance when enabling BNE log

- go to url: http://<host.domain>:<port>/oa_servlets/oracle.apps.bne.framework.BneAdminServlet

- click on the "clear-cache" link

- at the bottom of the page you should see 'Cache Cleared'

How to clear cache for Web Browsers

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

For Netscape:

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

- go to menu Edit => Preferences…,

- choose 'Cache' in 'Advanced' category,

- click on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.

- close all Netscape windows and restart new browser session.

For Internet Explorer:

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

- go to menu Tools => Internet Options…,

- select Genera' tab,

- click on button 'Delete Files' in 'Temporary Internet files' zone

- close all IE windows and restart new browser session.

For Mozilla Firefox:

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

- go to menu Tools

- select 'Clear Private Data...' to clear the entire cache

- go to menu Edit or Tools

- select Preferences or Options.

- expand the Advance' options and choose Cach' or Privacy

- click the button called Clear Cache.

For Safari:

----------

- go to Safari menu

- select Empty Cache => Click 'Empty' in the dialogue box

For Jinitiator

-----------

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:

- delete all files in directory:

C:\Program Files\Oracle\Jinitiator <version>\jcache\

for 1.3.1.x versions:

- go to Start => Parameters => Control Panel

- double-click on "Jinitiator <version>" icon

- in the new pop-up window, click on "Cache" tab

- click on "Clear Jar Cache " button. On prompt, click Yes.

(you can also delete directly all files under directory: C:\Documents and Settings\<your user name>\Oracle Jar Cache)

For Java

--------

Java/JRE plug-in (Windows)

- go to Start => Parameters => Control Panel

- double-click on "Java" icon

- in the new pop-up window, click on "General" tab

- click on "Delete Files..." button (you can select Applets, Applications or other files before)

For JVM

--------

- go to responsibility "Functional Administrator"

- click on "Core Services" tab then the "Caching Framework" sub-tab

- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's