IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
I will explain New Oracle XTTS , Cross Platform Oracle Database Migration Using Transportable Tablespace ( XTTS ) With Zero Downtime in this article.
You can read OLD Version XTTS article Before this article.
https://ittutorial.org/cross-platform-oracle-12c-migration-using-transportable-tablespace-xtts-with-zero-downtime/
Transportable Tablespace method can be used for Oracle database Migration and Upgrade when the operating systems of the source database and the target database are different and conversion between 32 bits and 64 bits.
When using Cross Platform Transportable Tablespaces (XTTS) to migrate database between systems that have different endian formats, the amount of downtime required is related directly proportional to the size of the data set being moved. To reduce amount of downtime, Oracle recommend Cross Platform Incremental Backup with Oracle 12c.
This article describes V4 XTTS procedures for Cross Platform Incremental Backup which is available for 11.2.0.4 and higher.
This new XTTS is simplified according to previous XTTS versions.
This version has the following differences:
- New XTTS uses simplified commands. One command (–backup) for the source and one command (–restore) for the destination.
- New XTTS requires only one file to be copied between the source’s and destination’s $TMPDIR (res.txt).
- New XTTS will automatically resolve added datafiles with no additional intervention.
- New XTTS allows for multiple incremental backups taken off the source without running the recovery. After which, recovery will be of all the incremental backups in the destination at once.
The high-level steps of V4 XTTS – Cross Platform Incremental Backup method are as follows.
Step 1. Initial setup
Step 2. Prepare phase (source data remains online)
2.1- Backup (level=0) of tablespaces to be transported
2.2- Transfer backup and other necessary setup files to destination system
2.3- Restore datafiles on destination system endian format
Step 3. Roll Forward phase (source data remains online – Repeat this phase as many times as necessary to catch destination datafile copies up to source database)
3.1- Create incremental backup on source system
3.2- Transfer incremental backup and other necessary setup files to destination system
3.3- Convert incremental backup to destination system endian format and apply the backup to the destination datafile copies
3.4- Repeat steps until ready to transport the tablespace.
Step 4. Transport phase (source data is READ ONLY)
4.1- Make tablespaces in source database READ ONLY
4.2- Repeat the Roll Forward phase one final time
This step makes destination datafile copies consistent with source database.
Time for this step is significantly shorter than traditional XTTS method when dealing with large data because the incremental backup size is smaller.
4.3- Create the final incremental backup and transfer to the destination.
4.4- Apply last incremental backup with nohup to destination datafiles
4.5 Run datapump export on source database:
4.6 Run datapump import on destination to plug in the tablespaces.
4.7- Alter the tablespaces in the destination database to READ WRITE
Step 5. Validation and Check after Migration
Step-1: Install Oracle database 12c or higher on Target side.
If you don’t know how to install it, you can read following article.
After Oracle 12c software and database installation on Target side, you should upload XTTS software both Source and Target side.
You can download new XTTS software ( rman_xttconvert_VER4.3.zip ) from this link, if you don’t have MOSC account.
Unzip rman_xttconvert_VER4.3.zip file as follows.
[oracle@MehmetSalihSource]$ pwd /home/oracle/xtt [oracle@MehmetSalihTarget]$ unzip rman_xttconvert_VER4.3.zip Archive: rman_xttconvert_VER4.3.zip inflating: xtt.newproperties inflating: xtt.properties inflating: xttcnvrtbkupdest.sql inflating: xttdbopen.sql inflating: xttdriver.pl inflating: xttprep.tmpl extracting: xttstartupnomount.sql [oracle@MehmetSalihTarget]$ pwd /home/oracle/xtt [oracle@MehmetSalihTarget]$ unzip rman_xttconvert_VER4.3.zip Archive: rman_xttconvert_VER4.3.zip inflating: xtt.newproperties inflating: xtt.properties inflating: xttcnvrtbkupdest.sql inflating: xttdbopen.sql inflating: xttdriver.pl inflating: xttprep.tmpl extracting: xttstartupnomount.sql
Display xtt.properties file and edit following parameters as follows.
tablespaces=DATATBS1,DATATBS2,DATATBS3,DATATBS4,DATATBS5 platformid=13 dfcopydir=/backup_stage backupformat=/backup_stage stageondest=/backup_stage storageondest=+DATA/DEVECI/datafile backupondest=/backup_stage parallel=8
You can modify these parameters’ value according to your environment.
Step-2: Backup (level=0) of tablespaces to be transported as follows.
Step-2.1: I will run backup with nohup as follows. Some part of logfile content is as follows
export TMPDIR=/home/oracle/xtt nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup &
============================================================
trace file is /backup/v4TTSource/backup_Oct6_Sun_10_01_42_283//Oct6_Sun_10_01_42_283_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
-------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------
scalar(or16
XXX: adding here for 2, 0, ....
--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------
sqlplus -L -s / as sysdba @/backup/v4TTSource/backup_Oct6_Sun_10_01_42_283//diff.sql +DATA
--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------
Prepare source for Tablespaces:
'USERS' /backup/backup
xttpreparesrc.sql for 'USERS' started at Sun Oct 6 10:01:44 2019
xttpreparesrc.sql for ended at Sun Oct 6 10:01:45 2019
Prepare source for Tablespaces:
...........................
...........................
--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'USERS'
Prepare newscn for Tablespaces: 'USER_DATA'
Prepare newscn for Tablespaces: 'USER_INDEX'
New /backup/v4TTSource/xttplan.txt with FROM SCN's generated
scalar(or16
XXX: adding here for 2, 0, USERS,USER_DATA,
Added fname here 1:/backup/backup/USERS_4.tf
Added fname here 1:/backup/backup/USERS_238.tf
Added fname here 1:/backup/backup/USER_DATA_6.tf
Added fname here 1:/backup/backup/USER_DATA_7.tf
Added fname here 1:/backup/backup/USER_DATA_8.tf
Added fname here 1:/backup/backup/USER_DATA_9.tf
...........................
...........................
============================================================
No new datafiles added
=============================================================
============================================================
trace file is /backup/v4TTSource/backup_Oct7_Mon_23_01_40_405//Oct7_Mon_23_01_40_405_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
scalar(or16
XXX: adding here for 2, 0, USERS,USER_DATA,USER_INDEX,
============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'USERS'
Prepare newscn for Tablespaces: 'USER_DATA'
Prepare newscn for Tablespaces: 'USER_INDEX'
--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'USERS'
Prepare newscn for Tablespaces: 'USER_DATA'
Prepare newscn for Tablespaces: 'USER_INDEX'
New /backup/v4TTSource/xttplan.txt with FROM SCN's generated
Step-2.2: Transfer the following Backup and res.txt configuration file to the Target side.
scp DEVECI_65_tcsj1tk1_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_64_tbsj1tk1_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_61_tdsj1tk1_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_59_tfsj1tk1_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_58_tesj1tk1_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_19_tasj1tk1_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_18_t9sj1tjv_1_1.tf 192.168.63.63:/u02/dump
scp DEVECI_11_t8sj1tjv_1_1.tf 192.168.63.63:/u02/dump
scp res.txt 192.168.63.63:/u02/dump
Step-2.3 Restore datafiles on destination system endian format (Target Side). Set TMPDIR and run Restore with Nohup as follows. Some part of logfile content is as follows
export TMPDIR=/home/oracle/xtt nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore & ============================================================ trace file is /backup/v4ttsTarget/restore_Oct8_Tue_16_34_02_490//Oct8_Tue_16_34_02_490_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Performing convert for file 4 -------------------------------------------------------------------- -------------------------------------------------------------------- Performing convert for file 238 -------------------------------------------------------------------- -------------------------------------------------------------------- Performing convert for file 6 -------------------------------------------------------------------- --------------------------------- ============================================================ trace file is /backup/v4ttsTarget/restore_Oct8_Tue_23_37_02_565//Oct8_Tue_23_37_02_565_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Start rollforward -------------------------------------------------------------------- -------------------------------------------------------------------- End of rollforward phase -------------------------------------------------------------------- [MehmetSalihTarget]/backup/v4ttsTarget $
Step 3. Roll Forward phase
Step 3.1- Run incremental backup with nohup on Source side as follows. Some part of logfile content is as follows
export TMPDIR=/home/oracle/xtt nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup &
trace file is /backup/v4TTSource/backup_Oct7_Mon_23_01_40_405//Oct7_Mon_23_01_40_405_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Backup incremental -------------------------------------------------------------------- scalar(or16 XXX: adding here for 2, 0, USERS,USER_DATA,USER_INDEX, ============================================================ No new datafiles added ============================================================= Prepare newscn for Tablespaces: 'USERS' Prepare newscn for Tablespaces: 'USER_DATA' Prepare newscn for Tablespaces: 'USER_INDEX' -------------------------------------------------------------------- Starting incremental backup -------------------------------------------------------------------- -------------------------------------------------------------------- Done backing up incrementals -------------------------------------------------------------------- Prepare newscn for Tablespaces: 'USERS' Prepare newscn for Tablespaces: 'USER_DATA' Prepare newscn for Tablespaces: 'USER_INDEX' New /backup/v4TTSource/xttplan.txt with FROM SCN's generated
3.2- Transfer incremental backup( 0* files) and other necessary setup files ( tsbkupmap.txt incrbackups.txt) to destination system
scp 0* 2* 192.168.63.64:/u02/dump
scp res.txt 192.168.63.64:/home/oracle/xtt
3.3- Convert incremental backup to destination system endian format and apply the backup to the destination datafile copies
Apply the Incremental backup to the destination datafile copies. Run Recover with nohup as follows. Some part of logfile content is as follows
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore &
============================================================ trace file is /backup/v4ttsTarget/restore_Oct8_Tue_23_37_02_565//Oct8_Tue_23_37_02_565_.log ============================================================= -------------------------------------------------------------------- Parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done parsing properties -------------------------------------------------------------------- -------------------------------------------------------------------- Checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Done checking properties -------------------------------------------------------------------- -------------------------------------------------------------------- Start rollforward -------------------------------------------------------------------- -------------------------------------------------------------------- End of rollforward phase -------------------------------------------------------------------- [MehmetSalihTarget]/backup/v4ttsTarget $
Repeat the roll forward phase 3 (3.1 – 3.3) to reduce downtime or proceed to phase 4 for final incremental backup.
Step 4. Transport phase (source data is READ ONLY)
4.1- Make tablespaces in source database READ ONLY
alter tablespace DATATBS1 read only; alter tablespace DATATBS2 read only; alter tablespace DATATBS3 read only; alter tablespace DATATBS4 read only; alter tablespace DATATBS5 read only;
4.2- Repeat the Roll Forward phase one final time. If you want to do, you can continue from Step 3.
4.3- Create the final incremental backup and transfer to the destination.
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup &
Transfer backup files to Target Side.
[oracle@MehmetSalihSource]$ scp `cat incrbackups.txt` oracle@MehmetSalihTarget:/stageondest
[oracle@MehmetSalihSource]$ scp res.txt 192.168.63.64:/home/oracle/xtt
4.4- Apply last incremental backup with nohup to destination datafiles
nohup $ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore &
4.5- Run datapump export on source database:
[oracle@MehmetSalihSource ]$ cat exp.par dumpfile=xttdump.dmp directory=DATA_PUMP_DIR statistics=NONE transport_tablespaces=DATATBS1,DATATBS2,DATATBS3,DATATBS4,DATATBS5 transport_full_check=y logfile=tts_export.log [oracle@MehmetSalihSource ]$ expdp system/manager parfile=exp.par
4.6 Run datapump import on destination to plug in the tablespaces.
[oracle@MehmetSalihTarget ]$ cat manual_imp.par dumpfile= xttdump.dmp directory=DATAPUMP transport_datafiles='/dest_datafile_location/TS1.dbf','/dest_datafile_location/TS2.dbf' [oracle@MehmetSalihTarget ]$ impdp system/oracle parfile=manual_imp.par
4.7- Make tablespaces in destination database READ WRITE
alter tablespace DATATBS1 read write; alter tablespace DATATBS2 read write; alter tablespace DATATBS3 read write; alter tablespace DATATBS4 read write; alter tablespace DATATBS5 read write;
Step 5. Validation and Check after Migration.
RMAN> validate tablespace DATATBS1,DATATBS2,DATATBS3,DATATBS4,DATATBS5 check logical;
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
Hi
What if there is TDE used on tablespace?
it is not problem, you should move the wallet the other side in this case.
I used Doc ID 2471245.1 for migration of 12.2 oracle database on AIX to same version to Linux x86_64, and on phase 5 while importing metadata , impdp fails with ora-39123 and ora-29342. The ??? user does not exist. There are 2.7k + users. Do I have to create all of them before import or do you have any idea how to resolve this problem? Thanks