Home > Others

EXPDP Export Error ORA-39002, ORA-39070 solution

Source: Internet
Author: User
Tags create directory reserved
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
Oracle@centos5 ~]$ EXPDP gys directory= dmp_dir dumpfile=stream.dmp schemas=stream

Export:release 10.2.0.4.0-production on Wednesday October, 2013 17:51:37

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

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-39002:invalid operation
Ora-39070:unable to open the log file.
Ora-29283:invalid file operation
Ora-06512:at "SYS. Utl_file ", line 488
Ora-29283:invalid file operation
Started thinking it was a dmp_dir permission issue, recreate the directory database object under test.
sql> Col OWNER format A6
Sql> Col Directory_name for A20
Sql> Col Directory_path for A30
Sql> select * from Dba_directories;

OWNER Directory_name Directory_path
------ -------------------- ------------------------------
SYS data_pump_dir/u01/oracle/db_1/rdbms/log/
SYS Stream/u01/testdata/dump
SYS oracle_ocm_config_di/u01/oracle/db_1/ccr/state
R
SYS Dmp_dir/u01/testdata/dump

sql> Drop directory Dmp_dir;

Directory dropped.

Sql> select * from Dba_directories;

OWNER Directory_name Directory_path
------ -------------------- ------------------------------
SYS data_pump_dir/u01/oracle/db_1/rdbms/log/
SYS Stream/u01/testdata/dump
SYS oracle_ocm_config_di/u01/oracle/db_1/ccr/state
R
Dmp_dir has been deleted

Re-create
Sql> Create directory Dmp_dir as '/u01/testdata/dump ';

Directory created.
Sql> Grant Read,write on directory Dmp_dir to stream;

Grant succeeded.
Sql>!
[Oracle@centos5 ~]$ EXPDP stream directory= dmp_dir dumpfile=stream.dmp

Export:release 10.2.0.4.0-production on Wednesday October, 2013 17:51:37

Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:
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-39002:invalid operation
Ora-39070:unable to open the log file.
Ora-29283:invalid file operation
Ora-06512:at "SYS. Utl_file ", line 488
Ora-29283:invalid file operation

The error is still ...
Think that the directory where the dump was created is not Linux under which the Oracle user does not have permission to this directory, causing the system to not write data to the directory.
Go ...
[Oracle@centos5 u01]$ LS-LRT
Total 28
Drwxr-xr-x 3 Oracle Oinstall 4096 Aug 12:41 Oracle
DRWXRWX---6 oracle oinstall 4096 Aug 13:01 orainventory
Drwxr-x---2 oracle oinstall 4096 Oct 09:26 Flash_recovery_area
Drwxr-x---3 oracle oinstall 4096 Oct 12:47 oradata
Drwxr-x---3 oracle oinstall 4096 Oct 12:47 admin
Drwxr-xr-x 2 Oracle Oinstall 4096 Oct 14:47 Orabak
DRWXRWXRWX 3 Oracle Oinstall 4096 Oct 17:15 testdata
[Oracle@centos5 u01]$ Cd/u01/testdata
[Oracle@centos5 testdata]$ LS-LRT
Total 4
Drwxr-xr-x 2 root root 4096 Oct 17:15 dump
Problem found, dump's user right is root Oracle does not have permissions on this directory
[Oracle@centos5 ~]$ Su-root
Password:
[Root@centos5 ~]# Cd/u01/testdata
[Root@centos5 testdata]# chown-r Oracle:oinstall *
[Root@centos5 testdata]# LS-LRT
Total 4
Drwxr-xr-x 2 Oracle Oinstall 4096 Oct 17:15 Dump
Modify the directory owner.
[Root@centos5 testdata]# Su-oracle
[Oracle@centos5 ~]$ EXPDP stream directory= dmp_dir dumpfile=stream.dmp

Export:release 10.2.0.4.0-production on Wednesday October, 2013 17:58:05

Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:
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
Starting "Gys". Sys_export_schema_01 ": gys/******** directory= dmp_dir dumpfile=gys/********.dmp schemas=gys/********
Estimate in progress using BLOCKS method ...
Processing Object Type Schema_export/table/table_data
Total estimation using BLOCKS method:1.489 GB
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/pre_schema/procact_schema
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/index/index
Processing Object Type Schema_export/table/constraint/constraint
Processing Object Type Schema_export/table/index/statistics/index_statistics
. . Exported "STREAM". Tabwsale "1.109 GB 11853316 rows
. . Exported "STREAM". Tabshiftflow "33.49 MB 365378 rows
. . Exported "STREAM". Tabshiftreport "32.32 MB 406880 rows
. . Exported "STREAM". Tabsweight "8.763 MB 148977 rows
. . Exported "STREAM". Tabvtimeout "7.701 MB 239696 rows
. . Exported "STREAM". Tablog "5.226 MB 92782 rows
. . Exported "STREAM". Tabfindayreport "3.901 MB 35089 rows
. . Exported "STREAM". Tabsaledayreport "3.278 MB 29743 rows
. . Exported "STREAM". Tabpassportreport "1.773 MB 48059 rows
. . Exported "STREAM". Tabriskvrecord "2.119 MB 23010 rows
. . Exported "STREAM". Tabdestroy "1.494 MB 34739 rows
. . Exported "STREAM". Tabfindayreport_old "1.028 MB 9218 rows
. . Exported "STREAM". Tabpstock "1.476 MB 34951 rows
. . Exported "STREAM". Tabveclib "789.4 KB 18425 rows
. . Exported "STREAM". Tabquarters "788.2 KB 17181 rows
. . Exported "STREAM". Tabticketdestroy "9.031 KB 2 rows
. . Exported "STREAM". TABBHBW "113.3 KB 2762 rows
. . Exported "STREAM". Tabprintplan "143.7 KB 1830 rows
. . Exported "STREAM". TABBHB "94.88 KB 2670 rows
. . Exported "STREAM". Tabbhbs "109.7 KB 2762 rows
. . Exported "STREAM". Tabcheckuser "8.406 KB rows
. . Exported "STREAM". Tabclassorder "5.664 KB 9 rows
. . Exported "STREAM". Tabclientinfo "8.398 KB rows
. . Exported "STREAM". Tablane "7.703 KB 6 rows
. . Exported "STREAM". Tabqueryhand "74.53 KB 1366 rows
. . Exported "STREAM". Tabuser "8.992 KB rows
. . Exported "STREAM". Tabwtollrate "10.86 KB rows
. . Exported "STREAM". Tabconfig "7.507 KB rows
. . Exported "STREAM". Tabcontract "17.36 KB rows
. . Exported "STREAM". TABDEP "6.265 KB 6 rows
. . Exported "STREAM". Tabdestroyserial "11.07 KB 178 rows
. . Exported "STREAM". Tabemployee "17.85 KB rows
. . Exported "STREAM". Tabfunc "10.20 KB rows
. . Exported "STREAM". Tabinstore "7.039 KB rows
. . Exported "STREAM". Tabmonthtoll "15.20 KB 131 rows
. . Exported "STREAM". Tabpowerassign "17.80 KB 612 rows
. . Exported "STREAM". Tabriskconfig "12.03 KB 152 rows
. . Exported "STREAM". Tabryznb "5.273 KB 3 rows
. . Exported "STREAM". Tabshiperman "11.71 KB 127 rows
. . Exported "STREAM". Tabshipproperty "9.898 KB rows
. . Exported "STREAM". Tabspecialvrecord "16.92 KB rows
. . Exported "STREAM". Tabstation "9.031 KB 2 rows
. . Exported "STREAM". Tabsynccheck "5.351 KB 5 rows
. . Exported "STREAM". Tabtimeflag "5.578 KB 3 rows
. . Exported "STREAM". Tabuse "9.578 KB $ rows
. . Exported "STREAM". Tabverifyrecord "15.48 KB 386 rows
. . Exported "STREAM". Plan_table "0 KB 0 rows
. . Exported "STREAM". Tabemptyship "0 KB 0 rows
. . Exported "STREAM". Tabticketrepeatscan "0 KB 0 rows
. . Exported "STREAM". Tabweightvflow "0 KB 0 rows
Master table "Gys". Sys_export_schema_01 "Successfully loaded/unloaded
******************************************************************************
Dump file set for Gys. SYS_EXPORT_SCHEMA_01 is:
/u01/testdata/dump/stream.dmp

Job "Gys". Sys_export_schema_01 "successfully completed at 18:05:16


==========================================================================================

oracle11g Export the database with exp, the empty table is not available, so you must import the Oracle database with data pump.

first, export data with EXPDP

1, creating directory syntax:sql> Create directory directory name (such as: DMPBX) as storage address (such as: '/root/usr/... ');
For example:sql> Create directory Dmpbx as ' D:\backup\ ';
2, to the directory to assign permissions
Syntax:sql> grant read,write on directory name to public;
For example:sql> Grant Read,write on DIRECTORYDMPBX to public;
Sql> exit

3. Execute EXPORT statement

Syntax: [oracle@data ~] $EXPDP username 1/Password @ instance dumpfile= backup file name (ecology.dmp) directory= directory name (DMPBX) For example: [Oracle@data ~]$ expdpuser1/ Pass1 dumpfile=data20140805.dmp Directory=dmpbx

Second, import use IMPDP

1, creating directory syntax:sql> Create directory directory name (such as: DMPBX) as storage address (such as: '/root/usr/... ');
For example:sql> Create directory Dmpbx as ' D:\Oracle\ ';
2, to the directory to assign permissions
Syntax:sql> grant read,write on directory name to public;
For example:sql> grant Read,write on directory Dmpbx to public;
Sql> exit

3. Execute import statement syntax: IMPDP username 2/Password @ instance directory= directory name dumpfile= backup file name. DMP logfile= log name. log remap_schema= username 1: User Name 2
For example: IMPDP user2/pass2 directory= dmpbx dumpfile= data20140805. DMP logfile= 20140805.log remap_schema= User1:user2

Note: 1, directory creation only need to create once on the line

2. If the import and export are operated on the same machine, then the steps to create the directory and the weights before exporting can be omitted.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Related Keywords:
ora 03114 not connected to oracle solution 302 found error solution error ora 03113 end of file on communication channel rman 04006 error from auxiliary database ora 12514 error at line 1 ora 01012 not logged on ora 00921 ora 03106

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

What's Trending
Top 10 Tags
datastax versions naming convention zookeeper client class definition md5 microsoft sql server 2005 data structures exception handling error handling
Top 10 Keywords
microsoft download center down wordpress address url site address url wordpress address url windows installer 4 0 download 302 not found web address url definition site address url wordpress db2 integer mac os installation step by step pdf abbreviation for return

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More