Tuesday, August 10, 2021

Database Links creation form PDB to NON-PDB and also MS SQL Server

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)



No comments:

Post a Comment