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
haha – thanks Mike – I’ve always created my own dump directories within PDBs so I didn’t even know that there was a default or a problem – ignorance is bliss ! but I’ve always wondered what those strange services were when I checked the listener stat – and now you’ve answered that – thank you !
& also thanks for the ongoing great service you provide on this blog – I caught a couple of the online things last week – good stuff – thanks
Thanks a lot, Andy!
Hi Mike,
Seems the ORA-19721 is just an expected behaviour on 12c, when you drop the re-attach a tablespace. You need to re-set the datafile as read only at OS level before re-attaching it:
In 12c, TTS Import Will Fail with ORA-19721 if we Try to Attach a Read-only Tablespace Second Time. Also, Sharing the Same Read-Only Tablespace across Databases Is Not Possible. (Doc ID 2094476.1)
Hi Miguel,
I didn’t try to share the tablespace between different databases – and this can be done with 19c on with a special option. But just btw.
And I didn’t receive an ORA-19721. But thanks for the hint to the note – I wasn’t aware of it.
Cheers,
Mike
Can we use TTS where source is PDB and destination would be non pdb – 20tb database
Hi,
yes, of course – TTS works independently of non-CDB/CDB architecture.
Cheers,
Mike