Skip to main content

Export from Oracle 11g and Import to Oracle 10g

Using EXPDP take dump from higher version and import into lower version. As you know EXPDP utility firstly was introduce in Oracle 10g. This is enhanced EXP/IMP utility (with additional features, example transport tablespace).
I will use Oracle 11g R2 (11.2.0.1.0) and Oracle 10g R2 (10.2.0.4)

Let`s create directory on Oracle 11g database and give read, write permission.

SQL> create directory my_dir as '/u01/app/oracle/admin/ulfetdb/dpdump';

Directory created.

SQL> grant read, write on directory my_dir to ulfet;

Grant succeeded.

SQL>

Now I will connect as ulfet user and will create new table and insert into one record.

SQL> conn ulfet/ulfet
Connected.

SQL> create table test_tbl (id number, name varchar2(10));

Table created.

SQL> insert into test_tbl values(1, 'Omar');

1 row created.

SQL> commit;

Commit complete.

Time to take export of table using DATAPUMP.

[oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl version=10.2

Export: Release 11.2.0.1.0 - Production on Mon Dec 10 14:45:54 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/********@ulfetdb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl version=10.2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ULFET"."TEST_TBL"                          5.289 KB       1 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ulfetdb/dpdump/test_tbl.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 14:46:29

[oracle@localhost dpdump]$

--Take dmp file to Oracle 10g`s db server and use same utility DATAPUMP`s IMPDP.

--To be sure directory works properly. I will create same directory on 10g.

SQL> create directory my_dir as '/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/dpdump'; 

Directory created.

SQL> grant read, write on directory my_dir to ulfet;

Grant succeeded.

SQL> 

Now try to imp

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:43:54

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_TABLE_01":  ulfet/********@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'REC_TBS' does not exist
Failing sql is:
CREATE TABLE "ULFET"."TEST_TBL" ("ID" NUMBER, "NAME" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "REC_TBS" 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "ULFET"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 13:44:07

[oracle@localhost ~]$ 

--There is error. Tablespace is different. To prevent such error use remap_tablespace option.

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:46:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_TABLE_01":  ulfet/********@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ULFET"."TEST_TBL"                          5.289 KB       1 rows
Job "ULFET"."SYS_IMPORT_TABLE_01" successfully completed at 13:46:24

[oracle@localhost ~]$

--Now, connect as ulfet and check table

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test_tbl;

        ID NAME
---------- ----------
         1 Omar

SQL> 

--Yes, this task completed.

--But what will happen if user omit version=10.2 during export/import. 
--Export will work normal as usual but while import data you will face exception such as ORA-39142.

--Let`s see.

--Oracle 11g

[oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl

Export: Release 11.2.0.1.0 - Production on Mon Dec 10 15:13:11 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/********@ulfetdb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ULFET"."TEST_TBL"                          5.414 KB       1 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ulfetdb/dpdump/test_tbl2.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 15:13:30

--Oracle 10g

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:53:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/dpdump/test_tbl2.dmp"

EXPDP has version options and supply the following Oracle versions:
  • Oracle 10g R1
  • Oracle 10g R2
  • Oracle 11g R1
  • Oracle 11g R2

To read in Azerbaijani : http://www.slideshare.net/ulfettanriverdiyev/oracle-11g-expdp-alib-oracle-10g-import-etmek-15608810

Comments

  1. Path InfotechJune 4, 2014 at 12:27 AM

    Path Infotech is in the field of oracle training program from past several years.

    For more info : Ocp Certification in Noida

    ReplyDelete

Post a Comment

Popular posts from this blog

Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open. SQL> select name from v$database; NAME --------- TEST SQL> shut abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 6597406720 bytes Fixed Size 2265664 bytes Variable Size 3204451776 bytes Database Buffers 3372220416 bytes Redo Buffers 18468864 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6552 Session ID: 191 Serial number: 3  What`s wrong?  SQL> alter database open resetlogs; ERROR:    ORA-03114: not connected to ORACLE    SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...
9 comments
Read more

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value. Example result of sql select                  execution_name, advisor_name,                  to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,                  to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' order by execution_start; Check value of TIME_LIMIT`s parameter : SQL> column parameter_value for A35 SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'DEFAULT_EXECUTION_TYP...
Post a Comment
Read more

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...
2 comments
Read more