Oracle11G Data Pump By Using Database Link

Scenario:
Directly importing the TEST01 schema in the production database (oraodrmu) to test database oraodrmt, over
a network by using database link and data pump in Oracle 11g.

Note: When you perform an import over a database link, the import source is a database, not a dump file set, and the data is imported to the connected database instance.
Because the link can identify a remotely networked database, the terms database link and network link are used interchangeably.

=================================================================
STEP-1 (IN PRODUCTION DATABASE – oraodrmu)
=================================================================

[root@szoddb01]>su – oraodrmu

Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant resource to test01;

Grant succeeded.

SQL> grant imp_full_database to test01;

Grant succeeded.

SQL> select owner,object_type,status,count(*) from dba_objects where owner=’TEST01′ group by owner,object_type,status;

OWNER OBJECT_TYPE STATUS COUNT(*)
—————————— ——————- ——- ———-
TEST01 PROCEDURE VALID 2
TEST01 TABLE VALID 419
TEST01 SEQUENCE VALID 3
TEST01 FUNCTION VALID 8
TEST01 TRIGGER VALID 3
TEST01 INDEX VALID 545
TEST01 LOB VALID 18

7 rows selected.

SQL>
SQL> set pages 999
SQL> col “size MB” format 999,999,999
SQL> col “Objects” format 999,999,999
SQL> select obj.owner “Owner”
2 , obj_cnt “Objects”
3 , decode(seg_size, NULL, 0, seg_size) “size MB”
4 from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
5 , (select owner, ceil(sum(bytes)/1024/1024) seg_size
6 from dba_segments group by owner) seg
7 where obj.owner = seg.owner(+)
8 order by 3 desc ,2 desc, 1
9 /

Owner Objects size MB
—————————— ———— ————
OND 8,097 284,011
SYS 9,601 1,912
TEST01 998 1,164

3 rows selected.

SQL> exit

=================================================================
STEP-2 (IN TEST DATABASE – oraodrmt)
=================================================================

[root@szoddb01]>su – oraodrmt

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 18:40:16 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
ODRMT READ WRITE

SQL> create tablespace test_test datafile ‘/trn_u04/oradata/odrmt/test01.dbf’ size 2048m;

Tablespace created.

SQL> create user test01 identified by test123 default tablespace test_test;

User created.

SQL> grant resource, create session to test01;

Grant succeeded.

SQL> grant EXP_FULL_DATABASE to test01;

Grant succeeded.

SQL> grant imp_FULL_DATABASE to test01;

Grant succeeded.

Note: ODRMU is the DNS hoste name.We can test the connection with: [oraodrmt@szoddb01]>sqlplus test01/test01@odrmu

SQL> create directory test_network_dump as ‘/dbdump/test_exp’;

Directory created.

SQL> grant read,write on directory test_network_dump to test01;

Grant succeeded.

SQL> conn test01/test123
Connected.

SQL> create DATABASE LINK remote_test CONNECT TO test01 identified by test01 USING ‘ODRMU’;

Database link created.

For testing the database link we can try the below sql:

SQL> select count(*) from OA_APVARIABLENAME@remote_test;

COUNT(*)
———-
59

SQL> exit

[oraodrmt@szoddb01]>impdp test01/test123 network_link=remote_test directory=test_network_dump remap_schema=test01:test01 logfile=impdp__networklink_grms.log;
[oraodrmt@szoddb01]>

Import: Release 11.2.0.2.0 – Production on Mon Dec 3 19:42:47 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “TEST01”.”SYS_IMPORT_SCHEMA_01″: test01/******** network_link=remote_test directory=test_network_dump remap_schema=test01:test01 logfile=impdp_grms_networklink.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 318.5 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”TEST01″ already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “TEST01″.”SY_TASK_HISTORY” 779914 rows
. . imported “TEST01″.”JCR_JNL_JOURNAL” 603 rows
. . imported “TEST01″.”GX_GROUP_SHELL” 1229 rows
. . . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

Job “TEST01”.”SYS_IMPORT_SCHEMA_01″ completed with 1 error(s) at 19:45:19

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 19:46:04 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner,object_type,status,count(*) from dba_objects where owner=’TEST01′ group by owner,object_type,status;

OWNER OBJECT_TYPE STATUS COUNT(*)
—————————— ——————- ——- ———-
TEST01 PROCEDURE VALID 2
TEST01 TABLE VALID 419
TEST01 SEQUENCE VALID 3
TEST01 FUNCTION VALID 8
TEST01 TRIGGER VALID 3
TEST01 INDEX VALID 545
TEST01 LOB VALID 18
TEST01 DATABASE LINK VALID 1

8 rows selected.

SQL>
SQL> set pages 999
SQL> col “size MB” format 999,999,999
SQL> col “Objects” format 999,999,999
SQL> select obj.owner “Owner”
2 , obj_cnt “Objects”
3 , decode(seg_size, NULL, 0, seg_size) “size MB”
4 from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
5 , (select owner, ceil(sum(bytes)/1024/1024) seg_size
6 from dba_segments group by owner) seg
7 where obj.owner = seg.owner(+)
8 order by 3 desc ,2 desc, 1
9 /

Owner Objects size MB
—————————— ———— ————
OND 8,065 247,529
SYS 9,554 6,507
TEST01 999 1,164

13 rows selected.

=================================================================
STEP-3 FOR REMOVING THE DATABASE LINK
=================================================================

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 19:16:01 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop database link remote_test;

Database link dropped.

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in Oracle and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s