Transporting Tablespaces on the Same OS Platform

In the following example, the transportable
tablespace set consists of two tablespaces—test1 and test2. Use the transport_set_check procedure of the DBMS_TTS package to verify whether the two tablespaces are selfcontained, as shown here:

SQL> execute sys.dbms_tts.transport_set_check('test1, test2',TRUE);
PL/SQL procedure successfully completed.

RMAN> transport tablespace test1,test2
2> tablespace destination '/u05/app/oracle/transportdest'
3> auxiliary destination '/u05/app/oracle/auxdest';

You can also use the transport tablespace command to perform a tablespace transport
to a past point in time. Simply add the unitl scn clause to the transport tablespace command, as shown here:

RMAN> transport tablespace test1,test2
2> tablespace destination '/u05/app/oracle/transportdest'
3> auxiliary destination '/u05/app/oracle/auxdest'
4> until SCN 259386;

Transporting Tablespaces Across Different Operating
System Platforms

To find out the endian formats of the two platforms, use the following query:

SQL> select platform_name, endian_format from
2* v$transportable_platform;


1. Place the tablespaces you want to transport in read-only mode:

SQL> alter tablespace myspace read only;
Tablespace altered.

SQL>

2. Use the convert tablespace command to convert the source (HP-UX in this case)
datafiles in the transportable tablespace set to the target (Linux in this case) platform:

RMAN> convert tablespace myspace
2> to platform 'Linux IA (32-bit)'
3> format='/tmp/dba/%U';

3. Verify that you have the two datafiles that are part of the myspace tablespace in the
/tmp/dba directory:

$ ls –altr /tmp/dba
data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs
data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200


4. Use the Data Pump Export utility to create the export dump file with the metadata for the myspace tablespace:

$ expdp pasowner/orbiter1 directory=direct1 transport_tablespaces=myspace

5. Move both the converted datafiles from step 2 and the export dump file
(myspace.dmp) from step 3 to the target host, as shown here:
$ rcp data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs prod1:/tmp/dba
$ rcp data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200 prod1:/tmp/dba
$ rcp myspace.dmp prod1:/tmp/dba


6. Import the tablespace metadata for the transported tablespaces into the target database,
as shown here:
$ impdp system/sammyy1 directory=data_dump_dir2
dumpfile='myspace2.dmp'
transport_datafiles='/u05/app/oracle/data_D-PASU_I-877170026_TS-YSPACE_FNO-➥
64_02i6g1v,
'/u05/app/oracle/ data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200'

RMAN> connect target /
RMAN> convert datafile=
'/u01/transport_solaris/sales/sales01.dbf',
'/u01/transport_solaris/sales/sales02.dbf'
from platform 'Solaris[tm] OE (32-bit)'
db_file_name_convert
'/u01/transport_solaris/sales','/u05/newdb/sales';

Transporting an Entire Database to a Different Platform

Problem

You want to transport an entire Oracle database to another host that is using a different operating system platform.

Solution

Use the convert database command to move an Oracle database from one platform to
another. The only requirement is that the two platforms share an identical endian format. You can perform the convert database operation on the source platform or the destination platform.

The following are the steps to transport an Oracle database from a Windows XP platform
to the Linux platform:

1. Make sure the source database is eligible for transporting to the destination operating system platform by executing the dbms_tdb.check_db procedure, as shown here. The source database is running on a Windows XP platform, and the target database is running on a Linux 32-bit platform.


SQL> connect sys/sammyy1 as sysdba
Connected.
SQL> set serveroutput on
SQL> declare
2> db_ready boolean;
3> begin
4> db_ready := dbms_tdb.check_db('Linux IA(32-bit)',
dbms_tdb.skip_readonly);
5* end;
SQL> /

PL/SQL procedure successfully completed.
SQL>

If you see the message “PL/SQL procedure successfully completed,” as is the case in
this example, it means you can migrate from the specified operating system platform
to the destination platform.

2. Since external tables aren’t automatically transported to the target platform, you must invoke the dbms_tdb.check_db procedure again to get a list of the external tables, which you can then use to re-create those tables on the destination platform after the database transportation.

SQL> declare
2> external boolean;
3> begin
4> external :=dbms_tdb.check_external;
5* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>

3. Put the target database in the read-only mode after shutting it down first and restarting it in mount state:

SQL> connect sys/sammyy1 as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 180357996 bytes
Database Buffers 427819008 bytes
Redo Buffers 2940928 bytes
Database mounted.

SQL> alter database open read only;
Database altered.
SQL>

RMAN> convert database new database 'mydb'
2> transport script 'c:\temp\mydb_script'
3> to platform 'Linux IA (32-bit)'
4> db_file_name_convert 'c:\oracle\product\11.1.0\oradata\nick\'
'c:\temp\';

5. On the target platform, first set the ORACLE_SID environment variable pointing to the
new database:

$ export ORACLE_SID=newdb
The ORACLE_HOME environment variable must point to the same version of Oracle
software as on the source platform.
Execute the mydb_script file after logging in to SQL*Plus as the user sys:

$ sqlplus /nolog
SQL> connect sys/sammyy1 as sysdgba
Connected to an idle instance.
SQL> @mydb
ORACLE instance started.
Total System Global Area 243269632 bytes
Fixed Size 1218748 bytes
Variable Size 79693636 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Control file created.
Database altered.
Tablespace altered.

6. Check to make sure there are no invalidated objects left in the database:
SQL> select count(*) from dba_objects where
status='INVALID';

Transporting a Database by Converting Datafiles on the
Target Platform

1. Make sure the source database is eligible for transporting to the destination operating system platform by executing the dbms_tdb.check_db procedure, as shown here. The source database is running on a Windows XP platform, and the target database is running on a Linux 32-bit platform.

SQL> connect sys/sammyy1 as sysdba
Connected.

SQL> set serveroutput on

SQL> declare
2> db_ready boolean;
3> begin
4> db_ready := dbms_tdb.check_db('Linux IA(32-bit)',
5> dbms_tdb.skip_readonly);
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>


2. Since external tables aren’t automatically transported to the target platform, you must invoke the dbms_tdb.check_external procedure again to get a list of the external tables that you can then use to re-create those tables on the destination platform after the database transportation.

SQL> declare
2> external boolean;
3> begin
4> external :=dbms_tdb.check_external;
5> end;
6> /
PL/SQL procedure successfully completed.


3. Put the target database in read-only mode after shutting it down first and restarting it in the mount state:

SQL> connect sys/sammyy1 as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 180357996 bytes
Database Buffers 427819008 bytes
Redo Buffers 2940928 bytes
Database mounted.

SQL> alter database open read only;
Database altered.
SQL>

4. Execute the convert database command on the source database platform with the on
target platform clause, as shown here:

RMAN> convert database on target platform
2> convert script 'c:\temp\convert\convertscript.rman'
3> transport script 'c:\temp\convert\transportscript.sql'
4> new database 'youdb'
5> format 'c:\temp\convert\%U'
6> ;

5. Copy the datafiles of the source database while that database is still in the read-only mode and move them to the target database platform.

6. Run the convertscript.rman script on the target database after first transporting all the source database files to the target platform. Once you copy the necessary files, you can put the source database in a read-write mode again.

7. You use a separate convert datafile command for each datafile you need to convert
from the source to the target platform. The format parameter of the convert datafile
command specifies the location of each file on the target platform.

$ rman target / cmdfile=CONVERTSCRIPT.RMAN


8. Make the necessary changes in the pfile and the transportscript.sql script produced
by the convert database script. Execute the transportscript.sql script from SQL*Plus
to produce a new database instance, and create the new database (named youdb in
the example):

$ sqlplus /nolog
SQL> @transportscript