Efficient data migration using the table space technology that can be transferred

Efficient data migration using the table space technology that can be transferred

This article demonstrates the Oracle tablespace migration process and many precautions.

Tutorial objective: to migrate the tablespace TBS_SEC_D on the oracle 10g database instance to the secooler database instance
OS: RedHat 5.3
Database: Oracle 10.2.0.3

[Experiment BEGIN]
[NOTE 1]: users in the target database must already exist before import.
[NOTE 2]: a tablespace with the same name cannot exist in the target database before import. For example, to migrate a tablespace with the same name, you must change the name of the tablespace in the source database or the database to be migrated.

1. Check whether the source database tablespace is "self-contained ".
1) log on to the database as a sys user
Sec @ ora10g> conn/as sysdba
Connected.

2) use dbms_tts.transport_set_check to check the migrated tablespace. The name of the tablespace to be migrated is TBS_SEC_D.
Sys @ ora10g> exec dbms_tts.transport_set_check ('tbs _ SEC_D ', true );

PL/SQL procedure successfully completed.

3) use the transport_set_violations view to check whether there is any content in violation of "self-contained". The result is not displayed here, so you can complete the migration of the TBS_SEC_D tablespace.
Sys @ ora10g> select * from transport_set_violations;

No rows selected

Briefly list the four possible situations of "non-self-contained" and their countermeasures:
-- Assume that the name of the tablespace to be migrated is TBS_SEC_D.
(1) [Index] There is an index on the tablespace TBS_SEC_D, but the base table of this index is in another tablespace (the experiment below will demonstrate violation of this constraint );
(2) The [LOB] Table is stored in the tablespace TBS_SEC_D, but the LOB field in the table is stored in other tablespaces;
(3) [constraint] some table constraints are on the tablespace TBS_SEC_D, but others are on the other tablespaces;
(4) [Partition Table] Some partitions of the partition table are in the tablespace TBS_SEC_D, but other partitions are in another tablespace.

If the preceding conditions are violated, it is not feasible to export the tablespace TBS_SEC_D separately. solution:
Solution 1: export related tablespace together
Solution 2: pre-process tablespaces that are not in the same table space to TBS_SEC_D. Then, you can export the tablespaces TBS_SEC_D.

2. Change the tablespace TBS_SEC_D to "read-only" ------ this step is critical.
Sys @ ora10g> alter tablespace TBS_SEC_D read only;

Tablespace altered.

3. Export tablespaces with SYSDBA Permissions
Ora10g @ testdb183/exp $ exp "'"/as sysdba "'" file = exp_TBS.dmp log = exp_TBS.log transport_tablespace = y tablespaces = TBS_SEC_D triggers = y constraints = n grants = n

Export: Release 10.2.0.3.0-Production on Tue Aug 25 19:54:22 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported
About to export transportable tablespace metadata...
For tablespace TBS_SEC_D...
. Exporting cluster definitions
. Exporting table definitions
.. Exporting table TEST
. Exporting triggers
. End transportable tablespace metadata export
Export terminated successfully without warnings.

OK. exported successfully.
The tablespace export is mainly because the parameter transport_tablespace = y is at work. Check the prompt. The exported exp_TBS.dmp file does not contain the object data and only contains the "metadata" of the tablespace ", the real data is still on the physical data file corresponding to the tablespace. Therefore, when using the tablespace transmission technology to complete the import, you need not only the exp_TBS.dmp export file, but also the data file corresponding to the tablespace.

4. Do not rush to restore the tablespace TBS_SEC_D to the "read/write" state. You must first send the exported exp_TBS.dmp file and the physical data file that comprise the tablespace to the secooler database server to be imported.
Note that data must be transmitted in binary (bin) mode.
I used to use the scp command to transfer data files.
It is best to place the data files in the directory where the data files in the target database are stored for unified management.

5. OK. After the transfer is complete, you can now restore the tablespace TBS_SEC_D to the "read/write" status.
Sys @ ora10g> alter tablespace TBS_SEC_D read write;

Tablespace altered.

6. Import the tablespace into the target database (secooler database instance)
Secooler @ dbserver/imp $ imp "'"/as sysdba "'" file = '/imp/exp_TBS.dmp' transport_tablespace = y datafiles = '/imp/delimiter' tablespaces = TBS_SEC_D tts_owners = sec fromuser = sec touser = sec

Import: Release 10.2.0.3.0-Production on Tue Aug 25 21:27:37 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT: V10.02.01 via conventional path
About to import transportable tablespace (s) metadata...
Import done in AL32UTF8 character set and UTF8 NCHAR character set
. Importing SYS's objects into SYS
. Importing SYS's objects into SYS
. Importing SEC's objects into SEC
.. Importing table "TEST"
. Importing SYS's objects into SYS
Import terminated successfully without warnings.
Secooler @ dbserver/imp $

7. log on to the sec user to query the database objects and verify that the data has been imported successfully.

8. Set the tablespace to read/write to complete the whole tablespace migration task.
Sec @ secooler> select TABLESPACE_NAME, STATUS from dba_tablespaces where TABLESPACE_NAME = 'tbs _ SEC_D ';

TABLESPACE_NAME STATUS
---------------------------------------
TBS_SEC_D READ ONLY

Sec @ secooler> alter tablespace SEC_D read write;

Tablespace altered.

Sec @ secooler> select TABLESPACE_NAME, STATUS from dba_tablespaces where TABLESPACE_NAME = 'tbs _ SEC_D ';

TABLESPACE_NAME STATUS
---------------------------------------
TBS_SEC_D ONLINE

[Experiment supplement ing]
[Simulate a process that violates the first principle of "self-contained]

Sec @ ora10g> create table t (x number) tablespace USERS;

Table created.

Sec @ ora10g> create index t_idx on t (x) tablespace TBS_SEC_D;

Index created.

Sec @ ora10g> conn/as sysdba
Connected.
Sys @ ora10g> exec dbms_tts.transport_set_check ('users', true );

PL/SQL procedure successfully completed.

Sys @ ora10g> select * from transport_set_violations;

No rows selected

Sys @ ora10g> exec dbms_tts.transport_set_check ('users', true );

PL/SQL procedure successfully completed.

Sys @ ora10g> select * from transport_set_violations;

No rows selected

Sys @ ora10g> exec dbms_tts.transport_set_check ('tbs _ SEC_D ', true );

PL/SQL procedure successfully completed.

Sys @ ora10g> select * from transport_set_violations;

VIOLATIONS
------------------------------------------------
Index SEC. T_IDX in tablespace TBS_SEC_D points to table SEC. T in tablespace USERS

Exporting the two tablespaces TBS_SEC_D and USERS at the same time will not cause the problem:
Ora10g @ testdb183/exp $ exp "'"/as sysdba "'" file = exp_TBS.dmp log = exp_TBS.log transport_tablespace = y tablespaces = bytes, USERS triggers = y constraints = n grants = n

Export: Release 10.2.0.3.0-Production on Tue Aug 25 19:40:09 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported
About to export transportable tablespace metadata...
For tablespace TBS_SEC_D...
. Exporting cluster definitions
. Exporting table definitions
.. Exporting table TEST
For tablespace USERS...
. Exporting cluster definitions
. Exporting table definitions
.. Exporting table T
. Exporting triggers
. End transportable tablespace metadata export
Export terminated successfully without warnings.

Exporting the USERS tablespace separately does not cause any problem:
Ora10g @ testdb183/exp $ exp "'"/as sysdba "'" file = exp_TBS.dmp log = exp_TBS.log transport_tablespace = y tablespaces = USERS triggers = y constraints = n grants = n

Export: Release 10.2.0.3.0-Production on Tue Aug 25 19:40:19 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported
About to export transportable tablespace metadata...
For tablespace USERS...
. Exporting cluster definitions
. Exporting table definitions
.. Exporting table T
. Exporting triggers
. End transportable tablespace metadata export
Export terminated successfully without warnings.

However, if you separate the TBS_SEC_D tablespace, an error is reported because it violates the following principle:
[Index] There is an index on the tablespace TBS_SEC_D, but the base table of this index is on another tablespace (the experiment below will demonstrate violation of this constraint)
Ora10g @ testdb183/exp $ exp "'"/as sysdba "'" file = exp_TBS.dmp log = exp_TBS.log transport_tablespace = y tablespaces = TBS_SEC_D triggers = y constraints = n grants = n

Export: Release 10.2.0.3.0-Production on Tue Aug 25 19:40:25 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29341 encountered
The ORA-29341: The transportable set is not self-contained
ORA-06512: at "SYS. DBMS_PLUGTS", line 1387
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

========================================================== ====================================
[Note] tablespace migration cannot be completed for different database character sets and country character sets! The following error is reported. Pay more attention.
Bomsdb1 @ testdb183/imp $ imp "'"/as sysdba "'" file = '/imp/exp_TBS.dmp' transport_tablespace = y datafiles = '/imp/delimiter' tablespaces = TBS_SEC_D tts_owners = sec fromuser = sec touser = sec

Import: Release 10.2.0.3.0-Production on Tue Aug 25 20:18:10 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT: V10.02.01 via conventional path
About to import transportable tablespace (s) metadata...
Import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Import server uses WE8ISO8859P1 character set (possible charset conversion)
Export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00017: following statement failed with ORACLE error 29345:
"BEGIN sys. dbms_plugts.beginImport ('10. 2.0.3.0 ', 873, '123', 13, 'linux 64-bi"
"T for AMD ', 113739801,); END ;"
IMP-00003: ORACLE error 29345 encountered
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
ORA-06512: at "SYS. DBMS_PLUGTS", line 2386
ORA-06512: at "SYS. DBMS_PLUGTS", line 1946
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

[Conclusion]
Table space migration technology can efficiently complete data migration tasks. The time used is basically the same as the time used to copy data files physically. However, there are some restrictions on the specific environment. Before the actual use, strict tests are required.

List the precautions for completing the tablespace migration. If they are incomplete, please add them.
[NOTE 1]: users in the target database must already exist before import.
[NOTE 2]: a tablespace with the same name cannot exist in the target database before import. For example, to migrate a tablespace with the same name, you must change the name of the tablespace in the source database or the database to be migrated.
[NOTE 3]: Set the tablespace to "read-only" before export"
[NOTE 4]: Table space migration must be completed with SYSDBA permissions.
[Note 5]: The tablespace needs to be "self-contained". If the table space does not meet the "self-contained" requirements, see the following:
(1) [Index] There is an index on the tablespace TBS_SEC_D, but the base table of this index is in another tablespace (the experiment below will demonstrate violation of this constraint );
(2) The [LOB] Table is stored in the tablespace TBS_SEC_D, but the LOB field in the table is stored in other tablespaces;
(3) [constraint] some table constraints are on the tablespace TBS_SEC_D, but others are on the other tablespaces;
(4) [Partition Table] Some partitions of the partition table are in the tablespace TBS_SEC_D, but other partitions are in another tablespace;