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;
