Overview
In oracle we can migrate a datafiles accross different operation systems, unlike other databases (cough DB2, cough cough) To do that, we have prictically 2 options. Convert the datafile at the SOURCE OR at the TARGET. It doesn't matter which one you will do. If you know the destination OS, convert it on the source is my recommendation.
Prepare
First we have to find out, the endian of the destination (target) operation system:
Check Endian format
sqlplus / as sysdba SQL> col platform_name format a30 SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%'; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ------------------------------ -------------- 10 Linux IA (32-bit) Little 11 Linux IA (64-bit) Little 9 IBM zSeries Based Linux Big 13 Linux x86 64-bit Little <- Target 18 IBM Power Based Linux Big <- Source SQL>
So, our current endian is Big, but we want to migrate a datafile to standard Linux x86-64 which has little endian. For that we have to use a combination of RMAN and EXPDP/IMPDP utilities. If it was the same endian we would've used ONLY RMAN.
Firstly, make the tablespace READ ONLY.
Make the TBS Read only
sqlplus / as sysdba SQL> ALTER TABLESPACE bartbs READ ONLY; Tablespace altered. SQL> exit [oracle@enode01 labs]$
That is needed in order to export the tablespace metadata
Convert(backup)
Then, using the RMAN utility, we can make a convert(backup) the datafiles and generated the necessary metadata for the restore
Convert the tablespace
[oracle@enode01 labs]$ rman target "'/ as sysbackup'" Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 30 13:12:59 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: GDSCAT (DBID=3218565307) RMAN> BACKUP TO PLATFORM 'Linux x86 64-bit' FORMAT '/home/oracle/backup/test.bck' DATAPUMP FORMAT '/home/oracle/backup/test.dmp' TABLESPACE bartbs; Starting backup at 30-SEP-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=31 device type=DISK Running TRANSPORT_SET_CHECK on specified tablespaces TRANSPORT_SET_CHECK completed successfully Performing export of metadata for specified tablespaces... EXPDP> Starting "SYSBACKUP"."TRANSPORT_EXP_GDSCAT_Foai": EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK EXPDP> Master table "SYSBACKUP"."TRANSPORT_EXP_GDSCAT_Foai" successfully loaded/unloaded EXPDP> *********************************************************************** ******* EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_GDSCAT_Foai is: EXPDP> /u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_GDSCAT_17352.dmp EXPDP> *********************************************************************** ******* EXPDP> Datafiles required for transportable tablespace BARTBS: EXPDP> /home/oracle/bartbs.dbf EXPDP> Job "SYSBACKUP"."TRANSPORT_EXP_GDSCAT_Foai" successfully completed at Wed Sep 30 13:15:06 2015 elapsed 0 00:00:33 Export completed channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/home/oracle/bartbs.dbf channel ORA_DISK_1: starting piece 1 at 30-SEP-15 channel ORA_DISK_1: finished piece 1 at 30-SEP-15 piece handle=/home/oracle/backup/test.bck tag=TAG20150930T131404 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 channel ORA_DISK_1: starting full datafile backup set input Data Pump dump file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_GDSCAT_1735 2.dmp channel ORA_DISK_1: starting piece 1 at 30-SEP-15 channel ORA_DISK_1: finished piece 1 at 30-SEP-15 piece handle=/home/oracle/backup/test.dmp tag=TAG20150930T131404 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-SEP-15 RMAN>
That procedure will generate us 2 files:
- Backup of the tablespace
- Metadata of the tablespace (expdp dump file)
Once we are done with that migrate both to the new server:
Move the datafiles
[oracle@enode01 ~]$ scp /home/oracle/backup/* oracle@trgnode01:/home/oracle/backup Password: ...
Restore(Import)
After that is done, we can saftly restore (import) the data as follows:
Restore the transported data
[oracle@trgnode01 ~]$ rman target "'/ as sysbackup'" RESTORE FOREIGN TABLESPACE bartbs FORMAT '+DATA/eastdb/bartbs.dbf' FROM BACKUPSET '/home/oracle/backup/test.bck' DUMP FILE FROM BACKUPSET '/home/oracle/backup/test.dmp'; channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring all files in foreign tablespace BARTBS channel ORA_DISK_1: reading from backup piece /home/oracle/backup/test.bck channel ORA_DISK_1: restoring foreign file 5 to +DATA/eastdb/bartbs.dbf channel ORA_DISK_1: foreign piece handle=/home/oracle/backup/test.bck channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring Data Pump dump file to /u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_EASTDB_54272.dmp channel ORA_DISK_1: reading from backup piece /home/oracle/backup/test.dmp channel ORA_DISK_1: foreign piece handle=/home/oracle/backup/test.dmp channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Performing import of metadata... IMPDP> Master table "SYSBACKUP"."TSPITR_IMP_EASTDB_wknf" successfully loaded/unloaded IMPDP> Starting "SYSBACKUP"."TSPITR_IMP_EASTDB_wknf": IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK IMPDP> Job "SYSBACKUP"."TSPITR_IMP_EASTDB_wknf" successfully completed at Wed Sep 30 13:23:51 2015 elapsed 0 00:00:31 Import completed Finished restore at 30-SEP-15 RMAN> select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE UNDOTBS2 ONLINE EXAMPLE ONLINE BARTBS READ ONLY RMAN> alter tablespace bartbs READ WRITE;
As you can see, the restored tablespace is in state of READ ONLY, which we changed to READ WRITE. P.S. If the target (destination) database has a physical standby, you will have to migrate the datafile there and do further actions.