Use the ability to transmit tablespaces to copy data

Restrictions on table space that can be transferred: The table space technology cannot be applied to system tablespaces or objects owned by sys users. In the transmission tablespace, The tablespace must be self-contained. The self-contained table space indicates that the internal tablespace set used for transmission does not reference the external tablespace set.

Portable tablespace lab environment

	OS	IP	SID	Storage Method	Tablespace	User	Table
Source database	RedHat5.4	10.10.10.8	Orcl	File System	Tb1, tb2	U1, u2	T1, t2
Target Database	Redhat5.4	10.10.10.7	Orcl	ASM	--	U1, u2	--

Note: In the source database, user u1 uses the tb1 tablespace, which contains a table t1. Use exp/imp for transmission. On the source database, user u2 uses the tb2 tablespace, which contains a table t2. Use expdp/impdp for transmission.

The following is a script for creating a test environment (operations performed on the source database)

1. USERIs"SYS"
2. SQL>CreateTablespace tb1 datafile'/U01/app/Oracle/oradata/orcl/tb1.dbf' Size10 m;
4. The tablespace has been created.
6. SQL>CreateTablespace tb2 datafile'/U01/app/oracle/oradata/orcl/tb2.dbf' Size10 m;
8. The tablespace has been created.
10. SQL>Create UserU1 identifiedByU1DefaultTablespace tb1;
12. The user has been created.
14. SQL>Create UserU2 identifiedByU2DefaultTablespace tb2;
16. The user has been created.
18. SQL>Grant Connect, ResourceToU1, u2;
20. Authorization successful.
22. SQL>Create TableU1.t1As Select*FromDba_objectsWhereRownum: <100;
24. The table has been created.
26. SQL>Create TableU2.t2As Select*FromDba_objectsWhereRownum: <200;
28. The table has been created.
30. SQL>Select Count(*)FromU1.t1;
32. COUNT(*)
33. ----------
34. 99
36. SQL>Select Count(*)FromU2.t2;
38. COUNT(*)
39. ----------
40. 199

Practice of table space Transfer

1. Check whether the platform for the tablespace to be transmitted is compatible (both the source database and the target database are the same, because they are all redhat5.4 operating systems)

1. SQL>SelectTp .*FromV $ transportable_platform tp, v $DatabaseDWhereTp. PLATFORM_NAME = d. PLATFORM_NAME;
3. PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
4. ------------------------------------------------------------
5. 10 Linux IA (32-Bit) Little

2. Check whether the transmitted tablespace is self-contained (operations performed on the source database)

1. SQL> showUser
2. USERIs"SYS"
3. SQL>ExecDbms_tts.transport_set_check ('Tb1',True);-- Only check Constraints
5. The PL/SQL process is successfully completed.
7. SQL>ExecDbms_tts.transport_set_check ('Tb2',True);-- Only check Constraints
9. The PL/SQL process is successfully completed.
11. SQL>ExecDbms_tts.transport_set_check ('Tb1',True,True);-- Strict check
13. The PL/SQL process is successfully completed.
15. SQL>ExecDbms_tts.transport_set_check ('Tb2',True,True);-- Strict check
17. The PL/SQL process is successfully completed.
19. SQL>Select*FromTransport_set_violations;-- A record does not meet the requirements
21. Unselected row

Note: The strict method is not only to check whether the objects referenced by the spatial set (such as tables) are self-contained, but also to check the objects referenced by other tablespaces (such as indexes ), whether the referenced table is in the tablespace set. If the tablespace to be transmitted is not self-contained, multiple tablespaces can be transmitted together.

3. Set the tablespace to be transferred as read-only (operations performed on the source database)

1. SQL> showUser
2. USERIs"SYS"
3. SQL>AlterTablespace tb1Read Only;
5. The tablespace has been changed.
7. SQL>AlterTablespace tb2Read Only;
9. The tablespace has been changed.

4. Export table space set metadata (operations performed on the source database)

1. SQL> showUser
2. USERIs"SYS"
3. SQL>Select*FromDba_directoriesWhereDIRECTORY_NAME ='Exp';
5. OWNER DIRECTORY_NAME DIRECTORY_PATH
6. --------------------------------------------------------------------------------------------------------------
7. Sys exp/u01/exp
9. SQL>!
10. [Oracle @ linux ~] $ Cd/u01/exp/
11. [Oracle @ linux exp] $ ll
12. Total 0
13. [Oracle @ linux exp] $ exp \"Sys/oracle @ orcl as sysdba \"Tablespaces = tb1 transport_tablespace = y file = tb1.dmp
14. [Oracle @ linux exp] $ expdp system/oracle @ orcl directory = exp dumpfile = tb2.dmp transport_tablespaces = tb2 nologfile = y
15. [Oracle @ linux exp] $ ll
16. Total 96
17. -Rw-r-- R -- 1 oracle oinstall 16384 10-06 tb1.dmp
18. -Rw-r----- 1 oracle oinstall 77824 10-06 tb2.dmp

5. Convert the byte sequence. If the byte sequence of the two platforms is different, you need a conversion process in the middle. You can use rman. (Optional, not executed here .)

1. RMAN>ConvertTablespace booksToPlatform'Microsoft Windows IA (32-bit )'Format'/Tmp/% N _ % F';
2. RMAN>ConvertDatafile'/Tmp/BOOKS_5'Db_file_name_convert'/Tmp/BOOKS_5','/Tmp/books01.dbf';

Note: This is the purpose of the first check, although conversion is not required here. Only the conversion method is provided here.

6. Copy metadata and data files to the target database (operations performed on the target database)

1. [Root @ linux u01] # chown-R oracle: oinstall tb/
2. [Root @ linux u01] # cd tb/
3. [Root @ linux tb] # ll
4. Total 20624
5. -Rw-r-- R -- 1 oracle oinstall 10493952 10-07 tb1.dbf
6. -Rw-r-- R -- 1 oracle oinstall 16384 10-07 tb1.dmp
7. -Rw-r-- R -- 1 oracle oinstall 10493952 10-07 tb2.dbf
8. -Rw-r-- R -- 1 oracle oinstall 77824 10-07 tb2.dmp
9. [Root @ linux tb] # pwd
10. /U01/tb

7. Modify the tablespace of the source database to read/write (the operations performed on the source database)

1. SQL> showUser
2. USERIs"SYS"
3. SQL>AlterTablespace tb1ReadWrite;
5. The tablespace has been changed.
7. SQL>AlterTablespace tb2ReadWrite;
9. The tablespace has been changed.

8. Check whether the size of the source tablespace is the same as that of the target database.

Source database

1. SQL>SelectBlock_sizeFromDba_tablespacesWhereTablespace_name ='Tb1';
3. BLOCK_SIZE
4. ----------
5. 8192
7. SQL>SelectBlock_sizeFromDba_tablespacesWhereTablespace_name ='Tb2';
9. BLOCK_SIZE
10. ----------
11. 8192
13. SQL> show parameter db_block_size
15. NAMETYPE VALUE
16. -----------------------------------------------------------------------------
17. Db_block_sizeInteger8192

Target Database

1. SQL> show parameter db_block_size
3. NAMETYPE VALUE
4. -----------------------------------------------------------------------------
5. Db_block_sizeInteger8192

1 2 Next Page