Wednesday, November 12, 2008

IMPDP fails with ORA-39002, ORA-29283: invalid file operation

Problem Description
My impdp operation fails with error ORA-39002, ORA-39070, ORA-29283, ORA-29283: invalid file operation as below.
SQL> host impdp cr_3/cr_3 remap_schema=cr_2:cr_3 dumpfile=skydbcr_2-04-10-2008.dmp directory=e

Import: Release 10.2.0.1.0 - Production on Wednesday, 12 November, 2008 16:42:27

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining 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 475
ORA-29283: invalid file operation

Cause and Solution of the Problem
1. Check whether the user who run the expdp operation has READ,WRITE permission on the database directory. If not then as a dba user run grant permission on cr_3,
GRANT READ,WRITE ON DIRECTORY E TO CR_3;

2. Check whether the path used in database directory physically exists on the OS.
Whenever you create a directory oracle does not check existence physical directory on OS. The checking is done on runtime.

3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed. On unix issue,
ls -l name_of_the_file and see permission. If not then using chmod grant read, write access to the file to oracle user.
On windows if NTFS file system then you can change it by right clicking on the file or folder and then select sharing and security option.

4. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle. In such case using chown change the ownership of the file on unix. On windows if NTFS file system then you can change it by right clicking on the file or folder and then select sharing and security option.

1 comment: