I face with this error message while I am doing import process on 11g database.

My source db version is 10g( 10.2.0.4.3 ) and OS is AIX 6.1 and my target db version is 11g RAC( 11.2.0.4.6 ) and OS is AIX 7.1.

The errors:
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string

From related MOS document:

The problem is that DBMS_PSWMG_IMPORT.IMPORT_HISTORY expects passwords to be in ‘yyyy/mm/dd’ format.

The issue is discussed in related bugs
Bug 13039027 – IPB01D : IMPDP ORA-39083: OBJECT TYPE PASSWORD_HISTORY FAILED TO CREATE WI
Bug 14521182 – DATAPUMP IMPORT PASSWORD_HISTORY ORA-39083,ORA-01847,ORA-01861
which are both closed with status ‘Not a Bug’.

For your information, because of security reasons I changed my schema name as SCOOT in below logs.

Complete logs here:
Master table “SYS”.”SYS_IMPORT_FULL_04″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_04″: “/******** AS SYSDBA” directory=EXPDP_DIR dumpfile=SCOOT%U.dmp PARALLEL=8 logfile=impdp_SCOOT.log cluster=N DATA_OPTIONS=DISABLE_APPEND_HINT
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”SCOOT” already exists
ORA-31684: Object type USER:”SCOOTUSR1″ already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string
Failing sql is:
DECLARE SUBTYPE HIST_RECORD IS SYS.DBMS_PSWMG_IMPORT.ARRAYOFHISTORYRECORDS; HIST_REC HIST_RECORD; i number := 0; BEGIN i := i+1; HIST_REC(i).USERNAME := ‘SCOOT’; HIST_REC(i).PASSWORD := ‘F99CA5C8EC2F8836′; HIST_REC(i).PASSWD_DATE := ’16-03-2015 15:03:34’; i := i+1; HIST_REC(i).USERNAME := ‘SCOOT’; HIST_REC(i).PASSWORD := ’98F7AE6C84E7A2E8′; HIST_REC(i).PASSWD_DATE :=
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

So here is the solutions:

1. At 10g source environment, first set the NLS_DATE_FORMAT environment variable:

$ export NLS_DATE_FORMAT=YYYY/MM/DD HH24:MI:SS

PS: If you are using AIX than you may hit this error message:
ksh: HH24:MI:SS: is not an identifier

If you hit this message you can use below syntax:

$ export NLS_DATE_FORMAT=”YYYY/MM/DD HH24:MI:SS”

verify:
$ echo $NLS_DATE_FORMAT
YYYY/MM/DD HH24:MI:SS

2. Re-run the Datapump export.

3. Perform the Datapump import into the 11g database using the new dump file.

Reference:
ORA-39083 and ORA-06502 during IMPDP for PASSWORD_HISTORY (Doc ID 1553898.1)
DataPump Import (IMPDP) Fails With Errors ORA-39083 ORA-6550 PLS-00103 On Object Type PASSWORD_HISTORY [ID 1053162.1]

Advertisements