Transported tablespaces in Oracle 11g
Hi habrovchane, in this article I will talk about the moved tablespaces (Transportable tablespaces) in Oracle 11g. The table space can be cloned and then inserted into another database by copying, and it can also be deleted from one Oracle database and included in another Oracle database on the same platform using a move.
Data migration using tablespace movement is an order of magnitude faster than with export / import or upload / download operations, since tablespace movement consists only of data file copy operations and tablespace metadata integration. Moving table spaces also allows you to move relevant indexes, so that after importing or loading tabular data, you do not need to rebuild the indexes.
Oracle Database allows you to copy data between databases, and share them with external files. Copying is done through export and import.
To accomplish this task there are utilities imp.exe and exp.exe
Promptly providing developers with a fresh copy of the production system data. Use of information published on non-rewritable media (CD-ROM, DVD, etc.). The rapid movement of data from the operating system to the storage or data mart.
Accelerating the transfer of information is achieved by replacing the resource-intensive export-import processes or unloading-loading by much faster copying of data files from one computing installation to another.
')
There are a number of conditions limiting the use of the described possibility.
You can move tablespaces only between databases that:
1. Have the same block size (db_block_size), created with the same encoding (character set), in the initialization files (INIT.ORA) of the source and target databases the COMPATIBLE parameter must be set to the value, work on compatible platforms of the same hardware manufacturer .
You can verify the requirements by running the following query in the source and target database:
The results of the queries in the source and target databases should be the same.
2. The target database should not have a table space with the same name as the one being connected.
3. Transportation is not supported: snapshots and replicable tables, functional indexes, local links to objects, domain indexes.
4. Choosing a self-contained set of table spaces
A self-sufficient set of table spaces is a collection of table spaces whose objects do not refer to any objects that are not contained in this set.
To verify self-sufficiency it is convenient to use the procedure TRANSPORT_SET_CHECK (to perform the procedure, the role EXECUTE_CATALOG_ROLE is required).
The results of her work are recorded in a temporary table and can be viewed through the system view SYS.TRANSPORT_SET_VIOLATIONS:
First, the table spaces should be transferred to the READ ONLY state (hereinafter referred to as XXX - the name of the table space):
Then, using the EXP utility, the dictionary metadata is exported.
In the command line you should change the encoding for correct display of information.
oracle will ask for your username and password:
Username: sys / system @ orcl2012 as sysdba
orcl2012 - connection string to the source database.
TRANSPORT_TABLESPACE = Y - indicates that the metadata of transported tablespaces is being exported, TABLESPACES = (USERS, USER_DATA, INDX) - sets the list of transportable tablespaces, TRIGGERS = Y - Export table triggers (if you specify N, then triggers will not be exported) Y - Export integrity constraints (when N, PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints are not exported, however NOT NULL constraints are exported), GRANTS = Y - Export table access privileges (N cancels privilege export), FILE = exp_tts and log = exp_tts file name and the data and export the log file name.
After the utility successfully exports the metadata, the output will be a file with the extension .dmp. (Exp_xxx.dmp)
After exporting the metadata, you can move the tablespaces back to the READ WRITE state:
Before importing, you need to transfer data from the source database to the target:
This can be done using ftp, command line or copying using Windows.
You need to copy the tablespace file (.dbf) from the source database to the target.
At the command prompt.
\\ server1 \ oradata \ orcl \ TS_XXX.dbf - path to the file of the table space being moved on the source database
\\ server2 \ oradata \ orcl \ - the path where the table space will be stored on the target database
Now you can connect a set of tablespaces to the target database.
oracle will ask for your username and password:
sys / system @ ora2015 as sysdba
ora2015 - the connection string to the target database.
TRANSPORT_TABLESPACE = Y - indicates that the metadata of the set of plug-in table spaces is imported, DATAFILES = (...) is a list that defines the names and location of the connected data files, TABLESPACES = (TS_XXX) is a list of table spaces. If the parameter is not specified, the list is taken from the export file. If specified, it is checked against the list contained in the export file (in case of a mismatch, an error message will be displayed). TTS_OWNERS = (XXXCORE) - the list of schemas that owns the objects in the connected table spaces. If the parameter is not specified, the list is taken from the export file. If specified, it is checked against the list contained in the export file (in case of a mismatch, an error message will be displayed). FROMUSER = (XXXCORE), TOUSER = (XXXCORE) - the names of the source and corresponding target schemes.
If the import is completed successfully, you can check the availability of data on the target database.
Used literature literature:
Data migration using tablespace movement is an order of magnitude faster than with export / import or upload / download operations, since tablespace movement consists only of data file copy operations and tablespace metadata integration. Moving table spaces also allows you to move relevant indexes, so that after importing or loading tabular data, you do not need to rebuild the indexes.
Work with export and import utilities
Oracle Database allows you to copy data between databases, and share them with external files. Copying is done through export and import.
To accomplish this task there are utilities imp.exe and exp.exe
Portable Tablespaces
Promptly providing developers with a fresh copy of the production system data. Use of information published on non-rewritable media (CD-ROM, DVD, etc.). The rapid movement of data from the operating system to the storage or data mart.
Accelerating the transfer of information is achieved by replacing the resource-intensive export-import processes or unloading-loading by much faster copying of data files from one computing installation to another.
')
Choosing a self-contained set of table spaces
There are a number of conditions limiting the use of the described possibility.
You can move tablespaces only between databases that:
1. Have the same block size (db_block_size), created with the same encoding (character set), in the initialization files (INIT.ORA) of the source and target databases the COMPATIBLE parameter must be set to the value, work on compatible platforms of the same hardware manufacturer .
You can verify the requirements by running the following query in the source and target database:
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 results of the queries in the source and target databases should be the same.
2. The target database should not have a table space with the same name as the one being connected.
3. Transportation is not supported: snapshots and replicable tables, functional indexes, local links to objects, domain indexes.
4. Choosing a self-contained set of table spaces
A self-sufficient set of table spaces is a collection of table spaces whose objects do not refer to any objects that are not contained in this set.
To verify self-sufficiency it is convenient to use the procedure TRANSPORT_SET_CHECK (to perform the procedure, the role EXECUTE_CATALOG_ROLE is required).
begin sys.dbms_tts.transport_set_check (ts_list => 'USER_DATA', incl_constraints => true); end; The results of her work are recorded in a temporary table and can be viewed through the system view SYS.TRANSPORT_SET_VIOLATIONS:
select * from sys.transport_set_violations Migrating a tablespace set
First, the table spaces should be transferred to the READ ONLY state (hereinafter referred to as XXX - the name of the table space):
ALTER TABLESPACE TS_XXX READ ONLY; Then, using the EXP utility, the dictionary metadata is exported.
In the command line you should change the encoding for correct display of information.
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 your username and password:
Username: sys / system @ orcl2012 as sysdba
orcl2012 - connection string to the source database.
TRANSPORT_TABLESPACE = Y - indicates that the metadata of transported tablespaces is being exported, TABLESPACES = (USERS, USER_DATA, INDX) - sets the list of transportable tablespaces, TRIGGERS = Y - Export table triggers (if you specify N, then triggers will not be exported) Y - Export integrity constraints (when N, PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints are not exported, however NOT NULL constraints are exported), GRANTS = Y - Export table access privileges (N cancels privilege export), FILE = exp_tts and log = exp_tts file name and the data and export the log file name.
After the utility successfully exports the metadata, the output will be a file with the extension .dmp. (Exp_xxx.dmp)
After exporting the metadata, you can move the tablespaces back to the READ WRITE state:
ALTER TABLESPACE TS_XXX READ WRITE; Connecting a set of tablespaces to the target database
Before importing, you need to transfer data from the source database to the target:
This can be done using ftp, command line or copying using Windows.
You need to copy the tablespace file (.dbf) from the source database to the target.
At the command prompt.
Copy \\server1\oradata\orcl\TS_XXX.dbf \\server2\oradata\orcl\ \\ server1 \ oradata \ orcl \ TS_XXX.dbf - path to the file of the table space being moved on the source database
\\ server2 \ oradata \ orcl \ - the path where the table space will be stored on the target database
Now you can connect a 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 your username and password:
sys / system @ ora2015 as sysdba
ora2015 - the connection string to the target database.
TRANSPORT_TABLESPACE = Y - indicates that the metadata of the set of plug-in table spaces is imported, DATAFILES = (...) is a list that defines the names and location of the connected data files, TABLESPACES = (TS_XXX) is a list of table spaces. If the parameter is not specified, the list is taken from the export file. If specified, it is checked against the list contained in the export file (in case of a mismatch, an error message will be displayed). TTS_OWNERS = (XXXCORE) - the list of schemas that owns the objects in the connected table spaces. If the parameter is not specified, the list is taken from the export file. If specified, it is checked against the list contained in the export file (in case of a mismatch, an error message will be displayed). FROMUSER = (XXXCORE), TOUSER = (XXXCORE) - the names of the source and corresponding target schemes.
If the import is completed successfully, you can check the availability of data on the target database.
Used literature literature:
- Tidwell D. XSLT / D. Tidwell - St. Petersburg: Symbol Plus, 2010. - 960 p.
- www.emanual.ru
- docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm
Source: https://habr.com/ru/post/148535/
All Articles