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:
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
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
Mike, first, thanks for all the incredible information on your site/feed. We’re getting ready to use FTEX to go from 12.2 AIX non-CDB to 19.x Linux CDB. Almost 300 data files to copy. To have fallback protection, we are planning to set the files to readonly before starting the import as you describe here. If the import is successful and there is no need to flashback the PDB, I assume it is safe to reset them via chmod at that point, or is any other action needed first?