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.
