Introduction:

For one of the projects the question came in to investigate and set up a  11.2.0.4 Real application cluster database with an extra challenge that the migration had to be done cross-platform from Oracle 10.2.0.3 on Solaris platform to 11.2.0.4.0 on Linux. From application provider came the suggestion to investigate a back-up – restore scenario with an upgrade on the new server ( Linux environment). Due to the fact that the Source environment was 10.20.3 on Solaris and due to fact we were heading towards a Rac cluster environment on on Linux that suggestion was  the first that was send to the dustbin.

Normal export  / import was the second scenario that was explored. Of course this is a valid scenario  but given  the fact that the database was more than 1.x TB not exactly the most favorite way to bring  the data across. But whit scripting and using multiple par-files  and or with moving  partitioned data across in waves would be a  fair plan-b.

From reading though Had put my mind to the use of  transportable tablespaces as a way forward with this challenging question.

Requirements:

As preparation for the job requested to have Nas filesystem mounted between the source Server (MySunServer) holding the 10G database and the target Server (MyLinuxcluster). This Nas filesystem  would hold  the datapumps to be created, to hold the scripts and parfiles  / config files as was suggested  based on Mos Note ( 1389592.1 ). Nas system was / read-writable from both  servers. The  perl scripts that come with the note will support in the transport of the tablespaces but also help in  the convert of big endian to little endian And as a bonus in my case will do the copy into ASM.

Due to the layout of the database in the source environment  Rman was chosen as the best way forward with the scenario.

As a preparation an 110204 Rac database was set up on the target cluster. This  database only to hold the normal tablespaces and a smal temporary tablespace for the users. ( In TTS solution the name of the data tablespaces that come across to the new environment may not exist in the new environment). All data- application users have been pre created on the new environment with a  new – default user tablespace.

Details & Comments

Configuration file for the Perl scripts:

This is a file  that is part of the unzipped file from the Mos note. It needs to be setup to match your specific needs.  Will only show settings  I have used and  its comments:

xtt.properties:

## Reduce Transportable Tablespace Downtime using Incremental Backups

## (Doc ID 1389592.1)

## Properties file for xttdriver.pl

## See documentation below and My Oracle Support Note 1389592.1 for details.

## Tablespaces to transport

## Specify tablespace names in CAPITAL letters.

tablespaces=MYDB_DATA,MYDB_EUC_DATA,MYDB_EUC_INDEX,MYDB_INDEX,MYTS,USERS

##tablespaces=MYCOMPTTS

## Source database platform ID

## platformid

## Source database platform id, obtained from V$DATABASE.PLATFORM_ID

platformid=2

## srclink

## Database link in the destination database that refers to the source

## database. Datafiles will be transferred over this database link using

## dbms_file_transfer.

srclink=TTSLINK

## Location where datafile copies are created during the “-p prepare” step.

## This location must have sufficient free space to hold copies of all

## datafiles being transported.

dfcopydir=/mycomp_mig_db_2_linux/mybox/rman

## backupformat

## Location where incremental backups are created.

backupformat=/mycomp_mig_db_2_linux/mybox/rman

## Destination system file locations

## stageondest

## Location where datafile copies are placed by the user when they are

## transferred manually from the souce system. This location must have

## sufficient free space to hold copies of all datafiles being transported.

stageondest=/mycomp_mig_db_2_linux/mybox/rman

# storageondest

## This parameter is used only when Prepare phase method is RMAN backup.

## Location where the converted datafile copies will be written during the

## "-c conversion of datafiles" step. This is the final location of the

## datafiles where they will be used by the destination database.

storageondest=+MYDBP_DATA01/mydbp/datafile

## backupondest

## Location where converted incremental backups on the destination system

## will be written during the "-r roll forward datafiles" step.

## NOTE: If this is set to an ASM location then define properties

##      asm_home and asm_sid below. If this is set to a file system

##       location, then comment out asm_home and asm_sid below

backupondest=+MYDBP_DATA01/mydbp/datafile

## asm_home, asm_sid

## Grid home and SID for the ASM instance that runs on the destination

asm_home=/opt/crs/product/11204/crs

asm_sid=+ASM1

## Parallel parameters

parallel=8

## rollparallel

## Defines the level of parallelism for the -r roll forward operation.

## If undefined, default value is 0 (serial roll forward).

rollparallel=2

## getfileparallel

## Defines the level of parallelism for the -G operation

getfileparallel=4

## desttmpdir

## This should be defined to same directory as TMPDIR for getting the

## temporary files. The incremental backups will be copied to directory pointed

## by stageondest parameter.

desttmpdir=/mycomp_mig_db_2_linux/MYDBP/scripts

 

Below in a Table format you will see the steps performed with comments.

Steps do qualify for

• I for Initial steps – activities
• P for Preparation
• R for Roll Forward activities
• T for Transport activities

Server column shows where the action needs to be done.

Step	Server	What needs 2 b done
I1.3	Source	Identify the tablespace(s) in the source database that will be transported ( Application owner needs to support with schema owner information) :
		tablespaces=MYDB_DATA,MYDB_EUC_DATA,MYDB_EUC_INDEX, MYDB_INDEX,MYTS,USERS
I1.5	Source + Target	In my case project offered an nfs filesystem which i could use : Nfs filesystem : /mycomp_mig_db_2_linux
I1.6	Source	Together with the Mos note cam  this zip file : Unzip rman-xttconvert.zip.
I1.7	Source	Tailor the extracted file xtt.properties file on the source system to match your environment.
I1.8	Target	As the oracle software owner copy all xttconvert scripts and the modified xtt.properties file to the destination system. This was not needed since we used the nas filesystem.
P1.9	Source + Target	On both environments set up this: export TMPDIR= /mycomp_mig_db_2_linux/MYDBP/scripts.
P2B.1	Source	perl xttdriver.pl -p
		Note. Do Not use ]$ $ORACLE_HOME/perl/bin/perl this did not work
P2B.2	Source	Copy files to destination. N/A since we use NFS
P2B3	Target	On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, copy the rmanconvert.cmd file created in step 2B.1 from the source system and run the convert datafiles step as follows:
		[oracle@dest]$ scp oracle@source:/home/oracle/xtt/rmanconvert.cmd /home/oracle/xtt N/A since we use NFS.
		perl/bin/perl xttdriver.pl –c
R3.1	Source	On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the create incremental step as follows:
		perl xttdriver.pl –I
R3.3	Target	[oracle@dest]$ scp oracle@source:/home/oracle/xtt/xttplan.txt /home/oracle/xtt
		[oracle@dest]$ scp oracle@source:/home/oracle/xtt/tsbkupmap.txt /home/oracle/xtt
		Since we are using Nas shared filesystem no need to copy with scp  between source and target.
		perl xttdriver.pl -r
R3.4	Source	On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the determine new FROM_SCN step as follows:
		perl xttdriver.pl –s
R3.5	Source	1.     If you need to bring the files at the destination database closer in sync with the production system, then repeat the Roll Forward phase, starting with step 3.1.
		2.     If the files at the destination database are as close as desired to the source database, then proceed to the Transport phase.
T4.0	Source	As found in note : Alter Tablespace Read Only Hanging When There Are Active TX In Any Tablespace (Doc ID 554832.1). A restart of the database is required to have no active transactions. Alternative during off hours . Actually during a first test with one dedicated tablespace with only one  object it took more than 7 hrs. Oracle seems to look and wait  for ALL active transactions, not only the ones that would impact   the object in the test tablespace i worked with.
T4.1	Source	On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, make the tablespaces being transported READ ONLY.
		alter tablespace MYDB_DATA read only;
		alter tablespace MYDB_EUC_DATA read only;
		alter tablespace MYDB_EUC_INDEX read only;
		alter tablespace MYDB_INDEX read only;
		alter tablespace MYTS read only;
		alter tablespace USERS read only;
T4.2	Source	Repeat steps 3.1 through 3.3 one last time to create, transfer, convert, and apply the final incremental backup to the destination datafiles.
		perl xttdriver.pl -i
T4.2	Target	[oracle@dest]$ scp oracle@source:/home/oracle/xtt/xttplan.txt /home/oracle/xtt
		[oracle@dest]$ scp oracle@source:/home/oracle/xtt/tsbkupmap.txt /home/oracle/xtt
		perl xttdriver.pl –r
..
T4.3	Target	On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, run the generate Data Pump TTS command step as follows:
		perl xttdriver.pl –e
		The generate Data Pump TTS command step creates a sample Data Pump network_link transportable import command in the file xttplugin.txt. It will hold list of all the TTS you have configured and all its transport_datafiles in details.
		Example of that generated file : cat xttplugin.txt
		impdp directory=MYDB_XTT_DIR logfile=tts_imp.log \
		network_link=TTSLINK.PROD.NL transport_full_check=no \
		transport_tablespaces=MYCOMPTTS ,A,B,C\
		transport_datafiles=’+MYDBP_DATA01/mycomptts_152.xtf’
		Note in our example once edited we chmodded   xttplugin.txt with 744 and ran it as script .
T4.3	Source	After the object metadata being transported has been extracted from the source database, the tablespaces in the source database may be made READ WRITE again, if desired.
T4.4	Target	At this step, the transported data is READ ONLY in the destination database.  Perform application specific validation to verify the transported data.
		Also, run RMAN to check for physical and logical block corruption by running VALIDATE TABLESPACE as follows:
		In rman:
		validate tablespace MYDB_DATA, MYDB_EUC_DATA, MYDB_EUC_INDEX, MYDB_INDEX, MYTS, USERS check logical;
T4.5	Target	alter tablespace MYDB_DATA read write;
		alter tablespace MYDB_EUC_DATA read write;
		alter tablespace MYDB_EUC_INDEX read write;
		alter tablespace MYDB_INDEX read write,
		alter tablespace MYTS read write;
		alter tablespace USERS read write;
T5	Source + Target	Cleanup of NFS filesystem.
		Put Source Db in restricted mode as a fallback after the go live for couple of days then put it to tape and decommission it;