I Hate ORA-12514

Whenever a user calls me and tells me they're getting an ORA-12514 when trying to connect I get a little depressed. I find this one of the hardest errors to solve because there are so many places for it to go wrong. Here's a recent example:

I checked the database and it was set to archive log mode, but automatic archiving was not enabled. This usually causes a database to hang, but can also prevent connections (but with a different error). I stopped and restarted the database in archive log mode, enabled automatic archiving, tied reconnecting and voila! It didn’t work.

After a recent migration we had issues with some of the ORACLE_HOMEs on some databases. Back in the dim and distant past the databases used /u01/app/oracle/product/8.1.7 as the ORACLE_HOME, then they were upgraded and moved to /u01/app/oracle/product/8.1.7.4. However, I found that the listener.ora had this database hard-coded in the SID list, but with the old ORACLE_HOME.
I changed the entry for the TST2 database to reflect the new home, reloaded the listener, restarted the database, tried reconnecting and voila! It didn’t work.
I then checked the listener and tnsnames.ora files and saw that the service name had “sysdomain.local’ in it, so I removed that, reloaded the listener, tried to connect again and voila! It didn’t work.

I then checked the parameters in the database, and saw that there was a reference to TST1 in the SERVICE_NAME parameter, so I changed that to TST2, stopped and restarted the database and voila! It worked.

It looks as if this database was created by copying TST1 and recreating the controlfile, but someone left the reference to TST1 in the init.ora file. So even though the listener and tnsnames ora files looked OK, this parameter was over-riding them. If I hadn't have spotted it I may have been still trying to solve this one.