[Oracle] Migrating SAP systems on Oracle with less...

stefan_koehler · ‎05-09-2010

Introduction

A feature called "Transportable Tablespaces" was introduced in Oracle 8i, but with a lot of restrictions. Release by release the restrictions were reduced and with Oracle 10g R2 it is usable in a SAP environment for migrating whole sap systems.

This blog article will focus on the extended feature "Cross-Platform Transportable Database" with Oracle 10g R2 and its limits.

Let's start with the main restriction on Oracle 10g R2: "The source and destination platforms must have the same endian format". For more informatiom about endianness - please check sapnote #552464 or the link in the references.

There is also another method were this main restriction is removed, but it is not very practicable and not supported in a SAP environment - check sapnote #1367451 for details.

Initial checks

At first we have to check on which platforms we can migrate our database with the procedure "Cross-Platform Transportable Database".

Let's assume our database is running on HP/UX 64 bit - run the following SQL on the database to get the possible target platforms:

shell> sqlplus / as sysdba
SQL> select * from V$DB_TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME   ENDIAN_FORMAT
----------- ----------------------------- --------------
          1 Solaris[tm] OE (32-bit)             Big
          2 Solaris[tm] OE (64-bit)             Big
          6 AIX-Based Systems (64-bit)    Big
          3 HP-UX (64-bit)                         Big
          4 HP-UX IA (64-bit)                    Big
          9 IBM zSeries Based Linux         Big
         16 Apple Mac OS                        Big
         18 IBM Power Based Linux        Big

Check if the database is currenctly in the correct state for transport (in the following example the target platform will be AIX 5L 64 bit)

shell> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database open read only;
SQL> set serveroutput on
SQL> declare
db_ready boolean;
begin
db_ready := dbms_tdb.check_db('AIX-Based Systems (64-bit)',0);
end;
/

Check if the database is using external tables, directories or BFILEs. Normally in a SAP environemnt these objects are not used.

shell> sqlplus / as sysdba
SQL> startup;
SQL> set serveroutput on
SQL> declare
external boolean;
begin
external := dbms_tdb.check_external;
end;
/

Software installation on the target platform

- Install the oracle RDBMS software on your target system with the same patchset and additional patches as on the source system
- Install the SAP appliaction server on your target system with SAPINST or do it manually (whatever you prefer)

The conversion of the database

The conversion can be done on the source or target platform - it depends on you to decide which platform provides the better performance or if you want to copy the system to several different platforms (in this case the conversion has to be done on the target platform). In the following example the conversion will be performed on the source host.

Let's assume again, that our database is running on HP/UX 64 bit and we want to migrate it to AIX 5L 64 bit.

The folder <TARGET_FOLDER> is the directory where the converted database files (and meta files) are stored. You can also influence the throughput with the parameter PARALLELISM.

Be sure that you have created all sub folders (of the SAPDATAs) for the data files in the folder <TARGET_FOLDER>.

shell> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database open read only;
shell> rman target /
RMAN> CONVERT DATABASE
      TRANSPORT SCRIPT '/<TARGET_FOLDER>/transportscript.sql'
      TO PLATFORM 'AIX-Based Systems (64-bit)'
      PARALLELISM <n>
      DB_FILE_NAME_CONVERT '/oracle/<SID>/sapdata1/','/<TARGET_FOLDER>/',
                                                 ....
                                                '/oracle/<SID>/sapdata2/','/<TARGET_FOLDER>/';

Post Steps

After the "CONVERT DATABASE" finished successfully, you will get an information like this:

"Run SQL script /oracle/TST/oraconv/transportscript.sql on the target platform to create database
Edit init.ora file /oracle/<SID>/102_64/dbs/init_00l0fo1j_1_0.ora.
This PFILE will be used to create the database on the target platform To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility"

Now you just have to copy the converted data files from <TARGET_FOLDER> to your target host. In the meantime you can edit the paths in the generated init.ora and SQL script "transportscript.sql".

If your copy job has finished run the transportscript.sql and your database is up and running.

Summary

Personally i really like that feature and used it a few times to migrate SAP or non-SAP oracle databases to different plattforms.

You can build up your whole environment previously and you only have to exchange the database on the productive migration run.

It also saves me a lot of time, because of i don't have to test the R3load migration procedure for the different SAP releases and the runtime of the "CONVERT DATABASE" was much less than with R3load.