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.

My tnsnames.ora file are as follows:

# tnsnames.ora Network Configuration File: D:\deployed\oracle\product\12.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.