Solved

ora-12514 - A 2nd DB will not register

Posted on 2006-07-09
30
854 Views
Last Modified: 2010-08-05
Hi Experts

I need help to get my 2nd DB up and running again and be able to connect to it.
I have one server with 2 10g DBs. Both are under the same Oracle Home. I recently patched my first DB up to release 10.2.0.2.0. This has the SID=ora10gdb1

Only a week ago I configured my 2nd DB using the dbca. It came up OK and seemed fine. I could access it through sqlplus and it showed up in EM. I then performed a shutdown immediate in order to perform a catalog upgrade, and since then I have not been able to connect to it again. It has the SID=Geneva.
All I get is the following error:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor

I have tried to register the database service through netmgr util. I have tried to manually register the service by entering the 2nd DB to listener.ora:
SID_LIST_LISTNER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/10.2.0/ora10g_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = Geneva)
      (ORACLE_HOME = /app/oracle/product/10.2.0/ora10g_1)
      (SID_NAME = Geneva)
    )
  )

As you can see, my 1st DB is not registered here, but registers automatically.
lsnrctl services
LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 09-JUL-2006 19:27:54

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "ora10gdb1" has 1 instance(s).
  Instance "ora10gdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER
Service "ora10gdb1XDB" has 1 instance(s).
  Instance "ora10gdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oracle.anonymous.com, pid: 32239>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle.anonymous.com)(PORT=59203))
Service "ora10gdb1_XPT" has 1 instance(s).
  Instance "ora10gdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER

Tnsnames exists for both instances:
ORA10GDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.anonymous.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORA10GDB1)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

GENEVA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.anonymous.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = GENEVA)
    )
  )


I don't seem to be able to get the listener to find my 2nd DB so I would appreciate any help I could get.

Regards
KMS
0
Comment
Question by:KjartanM-S
  • 13
  • 8
  • 6
  • +1
30 Comments
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Try typing
lsnrctl services
and check if your listener knows the second sid.

Also check your listener.log  file for any indications
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Also try doing ALTER SYSTEM REGISTER;

Check this post
http://forums.oracle.com/forums/thread.jspa?threadID=402117&tstart=0
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 250 total points
Comment Utility
what you need to is:

export ORACLE_SID=Geneva

sqlplus /nolog

SQL> connect  ... as sysdba

SQL> startup


then you wait a second to see if you can connect to Geneva instance.
0
 

Author Comment

by:KjartanM-S
Comment Utility
Hi actonwang

I tried the following and now I didn't get the TNS error but the following
[oracle@oracle ~]$ export ORACLE_SID=GENEVA
[oracle@oracle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jul 10 10:10:45 2006

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

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/app/oracle/product/10.2.0/ora10g_1/dbs/initGENEVA.ora'
SQL>

I also tried to alter system register, but this will not happen as long as the DB is shutdown.
Why is it that sqlplus suddenly can connect with the /nolog option?

Regards
KMS
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 250 total points
Comment Utility
Check whether the file exists

$ cat /app/oracle/product/10.2.0/ora10g_1/dbs/initGENEVA.ora

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup pfile='/app/oracle/product/10.2.0/ora10g_1/dbs/initGENEVA.ora'
SQL>
0
 

Author Comment

by:KjartanM-S
Comment Utility
MohanKNair

The file does not exists:
[oracle@oracle dbs]$ ls -la
drwxr-x---   2 oracle oracle    4096 Jul  9 17:22 .
drwxr-x---  61 oracle oracle    4096 Jul  4 15:39 ..
-rw-rw----   1 oracle oinstall  1552 Jul  3 15:13 hc_geneva.dat
-rw-rw----   1 oracle oinstall  1552 Jul  4 14:59 hc_Geneva.dat
-rw-rw----   1 oracle oracle    1552 May 16 09:28 hc_ora10gdb1.dat
-rw-r-----   1 oracle oracle   12920 May  3  2001 initdw.ora
-rw-r-----   1 oracle oracle    8385 Sep 11  1998 init.ora
-rw-rw----   1 oracle oinstall    24 Jul  3 15:14 lkGENEVA
-rw-r-----   1 oracle oracle      24 May  9 10:35 lkORA10GDB
-rw-r-----   1 oracle oracle      24 May  9 10:37 lkORA10GDB1
-rw-r-----   1 oracle oinstall  1536 Jul  4 15:39 orapwGeneva
-rw-r-----   1 oracle oracle    1536 Jun  7 12:23 orapwora10gdb1
-rw-r-----   1 oracle oinstall  3584 Jul  4 15:39 spfileGeneva.ora
-rw-r-----   1 oracle oracle    3584 Jul  9 16:35 spfileora10gdb1.ora
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
spfile is available
-rw-r-----   1 oracle oinstall  3584 Jul  4 15:39 spfileGeneva.ora

Startup using spfile
0
 

Author Comment

by:KjartanM-S
Comment Utility
Unfortunately that did not work either:
SQL> startup spfile=/app/oracle/product/10.2.0/ora10g_1/dbs/spfileGeneva.ora
SP2-0714: invalid combination of STARTUP options
SQL>
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
Create pfile from spfile and startup the database using pfile

SQL> CREATE PFILE = 'my_init.ora' FROM SPFILE = 's_params.ora';
SQL>startup pfile='my_init.ora'
SQL>
0
 

Author Comment

by:KjartanM-S
Comment Utility
Ok, after reading those two articles you posted I did the following:

Found the pfile:
[oracle@oracle pfile]$ pwd
/app/oracle/admin/geneva/pfile
[oracle@oracle pfile]$ ls -la
total 24
drwxr-x---  2 oracle oinstall 4096 Jul 10 12:00 .
drwxr-x---  8 oracle oinstall 4096 Jul 10 11:54 ..
-rw-r-----  1 oracle oinstall 2725 Jul 10 11:58 init.ora.610200612019

and tried a startup as suggested:
SQL> startup pfile=/app/oracle/admin/geneva/pfile/init.ora.610200612019
ORACLE instance started.

Total System Global Area 1090519040 bytes
Fixed Size                  2070520 bytes
Variable Size             301991944 bytes
Database Buffers          771751936 bytes
Redo Buffers               14704640 bytes
Database mounted.
Database opened.
SQL>

I have not created a spfile from the pfile yet, but if I do this should I perhaps name it to be initGeneva.ora since this was what it was looking for?
Is there a difference in the pfile and spfile since the one worked and the other didn't?
MohanKNair - I didn't read your last posting until I had done this, but I would assume that it would be safer to create the spfile from the pfile rather than opposite since it actually started from the pfile and not from the spfile, or is it just that they differ that is the problem?
Also where should I create the spfile?
I tried the following show parameter:
SQL> show parameter spfile;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
KjartanM-S,

Yes, it would be safer to create the spfile from the pfile.
SQL> CREATE SPFILE = 'my_init.ora' FROM PFILE = 's_params.ora';
SQL> show parameter spfile;

Mohan
0
 

Author Comment

by:KjartanM-S
Comment Utility
Thanks Mohan

SQL> show parameter spfile;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string
/app/oracle/product/10.2.0/ora
10g_1/dbs/spfileGENEVA.ora
SQL>


The database now starts up normally, however sqlplus still gives me the original TNS error when the db is shutdown. It does not do this with my first db I installed. Any ideas?
Last but not least - after I now restarted the DB the EM for this db now refuses to login my users - complains on wrong user/password. Although the sys user and password works fine in sqlplus?
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
Check the service name in listener.ora. Check the global name of DB.

SQL> select * from global_name;
SQL>
0
 

Author Comment

by:KjartanM-S
Comment Utility
# listener.ora Network Configuration File: /app/oracle/product/10.2.0/ora10g_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTNER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/10.2.0/ora10g_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = Geneva)
      (ORACLE_HOME = /app/oracle/product/10.2.0/ora10g_1)
      (SID_NAME = Geneva)
    )
  )

LISTNER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.annonymous.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

and
SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
GENEVA

Tnsnames.ora has this entry
GENEVA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.ats.accenture.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = geneva)
    )
  )


Could it be that any of this is case sensitive?

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 16

Expert Comment

by:MohanKNair
Comment Utility
See if listener is up and running

$ lsnrctl reload
$ lsnrctl status
0
 

Author Comment

by:KjartanM-S
Comment Utility
The listner is up. I'm able to connect via sqlplus, just not to login to EM. It complains on wrong username and/or password.
Both DBs are registered with the listener.

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 10-JUL-2006 14:43:51

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date                09-JUL-2006 19:15:07
Uptime                    0 days 19 hr. 28 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/10.2.0/ora10g_1/network/admin/listener.ora
Listener Log File         /app/oracle/product/10.2.0/ora10g_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.annonymous.com)(PORT=1521)))
Services Summary...
Service "GENEVA" has 1 instance(s).
  Instance "GENEVA", status READY, has 1 handler(s) for this service...
Service "genevaXDB" has 1 instance(s).
  Instance "GENEVA", status READY, has 1 handler(s) for this service...
Service "geneva_XPT" has 1 instance(s).
  Instance "GENEVA", status READY, has 1 handler(s) for this service...
Service "ora10gdb1" has 1 instance(s).
  Instance "ora10gdb1", status READY, has 1 handler(s) for this service...
Service "ora10gdb1XDB" has 1 instance(s).
  Instance "ora10gdb1", status READY, has 1 handler(s) for this service...
Service "ora10gdb1_XPT" has 1 instance(s).
  Instance "ora10gdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
do you have any special characters in your password like @ symbol?
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
Check tnsnames.ora file. tnsping the TNS service name.
0
 

Author Comment

by:KjartanM-S
Comment Utility
I do not use any special characters in the password - not even numeric chars.

The tnsnames.ora is posted above for the geneva instance.
The tnsping seems to work fine - I tried both lower- and upper case SID:
[oracle@oracle ~]$ tnsping geneva

TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 10-JUL-2006 15:36:23

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.annonymous.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = geneva)))
OK (0 msec)
[oracle@oracle ~]$ tnsping GENEVA

TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 10-JUL-2006 15:36:33

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.annonymous.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = geneva)))
OK (0 msec)
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
sorry for late reply, it seems that after setting "ORACLE_SID" you are able to bring up the instance. The problem for you is that you didn't bring up the instance so SID could not be found.

>> The listner is up. I'm able to connect via sqlplus, just not to login to EM.
      in EM, try to recreate the node to GENEVA and log in again.
0
 

Author Comment

by:KjartanM-S
Comment Utility
Ok, guys - thanks a lot for your help!

It turned out that I exported the ORACLE_SID to be the wrong sid...
I typed GENEVA rather than geneva and started up the database. This did seem to start the database - at least I could perform selects from sqlplus but EM could not login with my users.
Solution being to shutdown the instance - export the SID to its correct value (also verified that in tnsnames it was SID and not service_name that was entered) and then restarted the instance. It now came up without problems, and I was able to login to EM.

One small question though, if you don't mind - EM does not know that my listener is running. It says it is down, though if I go to my other instance's EM it lists the Listener as up. Also lsnrctl status shows it is fine with both DBs registered.

Regards
KMS
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>EM does not know that my listener is running

    try to restart your EM or recreate the node in EM using tns name.
0
 

Author Comment

by:KjartanM-S
Comment Utility
Tried to restart the EM but without luck.

Not sure what you mean by recreate the node in EM - How?

Regards
KMS
0
 

Author Comment

by:KjartanM-S
Comment Utility
This is the comment that is shown in EM if I click on the last know status link:
The listener is down: listener "LISTNER" is not running using "/app/oracle/product/10.2.0/ora10g_1/network/admin/listener.ora" file..
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>you mean by recreate the node in EM
   
    the node in the EM console you use to point to geneva instance.
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
delete it and recreate it.
0
 

Author Comment

by:KjartanM-S
Comment Utility
I don't seem to be able to...

I try to click on its link then edit it (or start it) and it asks for host credentials (net services administration)- and I am not able to login:
Validation Error - Failed to read stderr from process
is shown in the EM
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
i am not on oracle 10 and only on 9, maybe there is difference.

possible to create a new node to point to your new instance.
0
 

Author Comment

by:KjartanM-S
Comment Utility
Ok, well it is only a minor thing - I know the listener is up anyway.

Thanks a lot for your help!

Regards
KMS
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

Suggested Solutions

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

17 Experts available now in Live!

Get 1:1 Help Now