Wednesday, July 16, 2014

Crossplatform transportable tablespaces - part 1

There is couple of way to do heterogeneous migration of Oracle databases but staring with 12c there is whole set of new RMAN commands to transport data across different platforms.
I was looking for a best method of move tablespaces from HP-UX to Linux and after some research I found this presentation by Martin Bach from Enkitec (you can watch it online here). Martin describing Oracle Perl script (MOS ID 1389592.1) which allows you to convert tablespaces on 11g database including Exadata. At first sigh it looks like a solution for me but to use that script I need to create a new backup of all tablespaces I want to move. That could be an option but I already had a daily updated copy of all data files in FRA. So in next step I started to investigate who script is working and how to convert a backupset from HPUX to Linux and apply incremental backup into files not registered into database. Well solution was easy to predict - use PL/SQL RMAN interface - DBMS_BACKUP_RECOVERY package. 

It was not a first time when I was looking into it and it remembered me a Oracle 8i database with corrupted control file without RMAN catalog which had to be recovered. For those who forget RMAN in Oracle 8i had not a catalog functionality so you had to treat all control files or RMAN catalog with extra care. But using knowledge about files and backup names, PL/SQL and DBMS_BACKUP_RECOVERY it was possible to restore everything manually.

But let's come back to current problem - I was keen to use PL/SQL but before that I decided to check what new Oracle introduced in 12c and nice surprise - now all operations described used by Oracle perl script are possible from RMAN interface. So in next step I decided to do a little test with existing copies of data files from smallest tablespace called 'USERS'.
This small test was successful and now I need to document and describe all steps and this is material for a next post. It's working a little bit better than Oracle script as there is no need to convert backupset - recover can apply and do conversion on the fly

New syntax to learn in investigate more:
RMAN> recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy 'C:\TEMP\USERS01.DBF' from backupset 'c:\temp\inc15_2.bkp';

Starting restore at 15-JUL-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file C:\TEMP\USERS01.DBF
channel ORA_DISK_1: reading from backup piece c:\temp\inc15_2.bkp
channel ORA_DISK_1: foreign piece handle=C:\TEMP\INC15_2.BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUL-14