Not able to connect to non-default oracle database after reboot

connectivityinstallationoracleoracle-11g-r2

I have installed Oracle 11g r2 on Centos. On initial setup it installed database named INFA for me.I setup listener.ora and tnsnames.ora for INFA and on reboots all I had to do was run lsnrctl start command the instance by using following command sqlplus /as sysdba and SQL>startup.Now I needed another database I created another database using dbca as that was recommended by oracle(I think I should have used sql). New database named hayer_test with SID hayertestwas installed in same directory as INFA and it was up and running after install(before reboot).Now I rebooted the OS and when I did the steps I used to do only INFA database came up.Not sure how to bring this second database up.

Ok on more investigating I found that my init.ora has all description of only one database and I have to sp files now I have no idea how I should tell oracle to use both spfiles and start the instance for both databases.So I guess when I do sql>startup it looks at spfile$ORACLE_SID.ora and as I have only set up one $ORACLE_SID it only starts one database.Please help me set up multiple SID's.

Here the error I get when I try to connect now.

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 11:28:45 2014

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

Enter user-name: system@hayer_test
Enter password: 
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

My tnsnames.ora and listener.ora file contains this:
listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

tnsname.ora:

HAYER_TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hayer_test)
    )
  )

INFA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = infa)
    )
  )

lsnrctl status command gives me following result :

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-JUN-2014 11:25:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                27-JUN-2014 11:24:34
Uptime                    0 days 0 hr. 0 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hayer/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hayer)(PORT=1521)))
Services Summary...
Service "infa" has 1 instance(s).
  Instance "infa", status READY, has 1 handler(s) for this service...
Service "infaXDB" has 1 instance(s).
  Instance "infa", status READY, has 1 handler(s) for this service...

The command completed successfully

I registered the database with listener when I installed database first time.

Ran SQL> show parameter local_list; and got this result :

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
local_listener               string

The following query was ran ps -fe|grep pmon gives following result.

oracle   21012     1  0 08:44 ?        00:00:00 ora_pmon_infa
oracle   21367 21353  0 08:46 pts/1    00:00:00 grep pmon

The configuration files found by ls -l /u01/app/oracle/product/11.2.0/db_1/dbs/*HAYER* showed following result.

-rw-r-----. 1 oracle oinstall 24 Jun 26 16:33 /u01/app/oracle/product/11.2.0/db_1/dbs/lkHAYER_TE
-rw-r-----. 1 oracle oinstall 24 Jun 26 16:33 /u01/app/oracle/product/11.2.0/db_1/dbs/lkHAYER_TEST

The command completed successfullyI am new to this and learning please help.Also if anyone can recommend most efficient way of creating databases in Oracle(dbca or sql query).

Best Answer

This means that the listener (the software that listens on the network for database connection attempts) doesn't know that your DB exists.

Normally, when you start up an Oracle 11g DB, it'll automatically "register" with the listener within 60 seconds or so (I believe it's background process PMON responsible for this). You can force a listener registration by connecting as sysdba on the local machine - e.g.:

SHELL> sqlplus / as sysdba

Then typing:

SQL> ALTER DATABASE REGISTER;

This will tell the database to register with the listener.

However - this relies on the database knowing how to talk to the listener! Because your listener is listening on port 1522, so you'll need to explicitly tell your database where to find it:

See http://docs.oracle.com/cd/E11882_01/network.112/e41945/listenercfg.htm#NETAG1154 ("Registering Information with a Local Listener")

For a real-world example, see:

http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/

As to whether you should use DBCA or the query... it's up to you. DBCA is by far the simpler, however it also hides the details away. My preference is to use DBCA to generate a set of scripts which I then peruse and alter as appropriate.

Related Question