RSS

Category Archives: E-Business Suite

After R12 Clone: Consult the OPP service log for details

After make a clone of R12, the new instance gave “Completed, Warning” for all XML output requests the request log show the following:

Post-processing of request 509561 failed at <timestamp> with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.

you can review the OPP log file from the following query to find the location and file name for the OPP log:

SELECT 
fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name 
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp 
WHERE fcpp.processor_id = fcp.concurrent_process_id
 AND fcpp.action_type = 6
 AND fcpp.concurrent_request_id = &request_id;

the OPP log file will be in the $APPLCSF/$APPLLOG with an name of “FNDOPP*.txt”

now the log file shows the error:

[UNEXPECTED] [50032:RT509560] oracle.apps.xdo.XDOException: Unable to create font cache directory: /u01/SID/instapps/CONTEXT_NAME/temp/xdofonts/SID

Solution:

1. Navigation Path:

(Responsibility) XML Publisher Administrator > (Menu) Home > (Tab) Administration > (SubTab) Configuration > Properties > General > Temporary directory

2. Provide a valid value to ‘Temporary directory’, and click button ‘Save’.

3. Re-test the issue.
 

Tags: , , , ,

Bad Performance in R12 Online AP Create Accounting

According to the helpful Oracle Support DocId:791049.1 ::R12 SLA/FAH: How to Improve Performance in Subledger Accounting & Financials Accounting Hub
in the 4th FAQ which explain how to capturing trace and/or tkprof.

1. Enable tracing using the profile option – ‘Initialization SQL Statement – Custom’
Set this at the user level with the following string:
BEGIN FND_CTL.FND_SESS_CTL(”,”, ”, ‘TRUE’,”,’ALTER SESSION SET TRACEFILE_IDENTIFIER=’||””||’XXXTEST’||””||’ EVENTS =’||””||’ 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ‘||””); END;
2. Login using the user for which you set the profile option.
3. Reproduce the issue.
4. Reset the profile option to its prior value.
5. Locate the generated trace, using the query:
select name, value from v$parameter where name like ‘user_dump_dest’;
6. Convert the trace file into tkprof format using the command:
tkprof <filename.trc> <output_filename_SORT.txt> explain=apps/<password> sort='(prsela,exeela,fchela)’

in the 5Th FAQ witch explain basic points to check before logging a performance bug. and one of these basic step is to stop the Debug logging”if it;s not nessessary, Or you can make it on USER level not SITE level”

Do you have any of the following profiles set to Yes at the User Level?
If so, then please disable them (i.e., set them to No) and re-run Create Accounting:

When running Create Accounting, the profile values should be set as follows:

FND: Debug Log Enabled = No
SLA: Enable Diagnostics = No
AR: Enable Debug Message Output = No — If it is for Receivables.

 

 

 

Tags: , , , , , ,

Oracle 11i / R12 – How To : How To Disable The Personal Profiles Form

Oracle 11i / R12 – How To : How To Disable The Personal Profiles Form.

 

Tags: , ,

BIB-7000 Cannot access X Server

Subject: ATTEMPING TO VIEW GRAPHICS OR TRYING TO CONFIGURE THE JAVA MAILER HANGS
Doc ID: Note:351839.1 Type: PROBLEM
Last Revision Date: 31-MAY-2006 Status: PUBLISHED

In this Document
Symptoms
Changes
Cause
Solution

Applies to:
Oracle Applications Manager – Version: 11.5.9 to 11.5.10
This problem can occur on any platform.
Symptoms

On entering Oracle Applications Manager (OAM) to check on the status of workflow components (Workflow Manager, “Go” button), under Workflow Metrics, both Work Items and Agent Activity displayed the message

“BIB-7000 Cannot access X Server”

Navigating to OAM to view Activity Monitors or other Graphics also reflect the same error.

Also, Configuring the Java Mailer in OAM – the page hangs and then displays Page Cannot be Displayed
Changes
OAM requires the jserv.properties “wrapper.env=DISPLAY” setting to be consistent throughout the application environment and also it needs to correspond to the correct server where xhost is enabled.
Cause

The DISPLAY setting is not consistent throughout the environment or xhost has not been enabled on the server where the DISPLAY setting is directed to.

If DISPLAY is set and xhost was enabled, another cause is, that the xhost session was not left running on the Xserver machine.
Solution

As ROOT on the Console machine with the Graphics Display

1) $ uname -n

> record the exact value returned

2) – For LINUX:

$ cd /usr/X11/bin
or
$ cd /usr/X11R6/bin

– For SOLARIS:

$ cd /usr/openwin/bin

– For AIX & HP-UX see Note: 361535.1 to setup ‘Headless’ support for xServers

3) $ xhost +

> LEAVE THIS SESSION OPEN ON CONSOLE — DO NOT “SU APPLMGR”

4) Open a New Session and signon as APPLMGR

$ adstpall.sh apps/apps

> Stop all the services on all nodes in the environment

5) Open a New Session and signon as APPLMGR or clear the env of the previous APPLMGR session

$ DISPLAY=<same value as #1>:0.0; export DISPLAY

> See the “:0.0” , make sure that is specified

6) $ echo $DISPLAY

> ex:

$ <hostname from #1>:0.0

7) On all the Nodes in this environment — if this is a multiple node, then repeat on cp, forms/web & admin nodes

$ cd $IAS_ORACLE_HOME/Apache/Jserv/etc

$ cp jserv.properties jserv.properties.bak

8) $ vi jserv.properties

> Search down for “wrapper.env=DISPLAY”

9) Reassure or change the setting to match the following syntax ( repeat on cp, forms/web & admin nodes)

wrapper.env=DISPLAY=<hostname from #1>:0.0

> The hostname value in the DISPLAY must be the same in all nodes, it must be set to the value of “uname -n”
from step #1

> Setting this in OAM > AutoConfig > Edit Parameters and then running AutoConfig on all nodes
with accomplish the same thing

10) Restart all the services in the same APPLMGR sessions

 

Tags: , , ,

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1

Main “steps” documents id:

362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1: Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07

Other doc_id: 881505.1, 1367654.1

Troubleshooting:

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

Contents

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1. 1

Before the Database Installation and Upgrade. 3

I.       Interoperability “Pre”: 3

Database Upgrade: 4

II.      Pre-Export the database: 4

III.         Pre-Import the database: 6

IV-         Export the database: 9

V-     Import the database: 10

IV-         Post-Import and database configuration: 11

Before the Database Installation and Upgrade

       I.            Interoperability “Pre”:

From doc_id: 1265268.1 -> Section1: Upgrading an E-Business Suite 11i Database to Oracle Database 11g Release 2 (11.2.0)

1)   Verify S/W and system’s versions:

a)      Ebs 11.5.10.2

b)      ATG Rollup Patch 6

c)       Developer 6i Patch set 18

d)      AutoConfig Latest version with 9835302

2)   Nothing to do

3)   Nothing to do

4)   Deregister the current database server

To deregister the current database server node because If you plan to change the database port, host, SID, or database name parameter, run the following command as the owner of the Oracle RDBMS file system and current database instance:

$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl

appspass=[APPS db user password]

contextfile=$CONTEXT_FILE -removeserver

5)   Update application tier context file with new database listener port number 

If you want to continue use the APPS tier, modify XML file with new database info. OR just make new cloned instance to connect to new DB tier.

s_dbhost New database hostname
s_dbdomain New database domain name
s_db_serv_sid New database SID
s_dbport New database listener port
s_apps_jdbc_connect_descriptor NULL

6)   Upgrade the database instance

362205.1:   10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1:   Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2)  part no. E23633-07

According to previous documents the upgrade will be using import/export method.

Database Upgrade:

    II.            Pre-Export the database:

From doc_id: 362205.1 -> Section1: Prepare the source system

From doc_id: 557738.1 -> Section1: Prepare the source system

7)   Apply the Applications consolidated export/import utility patch

Apply both patches for exporting 10 (4872830) and 11g (1235353).

8)   Apply latest Applications database preparation scripts patch 

Apply Patch 7225862 to every application tier server node in the source system.

9)   Create a working directory on source database node

Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

10)       Generate target database instance creation script adcrdb.sql (10g),  aucrdb.sql (11g)

11g $sqlplus system/manager  @$AU_TOP/patch/115/sql/auclondb.sql 11

10g $sqlplus system/manager  @$AU_TOP/patch/115/sql/adclondb.sql

Then edit this script to reflect the new environment.

“Split it into 2 files, first one for CREATE DATABASE statement include in it the UNDO TABLESPACE section. The other file is to the rest of tablespaces adcrdb_db.sql and adcrdb_tbs.sql”.

Also adpostcrdb.sql which you use to convert tablespaces to locally managed will be created.

11)       Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql in the current directory.

12)       Remove rebuild index parameter in spatial indexes 

SQL> select * from dba_indexes where index_type=’DOMAIN’ and

upper (parameters) like ‘%REBUILD%’;

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index [index name] rebuild parameters (‘parameters’)

Where [parameters] is the original parameter set without the rebuild_index parameter.

13)       Synchronize Text indexes

$ sqlplus ‘/ as sysdba’

SQL> select pnd_index_owner,pnd_index_name,count(*)

from ctxsys.ctx_pending

group by pnd_index_owner,pnd_index_name;

To synchronize the indexes, run the following command:

SQL> exec ctx_ddl.sync_index(‘[index owner].[index name]’);

14)       Drop SYS.ENABLED$INDEXES (conditional) 

SQL> drop table sys.enabled$indexes;

15)       Shut down Applications server processes and database listener 

 III.            Pre-Import the database:

16)       Prepare to create the 11.2.0 Oracle home 

The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home

a) Finish OS prerequisites for new Oracle home “new host or exist host”.

b) Oracle OS owner profile option and environment variables.

17)       Install the base 11.2.0 software: install  software only

18)       Install Oracle Database 11g Products from the 11g Examples CD 

19)       Create nls/data/9idata directory

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

20)       Apply additional 11.2.0.1 RDBMS patches  “9 patches using opatch”

Append Opatch tool to PATH,

$export PATH=$PATH:$ORACLE_HOME/OPatch

and run the following patches:

8570322

8685327

8761974

8771297

12672969-> instead of 8772028

8796511

8897784

8964142

8405205

21)       Create working directory on target database server.

Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

22)       Copy scripts needed

Copy the following from Source environment “database or application servers” to the working directory on target database:

  1.         I.            Copy initialization parameter file and CBO parameter file (initSID.ora and ifilecbo.ora “if exists”) from source database server, modify wanted parameter
  2.       II.            Copy modified database creation scripts (adcrdb_db.sql and adcrdb_tbs.sql and adpostcrdb.sql) from source database server
  3.     III.            Copy database preparation scripts (addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql) OR (audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql) from $APPL_TOP/admin directory of the source administration server node.
  4.     IV.            Copy auque2.sql generated file on step 18 from source database server working directory.
  5.       V.            Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the target database server node
  6.     VI.            Copy $APPL_TOP/admin/adgrants.sql sql from the administration server node to the working directory in the target database server node.
  7.   VII.            Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the working directory in the target database server node.
  8. Create appsutil.zip file

perl <AD_TOP>/bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

Then copy it to new ORACLE_HOME then

unzip -o appsutil.zip

23)       Create the target database instance

Use modified adcrdb.sql script, generated from step 17

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool adcrdb.log;

SQL> startup nomount;

SQL> @adcrdb_db.sql

SQL> @adcrdb_tbs.sql

SQL> @ adpostcrdb.sql

SQL> exit;

24)     Set up the SYS schema
the audb1110.sql, audb1120.sqlor addb1020.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run as sys.

$ sqlplus “/ as sysdba” @/u01/expimp/audb1120.sql

25)     Set up the SYSTEM schema
the ausy1110.sql, ausy1120.sql or adsy1020.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system

$ sqlplus system/manager @/u01/expimp/ausy1120.sql

26)     Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql or adjv1020.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system.

$ sqlplus system/manager @/u01/expimp/aujv1120.sql

27)     Install other required components 

the aumsc1110.sql, aumsc1120.sql or admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and as system.

$ sqlplus system/manager @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP

28)     Install custom RDBMS components (conditional)
if you have other custom RDBMS components loaded in the source database such as Label Security; install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:

SQL> select * from dba_registry;

29)     Disable automatic gathering of statistics
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

30)       Back up the target database instance
the target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.

IV-             Export the database:

31)       Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat template file and edit it with proper values as following:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

filesize=1G

full=y

logfile=full_expdpapps.log

32)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

33)       Shut down Applications server processes

34)       Grant privilege to source system schema

$ sqlplus / as sysdba

SQL> grant EXEMPT ACCESS POLICY to system;

35)            Export the Applications database instance

If the source database is 10.2.0 or 11.1.0, use the following command:

$ expdp system/[system password]

parfile=[export parameter file name]

If the source database is 11.2.0, use the following command:

$ expdp “‘/ as sysdba'” parfile=[export parameter file name]

36)       Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

V-                  Import the database:

37)       Create the import parameter file

Copy the export parameter file you created in Step 30 from the source database server node to the working directory in the target database server node, renaming it if necessary. Updating the new file with the following changes converts it to an import parameter file:

  • Remove the exclude parameters.
  • Remove the filesize parameter.
  • Change the name of the log file.
  • Uncomment the transform parameter.

It will be like this:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

full=y

transform=oid:n

logfile=impdpapps.log

38)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

39)       Copy the export dump files from source to target

40)       Set Oracle Text parameter

if the target database is 11.2.0, use SQL*Plus to connect to the target database as SYSDBA and run the following command to grant datastore privileges:

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter (‘file_access_role’, ‘public’);

41)            Import the Applications database instance

Start the import session on the target database server node using the customized import parameter file.

If the target database is 11.1.0, use the following command:

$ impdp system/manager parfile=[import parameter file name]

If the target database is 11.2.0, use the following command:

$ impdp “‘/ as sysdba'” parfile=[import parameter file name]

42)       Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> revoke EXEMPT ACCESS POLICY from system;

IV-             Post-Import and database configuration:

From doc: Export/import notes on Applications 11i Database 11g [ID 557738.1]

And doc: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

And doc Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]

43)       Reset Advanced Queues 

Run file created in step:11.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @/u01/expimp/auque2.sql

44)       Perform patch post-install instructions
run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.

45)       Run adgrants.sql

$ sqlplus “/ as sysdba” @adgrants.sql APPS

46)       Grant create procedure privilege on CTXSYS

$ sqlplus apps/apps @adctxprv.sql SYSTEM CTXSYS

47)       Set CTXSYS parameter

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);

48)       Deregister the current database server

$ sqlplus apps/apps

SQL> exec fnd_conc_clone.setup_clean;

49)       Some errors will be shown in the autoconfig

  • To avoid any error in running autoconfig in coming steps, review and verify the number and status of CTXSYS objects, synm. and grants.
  • To avoid any problem in INDEXES owned by CTXSYS, please run the following selected scripts from document: @jtfiappr.sql apps

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

  • enable applsys.wf_java_defered queue
  • some of grants may be lost so run the following list:
  • Compile the invalid objects.

$ sqlplus “/ as sysdba” @$ORACLE_HOME/rdbms/admin/utlrp.sql

50)       Generate your Database Context File

Ensure that shell reflect the oracle_home and bin.

From extracted appsutil.zip

cd <RDBMS ORACLE_HOME>/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps

answer the question to generate XML file.

51)       Generate and Apply AutoConfig Configuration files in DB tier.

Execute the following commands:

cd <RDBMS ORACLE_HOME>/appsutil/bin
adconfig.sh contextfile=XML file created in step 50

52)       Re-create custom database links
if the Oracle Net listener in the 11.2.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:

$ sqlplus apps/[apps password]

SQL> select db_link from dba_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:

$ sqlplus apps/[apps password]

SQL> drop database link [custom database link];

SQL> create database link [custom database link] connect to

[user] identified by [password] using

‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])

(PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))’;

where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect

the new Oracle Net listener for the database instance.

Drop database link “PRODUCTION.HQ.ASQ.COM”;

create database link “PRODUCTION.HQ.ASQ.COM”

using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.local)(PORT=1521))))(CONNECT_DATA=(SERVICE_NAME=UPGRD))’;

Drop public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM”;

create public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.HQ.ASQ.COM”;

create database link “EDW_APPS_TO_WH.HQ.ASQ.COM” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL”;

create database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.HQ.ASQ.COM”;

create database link “APPS_TO_APPS.HQ.ASQ.COM” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL”;

create database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.LOCAL”;

create database link “APPS_TO_APPS.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.LOCAL”;

create database link “EDW_APPS_TO_WH.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

53)       Generate and Apply AutoConfig Configuration files in APPS tier.

54)       Apply Oracle Human Resources (HRMS) patch 7721754

55)       Use adadmin to:

  • Compile invalid object
  • Re-create grants and synonyms using adadmin
  • Compile flexfield
 

Tags: , , , , , , ,

SQL to get profile option values list using specific criteria

 

[HTD000069] Oracle E-Business Suite Show Active Database Sessions Details (Machine, Program, Module, Username, Action,…… )