ORA-39142: incompatible version number 5.1 in dump file

Recently I have encountered below error while importing database dump file on Oracle Database 11gR2 and dump file was exported from 19c database.

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/u01/HYDSERVER/admin/rhyddb22/dpdump/testing.dmp"

This is basically downgrade situation, because we are exporting dump file in 19c database and importing it into 11g database. To overcome this error, while exporting the dump file in 19c database we need to explicitly use VERSION parameter which specified to be the target COMPATIBLE initialization parameter. 

Here in my case, the target database COMPATIBLE initialization parameter is 11.2. So need to specify same value for the VERSION parameter during export the dump file. 

Source side on 19c database with compatible 19.0.0:

-bash-4.2$ expdp directory=DATA_PUMP_DIR dumpfile=testing.dmp logfile=testing.log tables=testing_tables version=11.2

Target side on 11gR2 database with compatible 11.2:

-bash-4.2$ impdp directory=DATA_PUMP_DIR dumpfile=testing.dmp logfile=testing_imp.log  tables=testing_tables

That's it, hope this article will help you.

Thank you !!

Logical Database Backups Using Export and Import Utilities:

Here in this article describes how to use the original export and import utilities, invoked with the exp and imp command, respectively.

What happens when we export data from a database?

When we export data from an oracle database then the export utility will simply prepare a set of sql select statements and execute them against the target database. the data that is returned by the select operation (definition and rows of the objects) will be written to a file known as the export dump(.dmp) file.
This .dmp file is platform independent and can be carried to any other oracle site. when required the DBA can import the objects from the .dmp file into any oracle database. Import operation reads the definitions from the dump file and creates the objects there after it executes insert statements from the dump file against the database and loads the data back into the database objects that were created from the dump file.

Advantages of oracle export and import:-

1. Using oracle export/import we can backup/restore a particular object (table). export is used to backup and import is used to restore a database object.

2. Exports can be used to copy objects from one oracle schema to another.

3. When performing migration of an oracle database from one o/s to another like from windows to linux then logical backups are the only way to migrate the data as export dump file is platform independent.

4. When we want to migrate database objects, from one database, to another database, when both source and target databases, are running on oracle then we use export and import.

5. Export can also help in performing a database re-org. when after using a database for a while, it gets fragmented and also certain segments may have too many extents which results in poor i/o performance. For this reason every 3-4 months of database usage we go for a full database re-org , which includes

1. Full DB exp

2. Drop the database

3. Create a brand new database

4. Full DB imp

At the end of the import all the segments will be back within single huge extents and we will get rid of all fragmentation.

6. Exports can be done at table level, schema level, tablespace level or full database.

7. While exporting a table we can mention the query option from 8i onwards, example export the emp table where the deptno=10.

8. If the database is too large the dump file can run into gb and this much space may not be available in a single file system, thus we can export the data to multiple files spanning across multiple file systems from 8i onwards.

9. The size of an export dump file is usually 6 to 10 times smaller than the size of the database. the reasons are

1. Control,redolog are not exported
2. Exp does not carry any base tables (objects owned by sys).
3. The temporary segments are ignored.
4. Undo and rollback segments are ignored as we are interested only in commited data.
5. Only table data goes into the dump file along with the table definions.

10. Time taken for exp vs imp is usually 1:6 ratio as export is only a select operation where as import is a ddl+dml operation and each line goes through the undo or rollback segments and also goes to logfiles.

11. Import operation will cause a lot of redo activity and hence log switching will occur.

Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter i.e. "exp help=yes" or "imp help=yes".

Following two articles for export and import commands.

> Useful Traditional EXP Commands
> Useful Traditional IMP Commands

Thats it. Hope this article will help you :)

Regards,
Chowdari

Useful Traditional IMP Commands

Here you can find some useful Import/IMP commands:

1) Command to imp full database:

[chow@hyddb exp_bkp]$ imp system/manager file=full.dmp log=full_imp.log full=y 

2) Command to imp all scotts objects into scott user

[chow@hyddb exp_bkp]$ imp system/manager file=scott.dmp log=scott_imp.log fromuser=scott touser=scott 

3) Command to imp scotts emp table:

[chow@hyddb exp_bkp]$ imp system/manager file=emp.dmp log=emp_imp.log fromuser=scott touser=scott tables=emp 

4) Command to imp scotts multiple tables

[chow@hyddb exp_bkp]$ imp system/manager file=full.dmp log=full_scott_imp.log fromuser=scott touser=scott tables=(emp,dept,salgrade) 

Thats it. Hope this article will help you :)

Regards,
Chowdari

Useful Traditional EXP Commands

Here you can find some useful Export/EXP commands:

1) Command to perform full database export:

[chow@hyddb exp_bkp]$ exp system/manager file=full.dmp log=full.log full=y 

2) Command to export scott user:

[chow@hyddb exp_bkp]$ exp scott/tiger file=scott.dmp log=scott.log

3) Command to export scott user by system/manager:

[chow@hyddb exp_bkp]$ exp system/manager file=scott_exp_by_system.dmp log=scott_exp_by_system.log owner=scott 

4) Command to export multiple users by system/manager:

[chow@hyddb exp_bkp]$ exp system/manager file=user_exp_by_system.dmp log=user_exp_by_system.log owner=(scott,demo)

5) Command to export single table from scott user:

[chow@hyddb exp_bkp]$ exp scott/tiger file=emp.dmp log=emp.log tables=emp

6) Command to export multiple tables:

[chow@hyddb exp_bkp]$ exp scott/tiger file=emp_dept.dmp log=emp_dept.log tables=(emp,dept) 

7) Command to exp multiple tables from system/manager:

[chow@hyddb exp_bkp]$ exp system/manager file=emp_dept_system.dmp log=emp_dept_system.log tables=(scott.emp,scott.dept)

8) Command to export multiple tables from multiple users:

[chow@hyddb exp_bkp]$ exp system/manager file=multi_user_tab_system.dmp log=multi_user_tab_system.log tables=(scott.emp,scott.dept,demo.emp,demo.dept)

9) Command to exp scott user without rows:

[chow@hyddb exp_bkp]$ exp system/manager file=scott_empty.dmp log=scott_empty.log owner=scott rows=n

10) Command to exp scott user without row's constraints,grants,triggers and indexes:

[chow@hyddb exp_bkp]$ exp system/manager file=scott_empty_cons.dmp log=scott_empty_cons.log owner=scott constraints=n rows=n grants=n indexes=n triggers=n

11) Following command to export using compress=y option:

[chow@hyddb exp_bkp]$ exp scott/tiger tables=(emp) file=emp_compress.dmp log=emp_compress.log compress=y

compress=y => when we export a table with compress=y then, the table, when ,imported back into the database will be brought into one single large extent, this will increase the i/o performance on the table.

12) Command to export a single tablespace with all its contents:

[chow@hyddb exp_bkp]$ exp system/manager tablespaces=(users) file=users_ts.dmp log=users_ts.log

13) Command to export multiple tablespaces:

[chow@hyddb exp_bkp]$ exp system/manager tablespaces=(users,userdata) file=users_userdata_ts.dmp log=users_userdata_ts.log 

14) Commands to specify a query:

[chow@hyddb exp_bkp]$ exp scott/tiger tables=emp query=\"where deptno=10\" file=query.dmp log=query.log
[chow@hyddb exp_bkp]$ exp scott/tiger file=emp_query.dmp query='"WHERE deptno = 10 AND sal > 1000"' tables=emp 

Thats it. Hope this article will help you :)

Regards,
Chowdari

Useful EXPDP Commands

Here you can find some useful EXPDP commands:

1) Export FULL database:

    expdp system/manager dumpfile=full.dmp directory=DATA_PUMP_DIR full=y logfile=full.log

2) Export database with multiple dump files:

     In some cases where the Database is in Terabytes and since the dump file size will be larger than the operating system limit, and hence export will fail. In those situations you can create multiple dump files by typing the following command.

     expdp  system/manager FULL=Y DIRECTORY=DATA_PUMP_DIR DUMPFILE=full%U.dmp FILESIZE=5G  LOGFILE=myfullexp.log JOB_NAME=myfullJob
   
     Note: This will create multiple dump files named full01.dmp, full02.dmp, full03.dmp and so on. The FILESIZE parameter specifies how large the dump file should be.

3) Export schema:

     Below command will useful for taking backup of single schema (SCOTT) with using SCOTT credentials:

     expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=scottexp.dmp logfile=scottexp.log

4) Export multiple schemas:

     Below command for taking backup of multiple schemas.

     expdp system/manager dumpfile=scott_demo.dmp directory=DATA_PUMP_DIR schemas=(scott,demo) logfile=scottexp.log

5) Export Table: 

     Below command for taking backup of single table (emp) under scott schema

     expdp scott/tiger dumpfile=scott_emp.dmp directory=DATA_PUMP_DIR tables=(emp) logfile=scottexp.log

6) Export multiple tables:

    Below command for taking backup of multiple tables (emp,dept) under scott schema

    expdp scott/tiger dumpfile=scott_emp_dept.dmp directory=DATA_PUMP_DIR tables=(emp,dept) logfile=scottexp.log

7) Export Tablespaces:

  Below command for taking backup of tablespace (users)
 
    expdp system/manager dumpfile=users_ts.dmp directory=DATA_PUMP_DIR tablespaces=(users) logfile=users_ts.log

8) Export multiple tablespaces:

     Below command for taking backup of multiple tablespaces (users, hr)

    expdp system/manager dumpfile=users_ts.dmp directory=DATA_PUMP_DIR tablespaces=(users, hr) logfile=users_ts.log

9) Export table with data only:

     Below command for taking single table with data only
 
     expdp scott/tiger dumpfile=emp_data.dmp directory=DATA_PUMP_DIR tables=(emp) content=data_only logfile=emp_data.log

10) Export table with metadata only:

       Below command for taking single table with metadata only.
 
       expdp scott/tiger dumpfile=emp_metadata.sql directory=DATA_PUMP_DIR tables=(emp) content=metadata_only logfile=emp_metadata.log

11) Export Full database without logfile:

      expdp system/manager dumpfile=full.dmp directory=DATA_PUMP_DIR full=y nologfile=y

Estimate parameter: 

This parameter will tell us how much space a new export job is going to consume. the space estimation is always in terms of bytes. we can specify the database to provide us with estimates using either number of database blocks or optimizer statistics.

12) Export with parameter estimate (blocks and statistics)

       expdp scott/tiger dumpfile=scott_estimate.dmp directory=data_pump_dir  logfile=scott_estimate.log estimate=blocks

       expdp scott/tiger dumpfile=scott_estimate.dmp directory=data_pump_dir  logfile=scott_estimate.log  estimate=statistics

INCLUDE and EXCLUDE parameters:

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.

13) Export with EXCLUDE and INCLUDE examples:

       Below command for excluding table EMP in SCOTT schema.

       expdp system/manager dumpfile=scott_1.dmp directory=data_pump_dir logfile=scott_1.log schemas=scott exclude=table:"in('EMP')"

       Below command for excluding database objetcs with table EMP in SCOTT schema.

expdp system/manager dumpfile=scott_2.dmp directory=data_pump_dir logfile=scott_2.log schemas=scott exclude=procedure,trigger,function,sequence,index,table:"in('EMP')"

Below command for excluding table which starts with T under SCOTT schema.

expdp scott/tiger directory=data_pump_dir dumpfile=scott_schema.dmp logfile=scott_3.log schemas=scott exclude=table:"like'T%'"

Below command for including table which starts with S under SCOTT schema.

expdp scott/tiger directory=data_pump_dir dumpfile=scott_schema1.dmp logfile=scott_4.log schemas=scott include=table:"like'S%'"

14) Export with QUERY Option: Predicate clause used to export a subset of a table.

       Below command will useful for export only pirticular records with using QUERY option

expdp scott/tiger QUERY=emp:'"WHERE deptno = 10 AND sal > 10000"' DIRECTORY=data_pump_dir DUMPFILE=exp1.dmp logfile=scott_5.log
 
Go through with my previous articles related to Datapump:

impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037
ORA-31685 Error while import using IMPDP
Script: Shell Script to export Full DB or Single or Multiple Schemas
Export Logical Database Backup Dumps to ASM Disk

Hope this article will help you.  :-)

Thanks,
Chowdari

impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037

When I am trying to import schema, The impdp is failed to open dump file. see below script I am using.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'

I got below error while import..

ORA-31693: Table data object "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P297" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp" for read
ORA-19505: failed to identify file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P315"      265.3 MB 1484775 rows
. . imported "SMARTSUPPY_SCHEMA"."TRC_HOP_T_90000":"P139"      219.7 MB 1402774 rows

The issue is due to I am using PARALLEL=10, so the dump file directory not being accessible from all nodes in the RAC. When we use PARALLEL > 1 option the child thread will be started in other node to complete the job faster, when job started in other node the dump file can't access the file. Due to this we receive this error. To fix the issue either you place the dump file to be accessed from all the nodes in that RAC environment or use cluster=N option.

Added cluster=N parameter in parfile and started the import again.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'
CLUSTER=N
$

$ nohup impdp parfile=parfile.par &
[1] 27620
> nohup: ignoring input and appending output to `nohup.out'
$
$ jobs -l
[1]+ 27620 Running                 nohup impdp parfile=parfile.par &
$

$ tail -f SMARTSUPPY_SCHEMA_09nov2014.log
Master table "SYS"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_03":  /******** AS SYSDBA parfile=parfile.par
Processing object type SCHEMA_EXPORT/USER
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P475"  1.956 GB 6711857 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P480"  1.965 GB 6794127 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P474"  1.958 GB 6727517 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P479"  1.921 GB 6616816 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P478"  1.903 GB 6512491 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P481"  1.909 GB 6618578 rows

Hope this will help you.. :)

Best Regards,
Chowdari

ORA-31685 Error while import using IMPDP

Got a ORA-31685 error on schema import with remap option.

ORA-31685: Object type REF_CONSTRAINT:"DIGITAL"."FK_SEVERITY" failed due to insufficient privileges.
Failing sql is: ALTER TABLE "DIGITAL"."DIGITAL_ALLERGYGRPTRANSACTION" ADD CONSTRAINT "FK_SEVERITY" FOREIGN KEY ("SEVERITYID") REFERENCES "EHIS"."ALLERGYSEVERITYMASTER" ("ALLERGYSEVERITYID") ENABLE

I tried in many ways to create FOREIGN KEY but still its showing insufficient privileges error. Finally I get to know that its below grant issue.

grant REFERENCES on EHIS.ALLERGYSEVERITYMASTER to DIGITAL;

Hope this will help you.

Best regards,

Export Logical Database Backup Dumps to ASM Disk

I have created both directory and folder within the ASM diskgroup.

bash-3.2$ echo $ORACLE_HOME
/oracle/grid_home
bash-3.2$
bash-3.2$ echo $ORACLE_SID
+ASM1

bash-3.2$ asmcmd -p
ASMCMD [+] >

ASMCMD [+] > ls
DATA/
FRA/

ASMCMD [+] > cd FRA
ASMCMD [+FRA] >

ASMCMD [+FRA] > mkdir dpdump
ASMCMD [+FRA] >

ASMCMD [+FRA] > cd dp*
ASMCMD [+FRA/dpdump] >

ASMCMD [+FRA/dpdump] > pwd
+FRA/dpdump
ASMCMD [+FRA/dpdump] >

bash-3.2$ sqlplus / as sysdba

SQL> create directory test_dir as '+FRA/dpdump';

Directory created.

SQL>
SQL> select * from dba_directories;

OWNER          DIRECTORY_NAME  DIRECTORY_PATH
--------------   --------------                ----------------------------------
SYS                TEST_DIR                     +FRA/dpdump
SYS                DATA_PUMP_DIR        /U04/dpdump

SQL>

bash-3.2$
bash-3.2$ expdp directory=TEST_DIR dumpfile=test_full.dmp logfile=test_full.log full=y exclude=statistics content=metadata_only

Export: Release 11.2.0.2.0 - Production on Thu Sep 26 15:43:15 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

bash-3.2$

Here we have seen an error "unable to open log file".

So now the gimmick is, to use existing directory, such as DATA_PUMP_DIR which is placed on a local filesystem and point the logfile to that directory. Using the syntax 'logfile=directory:logfile'. An alternative option is to use the expdp parameter to nologfile=yes

bash-3.2$
bash-3.2$ expdp directory=TEST_DIR dumpfile=test_full.dmp logfile=DATA_PUMP_DIR:test_full.log full=y exclude=statistics content=metadata_only

Export: Release 11.2.0.2.0 - Production on Thu Sep 26 15:48:48 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_05":  /******** AS SYSDBA directory=TEST_DIR dumpfile=test_full.dmp logfile=DATA_PUMP_DIR:test_full.log full=y exclude=statistics content=metadata_only
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
.
.
.
Dump file set for SYS.SYS_EXPORT_FULL_05 is:
  +FRA/dpdump/test_full.dmp
Job "SYS"."SYS_EXPORT_FULL_05" successfully completed at 16:00:52
bash-3.2$

Hope this help you.

Best Regards,