Transportable tablespaces in Oracle 11g

Original:
https://habr.com/ru/post/148535/

Marley: I have never used this feature. I always use dpdump and dpimp utilities. I haven’t verified the correctness.
The imp and exp utilities only make sense to use if you are working with version 9 of the database.



A tablespace can be cloned and then included in another database by copying, or it can be excluded from one Oracle database and included in another Oracle database on the same platform by moving.

Data transfer using tablespace transport is an order of magnitude faster than export/import or unload/load operations, since moving tablespaces consists only of copying data files and integrating tablespace metadata. Moving tablespaces also allows you to move the corresponding indexes, so after importing or loading table data, you do not need to rebuild indexes.
 

Working with export and import utilities


Oracle Database allows you to copy data between databases, as well as exchange it with external files. Copying is done through export and import.
For this task, there are imp.exe and exp.exe utilities

Transportable tablespaces


On-demand provisioning of developers with a fresh copy of production data. Using information published on non-rewritable media (CD-ROM, DVD, etc.). Quickly moving data from an OLTP system to a warehouse or data mart.
The acceleration of information transfer is achieved by replacing resource-intensive export-import or unload-load processes with much faster copying of data files from one computing facility to another.

Selecting a self-contained set of tablespaces


There are a number of conditions limiting the application of this feature.
Tablespaces can only be moved between databases that:

  1. Have the same block size (db_block_size), are created with the same character set, the COMPATIBLE parameter in the INIT.ORA files of the source and target databases must be set to a value, and they run on compatible platforms from the same hardware vendor.
    Compliance can be checked by running the following query in the source and target databases: 
select name, value from v$parameter where name in ('db_block_size','compatible')
union all
selct parameter, value from nls_database_parameters where parameter like '%characterset'


The query results in the source and target databases must be the same.

  1. The target database must not have a tablespace with the same name as the one being attached.

  2. Transport of the following is not supported: snapshots and replicated tables, function-based indexes, local object references, domain indexes.

  3. Selecting a self-contained set of tablespaces
    A self-contained set of tablespaces is a collection of tablespaces whose objects do not reference any objects not contained in the set.
    To check self-containment, it is convenient to use the TRANSPORT_SET_CHECK procedure (the EXECUTE_CATALOG_ROLE role is required to execute the procedure). 
begin
sys.dbms_tts.transport_set_check (ts_list => 'USER_DATA', incl_constraints => true);
end;


The results of its work are written to a temporary table and can be viewed through the system view SYS.TRANSPORT_SET_VIOLATIONS:

select * from sys.transport_set_violations


Transporting a set of tablespaces


First, put the tablespaces into READ ONLY state (hereinafter XXX is the tablespace name): 

ALTER TABLESPACE TS_XXX READ ONLY;


Then using the EXP utility, export the dictionary metadata.
In the command line, change the encoding for correct information display.

set nls_lang=russian_cis.ru8pc866



exp transport_tablespace=y tablespaces=(TS_XXX) triggers=y constraints=y grants=y file=d:\exp_xxx log=d:\exp_xxx


Oracle will ask for username and password:
Username: sys/system@orcl2012 as sysdba
orcl2012 – connection string to the source database.
TRANSPORT_TABLESPACE=Y — indicates that metadata export of transportable tablespaces is being performed, TABLESPACES=(USERS, USER_DATA, INDX) — specifies the list of transportable tablespaces, TRIGGERS=Y – Export table triggers (if N, triggers will not be exported) CONSTRAINTS=Y – Export integrity constraints (if N, PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints are not exported, but NOT NULL constraints are exported), GRANTS=Y – Export table access privileges (N cancels privilege export), FILE=exp_tts and log=exp_tts export data file name and export log file name.
After the utility successfully exports the metadata, the output will be a file with the .dmp extension (exp_xxx.dmp)
After exporting the metadata, you can put the tablespaces back into READ WRITE state:

ALTER TABLESPACE TS_XXX READ WRITE;


Attaching the set of tablespaces to the target database


Before importing, you need to transfer the data from the source database to the target:
This can be done via ftp, command line, or Windows copy.
You need to copy the tablespace file (.dbf) from the source database to the target.
In the command line.

Copy \\server1\oradata\orcl\TS_XXX.dbf  \\server2\oradata\orcl\


\server1\oradata\orcl\TS_XXX.dbf — path to the transportable tablespace file on the source database
\server2\oradata\orcl\ — path where the transportable tablespace will be stored on the target database
Now you can attach the set of tablespaces to the target database

imp transport_tablespace=y DATAFILES=('E:\server2\oradata\orcl\TS_XXX.dbf') TABLESPACES=(TS_XXX) TTS_OWNERS=(XXXCORE) fromuser=(XXXCORE) touser=(XXXCORE) FILE=D:\ exp_xxx.dmp LOG=D:\imp_xxx.log


Oracle will ask for username and password:
sys/system@ora2015 as sysdba
ora2015 – connection string to the target database.
TRANSPORT_TABLESPACE=Y – indicates that metadata of the set of transportable tablespaces is being imported, DATAFILES=(…) – list defining the names and location of the data files being attached, TABLESPACES=(TS_XXX) – list of tablespaces. If the parameter is not set, the list is taken from the export file. If set, it is checked against the list in the export file (if mismatch, an error message will be issued). TTS_OWNERS=(XXXCORE) – list of schemas that own objects in the transportable tablespaces. If the parameter is not set, the list is taken from the export file. If set, it is checked against the list in the export file (if mismatch, an error message will be issued). FROMUSER=(XXXCORE), TOUSER=(XXXCORE) – names of the source and corresponding target schemas.
If the import is completed successfully, you can check the data in the target database.