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