上一遍讲了使用IMAGE COPY方式进行表空间传输,如果文件比较大,多个文件拷贝还不方便,能否使用RMAN备份进行统一管理,答案是肯定的,从oracle 12c 开始,ORACLE提供了rman备份集的方式实现表空间传输。
为了简化命令,下面还是以单个表空间TSP_TEST1为例:
目标:
使用RMAN备份集实现表空间传输
环境:
源端:LINUX 8.8 + ORACLE 19.20 ,表空间:TSP_TEST1
目标端:WIN11 + ORACLE 19.20
步骤如下:
1.检查自包含
SQL> connect / as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check('TSP_TEST1', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
如果为空,表明没有问题,否则,需要去处理。
2.设置为只读
alter tablespace tsp_test1 read only;
需要传输的表空间,在传输之前,必须设置为只读。
3.source 源端备份
[oracle@dbserver ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 5 10:10:58 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1656922614)
RMAN> backup to platform 'HP Tru64 UNIX'
2> format '/tmp/test.bck'
3> datapump format '/tmp/test_meta.bck'
4> tablespace tsp_test1
5> ;
Starting backup at 05-MAR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=392 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully
Performing export of metadata for specified tablespaces...
EXPDP> Starting "SYS"."TRANSPORT_EXP_ORCL_xojo":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Master table "SYS"."TRANSPORT_EXP_ORCL_xojo" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TRANSPORT_EXP_ORCL_xojo is:
EXPDP> /u01/app/oracle/product/19.16.0/db_1/dbs/backup_tts_ORCL_69280.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TSP_TEST1:
EXPDP> /u01/app/oracle/oradata/ORCL/tsp_test1.dbf
EXPDP> Job "SYS"."TRANSPORT_EXP_ORCL_xojo" successfully completed at Sun Mar 5 10:12:56 2023 elapsed 0 00:00:31
Export completed
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/tsp_test1.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAR-23
channel ORA_DISK_1: finished piece 1 at 05-MAR-23
piece handle=/tmp/test.bck tag=TAG20230305T101219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
input Data Pump dump file=/u01/app/oracle/product/19.16.0/db_1/dbs/backup_tts_ORCL_69280.dmp
channel ORA_DISK_1: starting piece 1 at 05-MAR-23
channel ORA_DISK_1: finished piece 1 at 05-MAR-23
piece handle=/tmp/test_meta.bck tag=TAG20230305T101219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAR-23
RMAN> exit
4.拷贝RMAN备份和DMP备份到目标机器
拷贝
/tmp/test.bck
/tmp/test_meta.bck
文件到目标机器
放在以下位置:
d:\oracle\oradata\test.bck
d:\oracle\oradata\test_meta.dmp
5.在目标端恢复
RMAN> restore foreign tablespace tsp_test1
2> format 'd:\oracle\oradata\orcl\tsp_test01.dbf'
3> from backupset 'd:\oracle\oradata\test.bck'
4> dump file from backupset 'd:\oracle\oradata\test_meta.dmp'
5> ;
从位于 05-3月 -23 的 restore 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=378 设备类型=DISK
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原外部表空间 TSP_TEST1 中的所有文件
通道 ORA_DISK_1: 正在读取备份片段 d:\oracle\oradata\test.bck
通道 ORA_DISK_1: 将外部文件 5 还原到 D:\ORACLE\ORADATA\ORCL\TSP_TEST01.DBF
通道 ORA_DISK_1: 外部片段句柄 = d:\oracle\oradata\test.bck
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:02
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在将数据泵转储文件还原到 D:\oracle\product\19.16.0\db_1/dbs/backup_tts_ORCL_24724.dmp
通道 ORA_DISK_1: 正在读取备份片段 d:\oracle\oradata\test_meta.dmp
通道 ORA_DISK_1: 外部片段句柄 = d:\oracle\oradata\test_meta.dmp
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:02
正在执行元数据导入...
IMPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_IMP_ORCL_CBbE"
IMPDP> 启动 "SYS"."TSPITR_IMP_ORCL_CBbE":
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> 作业 "SYS"."TSPITR_IMP_ORCL_CBbE" 已于 星期日 3月 5 10:23:34 2023 elapsed 0 00:00:23 成功完成
导入完毕
在 05-3月 -23 完成了 restore
6.目标验证
RMAN> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TSP_TEST1 READ ONLY
RMAN> alter tablespace tsp_test1 read write;
已处理语句
RMAN> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TSP_TEST1 ONLINE
RMAN> select * from test.emp;
EMPID
----------
100
RMAN> insert into test.emp values(200);
已处理语句
RMAN> commit;
已处理语句
RMAN> select * from test.emp;
EMPID
----------
100
200