Oracle 12c: Transport tablespaces across platforms

July 15th, 2013 | Posted in 12c, Blog | No Comments


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

sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:00:47 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> CREATE TABLESPACE TBS_TRANSP DATAFILE AUTOEXTEND ON MAXSIZE 100M;

Tablespace created.

SQL> CREATE TABLE TAB_TRASNP (ID NUMBER) TABLESPACE TBS_TRANSP;

Table created.

SQL> insert into TAB_TRASNP values (1);

1 row created.

SQL> commit;

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.


SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS_TRANSP', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

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.

SQL> set linesize 2000
SQL> set pagesize 2000
SQL> select * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT      CON_ID
----------- ----------------------------------------------------------------------------------------------------- -------------- ----------
          1 Solaris[tm] OE (32-bit)                                                                               Big                     0
          2 Solaris[tm] OE (64-bit)                                                                               Big                     0
          7 Microsoft Windows IA (32-bit)                                                                         Little                  0
         10 Linux IA (32-bit)                                                                                     Little                  0
          6 AIX-Based Systems (64-bit)                                                                            Big                     0
          3 HP-UX (64-bit)                                                                                        Big                     0
          5 HP Tru64 UNIX                                                                                         Little                  0
          4 HP-UX IA (64-bit)                                                                                     Big                     0
         11 Linux IA (64-bit)                                                                                     Little                  0
         15 HP Open VMS                                                                                           Little                  0
          8 Microsoft Windows IA (64-bit)                                                                         Little                  0
          9 IBM zSeries Based Linux                                                                               Big                     0
         13 Linux x86 64-bit                                                                                      Little                  0
         16 Apple Mac OS                                                                                          Big                     0
         12 Microsoft Windows x86 64-bit                                                                          Little                  0
         17 Solaris Operating System (x86)                                                                        Little                  0
         18 IBM Power Based Linux                                                                                 Big                     0
         19 HP IA Open VMS                                                                                        Little                  0
         20 Solaris Operating System (x86-64)                                                                     Little                  0
         21 Apple Mac OS (x86-64)                                                                                 Little                  0

20 rows selected.

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.

SQL> ALTER TABLESPACE TBS_TRANSP READ ONLY;

Tablespace altered.

SQL>

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

BACKUP TO PLATFORM 'Microsoft Windows x86 64-bit'
FORMAT '/home/oracle/PDB02/tbs_transp.bck'
DATAPUMP FORMAT '/home/oracle/PDB02/tbs_transp.dmp'
TABLESPACE TBS_TRANSP;
sandbox1(orawiss):/home/oracle/PDB02>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 15 06:02:50 2013

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

RMAN>  CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN>

RMAN> BACKUP TO PLATFORM 'Microsoft Windows x86 64-bit'
FORMAT '/home/oracle/PDB02/tbs_transp.bck'
DATAPUMP FORMAT '/home/oracle/PDB02/tbs_transp.dmp'
TABLESPACE TBS_TRANSP;
2> 3> 4>

Starting backup at 07/15/2013 06:03:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=271 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=270 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=32 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=262 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces...
   EXPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

   EXPDP> Starting "SYSBACKUP"."TRANSPORT_EXP_ORAWISS_cfoz":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYSBACKUP"."TRANSPORT_EXP_ORAWISS_cfoz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_cfoz is:
   EXPDP>   /opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TBS_TRANSP:
   EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679
   EXPDP> Job "SYSBACKUP"."TRANSPORT_EXP_ORAWISS_cfoz" successfully completed at Mon Jul 15 06:05:17 2013 elapsed 0 00:01:07
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=00060 name=+DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679
channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:21
channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:23
piece handle=/home/oracle/PDB02/tbs_transp.bck tag=TAG20130715T060353 comment=NONE
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=/opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp
channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:23
channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:24
piece handle=/home/oracle/PDB02/tbs_transp.dmp tag=TAG20130715T060353 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07/15/2013 06:05:24

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:

Restore foreign tablespace TBS_TRANSP 
FORMAT 'C:\PDB02\tbs_transp.dbf'
FROM BACKUPSET 'C:\PDB02\tbs_transp.bck'
DUMP FILE FROM BACKUPSET 'C:\PDB02\tbs_transp.dmp';
C:\Users\wissem>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 15 12:08:36 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: CDBTST (DBID=918472262)

RMAN> 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';

Starting restore at 15-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=366 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 all files in foreign tablespace TBS_TRANSP
channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.bck
channel ORA_DISK_1: restoring foreign file 60 to C:\PDB02\TBS_TRANSP.DBF
channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
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 C:\app\wissem\product\12.1.0\dbh_1/dbs/backup_tts_CDBTST_83935.dmp
channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.dmp
channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.dmp
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Performing import of metadata...
   IMPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

   IMPDP> Master table "SYSBACKUP"."TSPITR_IMP_CDBTST_gtfF" successfully loaded/unloaded
   IMPDP> Source timezone version is +00:00 and target timezone version is -05:00.
   IMPDP> Starting "SYSBACKUP"."TSPITR_IMP_CDBTST_gtfF":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYSBACKUP"."TSPITR_IMP_CDBTST_gtfF" successfully completed at Mon Jul 15 12:12:43 2013 elapsed 0 00:00:23
Import completed

Finished restore at 15-JUL-13

RMAN> exit


Recovery Manager complete.

C:\Users\wissem>

Check if the tablespace exists.

C:\Users\wissem>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 12:13:53 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from TAB_TRASNP;

        ID
----------
         1

SQL> exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\wissem>

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

SQL> CREATE TABLESPACE TBS_TRANSP_2 DATAFILE AUTOEXTEND ON MAXSIZE 100M;

Tablespace created.

SQL>  CREATE TABLE TAB_TRASNP_2 (ID NUMBER) TABLESPACE TBS_TRANSP_2;

Table created.

SQL>  insert into TAB_TRASNP_2 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS_TRANSP_2',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL>

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

sandbox1(orawiss):/home/oracle/PDB02>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 15 06:29:11 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> BACKUP
FOR TRANSPORT
ALLOW INCONSISTENT
INCREMENTAL LEVEL 0
TABLESPACE TBS_TRANSP_2 FORMAT '/home/oracle/PDB02/tbs_incons_2.bck'
;

Starting backup at 07/15/2013 06:33:26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
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=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239
channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:33:27
channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:33:28
piece handle=/home/oracle/PDB02/tbs_incons_2.bck tag=TAG20130715T063327 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07/15/2013 06:33:28

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;

SQL> insert into TAB_TRASNP_2 values(3);

1 row created.

SQL> commit;

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.

BACKUP
FOR TRANSPORT
ALLOW INCONSISTENT
INCREMENTAL LEVEL 1
TABLESPACE TBS_TRANSP_2 FORMAT '/home/oracle/PDB02/tbs_incons_2_lvl1.bck'
sandbox1(orawiss):/home/oracle/PDB02>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 15 06:36:12 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> BACKUP
FOR TRANSPORT
ALLOW INCONSISTENT
INCREMENTAL LEVEL 1
TABLESPACE TBS_TRANSP_2 FORMAT '/home/oracle/PDB02/tbs_incons_2_lvl1.bck'2> 3> 4> 5>
6> ;

Starting backup at 07/15/2013 06:36:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=271 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=29 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=263 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=28 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=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239
channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:36:29
channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:36:30
piece handle=/home/oracle/PDB02/tbs_incons_2_lvl1.bck tag=TAG20130715T063629 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07/15/2013 06:36:30

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.

sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:39:23 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> ALTER TABLESPACE TBS_TRANSP_2 READ ONLY;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
sandbox1(orawiss):/home/oracle/PDB02>
sandbox1(orawiss):/home/oracle/PDB02>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 15 06:41:10 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> BACKUP
FOR TRANSPORT
INCREMENTAL LEVEL 1
TABLESPACE TBS_TRANSP_2
FORMAT '/home/oracle/PDB02/tbs_incons_final.bck'
DATAPUMP FORMAT '/home/oracle/PDB02/tbs_incons_final.dmp'
DESTINATION '/home/oracle/PDB02';

Starting backup at 07/15/2013 06:41:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=262 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=271 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=28 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=263 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces...
   EXPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

   EXPDP> Starting "SYSBACKUP"."TRANSPORT_EXP_ORAWISS_urkc":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYSBACKUP"."TRANSPORT_EXP_ORAWISS_urkc" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_urkc is:
   EXPDP>   /home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TBS_TRANSP_2:
   EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239
   EXPDP> Job "SYSBACKUP"."TRANSPORT_EXP_ORAWISS_urkc" successfully completed at Mon Jul 15 06:42:30 2013 elapsed 0 00:00:51
Export completed

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=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239
channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:33
channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:34
piece handle=/home/oracle/PDB02/tbs_incons_final.bck tag=TAG20130715T064125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
input Data Pump dump file=/home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp
channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:34
channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:35
piece handle=/home/oracle/PDB02/tbs_incons_final.dmp tag=TAG20130715T064125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07/15/2013 06:42:35

RMAN>

Copy all backup / dum files to destination Windows host;

First, Restore from full backup:

C:\Users\wissem>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 15 12:51:51 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: CDBTST (DBID=918472262)

RMAN> RESTORE
2> FROM PLATFORM 'Linux x86 64-bit'
3> FOREIGN DATAFILE 61
4> FORMAT 'C:\PDB02\tbs_transp_2_61.df'
5> FROM BACKUPSET 'C:\PDB02\tbs_incons_2.bck';

Starting restore at 15-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=364 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 00061
channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2.bck
channel ORA_DISK_1: restoring foreign file 61 to C:\PDB02\TBS_TRANSP_2_61.DF
channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_2.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 15-JUL-13

RMAN>

Recover from first incremental backup


RMAN> RECOVER
2> FROM PLATFORM 'Linux x86 64-bit'
3> FOREIGN DATAFILECOPY 'C:\PDB02\tbs_transp_2_61.df'
4> FROM BACKUPSET 'C:\PDB02\tbs_incons_2_lvl1.bck';

Starting restore at 15-JUL-13
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 C:\PDB02\tbs_transp_2_61.df
channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2_lvl1.bck
channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_2_LVL1.BCK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUL-13

RMAN>

Recover from last incremental backup

RMAN> RECOVER
2> FROM PLATFORM 'Linux x86 64-bit'
3> FOREIGN DATAFILECOPY 'C:\PDB02\tbs_transp_2_61.df'
4> FROM BACKUPSET 'C:\PDB02\tbs_incons_final.bck';

Starting restore at 15-JUL-13
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 C:\PDB02\tbs_transp_2_61.df
channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.bck
channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_FINAL.BCK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUL-13

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.

RMAN> RESTORE
2> FROM PLATFORM 'Linux x86 64-bit'
3> DUMP FILE 'tbs_incons_final_dest.dmp'
4> DATAPUMP DESTINATION 'C:\PDB02'
5> FROM BACKUPSET 'C:\PDB02\tbs_incons_final.dmp';

Starting restore at 15-JUL-13
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 Data Pump dump file to C:\PDB02/tbs_incons_final_dest.dmp
channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.dmp
channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_final.dmp
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 15-JUL-13

RMAN>

Create a directory object:

C:\Users\wissem>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:03:57 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create directory TBS_TRANSP_DIR AS 'C:\PDB02';

Directory created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\wissem>

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.

C:\Users\wissem>impdp \"/ as sysdba\" directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles='C:\PDB02\tbs_transp_2_61.df' nologfile=Y

Import: Release 12.1.0.1.0 - Production on Mon Jul 15 13:07:17 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -05:00.
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
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 15 13:07:34 2013 elapsed 0 00:00:08


C:\Users\wissem>

CHeck the table exists with its records.

C:\Users\wissem>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:08:40 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from TAB_TRASNP_2;

        ID
----------
         2
         3

SQL>

cheers,
Wissem

References: Doc 12cR1



No Comments to “Oracle 12c: Transport tablespaces across platforms”

There are no comments yet, add one below.


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • gabriel: what version of sqldeveloper support plugable database?
  • Kal: Quality – thanks…
  • wissem: Well , like I mentioned in the post, delete service and add service using IP.
  • Kelly: were you able to solve this? Thanks!
  • wissem: Hello, You can buy the book from PakT: http://www.packtpub.com/oracle -data-guard-11gr2-administr...

Oracle-Class Forums Latest Activity