Tales From A Lazy Fat DBA

The legendary Transportable Tablespace feature was introduced in Oracle 8i to make it convenient to transport a large amount of data between databases. Specially from Oracle10g this useful feature was enhanced with cross-platform support which allowed a tablespace/tablespaces, to be transported between databases deployed on different hardware platforms or between platforms with a different endian formats.

So till 11g the migration activity involves RMAN, EXPDP and IMPDP have to be used to transport tablespace(s) across platforms along with the RMAN CONVERT statement was used. Below are the steps that are required to perform the migration work till 11g.

Step 1: Check Platform Support and File Conversion Requirement
Step 2: Identify Tablespaces to be Transported and Verify Self-containment
Step 3: Check for Problematic Data Types
Step 4: Check for Missing Schemas and Duplicate Tablespace and Object Names
Step 5: Make Tablespaces Read-only in Source Database
Step 6: Extract Metadata from Source Database (We could use either data pump or original export to do this)
Step 7: Copy Files to Target Server and Convert if Necessary (Conversion involves RMAN)
Step 8: Import Metadata into Target Database (This step is sometimes called “plugging in” the tablespaces. Again we can use data pump or original import).
Step 9: Copy Additional Objects to Target Database as Desired

With the introduction of Oracle Database 12c, it includes a very easy and novel way to do the same – That is the ability to transport tablespaces across platforms using just RMAN and RMAN (compressed) backupsets!

Let me show you how we can transport a tablespace from Oracle Linux to Oracle Solaris. Which is an example of a cross platform migration with different ENDIAN formats. Solaris is BIG endian whereas the Linux is a small ENDIAN type OS.

SQL> select banner from v$version;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
PL/SQL Release 12.1.0.2.0 – Production

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ————————————————– ————–
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little

SQL> SELECT tablespace_name, segment_type, COUNT(*),
2 SUM (bytes) / 1024 / 1024 mb
3 FROM dba_segments
4 WHERE owner = ‘DIXIT’
5 GROUP BY tablespace_name, segment_type
6 ORDER BY 1, 2 DESC;

TABLESPACE_NAME SEGMENT_TYPE COUNT(*) MB
————— ———— ———- ——-
IND1 INDEX 88 1353.4
TAB1 TABLE 41 4079.6
TAB1 LOBSEGMENT 3 0.4
TAB1 LOBINDEX 3 0.2
TAB1 INDEX 53 106.4

Make Tablespaces Read-only in Source Database
With today’s filers and sophisticated storage systems, it is often possible to take a filer “snapshot” or split a mirror in order to get a copy of the data files very quickly. Extracting metadata is also quick. So, on a system with a good storage system, tablespaces may only need to be read-only for a few minutes.
NOTE: In 12c we can use a procedure that keeps the downtime to a minimum with the ‘Incremental Cross-Platform Transportable Tablespaces’. It also uses RMAN transportable backupsets but is a slightly more complicated procedure.

We put the tablespaces into read-only mode with the following statements:

SQL> ALTER TABLESPACE tab1 READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE ind1 READ ONLY;
Tablespace altered.

In order to create a TTS backup we have two of the optins available to use either BACKUP FOR TRANSPORT or the BACKUP TO PLATFORM RMAN

Whats the difference between the two ?
Answer: The difference between these two arguments or statements in RMAN is where the datafile conversion will take place. The BACKUP FOR TRANSPORT statement should be used if the datafile conversion is to be performed on the target system and the BACKUP TO PLATFORM statement should be used if the datafile conversion is to be performed on the source system.

For the test purposes we will perform the conversion of datafiles on the source using the BACKUP TO PLATFORM statement specifying the name of the target platform as its argument.
We have to provide some additional information like Where RMAN should place the backupsets. RMAN will create one backupset with the datafile(s) and a second backupset with the metadata dumpset that EXPDP will create in lieu of RMAN. For this test i will create a compressed transportable backupset.

$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Sun Aug 21 10:49:57 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
connected to target database: tunedb (DBID=1989879787)

RMAN> backup to platform ‘Solaris[tm] OE (64-bit)’ as compressed backupset
2> tablespace xtransport format ‘/tmp/dbfilebackups.bck’
3> datapump format ‘/tmp/infometaexpdp.bck’;
Starting backup at 21-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces…
EXPDP> Starting “SYS”.”TRANSPORT_EXP_TUNEDB_Y7OJ”:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
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 “SYS”.”TRANSPORT_EXP_V121_pyAn” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TRANSPORT_EXP_TUNEDB_Y7OJ is:
EXPDP> /u01/app/oracle/product/12.1.0.2.0/db_1/dbs/backup_transporttbs_tunedb_181881.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace XTRANSPORT:
EXPDP> /u01/db/v121/data/V121/datafile/o1_mf_ttftest_dixit_.dbf
EXPDP> Job “SYS”.”TRANSPORT_EXP_TUNEDB_Y7OJ” successfully completed at Sun Aug 21 10:53:55 2016 elapsed 0 00:04:03
Export completed

channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/db/v121/data/V121/datafile/o1_mf_ttftest_dixit_.dbf
channel ORA_DISK_1: starting piece 1 at 21-AUG-16
channel ORA_DISK_1: finished piece 1 at 21-AUG-16
piece handle=/tmp/dbfilebackups.bck tag=TAG201698188T888 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed full datafile backup set
input Data Pump dump file=/u01/db/v121/data/V121/datafile/o1_mf_ttftest_dixit_.dbf
channel ORA_DISK_1: starting piece 1 at 21-AUG-16
channel ORA_DISK_1: finished piece 1 at 21-AUG-16
piece handle=/tmp/infometaexpdp.bck tag=TAG201678777U998 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 21-AUG-16

Recovery Manager complete.

So in short the RMAN has performed below mentioned activities:
– Verify and Identify Tablespaces to be Transported and Verify Self-containment
– Extract Metadata from Source Database using EXPDP.
– RMAN created a compressed backupset which contains the tablespace’s datafile.
– Created a backupset containing the metadata dump.

Now its time to restore the transportable backupset!!

$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Sun Aug 21 12:39:13 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
connected to target database: tunedb (DBID=1989879787)

RMAN> restore from platform ‘Solaris[tm] OE (64-bit)’
2> foreign tablespace IND1, TAB1 to new
3> from backupset ‘/tmp/dbfilebackups.bck’
4> dump file from backupset ‘/tmp/infometaexpdp.bck’;
Starting restore at 21-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 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 IND1, TAB1
channel ORA_DISK_1: reading from backup piece /tmp/dbfilebackups.bck
channel ORA_DISK_1: restoring foreign file 9 to /u01/db/tunedb/data/tunedb/datafile/o1_mf_testtransport_ab77hho11_.dbf
channel ORA_DISK_1: foreign piece handle=/tmp/dbfilebackups.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
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 /u01/app/oracle/product/12.1.0.2.0/db/dbs/o1_mf_ttftest_dixit_.dmp
channel ORA_DISK_1: reading from backup piece /tmp/infometaexpdp.bck
channel ORA_DISK_1: foreign piece handle=/tmp/infometaexpdp.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Performing import of metadata…
IMPDP> Master table “SYS”.”TRANSDIXIT_IMPORT_tunedb_g7aahu” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TRANSDIXIT_IMPORT_tunedb_g7aahu”:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
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 “SYS”.”TRANSDIXIT_IMPORT_tunedb_g7aahu” successfully completed at Sun Aug 21 12:42:10 2016 elapsed 0 00:02:03
Import completed

Finished restore at 21-AUG-16

Recovery Manager complete.

Deducing on the basis of RMAN restore logs, its clear that the RMAN completed following steps:
– It restored the foreign tablespace’s datafile from the datafile backupset.
– Along it restores the tablespace metadata from the metadata backupset.
– Import the tablespace metadata using IMPDP.

Hope That Helps
Prashant Dixit