Tablespace Migration Using Cross Platform Transportable Tablespaces

[milanjajoo]

Overview

Oracle introduced Transportable tablespaces (TTS) feature with Oracle 8i using which we can migrate tablespaces between databases that run on same platform. With Oracle 10g, enhanced version of TTS released to support cross platform transport of tablespaces but having same endian format.

With Oracle 10g Release 1, Oracle introduced Cross Platform Transportable Tablespaces (XTTS) feature which can be used to migrate tablespaces between databases that run on different platform having different endian format.

Tablespaces can be transported or exchanged across platforms. It is therefore easier and quicker to transfer data from one Oracle database to another, whether for special applications or to clone a database.
In addition to the existing methods export/import, R3load, DB-Link,..., a further method is added to the exchange of data between databases.
When you exchange data using transportable tablespaces, a record of independent tablespaces set to "read-only" are exchanged between two databases. The operation is divided into meta data export/import and the copy of the files assigned to the tablespaces. First, the meta data is exported and then the relevant data is copied on operating system level from one system to the other (possible tools: ftp, scp, rcp, tar,...). After the meta data is imported into the target database, you can open the copied data from the target database and the database can work with the "new" database.

Procedure

The following steps describe the complete process of transporting tablespace between databases running on platform having different endian formats – HP-UX (Big Endian) to Oracle Enterprise Linux (Little Endian).

In our case:

Source OS - HP-UX (Big Endian)

Target OS - Oracle Enterprise Linux (Little Endian)

Tablespace – PSAPVERTEX

 

1.Ensure that the tablespace to be transferred is completely independent and that there is no dependency with other tablespaces.

TTS requires all the tablespaces, which we are moving, must be self-contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked by running the PL/SQL procedure sys.dbms_tts-transport_set_check using below SQL query:

SQL> connect / as sysdba

SQL> execute sys.dbms_tts. transport_set_check('<tablespace_name>',true);

SQL> select * from sys.transport_set_violations;

No rows should be displayed.

If you try to run below command:

SQL> execute sys.dbms_tts. transport_set_check('PSAPBDLSI,PSAPEPR,PSAPEPR700,PSAPEPR731X,PSAPEPRUSR,PSAPTEMP,PSAPUNDO,PSAPVERTEX,SYSAUX,SYSTEM',true);

its showing error:

ORA-29351: can not transport system, sysaux, or temporary tablespace 'PSAPTEMP'

Note: The above example shows that we can not transport system, sysaux, or temporary tablespace 'PSAPTEMP'

2. Check the "byte order" of the supported platforms

Run the following query to list all the platforms that are supported, and to determine their platform names and IDs and their endian format.

SQL> select * from v$transportable_platform order by platform_id;

3. Ensure same CHARACTERSETS is defined for the two databases (source and target)

SQL> select parameter, value from v$nls_parameters where parameter='NLS_CHARACTERSET';

At source:

At Target:

4. Set tablespace to READ ONLY mode at source database.

SQL> alter tablespace <tablespace> READ ONLY;

5. Take export of metadata at source

command: exp tablespaces=<tablespace_name> transport_tablespace=y file=<filename>.dmp

Check metadata dump file created

6. Copy datafiles and metadata export dump file to target

Use below query to find all datafiles and their location for tablespace:

select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='<tablespace_name';

Copy files from source to target

7. Carry out conversion of data files

You can carry out the conversion on the source system or on the target system. The conversion is carried out with the program rman. Here we are performing the conversion from big endian to little endian on the source system.

Note: If OS format is same, then no need of conversion.

Commands to perform the conversion:

> rman nocatalog target /

RMAN> convert tablespace <tablespace_name>

2> to platform 'Linux x86 64-bit'

3> FORMAT '/export/VERTEX_NEW/convert/%U';

Check the converted data files created under the path provided

8. Make tablespace writeable again at the source database

SQL> alter tablespace <tablespace_name> READ WRITE;

9. Keep converted data files at the target

Create directory structure for data files at the target (Same as Source)

/oracle/Q03/sapdata1/vertex_1

/oracle/Q03/sapdata2/vertex_2

/oracle/Q03/sapdata3/vertex_3

Copy converted data files to sapdata directories created above and rename these files as per source data files: (you can use scp or cp command to copy files)

10. Rename or drop the tablespace if already exists on the target database

Use below SQL query for dropping the tablespace. The following query drops the tablespace and drops all referential integrity constraints that refer to primary and unique keys inside that tablespace:

SQL> DROP TABLESPACE PSAPVERTEX INCLUDING CONTENTS CASCADE CONSTRAINTS;

11. Create required database users (schemas) of the objects in target database

We need to create users which are associated with tablespace same as source database.

Query to find the associated database users in source:

select USERNAME, ACCOUNT_STATUS, LAST_LOGIN, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where DEFAULT_TABLESPACE='PSAPVERTEX';

Use below query to create users at target database:

SQL> create user <username> identified by <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE PSAPTEMP;

Grant dba privileges to the newly created users:

12. Create parameter file (parfile_imp.txt) for metadata import at the target

Content of the parameter file:

userid='/ as sysdba'

file='<export_metadata_filename>'

transport_tablespace=y

datafiles='/oracle/Q03/sapdata1/vertex_1/vertex.data1', '/oracle/Q03/sapdata2/vertex_2/vertex.data2', '/oracle/Q03/sapdata3/vertex_3/vertex.data3'

13. Import metadata into target database

Command: imp parfile=<parameter_filename>

14. Validate tablespace transported

SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='<tablespace_name';

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='<tablespace_name';

Validate count of DB objects associated with tablespace as per source database:

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where OWNER like '%VERTEX%';

Possible issues during import and resolution steps performed:

1. ORA-29342: user SYSMAN does not exist in the database

Error1

Resolution: Manually create SYSMAN user in target database by using below query:

create user SYSMAN identified by <password> DEFAULT TABLESPACE PSAPEPRUSR TEMPORARY TABLESPACE PSAPTEMP;

2. ORA-19722: datafile is an incorrect version

Resolution: Make sure the datafile is copied while its tablespace is read only.

Reference Document:

1. SAP note “1035051 - Transportable tablespaces”