cancel
Showing results for 
Search instead for 
Did you mean: 

How do I migrate DBlink with Migration Portal?

How do I migrate DBlink with Migration Portal?

The documentation states that Migration Portal supports DBlink , but I do not know how to do so.

Because the DDL file is extracted for each database, DBlink can not be considered, and a large number of errors occur in the assessment.

What should I set when extracting DDL using SQL * Plus?

Accepted Solutions (0)

Answers (1)

Answers (1)

MuhammadIkram
EDB Team Member

Yes DB Links are supported in Migration from Oracle to EDB Postgres Advanced Server. Here is a sample that covers DB Link creation in Oracle schema HR, extraction using latest edb_ddl_extractor, Assessment in Migration Portal, manual changes in DB Link and then creation, usage in EDB Postgres Advanced Server.

 

Steps

 

1. Environment variables setting for using DB Link.

 

export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib:$LD_LIBRARY_PATH
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

2. Link Creation in Oracle.

 

CREATE DATABASE LINK hr_remote
CONNECT TO hr IDENTIFIED BY hr
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=XE))
)';

 

3. Extraction using latest edb_ddl_extractor.


########################################
## DATABASE LINKS
########################################

CREATE DATABASE LINK "HR_REMOTE"
CONNECT TO "HR" IDENTIFIED BY VALUES '05249A914E820E0DEE9EBABC30C0264FFF'
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=XE))
)';

 

4. Downloaded after assessment in migration portal.


CREATE DATABASE LINK HR_REMOTE
CONNECT TO HR IDENTIFIED BY VALUES '05249A914E820E0DEE9EBABC30C0264FFF'
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=XE))
)';

 

5. Make changes to the database link creation statement.


CREATE DATABASE LINK HR_REMOTE CONNECT TO HR IDENTIFIED BY 'hr'
USING '//*.*.*.*:1521/xe';

Or using OCI

CREATE DATABASE LINK HR_REMOTE CONNECT TO hr IDENTIFIED BY 'hr'
USING oci '//*.*.*.*:1521/XE';

 

6. Login PSQL client and test working of db link.


hr=# CREATE DATABASE LINK HR_REMOTE CONNECT TO hr IDENTIFIED BY 'hr'
hr-# USING '//*.*.*.*:1521/XE';
CREATE DATABASE LINK
hr=#
hr=#
hr=# SELECT *
FROM regions@hr_remote;
region_id | region_name
-----------+------------------------
1 | Europe
2 | Americas
3 | Asia
4 | Middle East and Africa
(4 rows)

hr=#

 

hr=# drop database link hr_remote;
DROP DATABASE LINK

 

In case You wish to use OCI then create link using following statement.

 

hr=# CREATE DATABASE LINK HR_REMOTE CONNECT TO hr IDENTIFIED BY 'hr'
hr-# USING oci '//*.*.*.*:1521/XE';
CREATE DATABASE LINK

hr=#
hr=# SELECT *
hr-# FROM regions@hr_remote;
region_id | region_name
-----------+------------------------
1 | Europe
2 | Americas
3 | Asia
4 | Middle East and Africa
(4 rows)

hr=#

 

 

Privileges required for user:

 

Other than necessary privileges (e.g. CONNECT/CREATE SESSION, RESOURCE, etc.) user must have SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY privileges.

Ask a Question