Home > 12c, Database, new features in 12c, Oracle > Transport tablespaces across platforms in Oracle Database 12c

Transport tablespaces across platforms in Oracle Database 12c

Introduction:

Transportable Tablespaces helps to copy one or more tablespaces from one Oracle Database to another.
Starting with Oracle Database version 11g, transport tablespaces can be used to migrate database from one platform to another.
This includes transporting tablespaces acrosse platforms with differemt endian formats.

Oracle database 12c Realease 1 comes with the possibility to use backupsets and image copies to transport data between platforms.

The new clause “ALLOW INCONSISTENT” in a BACKUP or CONVERT command creates a cross-platform inconsistent backup of one or more tablespaces.
You can create an inconsistent backup of the tablespace when the tablespace is still in read/write mode.

The first inconsistent backup is a level 0 incremental backup. Then, you can create multiple cross-platform level 1 incremental backups.
The final cross-platform incremental backup must be a consistent backup which requires bring the tablespace in read-only mode.

In the following article, we are going to :

– Perform a Cross-Platform Transport of Tablespaces Using consistent Backupsets.
– Perform a Cross-Platform Transport of Tablespaces Using Inconsistent Backupsets.

2- Performing Cross-Platform Transport of Tablespaces Using consistent Backupsets:

First, let’s create a new tablespace called “TBS_TRANSP”.
We will move this tablespace from Oracle Enterprise Linux 64 bit to Windows 8 64 bit.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:00:47 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  11. and Real Application Testing options  
  12.   
  13. SQL> CREATE TABLESPACE TBS_TRANSP DATAFILE AUTOEXTEND ON MAXSIZE 100M;  
  14.   
  15. Tablespace created.  
  16.   
  17. SQL> CREATE TABLE TAB_TRASNP (ID NUMBER) TABLESPACE TBS_TRANSP;  
  18.   
  19. Table created.  
  20.   
  21. SQL> insert into TAB_TRASNP values (1);  
  22.   
  23. 1 row created.  
  24.   
  25. SQL> commit;  
  26.   
  27. Commit complete.  

Pr-requisites:

Check if tablespace is self-contained; which means check for logical or physical dependencies between objects in the transportable tablespace
and those outside of the tablespace.

 
  1. SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS_TRANSP’TRUE);  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;  
  6.   
  7. no rows selected  
  8.   
  9. SQL>  

From the output above, we can go ahead with the procedure of transporting the tablespace as there is no violations

and the tablespace is self-contained.

Check if plateforms supported for cross-platform tablespace transport; note Microsoft Windows x86 64-bit is supported.

 
  1. SQL> set linesize 2000  
  2. SQL> set pagesize 2000  
  3. SQL> select * from v$transportable_platform;  
  4.   
  5. PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT      CON_ID  
  6. ———– —————————————————————————————————– ————– ———-  
  7.           1 Solaris[tm] OE (32-bit)                                                                               Big                     0  
  8.           2 Solaris[tm] OE (64-bit)                                                                               Big                     0  
  9.           7 Microsoft Windows IA (32-bit)                                                                         Little                  0  
  10.          10 Linux IA (32-bit)                                                                                     Little                  0  
  11.           6 AIX-Based Systems (64-bit)                                                                            Big                     0  
  12.           3 HP-UX (64-bit)                                                                                        Big                     0  
  13.           5 HP Tru64 UNIX                                                                                         Little                  0  
  14.           4 HP-UX IA (64-bit)                                                                                     Big                     0  
  15.          11 Linux IA (64-bit)                                                                                     Little                  0  
  16.          15 HP Open VMS                                                                                           Little                  0  
  17.           8 Microsoft Windows IA (64-bit)                                                                         Little                  0  
  18.           9 IBM zSeries Based Linux                                                                               Big                     0  
  19.          13 Linux x86 64-bit                                                                                      Little                  0  
  20.          16 Apple Mac OS                                                                                          Big                     0  
  21.          12 Microsoft Windows x86 64-bit                                                                          Little                  0  
  22.          17 Solaris Operating System (x86)                                                                        Little                  0  
  23.          18 IBM Power Based Linux                                                                                 Big                     0  
  24.          19 HP IA Open VMS                                                                                        Little                  0  
  25.          20 Solaris Operating System (x86-64)                                                                     Little                  0  
  26.          21 Apple Mac OS (x86-64)                                                                                 Little                  0  
  27.   
  28. 20 rows selected.  
  29.   
  30. SQL>  

Both databases must be COMPATIBLE set to 12.0 or greater.

Bring the tablespace TBS_TRANSP in read only mode prior the TTS (Trasportable TableSpace) process.

 
  1. SQL> ALTER TABLESPACE TBS_TRANSP READ ONLY;  
  2.   
  3. Tablespace altered.  
  4.   
  5. SQL>  

Perform a cross-platform Backup of the tablespace TBS_TRANSP and datapump export:

 
  1. BACKUP TO PLATFORM ‘Microsoft Windows x86 64-bit’  
  2. FORMAT ‘/home/oracle/PDB02/tbs_transp.bck’  
  3. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_transp.dmp’  
  4. TABLESPACE TBS_TRANSP;  
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:02:50 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN>  CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN>  
  13.   
  14. RMAN> BACKUP TO PLATFORM ‘Microsoft Windows x86 64-bit’  
  15. FORMAT ‘/home/oracle/PDB02/tbs_transp.bck’  
  16. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_transp.dmp’  
  17. TABLESPACE TBS_TRANSP;  
  18. 2> 3> 4>  
  19.   
  20. Starting backup at 07/15/2013 06:03:50  
  21. using target database control file instead of recovery catalog  
  22. allocated channel: ORA_DISK_1  
  23. channel ORA_DISK_1: SID=271 device type=DISK  
  24. allocated channel: ORA_DISK_2  
  25. channel ORA_DISK_2: SID=21 device type=DISK  
  26. allocated channel: ORA_DISK_3  
  27. channel ORA_DISK_3: SID=270 device type=DISK  
  28. allocated channel: ORA_DISK_4  
  29. channel ORA_DISK_4: SID=32 device type=DISK  
  30. allocated channel: ORA_DISK_5  
  31. channel ORA_DISK_5: SID=262 device type=DISK  
  32. Running TRANSPORT_SET_CHECK on specified tablespaces  
  33. TRANSPORT_SET_CHECK completed successfully  
  34.   
  35. Performing export of metadata for specified tablespaces…  
  36.    EXPDP>  
  37. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  38.   
  39.    EXPDP> Starting “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz”:  
  40.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  41.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  42.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  43.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  44.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  45.    EXPDP> Master table “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz” successfully loaded/unloaded  
  46.    EXPDP> ******************************************************************************  
  47.    EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_cfoz is:  
  48.    EXPDP>   /opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp  
  49.    EXPDP> ******************************************************************************  
  50.    EXPDP> Datafiles required for transportable tablespace TBS_TRANSP:  
  51.    EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679  
  52.    EXPDP> Job “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz” successfully completed at Mon Jul 15 06:05:17 2013 elapsed 0 00:01:07  
  53. Export completed  
  54.   
  55. channel ORA_DISK_1: starting full datafile backup set  
  56. channel ORA_DISK_1: specifying datafile(s) in backup set  
  57. input datafile file number=00060 name=+DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679  
  58. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:21  
  59. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:23  
  60. piece handle=/home/oracle/PDB02/tbs_transp.bck tag=TAG20130715T060353 comment=NONE  
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02  
  62. channel ORA_DISK_1: starting full datafile backup set  
  63. input Data Pump dump file=/opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp  
  64. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:23  
  65. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:24  
  66. piece handle=/home/oracle/PDB02/tbs_transp.dmp tag=TAG20130715T060353 comment=NONE  
  67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  68. Finished backup at 07/15/2013 06:05:24  
  69.   
  70. RMAN>  

Copy the Dump file and the backup file to destination host. The destination directory : C:\PDB02

Restore the foreign tablespace specifying the dump file and the backup taken in the previous step:

 
  1. Restore foreign tablespace TBS_TRANSP   
  2. FORMAT ‘C:\PDB02\tbs_transp.dbf’  
  3. FROM BACKUPSET ‘C:\PDB02\tbs_transp.bck’  
  4. DUMP FILE FROM BACKUPSET ‘C:\PDB02\tbs_transp.dmp’;  
 
  1. C:\Users\wissem>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 12:08:36 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: CDBTST (DBID=918472262)  
  11.   
  12. RMAN> Restore foreign tablespace TBS_TRANSP  
  13. 2> FORMAT ‘C:\PDB02\tbs_transp.dbf’  
  14. 3> FROM BACKUPSET ‘C:\PDB02\tbs_transp.bck’  
  15. 4> DUMP FILE FROM BACKUPSET ‘C:\PDB02\tbs_transp.dmp’;  
  16.   
  17. Starting restore at 15-JUL-13  
  18. using target database control file instead of recovery catalog  
  19. allocated channel: ORA_DISK_1  
  20. channel ORA_DISK_1: SID=366 device type=DISK  
  21.   
  22. channel ORA_DISK_1: starting datafile backup set restore  
  23. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  24. channel ORA_DISK_1: restoring all files in foreign tablespace TBS_TRANSP  
  25. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.bck  
  26. channel ORA_DISK_1: restoring foreign file 60 to C:\PDB02\TBS_TRANSP.DBF  
  27. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.bck  
  28. channel ORA_DISK_1: restored backup piece 1  
  29. channel ORA_DISK_1: restore complete, elapsed time: 00:00:05  
  30. channel ORA_DISK_1: starting datafile backup set restore  
  31. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  32. channel ORA_DISK_1: restoring Data Pump dump file to C:\app\wissem\product\12.1.0\dbh_1/dbs/backup_tts_CDBTST_83935.dmp  
  33. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.dmp  
  34. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.dmp  
  35. channel ORA_DISK_1: restored backup piece 1  
  36. channel ORA_DISK_1: restore complete, elapsed time: 00:00:02  
  37.   
  38. Performing import of metadata…  
  39.    IMPDP>  
  40. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  41.   
  42.    IMPDP> Master table “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF” successfully loaded/unloaded  
  43.    IMPDP> Source timezone version is +00:00 and target timezone version is -05:00.  
  44.    IMPDP> Starting “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF”:  
  45.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  46.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  47.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  48.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  49.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  50.    IMPDP> Job “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF” successfully completed at Mon Jul 15 12:12:43 2013 elapsed 0 00:00:23  
  51. Import completed  
  52.   
  53. Finished restore at 15-JUL-13  
  54.   
  55. RMAN> exit  
  56.   
  57.   
  58. Recovery Manager complete.  
  59.   

Check if the tablespace exists.

 
  1. C:\Users>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 12:13:53 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> select * from TAB_TRASNP;  
  13.   
  14.         ID  
  15. ———-  
  16.          1  
  17.   
  18. SQL> exit;  
  19. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  20. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  21.   
  22. C:\Users>  

Performing Cross-Platform Transport of Tablespaces Using Inconsistent Backupsets:

Note: the same per-requisites we have seen in the previous chapter must be used here as well.

First, let’s create a new tablespace called “TBS_TRANSP_2″.
We will move this tablespace from Oracle Enterprise Linux 64 bit to Windows 8 64 bit.

 
  1. SQL> CREATE TABLESPACE TBS_TRANSP_2 DATAFILE AUTOEXTEND ON MAXSIZE 100M;  
  2.   
  3. Tablespace created.  
  4.   
  5. SQL>  CREATE TABLE TAB_TRASNP_2 (ID NUMBER) TABLESPACE TBS_TRANSP_2;  
  6.   
  7. Table created.  
  8.   
  9. SQL>  insert into TAB_TRASNP_2 values(2);  
  10.   
  11. 1 row created.  
  12.   
  13. SQL> commit;  
  14.   
  15. Commit complete.  
  16.   
  17. SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS_TRANSP_2’,TRUE);  
  18.   
  19. PL/SQL procedure successfully completed.  
  20.   
  21. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;  
  22.   
  23. no rows selected  
  24.   
  25. SQL>  

Let’s take a full 0 backup of the TBS_TRANSP_2.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:29:11 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. ALLOW INCONSISTENT  
  15. INCREMENTAL LEVEL 0  
  16. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2.bck’  
  17. ;  
  18.   
  19. Starting backup at 07/15/2013 06:33:26  
  20. using channel ORA_DISK_1  
  21. using channel ORA_DISK_2  
  22. using channel ORA_DISK_3  
  23. using channel ORA_DISK_4  
  24. using channel ORA_DISK_5  
  25. channel ORA_DISK_1: starting incremental level 0 datafile backup set  
  26. channel ORA_DISK_1: specifying datafile(s) in backup set  
  27. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  28. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:33:27  
  29. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:33:28  
  30. piece handle=/home/oracle/PDB02/tbs_incons_2.bck tag=TAG20130715T063327 comment=NONE  
  31. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  32. Finished backup at 07/15/2013 06:33:28  
  33.   
  34. RMAN>  

Because FOR TRANSPORT is used instead of TO PLATFORM, this cross-platform backup can be restored on any platform.
The conversion will be performed on the destination database.

Let’s insert more records into the table;

 
  1. SQL> insert into TAB_TRASNP_2 values(3);  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> commit;  
  6.   
  7. Commit complete.  

Create a cross-platform level 1 incremental backup
of the tablespace TBS_TRANSP_2 that contains the changes made since last backup.
The tablespace is still in read/write mode.

 
  1. BACKUP  
  2. FOR TRANSPORT  
  3. ALLOW INCONSISTENT  
  4. INCREMENTAL LEVEL 1  
  5. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2_lvl1.bck’  
 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:36:12 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. ALLOW INCONSISTENT  
  15. INCREMENTAL LEVEL 1  
  16. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2_lvl1.bck’2> 3> 4> 5>  
  17. 6> ;  
  18.   
  19. Starting backup at 07/15/2013 06:36:26  
  20. using target database control file instead of recovery catalog  
  21. allocated channel: ORA_DISK_1  
  22. channel ORA_DISK_1: SID=18 device type=DISK  
  23. allocated channel: ORA_DISK_2  
  24. channel ORA_DISK_2: SID=271 device type=DISK  
  25. allocated channel: ORA_DISK_3  
  26. channel ORA_DISK_3: SID=29 device type=DISK  
  27. allocated channel: ORA_DISK_4  
  28. channel ORA_DISK_4: SID=263 device type=DISK  
  29. allocated channel: ORA_DISK_5  
  30. channel ORA_DISK_5: SID=28 device type=DISK  
  31. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  32. channel ORA_DISK_1: specifying datafile(s) in backup set  
  33. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  34. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:36:29  
  35. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:36:30  
  36. piece handle=/home/oracle/PDB02/tbs_incons_2_lvl1.bck tag=TAG20130715T063629 comment=NONE  
  37. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  38. Finished backup at 07/15/2013 06:36:30  
  39.   
  40. RMAN>  

To minimize application downtime, the level 0 and level 1 incremental backups created in previous steps
can be restored and applied on the destination database while the source tablespace is still in read/write mode.
When the destination database catches up with last level 1 incremental backup, you can create the final incremental
backup with the tablespace placed in read-only mode.

Let’s bring now the TABLESPACE TBS_TRANSP_2 in read only mode and perform a last incremental 1 backup
and an export datapump file
before trying to move the backups to the destination host.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:39:23 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  11. and Real Application Testing options  
  12.   
  13. SQL> ALTER TABLESPACE TBS_TRANSP_2 READ ONLY;  
  14.   
  15. Tablespace altered.  
  16.   
  17. SQL> exit  
  18. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  19. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  20. and Real Application Testing options  
  21. sandbox1(orawiss):/home/oracle/PDB02>  
 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:41:10 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. INCREMENTAL LEVEL 1  
  15. TABLESPACE TBS_TRANSP_2  
  16. FORMAT ‘/home/oracle/PDB02/tbs_incons_final.bck’  
  17. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_incons_final.dmp’  
  18. DESTINATION ‘/home/oracle/PDB02’;  
  19.   
  20. Starting backup at 07/15/2013 06:41:23  
  21. using target database control file instead of recovery catalog  
  22. allocated channel: ORA_DISK_1  
  23. channel ORA_DISK_1: SID=31 device type=DISK  
  24. allocated channel: ORA_DISK_2  
  25. channel ORA_DISK_2: SID=262 device type=DISK  
  26. allocated channel: ORA_DISK_3  
  27. channel ORA_DISK_3: SID=271 device type=DISK  
  28. allocated channel: ORA_DISK_4  
  29. channel ORA_DISK_4: SID=28 device type=DISK  
  30. allocated channel: ORA_DISK_5  
  31. channel ORA_DISK_5: SID=263 device type=DISK  
  32. Running TRANSPORT_SET_CHECK on specified tablespaces  
  33. TRANSPORT_SET_CHECK completed successfully  
  34.   
  35. Performing export of metadata for specified tablespaces…  
  36.    EXPDP>  
  37. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  38.   
  39.    EXPDP> Starting “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc”:  
  40.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  41.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  42.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  43.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  44.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  45.    EXPDP> Master table “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc” successfully loaded/unloaded  
  46.    EXPDP> ******************************************************************************  
  47.    EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_urkc is:  
  48.    EXPDP>   /home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp  
  49.    EXPDP> ******************************************************************************  
  50.    EXPDP> Datafiles required for transportable tablespace TBS_TRANSP_2:  
  51.    EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  52.    EXPDP> Job “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc” successfully completed at Mon Jul 15 06:42:30 2013 elapsed 0 00:00:51  
  53. Export completed  
  54.   
  55. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  56. channel ORA_DISK_1: specifying datafile(s) in backup set  
  57. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  58. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:33  
  59. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:34  
  60. piece handle=/home/oracle/PDB02/tbs_incons_final.bck tag=TAG20130715T064125 comment=NONE  
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  62. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  63. input Data Pump dump file=/home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp  
  64. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:34  
  65. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:35  
  66. piece handle=/home/oracle/PDB02/tbs_incons_final.dmp tag=TAG20130715T064125 comment=NONE  
  67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  68. Finished backup at 07/15/2013 06:42:35  
  69.   
  70. RMAN>  

Copy all backup / dum files to destination Windows host;

First, Restore from full backup:

 
  1. C:\Users\wissem>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 12:51:51 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: CDBTST (DBID=918472262)  
  11.   
  12. RMAN> RESTORE  
  13. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  14. 3> FOREIGN DATAFILE 61  
  15. 4> FORMAT ‘C:\PDB02\tbs_transp_2_61.df’  
  16. 5> FROM BACKUPSET ‘C:\PDB02\tbs_incons_2.bck’;  
  17.   
  18. Starting restore at 15-JUL-13  
  19. using target database control file instead of recovery catalog  
  20. allocated channel: ORA_DISK_1  
  21. channel ORA_DISK_1: SID=364 device type=DISK  
  22.   
  23. channel ORA_DISK_1: starting datafile backup set restore  
  24. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  25. channel ORA_DISK_1: restoring foreign file 00061  
  26. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2.bck  
  27. channel ORA_DISK_1: restoring foreign file 61 to C:\PDB02\TBS_TRANSP_2_61.DF  
  28. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_2.bck  
  29. channel ORA_DISK_1: restored backup piece 1  
  30. channel ORA_DISK_1: restore complete, elapsed time: 00:00:04  
  31. Finished restore at 15-JUL-13  
  32.   
  33. RMAN>  

Recover from first incremental backup

 
  1. RMAN> RECOVER  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> FOREIGN DATAFILECOPY ‘C:\PDB02\tbs_transp_2_61.df’  
  4. 4> FROM BACKUPSET ‘C:\PDB02\tbs_incons_2_lvl1.bck’;  
  5.   
  6. Starting restore at 15-JUL-13  
  7. using channel ORA_DISK_1  
  8.   
  9. channel ORA_DISK_1: starting datafile backup set restore  
  10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  11. channel ORA_DISK_1: restoring foreign file C:\PDB02\tbs_transp_2_61.df  
  12. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2_lvl1.bck  
  13. channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_2_LVL1.BCK  
  14. channel ORA_DISK_1: restored backup piece 1  
  15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
  16. Finished restore at 15-JUL-13  
  17.   
  18. RMAN>  

Recover from last incremental backup

  1. RMAN> RECOVER  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> FOREIGN DATAFILECOPY ‘C:\PDB02\tbs_transp_2_61.df’  
  4. 4> FROM BACKUPSET ‘C:\PDB02\tbs_incons_final.bck’;  
  5.   
  6. Starting restore at 15-JUL-13  
  7. using channel ORA_DISK_1  
  8.   
  9. channel ORA_DISK_1: starting datafile backup set restore  
  10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  11. channel ORA_DISK_1: restoring foreign file C:\PDB02\tbs_transp_2_61.df  
  12. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.bck  
  13. channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_FINAL.BCK  
  14. channel ORA_DISK_1: restored backup piece 1  
  15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
  16. Finished restore at 15-JUL-13  
  17.   
  18. RMAN>  

Restore the backup set containing the export dump file. The following command will create a dump file
called ‘tbs_incons_final_dest.dmp’ which be used later to import the metadata.

  1. RMAN> RESTORE  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> DUMP FILE ‘tbs_incons_final_dest.dmp’  
  4. 4> DATAPUMP DESTINATION ‘C:\PDB02’  
  5. 5> FROM BACKUPSET ‘C:\PDB02\tbs_incons_final.dmp’;  
  6.   
  7. Starting restore at 15-JUL-13  
  8. using channel ORA_DISK_1  
  9.   
  10. channel ORA_DISK_1: starting datafile backup set restore  
  11. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  12. channel ORA_DISK_1: restoring Data Pump dump file to C:\PDB02/tbs_incons_final_dest.dmp  
  13. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.dmp  
  14. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_final.dmp  
  15. channel ORA_DISK_1: restored backup piece 1  
  16. channel ORA_DISK_1: restore complete, elapsed time: 00:00:02  
  17. Finished restore at 15-JUL-13  
  18.   
  19. RMAN>  

Create a directory object:

 
  1. C:\Users>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:03:57 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> create directory TBS_TRANSP_DIR AS ‘C:\PDB02’;  
  13.   
  14. Directory created.  
  15.   
  16. SQL> exit  
  17. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  18. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  19.   

Plug the tablespace in to the destination database. Use the Data Pump import utility
to import the dump file containing the tablespace metadata in to the destination database.

 
  1. C:\Users>impdp \“/ as sysdba\” directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles=’C:\PDB02\tbs_transp_2_61.df’ nologfile=Y 
  2.  
  3. Import: Release 12.1.0.1.0 – Production on Mon Jul 15 13:07:17 2013 
  4.  
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved. 
  6.  
  7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production 
  8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
  9.  
  10. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. 
  11.  
  12. Master table “SYS“.”SYS_IMPORT_TRANSPORTABLE_01” successfully loaded/unloaded 
  13. Source timezone version is +00:00 and target timezone version is -05:00. 
  14. Starting “SYS“.”SYS_IMPORT_TRANSPORTABLE_01“:  “/******** AS SYSDBA” directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles=’C:\PDB02\tbs_transp_2_61.df’ nologfile=Y 
  15. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 
  16. Processing object type TRANSPORTABLE_EXPORT/TABLE 
  17. Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS 
  18. Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER 
  19. Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 
  20. Job “SYS“.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Mon Jul 15 13:07:34 2013 elapsed 0 00:00:08  z
  21.   

CHeck the table exists with its records.

 
  1. C:\Users\wissem>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:08:40 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> select * from TAB_TRASNP_2;  
  13.   
  14.         ID  
  15. ———-  
  16.          2  
  17.          3  
  18.   
  19. SQL>  
Advertisements
  1. September 2, 2014 at 1:30 pm

    Reblogged this on Dinesh Ram Kali..

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: