Oracle 10G, ora-12170 and ORA-12535

[labadie_1]

Hello

I have installed Oracle on an Itanium running VMS 8.3-1H1, created a test database, started Oracle.

Since I rebooted, I am unable to start Oracle again.

I have checked all my .ora files, they are in stream_lf format.

I have put trace in sqlnet.ora, the file sqlnet.log shows


***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for VMS: Version 10.2.0.2.0 - Production
Time: 03-MAR-2010 12:22:39
Tracing to file: [oratest]cli_20806de8.trc
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 324
nt OS err code: 0
Client address:

and the end of the trace file contains

[03-MAR-2010 12:22:39:306] nsmfr: entry
[03-MAR-2010 12:22:39:306] nsmfr: 748 bytes at 0x334b860
[03-MAR-2010 12:22:39:307] nsmfr: normal exit
[03-MAR-2010 12:22:39:308] nsopen: error exit
[03-MAR-2010 12:22:39:308] nsclose: entry
[03-MAR-2010 12:22:39:309] nsclose: normal exit
[03-MAR-2010 12:22:39:309] nladget: entry
[03-MAR-2010 12:22:39:310] nladget: exit
[03-MAR-2010 12:22:39:310] nsmfr: entry
[03-MAR-2010 12:22:39:311] nsmfr: 212 bytes at 0x3332290
[03-MAR-2010 12:22:39:312] nsmfr: normal exit
[03-MAR-2010 12:22:39:312] nladtrm: entry
[03-MAR-2010 12:22:39:313] nladtrm: exit
[03-MAR-2010 12:22:39:313] nscall: error exit
[03-MAR-2010 12:22:39:314] nioqper: error from nscall
[03-MAR-2010 12:22:39:315] nioqper: ns main err code: 12535
[03-MAR-2010 12:22:39:315] nioqper: ns (2) err code: 12560
[03-MAR-2010 12:22:39:316] nioqper: nt main err code: 505
[03-MAR-2010 12:22:39:316] nioqper: nt (2) err code: 324
[03-MAR-2010 12:22:39:317] nioqper: nt OS err code: 0
[03-MAR-2010 12:22:39:317] niomapnserror: entry
[03-MAR-2010 12:22:39:318] niqme: entry
[03-MAR-2010 12:22:39:318] niqme: reporting NS-12535 error as ORA-12535
[03-MAR-2010 12:22:39:319] niqme: exit
[03-MAR-2010 12:22:39:320] niomapnserror: exit
[03-MAR-2010 12:22:39:320] niotns: Couldn't connect, returning 12170
[03-MAR-2010 12:22:39:321] nioqer: entry
[03-MAR-2010 12:22:39:321] nioqer: incoming err = 12170
[03-MAR-2010 12:22:39:340] nioqce: entry
[03-MAR-2010 12:22:39:341] nioqce: exit
[03-MAR-2010 12:22:39:341] nioqer: returning err = 3113
[03-MAR-2010 12:22:39:342] nioqer: exit
[03-MAR-2010 12:22:39:343] niotns: exit
[03-MAR-2010 12:22:39:343] nigcui: entry
[03-MAR-2010 12:22:39:344] nigcui: Clr User Interrupt: hdl=1, prc=0x3109278, ctx=0x330dcc0.
[03-MAR-2010 12:22:39:344] nigcui: exit (0)
[03-MAR-2010 12:22:39:345] nsbfrfl: entry
[03-MAR-2010 12:22:39:346] nsbrfr: entry
[03-MAR-2010 12:22:39:346] nsbrfr: nsbfs at 0x33258e0, data at 0x334bc50.
[03-MAR-2010 12:22:39:347] nsbrfr: normal exit
[03-MAR-2010 12:22:39:347] nsbfrfl: normal exit
[03-MAR-2010 12:22:39:348] nigtrm: Count in the NI global area is now 1
[03-MAR-2010 12:22:39:349] nigtrm: Count in the NL global area is now 3
$


sqlplus
user
password

gives immediately

$ sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Mar 3 12:31:39 2010

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter user-name: sys
Enter password:
ERROR:
ORA-12170: TNS:Connect timeout occurred


Enter user-name:

Thanks for any hint

[Fekko Stubbe]

[03-MAR-2010 12:22:39:316] nioqper: nt (2) err code: 324

Could it mean vms error 324
exit 324: system-f-invdevnam,invalid device name

Maybe a logical disappeared after the boot??

Fekko

[Hein van den Heuvel]

Is there a BEQ Listener runnings?

Did you run @Oracle_home:INSORACLE or run @ORAUSER which wil invoke ORAINSTAL if you have the privs and ORA_AUTO_ORAINSTALL is defined as yes/

Is the Bequet Listener going?

Cheers,
Hein

[labadie_1]

Hein

>>>Is there a BEQ Listener runnings?
yes


>>>Did you run @Oracle_home:INSORACLE or run @ORAUSER

Yes

I am amazed it is so difficult to find information about this problem. I have found metalink notes with

nioqper: ns main err code: 12535
[03-MAR-2010 12:22:39:315] nioqper: ns (2) err code: 12560
[03-MAR-2010 12:22:39:316] nioqper: nt main err code: 505
[03-MAR-2010 12:22:39:316] nioqper: nt (2)

but not with my

err code: 324
[03-MAR-2010 12:22:39:317] nioqper: nt OS err code: 0

And no firewall blocking port 1521.

The user was clearly not tested, as entering a valid user/password couple, or a clearly invalid, let to the same result, immediate answer

ORA-12170: TNS:Connect timeout occurred

So I think it is not a timeout problem.
I have found notes with a logical name for timeout problems, but it is not relevant.

I relaunched OUI, created another test database, and it worked fine.

[Hein van den Heuvel]

>> I relaunched OUI, created another test database, and it worked fine


Ah that may have been the problem.
You are supposed to install twice.
There is always something that goes wrong. :-)

More seriously... have you rebooted since that last succesful install?

Of course you and I know that it is not the reboot, but an environmental thing like the right ORA table in LNM$FILE_DEV. THat's where the (right) BEQL comes in.

Enjoy,
Hein.

[labadie_1]

Yes, I have rebooted since the last successful install, and Oracle started correctly.

I had put a procedure starting Oracle in systartup_vms (this customer does not use SYSMAN), and was wondering how I could run a procedure shutting Oracle in syshutdwn.com, as this is supposed to run under a Oracle user ?

A submit/user=oracle will not work, as shutdown.com begins with stopping the queue manager.

[Hein van den Heuvel]

The oracle shutdown command is just an other Oracle (SQLplus) command, from an Oracle user with the right Oracle privs.

The OpenVMS username is really not relevant directly, other than it having been associated (external authentication) with a specific Oracle account or it having the password.

Whether the OpenVMS username is Oracle, OracleDBA, system or john_doe does not matter really, but _often_ accounts like oracledba are setup not to require an Oracle password making that convenient.

You could set that up for 'system' but most folks don't like that prefering to (pretend) to keep a separation between SA and DBA.

fwiw,
Hein.

[Zeni B. Schleter]

I am not the oracle support person but I remember a couple of problems we had around the time of adding Oracle 10G. One was that the sym PATH was defined in system-wide login and that caused a problem. The other one that related directly to TNS had to do with a logical that had to be added to the process table for oracle. That sounds similar to your problem.
It has been a while but all I really remember is that there was a logical (or logical table) that changed with reboots but it had to be added to logical table where it could be found by spawned jobs.
There should be a TNS_'pid logical where pid is the process id of the listener process. There should be a Server_%%%%%%%%%% table that is added to the LNM$FILE_DEV list. I have looked through startup procedures but have not found where the fix was applied and noted as such.
Sorry I can't be more specific.

[labadie_1]

Thanks Zeni for this info

Do you have a pointer on how to interpret the trace when I add
trace_level_client=support
trace_level_server=support
in sqlnet.ora ?

regards

[Zeni B. Schleter]

Sorry, I have no clue about how to read the trace. The only reason I knew about the logicals was because it was more of an OpenVMS specific issue that required DCL modification to fix.

[Dima Bessonov]

labadie, you're not giving much details on how you attempt to start Oracle 10.2

From my rather limited knowledge,


1) There is no single do-it-all startup procedure for Oracle 10.2. So you have to: a) install resident images; b) start the listener process via SRVCTL; c) bring your database instances up, one-by-one, also via SRVCTL. I assume you're running a plain vanilla Oracle 10.2, not a Real Application Cluster database. Otherwise, the entire startup procedure would be quite different.

2) Startup MUST be done from Oracle dedicated account so you can't run it directly from SYSTARTUP_VMS.COM, like you used to do with other products. Either do SUBMIT/USER=my_oracle_account or run it manually from that account.

3) To install resident images, you must either run ORAUSER and then INSORACLE, or first do DEFINE ORA_AUTO_INSORACLE TRUE and then do ORAUSER. From 10.2 on, that's how it's done.

4) When you have problems with Oracle 10.2 installation on OpenVMS, a good starting place for reading is Oracle note 726914.1, "Index of install related notes for Oracle 10.2 on hp OpenVMS". Make sure that you've read and followed the notes referenced there. You'll find a lot of seemingly minor but vitally important details. Takes time but saves you from a good deal of troubles...

5) BEFORE deploying any database instance, upgrade from 10.2.0.2 to 10.2.0.4 and apply the latest (10.2.0.4.3) patchset. Otherwise, you'll still have to do the upgrades AND manually run SQL scripts against each instance to recompile database views and objects after each upgrade. Without that, the sun will not rise ;)

[labadie_1]

Dima

>>> 2) Startup MUST be done from Oracle dedicated account so you can't run it directly from SYSTARTUP_VMS.COM, like you used to do with other products. Either do SUBMIT/USER=my_oracle_account or run it manually from that account.

I do a submit/user=oracle_account

>>>3) To install resident images, you must either run ORAUSER and then INSORACLE,

Yes I do


>>>4) When you have problems with Oracle 10.2 installation on OpenVMS, a good starting place for reading is Oracle note 726914.1, "Index of install related notes for Oracle 10.2 on hp OpenVMS".

Yes I had downloaded and read before the install

>>>5) BEFORE deploying any database instance, upgrade from 10.2.0.2 to 10.2.0.4 and apply the latest (10.2.0.4.3) patchset.

Not possible here, the customer wanted to stick on this version (10.2.0.2).

[Dima Bessonov]

labadie, you wrote:

>>>>>>5) BEFORE deploying any database instance, upgrade from 10.2.0.2 to 10.2.0.4 and apply the latest (10.2.0.4.3) patchset.

>>>Not possible here, the customer wanted to stick on this version (10.2.0.2).


Your customer should understand they're heading for big trouble and no one would be able to help them. Version 10.2.0.2 is NOT SUPPORTED. Oracle does not release fixes and CPUs (security updates) for unsupported versions. They must upgrade to 10.2.0.4 to be eligible for support. I really can't see any reason why they want to stick to 10.2.0.2 - to my (obviously very limited) knowledge, the difference is only bug fixes.

In any case, on VMS V8.3-1H1 I64, one must apply several mandatory patches for OpenVMS (see note 377480.1) and Oracle patches for 10.2.0.2 (see note 391580.1, for 10.2.0.4 see note 751537.1). Have you applied any of them yet? If yes, which ones? Did you try to open a support case on Metalink?

[labadie_1]

The customer has his dev box with this version.

Yes I had applied the patches you cited.

No I have not opened a case.

I do not see the advantage of having a trace file if it is not possible the interpret it.

[Dima Bessonov]

>>> I do not see the advantage of having a trace file if it is not possible the interpret it.

Frankly I'm more of the opinion that the trace files may be of _some_ use when one opens a TAR and is then asked by Oracle support to collect all that stuff... There are lots of Oracle problems out there that don't show up in the trace files. Imho, if you don't see there anything that gives you an immediate clue, don't waste your valuable time and open a TAR.

PS Regarding Oracle logicals and LNM$FILE_DEV. LNM$FILE_DEV is normally modified from within ORAUSER.COM. Every Oracle home has its own logical name table named SERVER_xxxxxxxxxx . When you run an Oracle command, it needs to know, what home should be affected by this command. To choose the appropriate home, the command looks up LNM$FILE_DEV and finds the name of corresponding logical name table. So every Oracle home has its own ORAUSER.COM and when you run a specific ORAUSER.COM, it redefines LNM$FILE_DEV appropriately. I'm not sure that'll help you much but who knows...

[labadie_1]

Thanks for the valuable input.

Fekko Stube may be right with the invalid device name, but I would like to be sure this is a correct use of this 324...

[labadie_1]

Fekko Stube was right!

I have found on
http://hpdba.wordpress.com/tag/keep-alive/

that the secondary error code is OS dependant

Extract

nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=nn.nn.nn.nn)(PORT=57922))

In this example, the Solaris error 145 shows that the disconnection was not due to an Oracle setting or database problem, (as some parties were insisting), but was actually from a connection timeout at the OS level. (Look up error number 145 in /usr/include/sys/errno.h ).

and if I do on a Solaris

grep 145 /usr/include/sys/errno.h
#define ETIMEDOUT 145 /* Connection timed out */

So the problem was an invalid device name, I suppose some mismatch in the ora* logical names, and the redefinitions of lnm$file_dev

[Dima Bessonov]

So, labadie, what was wrong in your VMS installation? I don't quite get it so far but am curious to learn.

[labadie_1]

Dima

If I only knew...

[Zeni B. Schleter]

Looking through old mail during the time when we migrated from 8i to 10g, there were problems relating to ORACLE_HOME definition and PARSE_STYLE setting. I remember that the fixes were simple but finding the source of the problems was not.

Since we did not use extended parse style setting up to that point, we stumbled over that setting more than once.