Transportable Tablespace with ASM

Posted on Posted in Oracle DB

In this post I’ll explain how managed the Transportable Tablespace Oracle utility, basically the method consist in copy one or more tablespace from a source database and plug it in to another Oracle database.

Basically we have two method to realize this objective, one putting the source tablespace in READ ONLY and export the Schema Metadata and the corresponding datafiles. This meaning that in the source schema any change not are allowed (possible lost of service, if the database is in Production).

To resolved this issue the RMAN procedure is the solution, this process create a Temporary Clone Database eliminates the need to put the tablespace in read-only mode. Using RMAN allow you to specify a target point in time, SCN or restore point during your recovery window.

Previous Checks

Be sure that aren’t any XML data in the source export schema.

SQL> select distinct p.tablespace_name from dba_tablespaces p, 
 dba_xml_tables x, dba_users u, all_all_tables t where
 t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
 and x.owner=u.username

Ok, now check if some limitation don’t allow to export the source schema

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('DATA', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Common TTS process

Export process

Tablespace ReadOnly

Logon in you source database and put the tablespace/s in read only mode.

SQL> ALTER TABLESPACE DATA READ ONLY;

Export Metadata

Now export the source metadata schema

expdp system/**** dumpfile=data.dmp directory=MY_DIR transport_tablespaces=DATA logfile=export_data_tts.log

Export: Release 11.2.0.3.0 - Production on S?b Jul 21 20:37:59 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=data.dmp directory=MY_DIR transport_tablespaces=DATA logfile=export_data_tts.log
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/PLUGTS_BLK
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TABLE
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/INDEX/INDEX
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TRIGGER
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
La tabla maestra "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" se ha cargado/descargado correctamente
******************************************************************************
El juego de archivos de volcado para SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 es:
  /src/data/data.dmp
******************************************************************************
Archivos de datos necesarios para TRANSPORT TABLESPACEable DATA:
 +DATA/MYDB/datafile/data.366.773260595 
El trabajo "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" ha terminado correctamente en 20:41:39

Optional you can run the expdp utility with TRANSPORT_FULL_CHECK=Y to be sure that there are no dependencies between the objects inside the transportable set and objects outside the transportable set.

Export Datafile/s

Select destination platform

Select the destination platform format for your datafile.

SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;

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

16 rows selected.

To be sure what format use, run this sql statement in your destination database:

SQL> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30) PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; 2 3

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
13 Linux x86 64-bit Little
Rman datafile export

Convert and extract the source datafile to a temporary destination.

RMAN> convert tablespace DATA TO PLATFORM 'Linux x86 64-bit' FORMAT '/src/data/%U';
convert tablespace DATA FORMAT '/src/data/%U';

Starting conversion at source at 21/07/2012 21:07:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=338 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00109 name=+DATA/MYDB/datafile/data.366.773260595
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of conversion at source command on ORA_DISK_1 channel at 07/21/2012 21:07:33
ORA-19699: can not make copies with compression enabled

RMAN not permit convert a datafile if compression is enabled, disable temporary the compression.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name MYDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Disable the compression

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2

Try again

RMAN> convert tablespace DATA TO PLATFORM 'Linux x86 64-bit' FORMAT '/src/data/%U';

Starting conversion at source at 21/07/2012 21:13:29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=338 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00109 name=+DATA/MYDB/datafile/data.366.773260595
converted datafile=/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
Finished conversion at source at 21/07/2012 21:14:24

Starting Control File and SPFILE Autobackup at 21/07/2012 21:14:24
piece handle=+FRA/MYDB/autobackup/2012_07_21/s_789254064.960.789254065 comment=NONE
Finished Control File and SPFILE Autobackup at 21/07/2012 21:14:25

Activate the compression

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2

Tablespace WRITE mode

Finally put the source tablespace in read/write mode

SQL> ALTER TABLESPACE DATA READ WRITE;

Import process

Metadata Import

Create user

Create in the destination DB the user/s that are implicate in the TTS process.

SQL> create user user_data identified by somepassword;

User created
Create MY_TTS

Create the datapump directory

SQL> create or replace directory MY_TTS as '/src/data/';

directory created

Import the metadata objects.

impdp system/****** dumpfile=data.dmp directory=MY_TTS transport_datafiles='/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp' logfile=impdp_data_tts.log

Import: Release 11.2.0.3.0 - Production on Dom Jul 22 12:34:03 2012

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
La tabla maestra "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" se ha cargado/descargado correctamente
Iniciando "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=data.dmp directory=MY_TTS transport_datafiles='/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp'
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/PLUGTS_BLK
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TABLE
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/INDEX/INDEX
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TRIGGER
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Procesando el tipo de objeto TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
El trabajo "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" ha terminado correctamente en 12:37:55

Optinally you can remap the schema:

IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
   TRANSPORT_DATAFILES=
   /salesdb/sales_101.dbf,
   /salesdb/sales_201.dbf
   REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)

Import Datafile

Import the datafile to the ASM

RMAN> backup as copy datafile '/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp' format '+DATA';
Starting backup at 22/07/2012 12:39:00
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: starting datafile copy
input datafile file number=00091 name=/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp
output file name=+DATA/MYDB/datafile/data.366.773260595 tag=TAG20120722T123901 RECID=337 STAMP=789309553
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 22/07/2012 12:39:16

If the database is open you need to offline the datafile first

SQL> alter database datafile '/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp' offline;
Database altered.

Switch the datafile to copy:

RMAN> switch datafile '/src/data/data_D-MYDB_I-624066827_TS-DATA_FNO-109_n2ngm3rp' to copy;
using target database control file instead of recovery catalog
datafile 91 switched to datafile copy "+DATA/MYDB/datafile/data.366.773260595"

Recover the datafile:

RMAN> recover datafile '+DATA/MYDB/datafile/data.366.773260595';
Starting recover at 22/07/2012 12:41:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 22/07/2012 12:41:14

Put the datafile online:

SQL> alter database datafile '+DATA/MYDB/datafile/data.366.773260595' online;

Database altered.

User default Tablespace

Alter the user and put DATA like the default tablespace

SQL> ALTER USER USER_DATA
DEFAULT TABLESPACE DATA
TEMPORARY TABLESPACE TST;
  User altered

Ok, the process are completed.

RMAN TTS Process

With this method you don’t need put the source tablespace in read-only mode.

Export RMAN TRANSPORT TABLESPACE

RMAN> TRANSPORT TABLESPACE DATA
 TABLESPACE DESTINATION '/src/data'
 AUXILIARY DESTINATION '/src/aux'
 DATAPUMP DIRECTORY MY_DIR;

This process create some files, the datapump metadata export, the datafile/s file/s, and SQL import script.

Optionally, you can restore the datafile from a specific point in time

TRANSPORT TABLESPACE 'DATA1','DATA2'
AUXILIARY DESTINATION '/src/aux'
TABLESPACE DESTINATION '/src/data'
DATAPUMP DIRECTORY MY_DIR
UNTIL TIME="to_date('29-03-2012:16:57:00', 'dd-mm-yyyy:hh24:mi:ss')";
TRANSPORT TABLESPACE 'DATA1','DATA2'
AUXILIARY DESTINATION '/src/aux'
TABLESPACE DESTINATION '/src/data'
DATAPUMP DIRECTORY MY_DIR
UNTIL TIME 'SYSDATE-15';

O until System Change Number (SCN)

TRANSPORT TABLESPACE 'DATA1','DATA2'
AUXILIARY DESTINATION '/src/aux'
TABLESPACE DESTINATION '/src/data'
DATAPUMP DIRECTORY MY_DIR
UNTIL SCN 13258;

O before restore point

TRANSPORT TABLESPACE 'DATA1','DATA2'
AUXILIARY DESTINATION '/src/aux'
TABLESPACE DESTINATION '/src/data'
DATAPUMP DIRECTORY MY_DIR
UNTIL RESTORE POINT 'before_restore_point';

Import

Create User

CREATE USER USER_DATA 
 IDENTIFIED BY ****** 
 PROFILE DEFAULT 
 ACCOUNT UNLOCK;
 GRANT CREATE SESSION TO USER_DATA;

Import script

Run the import script to import the metadata object.

sqlplus / as sysdba @impscrpt.sql

IMPDP method

If this script doesn’t works, import the metadata with the impdp utility.

impdp system/****** dumpfile=data.dmp directory=MY_TTS transport_datafiles='/src/data/o1_mf_data_80hmdg3c_.dbf' logfile=impdp_data_tts.log

Import Datafile

Import the datafile to the ASM

RMAN> backup as copy datafile '/src/data/o1_mf_data_80hmdg3c_.dbf' format '+DATA';
Starting backup at 22/07/2012 12:39:00
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: starting datafile copy
input datafile file number=00091 name=/src/data/o1_mf_data_80hmdg3c_.dbf
output file name=+DATA/MYDB/datafile/data.366.773260595 tag=TAG20120722T123901 RECID=337 STAMP=789309553
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 22/07/2012 12:39:16

If the database is open you need to offline the datafile first

SQL> alter database datafile '/src/data/o1_mf_data_80hmdg3c_.dbf' offline;
Database altered.

Switch the datafile to copy:

RMAN> switch datafile '/src/data/o1_mf_data_80hmdg3c_.dbf' to copy;
using target database control file instead of recovery catalog
datafile 91 switched to datafile copy "+DATA/MYDB/datafile/data.366.773260595"

Recover the datafile

RMAN> recover datafile '+DATA/MYDB/datafile/data.366.773260595';
Starting recover at 22/07/2012 12:41:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 22/07/2012 12:41:14

Put the datafile online:

SQL> alter database datafile '+DATA/MYDB/datafile/data.366.773260595' online;

Database altered.

User default Tablespace

Alter the user and put DATA like the default tablespace

SQL> ALTER USER USER_DATA
DEFAULT TABLESPACE DATA
TEMPORARY TABLESPACE TST;
  User altered

Leave a Reply

Your email address will not be published. Required fields are marked *