4 Replies Latest reply: Dec 4, 2012 3:11 AM by SHANOJ RSS

    Import Data over network link in oracle 11g

    SHANOJ
      We want to take export of the OND schema in production database and
      import it to the OND schema in UAT database over a network
      link by using data pump,in Oracle 11g.Kindly share the steps.
        • 1. Re: Import Data over network link in oracle 11g
          Osama_Mustafa
          This link will solve your issue
          http://blog.ronnyegner-consulting.de/2009/11/02/using-data-pump-to-export-and-import-data-using-network_mode-feature/
          • 2. Re: Import Data over network link in oracle 11g
            Niket Kumar
            you can use network link .take refrence from below link.

            http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
            • 3. Re: Import Data over network link in oracle 11g
              SHANOJ
              Thanks both of you...
              • 4. Re: Import Data over network link in oracle 11g
                SHANOJ
                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 connect 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.