Jeff Stonacek, Principal Architect
Overview
In part one of this post, we described the high level concept of using Oracle’s new cross platform incremental backup along with transportable tablespaces. These tools allow a DBA to perform cross platform transportable tablespace operations with the source database online, and then later to apply one or more incremental backups of the source database to roll the destination database forward. This can substantially reduce the downtime of a cross platform transportable tablespace operation.
In part two of this post, we will outline the specific steps required to perform this migration using the new cross platform incremental backup functionality.
Complete Migration Steps
The following are the high level steps necessary to complete a cross platform transportable tablespace migration with cross platform incremental backup using the Oracle scripts outlined in MOS note 1389592.1 (MOS note 2005729.1 for 12c):
- Install 11.2.0.4 Oracle home, if not already installed
- Initial configuration of the Oracle perl scripts
- Turn on block change tracking, if not already configured
- Transfer and convert datafiles from source to target
- Perform incremental backup and apply to target
- Repeat incremental backup and apply
- Put tablespaces into read only mode on the source
- Perform final incremental backup and apply to target
- Perform TTS Data Pump import on the target
- Perform metadata only export on the source
- Perform metadata only import on the target
- Audit objects between source and target to ensure everything came over
- Set the tablespaces to read-write on the target
Configure Oracle Perl Scripts
The first step in using this methodology is to download the zip file containing the scripts from MOS note 1389592.1. The current version of the scripts is located in file rman_xttconvert_2.0.zip. Unzip the file on both the source and target systems and then configure the xtt.properties file on both nodes. This is the parameter file that controls the operations. The comments in the file describe how to modify the entries.
Transfer and Convert the Datafiles
Run the Oracle supplied perl script to copy the datafiles to the target system.
nohup perl xttdriver.pl -p > prepare.log 2>&1 &
nohup perl xttdriver.pl -c > convert.log 2>&1 &
Perform the Incremental Backup
Run the script to take the incremental backup on the source.
Copy the following files to the target and apply the incremental backup.
tsbkupmap.txt & xttplan.txt
nohup perl xttdriver.pl -r > incr_apply.log 2>&1 &
Determine the starting SCN for the next incremental backup.
Repeat the incremental backup as many times as necessary.
Transport the Tablespaces
Complete the remaining steps to finish the migration.
1. Place the tablespaces into read only mode on the source.
alter tablespace APP_IDX read only;
alter tablespace APP_DATA2 read only;
…
2. Repeat the incremental backup and incremental apply steps from above.
3. Run a transportable tablespace Data Pump export on the source.
## migrate_tts.par
DIRECTORY=MIG_DIR
LOGFILE=MIG_TTS.log
NETWORK_LINK=ttslink
TRANSPORT_FULL_CHECK=no
TRANSPORT_TABLESPACES=APP_DATA,APP_IDX,APP_DATA2
TRANSPORT_DATAFILES=’/oradata/APP/APP_DATA_01.dbf’,’/oradata/APP/APP_IDX_01.dbf’,…
4. Run a metadata only Data Pump export from the source.
## migrate_meta.par
DIRECTORY = MIG_DIR
DUMPFILE = MIGRATE_META.dmp
LOGFILE = MIGRATE_META.log
FULL = Y
PARALLEL = 8
CONTENT = METADATA_ONLY
JOB_NAME = MIGRATE_META
EXCLUDE = STATISTICS,USER,ROLESTABLESPACE,DIRECTORY,TRIGGERS,INDEXES,TABLES,CONSTRAINTS
SCHEMA:”IN (‘SYSTEM’,’ANONYMOUS’,’DBSNMP’,’DIP’,’EXFSYS’,’MDSYS’,’MGMT_VIEW’,’ORACLE_OCM’,’ORDPLUGINS’,
‘ORDSYS’,’OUTLN’,’SI_INFORMTN_SCHEMA’,’SYSMAN’,’TSMSYS’,’WMSYS’,’XDB’,’PERFSTAT’,
‘OLAPSYS’,’APEX_030200′,’APEX_PUBLIC_USER’,’APPQOSSYS’,’FLOWS_FILES’,’CTXSYS’,’XS$NULL’)”
5. Run the metadata only Data Pump import on the target.
## migrate_meta_imp.par
DIRECTORY = MIG_DIR
DUMPFILE = MIGRATE_META.dmp
LOGFILE = MIGRATE_META_IMP.log
FULL = Y
PARALLEL = 8
JOB_NAME = MIGRATE_META_IMP
6. Reconcile the source and target databases to ensure that all objects came over successfully.
set lines 132 pages 500 trimspool on echo off verify off feedback off
col object_name format a30
select owner, object_type, object_name, status
from dba_objects
where owner not in (‘SYS’, ‘SYSTEM’, ‘TOAD’, ‘SCOTT’, ‘OUTLN’, ‘MSDB1’, ‘DBSNMP’,
‘PUBLIC’, ‘XDB’, ‘WMSYS’, ‘WKSYS’, ‘ORDSYS’, ‘OLAPSYS’, ‘ORDPLUGINS’, ‘ODM’, ‘ODM_MTR’, ‘MDSYS’, ‘CTXSYS’)
order by 1,2,3
minus
select owner, object_type, object_name, status
from dba_objects@ttslink
where owner not in (‘SYS’, ‘SYSTEM’, ‘TOAD’, ‘SCOTT’, ‘OUTLN’, ‘MSDB1’, ‘DBSNMP’,
‘PUBLIC’, ‘XDB’, ‘WMSYS’, ‘WKSYS’, ‘ORDSYS’, ‘OLAPSYS’, ‘ORDPLUGINS’, ‘ODM’, ‘ODM_MTR’, ‘MDSYS’, ‘CTXSYS’)
order by 1,2,3;
set echo on verify on feedback on
7. Set the tablespaces to read write on the target.
alter tablespace APP_IDX read write;
alter tablespace APP_DATA2 read write;
…
Conclusion
In this blog, we have demonstrated the steps for using cross platform incremental backup to reduce downtime for large dataset platform migrations without the need for additional licensed products.
