Simple Table space migration using RMAN

Confirm environment:

Source database:

System: RedHat Enterprise 5.4

Database Version: Oracle 10.2.0.1.0

IP: 10.37.100.100

Target database:

System: RedHat Enterprise 5.4

Database Version: ORACLE 10.2.0.3.0

IP: 10.37.100.101

Tablespace to be transmitted: TTS_TEST

 

Check the environment:

View the version support information on the source database:

SQL> select db. name, db. platform_name, tp. endian_format

2 from v $ transportable_platform tp, v $ database db

3 where tp. platform_name = db. platform_name

4/

 

NAME PLATFORM_NAME ENDIAN_FORMAT

-------------------------------------------

ORCL Linux IA (32-bit) Little

View the database version:

SQL> select version from v $ instance;

VERSION

-----------------

10.2.0.1.0

 

View the version information on the slave database:

SQL> select db. name, db. platform_name, tp. endian_format

2 from v $ transportable_platform tp, v $ database db

3 where tp. platform_name = db. platform_name

4/

 

NAME PLATFORM_NAME ENDIAN_FORMAT

-------------------------------------------

ORCL Linux IA (32-bit) Little

 

The source database must be backed up by the entire database and all archived logs.

 

RMAN> backup database plus archivelog format '/u01/rman_backup/all_database_bak _ % T _ % t. bak ';
 
 

RMAN> transport tablespace tts_test

2> tablespace destination '/u01/tts_dir/td'

3> auxiliary destination '/u01/tts_dir/ad ';

 

At this time, the data files, log files, transfer sets, and import scripts required to transmit the tablespace have been generated in the tablepsace desnation directory, and the temporary files in the auxilibary desnation path have been deleted.
 
[Oracle @ localhost td] $ cd/u01/tts_dir/td/

[Oracle @ localhost td] $ ls

Dmpfile. dmp explog. log impscrpt. SQL tts01.dbf tts02.dbf

 

[Oracle @ localhost td] $ cd/u01/tts_dir/ad/

[Oracle @ localhost ad] $ ls

TSPITR_ORCL_MWWE

[Oracle @ localhost ad] $ cd TSPITR_ORCL_MWWE/

[Oracle @ localhost TSPITR_ORCL_MWWE] $ ls

Datafile onlinelog

[Oracle @ localhost TSPITR_ORCL_MWWE] $ cd datafile/

[Oracle @ localhost datafile] $ ls

[Oracle @ localhost datafile] $ cd ../onlinelog/

[Oracle @ localhost onlinelog] $ ls

 

Copy all the files generated in the tablepsace desnation path to the target database:

 

[Oracle @ localhost ~] $ Scp 10.37.100.100:/u01/tts_dir/td/*/u01/tts_dir/

Oracle@10.37.100.100's password:

Dmpfile. dmp 100% 76KB 76.0KB/s

Explog. log 100% 1226 1.2KB/s

Impscrpt. SQL 100% 2175 2.1KB/s

Tts01.dbf 100% 20 MB 20.0 MB/s

Tts02.dbf 100% 20 MB 20.0 MB/s

 

SQL> @/u01/tts_dir/impscrpt. SQL

 

Directory created.

Directory created.

DECLARE

*

ERROR at line 1:

ORA-39002: invalid operation.

ORA-06512: at "SYS. DBMS_STREAMS_TABLESPACE_ADM", line 2006

ORA-06512: at line 20

Directory dropped.

Directory dropped.

The error is reported because the destination database's transfer set path is different from the source database. You can copy the transfer set to the same path, or modify the impscrpt. SQL path:

[Oracle @ localhost tts_dir] $ vi impscrpt. SQL

 

Modify the two paths to the path of the transport set in the target database:

Create directory streams $ DIROBJ $1 AS '/u01/tts_dir /';

Create directory streams $ DIROBJ $ dpdir as '/u01/tts_dir /';

 

Execute script import again:

 

SQL> @/u01/tts_dir/impscrpt. SQL

 

Directory created.

Directory created.

PL/SQL procedure successfully completed.

Directory dropped.

Directory dropped.

 

You can also directly use the inpdp command to import the table space. (You can use the remap_schma parameter to modify the table space owner)

 

[Oracle @ localhost tts_dir] $ impdp system/oracle dumpfile = dmpfile. dmp directory = tts_dir nologf
 
Ile = y transport_datafiles =/u01/tts_dir/tts01.dbf,/u01/tts_dir/tts02.dbf remap_schema = tts: xtt;
 
 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
 
With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
 
Starting "SYSTEM ". "SYS_IMPORT_TRANSPORTABLE_01": system/********* dumpfile = dmpfile. dmp directory = tts_dir nologfile = y transport_datafiles =/u01/tts_dir/tts01.dbf,/u01/tts_dir/tts02.dbf remap_schema = tts: xtt
 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:24:37

 

View the result:

SQL> conn xtt/oracle

Connected.

 

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

TEST TABLE

DG_TEST TABLE

STD_TABLE_1 TABLE

STD_TABLE TABLE