How to apply CPU or PSU patch without downtime!

Dear Brothers/Sisters,

Oracle releases Critical Patch Update every quarter for a year. CPU or PSU patches are not mandatory but its highly recommended to mitigate security issues. We will be in safer side if our databases are applied with latest CPU. Also its either DBA's decision or management to choose which release to be applied.

This article contains the few best approaching towards cpu patch activity. Basically all services should be down in order to test the CPU patch on database. Getting downtime is not an easiest option in critical environment. Also if we get the conflicts we may have to raise SR and wait for engineer's feedback. At this case we need multiple downtimes. 

To overcome this scenario i have been following this method which does not need any downtime :

Mainly You don't need to shut down any services since our activity is going to affect only the binaries where no services are running. 

Lets understand what opatch does. Basically opatch performs or modifies any libraries or binaries only on oracle home. Thus we dont need any services to be running under the oracle home when we apply opatch. 

Lets see how can we achieve this method without having any services :

1) Copy the entire oracle home binaries to different location and place them adjacent to any of your test instance. 

Test instance              :  /u01/clone/DEV/product
Dummy oracle home :  /u01/clone//DEV/product/CPU_11204

2) Name it as you like. Usually if i am replicating DEV oracle home, i would name it DEVCPU

3)  Create a dummy env file with new oracle home and new oracle sid


bash-3.2$ cat CPU_DEV.env

export ORACLE_HOME=/***/******/DEV/product/CPU_11204
export ORACLE_SID=DEVCPU
export PATH=$PATH:/***/******/DEV/product/CPU_11204/OPatch


4)  Register the newly create oracle home to the inventory. Usually i hoot the duplicate oracle home to central inventory. 

** source the dummy oracle home env file

bash-3.2$ cd $ORACLE_HOME/oui/bin

bash-3.2$ ./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=ORAHOME11Gr24CPU

Please read  how to attach db oracle home to global inventory for detail steps.

5) Run opatch lsinventory and see you are able to see the global inventory and present patches in oracle home. You can verify at the values below

Oracle Home       : /d01/oraclone/UAT/product/11204
Central Inventory : /u01/oracle/PROD/oraInventory

   from           : /d01/oraclone/UAT/product/11204/oraInst.loc

Please make sure the below values reflect the dummy oracle home and global inventory properly. 

6) Test the CPU patch on duplicate oracle home. 

7) If you are getting any conflicts, we can directly apply the patch.

8)If there are conflicts we need rollback the conflicts and then we need to apply the cpu patch. Finally we need to apply overlay patches (reverted patches)

** Its highly recommended to open service request if there are conflicts. Mainly SR engineer can help you with the overlay patches and the exact release which should be applied.

Steps are in detail :

step 1 : Rollback all the conflicts
step 2 : apply cpu patch
step 3 : Re-apply the overlay patches (rollbacked patches at step 1)


Once you are confident on the action plan you can get the down time and move the patches to instance by instance.

Hope this article helps!

Thanks!
~Sikky

Dear DBA's,

Autoconfig was unable to create listener due to the error below while r12.2.4 cloning. It was returning the error below in the autoconfig logs. 

If you ever face any issue in the listener.ora or tnsnames.ora in the oracle applications, Never attempt to create them manually or through anyother wizard. Instead run autoconfig which will populate listener.ora and tnsnames.ora for every run. If autoconfig is unable to create them, then we can conclude something is wrong with the setup. 

Error captured in log :


Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated

adgentns.pl exiting with status 2
ERRORCODE = 2 ERRORCODE_END
.end std out.

As soon as i see the message "Error generating tnsnames.ora from the database" from the log, nothing comes in my mind except fnd_nodes table. 

So i queried the fnd_nodes table and found the below :


SQL> select node_name from apps.fnd_nodes;

NODE_NAME
------------------------------
PRODERP --> Prod entry
PRODPDB --> Prod entry

Thats it. Error is due to the production entry in the fnd_nodes table and there is no entry for test. 

Solution is very simple. Clean the fnd_nodes table then Simply run autconfig on both side. Start with db tier then apps tier. 

how to clean fnd_nodes table in oracle apps  r12.2.4 ?

SQL> exec fnd_conc_clone.setup_clean();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select node_name from apps.fnd_nodes;

no rows selected

After cleaning the nodes double check you dont see any of the node entry in fnd_nodes.

Now just populate the host entries for test servers.

Run autoconfig in db tier.
Run autoconfig in apps tier. 

If you query fnd_nodes after autoconfig you should see the test servers entry. 

Thats it. The moment you ran autoconfig you can see the listener.ora and tnsnames.ora have been regenerated by autoconfig.

Just start the listener and it should start without any issue.

how to start oracle apps listener ?

Command to start listener : lsnrctl start APPS_UAT (ex : APPS_SID )

Thanks!
~Sikky

R12.2.4 autoconfig failed while cloning : ld.so.1: FNDCPUCF: fatal: /a01/appstier/UAT/fs2/EBSapps/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64

Dear DBA's,

This error encountered while cloning r12.2.4. My apps tier is repeatedly ended with error in apps side. Cloning ended with 100% completed status but autoconfig recorded some errors. I was able to start the services without any error status but i was unable to launch the forms. This issue is due to relink does not happen properly during autoconfig phase. So simply running autoconfig wont fix the issue. I had to fix the issue and re-run adcfgclone. 

I have described the steps i followed in detail through this article. 


Error returned at the bottom of the autoconfig log :

WARNING: [CVM Error Report]
The following report lists errors encountered during CVM Phase
      <filename>  <return code where appropriate>
  /a01/appstier/UAT/fs2/EBSapps/appl/ad/12.0.0/bin/adgentns.pl  2

 No. of scripts failed in CVM phase: 1



WARNING: [AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /a01/appstier/UAT/fs2/inst/apps/UAT_ahqtest/admin/install
      afcpctx.sh              INSTE8_PRF         137



AutoConfig is exiting with status 1


I wasted bunch of time on tracing out this issue. Later i found out this is not the reason for the error. 

I strongly recommend don't just come to conclusion that this is the reason for the error. We need to read through the entire autoconfig log to find actual issue. 

I just searched the autoconfig log with the keyword "error" and i found the below. 

Actual Error :

Uploading Metadata file /a01/appstier/UAT/fs2/EBSapps/appl/ad/12.0.0/admin/template/adctxinf.tmp
Metadata file /a01/appstier/UAT/fs2/EBSapps/appl/ad/12.0.0/admin/template/adctxinf.tmp upload failed.
Check log file created by FNDCPUCF program.
ERRORCODE = 137 ERRORCODE_END
.end std out.
ld.so.1: FNDCPUCF: fatal: /a01/appstier/UAT/fs2/EBSapps/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
Killed
ld.so.1: FNDCPUCF: fatal: /a01/appstier/UAT/fs2/EBSapps/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
Killed


You can find the note below if you search with the above error.

R12 - adcfgclone appsTier fails with error for ld.so.1 'wrong ELF class: ELFCLASS64' (Doc ID 1078123.1)


After reading the note, i found out that the user does not have any permission to central inventory. But instead of giving him actual permission we could follow the below :

1. Create a global inventory under its default locations. 

For Solaris : /var/opt/oracle/ 

2. oraInst.loc must point to any directory location where the user has access

You should see : /var/opt/oracle/oraInventory

3. Rename the previous global inventory :

mv oraInventory to oraInventory_old 

create empty directory as oraInventory (mkdir oraInventory)

Make sure the user which you run cfgclone has full permission on the directory above.

4. Start the Cloning scripts again - for the DB-Tier and the APPS-Tier

I just re-run the apps tier and i was able to complete the cloning. 

5. Check the new created logfile ohclone.log

Thanks!
~Sikky

Login failed for few users in oracle ebs R12.2.4

Dear Brothers/Sisters,

This issue happened only to few of the users. I could not find any error in application logs to figure out root cause.

Try the below once before you actually fixing the issue. Sometime the below help if there is no setup related issue.

1) Stop application $ADMIN_SCRIPTS_HOME/adstpall.sh
re-compile JSP pages

perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2 -log /tmp/ojspc_error.log

Start application $ADMIN_SCRIPTS_HOME/adstrtal.sh

Retest issue .


But in my case i had to do the below to figure out the issue.

2)

Is it possible to enable FNS_debug log on any user then provide fnd log by following steps
FND Debug Steps

1) Set below profile options on user level

FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %

Provide fnd.wf.worklist% in module for mobile apps else keep %

2) SELECT MAX(LOG_SEQUENCE) before_seq
FROM FND_LOG_MESSAGES;

3) Run your test case/ Produce issues

4) SELECT MAX(LOG_SEQUENCE) after_seq
FROM FND_LOG_MESSAGES;

5) Retrieve error messages and save in Excel format.

SELECT module, message_text
FROM fnd_log_messages
WHERE log_sequence between &before_seq and &after_seq
ORDER BY log_sequence;


Found the below in excel sheet :


fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log

Oracle error -1654: ORA-01654: unable to extend index APPLSYS.FND_LOGINS_N2 by 16 in tablespace APPS_TS_ARCHIVE has been detected in FND_SIGNON.AUDIT_USER.


fnd.plsql.FND_SESSION_MANAGEMENT.createSessionPrivate


Exception:-20001 ORA-20001: Oracle error -20001: ORA-20001: Oracle error -1654: ORA-01654: unable to extend index APPLSYS.FND_LOGINS_N2 by 16 in tablespace APPS_TS_ARCHIVE has been detected in FND_SIGNON.AUDIT_USER. has been detected in FND_SIGNON.NEW_PROXY_ICX_SESSION.

- Error is due to No space in one of the tablespace. This bigger step could have been avoided if i just have looked into DB alert log. I was chasing behind everything instead of DB alert log :-)

I found this step is really helpful when i thought what if there is no error in DB. I would be happy if it helps someone. It will be appreciated any feedback about this blog.

Thanks!
~Sikky

How to find the error when nothing is logged in the logs in R12.2?

Dear Brothers/Sisters,

We were getting these error in few of the pages in AP module and none of the notification worked due to this error.

"You have encountered an unexpected error. Please contact the System Administrator for assistance"

There is an another similar kinda issue i have blogged aboutm

Please read : EBS login page is not working   Few users unable to log into R12


Troubleshooting is very simple technique when you find out the error. All you have to know is where to find that one. Once you found the cause of the trouble, fixing is simple thing.

Main goal of this post to share my ideology to narrow the issue and how we can trace the error stack in OAF page when we have no clue.

I did the below the moment i noticed the error :

- Apache, oacore, oafm, forms logs
- Alert log in DB
- Mount point sizes in app and db server.

Everything seems to be normal and i had to clue to figure out the cause. So i had to trace one of the page as all the ways are closed. Let me summarize the events i had to figure out the error.

- Set FND: Diagnosticts profile option set to yes to user
- Reproduce the error

After i enabled debug option to my user, i could figure at least what was cause of the error.  I saw the additional options in the error page. When i clicked exception detail i can see error stack.

- See about this page or exception detail to find the error. I have highlighted the error in yellow.



Error Stack:

## Detail 0 ## java.sql.SQLException: ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT, N, ERRNO, -20001, N, REASON, ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option. ORA-06512: at "APPS.PO_DOCUMENT_TOTALS_PVT", line 475 ORA-06512: at line 1 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:213) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1111) at

- This error is specific to our env and cause of the error is one of the profile option (MO: Security profile). Profile option had been set to null value recently due to which few modules went into error. After fixing it, everything became normal. 

How i did?

-  First thing error was in production. 

-  I tried to replicate the error in recent clone which was cloned 3 days ago. Luckily i had the same error

- We had 2 more dev instance left which was not cloned for  a while. I was able to see error pages were working fine when i tried to replicate the error.

- So quickly i looked for profile option value to see what value it had. 

- Error disappeared after i set the same value in production. 

To implement the solution, please execute the following steps: 

1. Go into the responsibility: System Administrator 

2. Navigate to Profiles > System > check Site option 

3. Be sure that either profile for Multi-Org Access Control 'MO: Security Profile' OR 'MO: Operating Unit' is set appropriately. 

4. Retest the issue 

*** Value presented in the profile option was specific to our env. Please make sure with the consultants before you set this value in production***

Main goal of this post to share my ideology to narrow the issue and how we can trace the error stack in OAF page when we have no clue.

We can trace the error from the database as well. Please do the following to figure out the cause from tables.  

A. 

To Enable FND debug: 

Navigate Responsibility: System Administrator > Profile => System 

a. Find the FND profile options for User who is submitting the process to debug. 

User: User submitting the process 

Profile: FND:%Debug% 

b. Set the following Profile values at the user level 

FND: Debug Log Enabled :Yes 

FND: Debug Log Filename : NULL 

FND: Debug Log Level : STATEMENT 

FND: Debug Log Module : % 

c. Save. 

B. 

Get the current log sequence in the FND table. 

select max(log_sequence) from fnd_log_messages ; -- start 

C. 

Reproduce the issue 

D. Get the sequence in the FND log 

select max(log_sequence) from fnd_log_messages ; -- end 

E. Spool the output of the following query to an excel file which gives FND debug log: 

Run this script and replace start and end with the sequences you obtained from steps B and D. 

select module, message_text 

from fnd_log_messages 

where log_sequence between <start> and <end>;

Thanks!
~Sikky

step by step procedure to run concurrent recovery wizard in R12.2.4?

Dear Brothers/Sisters,

This post is all about the concurrent manager recovery wizard which will surprise you at critical time. This wizard comes up in-built and we dont need to make any extra efforts to configure. I have provided the step by step screen shots from home page to wizard ending page.

Please utilize this wizard when your ICM does not start even though after multiple bounce. I think oracle is developing their ERP with the real time scenarios, when this CM were failed to start it just reminded me my childhood days. When i dont get up from bed my mother used to splash water in face, the same idea gonna work our here as well to wake up CM.

 I have tested this in production and test when my CM failed to start and it worked like a champ. Please run this when your CM failed to start for no reason. But tried killing all FNDLIBR processed for the specific instance which causes trouble and try to restart CM. If there is no zombie processes registered in db, CM restart may help.

I can see this error in ICM logs which where helpless. No error in DB alert log. And finally i decided to run this wizard. You dont need to shutdown CM for this activity and ICM must be running while running this wizard.

Segmentation Fault - core dumped : The PROD_1027@PROD internal concurrent manager has terminated with status 139 - giving up.


========================================================================
Starting PROD_1027@PROD Internal Concurrent Manager -- shell process ID 1673

          logfile=/u01/oracle/PROD/fs_ne/inst/PROD_******logs/appl/conc/log/PROD_1027.mgr
          PRINTER=noprint
           mailto=applmgr
          restart=N
             diag=N
            sleep=30
             pmon=4
           quesiz=1
          Reviver is ENABLED

Segmentation Fault - core dumped
The PROD_1027@PROD internal concurrent manager has terminated with status 139 - giving up.

How to access the concurrent manager recovery wizard?

- Log into sysadmin user
- Navigate to oracle applications manager under System administrator responsibility
- Click any menu option under Oracle Applications Manager

- Click Site Map after landing to this page.

NAvigate to Diagnostics and Repair tab and at the right side you can see Wizard.

Thats it! its tedious to find the Concurrent manager recovery wizard. Once you found it, it will make the job easier.

Issue is due to there are run away processes are registered in database which does not allow CM to start. Once i hit update button, Wizard will clear the run away processes from DB.

For more Info :

Concurrent Manager Recovery Wizard - Oracle Applications Manager Troubleshooting and Diagnostics (Doc ID 2130545.1)

How to attach ebs 10.1.2 oracle home to central inventory

Hello,

This post is to define "How to attach ebs 10.1.2 oracle home to central inventory"

bash-3.2$ opatch apply

Oracle Interim Patch Installer version 1.0.0.0.64
Copyright (c) 2011 Oracle Corporation. All Rights Reserved..

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

Oracle Home           : /a01/appstier/UAT/fs2/EBSapps/10.1.2
Oracle Home Inventory : /a01/appstier/UAT/fs2/EBSapps/10.1.2/inventory
Central Inventory     : /var/opt/oracle/oraInventory
   from               : /var/opt/oracle/oraInst.loc
OUI location          : /a01/appstier/UAT/fs2/EBSapps/10.1.2/oui
OUI shared library    : /a01/appstier/UAT/fs2/EBSapps/10.1.2/oui/lib/solaris/liboraInstaller.so
Java location         : /a01/appstier/UAT/fs2/EBSapps/10.1.2/jdk/jre/bin/java
Log file location     : /a01/appstier/UAT/fs2/EBSapps/10.1.2/.patch_storage/<patch ID>/*.log

Creating log file "/a01/appstier/UAT/fs2/EBSapps/10.1.2/.patch_storage/18752577/Apply_18752577_11-25-2017_09-57-56.log"

The Oracle Home /a01/appstier/UAT/fs2/EBSapps/10.1.2 is not registered with the Central Inventory.  OPatch was not able to get details of the home from the inventory.

ERROR: OPatch failed because of Inventory problem.

When we apply patch in oracle home, OPatch checks for the inventory to understand the patch detail which already present. It gives the error above if it does not find any patch details. Since oracle home is not registered with central inventory, opatch could nt get any of the patch details thus it threw an error. We need to attach this particular oracle home to central inventory in order to overcome this error. This is one of the method to overcome this error. 

bash-3.2$ cat /var/opt/oracle/oraInst.loc
inventory_loc=/var/opt/oracle/oraInventory
inst_group=dba

Source env file

bash-3.2$ cd $ORACLE_HOME/oui/bin/

Command to be executed :

./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/a01/appstier/UAT/fs2/EBSapps/10.1.2" ORACLE_HOME_NAME="UAT_TOOLS_fs2_EBSapps_10_1_2"

bash-3.2$ ./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc ORACLE_HOME="/a01/appstier/UAT/fs2/EBSapps/10.1.2" ORACLE_HOME_NAME="UAT_TOOLS_fs2_EBSapps_10_1_2"

Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-11-25_10-27-20AM. Please wait ...bash-3.2$

bash-3.2$ 'AttachHome' was successful.