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
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..