Make sure you have TNS entries defined on both the database tnsnames.ora
Confirm with tnsping <SID>
CREATE DATABASE LINK <DBLINKNAME> CONNECT TO hr IDENTIFIED BY password USING '<TNSNAME(SID)>';
My target machine is server name PREPROD(SID)
What do I need to modify in my listener.ora? Do I need to modify somthing in tnsnames.ora? My source machine(where i am creating the database link) has service name BITEST, IP: 172.25.43.147
my listener .ora is
(SID_NAME = PREPROD)
STARTUP_WAIT_TIME_PREPROD = 0
CONNECT_TIMEOUT_PREPROD = 10
TRACE_LEVEL_PREPROD = OFF
If you are trying to create DB link from BITEST to PREPROD, then -
1. Add TNS entry of PREPROD in BITEST tnsnames.ora file.
2. tnsping PREPROD, if its OK then
3. CREATE DATABASE LINK link_name CONNECT TO target_db_user IDENTIFIED BY target_db_user_paswd USING 'target_tns_alias';
4. Verify the DB link - select * from dual@link_name;
When i connect as sysdba user it works. I do select * from tab@bi_apps;
But when I connect as XX_OCEAN/XX_OCEAN and connect it says:
ORA-02019: connection description for remote database not found
1 person found this helpful
It seems that you have created the DB link with SYS user.
Use below query to check owner of DB link :
SQL> select owner,DB_LINK from dba_db_links;
There are 2 types of DB link Public and Private:
Public : You can access the DB link with any DB user
Private : You can access the DB link with DB link owner only.
If you want to access DB link from any schema then create public database link else create private DB link by connecting to XX_OCEAN schema.
Syntax for Public DB link creation :
CREATE PUBLIC DATABASE LINK link_name CONNECT TO target_db_user IDENTIFIED BY target_db_user_paswd USING 'target_tns_alias';
Syntax for Private DB link creation :
CREATE DATABASE LINK link_name CONNECT TO target_db_user IDENTIFIED BY target_db_user_paswd USING 'target_tns_alias';