Transporting Data Across Platforms

浸心阁 2015-04-16

展开全文

25 Transporting Data Across Platforms

You can use RMAN to transport tablespaces across platforms with different endian formats. You can also use RMAN to transport an entire database to a different platform so long as the two platforms have the same endian format.

This chapter contains the following topics:

Overview of Cross-Platform Data Transportation

This section explains the basic concepts and tasks involved in transporting tablespaces and databases across platforms.

Purpose of Cross-Platform Data Transportation

You can transport tablespaces in a database that runs on one platform into a database that runs on a different platform. Typical uses of cross-platform transportable tablespaces include the following:

Publishing structured data as transportable tablespaces for distribution to customers, who can convert the tablespaces for integration into their existing databases regardless of platform
Moving data from a large data warehouse server to data marts on smaller computers such as Linux-based workstations or servers
Sharing read-only tablespaces across a heterogeneous cluster in which all hosts share the same endian format

A full discussion of transportable tablespaces, their uses, and the different techniques for creating and using them is found in Oracle Database Administrator's Guide.

You can also use RMAN to transport an entire database from one platform to another. For example, business requirements demand that you run your databases on less expensive servers that use a different platform. In this case, you can use RMAN to transport the entire database rather than re-create it from scratch and use import utilities or transportable tablespaces to repopulate the data.

You can convert a database on the destination host or source host. Reasons for converting on the destination host include:

Avoiding performance overhead on the source host due to the conversion process
Distributing a database from one source system to multiple recipients on several different platforms
Evaluating a migration path for a new platform

Basic Concepts of Cross-Platform Data Transportation

You must use the RMAN CONVERT command in a transportable tablespace operation when the source platform is different from the destination platform and the endian formats are different. When transporting between platforms for which the endian format is the same, you do not need to use CONVERT. You can use operating system utilities to copy the files from the source to the destination.

Tablespace and Datafile Conversion

You can perform tablespace conversion with the RMAN CONVERT TABLESPACE command on the source host, but not on the destination host. The CONVERT TABLESPACE command does not perform in-place conversion of datafiles. Rather, the command produces output files in the correct format for use on the destination platform. The command does not alter the contents of datafiles in the source database.

You can use CONVERT DATAFILE command to convert files on the destination host, but not on the source host. The Data Pump Export utility generates an export dump file that, in conjunction with datafiles manually copied to the destination host, can be imported into the destination database. Until the datafiles are transported into the destination database, the datafiles are not associated with a tablespace name in the database. In this case, RMAN cannot translate the tablespace name into a list of datafiles. Therefore, you must use CONVERT DATAFILE and identify the datafiles by filename.

Note:

Using CONVERT TABLESPACE or CONVERT DATAFILE is only one step in using cross-platform transportable tablespaces. Read the discussion of transportable tablespaces in Oracle Database Administrator's Guide in its entirety before attempting to follow the procedure in this chapter.

Database Conversion

To convert a whole database to a different platform, both platforms must use the same endian format. The RMAN CONVERT DATABASE command automates the movement of an entire database from a source platform to a destination platform. The transported database contains the same data as the source database and also has, with a few exceptions, the same settings as the source database.

Files automatically transported to the destination platform include:

Datafiles that belong to permanent tablespaces

Unlike transporting tablespaces across platforms, transporting entire databases requires that certain types of blocks, such as blocks in undo segments, be reformatted to ensure compatibility with the destination platform. Even though the endian formats for the source and destination platform are the same, the datafiles for a transportable database must undergo a conversion process. You cannot simply copy datafiles from one platform to another as you can when transporting tablespaces.
Initialization parameter file or server parameter file

If the databases uses a text-based initialization parameter file, then RMAN transports it. If the database uses a server parameter file, then RMAN generates an initialization parameter file based on the server parameter file and transports it and creates a new server parameter file at the destination based on the settings in the initialization parameter file.

In most cases, some parameters in the initialization parameter file require manual updating for the new database. For example, you may change the DB_NAME as well as parameters such as CONTROL_FILES that indicate the locations of files on the destination host.

You can convert the format of the datafiles either on the source platform or the destination platform. The CONVERT DATABASE command does not itself convert the format of datafiles. Rather, it generates scripts that you can run manually to perform the conversion. The CONVERT SCRIPT parameter creates a convert script that you can manually execute at the destination host to convert datafile copies in batch mode. The TRANSPORT SCRIPT parameter generates a transport script that contains SQL statements to create the new database on the destination platform.

Performing Cross-Platform Tablespace Conversion on the Source Host

Refer to the list of CONVERT prerequisites described in Oracle Database Backup and Recovery Reference. Meet all these prerequisites before doing the steps in this section.

For purposes of illustration, assume that you need to transport tablespaces finance and hr from source database prod_source, which runs on a Sun Solaris host. You plan to transport them to destination database prod_dest running on a Linux PC. You plan to store the converted datafiles in the temporary directory /tmp/transport_linux/ on the source host.

To perform cross-platform tablespace conversion on the source host:

Start SQL*Plus and connect to the source database prod_source with administrator privileges.

Query the name for the destination platform in V$TRANSPORTABLE_PLATFORM.

The database has a list of its own internal names for each platform supporting cross-platform data transport. You may need the exact name of the source or destination platform as a parameter to the CONVERT command. Query V$TRANSPORTABLE_PLATFORM to get the platform names. The following example queries Linux platform names:

SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
FROM   V$TRANSPORTABLE_PLATFORM 
WHERE  UPPER(PLATFORM_NAME) LIKE '%LINUX%';

The PLATFORM_NAME for Linux on a PC is Linux IA (32-bit).

Place the tablespaces to be transported in read-only mode. For example, enter:

ALTER TABLESPACE finance READ ONLY;
ALTER TABLESPACE hr READ ONLY;

Choose a method for naming the output files.

You must use the FORMAT or DB_FILE_NAME_CONVERT arguments to CONVERT to control the names of the output files. The rules are listed in order of precedence:

Files that match any patterns provided in CONVERT ... DB_FILE_NAME_CONVERT clause are named based upon this pattern.

If you specify a FORMAT clause, then any file not named based on patterns provided in CONVERT ... DB_FILE_NAME_CONVERT clause is named based on the FORMAT pattern.

Note:

You cannot use CONVERT ... DB_FILE_NAME_CONVERT to generate output filenames for CONVERT when the source files have Oracle-managed file names and the destination files have Oracle-managed file names.

Start RMAN and connect to the source database (not the destination database) as TARGET. For example, enter:

% rman
RMAN> CONNECT TARGET SYS@prod_source

Run the CONVERT TABLESPACE command to convert the datafiles into the endian format of the destination host.

In the following example, the FORMAT argument controls the name and location of the converted datafiles:

RMAN> CONVERT TABLESPACE finance,hr
2>   TO PLATFORM 'Linux IA (32-bit)'
3>   FORMAT '/tmp/transport_linux/%U';

The result is a set of converted datafiles in the /tmp/transport_linux/ directory, with data in the correct endian format for the Linux IA (32-bit) platform.

Performing Cross-Platform Datafile Conversion on the Destination Host

Refer to the list of CONVERT prerequisites described in Oracle Database Backup and Recovery Reference. Meet these prerequisites before doing the steps in this section.

About Cross-Platform Datafile Conversion on the Destination Host

Datafile conversion necessitates that you choose a technique for naming the output files. You must use the FORMAT or DB_FILE_NAME_CONVERT arguments to CONVERT to control the naming of output files. The rules are listed in order of precedence:

Files that match any patterns provided in CONVERT ... DB_FILE_NAME_CONVERT clause are named based upon this pattern.

If you specify a FORMAT clause, then any file not named based on patterns provided in CONVERT ... DB_FILE_NAME_CONVERT clause is named based on the FORMAT pattern.

Note:

You cannot use CONVERT ... DB_FILE_NAME_CONVERT to generate output filenames for CONVERT when both the source and destination files are Oracle Managed Files.

If the source and destination platforms differ, then you must specify the FROM PLATFORM parameter. View platform names by querying V$TRANSPORTABLE_PLATFORM. The FROM PLATFORM value must match the format of the datafiles to be converted to avoid an error. If you do not specify FROM PLATFORM, then this parameter defaults to the value of the destination platform.

Using CONVERT DATAFILE to Convert Datafile Formats

This section explains how to use CONVERT DATAFILE. The section assumes that you intend to transport tablespaces finance (datafiles fin/fin01.dbf and fin/fin02.dbf) and hr (datafiles hr/hr01.dbf and hr/hr02.dbf) from a source database named prod_source. The database runs on a Sun Solaris host. You plan to transport these tablespaces into a destination database named prod_dest, which runs on a Linux PC. You plan to perform conversion on the destination host.

When the datafiles are plugged into the destination database, you plan to store them in /orahome/dbs and preserve the current directory structure. That is, datafiles for the hr tablespace will be stored in the /orahome/dbs/hr subdirectory, and datafiles for the finance tablespace will be stored in the /orahome/dbs/fin directory.

To perform cross-platform datafile conversion on the destination host:

Start SQL*Plus and connect to the source database prod_source with administrator privileges.
Query the name for the source platform in V$TRANSPORTABLE_PLATFORM.

The database has a list of its own internal names for each platform supporting cross-platform data transport. You may need the exact name of the source or destination platform as a parameter to the CONVERT command. For example, you can obtain the platform name of the connected database as follows:
```
SELECT PLATFORM_NAME
FROM   V$TRANSPORTABLE_PLATFORM 
WHERE  PLATFORM_ID =
       ( SELECT PLATFORM_ID
         FROM   V$DATABASE );
```
For this scenario, assume the PLATFORM_NAME for the source host is Solaris[tm] OE (64-bit).
Identify the tablespaces to be transported from the source database and place them in read-only mode.

For example, enter the following SQL statements to place finance and hr in read-only mode:
```
ALTER TABLESPACE finance READ ONLY;
ALTER TABLESPACE hr READ ONLY;
```
On the source host, use Data Pump Export to create the export dump file

In this example, the dump file is named expdat.dmp.
Make the export dump file and the datafiles to be transported available to the destination host.

You can use NFS to make the dump file and current database files (not copies) accessible. Alternatively, you can use an operating system utility to copy these files to the destination host.

In this example, you store the files in the in the /tmp/transport_solaris/ directory of the destination host. You preserve the subdirectory structure from the original location of the files, that is, the datafiles are stored as:
- /tmp/transport_solaris/fin/fin01.dbf
/tmp/transport_solaris/fin/fin02.dbf
/tmp/transport_solaris/hr/hr01.dbf
/tmp/transport_solaris/hr/hr02.dbf
Start RMAN and connect to the destination database (not the source database) as TARGET. For example, enter the following command:
```
% rman
RMAN> CONNECT TARGET SYS@prod_dest
```
Execute the CONVERT DATAFILE command to convert the datafiles into the endian format of the destination host.

In this example, you use DB_FILE_NAME_CONVERT to control the name and location of the converted datafiles. You also specify the FROM PLATFORM clause.
```
RMAN> CONVERT DATAFILE
2>   '/tmp/transport_solaris/fin/fin01.dbf',
3>   '/tmp/transport_solaris/fin/fin02.dbf',
4>   '/tmp/transport_solaris/hr/hr01.dbf',
5>   '/tmp/transport_solaris/hr/hr02.dbf'
6>   DB_FILE_NAME_CONVERT
7>     '/tmp/transport_solaris/fin','/orahome/dbs/fin',
8>     '/tmp/transport_solaris/hr','/orahome/dbs/hr'
9>   FROM PLATFORM 'Solaris[tm] OE (64-bit)
```
The result is a set of converted datafiles in the /orahome/dbs/ directory that are named as follows:
- /orahome/dbs/fin/fin01.dbf
/orahome/dbs/fin/fin02.dbf
/orahome/dbs/hr/hr01.dbf
/orahome/dbs/hr/hr02.dbf

Follow the rest of the general outline for transporting tablespaces:

Plug the tablespace into the new database with the Import utility.

If applicable, place the transported tablespaces into read-only mode.

Checking the Database Before Cross-Platform Database Conversion

As explained in "Basic Concepts of Cross-Platform Data Transportation", you can use the RMAN CONVERT DATABASE command to automate the copying of an entire database from one platform to another.

Before converting the database, refer to the list of CONVERT DATABASE prerequisites described in Oracle Database Backup and Recovery Reference. For example, the source and destination platforms must use the same endian format. Make sure to meet all these prerequisites before attempting the procedure in this section.

The principal prerequisite on cross-platform transportable database is that the source and destination platform must share the same endian format. For example, you can transport a database from Microsoft Windows to Linux for x86 (both little-endian), or from HP-UX to AIX (both big-endian), but not from HP-UX to Linux for x86 (big-endian to little-endian).

Note:

If you cannot use CONVERT DATABASE because the platforms do not share endian formats, then you can create a new database on a destination platform manually and transport needed tablespaces from the source database with cross-platform transportable tablespaces.

To prepare for database conversion:

Start a SQL*Plus session as SYSDBA on the source database.

Open the database in read-only mode.

ALTER DATABASE OPEN READ ONLY;

Make sure that server output is on in SQL*Plus.

For example, enter the following SQL*Plus command:

SET SERVEROUTPUT ON

Execute the DBMS_TDB.CHECK_DB function.

This check ensures that no conditions would prevent the transport of the database, such as incorrect compatibility settings, in-doubt or active transactions, or incompatible endian formats between the source platform and destination platform.

You can call CHECK_DB without arguments to see if a condition at the source database prevents transport. You can also call this function with the arguments shown in Table 25-1.

Table 25-1 CHECK_DB Procedure Parameters

Parameter Description

target_platform_name

The name of the destination platform as it appears in V$DB_TRANSPORTABLE_PLATFORM.

This parameter is optional, but is required when the skip_option parameter is used. If omitted, it is assumed that the destination platform is compatible with the source platform, and only the conditions not related to platform compatibility are tested.

skip_option

Specifies which, if any, parts of the database to skip when checking whether the database can be transported. Supported values (of type NUMBER) are:

SKIP_NONE (or 0), which checks all tablespaces

SKIP_OFFLINE (or 2), which skips checking datafiles in offline tablespaces
SKIP_READONLY (or 3), which skips checking datafiles in read-only tablespaces

Example 25-1 illustrates executing CHECK_DB on a 32-bit Linux platform for transporting a database to 32-bit Windows, skipping read-only tablespaces.

Example 25-1 Executing DBMS_TDB.CHECK_DB

DECLARE
  db_ready BOOLEAN;
BEGIN
  db_ready := 
       DBMS_TDB.CHECK_DB('Microsoft Windows IA (32-bit)',DBMS_TDB.SKIP_READONLY);
END;
/

PL/SQL procedure successfully completed.

If no warnings appear, or if DBMS_TDB.CHECK_DB returns TRUE, then you can currently transport the database. Proceed to step 6.

If warnings appears, or if DBMS_TDB.CHECK_DB returns FALSE, then you cannot currently transport the database. Proceed to the next step.

Examine the output to learn why the database cannot be transported, fix the problem if possible, and then return to the preceding step. Refer to the DBMS_TDB documentation for the conditions present.

Execute DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories, or BFILEs. RMAN cannot automate the transport of these files, so you must copy the files manually and re-create database directories.

Example 25-2 shows how to call DBMS_TDB.CHECK_EXTERNAL.

Example 25-2 Executing DBMS_TDB.CHECK_EXTERNAL

DECLARE
     external BOOLEAN;
BEGIN
    /* value of external is ignored, but with SERVEROUTPUT set to ON
     * dbms_tdb.check_external displays report of external objects
     * on console */
    external := DBMS_TDB.CHECK_EXTERNAL;
END;
/

If no external objects exist, then the procedure completes with no output. If external objects exist, however, then the output is similar to the following:

The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.MEDIA_DIR, SYS.DATA_FILE_DIR, SYS.LOG_FILE_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA
 
PL/SQL procedure successfully completed.