ORA-14102: only one LOGGING or NOLOGGING clause may be specified
ORA-14102: only one LOGGING or NOLOGGING clause may be specified
Problem: when impdp recovers the database, the error in importing the index is as follows:
ORA-39083: Object type INDEX failed to create with error:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified
Failing sql is:
CREATE INDEX "chenjch_20180614". "CAL_CHEN_MATIAL" ON "chenjch_20180614". "T_CL_CHEN" ("aaa") PCTFREE 10 INITRANS 2 MAXTRANS 255NOLOGGING NOCOMPRESS LOGGING TABLESPACE "chenjch_D_chen_INDEX" PARALLEL 1
ORA-39083: Object type INDEX failed to create with error:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified
-sensitive information has been processed
The official document is explained as follows:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified
Cause: LOGGING was specified more than once, NOLOGGING was specified more than once, or both LOGGING and NOLOGGING were specified.
Action: Remove all but one of the LOGGING or NOLOGGING clauses and reissue the statement.
The cause of the problem:
The syntax of the individual indexed DDL statements generated by Oracle is problematic, including both NOLOGGING and LOGGING, which contradict each other, obviously caused by Oracle BUG
The database version, Oracle 11.2.0.1.0 for windows, is a version with a large number of BUG, and colleagues who have previously maintained it have not made any patches.
Solution:
If only a small number of indexes have this problem, you can recreate it manually
But if there are too many wrong objects, it can only be solved by patching.
A higher version of the Opatch tool is required:
You can download the p6880880_112000_MSWIN-x86-64.zip patch tool on MOS
The patch for ORA-14102 is p8795792_112010_Generic.zip
Or type the latest opatch (p12429529_112010_MSWIN-x86-64.zip) of version 11201 directly.
After patching, re-impdp found that there was still the same error
You also need to execute the following statement:
Exec SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS
After the execution is completed, import the impdp again, and everything will be fine.
Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!
