What a disaster this is with 12c full database export and ORACLE_MAINTAINED objects.
Somehow, oracle thinks MDINH is an ORACLE_MAINTAINED user and user MDINH does not get exported.
———
Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655 (Doc ID 2114233.1)
There are certain system generated schemas that are not exportable using exp or expdp because they contain Oracle-managed data and metadata. SYS, MDSYS, and ORDSYS are some examples.
———
|
From Blog about (ORACLE_MAINTAINED Column) DEMO was ORACLE_MAINTAINED user as well.
For testing purposes, I dropped and recreated the user to test export which removed ORACLE_MAINTAINED flag.
Still, this does not explained how MDINH came to be ORACLE_MAINTAINED user.
Has anyone experience this and has a solution or work around?
test:(SYS@test):PRIMARY> select username,oracle_maintained from dba_users order by 1;
USERNAME O
-------------------- -
ANONYMOUS Y
APPQOSSYS Y
AUDSYS Y
DBSNMP Y
DEMO N
DIP Y
GSMADMIN_INTERNAL Y
GSMCATUSER Y
GSMUSER Y
MDINH Y
OJVMSYS Y
ORACLE_OCM Y
OUTLN Y
SYS Y
SYSBACKUP Y
SYSDG Y
SYSKM Y
SYSTEM Y
TEST1 N
TESTING N
XDB Y
XS$NULL Y
22 rows selected.
test:(SYS@test):PRIMARY>
++++++++++
$ expdp parfile=expdp_full.par
Export: Release 12.1.0.2.0 - Production on Sat Aug 27 21:38:15 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** parfile=expdp_full.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Estimated 1 TABLE_DATA objects in 2 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Estimated 16 TABLE_DATA objects in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Estimated 9 TABLE_DATA objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimated 0 TABLE_DATA objects in 1 seconds
Total estimation using BLOCKS method: 2.125 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/TABLESPACE
Completed 4 TABLESPACE objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
Completed 1 USER objects in 0 seconds
*** There are 4 users but only 3 are exported.
Processing object type DATABASE_EXPORT/SCHEMA/USER
Completed 3 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/RADM_FPTM
Completed 1 RADM_FPTM objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Completed 6 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Completed 2 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Completed 4 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Completed 6 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Completed 7 ON_USER_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Completed 1 TRUSTED_DB_LINK objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Completed 1 SYNONYM objects in 5 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Completed 2 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed 3 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Completed 23 PROCOBJ objects in 5 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed 3 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed 11 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Completed 1 TABLE objects in 5 seconds
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 2 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Completed 16 TABLE objects in 16 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Completed 9 TABLE objects in 26 seconds
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Completed MARKER objects in seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Completed MARKER objects in seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Completed MARKER objects in seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Completed MARKER objects in seconds
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Completed 3 PROCACT_SCHEMA objects in 2 seconds
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Completed 2 AUDIT_POLICY_ENABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 0 seconds
ORA-31693: Table data object "SYS"."KU$_USER_MAPPING_VIEW" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
. . exported "SYS"."AUD$" 22.57 KB 0 rows in 0 seconds
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 7.187 KB 0 rows in 0 seconds
. . exported "SYS"."DAM_CLEANUP_JOBS$" 7.171 KB 0 rows in 0 seconds
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows in 0 seconds
. . exported "SYS"."TSDP_ASSOCIATION$" 5.898 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_CONDITION$" 5.890 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_FEATURE_POLICY$" 5.906 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows in 0 seconds
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows in 0 seconds
. . exported "SYS"."TSDP_PROTECTION$" 6.320 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 8.437 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 6.320 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_SOURCE$" 6.312 KB 0 rows in 0 seconds
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows in 0 seconds
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows in 0 seconds
. . exported "SYSTEM"."REDO_LOG" 26.34 KB 0 rows in 0 seconds
ORA-31693: Table data object "SYS"."FGA_LOG$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 8.390 KB 0 rows in 1 seconds
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.523 KB 12 rows in 0 seconds
ORA-31693: Table data object "SYS"."AUDTAB$TBS$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_SENSITIVE_DATA" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_TSDP_POLICY_PROTECTION" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_ACE_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_HOST_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_WALLET_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
Completed 0 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
Completed 16 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 0 seconds
Completed 2 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 1 seconds
Completed 0 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 0 seconds
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/oradata/dpump/expdp_full.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 8 error(s) at Sat Aug 27 21:39:50 2016 elapsed 0 00:01:34
++++++++++
$ impdp parfile=impdp_full.par
Import: Release 12.1.0.2.0 - Production on Sat Aug 27 21:43:12 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA parfile=impdp_full.par
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
Completed 4 TABLESPACE objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
Completed 3 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/RADM_FPTM
Completed 1 RADM_FPTM objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Completed 6 PROC_SYSTEM_GRANT objects in 2 seconds
Looks like MDINH was not exported.
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'MDINH' does not exist
Failing sql is:
GRANT CREATE SYNONYM TO "MDINH"
Completed 2 SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'MDINH' does not exist
Failing sql is:
GRANT "RESOURCE" TO "MDINH"
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'MDINH' does not exist
Failing sql is:
GRANT "CONNECT" TO "MDINH"
Completed 4 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
ORA-39083: Object type DEFAULT_ROLE:"MDINH" failed to create with error:
ORA-01918: user 'MDINH' does not exist
Failing sql is:
ALTER USER "MDINH" DEFAULT ROLE ALL
Completed 6 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
ORA-39083: Object type ON_USER_GRANT failed to create with error:
ORA-31625: Schema MDINH is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
GRANT INHERIT PRIVILEGES ON USER "MDINH" TO "PUBLIC"
Completed 7 ON_USER_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"PUBLIC"."MORE_RECS_TBL" already exists
Completed 1 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
ORA-39083: Object type SYNONYM:"MDINH"."TCUSTMER" failed to create with error:
ORA-01917: user or role '' does not exist
Failing sql is:
CREATE EDITIONABLE SYNONYM "MDINH"."TCUSTMER" FOR "DEMO"."TCUSTMER"
ORA-39083: Object type SYNONYM:"MDINH"."TCUSTORD" failed to create with error:
ORA-01917: user or role '' does not exist
Failing sql is:
CREATE EDITIONABLE SYNONYM "MDINH"."TCUSTORD" FOR "DEMO"."TCUSTMER"
Completed 2 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
Completed 3 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Completed 23 PROCOBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed 3 PROCACT_SYSTEM objects in 25 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema MDINH is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'CI2', inst_scn=>'806486');COMMIT; END;
Completed 11 PROCACT_SCHEMA objects in 1 seconds
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Completed 1 TABLE objects in 0 seconds
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Completed 16 TABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYS"."AMGT$DP$AUD$" 22.57 KB 0 rows in 0 seconds
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 7.187 KB 0 rows in 0 seconds
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 7.171 KB 0 rows in 0 seconds
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows in 0 seconds
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 5.898 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_CONDITION$" 5.890 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 5.906 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows in 0 seconds
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows in 0 seconds
. . imported "SYS"."DP$TSDP_PROTECTION$" 6.320 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 8.437 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 6.320 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SOURCE$" 6.312 KB 0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows in 0 seconds
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows in 0 seconds
. . imported "SYSTEM"."REDO_LOG_TMP" 26.34 KB 0 rows in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
ORA-39342: Internal error - failed to import internal objects tagged with SCHEDULER due to ORA-00910: specified length too long for its datatype.
ORA-39083: Object type TABLE:"SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to create with error:
ORA-00910: specified length too long for its datatype
Failing sql is:
CREATE TABLE "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" ("OWNER" VARCHAR2(128 BYTE) NOT NULL ENABLE, "PROGRAM_NAME" VARCHAR2(128 BYTE) NOT NULL ENABLE, "ARGUMENT_NAME" VARCHAR2(128 BYTE), "ARGUMENT_POSITION" NUMBER NOT NULL ENABLE, "ARGUMENT_TYPE" VARCHAR2(257 BYTE), "METADATA_ATTRIBUTE" VARCHAR2(19 BYTE), "DEFAULT_VALUE" VARCHAR2(32767 BYTE), "DEFA
ORA-39083: Object type TABLE:"SYSTEM"."SCHEDULER_JOB_ARGS_TMP" failed to create with error:
ORA-00910: specified length too long for its datatype
Failing sql is:
CREATE TABLE "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" ("OWNER" VARCHAR2(128 BYTE), "JOB_NAME" VARCHAR2(128 BYTE), "ARGUMENT_NAME" VARCHAR2(128 BYTE), "ARGUMENT_POSITION" NUMBER, "ARGUMENT_TYPE" VARCHAR2(257 BYTE), "VALUE" VARCHAR2(32767 BYTE), "ANYDATA_VALUE" "SYS"."ANYDATA" , "OUT_ARGUMENT" VARCHAR2(5 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
Completed 9 TABLE objects in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
ORA-39342: Internal error - failed to import internal objects tagged with AUDIT_TRAILS due to ORA-01403: no data found
ORA-01403: no data found.
Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Completed 3 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Completed 2 AUDIT_POLICY_ENABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Completed 1 MARKER objects in 0 seconds
Completed 16 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 1 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 16 error(s) at Sat Aug 27 21:43:55 2016 elapsed 0 00:00:41