Migrasi data from 11g to 10g with dumpfile

8 Nov

expdp VERSION=10.1 ……………..

################################
SOURCE
################################
1. set environment
[oracle@rac3 backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Mon Nov 8 10:33:17 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create or replace directory dumpfile as ‘/home/oracle/backup’;
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO master;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO mkt;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO survey;
Grant succeeded.
SQL> exit

[oracle@rac3 backup]$ mkdir /home/oracle/backup

2. expdp
[oracle@rac3 ~]$ expdp master/***** tables=GV_Main_Prod,Produk VERSION=10.1 DIRECTORY=dumpfile DUMPFILE=survey_ms101.dmp NOLOGFILE=y
Export: Release 11.1.0.6.0 – 64bit Production on Monday, 08 November, 2010 12:35:17
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting “MASTER”.”SYS_EXPORT_TABLE_01″: master/******** tables=GV_Main_Prod,Produk VERSION=10.1 DIRECTORY=dumpfile DUMPFILE=survey_ms101.dmp NOLOGFILE=y
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 39.56 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
. . exported “MASTER”.”PRODUK” 33.33 MB 263963 rows
. . exported “MASTER”.”GV_MAIN_PROD” 418.7 KB 7791 rows
Master table “MASTER”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MASTER.SYS_EXPORT_TABLE_01 is:
/home/oracle/backup/survey_ms101.dmp
Job “MASTER”.”SYS_EXPORT_TABLE_01″ successfully completed at 12:40:58

[oracle@rac3 /]$ expdp mkt/**** tables=ML_Master_Size VERSION=10.1 DIRECTORY=dumpfile DUMPFILE=survey_mk101.dmp NOLOGFILE=y
[oracle@rac3 /]$ expdp survey/***** schemas=survey VERSION=10.1 DIRECTORY=dumpfile DUMPFILE=survey_101.dmp NOLOGFILE=y

################################
TARGET
################################
3. set environment
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\xxx>d:
D:\>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 – Production on Mon Nov 8 10:58:13 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> create or replace directory dumpfile as ‘D:\’;
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO master;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO mkt;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO survey;
Grant succeeded.
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
D:\>

4. copy dmp from source
D:\>ftp 172.20.141.13
Connected to 172.20.141.13.
220 (vsFTPd 2.0.5)
User (172.20.141.13:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> bin
200 Switching to Binary mode.
ftp> cd /home/oracle/backup
250 Directory successfully changed.
ftp> bin
200 Switching to Binary mode.
ftp> mget *.dmp
200 Switching to Binary mode.
mget survey_101.dmp? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for survey_101.dmp (3108864 bytes).
226 File send OK.
ftp: 3108864 bytes received in 0.28Seconds 11063.57Kbytes/sec.
mget survey_mk101.dmp? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for survey_mk101.dmp (118784 bytes).
226 File send OK.
ftp: 118784 bytes received in 0.00Seconds 118784000.00Kbytes/sec.
mget survey_ms101.dmp? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for survey_ms101.dmp (36638720 bytes)
226 File send OK.
ftp: 36638720 bytes received in 3.34Seconds 10956.56Kbytes/sec.
ftp> bye
221 Goodbye.

5. impdp
impdp master/***** tables=GV_Main_Prod,Produk VERSION=LATEST DIRECTORY=dumpfile DUMPFILE=survey_ms101.dmp NOLOGFILE=y
impdp mkt/**** tables=ML_Master_Size VERSION=LATEST DIRECTORY=dumpfile DUMPFILE=survey_mk101.dmp NOLOGFILE=y
impdp survey/**** schemas=survey VERSION=LATEST DIRECTORY=dumpfile DUMPFILE=survey_101.dmp NOLOGFILE=y

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

%d bloggers like this: