DataPump Transportable Tablespace Import Raises Errors ORA-39083 ORA-1917 (Doc ID 1082116.1)
In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 06-May-2013***
SYMPTOMS
You successfully performed a transportable tablespace export using the DataPump utility expdp. During import, the following errors occurred:
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role ‘A_ROLE‘ does not exist
Failing sql is:
GRANT SELECT ON "A_USR"."A_TAB" TO "A_ROLE"
The following test case demonstrates this:
1. Create the environment in source database
connect / as sysdba
-- create a directory used by DataPump
create or replace directory dpu as ‘d:\databases\o111\dpu‘;
-- create a role
create role a_role;
-- create a tablespace
create tablespace a_tbs datafile ‘d:\databases\o111\dbf\a_tbs.dbf‘ size 10m;
-- create a user
create user a_usr identified by a_usr default tablespace a_tbs temporary tablespace temp;
grant connect, resource to a_usr;
-- in new created schema create a table and populate this
connect a_usr/a_usr
create table a_tab
(
id number,
text varchar2(10)
);
insert into a_tab values (1, ‘Text 1‘);
commit;
-- grant select to new created role
grant select on a_tab to a_role;
2. Perform the tablespace export in source database
connect / as sysdba
alter tablespace a_tbs read only;
#> expdp system/passwd directory=dpu dumpfile=a_tts.dmp reuse_dumpfiles=y transport_tablespaces=a_tbs
Export: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:27:20
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dpu dumpfile=a_tts.dmp reuse_dumpfiles=y transport_tablespaces=a_tbs
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
D:\DATABASES\O111\DPU\A_TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace A_TBS:
D:\DATABASES\O111\DBF\A_TBS.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:27:59
3. Perform the transportable tablespace import in target database
First, transfer the DataPump export dump file and database file(s) to the target machine (if using ftp then use the binary transfer mode). Second, verify, if the involved schemas (A_USR in this case) exist in target database before starting the import. Then start the DataPump import.
#> impdp system/password directory=dpu dumpfile=a_tts.dmp transport_datafiles=f:\databases\o111\dbf\a_tbs.dbf
Import: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:30:23
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dpu dumpfile=a_tts.dmp transport_datafiles=f:\databases\o111\dbf\a_tbs.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role ‘A_ROLE‘ does not exist
Failing sql is:
GRANT SELECT ON "A_USR"."A_TAB" TO "A_ROLE"
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at 10:30:29
CAUSE
The roles are not taken during transportable tablespace export. Only the objects (and adjacent depending objects) that reside in specified tablespace are extracted. During import of objects grants, all privileges granted to the missing roles will fail with error ORA-1917.
SOLUTION
1. Extract all the roles in source database using DataPump export
#> expdp system/passwd directory=dpu dumpfile=roles.dmp reuse_dumpfiles=y full=y include=role
Export: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:48:05
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_02": system/******** directory=dpu dumpfile=roles.dmp reuse_dumpfiles=y full=y include=role
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/ROLE
Master table "SYSTEM"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
D:\DATABASES\O111\DPU\ROLES.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_02" successfully completed at 10:48:24
2. Transfer the new dump to the target machine and before starting the DataPump transportable tablespace import, first import the roles from source.
#> impdp system/passwd directory=dpu dumpfile=roles.dmp full=y
Import: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:49:26
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dpu dumpfile=roles.dmp full=y
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JMXSERVER" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 29 error(s) at 10:49:29
This may raise errors like ORA-31684, ignore them. These are caused by already existing roles with same names in target database.
3. Start the DataPump transportable tablespace import that now successfully terminates without errors ORA-1917
#> impdp system/password directory=dpu dumpfile=a_tts.dmp transport_datafiles=f:\databases\o111\dbf\a_tbs.dbf
Import: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:50:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dpu dumpfile=a_tts.dmp transport_datafiles=f:\databases\o111\dbf\a_tbs.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:50:15
DataPump Transportable Tablespace Import Raises Errors ORA-39083 ORA-1917 (Doc ID 1082116.1)
原文：https://www.cnblogs.com/chendian0/p/10867527.html
