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.
Tuesday, May 31, 2011
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
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
Friday, May 27, 2011
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)
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)
Thursday, May 26, 2011
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.'
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.'
Monday, May 23, 2011
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
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
Saturday, May 14, 2011
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
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.
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
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
Wednesday, May 11, 2011
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.
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.
Tuesday, May 10, 2011
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
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;
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.
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.
Saturday, May 7, 2011
How to Create Custom Top
You can create custom top in oracle application using following Process:Step 1: Replicate APPL_TOP Dircetory in UNIXCd $APPL_TOPStep 2: Create Custom Top Under APPL_TOP DirMkdir 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 fileStep 4: Login Oracle apps using Sysadmin or AOLNavigate: Applications->RegisterAdd your Custom top Entry HereApplication: XXApplication nameShort name: XXCUSTBasePath: XXCUST_TOPDescription: XXApplication.Step 5: Restart the Internal Concurrent Manager (ICM) So that New Changes will recognize.Step 6: Finally Check Custom Entry inSelect BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAMEFrom fnd_applicationWhere application_Short_name like 'XXCUST_TOP%'
Friday, May 6, 2011
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.1Maintenance 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 performanceand reduces downtime when applying a patch. For more information, refer to Preparing your System for Patching in Oracle Applications Maintenance Utilities.
Thursday, May 5, 2011
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
$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
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_objectswhere object_name='FND_WEB_SEC';
SQL>col OBJECT_NAME for a30;
set line 200;
select object_name,object_type,status,owner from dba_objectswhere 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_valueswhere profile_option_id = (select profile_option_id from apps.fnd_profile_optionswhere 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 andFND_ORACLE_USERID tables,The FND_USER table stores application useraccount passwords and the FND_ORACLE_USERID table stores internal Oracle Applications database account passwords,Both tables use thesame 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_SECpackage in Apps.The DECRYPT function in the FND_WEB_SEC package isa 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 isselect user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);beginfor all_user in cur1 loopapps.fnd_user_pkg.EnableUser(all_user.user_name);commit;end loop;End
Subscribe to:
Comments (Atom)