Oracle apps upgrade db connection listener problem ORA-27101

Scenario : Upgraded the Database to 11.2.0.2 in an Oracle EBS environment.

Connection via sqlplus is working fine.

Manually configured the listener. Checked tnsping. Tnsping is working fine with the status as ok.

However when connecting to the Database for doing the other post activities, the failure occurs, and upon investigation, it has been found out that the actual connectivity via a tnsnames.ora is not occurring properly.

This has been verified by the following:

[devora@linux_server03 ~]$ sqlplus apps/apps@migr
SQL*Plus: Release 11.2.0.2.0 Production on Tue May 10 14:30:43 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:


=======

To resolve it :-


SQL> ALTER SYSTEM SET LOCAL_LISTENER="(address=(protocol=TCP)(host=linux_server03) (port=1528))" SID='MIGR';
System altered.
SQL>

or

you can add the below entry in the init.ora file and save it..

 LOCAL_LISTENER=MIGR


=======

Always ensure that you start the listener and then only start the Database for Oracle EBS (non-rac) environment.

Users and Responsibilities in Oracle E-Business Suite

Getting to know all Oracle Users and their responsbilities within E-Business Suite? Yes, this will be required during audit purposes and also to keep a track of who has what and whether they should be using that responsibility or not.



select fu.user_name, fu.description,fu.start_date,fu.end_date,
fu.email_address, fr.responsibility_name,  furg.START_DATE, 
furg.END_DATE from fnd_user_resp_groups_direct furg,
fnd_user fu, fnd_responsibility_tl fr
-- where fu.user_name = ‘&user_name’
where furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
order by 1
 

Applying PSU in Oracle Database? Check whether they have any patch conflicts

You would be better off to resolve the conflicts while applying the PSU Patches on the test environment than ignoring the conflicts, which can cause serious harm at a later stage.

To check if a particular patch has got any conflicts, run the below command from the patch directory.

$ cd 10248636
$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0
Central Inventory : /u02/oracle/FEB/DB_TIER/db/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.4.0
OUI location : /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/oui
Log file location : /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/cfgtoollogs/opatch/opatch2011-03-11_08-57-14AM.log

Patch history file: /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

ZOP-40: The patch(es) has conflicts with other patches installed in the Oracle Home (or) among themselves.

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
9032412, 10248636

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
9032412

Conflicts/Supersets for each patch are:

Patch : 10248636

Conflict with 9032412
Conflict details:
/u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/lib/libserver10.a:/kwqit.o

OPatch succeeded.


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

=====================================================================----------------------------------------------


$ cd 10248636
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0
Central Inventory : /u02/oracle/FEB/DB_TIER/db/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.4.0
OUI location : /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/oui
Log file location : /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/cfgtoollogs/opatch/opatch2011-03-11_08-57-31AM.log

Patch history file: /u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

ZOP-40: The patch(es) has conflicts with other patches installed in the Oracle Home (or) among themselves.

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
9032412, 10248636

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
9032412

Conflicts/Supersets for each patch are:

Patch : 10248636

Conflict with 9032412
Conflict details:
/u02/oracle/FEB/DB_TIER/db/tech_st/10.2.0/lib/libserver10.a:/kwqit.o

OPatch succeeded.
$

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

The above shows that there is a conflict if I were to apply the patch 10248636.
So, I resolve the conflict, or I open an SR with Oracle and send across the log file as well as my inventory in detail so I can get a merged patch .


-----

To get your detailed inventory, execute the below command...

$ opatch lsinventory -detail

Environment Variable - NLS_LANG in Windows Server 2008

After a new server  - Windows Server 2008 was built, and after the Oracle client was setup, the application that was talking to the database was not fetching the data.
Some of the tables were not displaying data, and as a result there were problems.

Oracle Apps tables wont display data, unless the correct environment variables are set.

a) You would need to add the nls_lang to the  system variables under the environment variables.




Adding NLS_LANG for Oracle environment

 

b) You would need to log off and log back in to get the settings in place.

c) Check your Oracle environment, if they have been in place. You may have to change the setting in registry, and in Win 2008 server, it is in --> HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1

Now users should be able to get the desired output from viewing the tables via command prompt, or with other tools.


d) Using SQLDeveloper, the developers were crying that they were not able to see the data.

Solution? You would need to change the NLS Settings in the SQL Developer to view the appropriate data.


I was astonished the next day when I came and the developer told me that he was not able to see the data. He searched in and out in the registry for any links to developer, but could not get it working.

SOLUTION: Change the NLS settings by going to SQL Developer -->  tools --> preferences --> databases --> NLS


SQL Developer NLS_LANG settings for NLS
Below is what was done in my laptop to view data from tables.