Fallback Strategies with Full Transportable Export/Import

I received an interesting question about how to implement Fallback Strategies with Full Transportable Export/Import several times within the past weeks. So it may be a good idea to showcase some possible scenarios.

Full Transportable Export/Import is an Oracle 12c feature which allows you to do a transportable tablespace migration but Data Pump will take away all the manual steps with only one command. You may need to have markers to revert to in order to avoid restarting the entire operation from scratch again.

And there are some special behaviors to know about, at first with Full Transportable Export/Import but also at second especially with Pluggable Databases and Oracle Muiltitenant.

Fallback Strategies with Full Transportable Export/Import

I’ll experiment a bit with our Hands-On Lab environment, especially the part 3. There we migrate the FTEX database with Full Transportable Export Import into a PDB from Oracle 11.2.0.4 to Oracle 12.2.0.1 directly. Of course this would work cross-platform and cross-Endianness as well.

On the source side, the FTEX 11.2.0.4 database, I don’t have to implement any fallback mechanism. There’s no change happening to the source. But on the destination site I will have to implement some fallback points. Otherwise in case of failure I’d have to rebuild my entire PDB or database and start over from scratch again.

If you’d like to redo the steps in our Hands-On Lab environment, then startup the FTEX database (environment: . ftex) and the CDB2 database (environment: . cdb2) first.

Then create a pluggable database (or any other database by yourself) in CDB2:

create pluggable database PDB2 admin user adm identified by adm
file_name_convert=('/oradata/CDB2/pdbseed', '/oradata/CDB2/pdb2');

Afterwards switch into this PDB (or your own fresh database). Then start it up and create a directory, privileges to it and create a database link pointing back to the source database:

alter session set container=PDB2;
startup
create directory mydir as '/u02/oradata/CDB2/mydir';
grant read, write on directory mydir to system;
create public database link SOURCEDB connect to system identified by oracle using 'FTEX';

Fallback Preparation

Now I’ll implement the fallback option. I could have done it before creating the directory and the database link. But as I will need them over and over again I consider these actions as not worth to be rolled back in case of failure.

For this purpose you have to meet three requirements:

• The database has to be in archivelog mode
• A db_recovery_file_dest must be defined
• Enable local undo for container databases

create restore point GRP1 guarantee flashback database;

Once this is completed, you can login to the source (here: the 11.2 FTEX database) and switch the tablespace(s) into read-only mode:

alter tablespace users read only;

Don’t forget to copy the file(s) of the tablespace(s):

cp /u02/oradata/FTEX/users01.dbf /u02/oradata/CDB2/pdb2

This step is absolutely crucial as otherwise you can’t flashback later on:

chmod 0444 /u02/oradata/CDB2/pdb2/users01.dbf

You must switch your copied data files on the OS level to read-only. Full Transportable Export/Import will otherwise switch them to read-write (which is a manual step with regular TTS) and the FLASHBACK command will fail.

Afterwards you can start the Full Transportable Export/Import. Data Pump will do all the manual work of Transportable Tablespaces in one command over the database link you’ve created.

impdp system/oracle@pdb2 network_link=sourcedb version=12 full=y \
 transportable=always metrics=y exclude=statistics directory=mydir \
 logfile=pdb2.log \
 transport_datafiles='/u02/oradata/CDB2/pdb2/users01.dbf'

You trigger the feature Full Transportable Export/Import with the combination of FULL=Y and TRANSPORTABLE=ALWAYS. The VERSION=12 option is necessary only when you are using this feature with an 11.2.0.3/4 source database. You will receive a good number of expected errors when doing this with an Oracle 11.2 source.

Fallback Steps

In case you need to fallback execute the steps to flashback your PDB documented here:

• Fallback Strategy: Flashback Pluggable Database

In brief:

sqlplus "sys/oracle@pdb2"
shutdown
flashback pluggable database PDB2 to restore point GRP1;
alter pluggable database open resetlogs;

Here I discovered an interesting (and at first unexpected) behavior:
When I issued the “RESETLOGS” command the file(s) I copied got removed.

A check in the alert.log reveals why this happens:

PDB2(3):Pluggable database PDB2 dictionary check beginning
PDB2(3):File #12 in the controlfile not found in data dictionary.
PDB2(3):Removing file from controlfile.
PDB2(3):data file 12: '/u02/oradata/CDB2/pdb2/users01.dbf'
PDB2(3):Deleted file /u02/oradata/CDB2/pdb2/users01.dbf
PDB2(3):Tablespace 'USERS' #4 found in controlfile,
PDB2(3):but not in the data dictionary. Deleting from controlfile.
PDB2(3):Pluggable Database PDB2 Dictionary check complete

Therefore I’ll have to copy your file(s) again in this case. In order to prevent this you’ll have to rename the file(s) before initiating the RESETLOGS operation.

Further Readings

• Full Transportable Export/Import – Things to Know
• Full Transportable Export/Import – Migrating an 11.2.0.4 database to Oracle Database 12c- into the Oracle Cloud
• Transportable Tablespaces – Characters Sets – Same same but different?
• Full Transportable Export/Import – PAR File Examples
• Transportable Tablespaces and READ ONLY in Oracle Database 12c
• Can you EXCLUDE tablespaces from Full Transportable Export/Import?
  .
• Fallback Strategy: Flashback to a Guaranteed Restore Point
• Fallback Strategy: Flashback Pluggable Database
• Fallback Strategy: Partial Offline Backups
• Fallback Strategies with Full Transportable Export/Import

–Mike