Summary of Oracle transmission tablespace migration data

Summary of Oracle tablespace migration data transmission Note: you must first create the corresponding user before the tablespace migration, otherwise the migration will fail. Sometimes, we need

Summary of Oracle tablespace migration data transmission Note: you must first create the corresponding user before the tablespace migration, otherwise the migration will fail. Sometimes, we need

Summary of Oracle transmission tablespace migration data

Note: users must be created before the tablespace is migrated. Otherwise, the migration will fail.

Sometimes, we need to migrate relatively large data across platforms (10 Gb supports cross-platform), using EXP/IMP and other methods is very slow, you can achieve fast and secure through the transmission of tablespaces. This operation must be performed with SYSDBA permissions. The specific steps are as follows:

1. Check whether the tablespace to be migrated is self-contained (that is, whether the basic conditions for the tablespace to be migrated are met)
Exec sys. dbms_tts.transport_set_check ('tablespace _ name', true );
Select * from sys. transport_set_violations;
If no record is returned, it indicates that it meets the conditions for the tablespace to be transmitted. If a record is returned, it does not.

2. Set the tablespace to be transferred to read-only
Alter tablespace tablespace_name read only;

3. Use exp tool to export metadata of the tablespace to be transmitted (metadata)
Exp userid = \ 'sys/lclsys2008 as sysdba \ 'file =/opt/test. dmp log =/opt/test. log transport_tablespace = y tablespaces = tablespace_name
Note: When SYSDBA is used, escape characters must be used. In LINUX, use \ '. In WINDOWS, use single quotes.

4. Use RMAN to convert the data file header of the tablespace to be transmitted as the target system file
Log on to RMAN:Rman target/
Rman>Convert tablespace "TABLESPACE_NAME" to platform 'linux IA (32-bit) 'format 'd: \ xxx. dbf'
Note: TABLESPACE_NAME is the name of the tablespace to be transmitted. Double quotation marks and uppercase letters are required. Linux IA (32-bit) is the name of the target platform, you can use select platform_name form v $ database to query the target platform database.

5. Copy the converted data files in the tablespace and export the files to the target platform.

6. Use IMP tool to load database files to the Target Platform

Imp userid = \ 'sys/ad as sysdba \ 'file = expdat. dmp transport_tablespace = y datafiles = ('d: \ xx. dbf') tablespaces = tablespace_name
Note: Try to use the same version when using IMP and EXP to avoid operation failure.

In addition, the piner book mentioned that metadata such as seq, function, proc, and view has not been migrated, and another migration is required.
Execute exp... rows = n
Import imp again.