Tranportable Tablespace to lower release? Is it possible?

Today my experiment will be trying to run a transportable tablespace to a lower release. The reason for this experiment is to evaluate a possible fast fallback plan in case I want to roll back a migration+upgrade (eg: 12c NON-CDB to 19c PDB).

The first important thing to note is that the non-cdb to pdb conversion is not reversible. So that's why I plan to use transportable tablespaces as my fallback strategy here.

However, before we start, if we check Compatibility and New Features when Transporting Tablespaces with Export and Import (Doc ID 291024.1), Oracle states:

While a transport tablespace to a lower release may work in some cases, it is not an action that Oracle supports. In some cases, it is possible to transport a tablespace from a higher release and plug it in a database with a lower release. Besides the standard compatibility restrictions for a 'normal' export and import (export with the lower release export utility that connects to the higher database via Oracle Net), the compatibility setting of the source and target database should also match. Even if all restrictions are met, errors might occur. In those cases, these errors should not be considered as a bug.
Examples:

a transport tablespace from Oracle9i (9.2.0.4) to Oracle8i (8.1.7.4) will fail due to not-a-bug:
Bug:3380564 "EXP-8 ORA-6550 PLS-306 WITH TRANSPORTABLE TABLESPACES WHEN EXPORTING 9I USING 8I";
a transport tablespace from Oracle10g (10.1.0.3) to Oracle10g (10.1.0.2) will fail due to not-a-bug:
Bug:3900320 "CANNOT TRANSPORT TABLESPACES BETWEEN 10.1.0.3 AND 10.1.0.2"

In those cases, use the conventional export and import instead, i.e.:

run a full database export (FULL=Y), or:
run a tablespace level export (TABLESPACES=xxx,yyy), or:
run a user-level export (OWNER=uuu,vvv), or:
run a table-level export (TABLES=sss,ttt).

So it's not supported. Period. But what happens if I insist?

My playground will be:

Source DB (19.11.0):
- 19.11 RU + 19.11.0.0.210420 OJVM
- COMPATIBLE: 12.1.0.2.0

Target DB 1 (12.1.0.2):
- 12.1.0.2.210420 PSU + 12.1.0.2.210420 OJVM
- COMPATIBLE: 12.1.0.2.0

Target DB 2 (11.2.0.4):
- 11.2.0.4.210420 PSU + 11.2.0.4.210420 OJVM
- COMPATIBLE: 11.2.0.4.0

Preparing my source environment

In my 19c source system, I will create some user data, so I can check them later after the migration.

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 9 16:50:11 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
SQL> alter session set container=PDB01;

Session altered.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP

SQL> CREATE TABLESPACE TOOLS DATAFILE '/u01/app/oracle/oradata/CDB19C/PDB01/tools01.dbf' size 10M;

Tablespace created.

SQL> CREATE USER TEST IDENTIFIED BY "oracle";

User created.

SQL> GRANT DBA TO TEST;

Grant succeeded.
SQL> CREATE TABLE TEST.T1 TABLESPACE TOOLS AS SELECT * FROM DBA_USERS;

Table created.

SQL> ALTER TABLESPACE TOOLS READ ONLY;

Tablespace altered.

SQL>

1. Trying to expdp for transportable using "version" parameter for 11g/12c.

Trying first with 12c as my target:

[oracle@odbfcl-19-0-0-0 ~]$ expdp \"sys/Rodrigo.123@localhost:1521/pdb01 as sysdba\" transport_tablespaces=tools version=12.1.0.2

Export: Release 19.0.0.0.0 - Production on Mon Jul 12 14:32:49 2021
Version 19.11.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
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "sys/********@localhost:1521/pdb01 AS SYSDBA" transport_tablespaces=tools version=12.1.0.2
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/cdb19c/dpdump/C6EE6CF0FB6D51D7E0536C01000AA811/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace TOOLS:
  /u01/app/oracle/oradata/CDB19C/PDB01/tools01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 12 14:33:16 2021 elapsed 0 00:00:25

It worked! What if I my target version was 11.2.0.4?

[oracle@odbfcl-19-0-0-0 ~]$ expdp \"sys/Rodrigo.123@localhost:1521/pdb01 as sysdba\" transport_tablespaces=tools version=11.2.0.4

Export: Release 19.0.0.0.0 - Production on Mon Jul 12 14:33:31 2021
Version 19.11.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-39250: Using version to downgrade a transportable job is not supported

It returned:

ORA-39001: invalid argument value
ORA-39250: Using version to downgrade a transportable job is not supported

Even though Oracle not officially support, you are not blocked from a 19c to 12.1.0.2, which is weird. It sounds like a "we won't block you, but try at your own risk".

So, what happens when I import that dump into a 12c database:

[oracle@odbfcl-12-1-0-2 ~]$ impdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=expdat.dmp transport_datafiles=/u01/app/oradata/tools01.dbf

Import: Release 12.1.0.2.0 - Production on Mon Jul 12 16:11:37 2021

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
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone version is 32 and target time zone version is 18.
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=expdat.dmp transport_datafiles=/u01/app/oradata/tools01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'BEGINIMPTABLESPACE'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Mon Jul 12 16:11:38 2021 elapsed 0 00:00:01

It will fail with a "PLS-00306: wrong number or types of arguments in call to 'BEGINIMPTABLESPACE'", which is not a bug, just a documented incompatibiliity.

2. Trying without version parameter in impdp

I will skip the expdp step, but once I try the impdp, I get the exact same error as before on 12c. For 11g, I get:

[oracle@odbfcl-11-2-0-4 ~]$ impdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=expdat.dmp transport_datafiles=/u01/app/oradata/tools01.dbf

Import: Release 11.2.0.4.0 - Production on Mon Jul 12 16:42:50 2021

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 4.1 in dump file "/u01/app/oracle/admin/orcl/dpdump/expdat.dmp"

It failed with "ORA-39142: incompatible version number 4.1 in dump file".

If we check MOS Note Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1), we have:

This explains why dump file has version 4.1 and why 11g can't read it.

3. And what if I use the 19c impdp connect directly to the target databases?

[oracle@odbfcl-19-0-0-0 ~]$ impdp \"sys/Rodrigo.123@odbfcl-11-2-0-4:1521/orcl as sysdba\" transportable=always dumpfile=expdat.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 12 16:51:11 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
UDI-00018: Data Pump client is incompatible with database version 11.2.0.4.0

[oracle@odbfcl-19-0-0-0 ~]$ impdp \"sys/Rodrigo.123@odbfcl-12-1-0-2:1521/orcl as sysdba\" transportable=always dumpfile=expdat.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 12 16:53:11 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
UDI-00018: Data Pump client is incompatible with database version 12.1.0.2.0

They fail with "UDI-00018: Data Pump client is incompatible with database version X".

4. Finally, and if I run the impdp from the 11g/12c connecting on 19c using network_link?

First I will create the DB Link on the 11g/12c pointing to the 19c PDB:

SQL> CREATE DATABASE LINK test
CONNECT TO test
IDENTIFIED BY oracle
USING '//odbfcl-19-0-0-0:1521/PDB01';

Database link created.

Now calling the impdp using this dblink:

[oracle@odbfcl-11-2-0-4 ~]$ impdp \"/ as sysdba\" transport_tablespaces=tools network_link=test transport_datafiles=/u02/test/tools01.dbf

Import: Release 11.2.0.4.0 - Production on Mon Jul 12 16:56:26 2021

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
ORA-39001: invalid argument value
ORA-39169: Local version of 11.2.0.4.0 cannot work with remote version of 19.0.0.0.0.

[oracle@odbfcl-12-1-0-2 ~]$ impdp \"/ as sysdba\" transport_tablespaces=tools network_link=test transport_datafiles=/u02/test/tools01.dbf

Import: Release 12.1.0.2.0 - Production on Mon Jul 12 16:56:33 2021

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
ORA-39001: invalid argument value
ORA-39169: Local version of 12.1.0.2.0 cannot work with remote version of 19.0.0.0.0.

Got the "ORA-39169: Local version of X cannot work with remote version of 19.0.0.0.0."