1

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).

Improve this question
8
  • On your second question, I would say to use the DBCA until you get more experienced and comfortable with the command line.
    – Andrew Brennan
    Commented Jun 26, 2014 at 16:01
  • Set the environment for your other database using $. oraenv (note the dot) and fire up the thing.
    – Bjarte Brandt
    Commented Jun 27, 2014 at 5:39
  • The error message of sqlplus says that the listnener doew not know a service HAYER_TEST. If you ask the listener directly using lsnrctl you can see that the listner does not know a service HAYER_TEST. 1)Do you have an instance HAYERTEST with a service called HAYER_TEST. 2)Why does the definition of tnsname HAYER_TEST contain a service_name and a sid? 3) can you show us the output of the following query: select 'Service' "Type", NETWORK_NAME "Name" from dba_services where NETWORK_NAME is not null union all select 'Instance',sys_context('USERENV','INSTANCE_NAME') from dual ;
    – miracle173
    Commented Jun 27, 2014 at 9:03
  • sorry, I see now that you wrote you cannot start your database. so my previous tests are meaningless because you already have not database named HAYERTEST.
    – miracle173
    Commented Jun 27, 2014 at 9:05
  • @miracle173 I have reinstalled oracle 11g setup listener on 1521 port then create second database was connected when created but after reboot same thing.Also when I installed I set it up as single instance database not sure if that makes any difference
    – Auguster
    Commented Jun 27, 2014 at 15:33

2 Answers 2

Reset to default
1

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.

Improve this answer
1
  • Summer I have already registered the database when I installed database first time.I have edited question accordingly.
    – Auguster
    Commented Jun 26, 2014 at 17:01
0

Looking at your tnsnames.ora, the output from lsnrctl status and the command you're entering, there is a mismatch:

You are trying to connect using system@hayer_test

Your tnsnames.ora is set with this for the alias hayer_test:

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

Yet there is nothing in the output of your lsnrctl status to indicate that that the listener knows about a database with SID hayertest or having a service name called hayer_test.

You should be able to connect using the command sqlplus system@orcl. Your listener knows about database orcl, and your tnsnames.ora has an alias to connect to a database called orcl.

Improve this answer
2
  • @PhilSummer Connecting to ORCL is not a problem how do I connect to other database I created and if other database is down how do I bring it up
    – Auguster
    Commented Jun 27, 2014 at 15:22
  • I have reinstalled oracle 11g setup listener on 1521 port then create second database was connected when created but after reboot same thing.
    – Auguster
    Commented Jun 27, 2014 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.