Thursday, 26 May 2016

ORA-12545

ORA-12545: Connect failed because target host or object does not exist

Problem

You attempt a connection to database through SQL*Plus and you receive the following error.

Error:- 

[oracle@sujeet ]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 30 10:29:27 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect hr@airteldb
Enter password: 
ERROR:
ORA-12545: Connect failed because target host or object does not exist

If you are unfamiliar with the error use the oerr utility to get more information on the error.

[oracle@sujeet ]$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

Nowhere in the error description above is any mention of this problem being
related to the listener. This error is due to the inability of TNS to contact
the host in the ADDRESS parameter for the database entry. This could be due to
a typo in the host name or IP address. You do not have to take my word for it
though. If you set up Oracle Net Services tracing on the client you should
see something similar to the following.

(3086055104) niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pr0d)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)(HOST=ora2.localdomain)(USER=oracle))))
(3086055104) nscall: connecting...
(3086055104) snlinGetAddrInfo: getaddrinfo() failed with error -3
(3086055104) nlad_expand_hst: GetAddrInfo call failed
(3086055104) nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pr0d)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)(HOST=ora2.localdomain)(USER=oracle))))
(3086055104) snlinGetAddrInfo: getaddrinfo() failed with error -2
(3086055104) nttbnd2addr: looking up IP addr for host: pr0d
(3086055104) snlinGetAddrInfo: getaddrinfo() failed with error -3
(3086055104) nttbnd2addr:  *** hostname lookup failure! ***
(3086055104) nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=111, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(3086055104) nioqper:  error from nscall
(3086055104) nioqper:    ns main err code: 12545
(3086055104) nioqper:    ns (2)  err code: 12560
(3086055104) nioqper:    nt main err code: 515
(3086055104) nioqper:    nt (2)  err code: 111
(3086055104) nioqper:    nt OS   err code: 0
(3086055104) niqme: reporting NS-12545 error as ORA-12545
(3086055104) niotns: Couldn't connect, returning 12545
Here we see that the call to getaddrinf() failed with error.
There was also a failure in attempting to get the IP address of pr0d.
So here the trace shows that we were not even able to contact the host
server let alone the listener.

Cause:  

The address specified is not valid, or the program being connected to does not exist.

solution

In the trace we can see the typo in the name of the host.
The HOST= should be prod not pr0d. Changing that in the
ORACLE_HOME/network/admin/tnsname.ora should resolve the problem.
A trace is not necessary to resolve this issue successfully.
The output from the oerr utility gave us the correct direction.
 We could have easily looked at the ORACLE_HOME/network/admin/tnsnames.ora
file and seen that the host name was incorrect.

This is my tnsnames.ora entry,

prdot = (DESCRIPTION=
  (ADDRESS=
  (COMMUNITY=TIA_TCP)
  (PROTOCOL=TCP)
  (Host=**.***.***.***)  - -- tried this with actual host name also instead of numeric ip
  (Port=1521)
  )
  (CONNECT_DATA=(SID=prdot) (srvr=dedicated) )
  )

The ORA-12545 is not an indication that the network is down or that the
host file or DNS has an incorrect address for the host in question.
In those cases an ORA-12543 will be given.





Monday, 23 May 2016

How to setup printer in oracle apps R12.2.4


A) Prerequisites:-

Printers should be first defined at Linux OS level by customers.
Please check the status of printer at Linux level by using the following command
lpstat -a

B) Configure Printer in Oracle Applications

The following is the order of Printer setup in Oracle Applications:
Define Driver
Define Style
Define Printer Type
Register the Printer

1) Define the printer driver, Install > Printer > Driver

Login to application using System Administrator or Application Administrator resp

Define the printer driver, Install > Printer > Driver



Please provide the below values (depending upon your configuration) to set up.

 Enter the Driver Name :Canon
 Enter the User Driver Name: Canon for Printer HPLJ2
 Description: Give some desc appropriate
 SRW Driver: HP

SRW drivers are read by Oracle reports when a report is run and insert control characters which tell the destination printer on where to insert the page breaks and font to be used etc.. SRW driver is used when output file is sent to the printer

SRW driver files on linux are located in $FND_TOP/reports. In the above example, file name would be HP.prt

 Platform: Not required
 Arguments: Printer Driver pass the following arguments to program
 Name of the destination printer
 Number of Copies to print
 Banner of the title page
 Filename
 Initialization and Reset strings: Instructs printer on printing characteristics are needed for the document to be printed. Printer vendor provides these details

2) Define the printer style , Install > Printer > Style

Login to application using System Administrator or Application Administrator resp

Define the printer style , Install > Printer > Style

Please provide the below values (depending upon your configuration) to set up.

 Style Name : HP
 Seq: Enter unique sequence number
 User Style: Canon -HPLJ2
 SRW Driver: HPW , this should match with driver define page
 Layout: Columns,Rows and Orientation: Columns and Rows inserted here override the height and width defined in SRW driver file

3) Define the printer type, Install > Printer > Type

Login to application using System Administrator or Application Administrator resp

Define the printer type, Install > Printer > Type

Please provide the below values (depending upon your configuration) to set up.

 Type: HPLJ2
 Style: Style name
 Driver Name: Driver name

4)Register a new printer , Navigation Path is Install > Printer > Register

Login to application using System Administrator or Application Administrator resp

Define the printer type, Install > Printer > Type



Please provide the below values (depending upon your configuration) to set up.

 Printer : Printer Name
 Type: Type defined earlier
 Description : Useful description


Some important tables related with printing are:
-----------------------------------------------

FND_PRINTER
FND_PRINTER_INFORMATION
FND_PRINTER_STYLES
FND_PRINTER_DRIVERS


NOTE: Please note any changes to Printer Type, Style and Driver needs bouncing of concurrent manager for the changes to take effect.

Sunday, 22 May 2016

PSU and CPU patch applied status in Oracle 11g2


How to check the PSU & CPU patch applied status???

[ora@sujeet ~]$ cd $ORACLE_HOME/OPatch

[ora@sujeet ~]$ opatch lsinv -bugs_fixed | grep PSU

[ora@sujeet ~]$ opatch lsinv -bugs_fixed | grep CPU


Opatch status check:


[ora@sujeet ~]$ opatch lsinventory|grep <patch no>

Security Patch status:-

SQL> select * from sys.registry$history;

-----------------------------------------
SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12
SELECT * FROM registry$history;
---------------------------------------------
NOTE:

1.Critical Patch Update (CPU)

2.Patch Set Update (PSU)

Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter.  Think of the CPU as the overarching quarterly release and not as a single patch.

Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and priority fixes.  The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).  Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.

Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch.  SPU patches are the same as previous CPU patches, just a new name.  For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version.

Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes.

Keeping your Oracle database software up to date is a critical and time-consuming task for DBAs.  For many years now, Oracle has been releasing Critical Patch Updates on a quarterly basis.  These patches, as the name implies, contain critical updates to the software, often released in response to a newly found security vulnerability.  More recently, Oracle has also been releasing Patch Set Updates on a quarterly basis.  These also contain important fixes to the Oracle software.  However, there is confusion about the difference between the two and more importantly, confusion about which one needs to be applied.  So whats the difference and which one should you apply?

 According to Oracle Support article ID 1446582.1: Frequently Asked Questions (FAQ) Patching Oracle Database Server:

“A PSU is a collection of proactive, stabilizing cumulative patches for a particular product version (base release or patch set).  PSUs are cumulative and include all of the security fixes from CPU patches, plus additional fixes.  Critical Patch Updates are the primary means of releasing security fixes for Oracle products. CPUs are cumulative with respect to prior CPUs and generally contain only security fixes.”

So, there you have it.  CPUs are smaller and more focused than PSU and mostly deal with security issues.  PSUs contain bug fixes AND they contain the security fixes from the CPU.  When you download a PSU, it will tell you which CPU it contains.  PSUs are on the same quarterly schedule as the Critical Patch Updates (CPU), specifically the Tuesday closest to the 17th of January, April, July, and October.  One thing to keep in mind, however, is that once a PSU has been installed, the recommended way to get future security content is to apply subsequent PSUs.  Reverting from PSU back to CPU, while possible, would require significant effort and so is not advised.  So with this in mind, why would someone choose to apply a CPU rather than a PSU?  I suppose for folks who are concerned only with security fixes and not functionality fixes, a CPU-only approach may be best.  It does seem to be the more conservative approach as a CPU is (in theory) less like to cause trouble than a PSU, simply because it has less code changes in it.

Differences between PSU , CPU and SPU in Oracle 11g

Differences between PSU , CPU and SPU in Oracle 11g.


Critical Patch Update (CPU) Release of security fixes each quarter instead the cumulative database security patch for the quarter. 

Patch Set Updates (PSU)  Cumulative patches that include both the security fixes and priority fixes.  They are "version upgrades" modifying the las number of version (11.2.0.1.1, 11.2.0.1.2,...).

Security Patch Update (SPU)  It is the new terminology of CPU (From October 2012) . SPU's can't be applied if any PSU's have been applied, unless your database is upgraded with a new base version.

Here is brief  summary of what is the difference between patch set , PSU and CPU. I will start with a image, might not be 100% accurate but will help a long way in understanding the differences.
Patch sets and PSU
PSU’s(Patch set updates) are patch sets but with some major differences with respect to regular patch sets.
PSUs are generally low risk and do not contain fixes to bugs that require configuration changes or impact database components like optimizer. To achieve this low risk behavior, Each PSU is limited from 25 to 100 new bug fixes. PSU’s are also well tested by Oracle compared to one off patches.
PSUs are referenced by their 5th place in the Oracle version numbers which makes it easier to track ( (e.g. 10.2.0.3.1) and will not change the version of oracle binaries (like sqlplus, exp/imp etc.) As of now (10.2, 11,2) , the best way to  determine the PSU version installed  is to use opatch utility using the command below
opatch lsinv -bugs_fixed | grep -i  PSU
From the database , you can execute the following query(Assuming that catbundle.sql  was executed when PSU was installed). The last 2 columns will provide information about the PSU.
select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version, 
substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;
In summary PSU’s  contain fix for bugs that cause
  • Instance crash
  • Wrong results
  • Data Corruption
PSU’s  do not contain fix for bugs  that may result in
  • Dictionary changes
  • Major Algorithm changes
  • Architectural changes
  • Optimizer plan changes
Regular patch sets on other hand include major bug fixes and most of the time requires re-certification.The importance of PSU is diminished once a regular patch set is released for a given version as they tend to be more security related fixes as CPU is part of PSU
PSU and CPU
PSUs  contain CPU’s and are released every quarter (like CPU’s) ; In other words  Critical Patch Update (CPU) is a subset of the Patch Set Update (PSU). CPU’s are built on the base Patch Set version (e.g. 10.2.0.3) whereas PSU are built on the base of the previous PSU (e.g. 10.2.0.3.1)
A PSU can always be applied over any CPU where as applying a CPU over a PSU will roll back the PSU (at least for now). Therefore it is easy to go from CPUs to PSUs and hard to go back to CPUs  from PSUs.


Wednesday, 18 May 2016

Check AD and TXK patches versions applied in R12.2.4

Oracle EBS12.2.4 Check AD and TXK patches versions applied.




[appl@sujeet ~]$ cd /u02/apps/fs1/EBSapps/appl/
[appl@sujeet appl]$ . APPSPREPROD_chintels.env 
[appl@sujeet appl]$ sqlplus apps/apps

SQL> select  ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad');

ABBREVIA NAME  CODELEVEL
-------- ---------------------------------------- ------------------------------
ad Applications DBA  C.6
txk Oracle Applications Technology Stack  C.6





ORA-32017

ORA-32017: failure in updating SPFILE

ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE


Modifying log_archive_dest_n will return this error due to invalid syntax. This parameter is dynamic and can be updated using the ALTER SYSTEM statement.

Here is an example of an error raised when trying up modify the parameter log_archive_dest_2 to NULL.

SQL> alter system set log_archive_dest_2=NULL scope=both;
alter system set log_archive_dest_2=NULL
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE

To update this parameter to NULL or to a valid location use the following syntax:

SQL> alter system set log_archive_dest_2='LOCATION=NULL' scope=both;

or
SQL> alter system set log_archive_dest_2='LOCATION=/u02/oradata/arch' scope=both;

ORA-16179

ORA-16179: INCREMENTAL CHANGES TO "log_archive_dest_2" NOT ALLOWED WITH SPFILE

Cause :-

Modifying log_archive_dest_2 will return this error for invalid syntax. This parameter is dynamic and can be changed using the ALTER SYSTEM/ALTER SESSION statement.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='\app\oradata\ARCHIVE2' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='D:\app\oradata\ARCHIVE2' SCOPE=BOTH
*
ERROR AT LINE 1:
ORA-32017: FAILURE IN UPDATING SPFILE
ORA-16179: INCREMENTAL CHANGES TO "log_archive_dest_2" NOT ALLOWED WITH SPFILE

Solution:-

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=\app\oradata\ARCHIVE2' SCOPE=BOTH;

SYSTEM ALTERED.

Friday, 6 May 2016

Create Menu in Oracle EBSR12.2.4

How to Create Menu in Oracle Apps R12.2.4

What is Menu in Oracle Apps?
It is a logical or hierarchical listing of functions (forms) so that the user
can easily navigate through the forms once he is in a responsibility.

A menu is a hierarchical arrangement of functions and menus of functions that
appears in the Navigator. Each responsibility has a menu assigned to it.
Every responsibility will be assigned only one main menu. Under that main menu,
 you can have either other sub menus or functions.

Note: Define any menus that you intend to call from your menu. Define the
lowest-level submenus first. A submenu must be defined before it can be called by
another menu.

Function

A function is a part of an application’s functionality that is registered
under a unique name for the purpose of assigning it to, or excluding it from, a menu
(and by extension, a responsibility).

Functions are nothing but the forms which ultimately the users would like to work on.

There are several types of functions: form functions, sub functions,
and non-form functions. We often refer to a form function simply as a form.
 One menu can be assigned to multiple responsibilities.

When you login to System Administrator responsibility, First of all,
you will see a lot of menus.

From here if you want to go Users form, you will navigate to

Security > Users > Define

And if you want to go on Responsibility form, you need to navigate:

Security > Responsibility > Define


Suppose you want to create a new custom menu: MY_ACCESS Menu

Which have Users and Responsibility form only, because you didn’t want
to navigate multiple sub menus and functions.

Follow these steps to understand how to build a new custom menu.

Identify the Function Name of Users Form
Navigation: System Administrator > Security > Responsibility > Define

Responsibilities form open, Press F11 and Query for “System Administrator”

Here you can see that the value in the Menu field is the Main Menu
attached to this responsibility. (Copy that main menu name and saved
in a separate text file.)

Now to find out the menu details, navigate to:

System Administrator > Application > Menu

Menus window have following fields:

Menu

It is a short name of the Menu. You can choose a name that describes the
purpose of the menu. Users do not see this menu name.

User Menu Name

It is the user defined name of the Menu. You use the user menu name when a
responsibility calls a menu or when one menu calls another.

Menu Type:

It’s the menu type which you can choose according to your menu. Here you can choose
“Standard”

Description: It’s the meaningful description for the menu.

Sequence

It’s the available menu items appear in the sequence specified. Enter a sequence
 number to specify where a menu entry appears relative to other menu entries in a menu.
 The default value for this field is the next whole sequence number.

A menu entry with a lower sequence number appears before a menu entry with a
 higher sequence number.

Note: You cannot replace a menu entry sequence number with another sequence
number that already exists. If you want to add menu entries to a menu entry sequence
 that uses only integers, carefully renumber your menu entries to a sequence range well
outside the sequence range you want, ensuring that you do not use existing sequence numbers.

Submenu:

Call another menu and allow your user to select menu entries from that menu.

Function

Call a function you wish to include in the menu. A form function (form)
appears in the Navigate window and allows access to that form. Other
non-form functions (subfunctions) allow access to a particular subset of form
functionality from this menu.

Description

Descriptions appear in a field at the top of the Navigate window when a menu
entry is highlighted.

View Tree Button:

It will display the menu and its entries in graphical format.

Grant:

A grant authorizes a particular role to perform a specified action or actions
(set of functions) on a specified object instance (or object instance set).

Press F11 and Query for User Menu Name as:

“Navigator Menu – System Administrator GUI”

(It is that you copied from Menu field value in responsibilities window).

Here you need to find the exact Function name of Users form. If you remember above,
Users form is under Security sub menu. So first find out the Security.

Here you can see that Function field is empty and there is a value under Sub Menu.

Why it is….?

Because, the Security is another Sub menu and not a form/function by itself.
(Copy this Sub Menu value and saved in your text file.)

Now, you will query this Sub menu in the same Menus window.

“Security Menu – System Administrator GUI”

As you know above, Users forms is under Security > Users

So when you find User in Security Menu. You see it has another Sub Menu.

Now, in the same Menus form, query for

“User Menu – System Administrator GUI” as User Menu Name.


You can see “Users” is coming under Function field which means that node is ended her.
There are no more sub menus, and it is that which you required. Make a note of the
function name – Users.

Identify the Function Name for Responsibility Form
As you know Responsibility was coming under Security > Responsibility


You can see Responsibility is also a Sub menu. (Copy this Responsibility Sub Menu value
and saved in your text file.) Now query this

Responsibility Menu – System Administrator GUI


Same like step-1. You can see Responsibility is coming under Function field which means
that node is ended her. There are no more sub menus, and it is that which you required. Here you note function name for Responsibility form is Responsibilities.

Creating a New Menu
Navigation: System Administrator > Application > Menu
Same Menus form will open

Enter details according to you requirement. For Example,

Here you want to make a custom menu MY_ACCESS Menu

Then enter the menu entries, here you want Users and Responsibilities functions
under our menu.

You will select Function; Users and Responsibilities which was identifies in step-1 and 2.


Save your work.


Click OK.

New Custom menu has been created which will have Users and Responsibilities forms.

4. Responsibility Assignment
Now you can assign this Custom menu to some responsibility to access this menu.
 For better understanding you can create a new responsibility and can assign this
menu to that responsibility.