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