Oracle multiple databases only able to access one database

Question

I am new to oracle. I've installed oracle 12c(required to be this specific version) in my windows pc with no problem. I created one database named LOCAL using Database Configuration Assistant. Then i created one more database named SIAKDB with same method.

I tried accessing both db using sqlplus system/12qwasZX@localhost/local for the LOCAL db, and sqlplus system/12qwasZX@localhost/siakdb for the SIAKDB db. I can connect to either db without problem. And i also imported dmp files to both db just fine.

However, problem arises when i restarted my pc. I can only connect to SIAKDB. When i try to connect to LOCAL db, i got this error

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have checked that both database are running in Services.msc. enter image description here

My tnsnames.ora file are as follows:

# tnsnames.ora Network Configuration File: D:\deployed\oracle\product12円.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
SIAKDB =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = siakdb)
 )
 )
LISTENER_SIAKDB =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
LOCAL =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = local)
 )
 )
LISTENER_LOCAL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 
LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 (CONNECT_DATA =
 (SID = CLRExtProc)
 (PRESENTATION = RO)
 )
 )
ORCL =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = orcl)
 )
 )

I've noticed that i created a database named ORCL when installing oracle, tried to access that database but got same error.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Somehow i only managed to access the latest DB i've made. How can i access the SIAKDB? And why does it works first time but not working after i restarted my PC?

Thanks

Edit : running tnsping local i got this:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = local)))
OK (20 msec)

Answer 1

Database Windows Service Status "Running" does not necessarily mean the database is open and available. The database can be in all states "down|nomount|mount|open" while the Windows Service says "Running".

- C:\>lsnrctl status # will tell if the database service has been exposed/made available.

You should validate the state of each database by:

set ORACLE_SID=<SID>
sqlplus / as sysdba (OS auth. Make sure your user is member of local group ORA_DBA)
SQL>select * from v$instance;

Check the alert_.log for errors. You will find the alert log in your Oracle Software trace folder.

Best of luck.

• gema: Thanks for the answer, i tried set ORACLE_SID=LOCAL then found out that the database instance is not running. Trying to run startup but got 00109: could not open parameter file ..../INITLOCAL.ORA checked the folder and the only .ora files i found are SPFILELOCAL.ORA and PWDlocal.ora. No INITLOCAL.ORA. or even INITSIAKDB.ORA (latest db i made) lsnrctl status shows only the latest db i made and CLRExtProc.
• Mark Stewart: If your error message truly says ..../INIT LOCAL.ORA then somehow your setting somewhere is messed up. If you were just hiding sensitive information.... is a poor choice. A .... isn't a valid directory path...