Cross-platform, cross-version tablespace transport (11g Windows to 12c Unix)

In 12c, RMAN can restore a Windows db backupset to a Unix environment.

The backupset can come from an earlier db version – 10g and later are supported.

For instance, a 11g tablespace backup, plus a transportable tablespace metadata dump file can be taken to a 12c database – from another platform - as long as endian format is same.

RMAN has new functionality to restore foreign tables/tablespaces/databases.

Foreign tables/tablespaces/databases are those which do not belong to the target database. The foreign objects may have come from another platform also.

The steps below are for a cross-platform, cross-version restore of a sample tablespace.

The technique used is a cross-platform restore followed by a transportable tablespace plug-in operation.

Source: Oracle Database 11g Enteprise Edition/Windows 2003 64 bit.

Destination: Oracle Database 12c Enterprise Edition/ Oracle Enterprise Linux 2.6.

Source schema: demo_user

Source tablespace name:ts_demo

Destination schema: salesadmin

Step 1 -  Check tablespace containment at the 11g source database.

Identify the tablespace and make sure it is self-contained and resolve containment issues, if any.

Run the dbms_tts.transport_set_check program and query the transport_set_violations view.
The view should not list any violations.

SQL>

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as SYS

 

SQL> begin

  2    dbms_tts.transport_set_check(ts_list => 'TS_DEMO');

  3  end;

  4  /

PL/SQL procedure successfully completed

SQL> select * from transport_set_violations;

VIOLATIONS

--------------------------------------------------------------------------------

SQL>

    

Step 2 - Make the tablespace read only and back it up as a backupset using RMAN at the 11g source.

Make the tablespace read only for doing a transportable tablespace operation.

Back up the tablespace using rman as a backupset. 

RMAN> SQL 'ALTER TABLESPACE TS_DEMO READ ONLY';
using target database control file instead of recovery catalog
sql statement: ALTER TABLESPACE TS_DEMO READ ONLY

RMAN> backup as compressed backupset tablespace TS_DEMO;
RMAN> backup as compressed backupset tablespace ts_demo format 'c:\temp\bkup_ts_demo.bck';
Starting backup at 27-JAN-14
using channel ORA_DISK_1
piece handle=C:\TEMP\BKUP_TS_DEMO.BCK tag=TAG20140127T154617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 27-JAN-14
RMAN> EXIT 

 Step 3  - Create a Data Pump transportable metadata dump file for plug-in to the 12c source.

• Tablespace should be read only for this operation else it will fail.
• Tablespace can be marked read-write after the dumpfile is created

C:\...Desktop> expdp system/oracle@demodb11g dumpfile=tts.dmp transport_tablespaces=TS_DEMO
Export: Release 11.2.0.3.0 - Production on Mon Jan 27 15:48:06 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********@demodb11g dumpfile=tts.dmp transport_tablespaces=TS_DEMO
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  D:\ORACLE11G\11.2.0.3\RDBMS\LOG\TTS.DMP
******************************************************************************
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:48:35
Optionally change the tablespace back to read-write
 

Step 4 - Copy the backupset from #2 and the data pump file from #3 to the 12c destination.

Step 5 - At the destination create the users that own the tablespace objects (or remap to existing user later).

Step 6 - At 12c destination, start up RMAN, connect to the desired container and ensure its open read-write

Step 7 - Restore the backupset to the target using the RESTORE FROM PLATFORM command.

RESTORE FROM PLATFORM
 'Microsoft Windows x86 64-bit'
  All Foreign datafiles
   Format'/u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_%u.dbf'
    FROM BACKUPSET '/u01/app/oracle/oradata/tmp/BKUP_TS_DEMO.BCK';
RMAN> RESTORE FROM PLATFORM
 'Microsoft Windows x86 64-bit'
  All Foreign datafiles
   Format'/u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_%u.dbf'
    FROM BACKUPSET '/u01/app/oracle/oradata/tmp/BKUP_TS_DEMO.BCK';
2> 3> 4> 5>
Starting restore at 27-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: restoring foreign file 23 to /u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_i8ov4m25.dbf
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 27-JAN-14
RMAN> exit

Note the name of the file(s) RMAN restores the tablespaces to, for use in the data pump plug-in command. Also note that the command specified ALL FOREIGN DATAFILES.

To control restore location, the file name pattern specified was: '/u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_%u.dbf'

The above RMAN restored the tablespace to this location

/u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_i8ov4m25.dbf

Step 8 - Create data pump directory object - in the target container

• Connect to the desired container as system

• Create the directory. 

[oracle@laboms ~]$ sqlplus system/oracle@salespdb

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 23 14:55:09 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Last Successful login time: Thu Jan 23 2014 14:54:45 +08:00
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
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
-------------------------------------------------------------------------------
SALESPDB
SQL> create directory dpdir as '/u01/app/oracle/oradata/tmp';
Directory created.
SQL>

Step 9 - Plug in the tablespace using Data Pump's transportable tablespace mode.

Data pump parameters used: 

Username/password    system/oracle@salespdb – connect to PDB not root
Directory            Dpdir (maps to location where the metadata dump is stored)
Remap_schema         Demo_user:hr 
                     Demo_user’s tables will be mapped to destination HR user
Transport_datafiles  The rman-restord file
                             /u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_4fouq10i.dbf
Exclude    OBJECT_GRANT,TRIGGER (do not import grants/triggers)

$ impdp system/oracle@salespdb directory=dpdir dumpfile=TTS.DMP remap_schema=demo:hr transport_datafiles=/u01/app/oracle/oradata/CDB1/salespdb/ts_plugin_11g_i8ov4m25.dbf exclude=OBJECT_GRANT,TRIGGERImport: Release 12.1.0.1.0 - Production on Mon Jan 27 16:24:06 2014
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, Real Application Testing
and Unified Auditing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source TSTZ version is 14 and target TSTZ version is 18.
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@salespdb directory=dpdir dumpfile=TTS.DMP
remap_schema=demo_user:hr transport_datafiles=/u01/app/oracle/oradata/CDB1/salespdb
/ts_plugin_11g_i8ov4m25.dbf
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jan 27 16:24:31 2014 elapsed 0 00:00:22

Step 10 - Inspect the imported tablespace and its contents.

SQL> select TABLE_NAME, TABLESPACE_NAME from cdb_tables where owner='HR'
SQL> /

TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
MERCHANT             TS_DEMO
REWARDS              TS_DEMO
SETTLEMENT           TS_DEMO
TXN                  TS_DEMO
TXNDETAIL            TS_DEMO
TRIAL                TS_DEMO
STAGING              TS_DEMO

7 rows selected.