Migrate database to Exadata with DBMS_FILE_TRANSFER

In this Document

Applies to:

Goal

Database migration to Exadata can be accomplished in a number of ways, as discussed in
Oracle? Database High Availability Best Practices 11g Release 2 (11.2)
Chapter 14 Reducing Downtime for Planned Maintenance
Section 14.2.7 Database Platform or Location Migration.

One way to perform this task is the Transportable Tablespaces for Platform Migration. The method described in this document can be used to migrate any database (version 10.2 to 11.2.0.4), from any platform, to 11.2.0.4 and later on Exadata. We have successfully performed the migration from Solaris SPARC (database versions 10.2.0.1, 10.2.0.4 and 11.2.0.3), IBM AIX (database version 11.1.0.7) and Linux (database version 11.2.0.3) to 11.2.0.4 and 12.1.0.1.0 on Exadata.

Solution

Introduction

This document shows an example of the transportable tablespaces for platform migration from 10.2.0.4 on Solaris SPARC to 11.2.0.4 on Exadata. For more examples please see the attachments:




In the initial phase we determine which tablespaces from the source database will be migrated and make sure all datafiles for those tablespaces are available. We ensure the destination database is available and that it has the same character set and the national character set as the source database. We download the rman_xttconvert_1.4.zip from Doc ID 1389592.1, and configure our migration environment.

In the phase two, we transport the datafiles from the source to the destination, using the DBMS_FILE_TRANSFER package. More specifically, we run DBMS_FILE_TRANSFER.GET_FILE procedure in the destination database, to transfer the datafiles from the source and perform the endian conversion. Note that we do this with the fuzzy datafiles, i.e. all source datafiles are in read write mode during this phase.

In the phase three, we take incremental backups of the source datafiles we transferred, and merge them with the datafiles on the destination. The aim of this approach is to reduce the downtime of the source database, during the migration process. The incremental backups and the merge with the destination datafiles, allow us to keep the destination datafiles in near sync with the source, regardless of the time it takes to transfer the datafiles.

If the source database downtime (duration) is of no concern, there is no need to use the method described in this document. You could simply perform the transportable tablespace migration in three steps: create transportable tablespace set on source, transfer the datafiles and metadata to destination, and plug the tablespaces in at the destination.

In the phase four, we put the datafiles in in read only mode, take the final incremental backup, merge those backups, and plug in the tablespaces into the destination database. The total downtime is equivalent to the time it takes to complete this phase.

1. Initial setup (phase 1)

1.1. Source database

The source database (SPARCDB) version 10.2.0.4.0, is running on Solaris SPARC 64-bit:

The tablespaces to be transported are TSPACE1, TSPACE2 and TSPACE3:

Note that the tablespace TSPACE1 has a single datafile, TSPACE2 has two datafiles, and TSPACE3 has three datafiles.

The source directory object (for DBMS_FILE_TRANSFER) is SOURCEDIR:

The source database is in archivelog mode:

The TMPDIR environment variable is set as follows:

The rman_xttconvert_1.4.zip was transferred and extracted to /export/home/oracle/xtt directory:

The parameters in the xtt.properties were defined as follows:

Most of the parameters are self explanatory and are described in the xtt.properties file. Note that the platformid should be set to the source database platform id, obtained by querying v$database as shown above.

The xttconvert files, including the xtt.properties with the parameters defined in the previous step, were copied from the source to the destination server:

1.2. Destination database

The destination database (EXADB) version 11.2.0.4.0, is running on Exadata (Linux x86 64-bit):

NOTE: It is important that both the source and the destination database have the same character set and the national character set. In our case those are AL32UTF8 and UTF8:

Database link to the source database is SPARCLINK:

The destination directory object (for DBMS_FILE_TRANSFER) is DESTDIR:

The staging area on the destination server (xtt.properties::stageondest) is directory /home/oracle/stage_dest.

The TMPDIR environment variable is set as follows:

2. Transport datafiles (phase 2)

During this phase the datafiles are transferred from the source to the final location in the destination database. The endian conversion is performed automatically by the DBMS_FILE_TRANSFER package. The steps in this phase are run only once. The data being transported is fully accessible in the source database during this phase.

2.1. Create the transport scripts on the source system

On the source system, log in as the oracle user, set the environment to the source database, and set the TMPDIR:

Run the 'perl xttdriver.pl -S':

This step created the following files in the current (TMPDIR) directory:

1. xttplan.txt - containing the tablespace names, their current SCNs and their datafile numbers:

Those SCNs will be used in phase 3 (incrementally updated backups).

2. xttnewdatafiles.txt - containing the tablespace names, datafile numbers, the destination directory object name and the source filenames:

3. getfile.sql - The PL/SQL script (formatting ours) that will be used at the destination to get the datafiles from the source:

Note the destination filenames (destination_file_name) with the underscore replacing the dot. Those will be the ASM aliases for the datafiles in the destination database.

4. xttpreparesrc.sql - the PL/SQL script used to create the files in this step.

5. xttprepare.cmd - the command script (empty at this step).

2.2. Transfer the datafiles to the destination

On the destination server, log in as the oracle user and set the environment to the destination database.

Copy the xttnewdatafiles.txt and getfile.sql files from the source server:

Transfer the datafiles, by running 'xttdriver.pl -G'.

Note that this step does not produce any output. This step is going to take the bulk of the database migration time, as we are transferring all datafiles from the source to the destination.

When this step is complete, the datafiles being transported will reside in the final location where they will be used by the destination database. We can verify this with the asmcmd command:

Note the ASM alias names for the transported datafiles and the actual filenames (FILE_TRANSFER.nnn.kkkkkkkkk).

3. Incrementally updated backups (phase 3)

An incrementally updated backup is an RMAN datafile copy that is updated by an incremental backup. In this case, we will be using the datafile copies created with the DBMS_FILE_TRANSFER.GET_FILE, instead of RMAN BACKUP AS COPY. During this phase the incremental backups are created at the source database, transferred to the destination system and merged with the datafiles transferred in phase 2.

The steps in this phase may be run multiple times, to get the datafiles in the destination closer to the time/SCN of the datafiles on the source. The data being transported is fully accessible in the source database during this phase.

3.1. Create incremental backups of the source datafiles

On the source system, logged in as the oracle user, with the environment set to the source database, and the TMPDIR set, run the 'xttdriver.pl -i':

This step created the following files in the current (TMPDIR) directory:

1. tsbkupmap.txt - containing the tablespace names, datafile numbers and the incremental backup pieces:

2. incrbackups.txt - containing the actual location of the incremental backup pieces:

3. rmanincr.cmd - containing the RMAN scripts used to create the incremental backups.

4. xttdetnewfromscnsrc.sql - the PL/SQL script used to create the files in this step

5. xttplan.txt.new- after the first run of 'xttdriver.pl -i' this is just a copy of the xttplan.txt

3.2. Transfer incremental backups to destination

Transfer the incremental backups to the stageondest location on the destination system.

3.3. Apply incremental backups on destination

On the destination server, log in as the oracle user and set the environment to the destination database. Copy the xttplan.txt and tsbkupmap.txt files from the source system

Merge the incremental backups with the datafiles, by running 'xttdriver.pl -r':

It is safe to ignore the "Can't locate strict.pm in @INC..." messages. We believe those are the result of the Perl pragma 'use strict' in the asmcmdcore.

At this step, the incremental backups were put in the backup destination and merged with the datafile copies.

Verify that the incremental backups are in the correct location:

3.4. Determine the FROM SCN for next incremental backup

On the source system, logged in as the oracle user, with the environment set to the source database, and the TMPDIR set, run the 'xttdriver.pl -s':

At this step the xttplan.txt file is copied to xttplan.txt. The 'FROM SCN' will be recorded in the xttplan.txt, next time the 'xttdriver.pl -i' is run (if required).

As noted earlier, the steps in phase 3 can be run as many times as required. For each additional run, we would run 'xttdriver.pl -i', copy the incremental backup, xttplan.txt and tsbkupmap.txt from the source to the destination, run 'xttdriver.pl -r' on the destination, and finally run 'xttdriver.pl -s' on the source.

In this example, I am not repeating steps in phase 3, and instead moving to phase 4.

4. Plug in transported tablespaces (phase 4)

During this phase the source datafiles are put in READ ONLY mode, and the destination datafiles are made consistent with the source by creating and merging the final incremental backups. Finally, the transportable tablespace metadata is imported into the destination database. The data being transported is accessible in READ ONLY mode until the end of this phase.

4.1. Make source tablespaces READ ONLY

On the source system, logged in as the oracle user, with the environment set to the source database, put the three tablespaces in READ ONLY mode:

4.2. Final incremental backup

Create the final incremental backup by running 'xttdriver.pl -i':

Copy the incremental backups, the xttplan.txt and the tsbkupmap.txt to the destination server:

4.3. Final incremental backup merge

Perform the final incremental backup merge by running 'xttdriver.pl -r':

4.4. Import the transported tablespaces metadata

Generate the data pump import script by running 'xttdriver.pl -e':

Edit xttplugin.txt to specify the correct values for , and . In my case the final impdp command is:

Where XTT_DIR is /home/oracle/xtt and the SPARCLINK is the database link created in phase 1.

Import the transported tablespaces metadata into the destination database by running the data pump import:

4.5. Validate plugged in tablespaces at destination

At this stage you may want to check if all tablespaces have been migrated, and if all data is present in the destination database. I have simply put the tablespaces into READ WRITE mode and queried their datafiles:

Conclusion

One way to migrate a database to Exadata is by performing a cross platform transportable tablespaces migration. This document shows an example of such migration from a single instance database version 10.2.0.4 on Solaris SPARC, to a two node RAC database version 11.2.0.4 on Exadata (Linux x86 64-bit).