Fallback Strategies with Full Transportable Export/Import

Fallback Strategies with Full Transportable Export/ImportI 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:

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

–Mike

2 thoughts on “Fallback Strategies with Full Transportable Export/Import

  1. Hi Mike,

    great article as always!
    You could maybe put some additional info, for those, who have data files stored within ASM – they can use “asmcmd cp” and “asmcmd chmod”, but renaming is a little bit trickier. How would you solve the renaming of the data file in this case?

    Thanks & best regards
    Dejan

    • Hi Dejan,

      you are right – and thanks for the hint. I use the HOL environment where I don’t have ASM configured.

      When you move to 12.1 you’ll always have the ALTER DATABASE DATAFILE … MOVE command available as a free EE feature.

      This should do the trick 🙂

      Thanks,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.