ORACLE_MAINTAINED Set From “_ORACLE_SCRIPT” parameter

A huge thank you to Pete Finnigan for his blog post.

Hacking Oracle 12c COMMON Users

Not only is the implementation of ORACLE_MAINTAINED not ideal, it’s a possible security risk.

My tip to you is to always exit the session before performing any object creation.

From my pain as you can see, looks like “_ORACLE_SCRIPT was not reset when new users where created.

Also ORACLE_MAINTAINED Objects Don’t Export

Learned quite a few new thing this weekend and can now lay to rest.

test:(SYS@test):PRIMARY> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

test:(SYS@test):PRIMARY> create user newdemo identified by demo;

User created.

test:(SYS@test):PRIMARY> select username,oracle_maintained from dba_users order by 2;

USERNAME             O
-------------------- -
TEST1                N
MDINH                N
TESTING              N
DEMO                 N
NEWDEMO              N
GSMUSER              Y
AUDSYS               Y
ANONYMOUS            Y
DBSNMP               Y
XDB                  Y
APPQOSSYS            Y
GSMADMIN_INTERNAL    Y
SYSBACKUP            Y
OUTLN                Y
SYSDG                Y
SYSKM                Y
SYSTEM               Y
XS$NULL              Y
OJVMSYS              Y
ORACLE_OCM           Y
DIP                  Y
SYS                  Y
GSMCATUSER           Y

23 rows selected.

test:(SYS@test):PRIMARY> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

test:(SYS@test):PRIMARY> create user ndemo identified by demo;

User created.

test:(SYS@test):PRIMARY> select username,oracle_maintained from dba_users order by 2;

USERNAME             O
-------------------- -
DEMO                 N
MDINH                N
TEST1                N
NEWDEMO              N
TESTING              N
GSMUSER              Y
SYSKM                Y
XS$NULL              Y
OJVMSYS              Y
APPQOSSYS            Y
ORACLE_OCM           Y
XDB                  Y
DBSNMP               Y
SYSDG                Y
DIP                  Y
OUTLN                Y
ANONYMOUS            Y
SYSBACKUP            Y
AUDSYS               Y
NDEMO                Y
GSMADMIN_INTERNAL    Y
GSMCATUSER           Y
SYS                  Y
SYSTEM               Y

24 rows selected.

test:(SYS@test):PRIMARY>

Advertisements

ORACLE_MAINTAINED Objects Don’t Export

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

To CDB or NOT To CDB (ORACLE_MAINTAINED Column)

I recently discovered about column ORACLE_MAINTAINED added to DBA views and got excited over nothing.

ORACLE_MAINTAINED from DBA_ROLES
Denotes whether the role was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql).
A role for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

STOP HERE:

Please read post ORACLE_MAINTAINED Set From “_ORACLE_SCRIPT” parameter to the real reasons (I must have messed up somewhere).

Updated: the statements below are not correct.

It looks like ORACLE_MAINTAINED is only valid for CDB and seems as if Oracle is really wanting all databases to go to CDB.

For Non-CDB, ORACLE_MAINTAINED value is ‘Y’ even when not.

test:(MDINH@test):PRIMARY> select name,cdb from v$database;

NAME                           CDB
------------------------------ ---
test                            NO

1 row selected.

test:(MDINH@test):PRIMARY> select username,oracle_maintained from dba_users order by 1;

USERNAME             O
-------------------- -
ANONYMOUS            Y
APPQOSSYS            Y
AUDSYS               Y
DBSNMP               Y
DEMO                 Y
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
XDB                  Y
XS$NULL              Y

20 rows selected.

test:(MDINH@test):PRIMARY> select role,oracle_maintained from dba_roles order by 1;

ROLE                           O
------------------------------ -
ADM_PARALLEL_EXECUTE_TASK      Y
AQ_ADMINISTRATOR_ROLE          Y
AQ_USER_ROLE                   Y
AUDIT_ADMIN                    Y
AUDIT_VIEWER                   Y
AUTHENTICATEDUSER              Y
CAPTURE_ADMIN                  Y
CDB_DBA                        Y
CONNECT                        Y
DATAPUMP_EXP_FULL_DATABASE     Y
DATAPUMP_IMP_FULL_DATABASE     Y
DBA                            Y
DBFS_ROLE                      Y
DELETE_CATALOG_ROLE            Y
EJBCLIENT                      Y
EM_EXPRESS_ALL                 Y
EM_EXPRESS_BASIC               Y
EXECUTE_CATALOG_ROLE           Y
EXP_FULL_DATABASE              Y
GATHER_SYSTEM_STATISTICS       Y
GDS_CATALOG_SELECT             Y
GLOBAL_AQ_USER_ROLE            Y
GSMADMIN_ROLE                  Y
GSMUSER_ROLE                   Y
GSM_POOLADMIN_ROLE             Y
HS_ADMIN_EXECUTE_ROLE          Y
HS_ADMIN_ROLE                  Y
HS_ADMIN_SELECT_ROLE           Y
IMP_FULL_DATABASE              Y
JAVADEBUGPRIV                  Y
JAVAIDPRIV                     Y
JAVASYSPRIV                    Y
JAVAUSERPRIV                   Y
JAVA_ADMIN                     Y
JAVA_DEPLOY                    Y
JMXSERVER                      Y
LOGSTDBY_ADMINISTRATOR         Y
OEM_ADVISOR                    Y
OEM_MONITOR                    Y
OPTIMIZER_PROCESSING_RATE      Y
PDB_DBA                        Y
PROVISIONER                    Y
RECOVERY_CATALOG_OWNER         Y
RECOVERY_CATALOG_USER          Y
RESOURCE                       Y
SCHEDULER_ADMIN                Y
SELECT_CATALOG_ROLE            Y
TEST                           Y
XDBADMIN                       Y
XDB_SET_INVOKER                Y
XDB_WEBSERVICES                Y
XDB_WEBSERVICES_OVER_HTTP      Y
XDB_WEBSERVICES_WITH_PUBLIC    Y
XS_CACHE_ADMIN                 Y
XS_NAMESPACE_ADMIN             Y
XS_RESOURCE                    Y
XS_SESSION_ADMIN               Y

57 rows selected.

test:(MDINH@test):PRIMARY>

++++++++++

SYS@tmnt> select username,oracle_maintained from dba_users order by 1;

USERNAME             O
-------------------- -
ANONYMOUS            Y
APPQOSSYS            Y
AUDSYS               Y
C##GGS_ADMIN         N
C##TESTING           N
DBSNMP               Y
DIP                  Y
GSMADMIN_INTERNAL    Y
GSMCATUSER           Y
GSMUSER              Y
ORACLE_OCM           Y
OUTLN                Y
SYS                  Y
SYSBACKUP            Y
SYSDG                Y
SYSKM                Y
SYSTEM               Y
XDB                  Y
XS$NULL              Y

19 rows selected.

SYS@tmnt> create role test;
create role test
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SYS@tmnt> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         MOUNTED
SYS@tmnt> alter session set container=april;

Session altered.

SYS@tmnt> create role test;

Role created.

SYS@tmnt> select role,oracle_maintained from dba_roles order by 1;

ROLE                           O
------------------------------ -
ADM_PARALLEL_EXECUTE_TASK      Y
AQ_ADMINISTRATOR_ROLE          Y
AQ_USER_ROLE                   Y
AUDIT_ADMIN                    Y
AUDIT_VIEWER                   Y
AUTHENTICATEDUSER              Y
CAPTURE_ADMIN                  Y
CDB_DBA                        Y
CONNECT                        Y
DATAPUMP_EXP_FULL_DATABASE     Y
DATAPUMP_IMP_FULL_DATABASE     Y
DBA                            Y
DBFS_ROLE                      Y
DELETE_CATALOG_ROLE            Y
EM_EXPRESS_ALL                 Y
EM_EXPRESS_BASIC               Y
EXECUTE_CATALOG_ROLE           Y
EXP_FULL_DATABASE              Y
GATHER_SYSTEM_STATISTICS       Y
GDS_CATALOG_SELECT             Y
GLOBAL_AQ_USER_ROLE            Y
GSMADMIN_ROLE                  Y
GSMUSER_ROLE                   Y
GSM_POOLADMIN_ROLE             Y
HS_ADMIN_EXECUTE_ROLE          Y
HS_ADMIN_ROLE                  Y
HS_ADMIN_SELECT_ROLE           Y
IMP_FULL_DATABASE              Y
LOGSTDBY_ADMINISTRATOR         Y
OEM_ADVISOR                    Y
OEM_MONITOR                    Y
OPTIMIZER_PROCESSING_RATE      Y
PDB_DBA                        Y
PROVISIONER                    Y
RECOVERY_CATALOG_OWNER         Y
RECOVERY_CATALOG_USER          Y
RESOURCE                       Y
SCHEDULER_ADMIN                Y
SELECT_CATALOG_ROLE            Y
TEST                           N
XDBADMIN                       Y
XDB_SET_INVOKER                Y
XDB_WEBSERVICES                Y
XDB_WEBSERVICES_OVER_HTTP      Y
XDB_WEBSERVICES_WITH_PUBLIC    Y
XS_CACHE_ADMIN                 Y
XS_NAMESPACE_ADMIN             Y
XS_RESOURCE                    Y
XS_SESSION_ADMIN               Y

49 rows selected.

SYS@tmnt>

Columns Affected by Extended Data Type

I am not going to post how to convert to extended data type since there are many blogs on that already.

Just a reminder, there’s no going back; hence have backup and possibly minimize changes during testing to be able to restore (which is ideal and may not be feasible).

Before reverting to MAX_STRING_SIZE=STANDARD, columns affected by extended data type need to be identified.

From Oracle documentation, MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply
(for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).

EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

Test case:

[05:41]oracle@arrow:test:/media/sf_working/sql
$ sysdba @max_string_size.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 24 05:41:22 2016

Copyright (c) 1982, 2014, Oracle.  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


OWNER                TABLE_NAME                               COLUMN_NAME               DATA_TYPE  DATA_LENGTH CHAR_LENGTH C
-------------------- ---------------------------------------- ------------------------- ---------- ----------- ----------- -
MDINH                T                                        NAME                      VARCHAR2          5000        5000 B
MDINH                T2                                       T2                        RAW               2555           0
MDINH                T3                                       ID                        VARCHAR2         24000        6000 C
SYS                  DBA_ADDM_FINDINGS                        FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        MORE_INFO                 VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASKS                           ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASKS                           STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASK_DIRECTIVES                 DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_ACTIONS                      MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_DEF_PARAMETERS               DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXECUTIONS                   ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXECUTIONS                   STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXEC_PARAMETERS              DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     MORE_INFO                 VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDING_NAMES                FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_PARAMETERS                   DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RATIONALE                    IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RATIONALE                    MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RECOMMENDATIONS              BENEFIT_TYPE              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_TASKS                        ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_TASKS                        STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_REGISTRY                             OTHER_SCHEMAS             VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_CHAIN_RULES                ACTION                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_CHAIN_RULES                CONDITION                 VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOBS                       PROGRAM_NAME              VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       PROGRAM_OWNER             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       RAISE_EVENTS              VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOBS                       SCHEDULE_NAME             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       SCHEDULE_OWNER            VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOB_RUN_DETAILS            ERRORS                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOB_RUN_DETAILS            OUTPUT                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_WINDOWS                    SCHEDULE_NAME             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_WINDOWS                    SCHEDULE_OWNER            VARCHAR2         16000       16000 B
SYS                  DBA_VIEWS                                TEXT_VC                   VARCHAR2         32767       32767 B
SYS                  INT$DBA_VIEWS                            TEXT_VC                   VARCHAR2         32767       32767 B

40 rows selected.

test:(SYS@test):PRIMARY> show parameter max_string

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
max_string_size                      string                         EXTENDED

test:(SYS@test):PRIMARY> desc mdinh.t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(1000)
 NAME                                                           VARCHAR2(5000)

test:(SYS@test):PRIMARY> desc mdinh.t2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 T2                                                             RAW(2555)

test:(SYS@test):PRIMARY> desc mdinh.t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(6000 CHAR)

test:(SYS@test):PRIMARY> @nls.sql

PARAMETER                      SESSION                        DATABASE                       INSTANCE
------------------------------ ------------------------------ ------------------------------ ------------------------------
NLS_COMP                       BINARY                         BINARY                         BINARY
NLS_SORT                       BINARY                         BINARY
NLS_CALENDAR                   GREGORIAN                      GREGORIAN
NLS_CURRENCY                   $                              $
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       AMERICAN
NLS_TERRITORY                  AMERICA                        AMERICA                        AMERICA
NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS          DD-MON-RR
NLS_TIME_FORMAT                HH.MI.SSXFF AM                 HH.MI.SSXFF AM
NLS_CHARACTERSET                                              AL32UTF8
NLS_ISO_CURRENCY               AMERICA                        AMERICA
NLS_DATE_LANGUAGE              AMERICAN                       AMERICAN
NLS_DUAL_CURRENCY              $                              $
NLS_RDBMS_VERSION                                             12.1.0.2.0
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR             HH.MI.SSXFF AM TZR
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_LENGTH_SEMANTICS           CHAR                           BYTE                           BYTE
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM       DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET                                        AL16UTF16
NLS_NUMERIC_CHARACTERS         .,                             .,
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR   DD-MON-RR HH.MI.SSXFF AM TZR

20 rows selected.

test:(SYS@test):PRIMARY>

Now it’s evident as to why there is no going back since SYS objects seem to be modified too.

That’s the easy part. Next is to create the database with identical components installed and hopefully full export/import will work.

Some useful information if you are thinking about migrating to extended data type.
12c Indexing Extended Data Types Part I (A Big Hurt)

12c Indexing Extended Data Types Part II (15 Steps)