Use of Expdp Impdp nologfile=y

Oracle data pump has one option called nologfile and by default its value is N. The purpose of this option is that if you specify the value for this parameter as Y then it will not create the log file for the export or import activity. This is very useful option when you do an import where the oracle user doesn’t have write privilege on the directory where the dump file exists. In such cases the impdp will throw the following error.

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

You can use the following syntax to avoid such kind of errors.
impdp dumpfile=emp.dmp directory=exp_dir nologfile=y full=y

Secondly, if you really don’t need a logfile to be created during the export or import you can set the value as Y

See one example here
$ impdp dumpfile=emp.dmp directory=exp_dir  full=y
 
Import: Release 11.2.0.3.0 - Production on Fri Jun 20 08:42:58 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: scott
Password:
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
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

$ impdp dumpfile=emp.dmp directory=exp_dir  full=y nologfile=y
Import: Release 11.2.0.3.0 - Production on Fri Jun 20 08:43:44 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** dumpfile=emp.dmp directory=exp_dir full=y nologfile=y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                             8.578 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 08:43:49
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google