We have an oracle 11g cloud database that was working fine until recently when it started throwing the ORA-12514 after every reboot. The instance is programmed to be started up every morning and shutdown two hours later.
To be able to fix this, I have to do the following every morning because it gets reinitialized after rebooting:
1) sudo su oracle 2) sqlplus /nolog 3) connect sys/xxxxxx as sysdba 4) shutdown abort; 5) startup 6) alter system set local_listener ='(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx.subnet.vcn.oraclevcn.com)(PORT=1521))' SCOPE=BOTH; 7) alter system register 8) quit
I have tried many things. First of all, I have tried tnsping the database and it works fine, so the listener is reachable.
I have seen in many other posts that we need to have the same service name in the database, in the listener.ora and tnsnames.ora files to fix this error, so I made sure of that.
Here are the tnsnames.ora file:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DB2020 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx.subnet.vcn.oraclevcn.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DB2020_lhr1vc.subnet.vcn.oraclevcn.com) ) )
and the listener.ora file:
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx.subnet.vcn.oraclevcn.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )