oracle db links useful queries

 

sqlplus / as sysdba

set pagesize 300

set linesize 300

col OWNER format a20;

col DB_LINK format a20;

col USERNAME format a20;

col HOST format a20;

select * from dba_db_links;

--------------------------------------------------

DROP PUBLIC DATABASE LINK <DBLINKNAME>;

CREATE PUBLIC DATABASE LINK <DBLINKNAME> CONNECT TO <USERNAME> IDENTIFIED BY <PASSWORD> using '<DBNAME>';

IssueS and fix:

1) Create DB link 

Following Note id 2592265.1, Setup the DBLink From DBCS to Autonomous Database (Serverless)

2) Validate Created db link 

SQL> select * from dual@adbdblink;
select * from dual@adbdblink
*
ERROR at line 1:
ORA-12696: Double Encryption Turned On, login disallowed

 3) Add the following parameter in the sqlnet.ora file of the Source(OCI DB system) db server to allow Native+ SSL (authentication to work together)

cd $OACLE_HOME/network/admin
cp -p sqlnet.ora sqlnet.ora_bkp
-- Add below line

sqlnet.IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE

 4) Another validation failed with: 

SQL> select * from dual@ADBDBLINK;
select * from dual@ADBDBLINK
*
ERROR at line 1:
ORA-02085: database link ADBDBLINK.SUB<<>>.ORACLEVCN.COM
connects to <ADB PDBNAME>

 5) Update Global name in source database This is due to the ADB-S global_name has no domain

sqlplus '/as sysdba'
show parameter global_names

If GLOBAL_NAMES is set TRUE, the local domain is automatically assigned if  there is no domain in the DBLINK name.
So ORA-2085 will occur when accessing DBLINK.

**  For test validation, Update at session level and later at the system level.

alter session set global_names=FALSE;
SQL> select sysdate from dual@ADBDBLINK;

SYSDATE
---------
06-SEP-21

## Update at the system level :

alter system set global_names=FALSE;

SQL> select sysdate from dual@ADBDBLINK;

SYSDATE
---------
06-SEP-21

 

Goal

Db link from OCI DB system to ADB
 

Symptoms

 

 ORA-12696: Double Encryption Turned On, login disallowed
 ORA-02085: database link ADBDBLINK connects to ADB