Transportable Tablespaces – Example and strange error with a PDB

Yesterday I was browsing around for a useful simple example to test Transportable Tablespaces. A colleague mailed with the other day with a strange error message. The attempt to import into a PDB in Oracle 19c failed. My first thought: Oh, this is simple. But I failed, too. And even worse, I couldn’t find a single useful note in MyOracle Support (MOS) for ORA-31640, ORA-27037, Linux-x86_64 Error: 2 with Additional information: 7. So I decided to summarize this in Transportable Tablespaces – Example and strange error with a PDB.

Photo by Kerensa Pickett on Unsplash

A simple Transportable Tablespace Example

I would attest myself to be able to search. Without the ability to search, learn and combine things, I wouldn’t have survived 6 years in Oracle Support. But I was a bit disappointed when I couldn’t find a really simple example for Transportable Tablespaces into a PDB. Of course, there are some notes in MOS. But none of them fit my purpose. Or they were too overly complicated. Or they were from the Oracle 8i days using good ol’ exp/imp.

So here’s a very very simple example – done in our Hands-On Lab. But this way I can play with the features by myself whenever I need to again.

Setup Instructions

Here you will find a very simple testcase. I execute this in a 11.2.0.4 database:

create tablespace PAX datafile '/u02/oradata/FTEX/pax01.dbf' size 10M autoextend on online;
create user uPAX identified by uPAX default tablespace PAX;
grant connect, resource to uPAX;
create table uPAX.tPAX as select * from ALL_OBJECTS;
alter tablespace PAX read only;

In this case I will skip the transport check you usually need to do with Transportable Tablespaces.

Export Parfile

Having a parameter file instead of typing a long line is general best practice. So this is my first attempt’s exptts.par file:

userid=system/oracle
metrics=y
directory=DATA_PUMP_DIR
dumpfile=pax.dmp
logfile=paxexport.log
transport_full_check=y
transport_tablespaces=(PAX)

Exporting the Tablespace for transport

I export the tablespace with above exptts.par file from my 11.2.0.4 database:

$ expdp parfile=exptts.par

Export: Release 11.2.0.4.0 - Production on Tue Jul 7 12:50:02 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** parfile=exptts.par 
Startup took 1 seconds
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
     Completed 1 PLUGTS_BLK objects in 2 seconds
Processing object type TRANSPORTABLE_EXPORT/TABLE
     Completed 1 TABLE objects in 2 seconds
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
     Completed 1 PLUGTS_BLK objects in 3 seconds
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/FTEX/dpdump/pax.dmp
******************************************************************************
Datafiles required for transportable tablespace PAX:
  /u02/oradata/FTEX/pax01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Jul 7 12:50:11 2020 elapsed 0 00:00:08

Copy files

Don’t forget to copy the data file(s) and the dump file. You can leave it where it is, or do an ONLINE RELOCATE afterwards. But as I’m in file system, I simply copy it to the new destination:

• cp /u02/oradata/FTEX/pax01.dbf /u02/oradata/CDB2/pdb1
• cp /u01/app/oracle/admin/FTEX/dpdump/pax.dmp /u01/app/oracle/admin/CDB2/dpdump

Import Parfile

Before I can import my tablespace, I need to create my user (here: uPAX) and grant the privileges. Then I can proceed.

And again, everything works much easier with a imptts.par file:

userid=system/oracle@pdb1
metrics=y
logtime=all
directory=DATA_PUMP_DIR
dumpfile=pax.dmp
logfile=paximport.log
transport_datafiles='/u02/oradata/CDB2/pdb1/pax01.dbf'

Plugin Failure into a PDB

Unfortunately, my well-working example from above fails when I try to import the tablespace into my 19.7.0 PDB. One of the reasons why I write this blog post is to document the error pattern. I couldn’t find a useful note in MOS.

$ impdp parfile=imptts.par

Import: Release 19.0.0.0.0 - Production on Tue Jul 7 15:01:08 2020
Version 19.7.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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/app/oracle/admin/CDB2/dpdump/A9D9581063C93148E055000000000001/pax.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Of course it raises an error when it tries to look for a dump file in “/u01/app/oracle/admin/CDB2/dpdump/A9D9581063C93148E055000000000001/pax.dmp“.

Where is this strange directory “A9D9581063C93148E055000000000001” coming from? I didn’t create it.

To lift the “secret”, it is the GUID of the PDB:

SQL> select GUID from V$PDBS;

GUID
--------------------------------
A9D9581063C93148E055000000000001

In addition, I realized something strange with the listener setup. I need to mention upfront that the listener setup in our Hands-On Lab is stable and reliable since a long while as we have 3 different homes and 5 different databases, 2 of them as CDBs with several PDBs. But there is a service active for this GUID “a9d9581063c93148e055000000000001” as well:

Service "a9c3fd917bcb15e9e055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "a9d9581063c93148e055000000000001.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...

Now I checked my directories in the database with:

set pagesize 1000
set linesize 160

column DIRECTORY_NAME format a29
column DIRECTORY_PATH format a80
column CON_ID format 99

select CON_ID, DIRECTORY_NAME, DIRECTORY_PATH
  from CDB_DIRECTORIES
  where DIRECTORY_NAME='DATA_PUMP_DIR'
  order by CON_ID, DIRECTORY_NAME
;

Resulting in:

CON_ID DIRECTORY_NAME		     DIRECTORY_PATH
------ ----------------------------- --------------------------------------------------------------------------------
     1 DATA_PUMP_DIR		     /u01/app/oracle/admin/CDB2/dpdump
     2 DATA_PUMP_DIR		     /u01/app/oracle/admin/CDB2/dpdump/878B6F392367587EE0532AB2A8C01897
     3 DATA_PUMP_DIR		     /u01/app/oracle/admin/CDB2/dpdump/A9D9581063C93148E055000000000001

There it is again. And this is the root cause of my problem. I used DATA_PUMP_DIR as it is equal, simple and defined by default.

Did I pay attention that it may be different within a PDB? Not at all.

But the GUID gets added automatically to the DATA_PUMP_DIR for each PDB separately. And the directory gets created, too.

Why is it set this way? I guess to distinct between different PDBs.

Is it needed? I would say “Yes” and “No“.

I collected a good number of notes and bugs in the “Links” section at the end of the blog post. Feel free to browse through them by yourself in case you are interested.

The bad thing – unlike described in some of the MOS notes written in the Oracle 12.2.0.1 days, you can’t overwrite the DATA_PUMP_DIR within a PDB:

SQL> create or replace directory DATA_PUMP_DIR as '/u01/app/oracle/admin/CDB2/dpdump';
create or replace directory DATA_PUMP_DIR as '/u01/app/oracle/admin/CDB2/dpdump'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

I guess, looking at this from a “cloud” perspective, it is necessary to prevent a tenant to change to a central directory. But to be honest, I couldn’t find any documentation describing this automatism.

Plugin Success with a non-CDB

Of course, you will have no such issue when you import your tablespace(s) into a non-CDB for the simple reason that DATA_PUMP_DIR does not have a secret GUID added.

I tried here with a 12.2.0.1 non-CDB:

$ impdp parfile=ft.par 

Import: Release 12.2.0.1.0 - Production on Mon Jul 6 19:46:00 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
W-1 Startup took 0 seconds
W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=imp.par 
W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
W-1      Completed 1 PLUGTS_BLK objects in 0 seconds
W-1 Processing object type TRANSPORTABLE_EXPORT/TABLE
W-1      Completed 1 TABLE objects in 1 seconds
W-1 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
W-1      Completed 1 PLUGTS_BLK objects in 0 seconds
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 6 19:46:06 2020 elapsed 0 00:00:05

Simple, isn’t it?

But let’s have a look at the solution for transporting tablespaces into a PDB.

The Ultimate (and only) Workaround

The workaround to this problem is simple – if you know that you need to do it.

You have to create your own directory, assign read and write privileges to your user, and then all works flawless. Or, turning it around, don’t use the DATA_PUMP_DIR when trying to use Transportable Tablespaces with PDBs. At least not in Oracle 19c.

Prepare the PDB

At first, I create my user from above – of course, without the default tablespace as this is the tablespace I will transport into the PDB.

create user uPAX identified by uPAX;
grant connect, resource to uPAX;

Then I will create a directory, add read and write privileges to my importing user (in my case: SYSTEM).

create directory mydir as '/u01/app/oracle/admin/CDB2/dpdump';
grant read, write on directory mydir to SYSTEM;

Adjust the imptts.par file

At this point I need to do some adjustments to the imptts.par file to fit for the new directory.

userid=system/oracle@pdb1
metrics=y
logtime=all
directory=mydir
dumpfile=pax.dmp
logfile=paximport.log
transport_datafiles='/u02/oradata/CDB2/pdb1/pax01.dbf'

Import the tablespace(s)

Now let us see if the solution works:

$ impdp parfile=imptts.par

Import: Release 19.0.0.0.0 - Production on Tue Jul 7 15:31:19 2020
Version 19.7.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
07-JUL-20 15:31:23.138: W-1 Startup took 1 seconds
07-JUL-20 15:31:24.299: W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
07-JUL-20 15:31:24.620: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@pdb1 parfile=imptts.par 
07-JUL-20 15:31:24.713: W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
07-JUL-20 15:31:25.295: W-1      Completed 1 PLUGTS_BLK objects in 1 seconds
07-JUL-20 15:31:25.295: W-1 Processing object type TRANSPORTABLE_EXPORT/TABLE
07-JUL-20 15:31:26.744: W-1      Completed 1 TABLE objects in 1 seconds
07-JUL-20 15:31:26.744: W-1 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
07-JUL-20 15:31:27.075: W-1      Completed 1 PLUGTS_BLK objects in 0 seconds
07-JUL-20 15:31:27.233: Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Jul 7 15:31:27 2020 elapsed 0 00:00:06

Final steps

At the end, I need to alter my user – and then I accomplished the mission.

alter session set container=pdb1;
alter user uPAX default tablespace PAX;

I hope this post helps you in case you struggle with the same topic. And feel free to comment if I missed something, or in case you have additional information.

Coda

If you thought to yourself: Why didn’t he just copy the pax.dmp file into the subdirectory created for the PDB? Well, of course I tried this as well. But see what happens then:

$ impdp parfile=imptts.par

Import: Release 19.0.0.0.0 - Production on Tue Jul 7 15:42:00 2020
Version 19.7.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
07-JUL-20 15:42:04.527: W-1 Startup took 1 seconds
07-JUL-20 15:42:05.808: W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
07-JUL-20 15:42:06.156: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@pdb1 parfile=imptts.par 
07-JUL-20 15:42:06.227: W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
07-JUL-20 15:42:06.571: ORA-39123: Data Pump transportable tablespace job aborted
ORA-19721: Cannot find data file with absolute file number 3 in tablespace PAX

07-JUL-20 15:42:06.583: Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Tue Jul 7 15:42:06 2020 elapsed 0 00:00:04

I couldn’t go around the error: ORA-19721: Cannot find data file with absolute file number 3 in tablespace PAX.

And at this point, I was not in the mood to search for other workarounds for something which looks odd.

Further Information and Links

• MOS Note: 2334569.1 – 12.2 DataPump Job Can Fail With ORA-1422 When Using DATA_PUMP_DIR To Export/Import A Pluggable Database (PDB)
• Unpublished Bug 27194511 – DATAPUMP_UTL.CREATE_DEFAULT_DIR POPULATES DATA_PUMP_DIR INCORRECTLY
• Unpublished Bug 28360226 – ERROR CREATING DATA_PUMP_DIR DIRECTORY OBJECT IN PDB DURING AN UPGRADE
• Bug 29261906 – Privileges on DATA_PUMP_DIR granted inside the PDB got wiped out during upgrade (fixed since 19.3.0)
• MOS Note: 2630666.1 – OCI How to change default DATA_PUMP_DIR to a new directory at PDB level for a OCI DB System
  • Very misleading note title – in fact not DATA_PUMP_DIR gets changed but a new directory gets created and used 😉
• MOS Note: 1166564.1 – Master Note for Transportable Tablespaces (TTS) — Common Questions and Issues

–Mike