Shulou(Shulou.com)05/31 Report--

Editor to share with you how MOS can use RMAN to transfer tablespaces to migrate databases to different byte order platforms, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

target

Starting with Oracle Database 10g, you can transfer tablespaces across platforms. This document provides a step-by-step guide to explain how to implement the transport table space for ASM data files and OS file system data files.

If your goal is to migrate a database to a different byte order platform, the following steps outline how to migrate a database to a new platform using transferable tablespaces:

1-create a new, empty database on the target platform.

2-Import the objects required by the transfer operation from the source database to the target library.

3-Export transferable metadata from the source database for all user tablespaces.

4-transfer the data files of the user tablespace to the target system.

5-use RMAN to convert the data file to the byte order format of the target system.

6-Import transferable metadata from all user tablespaces to the target database.

7-Import the remaining database objects and metadata (the part of the transfer operation that has not been moved) from the source database to the target library.

You can also convert data files on the source platform and transfer them to the target platform after the conversion is complete.

For the MAA white paper "platform Migration using Tablespace Transport", please refer to:

Http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-platformmigrationtts-129269.pdf

If you want to switch to Linux x86-64 after 11.2.0.4 and 12c, refer to the following documentation:

Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup [1389592.1]

Platform supported by the solution

Check V$TRANSPORTABLE_PLATFORM to see which platforms are supported and determine the byte order for each platform.

SQL > COLUMN PLATFORM_NAME FORMAT A32

SQL > SELECT * FROM V$TRANSPORTABLE_PLATFORM

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

1 Solaris [tm] OE (32-bit) Big

2 Solaris [tm] OE (64-bit) Big

7 Microsoft Windows IA (32-bit) Little

10 Linux IA (32-bit) Little

6 AIX-Based Systems (64-bit) Big

3 HP-UX (64-bit) Big

5 HP Tru64 UNIX Little

4 HP-UX IA (64-bit) Big

11 Linux IA (64-bit) Little

15 HP Open VMS Little

8 Microsoft Windows IA (64-bit) Little

9 IBM zSeries Based Linux Big

13 Linux 64-bit for AMD Little

16 Apple Mac OS Big

12 Microsoft Windows 64-bit for AMD Little

17 Solaris Operating System (x86) Little

If the source and target platforms are in different byte order, an additional step must be taken on the source or target platform to convert the transferred tablespace to the target format. If they are in the same byte order, no conversion is required, and tablespaces can be transferred as if they were on the same platform.

Transport tablespace

Preparation before transmitting tablespace

Check that the tablespace is self-contained:

SQL > execute sys.dbms_tts.transport_set_check ('TBS1,TBS2', true)

SQL > select * from sys.transport_set_violations

Note: these violations of transport standards must be resolved before the tablespace is transferred.

To successfully run the transport tablespace export, the tablespace must be in READ ONLY schema:

SQL > ALTER TABLESPACE TBS1 READ ONLY

SQL > ALTER TABLESPACE TBS2 READ ONLY

Export metadata

Use traditional export tools:

Exp userid=\ 'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=TBS1,TBS2

Export using the data pump:

First create the catalog object used by the data pump, for example:

CREATE OR REPLACE DIRECTORY dpump_dir AS'/ tmp/subdir'

GRANT READ,WRITE ON DIRECTORY dpump_dir TO system

Then initialize the data pump export:

Expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1,TBS2

If you want to perform a strict inclusion check while performing a transport tablespace operation, use the TRANSPORT_FULL_CHECK parameter.

Expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

If the table space set being transferred is not self-contained, the export will fail.

Using V$TRANSPORTABLE_PLATFORM to determine the byte order of each platform, you can execute the following query on each platform instance:

SELECT tp.platform_id,substr (d.PLATFORMNAME Magi 1mai 30), ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME

If you find that the byte order is different, you must convert when transferring the tablespace set:

RMAN > convert tablespace TBS1 to platform= "Linux IA (32-bit)" FORMAT'/ tmp/%U'

RMAN > convert tablespace TBS2 to platform= "Linux IA (32-bit)" FORMAT'/ tmp/%U'

Then copy the data file and the exported file to the target environment.

Import transferable tablespace

Use traditional import tools:

Imp userid=\ 'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_imp.log transport_tablespace=y datafiles='/tmp/....','/tmp/...'

Use the data pump:

CREATE OR REPLACE DIRECTORY dpump_dir AS'/ tmp/subdir'

GRANT READ,WRITE ON DIRECTORY dpump_dir TO system

Then execute:

Impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='/tmp/....','/tmp/...' REMAP_SCHEMA= (source:target) REMAP_SCHEMA= (source_sch3:target_schema_sch3)

If you want to change the owner of the transferred database object, you can use REMAP_SCHEMA.

Place the tablespace in read/write mode:

SQL > ALTER TABLESPACE TBS1 READ WRITE

SQL > ALTER TABLESPACE TBS2 READ WRITE

Use DBMS_FILE_TRANSFER

You can also use DBMS_FILE_TRANSFER to copy data files to another host.

Starting with 12c and 11.2.0.4, DBMS_FILE_TRANSFER converts by default. With DBMS_FILE_TRANSFER, when the target database receives a file from a platform with a different byte order, it converts each block. As part of the transferable operation, after the data file is moved to the target database, it can be imported without RMAN conversion.

On versions earlier than 11.2.0.4, the above steps are also required for ASM files. But if the byte order format is different, then you must use RMAN conversion after transferring the file. Files cannot be copied directly between two ASM instances on different platforms.

The following is an example of usage:

RMAN > CONVERT DATAFILE

'/ hq/finance/work/tru/tbs_31.f'

'/ hq/finance/work/tru/tbs_32.f'

'/ hq/finance/work/tru/tbs_41.f'

TO PLATFORM= "Solaris [tm] OE (32-bit)"

FROM PLATFORM= "HP TRu64 UNIX"

DB_FILE_NAME_CONVERT= "/ hq/finance/work/tru/", "/ hq/finance/dbs/tru"

PARALLELISM=5

The same example, but here shows that the destination is an ASM disk group:

RMAN > CONVERT DATAFILE

'/ hq/finance/work/tru/tbs_31.f'

'/ hq/finance/work/tru/tbs_32.f'

'/ hq/finance/work/tru/tbs_41.f'

TO PLATFORM= "Solaris [tm] OE (32-bit)"

FROM PLATFORM= "HP TRu64 UNIX"

DB_FILE_NAME_CONVERT= "/ hq/finance/work/tru/", "+ diskgroup"

PARALLELISM=5

* * warning * *

When migrating from Solaris,Linux or AIX to HP/UX using transferable tablespaces (TTS), the index organization table (IOT) may be corrupted.

This is a limitation imposed by BUG:9816640.

There is currently no patch for this problem. The index organization table (IOT) needs to be rebuilt after TTS.

Reference document 1334152.1 Corrupt IOT when using Transportable Tablespace to HP from different OS.

When using a column dropped by drop, you may encounter this Bug:13001379-Datapump transport_tablespaces produces wrong dictionary metadata for some tables can occur. Document 1440203.1 gives the details of this warning.

Known issues with using DBMS_FILE_TRANSFER

= > undisclosed Bug 13636964-ORA-19563 from RMAN convert on datafile copy transferred with DBMS_FILE_TRANSFER (Doc ID 13636964.8)

Confirm the affected version

11.2.0.3

The problem is fixed in the following version

12.1.0.1 (Base Release)

11.2.0.4 (Future Patch Set)

Description

Files transferred using DBMS_FILE_TRANSFER failed in the RMAN convert operation.

For example:

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of conversion at target command at 01/24/2012 16:22:23

ORA-19563: cross-platform datafile header validation failed for file + RECO/soets_9.tf

Rediscovery Notes:

If RMAN fails to convert a file that is transferred using DBMS_FILE_TRANSFER, it may be because of the Bug.

Circumvention plan:

Use the OS tool to transfer files.

= > Dbms_file_transfer Corrupts Dbf File When Copying between endians (Doc ID 1262965.1)

Additional resources

Community: Database Utilities

Are there any other questions? Use the community above to search for similar discussions, or start a new discussion on the topic.

Restrictions on the use of transportable tablespaces

The source and target libraries must use the same character set and national character set.

If there is already a tablespace with the same name on the target library, it cannot be transferred. However, you can rename the tablespace to be transferred or the tablespace on the target library before transferring.

If an object has underlying objects (such as materialized views) or included objects (such as partition tables), it cannot be transferred. Unless all underlying objects or included objects are in this tablespace set.

Look at the table "Objects Exported and Imported in Each Mode" in the Oracle Database Utilities document. There are several object types that are not exported in the tablespace schema.

If the owner of the tablespace object does not exist in the target library, you need to manually create the user name before starting the transferable tablespace import.

Note that in 10gR1 and 10gR2, TTS operations across different byte order platforms are not supported for spatial indexes. This restriction was lifted in 11g.

Special spatial packages must be run before and after export, please refer to the Oracle Spatial documentation.

If you use the spatial index, then:

Starting with Oracle Database 11gR1, for tablespaces that contain XMLType, you must use a data pump to export and import tablespace metadata.

The following query returns a list of tablespaces containing XMLType:

Select distinct p.tablespace_name

From dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t

Where t.table_name=x.table_name and

T.tablespace_name=p.tablespace_name and

X.owner=u.username

There are the following restrictions on transferring tablespaces with XMLType

The target database must have XML DB installed.

The schema referenced by the XMLType table cannot be an XML DB standard schema.

The schema referenced by the XMLType table cannot have circular dependencies.

Any row-level security on the XMLType table is lost on import.

If the schema of a transferred XMLType table is not in the target database, it will be imported and registered. If the schema already exists in the target database, an error will be returned unless the ignore=y option is used.

The advanced queue transportable tablespace does not support 8.0 compatible advanced queues with multiple containers.

You cannot transfer objects owned by SYSTEM tablespaces or user SYS.

Opaque types (such as RAW,BFILE and AnyTypes) can be transferred, but they are not converted in cross-platform transport operations. Their actual framework is known only to applications, so applications must be moved to the new platform to deal with byte ordering issues.

Floating point BINARY_FLOAT and BINARY_DOUBLE types can be transferred, but you must use Data Pump, not the original export utility EXP.

For additional restrictions and requirements, please see the following documentation: Document 1454872.1-Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable

Transferable tablespace export / import of ASM files

Use RMAN CONVERT

There is no direct way to export / import ASM files as transferable tablespaces. However, you can do this through RMAN.

Be sure to follow these steps:

Use the original import tool:

Imp userid=\ 'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_imp.log transport_tablespace=y datafiles='/tmp/....','/tmp/...'

Import using the data pump:

CREATE OR REPLACE DIRECTORY dpump_dir AS'/ tmp/subdir'

GRANT READ,WRITE ON DIRECTORY dpump_dir TO system

Then execute:

Impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='/tmp/....','/tmp/...' REMAP_SCHEMA= (source:target) REMAP_SCHEMA= (source_sch3:target_schema_sch3)

If you want to change the owner of the transferred database object, you can use the REMAP_SCHEMA parameter.

Use the original export utility:

Exp userid=\ 'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=TBS1,TBS2

Export using the data pump:

CREATE OR REPLACE DIRECTORY dpump_dir AS'/ tmp/subdir'

GRANT READ,WRITE ON DIRECTORY dpump_dir TO system

Then execute:

Expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1,TBS2

If you want to perform strict containment checks while performing transportable tablespace operations, use the TRANSPORT_FULL_CHECK parameter:

Expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

Check that the tablespace is self-contained:

SQL > execute sys.dbms_tts.transport_set_check ('TBS1,TBS2', true)

SQL > select * from sys.transport_set_violations

Note: the results of these violations must be resolved before the tablespace is transferred.

For transportable tablespaces to be exported successfully, these tablespaces must be in READ ONLY mode.

SQL > ALTER TABLESPACE TBS1 READ ONLY

SQL > ALTER TABLESPACE TBS2 READ ONLY

Preparation before exporting tablespaces.

Export metadata.

If the transferred tablespace is not self-contained, the export will go wrong.

Use V$TRANSPORTABLE_PLATFORM to find the exact platform name of the target library. You can execute the following query on the target platform instance.

SELECT tp.platform_id,substr (d.PLATFORMNAME Magazine 2 and 30), ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME

Generate an OS file from the ASM file in the format of the target platform.

RMAN > CONVERT TABLESPACE TBS1

TO PLATFORM 'HP-UX (64-bit)' FORMAT'/ tmp/%U'

RMAN > CONVERT TABLESPACE TBS2

TO PLATFORM 'HP-UX (64-bit)' FORMAT'/ tmp/%U'

Copy the generated file to the target server (if it is not on the same machine as the source server).

Import transferable tablespaces.

Place the tablespace in read/write mode.

SQL > ALTER TABLESPACE TBS1 READ WRITE

SQL > ALTER TABLESPACE TBS2 READ WRITE

If you want to transfer data files from the ASM environment to the file system, the operation is over. But if you want to transfer tablespaces between two ASM environments, you need to continue with the following.

Use rman to copy the file'/ tmp/....dbf' to the ASM environment.

Rman nocatalog target /

RMAN > backup as copy datafile'/ tmp/....dbf' format'+ DGROUPA'

Where + DGROUPA is the name of the ASM disk group.

Swap the data file to this copy.

If it is a 10g database, first take the data file offline:

SQL > alter database datafile'/ tmp/....dbf' offline

The file is exchanged to this copy:

Rman nocatalog target /

RMAN > switch datafile'/ tmp/....dbf' to copy

Make a note of the name of the copy created in the + DGROUPA disk group, for example,'+ DGROUPA/s101/datafile/tts.270.5'.

To bring the file back online, we first need to recover it.

SQL > recover datafile'+ DGROUPA/s101/datafile/tts.270.5'

SQL > alter database datafile'+ DGROUPA/s101/datafile/tts.270.5' online

Check that the data file is indeed part of the ASM environment and is online.

SQL > select name, status from v$datafile

The output should be:

+ DGROUPA/s101/datafile/tts.270.5 ONLINE

Use DBMS_FILE_TRANSFER

You can also use DBMS_FILE_TRANSFER to copy data files from one ASM disk group to another, or even to another host. From 10gR2 you can also use DBMS_FILE_TRANSFER to copy data files from ASM to the file system, and from the file system to ASM.

The PUT_FILE process reads a local file or ASM and contacts the remote database to create a copy of the remote file system. The copied file is the source file, and the new file brought by the copy is the target file. The target file will not be closed until the process completes successfully.

Syntax:

DBMS_FILE_TRANSFER.PUT_FILE (

Source_directory_object IN VARCHAR2

Source_file_name IN VARCHAR2

Destination_directory_object IN VARCHAR2

Destination_file_name IN VARCHAR2

Destination_database IN VARCHAR2)

Where:

If we want to use DBMS_FILE_TRANSFER.PUT_FILE to transfer files from the source side to the destination host, step 3, 4, 5, make the following changes:

Dbs1: connection string to the source database

Dbs2: dblink to the target database

A1.dat: the file name of the source database

A4.dat: the file name of the target database

Source_directory_object: the directory object where the files copied on the local source side are located. On the source side, this directory object must exist.

Source_file_name: the name of the file copied from the local file system. This file must exist in the directory specified by source_directory_object on the local file system.

Destination_directory_object: this is the directory object to be placed on the destination side of the file. This directory object must exist on the remote file system.

Destination_file_name: the name of the file placed on the remote file system. There must be no files with duplicate names in the target directory of the remote file system.

Destination_database: the name of the database link to the remote database that is the destination for copying the file.

Create a directory on the target database host and authorize it to local users. This is the directory object where the file is to be placed on the target side and must exist on the remote file system.

CREATE OR REPLACE DIRECTORY target_dir AS'+ DGROUPA'

GRANT WRITE ON DIRECTORY target_dir TO "USER"

Create a directory on the source database host. This is the directory object that exists on the local source side of the file to be copied. This directory object must exist on the source side.

CREATE OR REPLACE DIRECTORY source_dir AS'+ DGROUPS/subdir'

GRANT READ,WRITE ON DIRECTORY source_dir TO "USER"

CREATE OR REPLACE DIRECTORY source_dir_1 AS'+ DGROUPS/subdir/subdir_2'

Create a dblink connection to the target database host:

CREATE DATABASE LINK DBS2 CONNECT TO 'user' IDENTIFIED BY' password' USING 'target_connect'

Here target_connect is the connection string of the target database, and USER is the user we will use to transfer the data file.

Connect to the source instance. The following items will be used:

CONNECT user/password@dbs1

-put a1.dat to a4.dat (using dbs2 dblink)

-level 2 sub dir to parent dir

-user has read privs on source_dir_1 at dbs1 and write on target_dir

-in dbs2

BEGIN

DBMS_FILE_TRANSFER.PUT_FILE ('source_dir_1', 'a1.dat'

'target_dir', 'a4.datum, 'dbs2')

END

The above is all the contents of the article "how MOS can use RMAN to transfer tablespaces to migrate databases to platforms with different byte order". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.