Thursday, December 27, 2007

Database Migration from Sun(File system) to Linux(ASM and RAC)

a. Create a tablespace MIG_SUN_TO_LINUX and a object:

sqlplus / as sysdba
CREATE TABLESPACE MIG_SUN_TO_LINUX DATAFILE '/u01_ORADB/oradata/ORADB/mig_sun_to_linux_01.dbf' SIZE 100M;

CONNECT ORADBDEV1/xxxxx@ORADB
DROP TABLE EMP;
CREATE TABLE EMP (ENO NUMBER(10), ENAME VARCHAR2(50))
TABLESPACE MIG_SUN_TO_LINUX;

INSERT INTO EMP VALUES(10000001, 'VJ');
INSERT INTO EMP VALUES(10000002, 'ASHRAY');
COMMIT;


b. Use TTS procedure to move the tablesapce files:

sqlplus / as sysdba
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'MIG_SUN_TO_LINUX', incl_constraints => TRUE);
SELECT * FROM transport_set_violations;

ALTER TABLESPACE MIG_SUN_TO_LINUX READ ONLY;

exp USERID=\"sys/xxxxx@oradb AS SYSDBA\" TRANSPORT_TABLESPACE=y TABLESPACES=MIG_SUN_TO_LINUX FILE=MIG_SUN_TO_LINUX.dmp

scp MIG_SUN_TO_LINUX.dmp atlxd215:/export/home/oracle


c. Use RMAN to convert the datafiles from SUN to LINUX format:

rman target / nocatalog

CONVERT TABLESPACE MIG_SUN_TO_LINUX
TO PLATFORM = "Linux IA (64-bit)"
DB_FILE_NAME_CONVERT = "/u01_ORADB/oradata/ORADB/mig_sun_to_linux_01.dbf" , "/backup_ORADB/oracle/ORADB/mig/mig_sun_to_linux_01.dbf"
PARALLELISM=5;

Side note: If your linux is 32bit? Change TO PLATFORM = "Linux IA (64-bit)" above command to TO PLATFORM = "Linux IA (32-bit)"


d. ftp or scp the MIG_SUN_TO_LINUX tablespace file to destination RAC server(eg:. copy the datafiles under /u00/mig_area/oradb directory):

scp /backup_ORADB/oracle/ORADB/mig/mig_sun_to_linux_01.dbf atlxd215:/u00/mig_area/oradb


e. copy file from disk to ASM using DBMS_FILE_TRANSFER:

-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY sour_db_files_dir AS '/u00/mig_area/oradb';
CREATE OR REPLACE DIRECTORY dest_db_files_dir AS '+ORADB_DATA01_DG/oradb/datafile';

-- Copy the file to ASM disk group.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'sour_db_files_dir',
source_file_name => 'mig_sun_to_linux_01.dbf',
destination_directory_object => 'dest_db_files_dir',
destination_file_name => 'mig_sun_to_linux_01.dbf');
END;
/


f. Attach the file to target DB (Linux) - imp:

imp USERID=\"sys/xxxxx@oradb AS SYSDBA\" FROMUSER=ORADBDEV1 TOUSER=ORADBTEST1 TRANSPORT_TABLESPACE=y DATAFILES='+ORADB_DATA01_DG/oradb/datafile/mig_sun_to_linux_01.dbf' TABLESPACES=MIG_SUN_TO_LINUX FILE=/export/home/oracle/MIG_SUN_TO_LINUX.dmp


g. Make the tablespace to read write:

ALTER TABLESPACE MIG_SUN_TO_LINUX READ WRITE;

No comments: