DATABASE TABLE REFRESH OVER NETWORK USING DATABASE LINK

TABLE REFRESH:

Advantages:

This is an alternate and fast approach for importing database from one database to another.

This process will help you to save time as we can skip following activities:

1.       Creating database directory for backup on source database.

2.       Create dump file on source database.

3.       Copy dump file at destination server.

4.       Creating database directory for backup restoration on Destination database.

5.       Expdp command to take database backup.

Suppose we have two databases DB11G and DB11GDEV. We need to restore user Metals table called ‘Products’ backup from db11g to user FINANCE db11gdev database.
In another words source and Target will be as below:

Souce: METSLS.PRODUCTS
TARGET:FINANCE.PRODUCT

Prerequisites:

1.       Source and Destination database must be connected through the network.

2.       Both databases must be in open mode.

3.       Source database (DB11G) tns detail must exist at destination (DB11GDEV).

STEP 1)  Create a public database link at DB11GDEV connecting to DB11G using system user.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

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

DB11GDEV  READ WRITE

SQL> create public database link metals_link connect to system identified by oracle_4U using 'db11g';

Script for checking db links:

COLUMN owner FORMAT A30

COLUMN db_link FORMAT A50

COLUMN username FORMAT A30

COLUMN host FORMAT A30

SELECT owner,

       db_link,

       username,

       host

FROM   dba_db_links

ORDER BY owner, db_link

Crosscheck the db link is working.

SQL> select * from dual@metals_link ;

D

-

X

STEP 2)   Import using Impdp command at  DB11GDEV (Destination)

SET ORACLE_SID=db11gdev

impdp finance/finance@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance tables=metals.products table_exists_action=replace

C:\Users\ashwanik\Desktop\MY_SCRIPT>impdp finance/finance@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance tables=metals.products table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Fri Jul 19 16:10:54 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "FINANCE"."SYS_IMPORT_TABLE_01":  finance/********@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance tables=metals.products table_exists_action=replace

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 584 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . imported "FINANCE"."PRODUCTS"                       16664157 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-02270: no matching unique or primary key for this column-list

Failing sql is:

ALTER TABLE "FINANCE"."PRODUCTS" ADD CONSTRAINT "FK_PROD_MAT" FOREIGN KEY ("ILI_MATERIAL_NUMBER") REFERENCES "FINANCE"."MATERIAL_RECORDS" ("ILI_MATERIAL_NUMBER") ON DELETE CASCADE ENABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "FINANCE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri Jul 19 16:13:57 2019 elapsed 0 00:03:03

DATABASE SCHEMA REFRESH OVER NETWORK USING DATABASE LINK

SCHEMA REFRESH:

SET ORACLE_SID=db11gdev

impdp finance/finance@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance table_exists_action=replace

OR

system/********@db11gdev directory=pump network_link=metals_link schemas=metals remap_schema=metals:finance table_exists_action=replace