I want to be able to create an export using datapump, as of a specific SCN, for use with GoldenGate, so I can use this for initial load during implementation.
The issue I am running into is this:
I do the following:
1. On my 11.2.0.4 RAC instance on exadata using Oracle Linux, I run this query:
col GET_SYSTEM_CHANGE_NUMBER format 999999999999999
select dbms_flashback.get_system_change_number, to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') from dual;
GET_SYSTEM_CHANGE_NUMBER TO_CHAR(SYSDATE,'YY
------------------------ -------------------
244313877842 2014-02-05:18:49:52
2. I put this scn in an expdp parameter file:
DIRECTORY=DUMP_DIR
CONTENT=ALL
DUMPFILE=DCOGS1_NSE_PUB.dmp
LOGFILE=DCOGS1_NSE_PUB.log
FLASHBACK_SCN=244313877842
REUSE_DUMPFILES=Y
SCHEMAS=NSE_PUB
3. I run the export a minute or two later and get:
ORA-31693: Table data object "NSE_PUB"."PORTAL_STAT_CPCODE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08186: invalid timestamp specified
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1
(repeated for every non-empty table in schema NSE_PUB, empty tables export fine)
What I have found is that I get this inconsistently. It's repeatable when it occurs and then the next day this process works fine.
How can I diagnose this issue? What is happening here? Why would data be unavailable as of an SCN I just got from the database?
Thanks
Sandra