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 220.127.116.11 to Oracle 18.104.22.168 directly. Of course this would work cross-platform and cross-Endianness as well.
On the source side, the FTEX 22.214.171.124 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';
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
db_recovery_file_destmust 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
VERSION=12 option is necessary only when you are using this feature with an 126.96.36.199/4 source database. You will receive a good number of expected errors when doing this with an Oracle 11.2 source.
In case you need to fallback execute the steps to flashback your PDB documented here:
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
- Full Transportable Export/Import – Things to Know
- Full Transportable Export/Import – Migrating an 188.8.131.52 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