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 18.104.22.168.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
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 22.214.171.124.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 126.96.36.199.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.
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).