Import and Export using the external table method.

Import and Export using the external table method..

Recently I was the part of U2L database migration team.we performed database migration using
import and export.some tables were large because of that we used the import/export using external table.
I am sharing migration steps with you.

source machine: pt.example.com
targate machine: gg1.exampl.com

on Source Database:

1)

SQL> grant exp_full_database,imp_full_database to scott;        ---No need

2)

[oracle@pt ~]$ mkdir /u01/dba_pump
[oracle@pt ~]$ exit

exit

SQL> create directory dba_pump as '/u01/dba_pump';

Directory created.

3)

CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY dba_pump ACCESS
PARAMETERS (COMPRESSION ENABLED)
LOCATION ('emp_TBL_AP_1.dmp' ,'emp_TBL_AP_2.dmp'))
PARALLEL 2
AS SELECT /*+ PARALLEL (t,2)*/  *  FROM test t

4) give the permision to dump file as per your company polcy.

[oracle@pt dba_pump]$ chmod 777 emp_TBL_AP_2.dmp
[oracle@pt dba_pump]$ chmod 777 emp_TBL_AP_1.dmp
[oracle@pt dba_pump]$ ls -ltr
total 40
-rwxrwxrwx 1 oracle oinstall 12288 Mar 11 23:51 emp_TBL_AP_2.dmp
-rwxrwxrwx 1 oracle oinstall 16384 Mar 11 23:51 emp_TBL_AP_1.dmp
-rw-r--r-- 1 oracle oinstall    41 Mar 11 23:51 EMP_EXT_3740.log
-rw-r--r-- 1 oracle oinstall    41 Mar 11 23:51 EMP_EXT_3738.log
-rw-r--r-- 1 oracle oinstall    41 Mar 11 23:51 EMP_EXT_3732.log


On Targate Database:-

5)

SQL> grant exp_full_database,imp_full_database to scott;       -----no need

[oracle@pt ~]$ mkdir /u01/dba_pump
[oracle@pt ~]$ exit

exit

SQL> create directory dba_pump as '/u01/dba_pump';

Directory created.


 On source database. 

6) transfer the file from source to target database..

- I used scp methed to trasfer files.

[oracle@pt dba_pump]$ scp emp_TBL_AP_2.dmp emp_TBL_AP_1.dmp oracle@gg1.example.com:/u01/dba_pump
The authenticity of host 'gg1.example.com (192.168.0.153)' can't be established.
RSA key fingerprint is 6b:ac:03:70:72:38:2f:ce:80:f9:99:26:8c:ce:4a:60.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'gg1.example.com,192.168.0.153' (RSA) to the list of known hosts.
oracle@gg1.example.com's password:
emp_TBL_AP_2.dmp                                                                           100%   12KB  12.0KB/s   00:00
emp_TBL_AP_1.dmp                                                                           100%   16KB  16.0KB/s   00:00


Targate Database:-

7) check file present or not..

[oracle@gg1 ~]$ cd /u01/dba_pump
[oracle@gg1 dba_pump]$ ls -ltr
total 28
-rwxr-xr-x 1 oracle oinstall 12288 Mar 12 00:04 emp_TBL_AP_2.dmp
-rwxr-xr-x 1 oracle oinstall 16384 Mar 12 00:04 emp_TBL_AP_1.dmp

8) create table from dump file (external table)

- structure same as the exported table..
- remove all constriant of table..

create table emp_ext(
 EMPNO  NUMBER,         ----in table not null was present i deleted it
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY dba_pump
LOCATION ('emp_TBL_AP_1.dmp' ,'emp_TBL_AP_2.dmp'))
REJECT LIMIT 0
PARALLEL ( DEGREE 10 INSTANCES 1 )
NOMONITORING;

9) create normal table  structure..

sql>create table emp_2 (EMPNO NUMBER not null,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2))
 /

10) insert data from externel table to normale table..

-vi insert.sh

#!/usr/bin/sh

sqlplus -s scott/tiger  << EOF

col instance_name for a15
col host_name for a15
col version for a15
set lines 200
col partition_name for a20
col dt for a15


### Create logs

spool Insert_TBL_CUSTOMER_12.log


select instance_name,host_name,version, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') as "DB Started AT",status,archiver from v\$instance;

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') as "Current Date Time" from dual ;
alter session enable parallel dml;


insert /*+ parallel(emp_2,2) */  into emp_2 select /*+ parallel(t,2) */ * from emp_ext t ;

commit;
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') as "Current Date Time" from dual ;

spool off

exit
EOF

11)
[oracle@gg1 dba_pump]$ sh -x insert.sh
+ sqlplus -s scott/tiger
SP2-0734: unknown command beginning "## Create ..." - rest of line ignored.

INSTANCE_NAME   HOST_NAME       VERSION         DB Started AT        STATUS       ARCHIVE
--------------- --------------- --------------- -------------------- ------------ -------
DB11G           gg1.example.com 11.2.0.2.0      11-MAR-2015 23:34:37 OPEN         STARTED


Current Date Time
--------------------
12-MAR-2015 00:42:14


Session altered.


1400 rows created.


Commit complete.


Current Date Time
--------------------
12-MAR-2015 00:42:15


12) check count both table from source to targate.


SQL> select count(*) from test;                  -----source database count

  COUNT(*)
----------
      1400



SQL> select count(*) from emp_2;                 ----targate database count

  COUNT(*)
----------
      1400


13) create constraint/index after the verification.


14) drop external table

sql> drop table emp_ext;