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

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

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.