Thursday, October 22, 2015

ORA-39070: Unable to open the log file during EXPDP in RAC ASM

DB version: 11.2.0.4 RAC 2 node
Os version: RHEL 6

SQL> create directory EXP_DIR as '+FRADG/EXP';

Directory created.

SQL> grant read,write on directory EXP_DIR to sys;

Grant succeeded.

[oracle@oraclehost]$ expdp directory=EXP_DIR dumpfile=STDBY_FULL_metadata.dmp logfile=exp_STDBY_FULL_metadata.log full=y content=metadata_only

Export: Release 11.2.0.4.0 - Production on Sat Oct 10 23:39:03 2015

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
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


Solution:

You cannot put logfiles in asm directory so specify another directory for logfile file or use nologfile=y

Ex:
 logfile= EXP: exp_STDBY_FULL_metadata.log    à where ‘EXP’ is the directory in my local filesystem
 (or)
 Nologfile=y     à This does not create any Logfile at all

Ref:
DataPump Export (EXPDP) To ASM Directory Fails With Errors ORA-6512 At SYS.UTL_FILE (Doc ID 1464897.1)

ORA-01466: unable to read data - table definition has changed


DB version: 11.2.0.4
OS: RHEL 6

My export command:

expdp directory=EXP_DIR dumpfile=SBX_STDBY _%U.dmp logfile=EXP:exp_SBX_STDBY.log schemas=SCHEMA_NAME flashback_scn=5424088950 parallel=4 reuse_dumpfiles=y cluster=N

 ORA-31693: Table data object "SCHEMA"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed


Reason:

There is a DDL operation performed on this table causing the error.
Here i gave SCN number for an object before its LST_DDL_TIME 

Ex:
SQL> select object_name,to_timestamp(last_ddl_time) from dba_objects where owner=’SCHEMA_NAME’  and object_name=’TABLE_NAME’;

OBJECT_NAME
--------------------------------------------------------------------------------
TO_TIMESTAMP(LAST_DDL_TIME)
---------------------------------------------------------------------------
TDFM_EVT
15-OCT-15 12.00.00 AM


SQL> select SCN_TO_TIMESTAMP(5424088950) from dual;

SCN_TO_TIMESTAMP(5424088950)
---------------------------------------------------------------------------
15-OCT-15 08.44.33.000000000 PM


Solution:

Remove FLASHBACK_SCN  parameter and rerun  (or)
Specify SCN after the time “LST_DDL_TIME” and start again …..


Ref:

EXPDP - How to Solve "ORA-01466: unable to read data - table definition has changed" (Doc ID 1902481.1)


Auto Scroll Stop Scroll