How to transfer database and tablespace across platforms by RMAN 01/31 Update SLTechnology News&Howtos

How to transfer database and tablespace across platforms by RMAN

2025-01-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Shulou(Shulou.com)06/01 Report--

This article mainly explains "how RMAN transmits databases and tablespaces across platforms". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how RMAN transmits databases and tablespaces across platforms.

one。 Cross-platform transfer tablespace

Using cross-platform transfer tablespaces, there can be different byte storage order (endian format) between platforms. When the byte storage order of the source platform and the destination platform is different, the byte storage order of the data files under the table space must be converted to the same as the destination platform. The conversion operation uses either the RMAN CONVERT TABLESPACE command (converted on the source platform) or the CONVERT DATAFILE command (converted on the destination platform).

The CONVERT TABLESPACE command must be used on the source platform and the CONVERT DATAFILE command must be used on the destination platform.

1. Using CONVERT TABLESPACE... on the source platform TO PLATFORM command:

CONVERT TABLESPACE... The TO PLATFORM command is used to convert tablespaces to destination platform formats on the source platform. Call the CONVERT TABLESPACE command on the source platform database to specify one or more tablespace names, for example:

RMAN > CONVERT TABLEPSACE ts_1, ts_2...

TO PLATFORM 'platform_name'

The values supported by platform_name can be queried from V$TRANSPORTABLE_PLATFORM.

The entire tablespace must be converted on the source platform, not just one data file.

CONVERT TABLESPACE... TO PLATFORM contains the following optional parameters:

PARALLELISM n

Used to specify n server sessions to perform conversion work in parallel to improve performance. Each data file can only be assigned a separate server session for conversion, so performance cannot be improved by allocating a single data file with a large degree of parallelism.

FileNameConversionSpec

Specifies that the fileNameConversionSpec parameter is used to generate a new file name for the converted data file.

FORMAT formatSpec

Provides a format template for generating a new, unique file name for the converted data file. If FORMAT,RMAN is not specified to use the destination and format associated with the platform.

Here is an example of converting tablespaces on the source platform:

1)。 Sets the transport tablespace to read-only mode.

2)。 View the name of the destination platform in V$TRANSPORTABLE_PLATFORM.

SQL > SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM

WHERE UPPER (PLATFORM_NAME) LIKE 'LINUX'

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

3)。 Use the CONVERT TABLESPACE conversion command.

Use RMAN to convert the byte storage order of the data file to the destination platform, and the FORMAT parameter controls the name and location of the converted data file:

% rman TARGET /

RMAN > CONVERT TABLESPACE finance,hr

TO PLATFORM 'Linux IA (32-bit)'

FORMAT='/tmp/transport_linux/%U'

The converted data files are placed in the / tmp/transport_linux directory, and the bytes of the data and the destination system are stored in the same order.

4)。 Use the export utility to create an exported dump file.

5)。 Move the converted file and the exported dump file to the destination platform host.

6)。 Use the import tool to insert tablespaces into the new database.

7)。 Modify the tablespace to read-write mode.

two。 Use CONVERT DATAFILE... on the destination host FROM PLATFORM command:

CONVERT DATAFILE... FROM PLATFORM is used to convert the tablespace byte storage order of the source platform on the destination platform. CONVERT TABLESPACE cannot be used on the destination platform.

The CONVERT DATAFILE command can follow one or more data files that need to be converted, for example:

RMAN > CONVERT DATAFILE datafile_1, datafile_2...

FROM PLATFORM 'platform_name'

The FROM PLATFORM value provided must match the real platform of the data file to be converted, otherwise RMAN returns an error and the values supported by platform_name can be queried from V$TRANSPORTABLE_PLATFORM.

The PARALLELISM, FORMAT, fileNameConversionSpec parameters, and CONVERT TABLESPACE are used the same on the source platform.

The following is an example of converting data files on the destination platform:

1)。 Set the tablespace to be converted to read-only mode in the source database, and the corresponding data files in the tablespace include:

/ tmp/transport_solaris/fin/fin01.dbf

/ tmp/transport_solaris/fin/fin02.dbf

/ tmp/transport_solaris/hr/hr01.dbf

/ tmp/transport_solaris/hr/hr02.dbf

2)。 Use the export utility to create a dump file in the source library.

3)。 Copy the dump file and data file to the destination.

4)。 Use the RMAN CONVERT command to convert the data file to the target host format, and store the converted data file in the / orahome/dbs directory.

% rman TARGET /

RMAN > CONVERT DATAFILE

'/ tmp/transport_solaris/fin/fin01.dbf'

'/ tmp/transport_solaris/fin/fin02.dbf'

'/ tmp/transport_solaris/hr/hr01.dbf'

'/ tmp/transport_solaris/hr/hr02.dbf'

DB_FILE_NAME_CONVERT

'/ tmp/transport_solaris/fin','/orahome/dbs/fin'

'/ tmp/transport_solaris/hr','/orahome/dbs/hr'

Note:

A) the .format parameter controls the name and location of the data file.

b)。 When converting data files at the destination platform, the source platform must be specified using the FROM PLATFORM parameter. Otherwise, RMAN assumes that the source and destination platform bytes are stored in the same order.

5)。 Use the import tool to insert the converted tablespace into the new database.

6)。 Modify the tablespace to read-write mode.

For cross-platform transport tablespaces, if the source and destination platforms have the same byte storage order, you can simply copy them without performing CONVERT TABLESPACE or CONVERT DATAFILE conversion.

CONVERT TABLESPACE and CONVERT DATAFILE restrictions:

a)。 The COMPATIBLE initialization parameters for the source and destination databases must be set to 10.0 or higher.

b)。 Not all combinations of source and destination platforms are supported, and it is necessary to query the V$TRANSPORTABLE_PLATFORM to determine whether the source and destination platforms are supported. If the source and destination are both in the view list, then CONVERT can be used for conversion from one platform to another.

c)。 In the 10g version, tablespaces must be set to read-write mode at least once before passing tablespaces to another platform using the CONVERT command.

D) endian conversion of .rman cannot handle user-defined data types.

E) in versions prior to .10g, CLOBs was created as a variable-length character set and relied on byte storage order storage, the CONVERT command could not perform CLOBs conversion, and RMAN captured the byte storage order of each LOB field and propagated it to the destination database. The data is then read and written through the SQL layer, and the data is translated correctly based on the byte storage order of one of the two. If the table space is writable, then the data is written based on the relevant byte storage order.

f)。 The CLOBs created in Oracle Database 10g is stored in the AL16UTF16 character set and is platform independent.

two。 Transfer database across platforms

The RMAN CONVERT DATABASE command is used to automatically move the entire database from one platform (source platform) to another (destination platform). The source and destination platforms must be in the same byte storage order, and RMAN automatically completes most of the steps to create a new database on the destination platform, ensuring that it has the same data and settings as the source database.

CONVERT DATABASE steps can be performed on either the source platform or the destination platform.

Cross-platform transfer database requires that there must be the same byte storage order (endian format) between platforms. The data files of the transfer database must go through the conversion process, and can not be simply copied from one platform to another, but the table space can be transferred.

If the database uses PFILE, it can be passed. If you are using SPFILE, a PFILE is generated based on SPFILE, passed on, and then a new SPFILE is created on the target host based on the settings of PFILE. In most cases, some parameters in PFILE require manual updates to the new database, such as DB_NAME,CONTROL_FILES, and so on.

Restrictions on transferring databases across platforms:

a)。 The source and destination platform bytes must be stored in the same order.

b)。 Database files cannot be simply copied directly from the source to the target host, but must go through the conversion process.

c)。 The Redo log files and control files of the source platform do not support transfer. During the transfer, new control files and redo log files will be created for the new database. Once the OPEN RESETLOGS is executed, the new database will be created.

Note: the converted database control file will not contain the copy information of the RMAN database of the source database, and the backup of the source database cannot be used for the new converted database.

D) .BFILEs cannot be transferred, RMAN contains a list of objects of type BFILE in the output of the CONVERT DATABASE command, and the user must copy the BFILEs to the location specified by the destination platform.

e)。 Temporary files under a locally managed temporary tablespace cannot be transferred, and when transport script is executed, the temporary tablespace will be rebuilt on the destination platform.

f)。 External tables and directory cannot be transferred, RMAN lists the affected objects in the output of the CONVERT DATABASE command, and the user must redefine them in the destination database.

g)。 The password file cannot be transferred. If the source library has a password file, the output of the CONVERT DATABASE command includes a list of all users in the password file and their corresponding permissions, which users refer to to recreate a new password file in the destination database.

1. Use the DBMS_TDB package to prepare the CONVERT DATABASE operation.

The DBMS_TDB PL/SQL package defines two functions for preparing CONVERT DATABASE operations.

1)。 Use DBMS_TDB.CHECK_DB to check the database status:

DBMS_TDB.CHECK_DB checks whether the database can be transferred to the desired platform and whether the current database state allows transfer. It can be called without any parameters to see if the source library has any conditional output.

DBMS_TDB.CHECK_DB returns TRUE, indicating that the source database can be transmitted using CONVERT DATABASE, while FALSE indicates that it cannot be transferred.

Make sure the database is open in read-only mode, and then call DBMS_TDB.CHECK_DB with the appropriate parameters.

If SERVEROUTPUT returns FALSE from ON,DBMS_TDB.CHECK_DB, then the reason why the database cannot be transferred is output. The following are the reasons why the use of CONVERT DATABASE transport may be blocked:

a)。 Unknown destination platform name.

b)。 The destination platform has different byte storage order.

c)。 The database is not opened in read-only mode.

d)。 The database has active or suspected transactions.

e)。 A transaction rollback operation is required.

f)。 The database compatible version is less than 10.

g)。 Some tablespaces have not been opened in read-write mode in libraries with compatible version 10 or higher.

If the call to DBMS_TDB.CHECK_DB does not block the display of messages that are transmitted before the PL/SQL procedure successfully completed message, the database is ready to be transferred.

2)。 Use DBMS_TDB.CHECK_EXTERNAL to identify external objects:

DBMS_TDB.CHECK_EXTERNAL is used to represent any external table, directory or BFILEs. RMAN cannot transfer these files automatically.

DBMS_TDB.CHECK_EXTERNAL has no parameters when SERVEROUTPUT is set to ON,DBMS_TDB.CHECK_EXTERNAL to output the external tables contained in the database, directory and BFILEs.

two。 Use the RMAN CONVERT DATABASE command to convert the database.

The process of performing CONVERT DATABASE on the source and destination platforms is different.

1)。 Use CONVERT DATABASE to convert data files on the source platform.

Here are the conversion steps:

a)。 Ready to transfer the database, the source database must be open read-only.

SQL > STARTUP MOUNT

SQL > ALTER DATABASE OPEN READ ONLY

b)。 Use DBMS_TDB.CHECK_DB to check the database status.

Use the CHECK_DB function of the DBMS_TDB package to check that the database is ready.

Set serveroutput on

Declare

Db_ready boolean

Begin

/ * db_ready is ignored, but with SERVEROUTPUT set to ON any

* conditions preventing transport will be output to console * /

Db_ready: = dbms_tdb.check_db ('Microsoft Windows IA (32-bit)'

Dbms_tdb.skip_none)

End

c)。 Use DBMS_TDB.CHECK_EXTERNAL to identify external objects.

SQL > set serveroutput on

SQL > 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

When the database is ready for transfer, execute the RMAN CONVERT DATABASE command to specify the destination platform and the output file name. RMAN processes files that need to be moved to the destination database, including the following:

* A complete copy of the database data file, ready for transfer.

* PFILE for the new database of the destination platform, including the settings of the source database PFILE or SPFILE:

# Please change the values of the following parameters:

Control_files = "/ tmp/convertdb/cf_D-NEWDBT_id-1778429277_00gb9u2s"

Db_recovery_file_dest = "/ tmp/convertdb/orcva"

Db_recovery_file_dest_size= 10737418240

Instance_name = "NEWDBT"

Service_names = "NEWDBT.regress.rdbms.dev.us.oracle.com"

Plsql_native_library_dir = "/ tmp/convertdb/plsqlnld1"

Db_name = "NEWDBT"

* transport script, which contains SQL statements for creating a new database on the destination platform.

d)。 Perform the CONVERT DATABASE operation.

The following is an example of using CONVERT DATABASE on the source platform:

RMAN > CONVERT DATABASE NEW DATABASE 'newdb'

Transportscript' / tmp/convertdb/transportscript'

To platform 'Microsoft Windows IA (32-bit)'

Db_file_name_convert'/ disk1/oracle/dbs''/ tmp/convertdb'

Starting convert at 25-JAN-05

Using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.LOG_FILE_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

User OPER with SYSDBA privilege found in password file

Channel ORA_DISK_1: starting datafile conversion

Input datafile fno=00001 name=/disk1/oracle/dbs/tbs_01.f

Converted datafile=/tmp/convertdb/tbs_01.f

Channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15

Channel ORA_DISK_1: starting datafile conversion

Input datafile fno=00002 name=/disk1/oracle/dbs/tbs_ax1.f

Converted datafile=/tmp/convertdb/tbs_ax1.f

Channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03

Channel ORA_DISK_1: starting datafile conversion

Input datafile fno=00016 name=/disk1/oracle/dbs/tbs_52.f

Converted datafile=/tmp/convertdb/tbs_52.f

Channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Run SQL script / tmp/convertdb/transportscript on the target platform

To create database

Edit init.ora file init_00gb3vfv_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

Finished backup at 25-JAN-05

When the CONVERT DATABASE is complete, the source database can be opened again in read-write mode.

e)。 Copy all processed files to the destination host.

Place the data file to the desired location of the destination host, and edit the transport script to reference the new data file location if the data file path and source at the destination are different. At the same time, edit PFILE to modify any settings for the destination database.

f)。 Execute transport scirpt in the SQL*PLUS of the destination host to create a new database.

SQL > @ transportscript

When the transport sciprt is complete, the new database is created.

2)。 Use CONVERT DATABASE to convert data files on the destination host.

We may consider performing an CONVERT DATABASE conversion operation at the destination for the following reasons:

* prevent any performance overhead from performing the conversion process on the source host.

* distribute databases from the source system to multiple acceptable different platforms.

Here are the conversion steps:

a)。 Open source database in read-only mode.

b)。 Perform a DBMS_TDB.CHECK_DB to identify any reason for blocking transmission.

c)。 Execute DBMS_TDB.CHECK_EXTERNAL to identify external objects.

d)。 Run the RMAN CONVERT DATABASE command on the source platform to specify the ON TARGET PLATFORM parameters. Here is an example of executing CONVERT DATABASE ON TARGET PLATFORM on the source host:

RMAN > convert database on target platform

Convertscript'/ tmp/convertdb/convertscript-target'

Transportscript'/ tmp/convertdb/transportscript-target'

New database 'newdbt'

Format'/ tmp/convertdb/%U'

Starting convert at 28-JAN-05

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=39 devtype=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.LOG_FILE_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

User OPER with SYSDBA privilege found in password file