How to compile Oracle Apps Forms 11i/R12

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 (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

How to compile Oracle Apps

How To Trace a Concurrent Request And Generate TKPROF

Enable Tracing For The Concurrent Manager  Program

Select the Enable Trace Checkbox



Note : Checking the Trace Check box on the Concurrent Program gives an Event 10046
Level 8 trace. So even if the trace is set for Binds and Waits on the Submission form once the
concurrent program is encountered in the trace it will reset to level 8 so no binds
will be present in the trace after that point.

Turn On Tracing

• Responsibility: System Administrator


• Navigate: Profiles > System


• Query Profile Option Concurrent: Allow Debugging


• Set profile to Yes

Run Concurrent Program With Tracing Turned On

• Logon to the Responsibility that runs the Concurrent Program


• In the Submit Request Screen click on Debug Options (B)


• Select the Checkbox for SQL Trace

If the Debug option is greyed out & not updateable set the profile Concurrent: Allow Debugging to Yes

2. Find Trace File Name

Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
--- and prog.application_id = execname.application_id
and prog.executable_application_id = execname.application_id
and prog.executable_id=execname.executable_id;


To check the timeline of the request :

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');

You may produce a complete report reference of the request ID using the Note:187504.1 bde_request.sql Process and Session info for one Concurrent Request(11.5)

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where:

raw_trace_file.trc: Name of trace file
output_file:        tkprof out file
explain:            This option provides the explain plan for the sql
statements
sort:               This provides the sort criteria in which all sql
statements will be sorted.  This will bring the bad sql at
the top of the outputfile.
sys=no:             Disables sql statements issued by user SYS

How to check whether the port is opened or not from client machine

Status Check of port 8000 from Windows Operating System

Go to Start menu tab.   Then run ‘cmd,’ which will take you to the command prompt.

In the command prompt, you must type the following command and hit Enter:

telnet 123.456.789.86 8000

Oracle Fusion Application Architecture

Oracle Fusion Applications is set of business applications (CRM, HCM, HRMS, Financials..) deployed on Oracle Fusion Middleware  (as middleware) and using Oracle Database as data repository .

1. Oracle Applications is delivered as a Suite (collection of modules), but can be adopted in modules (suset of Suite).

2. Various Modules in Fusion Applications are
a) Oracle Fusion CRM (Customer Relationship Management) - customers, contacts, and resources
b)Oracle Fusion Financials - assets, ledgers, cash cycle, invoices and payments, accounts receivable, collections, and setup of sub-ledger accounting and tax configuration
c) Oracle Fusion HCM (Human Capital Management) - Employee management for an organization
d) Oracle Fusion Procurement  - procurement including requisitions, purchase orders, and supplier negotiations
e) Oracle Fusion PPM (Project Portfolio Management) - projects, budget, forecast, collect costs, bill customers, and report performance.
f) Oracle Fusion SCM (Supply Chain Management) - integrates and automates all key supply chain processes.
g) Oracle Fusion Setup - special product family for Functional Setup and setting up Home Page or Help.
h) Oracle Fusion GRC (Governance, Risk, and Compliance) - automated controls enforcement to enable sustainable risk and compliance management.

3. EAR, JAR, and WAR
a) EAR is enterprise application archive (enterprise application) which contains JAR and WAR files.
b) JAR is Java application Archive which contains Java EE application.
c) WAR is Web application Archive which contains Web Application.

Each product in Fusion Application typically has one-to-one relationship with EAR file, for ex. EarSales.ear is an enterprise application for product Sales.

4. Product Families (CRM, HCM, Financials) mentioned above contains one or more Java EE applications and are deployed on Oracle WebLogic Server.

5. An application can contain multiple products, and a product can also span across multiple  applications.



6.There are two type of components in Fusion Middleware , Java Components(deployed and managed by WebLogic) and System Component (managed by OPMN) and Fusion Applications uses both these components.

For basic concepts in Oracle Fusion Middleware click here

7.  Oracle Fusion Applications consists of following components
a) Oracle Fusion Applications (CRM, HCM, Projects, Financials…)
b) Oracle Fusion Middleware Infrastructure Components for Fusion Applications (Enterprise Scheduler, FMW Extension for Applications, Crawl & Search and Functional Setup Manager)
Note: Enterprise Scheduler is similar to Concurrent Manager in EBS 11i/R12
c) Oracle Fusion Middleware (WebLogic, OHS, WebGate, OWSM, ADF, OBIEE, ODI, ECM, IDAM, IDM, WebCenter, SOA, OWSM..)
d) Database

8. Task of Fusion Applications Administrators
a) Installation and Deployment
b) Management & Availability
c) Debugging/Troubleshooting
d) Cloning
e) Backup & Recovery
f)Patching (Fusion Applications Patch Manager, AutoPatch, AD Controller)

9.Oracle Fusion Applications is currently available for Linux x86-64 from eDelivery edelivery

Implementing AutoConfig Customizations

How to customize AutoConfig in Oracle Apps 11i

Create the custom template directory

Create a directory named "custom" at the location where the AutoConfig template file resides.
For example, if you want to customize <FND_TOP>/admin/template/appsweb.cfg, execute the following command as the applmgr user:

mkdir $FND_TOP/admin/template/custom


Copy the AutoConfig template file

Copy the AutoConfig template file to the custom template file.

Execute the following command as the applmgr user:

cp -i <AutoConfig template file> <custom template file>
For example:
cp -i $FND_TOP/admin/template/appsweb.cfg \
$FND_TOP/admin/template/custom/appsweb.cfg

Edit the custom template file

Edit the custom template file with the editor of your choice, such as vi on UNIX or Wordpad on Windows.

Verify your customizations

Execute the adchkcfg utility. When this utility runs, it instantiates any custom template files in place of the corresponding AutoConfig template file. The adchkcfg utility generates a report with information about all files and profile options that will be changed during the next normal execution of AutoConfig. Verify that your customizations would be applied as expected in your next AutoConfig run.

Run AutoConfig

Run AutoConfig. When AutoConfig runs, it instantiates any custom template file in place of the corresponding AutoConfig template file.

Oracle Certification Exam Codes

1z0-001: Introduction to Oracle: SQL and PL/SQL

1z0-007: Introduction to Oracle9i: SQL

1z0-020: Oracle8i: New Features for Administrators

1z0-023: Architecture and Administration

1z0-024: Performance Tuning

1z0-025: Backup and Recovery

1z0-026: Network Administration

1z0-030: Oracle9i: New Features for Administrators

1z0-031: Oracle9i:Database Fundamentals I

1z0-032: Oracle9i:Database Fundamentals II

1z0-033: Oracle9i:Performance Tuning

1z0-035: Oracle 7.3 & 8 to Oracle9i DBA OCP Upgrade

1z0-036: Managing Oracle 9i on Linux

1z0-040: Oracle Database 10g: New Features for Administrators

1z0-041: Oracle Database 10g: DBA Assessment

1z0-042: Oracle Database 10g: Administration I

1z0-043: Oracle Database 10g: Administration II

1z0-045: Oracle Database 10g: New Features for Oracle8i OCPs

1z0-046: Oracle Datebase 10g: Managing Oracle on Linux for DBAs

1z0-047: Oracle Database SQL Expert

1z0-048: Oracle Database 10g R2: Administering RAC

1z0-050: Oracle Database 11g: New Features for Administrators

1z0-051: Oracle Database: SQL Fundamentals I

1z0-052: Oracle Database 11g: Administration I

1z0-053: Oracle Database 11g: Administration II

1z0-054: Oracle Database 11g: Performance Tuning

1z0-055: Oracle Database 11g: New Features for 9i OCPs

1z0-101: Develop PL/SQL Program Units

1z0-108: Oracle WebLogic Server 10g System Administration

1z0-109: Oracle WebLogic Server 10g Developer

1z0-131: Oracle9i, Build Internet Applications I

1z0-132: Oracle9i, Build Internet Applications II

1z0-140: Oracle9i Forms Developer: New Features

1z0-141: Oracle9i Forms Developer: Build Internet Applications

1z0-144: Oracle Database 11g: Program with PL/SQL

1z0-146: Oracle Database 11g: Advanced PL/SQL

1z0-147: Oracle9i: Program with PL/SQL

1z0-200: Oracle 11i E-Business Essentials

1z0-211: Oracle 11i General Ledger

1z0-212: Oracle Payables 11i Fundamentals

1z0-213: Oracle Receivables 11i Fundamentals

1z0-215: Oracle E-Business Suite R12: General Ledger and Payables Fundamentals

1z0-216: Oracle EBS R12: General Ledger and Receivables Fundamentals

1z0-222: Oracle Purchasing 11i Fundamentals

1z0-223: Oracle Order Management 11i Fundamentals

1z0-225: Oracle EBS R12: Inventory and Purchasing Fundamentals

1z0-226: Oracle EBS R12: Inventory and Order Management Fundamentals

1z0-232: Oracle 11i Systems Administrator

1z0-233: Oracle 11i Install, Patch and Maintain Applications

1z0-235: Oracle 11i Applications DBA:Fundamentals I

1z0-236: 11i Applications DBA Fundamentals II

1z0-238: EBS R12: Install, Patch and Maintain Applications

1z0-241: PeopleSoft Application Developer I: People Tools & PeopleCode

1z0-311: Oracle Application Server 10g: Administration I

1z0-312: Oracle Application Server 10g:Administration II

1z0-402: Oracle Enterprise Linux: Fundamentals

1z0-403: Enterprise Linux System Administration

1z0-450: Oracle Application Express 3.2: Developing Web Applications

1z0-451: Oracle SOA Foundation Practitioner

1z0-514: Oracle Database 11g Essentials

1z0-515: Data Warehousing 11g Essentials

1z0-516: Oracle EBS R12.1 General Ledger Essentials

1z0-517: Oracle EBS R12.1 Payables Essentials

1z0-518: Oracle EBS R12.1 Receivables Essentials

1z0-519: Oracle EBS R12.1 Inventory Essentials

1z0-520: Oracle EBS R12.1 Purchasing Essentials

1z0-521: Oracle EBS R12.1 Order Management Essentials

1z0-527: Oracle CRM On Demand Essentials

1z0-530: Oracle Enterprise Manager 11g Essentials

1z0-548: Oracle E-Business Suite R12.1 Human Capital Management Essentials Exam

Oracle Apps R12 New Features

Advantage & Disadvantages of Forms Servlet Mode

Simple SSL Configuration (as no separate SSL configuration is required for Forms as

connections are via web/http server)

No port need to open to access forms in firewall.

More secure method of deployment over Internet

Result in more network traffic because of HTTP is more chatty than socket (dedicated)

so not preferred in WAN implementation.

Unified APPL_TOP in Oracle Applications R12

Unified APPL_TOP in Oracle Applications R12 which is different from Separate

APPL_TOP in Oracle Applications 11i.

Unified APPL_TOP make more sense if you are using multi node Oracle Applications R12.

In Oracle Applications 11i , in multi node installation each APPL_TOP have its different
name and files in each APPL_TOP will depend on type of Node (i.e. Forms Node will
have fmx or forms related files where as APPL_TOP belonging to CM only node will
have .rdf or files required to run CM node)

Starting from R12, it will use Unified APPL_TOP which means all files required for all middle tier services are included in all nodes of Multi Node installation (irrespective of services running on that node)

Advantages of Unified APPL_TOP

Cloningof multi node Oracle Application install, only one copy (any one APPL_TOP)

of Application Node files need to copied to target instance.

Cloning of Multi Node to Single Node you don’t have to merge APPL_TOP in R12 as

required in 11i .

Services start/stopIf you are using adstrtall.sh to start services on a node then it will

start services which were configured to start during install on that node (You can still
start a specific service even though it was not suppose to configure/start by calling startup
script of that specific services).
To explain this better, let’s assume you installed multi node R12 instance with Node 1 as
Forms & Web Server where as Node 2 was installed with Concurrent Manager. Now on
Node1 when you use adstrtall.sh script to start services as expected it will start Forms and
Web services but unlike 11i, You can still start Concurrent Manager on Node1 by
running  adcmctl.sh

FND_NODESFrom R12 all nodes will have Y against all services (For multi Node) in

FND_NODES table.

Additional New Feature in R12

Rapid install

Store Base Configuration in Database

No need to copy config.txt

Multiple Domain Support for Database and Middle Tier

Rapid Install supports deploying the database node and the middle tier node into

different domains.

Shared File System Support

Rapid install allows you to select if app servers are using shared APPL_TOP

No hardcode apps password on Unix file system

FNDCPASS Utility New Feature: Enhance Security With Non-Reversible Hash

Password (enhance FND_USER password security)

Improved Forgot password feature

–Retrieve user_name and password

–no workflow inbound process needed

New environment variables e.g. FORMS_TRACE_DIR
FORMS_ environment variables replace FORMS60_
Delivers native runtime executables for Forms (frmweb) (11i: frmwebx)
Forms Trace in addition to Forms Runtime Diagnostics (FRD) record both built-in Formsdata and performance data in a single output (profile: Forms Runtime Parameters)
Reports Only available through Concurrent Manager
Reports Server is not used
Reports executable rwrun is used directly

APPS Password change made easy, no need to change the password in any files as in 11i.

How to change Oracle Applications Products/Schema Password including APPS ?

As most of you might already be aware that you use FNDCPASS utility on Concurrent

Manager Node to change the password.

FNDCPASS logon 0 Y system/password mode username new_password

WhereMODE is SYSTEM/USER/ORACLE/

ALLORACLE (Introduced with patch 4745998)

Changing APPS Password using FNDCPASS

example FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS  WELCOME

In 11i we need to change the passwords in the below files as it was used and hard coded,
in R12i there is no files where the password is stored, so we need not worry about
changing the passwords in any file, this is due to the security reason,

Only recommended is to run the autoconfig on the Middle Tier after changing the APPS Password.

In 11i files to change after changing Apps Password ?

Once you change apps password you need to change apps password stored in file

wdbsvr.app at $IAS_ORACLE_HOME/Apache/modplsql/cfg

Following Metalink Notes are useful & related to Oracle Applications or Apps Password
–303621.1 How to Change and Which Apps Database Users Passwords Can Be Changed
in a Multi – Node Apps Installation?
–159244.1 How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS
and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in UNIX.
– 437260.1 – How to Change Oracle Applications Release 12 Passwords using Oracle
Applications Schema Password Change Utility (FNDCPASS)

Interesting links of R12 EBS Installation

Release 12.1.x
Operating Systems Install & Upgrade Notes

Note 761569.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for IBM AIX Based Systems (64-bit)'
Note 762891.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for HP-UX Itanium'
Note 762894.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for HP-UX PA-RISC (64-bit)'
Note 761564.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86'
Note 761566.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64'
Note 761568.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Sun Solaris SPARC (64-bit)'
Note 761567.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Microsoft Windows Server (32-bit)'

Release Notes

Note 798258.1 - 'Oracle Applications Release Notes, Release 12.1.1'
Note 788053.1 - 'Oracle Applications NLS Release Notes, Release 12.1.1'

Release 12.0.x
Operating Systems Install & Upgrade Notes

Note 402310.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Linux (32-bit)'
Note 416305.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Linux (64-bit)'
Note 402312.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Solaris Operating System (SPARC)'
Note 402311.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Microsoft Windows'
Note 402306.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for AIX-Based Systems'
Note 402307.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for HP-UX Itanium'
Note 402308.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for HP-UX PA-RISC'

Note 555895.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for HP-UX PA-RISC'
Note 555893.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for HP-UX Itanium'
Note 555874.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for Linux (32-bit)'
Note 555876.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for Linux (64-bit)'
Note 555879.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for Microsoft Windows'
Note 555873.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for AIX-Based Systems'
Note 555877.1 - 'Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for Solaris Operating System (SPARC)'


Known Issues, How-To docs and Environment checks

Note 421409.1 - 'Unbreakable Linux Enviroment check before R12 install'
Note 343917.1 - 'Frequently Asked Questions Oracle E-Business Suite Support on x86-64'
Note 828157.1 - 'Useful Articles for the Oracle E-Business Suite Technology Stack for Technical Consultant / DBA'
Note 452120.1 - 'How to locate the log files and troubleshoot RapidWiz for R12'
Note 743518.1 - 'Starting up AS10g services in an EBusiness Suite Release 12 environment'
Note 733303.1 - 'How to migrate from Cygwin to MKS Toolkit'

Note 752933.1 - 'The Fresh R 12.0.4 installation on Linux RHEL5 (64-bit), Fails During The RDBMS Linking Phase'
Note 565966.1 - 'EBS 12.0.4 Install fails on Solaris While Creating the Database'
Note 579254.1 - 'Installing Rel 12.0.4 Ebuisines Suite autoconfig fails with sqlplus error while loading shared libraries libclntsh.so.10.1 cannot open shared object file No such file or directory'
Note 850264.1 - 'On R 12.1.1 Fresh Installation, POST RAPIDWIZ INSTALL CHECKS FAILS , Rapidwiz Ends With Error Jsp, Virtual Directory And Login Page Does Not Work RW-50016 Error - {0} was not created'
Note 468695.1 - 'Getting RW-50010 and RW-50004 Errors When Installing Oracle Applications Release 12.0 on Linux 64-bit'
Note 452101.1 - 'Fresh R12 Installation Fails with RW-50015'
Note 414414.1 - 'R12 Rapidwiz postinstall step Http, Login page, Virtual directory, JSP, Help page fails'
Note 789431.1 - 'R 12.0.x, Relink via adadmin or adrelink Manually Failed On cat cannot open /xxx/yyyyy/apps/tech_st/10.1.2/lib32/ldflags'
Note 418109.1 - 'Rapidwiz R12 Failed at 66% with error RW-50004 Error code received when running external process.'

Oracle DB Upgrade from 9.2.0.6 to 10.2.0.2

Patching before Upgrading Database from 9.2.0.6 to 10.2.0.2

Step-1

Apply Developer 6i Patchset version 19.

Step-2 

Apply TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH p9535311_11i_LINUX.zip

Generate appsutil.zip and ftp to RDBMS OH.

unzip and run autoconfig

Features & Enhancements

Step-3

Apply AD.I.7 Patch p7429271_11i_LINUX.zip from AD.I.2

Step-4

Apply Patch p5989593_11i_LINUX.zip

Step-5

Apply Patch p3218526_11i_GENERIC.zip

Step-6

Apply Patch p4206794_11i_GENERIC.zip

Step-7

Apply Patch p3854951_11i_LINUX.zip

Step-7a

Apply Patch p5194357_11i_GENERIC.zip

Step-8

Patch 11i.ATG_PF.H.delta.5: 11i.ATG_PF.H.RUP p6241631_11i_LINUX.zip

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

Will take 4-5Hrs

Meanwhile we can install Oracle 10g and patch it.

Unzip 10201_database_linux32.zip and install

unzip Companion Cd 10201_companion_linux32.zip and install

Apply patch for 10.2.0.3 unzip p5337014_10203_LINUX.zip and install

Step-9

Apply Patch 4653225 - 11.5.10 INTEROP PATCH FOR 10GR2

Step-10

Apply additional patches for Invalid Objects.

i.p4619025_11i_GENERIC.zip

ii.p5194357_11i_GENERIC.zip

Step-11

After Applying Patches run adadmin and select recreate grants and synonyms

and complie apps schema.

Suceesfully Completed Pre Upgradation Patching Process

Upgradation

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

Step-1 

Install Oracle 10g

Install Companion CD

Apply 10.2.0.3 patch Set

Step-2

Configuration

Create nls/data/9idata directory 

perl $ORACLE_HOME/nls/data/old/cr9idata.pl 

Check whether files exist in $ORACLE_HOME/nls/data/9idata else copy the files

cd $ORACLE_HOME/nls/data/9idata

if not then issue below commands 

$ cp $ORACLE_HOME/nls/data/*.nlb .

$ cp $ORACLE_HOME/nls/data/old/*.nlb .

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Step-3

Install 10g Listener

i.stop 9i listener

ii.run netca from Oracle 10g home and create new listener.

iii.copy cp /d01/oracle/VIS/db/tech_st/10.2.0/rdbms/admin/utlu102i.sql /tmp/

iv. in 9i run this script to generate a report.

Note: at this time 10g listener and 9i database is up and running.

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

SQL> @utlu102i.sql

Oracle Database 10.2 Upgrade Information Utility 12-13-2010 13:48:09.

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

Database:

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

--> name: VIS

--> version: 9.2.0.6.0

--> compatible: 9.2.0

--> blocksize: 8192.

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

Logfiles: [make adjustments in the current environment]

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

--> The existing log files are adequate. No changes are required.

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 8056 MB

--> APPS_UNDOTS1 tablespace is adequate for the upgrade.

.... minimum required size: 469 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

--> APPS_TS_QUEUES tablespace is adequate for the upgrade.

.... minimum required size: 567 MB

--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.

.... minimum required size: 10457 MB

--> ODM tablespace is adequate for the upgrade.

.... minimum required size: 15 MB

--> OLAP tablespace is adequate for the upgrade.

.... minimum required size: 32 MB.

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

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

WARNING: --> "streams_pool_size" is not currently defined and needs a value of

at least 50331648

WARNING: --> "large_pool_size" needs to be increased to at least 8388608

WARNING: --> "session_max_open_files" needs to be increased to at least 20.

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

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

--> "optimizer_max_permutations"

--> "row_locking"

--> "undo_suppress_errors"

--> "max_enabled_roles"

--> "enqueue_resources"

--> "sql_trace".

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

--> JServer JAVA Virtual Machine [upgrade] VALID

...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)

...is required to be installed from the 10g Companion CD.

--> Oracle XDK for Java [upgrade] VALID

--> Oracle Java Packages [upgrade] VALID

--> Oracle Text [upgrade] VALID

--> Oracle XML Database [install]

--> Real Application Clusters [upgrade] INVALID

--> Oracle Data Mining [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] UPGRADED

--> OLAP Catalog [upgrade] VALID

--> Oracle OLAP API [upgrade] UPGRADED

--> Oracle interMedia [upgrade] VALID

...The 'Oracle interMedia Image Accelerator' is

...required to be installed from the 10g Companion CD.

--> Spatial [upgrade] VALID.

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

Miscellaneous Warnings

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

WARNING: --> Passwords exist in some database links.

.... Passwords will be encrypted during the upgrade.

.... Downgrade of database links with passwords is not supported.

WARNING: --> Deprecated CONNECT role granted to some user/roles.

.... CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

.... ODM

.... OLAPSYS

.... MDSYS.

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

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

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

--> New "SYSAUX" tablespace

.... minimum required size for database upgrade: 500 MB.

PL/SQL procedure successfully completed.

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

Step-4

--------

Copy the existing initTEST.ora(9i) to $ORACLE_HOME/dbs/initTEST.ora(10g)

Modify initTEST.ora for the below parameters

user_dump_dest =

/

background_dump_dest =

core_dump_dest =

utl_file_dir =

/VIS_linux1,/usr/tmp

# Make the below changes in initTEST.ora

#optimizer_max_permutations

#row_locking

#undo_suppress_errors

#max_enabled_roles

#enqueue_resources

#sql_trace

streams_pool_size=50331648

large_pool_size = 8388608

session_max_open_files=20

optimizer_features_enable=10.2.0.3

compatible=10.2.0

Step-5

Copy the 9i environment file and modify it

Step-6

Copy the 9i network folder to 10g to get tnsnames.ora and

listener.ora

Step-7

# Shutdown all the middle tier process on application tier, if they are started

# Shutdown the 9i listener and 9i database.

# Please do take a backup of database before running manual update or dbua

# At this point in time all the services are down only 10 listener is up

SQL> startup upgrade

cd $ORACLE_HOME

. ./VIS_linux1.env

Create sysaux tablespace

CREATE TABLESPACE sysaux DATAFILE '/d01/oracle/devdbdata/sysaux01.dbf'

SIZE 1000M REUSE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

ONLINE; 

cd $ORACLE_HOME/rdbms/admin

startup upgrade

SPOOL upgrade.log

@catupgrd.sql

SPOOL OFF

SHUTDOWN IMMEDIATE

STARTUP

@utlrp.sql

Oracle Database 10.2 Upgrade Status Utility 12-13-2010 15:55:38.

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.3.0 00:30:11

JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:03:17

Oracle XDK VALID 10.2.0.3.0 00:06:51

Oracle Database Java Packages VALID 10.2.0.3.0 00:00:17

Oracle Text VALID 10.2.0.3.0 00:00:42

Oracle XML Database VALID 10.2.0.3.0 00:00:54

Oracle Real Application Clusters INVALID 10.2.0.3.0 00:00:01

Oracle Data Mining VALID 10.2.0.3.0 00:00:14

OLAP Analytic Workspace VALID 10.2.0.3.0 00:00:24

OLAP Catalog VALID 10.2.0.3.0 00:00:49

Oracle OLAP API VALID 10.2.0.3.0 00:00:24

Oracle interMedia VALID 10.2.0.3.0 00:04:07

Spatial VALID 10.2.0.3.0 00:02:42 

Step-8

--------

Post Upgrade Steps:

Some more fixes

i) Fix Korean lexers

a. Connect to sqlplus as sysdba

b. execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

ii) Run adgrants.sql

a. Connect to sqlplus as sysdba

b. Execute @$APPL_TOP/admin/adgrants.sql APPLSYS

iii) Grant create procedure privilege on CTXSYS

a. Connect to sqlplus with apps

b. Execute @$AD_TOP/patch/115/sql/adctxprv manager CTXSYS

manager is the SYSTEM password

Added few steps

i) Gather Statistics for SYS schema

a. sqlplus “/as sysdba”

b. shutdown immediate

c. startup restrict

d. @/oracle/tst10appl/admin/adstats.sql

e. shutdown

f. startup

ii) Recreate grants and synonym for apps

a. Log in to server with applmgr user

b. Execute adadmin

c. Choose -> Maintain Applications Database Entities menu

d. Choose -> Re-create grants and synonyms for APPS schema

d) On the application tier run the below command to generate

appsutil.zip file.

perl /d01/oracle/singlenode/testappl/ad/11.5.0/bin/admkappsutil.pl

Copy the appsutil.zip file generated on application tier to the

database tier inside 10g Oracle Home.

su - oracleupd

cd $ORACLE_HOME

cp /d01/oracle/singlenode/testappl/admin/out/appsutil.zip .

unzip appsutil.zip

f) Next step is to create a context file on database tier. Run the

below commands with appropriate values. This will generate context file

on database tier.

cd $ORACLE_HOME/appsutil/bin

perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

On the application tier run the below command to generate

appsutil.zip file.

perl /d01/oracle/singlenode/testappl/ad/11.5.0/bin/admkappsutil.pl

Copy the appsutil.zip file generated on application tier to the

database tier inside 10g Oracle Home.

su - oracleupd

cd $ORACLE_HOME

cp /d01/oracle/singlenode/testappl/admin/out/appsutil.zip .

unzip appsutil.zip

Next step is to create a context file on database tier. Run the

below commands with appropriate values. This will generate context file

on database tier.

cd $ORACLE_HOME/appsutil/bin

perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

Once the context file is created on db tier, run autoconfig.

cd $ORACLE_HOME/appsutil/bin

./adconfig.sh

contextfile=/d01/oracle/singlenode/testdb/10.2.0/appsutil/TEST_linux1.xml appspass=apps

AutoConfig completed successfully.

The log file for this session is located at:

/d01/oracle/singlenode/testdb/10.2.0/appsutil/log/TEST_linux1/12130604/adconfig.log

Simple Clone Process in 11i/R12

In normal cloning process, we do archive the Source node then copy the archived files to the Target node then extract the archived files back.

If you want to speed up this process, you can follow this one:

Example: Ine One step, we have to copy the ORACLE_HOME form Source Node (Prod) to Target Node (Test).

On the Target node create the ORACLE_HOME directory if it does not exist:

$ mkdir -p /u01/app/oracle/product/10.2.0./

On the Source node:

$ cd $ORACLE_HOME

$ tar cf - * | ssh TEST '(cd /u01/app/oracle/product/10.2.0./; tar xf - )'

on the Target node, you can check files are copying and extracting by giving:

$ cd /u01/app/oracle/product/10.2.0./db

$ ls -ltr

total 240

drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 jre

drwxr-x--- 7 oracle dba 4096 Oct 25 20:38 javavm

drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 has

drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 diagnostics

drwxr-x--- 3 oracle dba 4096 Oct 25 20:38 demo

drwxr-x--- 6 oracle dba 4096 Oct 25 20:38 crs

drwxr-x--- 4 oracle dba 4096 Oct 25 20:38 clone

drwxr-x--- 7 oracle dba 4096 Oct 25 20:38 assistants

drwxr-x--- 4 oracle dba 4096 Oct 25 20:49 jdbc

REP-3000 Oracle Toolkit Error

Solution:

1.  Ensure the correct DISPLAY environment variable is set on the E-Business server(s)

In the main environment file, as pointed to by $APPLFENV, verify / set the DISPLAY environment variable to an active and available X display server.

DISPLAY=<hostname>:<display_number>.0 ; export DISPLAY

i.e. DISPLAY=acme.com:0.0 ; export DISPLAY

Note 2: Some notes, such as 200474.1, advocates placing the DISPLAY environment variable in startup scripts: adrepctl.sh, adcmctl.sh, gsmstart.sh. Caution should be observed; these scripts override the DISPLAY value in the main environment file and may add a level of confusion if different values are present on each server or script.

On Autoconfig ready instances, make any environment variable changes via the Autoconfig Context Editor or Oracle Applications Manager (OAM) and re-run Autoconfig on all nodes in order to ensure that all changes are properly synchronized across all tiers and to prevent the lost of the current environment changes next time Autoconfig runs.

2. Invoke or re-invoke the xhost command as root.

On the server where the X display server resides, as root, execute xhost +<hostname1>+<hostname2>… Where <hostname> are the E-Business server names requiring X display server access, such as the Concurrent Processing Server, Oracle Reports Server, etc.

Typically, this action needs to be done any time the host with the X display server is bounced.

3. As the "root" user, bounce (restart) the X display server and/or any required window manager (i.e. mwm, olwm, fvwm, twm, etc.) associated with the X display server or window system.

Make sure to perform the xhost + command again.

Note 3: Please reference the X display server’s guide or contact the vendor for assistance on stopping or starting the X sever or window manager in used.

4. Validate that the Concurrent Processing Server sees the correct DISPLAY value.

Run the Sysadmin report "Prints environment variable values" with the "DISPLAY" parameter and check that the correct DISPLAY value is present. If this value is incorrect, then the concurrent processing server is not aware of the proper DISPLAY setting. In short, this environment variable needs to be defined before starting the concurrent processing server.

The Sysadmin report "Generate concurrent processing environment information" also lists all environment variables visible to the concurrent processing server.

5. Verify that the DISPLAY variable is usable by running a concurrent report in Postscript format.

Run the Sysadmin report "CP Postscript Report Regression Test" with the parameter BASIC. If the reports completes successfully, the concurrent processing server is aware of the DISPLAY value and the current variable value is usable.

Note 4: If Pasta is being utilized and the IX_RENDERING variable is set without a corresponding displayfontpath entry, a REP-3000 can occur--see Note 361639.1 "Cannot Open Display Font File: ... REP-3000: Internal Error Starting Oracle Toolkit"

6. If bitmap (Postscript, PDF, etc) reports fail from within Oracle Applications, does a report in Postscript format run successfully from the OS command line?

a) Login as the applmgr on the host where the concurrent processing server resides and source the main environment file.

b) Set the DISPLAY environment variable, if not already set by the main environment file.

echo $DISPLAY

DISPLAY=<hostname>:0.0 ; export DISPLAY

c) Run the "Active Users" report in Postscript format with the ar60runb executable and the following options. Provide the apps password an populate the parameters with the full path of $FND_TOP and $APPLTMP.

--- 12.0 Example ---

appsrwrun.sh userid=apps/apps report=/<$FND_TOP>/reports/US/FNDSCURS.rdf batch=yes destype=file mode=bitmap desname=/<$APPLTMP>/ActUsr.ps desformat=/<10.1.2.$ORACLE_HOME>/reports/printers/psl132.prt errfile=/<$APPLTMP>/ActUsr.log

Note: The full path to appsrwrun.sh is contained within the $APPLORB environment variable

--- 11.5 Example ---

ar60runb userid=apps/apps report=/<$FND_TOP>/reports/US/FNDSCURS.rdf batch=yes destype=file mode=bitmap desname=/<$APPLTMP>/ActUsr.ps desformat=/<8.0.6.$ORACLE_HOME>/reports60/admin/printer/psl132.prt errfile=/<$APPLTMP>/ActUsr.log

7. If bitmap reports still fail with a REP-3000 from Oracle Applications or from the OS command line, the most likely cause of the error is that the X display server and/or window manager is not accessible, usable, or started.

a) Review the following documents for further insight and additional recommended checks:

Note 200474.1 "Comprehensive REP-3000 Troubleshooting and Overview Guide", particularly the startup script examples at section "VI-6. GENERAL ISSUES:"

Note 207532.1 "Troubleshooting Tips for REP-3000 Error when Running PDF/POSTSCRIPT/HTML Reports"

Note 153960.1 "FAQ: X Server testing and troubleshooting"

Note 181244.1 "Configuring VNC Or XVFB As The X Server For Applications 11i("Configuring an X Display Server for Applications on Unix Platforms")"

b) Update any on-going Oracle service request with the results of the previous steps.

c) Use the command xdpyinfo -display <hostname>:<display_number>.0 to inspect and verify the display settings. Please consult the vendor’s documentation for detailed instructions on modifying any settings.

d) Contact the vendor of the third party X display server or VNC product for assistance on configuring their product for use.

Oracle Apps Yellow Bar Issue

It can occur while copying the content from Apps to Any other application like Apps forms to notepad or excel.

Troubleshooting:

1.  Delete cache : Delete all the files from Temporary Internet Files and temp table.

2.  Uninstall the the Jinitiator from the system (Control Panel)

3.  Open the Oracle application

Login to apps.  Choose any responsibility.  Appsbase.htm window will be opened(which initiates Applet in it).

It instals the latest version available on server and now you can see "Yellow Bar" is disabled and you can perform Copy and Paste

If you have problem while installing jinitiator.exe with the following errors.

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

UnZipSFX 5.40 of 28 November 1998, by Info-ZIP (Zip-Bugs@lists.wku.edu).

extracting: adjbuild/appltop.cerextracting: adjbuild/jinit11816.

exeextracting: adjbuild/oajsetup.

exeextracting: autosetup.txt

extracting: oajsetup.bat

extracting: setup.bat

oajsetup: Jinitiator version number not valid, should be 1.1.x.x

Oracle Jinitiator Setup Utility

Copyright (c) Oracle Corp., 2000

Usage:

oajsetup [] []

The system cannot find the file specified.

The batch file cannot be found.

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

It may happen because some times the version at server may be corrupted or so.

Then downlod the jinitiator.exe and version should be same as the one you have on server.

How to check the version of jinitiator on your sever?

When you login to apps and choose responsibility, you can see a appsbase.htm which starts applet,

there you right click and view source there you can see the version of jinitiator.

Download the jinitiator from the above below path

http://www.oracle.com/technology/software/products/developer/htdocs/jinit.htm

Clear Cache, delete all the temporaty files from PC

Install jinitiator in your PC and check the Oracle apps again.

It should work now.

If you still have problem, there is another tip, copy the " identitydb.obj " from any of your network users who are using the same Oracle application and paste in your "Oracle" folder where jinitiator is installed the typical path is like c:\program files\oracle\identitydb.obj

Why excessive redo during Hot Backup

When you put the db in backup mode, Oracle will take a checkpoint and update the datafile headers with checkpoint SCN and thereafter it will freeze the datafile header, not datafile, so data will be contiously updated in datafiles.

Considering a case, where an Oracle database is installed on Linux platform. The standard Oracle block size is 8K and lets say that OS level data block size is 512K. When we put the db in “Begin Backup” mode, checkpoint will occur and datafile headers will be freezed. When you start copying datafiles using OS command, it is going to copy as per OS block size (512k). Lets say when you start copying, it gave 8 os blocks to copy – that means you are copying 8 x 512k = 4096k / 1024 = 4K to backup location. That means you are copying half of Oracle block to backup location. Now this process of copy can be preempted by Server CPU depending on load then the process get preempted by CPU and it has allocated CPU time to some other important process. Meanwhile DBWR process changes that block that you have copied halfway (since datafile is not freezed and only header is freezed, continuous updates can happen to datafile).

After a while CPU returns back and gives you next 8 blocks to copy (rest of the 4k Oracle block). Here the problem will raise. We copied half of the oracle block before update and half block after update. These type of block is called “Fractured Block”.

Once db is in begin backup mode, if a transaction happens and changes any block FOR THE FIST TIME, oracle copies the complete block to redo log file. This happens only during first time. If subsequent transaction updates the same block again, oracle will not copy the complete block to redo, instead it will generate minimum information to redo the changes. Now because oracle has to copy the complete block when it changes for the first time in begin backup mode, we say that excess redo gets generated when we put tablespace in begin backup mode.

Then why Oracle has to copy the complete block to redo log files. As you have seen above, during copy of datafile, there can be many fractured blocks, and during restore and recovery it is going to put those blocks back and try to recover. Now assume that block is fractured and oracle has minimum information that it generates in the redo. Under such condition it is not possible for Oracle to recover such blocks, so instead Oracle just copies the entire block back from redo log files to datafiles during recovery process. This will make the datafile consistent, so recovery process is very important which takes care of all fractured blocks and makes it possible to recover a database.

Clearing Cache in R12

There is a change in the default behaviour of handling JSP cache in R12 as opposed to 11i.

JSPs were handled by Apache Jserv engine in 11i and is being handled by OC4J (10.1.3 AS) in R12.

In 11i, when we clear cache ($COMMON_TOP/_pages) and try to  access JSP pages,
it gets automatically recompiled. However, this is not the case in R12 by default.

In R12 instance, if you clear _pages and restart apache,you will only get a blank screen
since AppsLocalLogin.jsp wouldn’t get compiled by default. You will not even see a new _pages directory.

As per Oracle, whenever JSP cache is cleared, we have to run “ojspCompile.pl --compile --flush -p 2”
to recompile ALL jsp pages in the Application.  This will take roughly around 15 to 20 mts.
However, in the real world (atleast in the pre-production stage) this is not affordable.

The solution to this is to change the XML file parameter "s_jsp_main_mode" to a value of “recompile”
from the default value of “justrun” and run autoconfig. JSP pages will be recompiled automatically
after this change.

Note:  742107.1 - How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI)
for E-Business Suite

Count of users connected to Oracle Apps

1:  Use this SQL statement to count number of concurrent_users connected to Oracle apps:

select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d

where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1)

2:  Use this SQL statement to count number of users connected to Oracle Apps in the past 1 hour.

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

3:  Use this SQL statement to get number of users connected to Oracle Apps in the past 1 day.

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

4:  Use this SQL statement to get number of users connected to Oracle Apps in the last 15 minutes.

select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

How to clean FND_NODES table

Step 1:   Always apply the latest cloning patches to avoid all the bugs and fixes

Step 2:  SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

EXIT;

It will delete all old data from the table.

Step 3:  Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers to repopulate the required system tables.

How to Create Custom Top

You can create custom top in oracle application using following Process:

Step 1: Replicate APPL_TOP Dircetory in UNIX

Cd $APPL_TOP

Step 2: Create Custom Top Under APPL_TOP Dir

Mkdir XXCUST_TOP (Exactly Product Directories)

Make sure all the files and Directories are same as Other Product Dir.

Step 3: Add Custom Top entry in APPLSYS.env file

Step 4: Login Oracle apps using Sysadmin or AOL

Navigate: Applications->Register

Add your Custom top Entry Here

Application: XXApplication name

Short name: XXCUST

BasePath: XXCUST_TOP

Description: XXApplication.

Step 5: Restart the Internal Concurrent Manager (ICM) So that New Changes will recognize.

Step 6: Finally Check Custom Entry in

Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME

From fnd_application

Where application_Short_name like 'XXCUST_TOP%'

Maintenance Mode

While Applying a Patch, it is not Mandatory to Bring down All the Application Services 

except if it Mentioned in the Patch Read Me.

The Scope of the Maintenance Mode is to Avoid the End users to log in to application at the time of... Patching.

As per the MOS Note 233044.1

Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications 

and system downtime for maintenance. Enabling the maintenance mode feature shuts down the Workflow 

Business Events System and sets up function security so that no Oracle Applications functions are 

available to users. Used only during AutoPatch sessions, maintenance mode ensures optimal performance

and reduces downtime when applying a patch. For more information, refer to Preparing your System for 

Patching in Oracle Applications Maintenance Utilities.

RMAN Restore and Recovery Scenarios

Full Database Restore

 $ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

database opened 

Tablespace Restore (online)

 $ORACLE_HOME/bin/rman target / nocatalog

RMAN> sql 'alter tablespace users offline';

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> sql 'alter tablespace users online';

* A SYSTEM tablespace cannot be recovered with the database online.

 Tablespace Restore (offline) 

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> alter database open;

database opened 

Restoring a Specific Datafile 

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore datafile '/oradata/DB1/dbf/users01.dbf';

RMAN> recover datafile '/oradata/DB1/dbf/users01.dbf';

RMAN> alter database open;

database opened 

Control File Restoration 

Prerequisite: In your rman backup directory determine the latest control file backup.

Default Format: c-nnnnnnnnnn-nnnnnnnn-nn 

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup nomount;

RMAN> set dbid = 1184749195

RMAN> restore controlfile from '/oradata/DB1/rman/c-1184749195-20060626-02'

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

database opened

Database Point-In-Time-Recovery (PITR) 

Also known as time-based incomplete recovery.

$ORACLE_HOME/bin/rman target / nocatalog

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore database until time "to_date('09/03/07 13:00:00', 'MM/DD/YY HH24:MI:SS')";

RMAN> recover database until time "to_date('09/03/07 13:00:00', 'MM/DD/YY HH24:MI:SS')";

RMAN> alter database open resetlogs;

database opened 

* Make sure you perform a full backup after this operation!

 Restore to Another System 

Prerequisites

Ideally ensure destination system configured exactly like source.

Same OS version and patch level.

Same drives (C:, D:, S: etc.).

CPU and RAM same or better.

The same version of Oracle is installed on the target system as the source.

Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.

Ensure the listener is running. 

Copy RMAN backupset files to the destination system rman directory.

If Windows:

Create the password file.

orapwd file=orapwDB1 password=mypassword

Creates the file %ORACLE_HOME%\dbs\orapwDB1

Copy %ORACLE_HOME%\dbs\orapwDB1 to %ORACLE_HOME%\database.

In some instances of a restore like this it may look for the file here.

Create or start the Windows database instance service.

oradim -new -sid DB1 -intpwd mypassword -startmode MANUAL

Creates the file: %ORACLE_HOME%\database\PWDDB1.ORA

Ensure the drive\path to the admin (adump,bdump,cdump,udump), data and redo directories on the source and destination systems are identical.

Example:

Admin Dump Directories

mkdir C:\oracle\product\10.2.0\admin

mkdir C:\oracle\product\10.2.0\admin\DB1

mkdir C:\oracle\product\10.2.0\admin\DB1\adump

mkdir C:\oracle\product\10.2.0\admin\DB1\bdump

mkdir C:\oracle\product\10.2.0\admin\DB1\cdump

mkdir C:\oracle\product\10.2.0\admin\DB1\udump

Data Directories

mkdir D:\oradata

mkdir D:\oradata\DB1

Redo and Archive Log Directories

mkdir D:\oradata\DB1\recovery1

mkdir D:\oradata\DB1\recovery2 

Procedure 

Restore SPFILE and Control File

%ORACLE_HOME%\bin\rman target / nocatalog

RMAN> set dbid 161080442

RMAN> startup nomount;

Creates the file: %ORACLE_HOME%\database\hc_db1.dat

RMAN> restore spfile from 'R:\rman\C-161080442-20080313-00';

Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA

RMAN> startup force nomount

RMAN> restore controlfile from 'R:\rman\C-161080442-20080313-00';

RMAN> shutdown immediate

RMAN> exit

 Restore and Recover the Data 

%ORACLE_HOME%\bin\rman target / nocatalog

RMAN> startup mount;

RMAN> restore database;

For a large database this step may take some time.

RMAN> recover database;

If you do not have\need the very last log(s) you can disregard any error messages.

ORA-00310: archived log contains sequence 100; sequence 101 required...

RMAN> alter database open resetlogs;

database opened

Find component Version in Apps 11i/R12

How to find out Oracle Apps password??

The below methog helps us to find out when we lost the Apps password.

Please note you need have system password to perform the below actions.

The below method is not suggested for Production or Critical systems.

Login to Middle Tier as ap user.
-------------------------------
$cd $FND_TOP/patch/115/sql

$cp AFSCJAVS.pls AFSCJAVS.pls.orig

Add following lines to the AFSCJAVS.pls file

CREATE OR REPLACE PACKAGE apps.fnd_web_sec AS 

/*--------------------------------------------------------------*/

function decrypt(key in varchar2, value in varchar2)

return varchar2;  

/*--------------------------------------------------------------*/

$sqlplus system/ @AFSCJAVS.pls

Package created.

Commit complete.

as system excute the following to find out if anything marked as invalid.

SQL> show user

USER is "SYSTEM"

SQL>col OBJECT_NAME for a30;

set line 200;

select object_name,object_type,status,owner from dba_objects

where object_name='FND_WEB_SEC';

SQL>col OBJECT_NAME for a30;

set line 200;

select object_name,object_type,status,owner from dba_objects

where object_name='FND_WEB_SEC'; 

OBJECT_NAME OBJECT_TYPE STATUS OWNER

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

FND_WEB_SEC PACKAGE VALID APPS

FND_WEB_SEC PACKAGE BODY INVALID APPS 

SQL> alter PACKAGE apps.FND_WEB_SEC compile body; 

Package body altered.  

If there are any invalids compile them. 

Obtain Guest user ID and password. 

as system excute the following. 

SQL> show user

USER is "SYSTEM" 

SQL> select profile_option_value from apps.fnd_profile_option_values

where profile_option_id = (select profile_option_id from apps.fnd_profile_options

where profile_option_name='GUEST_USER_PWD');

PROFILE_OPTION_VALUE

------------------------------------------------------------------------------------
GUEST/ORACLE 

SQL> show user

USER is "SYSTEM" 

SQL>create synonym FND_WEB_SEC_TEST for apps.FND_WEB_SEC; 

Synonym created. 

SQL> show user

USER is "SYSTEM"

SQL> create synonym FND_WEB_SEC_TEST for apps.FND_WEB_SEC; 

Synonym created. 

-- Now Obtain Apps password using the below sql :) 

SQL> SELECT(

SELECT

fnd_web_sec_test.decrypt('GUEST/ORACLE',encrypted_foundation_password)

FROM dual

)AS APPS_PASSWORD

FROM

apps.fnd_user

WHERE

user_name like 'GUEST'; 2 3 4 5 6 7 8 9 

APPS_PASSWORD
-------------------------------------------------------------------
APPS 

-- WOW we got the Apps password now 

SQL> drop synonym FND_WEB_SEC_TEST; 

Synonym dropped.  

-bash-3.2$ sqlplus apps/ @AFSCJAVS.pls  

Package created.  

Commit complete.  

Please find the below explanation for the above steps:
-------------------------------------------------------------

A)

We know in Oracle Applications passwords are stored in FND_USER and

FND_ORACLE_USERID tables,The FND_USER table stores application user

account passwords and the FND_ORACLE_USERID table stores internal 

Oracle Applications database account passwords,Both tables use the

same encryption algorithm to protect the passwords. 

The FND_USER table contains all the application accounts.

There are two password columns in this table:

ENCRYPTED_FOUNDATION_PASSWORD and ENCRYPTED_USER_PASSWORD. 

====================================================
Column Value Encryption Key
===================================================

ENCRYPTED_FOUNDATION_PASSWORD APPS username/password

ENCRYPTED_USER_PASSWORD user APPS password

The 2 columns as... 

 a)If we know the username/password we can get the Apps

 Password (ENCRYPTED_FOUNDATION_PASSWORD)

b)If we know the Apps password we can get any users

 password (ENCRYPTED_USER_PASSWORD) 

B) 

To decrypt and verify user passwords Oracle Apps uses FND_WEB_SEC

package in Apps.The DECRYPT function in the FND_WEB_SEC package is

a local function and cannot be called from outside the package. 

So to make the function available outside the package,

we have altered the AFSCJAVS.pls above

How to end date large number of fnd_users in Oracle Apps

Below is the syntax, which can used to Huge number of Users in one GO !!

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

declare cursor cur1 is

select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);

begin

for all_user in cur1 loop

apps.fnd_user_pkg.EnableUser(all_user.user_name);

commit;

end loop;

End

Oracle Apps Multi Node To Single Node Cloning

With Oracle Applications 11.5.10, the ability to clone from a multi node to a single node system using rapid clone has now been certified.  This is accomplished by the Shared APPL_TOP and Merging APPL_TOP features.

For a more elaborate explanation you might want to refer to the metalink note Sharing the Application Tier File System in Oracle E-Business Suite 11i (Note 233428.1).

Source System here refers to the multi node system to be cloned

Target System refers to the newly to be created single node system

Applications Version 11.5.10

Operating System Linux ES 4

The following Steps are required to be performed on the source system

1.  Apply application tier patches using adpatch

Apply patch 4038964Apply patch 4175764

Both the above patches are include in the consolidated update 2 or CU2 so in case you are on 11.5.10.2 or later you can ignore this step.

2.  Maintain snapshot information

Log in as the applications user on each application tier node and run ‘Maintain Snapshot Information’ by using adadmin.

3.  Merge existing APPL_TOPs

Log in to the primary node of your application tier as the application user user and run:

$ cd [COMMON_TOP]/admin/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl appsTier merge

This will prompt you with option to merge secondary nodes

Now log in as the applications user to each of the secondary application tier nodes being merged and run:

$ cd [COMMON_TOP]/admin/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl appltop merge

4.  Prepare the source system database tier.

Log on to the database tier of the source system as the oracle user and run the following commands:

$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl dbTier

With this all the pre clone tasks on the source system have been completed.

The next set of tasks are required to be carried out on the target system that is the system on which you wish to place the merged single node instance.

1.  Create OS user accounts

Create a OS user account for your applications

$ useradd -g dba -d [home_directory] -p password username

Similarly create a OS user account for your database

2.  Modify the orainventory to reflect the new location

$ vi /etc/oraInst.loc

3.  Copy the following application tier directories

Copy the following application tier directories from the primary node of your source system to the target application tier node, retaining the original directory structure:

– [APPL_TOP]– [OA_HTML]– [OA_JAVA]– [COMMON_TOP/util]– [COMMON_TOP/clone>– [806 ORACLE_HOME]– [iAS ORACLE_HOME]

4.  Copy the required files for merging

Log in as the applications user to each of the secondary source nodes and recursively copy:

directory [COMMON_TOP]/clone/appl- to -directory [COMMMON_TOP]/clone/appl on the target system node

Before proceeding with the next steps you must shutdown your oracle applications services and the database on the source system

5. Copy the database tier file system

Log on to the source system as the database user

Copy the database (DBF) files from the source to the target systemCopy the source database ORACLE_HOME to the target system

After this you can now startup the database and applications services on your source system and release it for use.

6.  Configure the target system database server

Log on to the target system as the database user and type the following commands to configure and start the database:

$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbTier

This will prompt for new ORACLE_SID,ORACLE_HOME,Port Pool,JAVA_TOP and DATA_TOP give the appropriate values matching your target system

Once successful this should start your database and listener

7.  Configure the application tier server nodes

The database and its listener should remain up before executing the next set of commands.Log in to the merged APPL_TOP node as the applications user and execute the following commands:

$ cd [COMMON_TOP]/clone/bin

$ perl adcfgclone.pl appsTier

This will prompt you new port pool for applictaion tier services as well as new APPLTOP,COMMON_TOP,ORACLE_HOME and IAS_TOP

Successful completion of this task will bring up your application tier services on the target or the cloned node.

8. Post Clone Tasks

Log in to the target system application tier node as the APPLMGR user.Run the following tasks in adadmin for all products:o generate JAR fileso generate message fileso relink executableso copy files to destination

9. Clean up of the target system

Remove the temporary directory [COMMON_TOP]/clone/appl to reduce disk space usage.

Note: The version of Oracle Applications used is 11.5.10 and the operating system is Linux ES 4

Oracle Apps DBA Interview Questions - VI

Q: Why we need to put maintenance mode when we are applying a patch ?

A: Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for

maintenance. Enabling the maintenance mode feature shuts down the Workflow Business Events System and sets up function security so

that no Oracle Applications functions are available to users. Used only during AutoPatch sessions, maintenance mode ensures

optimal performance and reduces downtime when applying a patch. For more information, refer to Preparing your System for Patching

in Oracle Applications Maintenance Utilities.

Q: After applying patch why we need to take snapshot and what is the use of it ??

A: Snapshot is a view of the system at specific time. In apps, Patch Wizard uses Global snapshot to determine which patches have

been applied to the system and Autopatch uses APPL_TOP snapshot to determine what patches have been applied to that APPL_TOP.

Snapshot actually records the list of files,file versions and bug fixes. Both snapshot are views, they are created once during

installation and then updated during patching. APPL_TOP snapshot information is stored in the AD_SNAPSHOTS, AD_SNAPSHOT_FILES, and

AD_SNAPSHOT_BUGFIXES tables

Q: Whether we can apply 2 patches at a time without merging them, why ??

A: We can't apply 2 patches at a time because when patch started it will create 2 tables in db (AD_DEFERRED_JOBS AND

FND_INSTALL_PROCESSES). If you apply patches at a time both will try to create those 2 tables in db, so both will fail.

Q. After applying patch whether we can revert it back ?

A: No, as it is going to update some tables in db.

Q. Whether we need to run adpreclone everytime when we clone, why ?

A: We have to run adpreclone if there are changes in any customizations or any other tablespaces like if applied any AD patches,

Big Patches, Minipacks, ATG patches, Tech Stack, and AD Patches. If there are no changes, then i don't think there is any need to

preclone it again.

Example: When you run adpreclone.pl, it essentially does an "alter database backup controlfile to trace" and stores the information from

that in a file within ${ORACLE_HOME}/appsutil. This represents all of the datafiles in the database at that particular time. The information

gathered at this time represents the state of the source at this time. If you does not run adpreclone.pl again when u do clone next time,

Your backup (your target) represents an earlier time (prior to the addition of that datafile), so the "create controlfile" step

that adcfgclone is doing is going to fail.

Q: Which files it will call when we run adcmctl.sh start apps/apps ?

A: It will call FNDLIBR executable, which will be located in $FND_TOP/bin.

Q: What are the main tables related to concurrent manager ?

A: FND_NODES

FND_CONCURRENT_PROCESSES (fcproc)

FND_CONCURRENT_REQUESTS (fcr)

FND_CONCURRENT_QUEUES (fcq)

FND_CONCURRENT_PROGRAMS (fcprog)

FND_EXECUTABLES

FND_CP_SERVICES

FND_CONCURRENT_QUEUE_SIZE

FND_CONCURRENT_QUEUE_CONTENT

FND_CONCURRENT_PROGRAM_SERIAL

FND_CONCURRENT_TIME_PERIODS

FND_CONCURRENT_PROCESSORS

Q: What is FNDFS ??

A: Report Review Agent(RRA) also referred by executable FNDFS is default text viewer in Oracle Applications 11i for viewing

output files and log files. As most of Applications DBA's are not clear about Report Server and RRA.

Q: What is FNDSM ?? It has seperate name, what it is ?

A: FNDSM is executable & core component in GSM. GSM stands for Generic Service Management Framework. Oracle E-Business Suite

consist of various compoennts like Forms, Reports, Web Server, Workflow, Concurrent Manager .. Earlier each service used to start

at their own but managing these services (given that) they can be on various machines distributed across network. So Generic

Service Management is extension of Concurrent Processing which manages all your services , provide fault tolerance (If some

service is down ICM through FNDSM & other processes will try to start it even on remote server) With GSM all services are

centrally managed via this Framework.

Q: How to check whether ICM is up and running from backend ?

A: ps -ef | grep LIB

ps –ef | grep CPMGR

ps –ef | grep FNDCPMBR

$FND_TOP/sql/afcmstat.sql --- Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql --- Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger

monitor is running.

afcmcreq.sql --- Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql --- Displays the requests that are pending, held, and scheduled.

afrqstat.sql --- Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql --- Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id

can then be used with the ORADEBUG utility.

afimlock.sql --- Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to

get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with

another oracle process.

Q: Which component is responsible for PCP failover ?

A: When PCP is enabled, can you check that FNDSM is running on all your CCM nodes? That process is mainly reponsible to getting

the failovers to happen and also start up CCMs

Q: Where custom top information will be there ?

A: $APPL_TOP/APPLSYS.env file.

Q: What will happen when we run adpreclone ?

A: It will collect all info of the environment and store in seperate template files.

Q: Which two parameters are required when we do clone using RMAN ?

A: DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT

Q: How to clean FND_NODES table?

A: EXEC FND_CONC_CLONE.SETUP_CLEAN.

Oracle Apps DBA Interview Questions – V

1. What is the pre-req for applying a rdbms patch?

Ans : OraInventory should be set in file oraInst.loc @/var/opt/oracle or /etc

2. What is OraInventroy?

Ans: The oraInventory is the location for the OUI (Oracle Universal Installer)'s bookkeeping. The orainventory stores information about: All Oracle software products installed in all ORACLE_HOMES on a machine Other non-Oracle products, such as the Java Runtime Environment (JRE).  In a 11i Application system the RDBMS and iAS ORACLE_HOMEs are registered in the oraInventory. The 806 ORACLE_HOME, which is not managed through OUI, is not.

3. What are different types of inventories?

Ans:  The Global orainventory (or Central inventory) The Local inventory (or Home inventory)

4. What is Global orainventory?

Ans : The Global oraInventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc.The Global OraInventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs.The Global OraInventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone.

Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global OraInventory synchronized.

5. What is local Orainventory?

Ans : There is one Local OraInventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME.The Local OraInventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

6. What is rapid clone?

Ans : Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install

7. How do I determine if my system is rapid clone enabled?

Ans : First, verify that your system is AutoConfig enabled. Then, verify that you have applied the latest Rapid Clone patch.

8. Explain the cloning process?

Ans : 

1. Run adpreclone as applmgr and oracle user on source Perl adpreclone.pl dbTier as oracle user Perl adpreclone.pl appsTier as applmgr user

2. Take the cold/hotbackup of source database

3. Copy the five directories appl,comn,ora , db,data to target

4. Rename the directories, and change the permisssion

5. Set the inventory in oraInst.loc

6. Run perl adcfgclone.pl dbTier as oracle user,if the backup type is cold

7. If the backup type is hotbackup then Perl adcfgclone.pl dbTechStack. Create the control file on target from the control script trace file from source Recover the database Alter database open resetlogs

8. Run autoconfig with the ports changed as per requirement in xml.

9. Run perl adcfgclone.pl appsTier as applmgr

10. Run autoconfig with the ports changed as per requirement in xml.

9. What is the location of adpreclone.pl for oracle user?

Ans : RDBMS_ORACLE_HOME/appsutil/scripts/

10. What is the location of adpreclone.pl for applmgr user?

Ans : $COMMON_TOP/admin/scripts/

11. What is the location of adcfgclone.pl for oracle user?

Ans : $RDBMS_ORACLE_HOME/appsutil/clone/bin

12. What is the location of adcfgclone.pl for applmgr user?

Ans : $COMMON_TOP/clone/bin

13. What is statspack?

Ans : Statspack is a database utility to gather database and session level performance information.

14. How to install statspack?

Ans : Run the script spcreate.sql @RDBMS_ORACLE_HOME/rdbms/admin

Note more details on statspack refer metalink noteid: 149113.1

15. How to enable trace at database level?

Ans : set init.ora parameter sql_trace

16. How to enable trace for a session?

Ans: Alter system set sql_trace=true;

Execute the sql query

Alter system set sql_trace=false;

This will create a trace file at

$RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.

17. How to enable trace for other session?

Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)

Eg: To enable trace for sql session with sid 8SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);

PL/SQL procedure successfully completed.

To disable trace

SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);

18.What is the location of inint.ora ?

Ans : $RDBMS_ORACLE_HOME/dbs

19. What is that trace files contains and the utiliy used to read them?

Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

20. What is the syntax for tkprof?

Ans: tkprof explain=apps/ sys=no

21.How do we find adpreclone is run in source or not ?

Ans : If clone directory exists under RDBMS_ORACLE_HOME/appsutil for oracle user and $COMMON_TOP for applmgr user.

23. How to find trace file for a given concurrent request id?

Ans : Go to $RDBMS_ORACLE_HOME/admin//udump

grep “ “ *

24. What is a database link? How to create it?

Ans : If we want to access objects of another database from this database then we need a database link from this database to the other.

1.Login as oracle user

2.sqlplus “/as sysdba”

3. create database link connect to identified by using '';

Ex

SQL> create database link TEST1_TO_TEST2 connect to apps identified by apps using 'TEST2';

Database link created.

SQL> select name from v$database@ TEST1_TO_TEST2;

NAME

---------

TEST2

SQL>select db_link from dba_db_links;

4. Add destination database tns entry in tnsnames.ora

25. How many clonings u have done?

Ans : If u r very much confident on cloning processes then say 5 to 8 otherwise just 2 or 3.

26. What u know abt RMAN?

Ans : If u r good at RMAN then say yes, otherwise say we are not using RMAN for backup/recovery , why because we are using netapp snap technology for backups.

27. What is netapp?

Ans : Netapp is a storage technology.

28. What is formserver url?

Ans :http://hostname.domain:/dev60cgi/f60cgi

29. What is jinitiator?

Ans : Oracle jinitiator is the one which provide the required jvm to run forms interface/applet. When we access forms applet first time , oracle jinitiator will be installed automatically.

30. What is discoverer server?

Ans : Discoverer server is reporting tools which allows novoice user to use oracle application reports. Discoverer will come along with oracle applications when installed.

31. What is discoverer viewer url?

Ans: /discoverer4i/viewer152. What is discoverer plus url?

Ans : Ans : Aoljtest is a web based utility to test the availability of the different components of oracle applications like jserv,modplsql,jsp,forms etc

URL: /OA_HTML/jsp/fnd/aoljtest.jsp

32. What is adsplicer?

Ans : Adsplicer is a uitility used to register off cycle products.

33. What is licence manager?

Ans : Licence manager(adlicmgr) utility is used to licence/unlicence , enable new languages,enbale country specific functionality.

34. What is tnsping?

Ans : tnsping is command used to check the connectivity to the database server node from other nodes.

Ex: tnsping

Note: Tns entry should be there in tnsnames.ora for the database we are trying to work this command.

35. How to compile a form using f60gen?Ans :

f60gen module=/TEST/testappl/au/11.5.0/forms/F/ARXTWMAI.fmb userid=APPS/APPS output_file=/TEST/testappl/ar/11.5.0/forms/F/ARXTWMAI.fmx module_type=form batch=yes compile_all=special

36. What is APPLPTMP environment variable?

Ans : This is the temporary file location for the pl/sql temp files. If this variable was not set then the concurrent programs may errored out. 161. What is mean by enabling maintanance mode?

Ans : Maintanance mode is the adadmin option introduced from AD.I. When maintanance mode is enabled user may able to login to application but they only get profile option in the frontend navigation menu.

37. Is that necessary to enable maintanance mode while applying a patch?

Ans : We can even apply a patch without enabling maintanance mode with the following option

Adpatch options=hotpatch

38. How to find out oracle application framework version?

Ans : 1. Through aoljtest2. cd $COMMON_TOP/html/3. adident Header OA.jsp

39. How to find out what are the rdbms patches applied to an oracle home?

Ans: 

1. opatch lsinventory

2. $RDBMS_ORACLE_HOME/.patch_storage directory contains the directories with the rdbms patch number, which are applied to this oracle home.

165. Is that necessary to shutdown database while applying a database patch?

Ans : Yes.

40. What is the command line utility to submit a concurrent request?

Ans : CONSUB

41. What is the significance of utl_file_dir parameter in init.ora file?

Ans : The value of this parameter is the group of directories to which u r database can write, means u r database packages have permission to write to flat files in these directories.

42. How you will find out discoverer version?

Ans : cd $806_ORACLE_HOME/discwb4/lib strings libd* grep 'Version:'

43. While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you will do, and how you will apply the patch?

Ans: We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory. If still it won’t work we will apply patch with the following command:

Opatch apply no_inventory

44. Have you applied rdbms patches and for what?

Ans : We got ORA-7445 error in alert log, for which oracle recommended to apply a rdbms patch.

45. What are the patch errors , you have encountered?

Ans :

1)Patch fails with the error, unable to generate perticular form, do u want to continue. We continue patching by saying “yes”, then we manually regenarate the form using f60gen utility.

2) Unable to generate jar files under JAVA_TOP AutoPatch error: Failed to generate the product JAR files Solution:Run adjkey -initialize -----------to creat identitydb.obj file which will beused by adjava to sign jar files.

46. What is adjkey? What files it will create?

Ans : adjkey is an adutility which will create digital signature, which will be used to sign all t" admin? mailto:adsign.txt@APPL_TOP>adsign.txt@APPL_TOP/adminappltop.cer@APPL_TOP/adminidentitydb.obj@applmgr home

47. What are the post installation task?

Ans : Running adjkey –initialize and then runnning adadmin to regerate jar files.

48. What are the clone errors, you have encountered?

Ans : Error:

RC-50013: Fatal: Failed to instantiate driver/u01/fms2c/appfms2c/fms2cora/iAS/appsutil/driver/instconf.drvCauseThe source instance has files that adpreclone flags as 'autoconfigable' but in reality they are not. So adpreclone.pl adds these files into the instconf.drv. Then when adcfgclone.pl is run on target it looks for the template file to instantiate for these files and since there isn't a template file adcfgclone.pl fails. SolutionModify the target's instconf.drv and remove the offending lines. Then rerun adcfgclone.pl

49. What are the real time problems you have encountered and how you trouble shooted that?

Ans:1. Concurrent Program is erroing out with snapshot too old error. To resolve this we have added space to temp tablespace.2. Concurrent Program is erroing out with unable to extent a perticular tablespace by so and so extents. To resolve this we have added on more data file to that tablespace.3. When we are trying to start apache with adapcctl.sh script after a autoconfig run, its saying that “node id is not matching with the application server id”. To resolve this we have updated the server id column in fnd_nodes table with the server id value in dbc file.

50. How you will find workflow version?

Ans : Run wfver.sql@FND_TOP/sql script as apps user

51 . When forms are running in servlet mode then the environment variables required for forms must be defined in what file and its location?

Ans : formsservlet.ini@$APACHE_TOP/Jserv/etc.

52. How to find out which patch driver is applied(like c,d,g or u)?

Ans: query ad_patch_drivers.

53. How to find out whether a language patch is applied for a perticular patch?

Ans : Query ad_patch_driver_langs.

54. How to validate that sysadmin password is correct or not from backend?

Ans: select fnd_web_sec.validate_login('SYSADMIN','Qwert8765') from dual;

55. How to compile jsp's(other than from adadmin)?

Ans: Force compilation of all jsps using the following command ojspCompile.pl --compile --flush

56. How to rotate logs for apache logs?

Ans: Using rotatelogs executable in httpd.conf file. Use Errorlog for error_log file rotation. Transferlog for other log files.

57. Other way of checking whether MRC is enabled or not besides using adadmin?

Ans : select multi_currency_flag from fnd_product_groups;

58. How to compile rdf?

Ans: Either using adadmin or rwcon60

59. How to change file/directory owner in linux/solaris?

Ans : chown - R :

Ex: chown - R applmgr:dba testappl

60. How to change the permission of file/directory in linux/solaris?

Ans : chmod –R

Ex : chmod –R 755 testappl

61. What are the files which contain apps password?

Ans :

1. wdbsrv.app@IAS_ORACLE_HOME/Apache/modplsql/cfg

2. CGIcmd.dat@806_ORACLE_HOME/reports60/server

3. wfmail.cfg@FND_TOP/resource - optional

4. CatalogLoader.conf@OA_JAVA - optional

5. CatalogLoader.xml@OA_HTML - optional

62. What is the script to find out ICM status?

Ans : afimchk.sql@FND_TOP/sql

63. What is the script to list the concurrent request status?

Ans: afrqrun.sql@FND_TOP/sql

64. What is the script that Lists managers that currently are running a request?

Ans : afcmrrq.sql@FND_TOP/sql

65. How can I determine whether a template is customizable or non-customizable?

Ans : If a keyword "LOCK" is present at the end of the file entry in the respective driver, then it is a non-customizable template. If the "LOCK" keyword is not seen, then that template can be customized.

66. How to find out JDBC version :

Ans : In the middle tier, edit the jserv.properties file located in the IAS_ORACLE_HOME/Apache/Jserv/etc directory- Locate the wrapper.classpath that is pointing to the jdbc zip file/opt/oracle/apps/$TWO_TASK/comn/java/jdbc14.zip

67. How to findout XML Parser Version

Ans : SQL> select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;

68.  How to find out WorkFlow Version

Ans :

SQL> select TEXT Version from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION';

69.  How to find a file version in Application DB:

select v.version,v.CREATION_DATE,c.CREATION_DATE from AD_FILES c,AD_FILE_VERSIONS v where c.FILENAME like 'ARPURGEB.pls' AND c.file_id = v.file_id AND c.app_short_name = 'AR';

When a copy driver (C) or the copy portion of a unified driver (U) are aborted for any reason, upon reapplying, the CREATION_DATE and/or LAST_UPDATE_DATE columns in the patching history tables (Ex: AD_FILE_VERSIONS)are not updated to show the proper installation date but are left with the 01-01-1950 date. you can resolve the issuse by applying the latest ad.I patch.

70.  How to check whether the product is install,shared and Not installed in Apps.?

Ans :

SQL>select t.application_name, t.application_id, i.patch_level, decode(i.status,’I',’Fully Installed’,‘N’,'Not Installed’,'S’,'Shared’,'Undetermined’) statusfrom fnd_product_installations i, fnd_application_vl twhere i.application_id = t.application_idorder by t.application_id;

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

Oracle Applications Question Answer

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