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)
[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)))
SQL> select sysdate from dual@AJARDB;
SYSDATE
---------
30-JUN-24
SQL>
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>
################################################################################################################
#-- * Heterogeneous DB Link to access MS SQL Server form Oracle RAC Database 1521 (Port)
========================================================
Download Linux Gateway software from below
LINUX.X64_193000_gateways.zip
High Level Steps
- Resolve the network connectivity
$ nc –zv $HOSTNAME 1433
- Install Gateway software for MS SQL on node1 without set any env like (/u01/app/oracle/product/19.0.0/tghome_1)
- Name Listener to LISTENER_SQL on 1535 port
- Update listener file and initfiles (/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/admin) from Gateway home
- Add new tns entries in RDBMS HOME and test connection using tnsping dg4msql
- Create db link using tns and verify
SQL> select sysdate from dual@ACCOUNTS_DB;
- if all set copy RDBMS tns entries from node1 to all rac nodes
========================================================
Installation of Gateway
SQL> CREATE TABLESPACE MYPASAU DATAFILE '+DATA' size 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;
SQL> create user MYPASAU identified by MYPASAU default tablespace MYPASAU quota unlimited on MYPASAU;
User created.
SQL> grant connect,resource,create view to MYPASAU;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO "MYPASAU";
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO "MYPASAU";
Grant succeeded.
SQL> exit
[oracle@testdb admin]$ time expdp system/abcd1234 schemas=MYPASAU directory=expdp dumpfile=MYPASAU_`date +%d-%m-%y`.dmp logfile=MYPASAU_exp_`date +%d-%m-%y`.log logtime=all compression=all
[oracle@rac1 expdp]$ time impdp system/abcd1234 schemas=MYPASAU remap_schema=MYPASAU:MYPASAU remap_tablespace=USERS:MYPASAU directory=expdp dumpfile=MYPASAU_30-12-24.dmp logfile=MYPASAU_imp_`date +%d-%m-%y`.log logtime=all
-----------------------------------------------------------
-----------------------------------------------------------
MSSQL Database List
1. Accounts_db
2. Employment_db
3. Leadership_DB
4. Research_db
5. Sms_db
6. TimeAward_db
-----------------------------------------------------------
Listner parameter for Gateway home
C:\Users\m.areef>ping -a LIS-ESERVICE.areef.com.sa
[oracle@ rac1 admin]$ nslookup LIS-ESERVICE.areef.com.sa
Server: 10.98.192.100
Address: 10.98.192.100#53
Name: LIS-ESERVICE.areef.com.sa
Address: 18.8.171.76
[oracle@ rac1 admin]$
[oracle@rac1 admin]$ nc -zv LIS-ESERVICE.areef.com.sa 1433
NNcat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 18.8.171.76:1433.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.
[oracle@rac1 admin]$
gateway lsnrctl parameters update
[oracle@rac1 admin]$ vi /u01/app/oracle/product/19.0.0/tghome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/tghome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_SQL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.areef.com.sa)(PORT = 1535))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1535))
)
)
SID_LIST_LISTENER_SQL=
(SID_LIST=
(SID_DESC=
(SID_NAME=Accounts_db)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
(PROGRAM=dg4msql)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib")
)
(SID_DESC=
(SID_NAME=Employment_db)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
(PROGRAM=dg4msql)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib")
)
(SID_DESC=
(SID_NAME=Leadership_DB)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
(PROGRAM=dg4msql)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib")
)
(SID_DESC=
(SID_NAME=Research_db)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
(PROGRAM=dg4msql)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib")
)
(SID_DESC=
(SID_NAME=Sms_db)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
(PROGRAM=dg4msql)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib")
)
(SID_DESC=
(SID_NAME=TimeAward_db)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
(PROGRAM=dg4msql)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib")
)
)
[oracle@rac1 admin]$
[oracle@rac1 admin]$ lsnrctl stop LISTENER_SQL
[oracle@rac1 admin]$ lsnrctl start LISTENER_SQL
[oracle@rac1 admin]$ lsnrctl status LISTENER_SQL
----------------------------------------------------------- tns parameter for oraclehome
[oracle@rac1 admin]$ vi /u01/app/oracle/product/19.0.0.0/db_1/network/admin/tnsnames.ora
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ACCOUNTS_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Rac1.areef.com.sa)(PORT=1535))
(CONNECT_DATA=(SID=Accounts_db))
(HS=OK)
)
EMPLOYMENT_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Rac1.areef.com.sa)(PORT=1535))
(CONNECT_DATA=(SID=Employment_db))
(HS=OK)
)
LEADERSHIP_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Rac1.areef.com.sa)(PORT=1535))
(CONNECT_DATA=(SID=Leadership_DB))
(HS=OK)
)
RESEARCH_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Rac1.areef.com.sa)(PORT=1535))
(CONNECT_DATA=(SID=Research_db))
(HS=OK)
)
SMS_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Rac1.areef.com.sa)(PORT=1535))
(CONNECT_DATA=(SID=Sms_db))
(HS=OK)
)
TIMEAWARD_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Rac1.areef.com.sa)(PORT=1535))
(CONNECT_DATA=(SID=TimeAward_db))
(HS=OK)
)
[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping ACCOUNTS_DB
[oracle@rac1 admin]$ tnsping EMPLOYMENT_DB
[oracle@rac1 admin]$ tnsping LEADERSHIP_DB
[oracle@rac1 admin]$ tnsping RESEARCH_DB
[oracle@rac1 admin]$ tnsping SMS_DB
[oracle@rac1 admin]$ tnsping TIMEAWARD_DB
[oracle@rac1 admin]$ scp tnsnames.ora oracle@rac2:/u01/app/oracle/product/19.0.0.0/db_1/network/admin
----------------------------------------------------------- Gateway file configuration
[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/19.0.0/tghome_1/dg4msql/admin
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cp initdg4msql.ora initTimeAward_db.ora
[oracle@rac1 admin]$ cp initdg4msql.ora initSms_db.ora
[oracle@rac1 admin]$ cp initdg4msql.ora initResearch_db.ora
[oracle@rac1 admin]$ cp initdg4msql.ora initAccounts_db.ora
[oracle@rac1 admin]$ cp initdg4msql.ora initEmployment_db.ora
[oracle@rac1 admin]$ cp initdg4msql.ora initLeadership_DB.ora
[oracle@rac1 admin]$ vi initTimeAward_db.ora
[oracle@rac1 admin]$ vi initSms_db.ora
[oracle@rac1 admin]$ vi initResearch_db.ora
[oracle@rac1 admin]$ vi initAccounts_db.ora
[oracle@rac1 admin]$ vi initEmployment_db.ora
[oracle@rac1 admin]$ vi initLeadership_DB.ora
----------------------------------------------------------- Database links to MSSQL PROD DB
SQL> CREATE DATABASE LINK "ACCOUNTS_DB" CONNECT TO "ABCDUSER" IDENTIFIED BY "ABCDPASS" USING 'ACCOUNTS_DB';
CREATE DATABASE LINK "EMPLOYMENT_DB" CONNECT TO "ABCDUSER" IDENTIFIED BY "ABCDPASS" USING 'EMPLOYMENT_DB';
CREATE DATABASE LINK "LEADERSHIP_DB" CONNECT TO "ABCDUSER" IDENTIFIED BY "ABCDPASS" USING 'LEADERSHIP_DB';
CREATE DATABASE LINK "RESEARCH_DB" CONNECT TO "ABCDUSER" IDENTIFIED BY "ABCDPASS" USING 'RESEARCH_DB';
CREATE DATABASE LINK "SMS_DB" CONNECT TO "ABCDUSER" IDENTIFIED BY "ABCDPASS" USING 'SMS_DB';
CREATE DATABASE LINK "TIMEAWARD_DB" CONNECT TO "ABCDUSER" IDENTIFIED BY "ABCDPASS" USING 'TIMEAWARD_DB';
-------------------------
select sysdate from dual@ACCOUNTS_DB;
select sysdate from dual@EMPLOYMENT_DB;
select sysdate from dual@LEADERSHIP_DB;
select sysdate from dual@RESEARCH_DB;
select sysdate from dual@SMS_DB;
select sysdate from dual@TIMEAWARD_DB;
Check cat Run Steps #-- * Repe_O 'TESTUSER1' EBS_web_OHS* HS
========================================================
https://support.oracle.com/epmos/faces/DocumentDisplay?&id=2319360.1
E-Business Suite Applications Login Analyzer (Doc ID 2319360.1)
Thanks for Reading
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux | DATABASE | RMAN | RAC | EBS |
R1229 M7 Clone | ||||
RAC DataGuard | Pluggable DB Clone | |||
appsutil for DB | ||||
JDK JRE upgrade | ||||
Add EBS Node | ||||
No comments:
Post a Comment