oracle_12c_migrate_datafile_different_os [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====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:

<Code:bash|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>
</Code>

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.

<Code:bash|Make the TBS Read only>
sqlplus / as sysdba
SQL> ALTER TABLESPACE bartbs READ ONLY;
Tablespace altered.
SQL> exit
[oracle@enode01 labs]$
</Code>
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

<Code:bash|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>
</Code>

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:

<Code:bash|Move the datafiles>
[oracle@enode01 ~]$ scp /home/oracle/backup/* oracle@trgnode01:/home/oracle/backup
Password:
...
</Code>

=====Restore(Import)=====
After that is done, we can saftly restore (import) the data as follows:

<Code:bash|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;
</Code>

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.