Cross platform migration using transportable tablespace 12c with zero downtime DBACLASS

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 database, 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

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

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------
Prepare source for Tablespaces:
                  'WEBMDATA'  /b2cdev2/TEST_TAR
xttpreparesrc.sql for 'WEBMDATA' started at Wed Aug 17 18:21:04 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:21:05 2016
Prepare source for Tablespaces:
                  'WEBMINDX'  /b2cdev2/TEST_TAR
xttpreparesrc.sql for 'WEBMINDX' started at Wed Aug 17 18:26:08 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:26:09 2016
Prepare source for Tablespaces:
                  ''  /b2cdev2/TEST_TAR
xttpreparesrc.sql for '' started at Wed Aug 17 18:27:11 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:27:12 2016
Prepare source for Tablespaces:
                  ''  /b2cdev2/TEST_TAR
xttpreparesrc.sql for '' started at Wed Aug 17 18:27:14 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:27:15 2016
Prepare source for Tablespaces:
                  ''  /b2cdev2/TEST_TAR
xttpreparesrc.sql for '' started at Wed Aug 17 18:27:17 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:27:18 2016
Prepare source for Tablespaces:
                  ''  /b2cdev2/TEST_TAR
xttpreparesrc.sql for '' started at Wed Aug 17 18:27:20 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:27:20 2016
Prepare source for Tablespaces:
                  ''  /b2cdev2/TEST_TAR
xttpreparesrc.sql for '' started at Wed Aug 17 18:27:23 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:27:23 2016
Prepare source for Tablespaces:
                  ''  /b2cdev2/TEST_TAR
xttpreparesrc.sql for '' started at Wed Aug 17 18:27:26 2016
xttpreparesrc.sql for  ended at Wed Aug 17 18:27:26 2016

--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------

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

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

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Start restore/recover
--------------------------------------------------------------------

--------------------------------------------------------------------
End of restore/recover phase
--------------------------------------------------------------------

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

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


--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'WEBMDATA'
Prepare newscn for Tablespaces: 'WEBMINDX'
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
rman target /  cmdfile /export/home/oracle/DBA_LAB/rmanincr.cmd

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 17 19:12:01 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: WMTRAIN (DBID=764374840)

RMAN> set nocfau;
2> host 'echo ts::WEBMDATA';
3> backup for transport allow INCONSISTENT incremental from scn 166287830
4>   tablespace 'WEBMDATA' format
5>  '/export/home/oracle/DBA_LAB/SOURCEDUMP/%U';
6> set nocfau;
7> host 'echo ts::WEBMINDX';
8> backup for transport allow INCONSISTENT incremental from scn 166287830
9>   tablespace 'WEBMINDX' format
10>  '/export/home/oracle/DBA_LAB/SOURCEDUMP/%U';
11>
executing command: SET NOCFAU
using target database control file instead of recovery catalog

ts::WEBMDATA
host command complete

Starting backup at 17-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=889 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmdata01.dbf
input datafile file number=00008 name=/WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmdata02.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-16
channel ORA_DISK_1: finished piece 1 at 17-AUG-16
piece handle=/export/home/oracle/DBA_LAB/SOURCEDUMP/09rdggs6_1_1 tag=TAG20160817T191205 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:25
Finished backup at 17-AUG-16

executing command: SET NOCFAU

ts::WEBMINDX
host command complete

Starting backup at 17-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmindx01.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-16
channel ORA_DISK_1: finished piece 1 at 17-AUG-16
piece handle=/export/home/oracle/DBA_LAB/SOURCEDUMP/0ardgh2k_1_1 tag=TAG20160817T191531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 17-AUG-16

Recovery Manager complete.


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

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

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


--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Start restore/recover
--------------------------------------------------------------------

--------------------------------------------------------------------
End of restore/recover phase
--------------------------------------------------------------------


STEP 7 : ( GET LATEST SCN FROM SOURCE):


 $ORACLE_HOME/perl/bin/perl xttdriver.pl -s
 
 --------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'WEBMDATA'
Prepare newscn for Tablespaces: 'WEBMINDX'
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
New /export/home/oracle/DBA_LAB/xttplan.txt with FROM SCN's generated

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

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'WEBMDATA'
Prepare newscn for Tablespaces: 'WEBMINDX'
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
Prepare newscn for Tablespaces: ''
rman target /  cmdfile /export/home/oracle/DBA_LAB/rmanincr.cmd

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 17 19:22:15 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: WMTRAIN (DBID=764374840)

RMAN> BACKUP FOR TRANSPORT INCREMENTAL from scn 166314204 TABLESPACE  WEBMDATA,WEBMINDX FORMAT '/export/home/oracle/DBA_LAB/SOURCEDUMP/%U' DATAPUMP FORMAT '/export/home/oracle/DBA_LAB/SOURCEDUMP/%
';
2>
Starting backup at 17-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=937 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces...
   EXPDP> Starting "SYS"."TRANSPORT_EXP_WMTRAIN_aiao":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TRIGGER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TRANSPORT_EXP_WMTRAIN_aiao" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TRANSPORT_EXP_WMTRAIN_aiao is:
   EXPDP>   /u01/app/oracle/product/12.1.0.2/WMTRAIN/dbs/backup_tts_WMTRAIN_28718.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace WEBMDATA:
   EXPDP>   /WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmdata01.dbf
   EXPDP>   /WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmdata02.dbf
   EXPDP> Datafiles required for transportable tablespace WEBMINDX:
   EXPDP>   /WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmindx01.dbf
   EXPDP> Job "SYS"."TRANSPORT_EXP_WMTRAIN_aiao" successfully completed at Wed Aug 17 19:24:28 2016 elapsed 0 00:01:51
Export completed

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmdata01.dbf
input datafile file number=00006 name=/WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmindx01.dbf
input datafile file number=00008 name=/WMTRAIN/oradata/WMTRAIN/WMTRAIN/webmdata02.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-16
channel ORA_DISK_1: finished piece 1 at 17-AUG-16
piece handle=/export/home/oracle/DBA_LAB/SOURCEDUMP/0brdghjg_1_1 tag=TAG20160817T192219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
input Data Pump dump file=/u01/app/oracle/product/12.1.0.2/WMTRAIN/dbs/backup_tts_WMTRAIN_28718.dmp
channel ORA_DISK_1: starting piece 1 at 17-AUG-16
channel ORA_DISK_1: finished piece 1 at 17-AUG-16
piece handle=/export/home/oracle/DBA_LAB/SOURCEDUMP/0crdghk9_1_1 tag=TAG20160817T192219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-AUG-16

Recovery Manager complete.


--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

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

$ORACLE_HOME/perl/bin/perl xttdriver.pl --resincrdmp
  
 --------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Start restore/recover
--------------------------------------------------------------------

--------------------------------------------------------------------
End of restore/recover phase
--------------------------------------------------------------------

--------------------------------------------------------------------
Start creating dumpfile
--------------------------------------------------------------------

--------------------------------------------------------------------
End of creating dumpfile
--------------------------------------------------------------------

--------------------------------------------------------------------
Generating plugin
--------------------------------------------------------------------

--------------------------------------------------------------------
Done generating plugin file /b2cdev2/TEST_TAR/xttplugin.txt
--------------------------------------------------------------------

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”

#impdp parfile="imp_ts.par"

Import: Release 12.1.0.2.0 - Production on Wed Aug 17 19:28:56 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA parfile=imp_ts.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"WEBMDB"."ISTRIGDOC_DOCID_TBI" created with compilation warnings
ORA-39082: Object type TRIGGER:"WEBMDB"."ISDATSTR_ID_TBI" created with compilation warnings
ORA-39082: Object type TRIGGER:"WEBMDB"."WMTBLUF005" created with compilation warnings
ORA-39082: Object type TRIGGER:"WEBMDB"."WMTBLUF3LD" created with compilation warnings
ORA-39082: Object type TRIGGER:"WEBMDB"."WMTBLUF0EU" created with compilation warnings
ORA-39082: Object type TRIGGER:"WEBMDB"."WMTBLUF0HG" created with compilation warnings
ORA-39082: Object type TRIGGER:"WEBMDB"."CMPNNTVNT_RBI" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."BI_MED_EVENT_TXN" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."ISTRIGDOC_DOCID_TBI" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."ISDATSTR_ID_TBI" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."BI_MED_EVENT_MON" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."BI_MED_EVENT_PV" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."BI_MED_EVENT_ERR" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."BI_MED_EVENT_LC" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."BI_MED_EVENT_METRICS" created with compilation warnings
ORA-39082: Object type TRIGGER:"WMMED"."CMPNNTVNT_RBI" created with compilation warnings
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 16 error(s) at Wed Aug 17 19:29:47 2016 elapsed 0 00:00:46

Lets compare the object count in both source and target:

Source:

SQL>  select object_type,count(*) from dba_objects where owner='WEBMDB' group by object_type;


OBJECT_TYPE               COUNT(*)
----------------------- ----------
INDEX                          139
PROCEDURE                       18
TABLE                           67
TRIGGER                          9
VIEW                             1
TYPE                             3
FUNCTION                         2
SEQUENCE                         8
LOB                             33

on target:

SQL> select object_type,count(*) from dba_objects where owner='WEBMDB' group by object_type;

OBJECT_TYPE               COUNT(*)
----------------------- ----------
INDEX                          139
TABLE                           67
TRIGGER                          9
LOB                             33

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

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

Import: Release 12.1.0.2.0 - Production on Wed Aug 17 20:11:05 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=test2.dmp logfile=test2.log directory=dpump_tts
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 17 20:11:11 2016 elapsed 0 00:00:03

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

SQL> select object_type,count(*) from dba_objects where owner='WEBMDB' group by object_type;


OBJECT_TYPE               COUNT(*)
----------------------- ----------
INDEX                          139
PROCEDURE                       18
TABLE                           67
TRIGGER                          9
TYPE                             3
FUNCTION                         2
VIEW                             1
LOB                             33
SEQUENCE                         8

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 .

7 thoughts on “Cross platform migration using transportable tablespace 12c with zero downtime”

Dave Cleary says:

October 23, 2017 at 2:13 pm

Misleading title. This process has downtime, but the title says Zero Downtime.

1. Admin says:
  
  October 30, 2017 at 7:57 pm
  
  Hi Dave,
  
  Oracle only named it as zero downtime method. As mentioned the downtime is very minimal for this methods though not completely zero.
  
Pramod says:

August 15, 2018 at 5:11 pm

Can this be applied on migration with different endian format in Multitenant Architecture? We have Single Tenant database in Container and we wanted to migrate to Linux with only PDB migration. Can you let me know if that is possible by using these scripts? From Oracle Support site we got to know for MultiTenant cant be used. But we wanted to see if that is Possible

Pritesh Devda says:

October 3, 2019 at 1:30 pm

Hello,
You have mentioned impdp14442_745.dmp as dumpfile name which is different from generated at source side. please check. Thanks

Steve A says:

October 25, 2019 at 3:40 pm

in my experience using xttconvert, the incremental backups take every bit as much time as full backups so the downtime can be significant depending on the size of your database..

YMMV

JesC says:

May 18, 2021 at 2:41 pm

Hi,

Thanks for taking the time to publish this and your other resources.

Is this a typo – “The first step will take a full backup of the script”? Of the script? Of the database?

1. Admin says:
  
  May 18, 2021 at 2:44 pm
  
  I have corrected . Thank you 🙂

Cross platform migration using transportable tablespace 12c with zero downtime

PREPARATION PHASE:

INITIAL PHASE:

ROLLING FORWARD PHASE:

FINAL INCREMENTAL BACKUP:

POST MIGRATION ACTIVITY:

About Admin

7 thoughts on “Cross platform migration using transportable tablespace 12c with zero downtime”

Leave a Reply Cancel reply

PREPARATION PHASE:

INITIAL PHASE:

ROLLING FORWARD PHASE:

FINAL INCREMENTAL BACKUP:

POST MIGRATION ACTIVITY:

About Admin

Related Articles

7 thoughts on “Cross platform migration using transportable tablespace 12c with zero downtime”

Leave a Reply Cancel reply