Database Links creation form PDB to NON-PDB

Trying to create a database link from pdb to non pdb and got below error.

12c PDB or Multitenant Only: ORA-02085: Database Link "LINK_NAME_HERE" Connects To "TARGET_DB" (Doc ID 2344831.1)

List of Topics (Linux, Database, RAC, EBS)

[oracle@mncsuatclouddb ~]$ 

[oracle@mncsuatclouddb ~]$ . $ORACLE_HOME/UATPDB_mncsuatclouddb.env

[oracle@mncsuatclouddb ~]$ echo $ORACLE_SID

UATPDB

[oracle@mncsuatclouddb ~]$ tnsping ajardb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-AUG-2021 12:57:04

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/UATPDB_mncsuatclouddb/sqlnet_ifile.ora 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = 15xx)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = AJARDB)))

OK (40 msec)

[oracle@mncsuatclouddb ~]$ sqlplus ajar_live/password@ajardb (test the connection)

 

ERROR 1: description missing in db link(AJARDB_1) if used tns connection

Here I have created two database link namely ajardb & ajardb_1 one with manual sting creation and the other with tns;

[oracle@mncsuatclouddb ~]$ sqlplus ajar_live/password@uatpdb

SELECT * FROM dba_DB_LINKS;

SELECT * FROM all_DB_LINKS;

SELECT * FROM USER_DB_LINKS;

SELECT * FROM V$DBLINK;

SQL> GRANT CREATE PUBLIC DATABASE LINK TO AJAR_LIVE;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO AJAR_LIVE;

Grant succeeded.

SQL> 

SQL> GRANT DROP PUBLIC DATABASE LINK TO AJAR_LIVE;

Grant succeeded.

SQL> 

CREATE PUBLIC DATABASE LINK AJARDB CONNECT TO ajar_live IDENTIFIED BY xxxx USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=19.xx.xx.4)(PORT=15xx))(CONNECT_DATA=(SERVICE_NAME=AJARDB)))'; -- PUBLIC database link to a user in a remote database, with full connection string. -- Public database link available to all DB SCHEMAS.

SQL> CREATE DATABASE LINK AJARDBPRV CONNECT TO ajar_live IDENTIFIED BY xxxx USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=19.xx.xx.4)(PORT=15xx))(CONNECT_DATA=(SERVICE_NAME=AJARDB)))'; -- Private database link to a user in a remote database, with full connection string.

SQL>

CREATE DATABASE LINK AJARDB_1 CONNECT TO ajar_live IDENTIFIED BY xxxx USING 'AJARDB'; -- applicable only for non-pdbs

set linesize 200;

col DB_LINK for a80;

select DB_LINK,HOST from dba_db_links;

DB_LINK

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

HOST

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

AJARDB.PRDDBSN.MNCSCLOUDPRDVCN1.ORACLEVCN.COM

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=19x.xx.xx.4)(PORT=15xx))(CONNECT_DATA=(SERVICE_NAME=AJARDB)))

 

AJARDB_1.PRDDBSN.MNCSCLOUDPRDVCN1.ORACLEVCN.COM

AJARDB  (connect description is missing if created link with tns names)

 

SQL> select HOST from user_db_links;

HOST

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=19x.xx.xx.4)(PORT=15xx))(CONNECT_DATA=(SERVICE_NAME=AJARDB)))

AJARDB (this cause the problem for link form pdb to non pdb)

SQL>

SQL> select count(*) from ajar_live.accidents@AJARDB_1;

select * from ajar_live.accidents@AJARDB_1                             *

ERROR at line 1:

ORA-02085: database link AJARDB connects to

AJARDB_1.PRDDBSN.MNCSCLOUDPRDVCN1.ORACLEVCN.COM

 

ERROR 2: reset pdbs glabal_names to false:

SQL> select count(*) from ajar_live.accidents@ajardb_1;

select * from ajar_live.accidents@ajardb                 *

ERROR at line 1:

ORA-12154: TNS:could not resolve the connect identifier specified

Solution:

Simply re-issuing the alter system set command to set GLOBAL_NAMES to false at the PDB will allow the link to work.

SQL> alter session set container=uatpdb;

PDB> alter system set GLOBAL_NAMES=false;

Now dblink works.

SQL> select count(*) from ajar_live.accidents@ajardb;

  COUNT(*)

----------

         4

SQL>

SQL> CREATE OR REPLACE SYNONYM ajardb_accidents FOR ajar_live.accidents@ajardb;

SQL> select count(*) from ajardb_accidents;

  COUNT(*)

----------

         4

SQL> drop SYNONYM ajardb_accidents;

Synonym dropped.

SQL>

SQL> drop DATABASE LINK ajardb;

Database link dropped.

SQL> 

SQL> show user;

USER is "AJAR_LIVE"

SQL>

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

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

db_name                              string      UATCDB

SQL> show con_name;

CON_NAME

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

UATPDB

SQL>

Thanks for Reading

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	12102 RAC INSTAL	R1229 M7 Clone
DNS in Linux 7	WIN-LINUX MIGRATE	RMAN DB CLONE	RAC DataGuard	Pluggable DB Clone
lpadmin cups	ASMCMD ASM Migrt	RMAN ASM Migrt	RAC DB Clone	appsutil for DB
	11204 M-Upgrade		RAC - RAC Clone	JDK JRE upgrade
	12102 M-Upgrade		12C-19C RAC UPGRADE	Add EBS Node
	Flashback DB			ADOP in R12.2.9
				12102 EBS UPGRADE
Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics