Cross-platform migration - large Oracle 9i db from Solaris 64bit to Linux 32bit

Question for this post is - what is the best way to migrate >500GB Oracle 9.2.0.6 database from Solaris 64 to Linux 64 operating system?

As imp/exp or impdp/expdp would probably be very slow option better choice is cross-platform migration.

In this case we have to upgrade db from 9i to 10g and then migrate tablespaces using cross-platform tablespace conversion with transport tablespace feature.



Why upgrading to 10g?
Because we don't have RMAN cross-platform tablespace conversion in Oracle 9i.


This time I will do simple demo case how to migrate Oracle 9.2.0.6 database from Solaris 64bit to Linux 32bit. Similar actions are performed when Linux 64bit is target platform instead of Linux 32bit.


UPGRADE PROCESS:

1. First create new 10g installation using OUI on Solaris machine.

2. Run utlu102i.sql (in 10g ORACLE_HOME) script in Oracle 9i environment while Oracle 9i db is running.

SQL> spool /tmp/preupgrade.log
SQL> @utlu102i.sql
SQL> spool off

Check in log file what actions you have to do before upgrading.


3. Create pfile from current spfile of Oracle 9i database and copy that parameter file to 10g ORACLE_HOME/dbs.

SQL> create pfile from spfile;

File created.


cp initdbtest.ora /oradata1/oratest/product/1020/dbs/

Edit parameter file according to recommendations from pre-upgrade tool.

I've set this parameter values for this little demo:

pga_aggregate_target = 25M
shared_pool_size = 200M
streams_pool_size = 64M
session_max_open_files = 20

4. Few more steps on 9i database.

- Check for invalid objects and recompile:

SQL> SELECT UNIQUE object_name, object_type, owner
FROM dba_objects WHERE status='INVALID';

SQL>@?/rdbms/admin/utlrp

- Set SYSTEM as default tablespace for SYS and SYSTEM users.

SQL> select username, default_tablespace from dba_users
where username in ('SYS','SYSTEM');

To modify use:

SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM;

5. Shutdown database and stop listener.


6. Very IMPORTANT setp - create backup!


7. Edit "/var/opt/oracle/oratab" entry and change entry of old 9i ORACLE_HOME to 10g ORACLE_HOME.


8. Login using 10g environment and execute following statements:

-bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 21:57:46 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='$ORACLE_HOME/dbs/initdbtest.ora';
ORACLE instance started.

Total System Global Area  473956352 bytes
Fixed Size                  2041464 bytes
Variable Size             432019848 bytes
Database Buffers           33554432 bytes
Redo Buffers                6340608 bytes
Database mounted.
Database opened.

SQL>
CREATE TABLESPACE SYSAUX
DATAFILE '/oradata1/oradata/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

9. Start with upgrade process and check log file for errors:

SQL> set echo on
SQL> SPOOL /tmp/upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

10. Set compatible value to 10.2.0.4.0, create spfile from pfile, restart and recompile.


11. Run post-upgrade status tool - utlu102s.sql:

@?/rdbms/admin/utlu102s.sql

Check status and version of components.


12. Enable listener in new home.


13. I will skip gathering stats.


MIGRATION:

1. List internal names for each platform supporting cross-platform data transport:

SQL> set lines 200
SQL> set pages 999
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 x86 64-bit                                    Little
16 Apple Mac OS                                        Big
12 Microsoft Windows x86 64-bit                        Little
17 Solaris Operating System (x86)                      Little
18 IBM Power Based Linux                               Big
20 Solaris Operating System (x86-64)                   Little
19 HP IA Open VMS                                      Little

19 rows selected.

In this case:
source platform = Solaris[tm] OE (64-bit) - ENDIAN_FORMAT = big
target platform = Linux IA (32-bit) - ENDIAN_FORMAT = little


Endian format of the source platform is different from that of the destination platform so we must perform conversion. The endian format of the datafiles in the transportable tablespace set must be converted to match destination platform.


2. Check if tablespace is self contained.

SQL> begin
2     dbms_tts.transport_set_check('TBS_NAME',TRUE);
3  end;
4  /

PL/SQL procedure successfully completed.

SQL> select * from TRANSPORT_SET_VIOLATIONS;

no rows selected

If select query shows any rows we should transport mentioned tablespace also.


3. Put in read only mode tablespaces chosen for migration.

SQL> select 'alter tablespace '||tablespace_name||' read only;'
2  from dba_tablespaces
3  where tablespace_name not in ('SYSTEM','UNDOTBS1','TEMP','SYSAUX','DRSYS',
4                                'XDB','ODM','CWMLITE','USERS');

'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'
----------------------------------------------------------
alter tablespace EXAMPLE read only;
alter tablespace INDX read only;
alter tablespace TOOLS read only;
alter tablespace DATA1 read only;

4 rows selected.

For this test case I've chosen specified tablespaces.


5. Use RMAN to convert datafiles into the endian format of the destination host:

RMAN> convert tablespace 'DATA1'
to platform="Linux IA (32-bit)"
db_file_name_convert='/oradata1/oradata/data01.dbf','/oradata1/rman_convert/data01.dbf';

Starting backup at 27-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00012 name=/oradata1/oradata/data01.dbf
converted datafile=/oradata1/rman_convert/data01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-NOV-09

Execute this for all tablespaces you would like to migrate.


6. Create transportable tablespace metadata:

-bash-3.00$ exp userid=\'/ as sysdba\' transport_tablespace=y
tablespaces=DATA1 file=data1.dmp statistics=none

Export: Release 10.2.0.4.0 - Production on Fri Nov 27 14:41:28 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace DATA1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                     TEST_TABLE
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Export metadata of all tablespaces you would like to migrate.


7. Create 10.2.0.4 database on target Linux machine.

Notice!
Check database character set on source and target database.
Create users on target database if you are using imp/exp tools.


8. Copy converted datafiles and export output to target machine.

$ scp rman_convert/* oracle@10.10.10.18:/oracle/oradata/rman_convert

9. Import transportable tablespace in target databse:

$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y
datafiles='/oracle/oradata/rman_convert/data01.dbf' 
file=/oracle/oradata/rman_convert/data1.dmp

Import: Release 10.2.0.4.0 - Production on Fri Nov 27 15:02:28 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing MSUTIC's objects into MSUTIC
. . importing table                   "TEST_TABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

Use this script to import all transportable tablespaces.


10. Excerpt from alert log:

...
Fri Nov 27 15:02:28 2009
Plug in tablespace DATA1 with datafile
'/oracle/oradata/rman_convert/data01.dbf'
Plug in tablespace TOOLS with datafile
'/oracle/oradata/rman_convert/tools01.dbf'
...

11. Put imported tablespaces in read/write mode:

SQL> alter tablespace DATA1 read write;

Tablespace altered.

SQL> alter tablespace TOOLS read write;

Tablespace altered.

...

12. Small check on target database:

SQL> conn msutic/pwd
Connected.
SQL> select count(*) from test_table;

COUNT(*)
----------
50327

References:
http://youngcow.net/doc/oracle10g/backup.102/b14191/dbxptrn001.htm