Hey all my impdp for oracle is listing quite a few errors that look like the following:
Failing sql is: BEGIN SYS.DBMS_AQ_IMP_INTERNAL.CLEANUP_SCHEMA_IMPORT;COMMIT; END;
ORA-39083: Object type PROCACT_SCHEMA failed to create with error: ORA-31625: Schema OPENDEMO_SNAP is needed to import this object, but is unaccesscible ORA-01435: user does not exist
I am importing and exporting as user system and to make double sure I ran the follow SQL commands:
GRANT DATAPUMP_EXP_FULL_DATABASE TO system; GRANT DATAPUMP_IMP_FULL_DATABASE TO system;
The import and export commands are as follows:
expdp system/password directory=dp_dir transportable=always full=y dumpfile=full_tts.dmp log=full_tts_export.log
impdp system/password directory=dp_dir dumpfile=full_tts.dmp logfile=full_tts_imp.log transport_datafiles='/tmp/oracle-db/users*','/tmp/oracle-db/data*'
Any help would be appreciated thanks!
EDIT: Here is the full export log. ;;; Export: Release 12.1.0.2.0 - Production on Tue Sep 27 09:35:55 2022
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 ;;; Legacy Mode Active due to the following parameters: ;;; Legacy Mode Parameter: "log=full_tts_export.log" Location: Command Line, Replaced with: "logfile=full_tts_export.log" ;;; Legacy Mode has set reuse_dumpfiles=true parameter. Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=dp_dir transportable=always full=y dumpfile=full_tts.dmp logfile=full_tts_export.log reuse_dumpfiles=true Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END; ORA-01950: no privileges on tablespace 'SYSAUX' Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1002. MB Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/LIBRARY/LIBRARY Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/IDENTITY_COLUMN Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/STATISTICS/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW Processing object type DATABASE_EXPORT/END_PLUGTS_BLK Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.593 KB 65 rows . . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows . . exported "SYS"."AUD$" 0 KB 0 rows . . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows . . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows . . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows . . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows . . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows . . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows . . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows . . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows . . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows . . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows . . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows . . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows . . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows . . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows . . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows . . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows . . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows . . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows . . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows . . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows . . exported "WMSYS"."WM$HINT_TABLE$" 9.453 KB 75 rows . . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows . . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows . . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.054 KB 10 rows . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows . . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows . . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows . . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows . . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows . . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows . . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows . . exported "LBACSYS"."OLS$POL" 0 KB 0 rows . . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows . . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows . . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows . . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows . . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows . . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows . . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows . . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 rows . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows . . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows . . exported "SYS"."NACL$_ACE_EXP" 9.929 KB 1 rows . . exported "SYS"."NACL$_HOST_EXP" 6.914 KB 1 rows . . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows . . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows . . exported "SYSTEM"."TEST1" 697.0 MB 20895367 rows . . exported "SYSTEM"."TEST2" 125.6 MB 3805424 rows . . exported "SYSTEM"."TEST_SPEAKERS" 4.876 MB 121874 rows . . exported "SYSTEM"."SYS_IMPORT_SCHEMA_01" 3.468 MB 1842 rows . . exported "SYSTEM"."SYS_IMPORT_SCHEMA_02" 3.467 MB 1842 rows . . exported "SYSTEM"."SYS_IMPORT_SCHEMA_03" 3.469 MB 1843 rows . . exported "SYSTEM"."SYS_IMPORT_SCHEMA_04" 3.468 MB 1842 rows . . exported "SYSTEM"."SYS_IMPORT_SCHEMA_05" 3.468 MB 1842 rows Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /u01/app/datafiles/full_tts.dmp
Datafiles required for transportable tablespace DATA: /export/u11/app/oracle/oradata/orcl/data01.dbf /export/u11/app/oracle/oradata/orcl/data02.dbf /export/u11/app/oracle/oradata/orcl/data03.dbf /export/u11/app/oracle/oradata/orcl/data04.dbf /export/u11/app/oracle/oradata/orcl/data05.dbf /export/u11/app/oracle/oradata/orcl/data06.dbf /export/u11/app/oracle/oradata/orcl/data07.dbf /export/u11/app/oracle/oradata/orcl/data08.dbf /export/u11/app/oracle/oradata/orcl/data09.dbf /export/u11/app/oracle/oradata/orcl/data10.dbf /export/u11/app/oracle/oradata/orcl/data11.dbf /export/u11/app/oracle/oradata/orcl/data12.dbf /export/u11/app/oracle/oradata/orcl/data13.dbf /export/u11/app/oracle/oradata/orcl/data14.dbf /export/u11/app/oracle/oradata/orcl/data15.dbf Datafiles required for transportable tablespace USERS: /export/u10/app/oracle/oradata/orcl/users01.dbf Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Sep 27 09:51:37 2022 elapsed 0 00:15:40
Here is the command that fixed my issue.
GRANT UNLIMITED TABLESPACE TO PUBLIC;
It is my understanding that this gives permissions to all users and allowed me to skip creating a script for this.
Be warned granted unlimited tablespace can have adverse effects!