I really do like the idea of transportable tablespaces in the Oracle database world for several reasons. It is a good feature for migrating data from one system to another, it allows to skip lenghty upgrade procedures, it can be used to change the OS platform and it can reduce downtime by utilizing incremental updates. In a current project we are using this feature to migrate to AWS. Please don’t ask why, in my opinion there are other cloud providers, that would fit better to host an Oracle database. But that’s only a side note. AWS provides Oracle databases as a managed service, somehow similar to Oracle Autonomous Database. You don’t get SYSDBA access to the database, there is only an administrative account with restricted privileges and access to some AWS-provided packages. Since the database in question is roughly 6.5TB in size, Datapump is not the first approach to migrate the data. But AWS allows to use the Transportable Tablespace feature as well. So we decided to go that way.
We followed these steps using the XTTS V4 scripts from Oracle to generate the initial backup and an incremental backup with the tablespaces in read only state. Finally we did a metadata and a tablspace export. During the final import at the AWS end, we run into an issue that I could also reproduce in the old on-prem world. Before starting a transportable tablespace import some prerequisites must be fullfilled. The TTS import creates all objects, that are in the transported tablespaces. Basically we talk about tables and indexes. Nothing else has segments in a tablespace. And nothing else is in the TTS export file. Not quite true, dependend objects are part of it, like grants, triggers etc. So we need to create all the users, that own segments in these tablespaces beforehand since their definition is not part of the TTS export. I did that with a simple “create user XYZ” statement. Also we might need to create all sequences since ID columns might use sequences as a default value. I simply took these from the full metadata export using these IMPDP parameters.
full=y
include=sequence
Then I started the TTS import. But unfortunately it did not succeed.
oracle@olca0046:~/xtts_ref> impdp parfile=imp_ts.par
Import: Release 19.0.0.0.0 - Production on Tue Jan 9 14:08:02 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
09-JAN-24 14:08:05.558: W-1 Startup took 0 seconds
09-JAN-24 14:08:10.276: W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
09-JAN-24 14:08:15.467: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@olca0046:1523/xttback parfile=imp_ts.par
09-JAN-24 14:08:16.050: W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
09-JAN-24 14:08:20.865: W-1 Completed 1 PLUGTS_BLK objects in 4 seconds
09-JAN-24 14:08:20.865: W-1 Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
09-JAN-24 14:08:22.262: W-1 Completed 1 TYPE objects in 0 seconds
09-JAN-24 14:08:22.262: W-1 Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
09-JAN-24 14:08:24.473: W-1 Completed 1 PROCACT_INSTANCE objects in 1 seconds
09-JAN-24 14:08:24.473: W-1 Processing object type TRANSPORTABLE_EXPORT/TABLE
09-JAN-24 14:12:52.460: ORA-39083: Object type TABLE:"EC_NDB"."NDB_GETAG_CACHE" failed to create with error:
ORA-01031: insufficient privileges
CREATE TABLE "EC_NDB"."NDB_GETAG_CACHE" ("ID" NUMBER NOT NULL ENABLE, "KALK_ID" NUMBER, "DB_USER" VARCHAR2(50 BYTE) DEFAULT user NOT NULL ENABLE, "REQUEST_START" DATE, "REQUEST_END" DATE, "REQUEST" VARCHAR2(50 BYTE), "STATUS" VARCHAR2(10 BYTE), "ERROR" VARCHAR2(1000 BYTE), "RESPONSE" "SYS"."XMLTYPE" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(SEG_FILE 34 SEG_BLOCK 87786 OBJNO_REUSE 67318 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EC_TBS_SYS" XMLTYPE COLUMN "RESPONSE" STORE AS SECUREFILE BINARY XML ( TABLESPACE "EC_TBS_LOB" ENABLE STORAGE IN ROW CHUNK 8192 CACHE NOCOMPRESS KEEP_DUPLICATES STORAGE(SEG_FILE 27 SEG_BLOCK 1505 OBJNO_REUSE 67321 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) INDEX ( INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 27 SEG_BLOCK 1522 OBJNO_REUSE 67322 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ALLOW NONSCHEMA DISALLOW ANYSCHEMA
09-JAN-24 14:12:52.460: ORA-39083: Object type TABLE:"EC_UK"."UK_XCH_XML_IMPORT" failed to create with error:
ORA-01031: insufficient privileges
Failing sql is:
CREATE TABLE "EC_UK"."UK_XCH_XML_IMPORT" ("ID" NUMBER(15,0) NOT NULL ENABLE, "XML_CONTENT" "SYS"."XMLTYPE" , "LOADINGDATE" DATE DEFAULT sysdate, "PROCESSDATE" DATE, "STATUS" NUMBER DEFAULT 100 NOT NULL ENABLE, "IFI_ID" NUMBER(15,0), "STATUSTEXT" VARCHAR2(1000 BYTE), "MSG_ID" VARCHAR2(100 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(SEG_FILE 34 SEG_BLOCK 87802 OBJNO_REUSE 67394 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EC_TBS_SYS" XMLTYPE COLUMN "XML_CONTENT" STORE AS SECUREFILE BINARY XML ( TABLESPACE "EC_TBS_LOB" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(SEG_FILE 27 SEG_BLOCK 1545 OBJNO_REUSE 67395 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) INDEX ( INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 27 SEG_BLOCK 1562 OBJNO_REUSE 67396 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ALLOW NONSCHEMA DISALLOW ANYSCHEMA
[...]
There were some more tables like these which failed to import. My first assumption was, all these tables have LOBs in it. But there are other tables with LOBs which were imported successfully. So that was not the root cause. On the other hand, all of these failing tables have columns of type XMLTYPE. So I investigated in that direction and found MOS note 2224183.1: “ORA-39083 / ORA-01031 ‘ insufficient privileges’ Error Using Data Pump to Import Data when the Schema Used to Import has the Necessary Privileges”. The note explains the behaviour. For importing normal tables it is sufficient to have “soft” privileges to create tables in any schema. But for these tables with XMLTYPE columns, it is neccessary to act as the owning user. Therefore the user must be granted the “CREATE TABLE” privilege. Having that, I did grant the privilege to the users in qestion and re-run the import again.
oracle@olca0046:~/xtts_ref> impdp parfile=imp_ts.par
Import: Release 19.0.0.0.0 - Production on Tue Jan 9 15:12:57 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "statistics=none" Location: Parameter File, ignored.
09-JAN-24 15:13:09.061: W-1 Startup took 1 seconds
09-JAN-24 15:13:17.169: W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
09-JAN-24 15:13:22.628: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@olca0046:1523/xttback parfile=imp_ts.par
09-JAN-24 15:13:23.180: W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
09-JAN-24 15:13:31.874: W-1 Completed 1 PLUGTS_BLK objects in 8 seconds
09-JAN-24 15:13:31.874: W-1 Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
09-JAN-24 15:13:33.209: W-1 Completed 1 TYPE objects in 0 seconds
09-JAN-24 15:13:33.209: W-1 Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
09-JAN-24 15:13:35.363: W-1 Completed 1 PROCACT_INSTANCE objects in 1 seconds
09-JAN-24 15:13:35.363: W-1 Processing object type TRANSPORTABLE_EXPORT/TABLE
09-JAN-24 15:18:25.860: W-1 Completed 5756 TABLE objects in 289 seconds
09-JAN-24 15:18:25.860: W-1 Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
09-JAN-24 15:20:12.815: W-1 Completed 92252 OBJECT_GRANT objects in 106 seconds
09-JAN-24 15:20:12.815: W-1 Processing object type TRANSPORTABLE_EXPORT/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
09-JAN-24 15:20:14.143: W-1 Completed 61 OBJECT_GRANT objects in 0 seconds
09-JAN-24 15:20:14.143: W-1 Processing object type TRANSPORTABLE_EXPORT/GRANT/CROSS_SCHEMA/OBJECT_GRANT
09-JAN-24 15:20:15.902: W-1 Completed 455 OBJECT_GRANT objects in 1 seconds
09-JAN-24 15:20:15.902: W-1 Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
09-JAN-24 15:24:11.479: W-1 Completed 16611 INDEX objects in 234 seconds
09-JAN-24 15:24:11.479: W-1 Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
[...]
This time all the tables were imported successfully. There were other issues though, but that might make up another blog post.
Marco's DBA Blog 