In oracle 12c, transportable tablespace feature has been enhanced a lot. We can do cross platform migration using this feature with zero downtime.  This method uses both RMAN and datapump utility .

The first step will take a full backup of the script, when the tablespaces are online and restore the same in target . For next few iterations incremental will be applied till we reach cut over period(actual downtime period). During the final cutover phase, We have keep the tablespaces read only and take the final incremental with metadata exp.  This is the only time for which downtime is required during migration.  For all these activities oracle provided few configuration files, we need to unzip in both the environments.

In the below demo, we will do migration of two tablespaces from db server solaris sparc to hp ux  db server.(cross platform)

Source DB: WMTRAIN ( solaris sparc)

Target DB: BSSTEST ( hp ux )

 

PREPARATION PHASE:

1. Download the rman-xttconvert_2.0 file  :Doc ID 2005729.1)

2. Unzip the rman xtts_convert.zip in source db server.

 

/export/home/oracle/DBA_LAB/

-rw-r–r– 1 oracle oinstall 52 May 22 2015 xttstartupnomount.sql
-rw-r–r– 1 oracle oinstall 11549 May 22 2015 xttprep.tmpl
-rw-r–r– 1 oracle oinstall 91722 May 22 2015 xttdriver.pl
-rw-r–r– 1 oracle oinstall 71 May 22 2015 xttdbopen.sql
-rw-r–r– 1 oracle oinstall 1390 May 22 2015 xttcnvrtbkupdest.sql
-rw-r–r– 1 oracle oinstall 7789 May 22 2015 xtt.properties

 

3. Update the xtt.properties file with below parameters.

 

# cat xtt.properties
tablespaces=WEBMDATA,WEBMINDX
platformid=2
backupformat=/export/home/oracle/DBA_LAB/SOURCEDUMP
stageondest=/b2cdev2/TEST_TAR
storageondest=/b2cst1/oradata/BSSTEST

 

Where

tablespaces – Mention the tablespaces need to be migrated.

platformid ( we can get the value by using select platform_id from v$database;)

backupformat – Location on source where where full backup and incrmental rman backup will be stored.

stageondest – Location on TARGET ,where the rman and dumpfiles will be copied

storageondest– Location on TARGET, where datafiles will be created for the tablespaces.

 

5. Copy all the files including the updated xtt.properties file to target server.

cd /export/home/oracle/DBA_LAB/

scp * oracle@target_host: /b2cdev2/TEST_TAR/

6. export TMPDIR variable on both SOURCE and TARGET.

TMPDIR – location where the configuration files are stored, after unzipping.

on source – export TMPDIR =  /export/home/oracle/DBA_LAB/

on target – export TMPDIR= /b2cdev2/TEST_TAR/

 

INITIAL PHASE:

1.  Take fullrman backup of the source database.(use below script)

$ORACLE_HOME/perl/bin/perl  xttdriver.pl –backup

 

 

2. Move the created rman backup set  to target location  to stageondest=/b2cdev2/TEST_TAR

scp *bkp oracle@target-host:/b2cdev2/TEST_TAR/

3. Copy below newly created files from SOURCE to TARGET tsbkupmap.txt and  xttnewdatafiles.txt

scp tsbkupmap.txt oracle@target-host:/b2cdev2/TEST_TAR/

scp xttnewdatafiles.txt  oracle@target-host:/b2cdev2/TEST_TAR/

 

4. Run the restore script on target:

$ORACLE_HOME/perl/bin/perl xttdriver.pl –restore

 

 

Now we did a complete restore of the full backup. Now next phase is ROLLING FORWARD .

ROLLING FORWARD PHASE:

In this phase, we will apply the incremental backups from source to target in multiple iterations, to keep minimal lag, so that during downtime, for final incremental ,it will take less time.

1. Take incremental on source:

$ORACLE_HOME/perl/bin/perl xttdriver.pl –bkpinc

 

2. Copy the below files from source to target:

scp cat incrbackups.txt oracle@target-host:/b2cdev2/TEST_TAR
scp xttplan.txt oracle@target-host:/b2cdev2/TEST_TAR/
scp tsbkupmap.txt oracle@target-host:/b2cdev2/TEST_TAR/
scp incrbackups.txt oracle@target-host:/b2cdev2/TEST_TAR/

3. On TARGET , recover the incremental backup

$ORACLE_HOME/perl/bin/perl xttdriver.pl –recover

 

This rolling forward phase can done in multiple iterations to reduce to difference between source and target database.

Once we reach the final cut over time ( where we will get downtime) then we will take the final incremental.

 

FINAL INCREMENTAL BACKUP:

In this phase, source tablespaces will be made read only and incremental backup from source to target db will be applied.

1. Make tablespaces read only on SOURCE:

alter tablespace WEBMDATA READ ONLY;

alter tablespace WEBMINDX READ ONLY;

 

2. Take final incremental backup with metadata export on SOURCE:

$ORACLE_HOME/perl/bin/perl xttdriver.pl –bkpexport

 

3. Copy the below files from SOURCE to TARGET:

 

scp cat incrbackups.txt oracle@target-host:/b2cdev2/TEST_TAR/
scp xttplan.txt oracle@target-host:/b2cdev2/TEST_TAR/
scp tsbkupmap.txt oracle@target-host:/b2cdev2/TEST_TAR/
scp incrbackups.txt oracle@target-host:/b2cdev2/TEST_TAR/

 

4. Import the incremental backup on TARGET:

$ORACLE_HOME/perl/bin/perl xttdriver.pl –resincrdmp

 

5. Import the generated metadata dump on TARGET:

create directory dpump_tts as ‘/b2cdev2/TEST_TAR’;

 

prepare the parfile as below:

cat “imp_ts.par”
dumpfile=impdp14442_745.dmp
logfile=imp_log.log
directory=dpump_tts
transport_datafiles=’/b2cst1/oradata/BSSTEST/webmdata01.dbf’,’/b2cst1/oradata/BSSTEST/webmindx01.dbf’,’/b2cst1/oradata/BSSTEST/webmdata02.dbf’

 

impdp parfile=”imp_ts.par”

 

 

Lets compare the object count in both source and target:

Source:

 

on target:

We observed that sequences, function,pl/sql objects, type,views are not getting migrated with this method. These objects need to migrated using datapump method.

 

POST MIGRATION ACTIVITY:

                                       In this phase, we will bring the missing objects from source to target. In our case, we observed that PROCEDURE,VIEW,TYPE,FUNCTION,SEQUENCE were missing. Lets take export these object types and import in target.

expdp dumpfile=test2.dmp logfile=test1.dmp directory=SOURCEDUMP schemas=WEBMDB include=SEQUENCE,function,TYPE,view,PROCEDURE

transfer the dump and import in target:

impdp dumpfile=test2.dmp logfile=test2.log directory=dpump_tts

 

Now compile all the objects in target using utlrp.sql and check the object count:

We can see all objects were migrated successfully. With this the migration activity has been completed . And only downtime window we required is only from the incremental backup to till end .