Tip#24 Fix for ORA-12545 on RAC

We had a case recently with a 10gR2 RAC, Some clients getting ORA-12545 errors i.e. ORA-12545 Connect failed because target host or object does not exist. TNS and Listener entries were all fine and TNSPING was also working so it had to be something else.

On digging further and googling a bit it turned out that root cause was that the client was being redirected to the server hostname instead of virtual addresses. Due to that the client had to resolve the server hostname and in some cases it could not ( e.g. no entry in their DNS & client Host file), hence the error. Here is the link where it is nicely explained Link .

Hence two thing needed to be fixed,

1) Client should have been pointed(redirected) to Virtual address and not real host names
2) Client should be able to resolve virtual address

Point 1 can be fixed by setting up LOCAL_LISTENER parameter on all RAC nodes. Here is how,

Add entry in TNSnames.ora on each node e.g. on Node 1

LISTENER_NODE1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT =1521))
)

set local listner for each node,

Alter system set LOCAL_LISTENER= 'LISTENER_NODE1' scope=both sid='SID1';

Repeat above steps on each node with respective host & tns name.

Point 2 can be fixed by either add DNS entry for the host names or if not possible, add entry in Client's host file e.g. on Linux add entry in /etc/hosts or on Windows add entry in windows\system32\drivers\etc\hosts.

Tip#23 2>&1 and Argument list too long

What does 2>&1 mean?

Sometimes I call my SQL script via shell script which generally have this kind of statement in it,

$SCRIPTDIR/run_me.sh 2>&1 > $LOGDIR/log_me.log

What above statement does for me is, it runs run_me.sh script from my script folder and logs the detail in files called log_me.log in my log folder. Over here what I am interested in is what is 2>&1. Well firstly let me define three data streams in linux i.e. STDIN, STDOUT, and STDERR,

STDIN : Standard input usually comes from the keyboard or from another program

STDOUT : The program usually prints to standard output

STDERR : The program sometimes prints to standard error

In Linux/Unix, The built-in numberings for them are 0, 1, and 2, in that order.

The command above is redirecting standard error into standard output (you have to put an & in front of the destination when you do this) and redirecting standard output into my log file, which is a place I want to dump anything my scripts writes out.

So effectively, all output from this command should be logged into my log file. So in case of any issue I can always look at the log to find out the trouble area.


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

Sometimes there will be cases where a directory is filled with lots of files e.g. dump directory with lots of trace file. In such case when I tried following,

/bin/rm *.trc

I got following error message:

bash: /bin/rm: Argument list too long

Solution:

find . -name "*.trc"| xargs /bin/rm.

Depending on the number of files, after a while all files will be erased.

Tip#22 Fix for ORA-01882

ORA-01882: timezone region %s not found

Recently we got this error while running via TOAD

select * from dba_scheduler_jobs;

The error message itself turns out not very informative.

01882, 00000, "timezone region %s not found"
// *Cause: The specified region name was not found.
// *Action: Please contact Oracle Customer Support.

In short, the error is because there are 7 timezone region IDs changed from version 3 and above. If you have old Timezone data from Version 2 that using one of these IDs the error raises.

In our case, server had time zone files were already upgraded so running same query on the DB server was working fine. So to fix it we had fix it on the client side,

1) Download patch 5731535 for a 10.2.0.X client ( 10.2.0.1 to 10.2.0.3)

2) Copy the 2 .dat files located at Client_patch\5731535\files\oracore\zoneinfo and the readme.txt file into the %ORACLE_HOME%\oracore\zoneinfo directory on your oracle client.

3) restart the TOAD


Ref : Metalink Doc ID: 414590.1 and 417893.1