ORA-12514: TNS listener cannot resolve service name error

This came up after I created a test RMAN catalog database on one of my test serves that had three other databases running on it. These databases are actually ones that I restored while testing my full database backups, therefore I never really bothered about the networking aspect of them as I never connect to them from outside the server.

I had to setup the tnsnames.ora and listener configuartion to enable connections to and from the RMAN catalog database. This was a pretty straight forward thing but I unfortunately skipped a key configuration which caused the error ORA-12514 every time I tried to connect to the Catalog DB from any of the others.

Note that #tnsping CATDB was successful, which got me more confused and at the same time convinced that all was well with the SERVICE_NAME definition.

I also ran #lsnrctl service and I could see the CATDB service running.

However, the problem was the SERVICE_NAME I defined for the Catalog DB. 

Original configuration in tnsnames.ora

CATDB=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
                (CONNECT_DATA = (SERVICE_NAME = CATDB))

Correct configuration

CATDB=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
                (CONNECT_DATA = (SERVICE_NAME = CATDB.xxxxxx.local))

I did not fully specify the service name!

This resolved my problem and I was able to connect to the Catalog DB.