News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

12c RMAN New Feature – Cross Platform Data Transport Using Incremental Backups

  • Posted by Gavin Soorma
  • On August 12, 2014
  • 1 Comments
  • 11g upgrade, 12c, cross platform, little endian, recover from platform, restore from platform, rman

In Oracle 12c we can now transport data across platforms using full as well as incremental backup sets.

The use of RMAN Incremental backups can significantly reduce overall application downtime during cross-platform data migration and will be useful in any 11g to 12c future upgrades.

In this example we look at migrating data from an Oracle 11g R2 database on Windows platform to an Oracle 12c container database hosted on a Linux platform using incremental backup sets.

Note that since both the source (Windows) and target (Linux) platforms have the same little endian format , we do not have to do any format conversion in this case. Otherwise we would have to use the RMAN CONVERT command to perform the conversion on either the source or the target database.

Let us look at the steps involved.

Create the tablespaces and the test table and index in the source 11g Windows database

SQL> create tablespace tts_data
  2  datafile 'C:\ORADATA\ORCL2\TTS_DATA01.DBF' size 20m;

Tablespace created.

SQL> create tablespace tts_ind
  2  datafile 'C:\ORADATA\ORCL2\TTS_IND01.DBF' size 10m;

Tablespace created.



SQL> alter user sh quota unlimited on tts_data;

User altered.

SQL> alter user sh quota unlimited on tts_ind;

User altered.

SQL> create table sh.mycustomers
  2  tablespace tts_data
  3  as select * from sh.customers
  4  where rownum < 1001; Table created. SQL> create index sh.mycustomers_ind
  2  on sh.mycustomers (cust_id)
  3  tablespace tts_ind;

Index created.

At this stage the tablespace is still in READ/WRITE mode

RMAN> backup  incremental level 0 format 'C:\ORADATA\ORCL2\BKP_TS_LEV0_%U' tablespace tts_data, tts_ind;

Starting backup at 21-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=C:\ORADATA\ORCL2\TTS_DATA01.DBF
input datafile file number=00008 name=C:\ORADATA\ORCL2\TTS_IND01.DBF
channel ORA_DISK_1: starting piece 1 at 21-JUL-14
channel ORA_DISK_1: finished piece 1 at 21-JUL-14
piece handle=C:\ORADATA\ORCL2\BKP_TS_LEV0_04PDVJFU_1_1 tag=TAG20140721T190742 co
mment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUL-14

Copy the RMAN backupset pieces from the Windows OS server to the Linux OS server

Connect to the Oracle 12c Container database – pluggable database PDB3 and restore the copied backupset

[oracle@orasql-001-dev ~]$ rman target sys/passwd@pdb3

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 21 19:11:48 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CONDB1 (DBID=3738773602)

RMAN> restore from platform 'Microsoft Windows x86 64-bit' foreign datafile 7 format '/u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf' from backupset '/home/oracle/BKP_TS_LEV0_04PDVJFU_1_1';

Starting restore at 21-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISK

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 foreign file 00007
channel ORA_DISK_1: reading from backup piece /home/oracle/BKP_TS_LEV0_04PDVJFU_1_1
channel ORA_DISK_1: restoring foreign file 7 to /u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf
channel ORA_DISK_1: foreign piece handle=/home/oracle/BKP_TS_LEV0_04PDVJFU_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 21-JUL-14

RMAN>  restore from platform 'Microsoft Windows x86 64-bit' foreign datafile 8 format '/u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf' from backupset '/home/oracle/BKP_TS_LEV0_04PDVJFU_1_1';

Starting restore at 21-JUL-14
using channel ORA_DISK_1

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 foreign file 00008
channel ORA_DISK_1: reading from backup piece /home/oracle/BKP_TS_LEV0_04PDVJFU_1_1
channel ORA_DISK_1: restoring foreign file 8 to /u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf
channel ORA_DISK_1: foreign piece handle=/home/oracle/BKP_TS_LEV0_04PDVJFU_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 21-JUL-14

Note the datafiles have been restored in the required location

[oracle@orasql-001-dev ~]$ ls -l /u01/app/oracle/oradata/condb1/pdb3/tts*
-rw-r----- 1 oracle oinstall 20979712 Jul 21 19:13 /u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 21 19:14 /u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf

Now update some rows in the source Windows 11g database

SQL> update sh.mycustomers
  2  set cust_city='Perth';

1000 rows updated.

SQL> commit;

Commit complete.

This time take a RMAN level 1 incremental backup

RMAN> backup  incremental level 1 format 'C:\ORADATA\ORCL2\BKP_TS_LEV1_%U' table
space tts_data, tts_ind;

Starting backup at 21-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=C:\ORADATA\ORCL2\TTS_DATA01.DBF
input datafile file number=00008 name=C:\ORADATA\ORCL2\TTS_IND01.DBF
channel ORA_DISK_1: starting piece 1 at 21-JUL-14
channel ORA_DISK_1: finished piece 1 at 21-JUL-14
piece handle=C:\ORADATA\ORCL2\BKP_TS_LEV1_05PDVK3E_1_1 tag=TAG20140721T191806 co
mment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUL-14

Again copy the level 1 incremental backupset pieces from the Windows server to the Linux server and initiate a recovery in the 12c environment.

[oracle@orasql-001-dev ~]$ rman target sys/passwd@pdb3

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 21 19:11:48 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CONDB1 (DBID=3738773602)



RMAN> recover from platform 'Microsoft Windows x86 64-bit' foreign datafilecopy  '/u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf' from backupset '/home/oracle/BKP_TS_LEV1_05PDVK3E_1_1';

Starting restore at 21-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK

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 foreign file /u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/BKP_TS_LEV1_05PDVK3E_1_1
channel ORA_DISK_1: foreign piece handle=/home/oracle/BKP_TS_LEV1_05PDVK3E_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-JUL-14

RMAN> recover from platform 'Microsoft Windows x86 64-bit' foreign datafilecopy  '/u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf' from backupset '/home/oracle/BKP_TS_LEV1_05PDVK3E_1_1';

Starting restore at 21-JUL-14
using channel ORA_DISK_1

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 foreign file /u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/BKP_TS_LEV1_05PDVK3E_1_1
channel ORA_DISK_1: foreign piece handle=/home/oracle/BKP_TS_LEV1_05PDVK3E_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-JUL-14

Now on the source 11g Windows database we will make the tablespace read only. From this point onwards we will have a brief outage just to take a final incremental backup and a data pump export of the tablespace metadata.

Remember in versions prior to 12c we would have had to make the tablespaces read only right at the very beginning of the exercise and our outage would have been much longer in that case.

SQL> alter tablespace tts_data read only;

Tablespace altered.

SQL> alter tablespace tts_ind read only;

Tablespace altered.

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 21 20:19:20 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL2 (DBID=834251529)

RMAN> backup  incremental level 1 format 'C:\ORADATA\ORCL2\BKP_TS_LEV1_%U' table
space tts_data, tts_ind;

Starting backup at 21-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=C:\ORADATA\ORCL2\TTS_DATA01.DBF
input datafile file number=00008 name=C:\ORADATA\ORCL2\TTS_IND01.DBF
channel ORA_DISK_1: starting piece 1 at 21-JUL-14
channel ORA_DISK_1: finished piece 1 at 21-JUL-14
piece handle=C:\ORADATA\ORCL2\BKP_TS_LEV1_06PDVNMG_1_1 tag=TAG20140721T201928 co
mment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUL-14


C:\windows\system32>expdp directory=data_pump_dir dumpfile=tts_exp.dmp logfile=t
ts_exp.log transport_tablespaces=tts_data,tts_ind

Export: Release 11.2.0.1.0 - Production on Mon Jul 21 20:21:02 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  sys/******** AS SYSDBA directory=
data_pump_dir dumpfile=tts_exp.dmp logfile=tts_exp.log transport_tablespaces=tts
_data,tts_ind
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\APP\GAVIN\ADMIN\ORCL2\DPDUMP\TTS_EXP.DMP
******************************************************************************
Datafiles required for transportable tablespace TTS_DATA:
  C:\ORADATA\ORCL2\TTS_DATA01.DBF
Datafiles required for transportable tablespace TTS_IND:
  C:\ORADATA\ORCL2\TTS_IND01.DBF
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:21:48



SQL> alter tablespace tts_data read write;

Tablespace altered.

SQL> alter tablespace tts_ind read write;

Tablespace altered.

Copy the data pump export dump file and the final RMAN incremental backupset piece from source Windows to target Linux server and perform a recovery.

RMAN> recover from platform 'Microsoft Windows x86 64-bit' foreign datafilecopy  '/u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf' from backupset '/home/oracle/BKP_TS_LEV1_06PDVNMG_1_1';

Starting restore at 21-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK

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 foreign file /u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/BKP_TS_LEV1_06PDVNMG_1_1
channel ORA_DISK_1: foreign piece handle=/home/oracle/BKP_TS_LEV1_06PDVNMG_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-JUL-14



RMAN> recover from platform 'Microsoft Windows x86 64-bit' foreign datafilecopy  '/u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf' from backupset '/home/oracle/BKP_TS_LEV1_06PDVNMG_1_1';

Starting restore at 21-JUL-14
using channel ORA_DISK_1

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 foreign file /u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/BKP_TS_LEV1_06PDVNMG_1_1
channel ORA_DISK_1: foreign piece handle=/home/oracle/BKP_TS_LEV1_06PDVNMG_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 21-JUL-14

Lastly perform a data pump import of the tablespace metadata and make the tablespaces read write

[oracle@orasql-001-dev pdb3]$ impdp '"sys@pdb3 as sysdba"' directory=mytest dumpfile=TTS_EXP.DMP transport_datafiles='/u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf','/u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf'

Import: Release 12.1.0.1.0 - Production on Mon Jul 21 21:38:22 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source TSTZ version is 11 and target TSTZ version is 18.
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/********@pdb3 AS SYSDBA" directory=mytest dumpfile=TTS_EXP.DMP transport_datafiles=/u01/app/oracle/oradata/condb1/pdb3/tts_data01.dbf,/u01/app/oracle/oradata/condb1/pdb3/tts_ind01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 21 21:38:37 2014 elapsed 0 00:00:10


SQL> alter session set container=pdb3;

Session altered.

SQL>  alter tablespace tts_data read write;

Tablespace altered.

SQL> alter tablespace tts_ind  read write;

Tablespace altered.

Verify that the table has the last update statement which we had executed on the source database

SQL> select count(*) from sh.mycustomers;

  COUNT(*)
----------
      1000

SQL> select distinct cust_city from sh.mycustomers;

CUST_CITY
------------------------------
Perth

 

1 Comments

Prashant
  • Jan 11 2018
Hi Gavin, Thanks for the document . Can you please let us know, in this case as well we need to check tablespace is self contained etc..

Leave Reply

Your email address will not be published. Required fields are marked *