Connection to remote Oracle Database: ORA-12638, ORA-02019

In this article I’ll describe how to create to remote Oracle database, because there are no clear description for that. There are two ways to achieve it: throught link, i.e. you will be able to execute query like select * from table@db or directly connecting to remote database with sqlplus command sqlplus l/p@db.

This article is for Windows Server 2012, but it could works with other Windows versions and with Linux, if you could locate files on your own.

Note: even if you want to create link @db for SQL you should follow both steps, because links are using SID from tnsnames.ora.

Step 1 — create sqlplus connection

First of all you’ll need to create record in your tnsnames.ora located in PathToOracle\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN and you should add connection string:

db =  
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db)
    )
  )

Host could be localhost or ip address of the remote database. Now you just type sqlplus l/p@db... and recieve error ORA-12638.

You should open file sqlnet.ora in the same directory as tnsnames and comment line: #SQLNET.AUTHENTICATION_SERVICES= (NTS). This fix error for me, but this error could be caused be many reasons.

Step 2 — Creating link

But I need to connect to database and its tables from sql console of the IDEA or SQL Developer, but I’ve recievening error ORA-02019. After some googling I found that I should create link. It’s obvious for expirienced Oracle SQL developers, but I’m not DBA and have low expirience, so I’ve forgot about it.

Following command in sqlplus will cteate link:

create public database link LINK_NAME connect to SCHEMA identified by PASSWORD using 'SID';

If you’ve done mistake, then you could drop link using following:

drop public database link LINK_NAME';

If you create link using public keyword then you should delete it using it too. I don't know why so strict policy for commands, but I took it for granted.