Transport Tablespace

Home Up

 

Transporting tablespaces with Smenu

1. Introduction

 

Transporting tablespace is a fast and convenient way to transfer tables between 2 databases without having the fuss of export/import. Smenu support this feature and make it easy. You can perform a transport tablespace either by menu or shortcuts. The Screen menu is in module 3 section 2 while the 3 shortcuts are 'ttbc', 'ttbe' , 'ttbi' which stands respectively for:
bullet 'ttbc' : transport tablespaces check
bullet 'ttbe' : transport tablespaces export
bullet 'ttbi' : transport tablespaces import


Here is the Screen menu, the transport tablespace are options 10,11,12.

 

   -------------------------------------------------------------
   Date           : 28/11-16:15         Host  : befsam18
   Oracle SID     : POLDEV              menu  : sm/3.2
   Last Selection :
   *************************************************************
   *                                                           *
   *                 Dataguard & Transport Tablespace          *
   *                                                           *
   *************************************************************


           Data Guard :
           -----------
             1  :  List main data guards parameters value
             2  :  Show applied archive logs
             3  :  Report log ship to standby (run on primary)
             4  :  Report Dataguard status

           Transport Tablespace:
           ---------------------
            10  :  Check if a tablespace is self contained
            11  :  Transport a set of tablespace : Create metadata
            12  :  Transport a set of tablespace : Import metadata

     e ) exit

2. Check if a set of tablespaces is transportable

 

In order to transport a tablespace, you need to put them in read only mode. The tablespaces must not have references to objects outside the set of tablespaces you want to transport. Oracle provide a function to test this feature and Smenu makes it easy to use it with smenu option sm/3.2.10 or the shortcuts: 'ttbc'.

When you call 'ttbc', smenu select on all available tablespaces and display them on a list for your convenience to pick. Remember that you can use 'fsi' to see which of your tablespaces are in read only mode.

In this example we select 3 read only tablespaces, READ1,READ2,READ3 with contains each 1 table TBL1, TBL2, TBL3. We intend to export them as a logical set of related tablespaces. So first, we need to check if this set is self contained. Smenu will call the package dbms_tts.transport_Set_check(TS_LIST=>'READ1','READ2,'READ3',incl_constraints=>TRUE) and then perform a select on transport_set_violations. If no rows are returned then this set of 3 tablespaces is self-contained, otherwise you need to drop the links outsides the tablespaces.
In red we put our command and responses. We call the script [ttbc], then build the list of tablespaces and finally perform the check ['t']

  


  [befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp>ttbc

   1) SYSTEM
   2) TOOLS
   3) TEMP
   4) USERS
   5) DATA
   6) INDX
   7) TEST
   8) UNDOTBS
   9) READ1
  10) READ2
  11) READ3

   Select tablespace to Check, "e" to abort, "c" to start process  ===> 9
  List -->   READ1

  Select tablespace to Check, "e" to abort, "c" to start process  ===> 10

  List -->   READ1  READ2

 Select tablespace to Check, "e" to abort, "c" to start process  ===> 11

  List -->   READ1  READ2  READ3

  Select tablespace to Check, "e" to abort, "c" to start process  ===> c        
  Checking tablespace set   READ1  READ2  READ3
  Checking for READ1,READ2,READ3

PL/SQL procedure successfully completed.

  Counting from transport_set_violations


  MACHINE befsam18          - ORACLE_SID : POLDEV
                                     Page:   1

  Date              -  Monday    28th November  2005  15:40:23
  Username          -  SYS
  Display Violation to transport tablespace READ1,READ2,READ3


  no rows selected

 

3. Tranport a set of tablespaces : Export metadata

 

 

There are to 2 ways to use a read-only tablespace.

  1. Copy the read-only tablespace datafiles toward another location and import the metadata that specifies new location for the datafiles.
  2. Mount the datafiles of the tablespace through NFS or samba on the network and import only the tablespace metadata ad hoc into another
    DB without moving the original datafiles.
What ever you choose, you need to export the metadata of the read only set of tablespaces.
To export metadata we will call sm/3.2.11 or 'ttbe'. Again we will export the metadata for the set of of 3
tablespaces READ1, READ2, READ3 :		 
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> ttbe

 Tablespace to export:


 1) SYSTEM
 2) TOOLS
 3) TEMP
 4) USERS
 5) DATA
 6) INDX
 7) TEST

 8) UNDOTBS
 9) READ1
10) READ2
11) READ3
 Select list of tablespace to Transport, "e" to abort, "t" to start process  ===> 9
List -->   READ1
 Select list of tablespace to Transport, "e" to abort, "t" to start process  ===> 10
List -->   READ1  READ2
 Select list of tablespace to Transport, "e" to abort, "t" to start process  ===> 11
List -->   READ1  READ2  READ3
 Select list of tablespace to Transport, "e" to abort, "t" to start process  ===> t


     DO you want To export   READ1  READ2  READ3 (y/n) ? y

MACHINE befsam18          - ORACLE_SID : POLDEV           Page:  1

Export: Release 9.2.0.4.0 - Production on Mon Nov 28 16:12:02 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace READ1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          TOTO1
For tablespace READ2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          TOTO2
For tablespace READ3 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          TOTO3
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
Metadata Export is : /export/TAR/oracle/scripts/smenu/tmp/ttbs_11281611.dmp
List of datafiles  : /export/TAR/oracle/scripts/smenu/tmp/ttbs_11281611.dat

 

At this stage, you have exported the metadata of the transported tablespace set. Smenu as created 2 files in the export directory with the same radical name 'ttbs_' plus a date and the extension '.dmp' for the dump and extension '.dat' for you to change path before you reload the meta in a new DB. The directory can be specified by you with the option -d or it is defaulted to $SBIN/tmp. The file name can be override with the option -f <myname>. So it is easy to override this default setting using 'ttbe -d $MYDIR -f MYFILE'. There is a help 'ttbe -h' for that.

The file 'ttbs_<mmddhhmi>.dat' contains information delimited with ':' and each field prefixed with its nature: 'file','tbs,'owner'. There informations are used at import time to determine the new path of datafiles and switch objects ownerships ('Fromuser', 'touser').

  


[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> cat ttbs_11281611.dat
file:/u02/oradata/POLDEV/read03.dbf:tbs:READ3:
file:/u02/oradata/POLDEV/read02.dbf:tbs:READ2:
file:/u02/oradata/POLDEV/read01.dbf:tbs:READ1:

owner:STAT:


If you copy the datafiles to another location, you need to edit and update this file with the new path for each datafile. Leave the rest untouched.

4. Transport a set of tablespaces : Import metadata

 

Now it is time to import our set of 3 tablespaces into a new DB. You need to be sure that the files referenced in your ttbs_<mmddhhmi>.dat are visible. Begin with setting your environment to the target ORACLE_SID. If you are attaching read only files that belong to a data guard, you need to put the data guard into read only for the duration of the attach, this is not a long operation.

We will use the third Smenu command, that is 'ttbi'.
In this example I have chosen to import in a new DB, the 3 datafiles from a data guard db. so the datafiles were not copied, only metat data are imported. Also the owner of my 3 tables already existed in the target DB so I did not have to created it or use Fromuser to User to switch ownership of the contents of the 3 tablespaces.

  


[befsam18:CUSTDEV]:/export/TAR/oracle/scripts/smenu/tmp> ttbi
     DO you want to transport into CUSTDB the tablespaces READ3','READ2','READ1' (y/n) ? y

Datafiles=/u12/oradata/POLDEV/read03.dbf','/u12/oradata/POLDEV/read02.dbf','/u12/oradata/POLDEV/read01.dbf

Import: Release 9.2.0.4.0 - Production on Mon Nov 28 14:40:57 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing STAT's objects into STAT
. . importing table                        "TOTO1"
. . importing table                        "TOTO2"
. . importing table                        "TOTO3"
Import terminated successfully without warnings.

Here are the datafiles of CUSTDEV
                                                                 Size                 Max  Next
Datafile/redo/control                                            (meg) Type  id   Av  Size (meg)   Tablespace
==============================================================================================================
                                                                  Control file
/u11/oradata/CUSTDEV/control01.ctl                                    Control file
/u12/oradata/CUSTDEV/control02.ctl                                    Control file
.
.
.
/u11/oradata/CUSTDEV/pdbdata01.dbf                                     1024 Dbf  id:6   y 1024  0        PDBDATA
/u12/oradata/CUSTDEV/pdbindex01.dbf                                      73 Dbf  id:3   y 32768 1        PDBINDEX
/u11/oradata/CUSTDEV/pdbonss_data01.dbf                                 600 Dbf  id:17  y 1024  100      PDBONSS
/u11/oradata/CUSTDEV/qcodata01.dbf                                      300 Dbf  id:24  y 2000  100      QCODATA
/u12/oradata/CUSTDEV/qcoindx.dbf                                        200 Dbf  id:25  y 500   10       QCOINDX
/u12/oradata/POLDEV/read01.dbf                                       10 Dbf  id:26  n 0     0        READ1
/u12/oradata/POLDEV/read02.dbf                                       10 Dbf  id:27  n 0     0        READ2
/u12/oradata/POLDEV/read03.dbf                                       10 Dbf  id:28  n 0     0        READ3
/u11/oradata/CUSTDEV/system01.dbf                                       441 Dbf  id:1   y 32768 1        SYSTEM
/u11/oradata/CUSTDEV/tools01.dbf                                         10 Dbf  id:4   y 100   0        TOOLS
/u12/oradata/CUSTDEV/undotbs01.dbf                                      280 Dbf  id:2   y 2000  5        UNDOTBS

and tablespaces :

[s05096:CUSTDEV]:/u12/oradata/CUSTDEV> frg


MACHINE befsam18 - ORACLE_SID : CUSTDEV                                  Page:   1

Date              -  Monday    28th November  2005  15:02:26
Username          -  SYS
FREE - Free space summary by Tablespace


Type 'frg b' or 'frg g' for results in bytes or Giga

                       Free     Largest       Total      Available    %
     Tablespace       Frags   Frag (Megs)     (Megs)       (Megs)    Used
-------------------- -------- ------------ ------------ ------------ ----
TEMP                        0         -1.0         50.0         -1.0  100
READ1                       1          9.9         10.0          9.9    1
READ2                       1          9.9         10.0          9.9    1
READ3                       1          9.9         10.0          9.9    1
TOOLS                       1          9.9         10.0          9.9    1
USERS                       3         16.9         25.0         20.8   17
EHR_MV                      1        285.9        300.0        285.9    5
SYSTEM                      2        123.8        441.0        123.9   72
CM_DATA                     1         46.1         50.0         46.1    8
ONADATA                     2        367.9        500.0        368.4   26
PDBDATA                    23        395.9      1,024.0        991.6    3
.
.
                     --------              ------------ ------------
sum                       104                   8,442.5      3,776.8


SQL> select count(1) from toto1 ;

  COUNT(1)
----------
         0

 

 

Last Update : 27 November 2005