Solved

ORA-12560 when using the Database Upgrade Assistant

Posted on 2004-08-19
33
1,545 Views
Last Modified: 2009-07-29
I'm trying to upgrade my Oracle database from 8.1.7.3.2 to 9.2.0.5.2.

The new database software has been installed in a new Oracle home (D:\Oracle\TST\920).
Now, I want to start the upgrade process using the Database Upgrade Assistant. When I start this assistant, I have to choose the database to upgrade. It shows my "old" database "8.1.7 - D:\Oracle\TST\817". I select this database and choose next. The program says it's "getting database information", and then immediatley gives an "ORA-12560: TNS: protocol adapter error".

The listener for 920 has been started. "lsnrctl status" is succesfull, as well as "tnsping TST". The database is up and running and I can connect to it using sqlplus (I performed a select on a database table to check wether the connection really worked).

What is going on here and how can I resume my upgrade project?
0
Comment
Question by:Repeat64
  • 13
  • 11
  • 7
  • +2
33 Comments
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11838886
0
 

Author Comment

by:Repeat64
ID: 11838899
I already saw that comment. Orcale_Home has been removed from the user's environment, as stated in my upgrade manual (SAP).
That Upgrade Assistant recognizes the "old" database, since it's listed in the list of available databases (it's actually the only entry in this list).
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11839166
hi

check the link below.

http://www.experts-exchange.com/Databases/Oracle/Q_20866347.html

regards
annamalai
0
 

Author Comment

by:Repeat64
ID: 11839175
I found that thread also. The last comment has actually been logged by me.

There is no resultion posted in that message. I tried all the other suggestions in that thread.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11840375
@repeat:

can you post the following results from your DOS window?

C:\>tnsping -v


C:\>lsnrctl -v


C:\>set | findstr  ora


0
 

Author Comment

by:Repeat64
ID: 11841079
@seazodiak:

I executed "tnsping <SID>" and "lsnrctl status". Both "-v" flags are unknown (it's a Windows system, not UNIX).

***************************************
tnsping TST:

D:\Oracle\TST\920\bin>tnsping TST

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 19-AUG-2004 15:26:48

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files: D:\Oracle\TST\920\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = s31000039)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TST)))
OK (30 msec)

***************************************
lsnrctl status:

D:\Oracle\TST\920\bin>lsnrctl status

LSNRCTL for 32-bit Windows: Version 9.2.0.5.0 - Production on 19-AUG-2004 15:27:30

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s31000039)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.5.0 - Production
Start Date                19-AUG-2004 13:31:12
Uptime                    0 days 1 hr. 56 min. 18 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   D:\Oracle\TST\920\network\admin\listener.ora
Listener Log File         D:\Oracle\TST\920\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s31000039.ct.group.local)(PORT=1521)
))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "TST" has 1 instance(s).
  Instance "tst", status READY, has 1 handler(s) for this service...
The command completed successfully

***************************************

Environment:

DBMS_TYPE=ORA
DBS_ORA__SCHEMA=SAPR3
dbs_ora_tnsname=TST
ORACLE_SID=TST
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841146
ok, sounds good.

Can you also take a look at TNSNAMES.ORA file under D:\Oracle\TST\920\network\admin\ ?

Make sure it contains TST entry there, otherwise, you have to manually add it there.


then you should be able to run DBUA
0
 

Author Comment

by:Repeat64
ID: 11841181
My TNSNAMES.ORA looks like this:

# TNSNAMES.ORA Network Configuration File: D:\Oracle\TST\920\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

TST.CT.GROUP.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = s31000039)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TST)
    )
  )

EXTPROC_CONNECTION_DATA.CT.GROUP.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841256
sorry, can you also post up your SQLNET.ORA file in the same folder?



also show the result of the command below:

D:\Oracle\TST\920\bin>tnsping TST.CT.GROUP.LOCAL
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841308
Just one more thing, Make sure oracle8i database is up and running.

you can do this by using sqlplus log in .
0
 

Author Comment

by:Repeat64
ID: 11841310
SQLNET.ORA:

# SQLNET.ORA Network Configuration File: D:\Oracle\TST\817\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

#NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DEFAULT_DOMAIN = ct.group.local

SQLNET.AUTHENTICATION_SERVICES = (NTS)

AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
SQLNET.EXPIRE_TIME = 0
NAME.DEFAULT_ZONE = WORLD

#### Attributes from protocol.ora ####

tcp.nodelay = true

*************************************

Results of the TNSPING:

D:\Oracle\TST\920\bin>tnsping TST.ct.group.local

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 19-AUG-2004 15:53:49

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
D:\Oracle\TST\920\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = s31000039)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TST)))
OK (30 msec)
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841347
ok, this should solve you problem.


YOu need to get rid of ".CT.GROUP.LOCAL " on TST entry in TNSNAMES.ORA

so change from :


TST.CT.GROUP.LOCAL =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = s31000039)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = TST)
   )
 )



to

TST=
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = s31000039)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = TST)
   )
 )

0
 

Author Comment

by:Repeat64
ID: 11841398
I changed the entry in tnsnames.ora and restarted the listener.
After that, the ODUA still gives the ORA-12560 error.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11841399
In your sqlnet.ora I noticed that it says
D:\Oracle\TST\817\NETWORK\ADMIN\sqlnet.ora

whereas I would expect it to be
D:\Oracle\TST\920\network\admin\sqlnet.ora

Can you check and post sqlnet.ora from D:\Oracle\TST\920\network\admin\sqlnet.ora

Also, when you log on to sqlplus, what is the banner displayed to you ?
0
 

Author Comment

by:Repeat64
ID: 11841505
The reference to my old oracle home in the SQLNET.ORA is because this file was copied from the old location to the new.
It's a commented line, so it shouldn't matter.

When I start SQLPlus, The banner reads:

"SQL*Plus: Release 9.2.0.5.0 - Production on Thu Aug 19 16:09:54 2004"

The command "connect / as sysdba" now results in "ORA-12514: TNS: Listener could not resolve SERVICE_NAME given in connect descriptor", where this connection was possible earlier today.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841527
this is one of solutions appearing in metalink:



i deleted the oracle_home environment variable and rebooted my windows server.
i could then connect. however, if i connected to the database using sqlplus 8.1.7 just prior to starting the dbua, i would get a 12560! connecting via the 9.2 sqlplus and then quitting solves the problem!?!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:pratikroy
ID: 11841538
In your SQLNET.ora you have NAMES.DEFAULT_DOMAIN = ct.group.local
so, you should not remove ".CT.GROUP.LOCAL" from tnsnames.ora.

0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11841548
from the command prompt, can you post the path ?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 450 total points
ID: 11841574
Repeats:

ok, it's getting a bit confusing now. Let's pay a bit more attention to which dir we are working right now.

YOu should work strictly in Oracle9i directory from now on, Never in Oracle8i directory.


in SQLNET.ORA, try to comment out this line ( for a test for upgrade)


NAMES.DEFAULT_DOMAIN = ct.group.local


then use SQLPLUS 9205 to connect Oracle8i TST database...

see if you can use Sqlplus to connect
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11841578
and the classpath (from environment variables)
0
 

Author Comment

by:Repeat64
ID: 11841632

@ seazodiak:

After I commented out the line you sugested, I get a "ORA-12154: TNS: Could not resolve service name" when connecting.

When I activate the line again, I'm able to connect to the database.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11841671
@@Repeat64 ::

from the command prompt, can you post the PATH ? and the classpath (from environment variables) ??
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841675
ok, anyway, once you can use sqlplus to connect TST,

you should be able to run DBUA to upgrade
0
 

Author Comment

by:Repeat64
ID: 11841742

@ pratikroy:

PATH=C:\Program Files\Perl\bin\;D:\Oracle\TST\920\jre\1.4.2\bin\client;D:\Oracle\TST\920\jre\1.4.2\bin;D:\Oracle\TST\920\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;D:\Oracle\TST\817\bin;C:\Program Files\Oracle\jre\1.1.7bin;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Progra
m Files\Support Tools\;D:\Program Files\nsr\bin;d:\oracle\tst\920\opatch;;H:\usr\sap\TST\sys\exe\run;D:\Oracle\TST\817\bin

I can't seem to find any reference to classpath

@seazodiak:

While I'm connected to the database using sqlplus 9205 but the ODUA still reports the ORA-12560.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841769
try to reboot machine.

and start DBUA that way.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11841834
If you check the System Properties (You may find the 'system' icon on control panel - you'll have to double click this icon or open it to see the system properties), you would find 'Environment Variables' Button on the 'Advanced' Tab. In the environment variables, you would find classpath as one of the system variables. If you double click on it, you would be able top copy and paste the value from the edit box there.

Anyways, have you tried rebooting your machine ?
0
 

Author Comment

by:Repeat64
ID: 11841890

1) I shutdown the database using sqlplus 9205
2) I rebooted the server
3) I started svrmgrl and started up the database
4) I connected to the database using sqlplus 9205
5) I started the ODUA and selected the old Oracle home
6) ORA-12560 occurred
0
 

Author Comment

by:Repeat64
ID: 11841908

@pratikroy:
There is no environment variable called classpath either in the user's environment or in the system's environment.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11841917
3) I started svrmgrl and started up the database

this could cause the problem.


In the service control , you should make the OracleServiceTST automatically start..


YOu should not use any 817 tool after restart.
0
 

Author Comment

by:Repeat64
ID: 11841977

Huh? The OracleServiceTST is set to automatic, but this does not start my database automatically.

I'm unable to start the database using the 9205 tools provided.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11842066
try this:

C:\>Set ORACLE_HOME=<to oracle8i HOME>
C:\>set ORACLE_SID=TST

C:\>D:\Oracle\TST\920\BIN\dbua
0
 

Author Comment

by:Repeat64
ID: 11842609

This, you are not going to believe: The problem was solved by installing VNC on the server and by using that tool to connect to the server instead of using a Remote Desktop connection. I didn't even mention the fact thta we were using RDC since I thought it to be obvious or not important.

Thanks for the input you all have given me over the past hours.
0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 50 total points
ID: 11842649
Glad your problem is resolved !!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now