1

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\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)
0

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.

1
  • 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. – gema Aug 5 '20 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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