文件系统与ASM的左右迁移
文件系统与ASM的左右迁移
ASM迁移到文件系统
现我们接到如下需求：
将 ASM 共享盘中 OPTMTBS 表空间数据文件迁移到文件系统中。
asm 迁移到文件系统上共有三种解决方案。
SQL> select a.name, b.name from v$tablespace a, v$datafile b where a.ts# = b.ts#;
NAME NAME
-------------------------------------------------- --------------------------------------------------
SYSTEM +DATA/szdb/datafile/system.259.1089334417
SYSAUX +DATA/szdb/datafile/sysaux.260.1089334417
UNDOTBS1 +DATA/szdb/datafile/undotbs1.261.1089334419
UNDOTBS2 +DATA/szdb/datafile/undotbs2.263.1089334425
USERS +DATA/szdb/datafile/users.264.1089334425
OPTMTBS +DATA/szdb/datafile/optmtbs01.dbf
rman
# 创建数据文件存放路径
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/oradata/szdb
# 将表空间离线
RMAN> sql 'alter tablespace OPTMTBS offline';
# 拷贝数据文件到文件系统中
RMAN> copy datafile '+DATA/szdb/datafile/optmtbs.269.1091028875' to '/u01/app/oracle/oradata/szdb/optmtbs.269.1091028875';
Starting backup at 2021-12-11 16:08:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 instance=szdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/szdb/datafile/optmtbs.269.1091028875
output file name=/u01/app/oracle/oradata/szdb/optmtbs.269.1091028875 tag=TAG20211211T160850 RECID=3 STAMP=1091030935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2021-12-11 16:08:57
# 转换数据文件
RMAN> switch datafile '+DATA/szdb/datafile/optmtbs.269.1091028875' to copy;
datafile 6 switched to datafile copy "/u01/app/oracle/oradata/szdb/optmtbs.269.1091028875"
# 恢复数据文件
RMAN> recover datafile "/u01/app/oracle/oradata/szdb/optmtbs.269.1091028875";
Starting recover at 2021-12-11 16:12:01
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-12-11 16:12:02
# 将表空间上线
RMAN> sql 'alter tablespace OPTMTBS online';
sql statement: alter tablespace OPTMTBS online
asmcmd cp
# 将表空间离线
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL> alter tablespace OPTMTBS offline;
# 创建数据文件存储路径
[grid@rac2 oracle]$ mkdir -p /u01/app/oracle/oradata/szdb/
# 将asm文件拷贝到文件系统
[grid@rac2 oracle]$ asmcmd
ASMCMD> cp +DATA/szdb/datafile/optmtbs01.dbf /u01/app/oracle/oradata/szdb/optmtbs01.
copying +DATA/szdb/datafile/optmtbs01.dbf -> /u01/app/oracle/oradata/szdb/optmtbs01.
ASMCMD> quit
# 修改数据文件权限
[grid@rac2 ~]$ ll /u01/app/oracle/oradata/szdb/optmtbs01.dbf
-rw-r----- 1 grid oinstall 1992302592 Dec 11 14:55 /u01/app/oracle/oradata/szdb/optm
[root@rac2 ~]# chown oracle.oinstall /u01/app/oracle/oradata/szdb/optmtbs01.dbf
# 重命名数据文件路径
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL> alter database rename file '+DATA/szdb/datafile/optmtbs01.dbf' to '/u01/app/oracle/oradata/szdb/optmtbs01.dbf';
# 将表空间上线
SQL> alter tablespace OPTMTBS online;
dbms_file_transfer
# 创建逻辑路径
SQL> create directory file_system as '/u01/app/oracle/oradata/szdb';
Directory created.
SQL> create directory share_system as '+DATA/szdb/datafile';
Directory created.
# 查看dbms_file_transfer执行过程
SQL> desc dbms_file_transfer
PROCEDURE COPY_FILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
PROCEDURE GET_FILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
SOURCE_DATABASE VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
PROCEDURE PUT_FILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
DESTINATION_DATABASE VARCHAR2 IN
# 传输文件
SQL> begin
2 dbms_file_transfer.copy_file (source_directory_object => 'SHARE_SYSTEM', source_file_name => 'optmtbs.269.1091028875', destination_directory_object => 'FILE_SYSTEM', destination_file_name => 'optmtbs');
3 end;
4 /
PL/SQL procedure successfully completed.
# 查看文件是否拷贝完成
[oracle@rac1 ~]$ ll /u01/app/oracle/oradata/szdb/
total 1945608
-rw-r----- 1 oracle asmadmin 1992302592 Dec 11 17:04 optmtbs1
# 修改数据文件路径
SQL> alter database rename file '/u01/app/oracle/oradata/szdb/optmtbs.269.1091028875' to '+DATA/szdb/datafile/optmtbs';
# 将表空间离线
SQL> alter tablespace optmtbs offline;
# 转换数据文件路径
SQL> alter database rename file '+DATA/szdb/datafile/optmtbs' to '/u01/app/oracle/oradata/szdb/optmtbs1';
# 恢复数据文件
SQL> recover datafile '/u01/app/oracle/oradata/szdb/optmtbs1';
# 将表空间在线
SQL> alter tablespace optmtbs online;
文件系统迁移到ASM
现我们接到如下需求：
将文件系统中 OPTMTBS 表空间数据文件迁移到 ASM 共享盘中。
SQL> set line 300 pages 9999
SQL> col name for a50
SQL> select a.name, b.name from v$tablespace a, v$datafile b where a.ts# = b.ts#;
NAME NAME
-------------------------------------------------- --------------------------------------------------
SYSTEM +DATA/szdb/datafile/system.259.1089334417
SYSAUX +DATA/szdb/datafile/sysaux.260.1089334417
UNDOTBS1 +DATA/szdb/datafile/undotbs1.261.1089334419
UNDOTBS2 +DATA/szdb/datafile/undotbs2.263.1089334425
USERS +DATA/szdb/datafile/users.264.1089334425
OPTMTBS /u01/app/oracle/oradata/szdb/optmtbs01.dbf
rman
[oracle@rac2 ~]$ rman target /
# 将表空间离线
RMAN> sql 'alter tablespace OPTMTBS offline';
# 拷贝数据文件
RMAN> copy datafile '/u01/app/oracle/oradata/szdb/optmtbs01.dbf' to '+DATA';
Starting backup at 2021-12-11 15:34:34
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 instance=szdb2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/szdb/optmtbs01.dbf
output file name=+DATA/szdb/datafile/optmtbs.269.1091028875 tag=TAG20211211T153434 RECID=1 STAMP=1091028878
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2021-12-11 15:34:41
# 转换数据文件
RMAN> switch datafile '/u01/app/oracle/oradata/szdb/optmtbs01.dbf' to copy;
# 恢复数据文件
RMAN> recover datafile '+DATA/szdb/datafile/optmtbs.269.1091028875';
# 将表空间上线
RMAN> sql 'alter tablespace OPTMTBS online';
asmcmd copy
# 拷贝文件系统数据文件到ASM共享盘上
[grid@rac1 ~]$ asmcmd
ASMCMD> cp /u01/app/oracle/oradata/szdb/optmtbs1 +DATA/szdb/datafile/optmtbs1
copying /u01/app/oracle/oradata/szdb/optmtbs1 -> +DATA/szdb/datafile/optmtbs1
# 将表空间离线
SQL> alter tablespace optmtbs offline;
# 重命名数据文件路径
SQL> alter database rename file '/u01/app/oracle/oradata/szdb/optmtbs1' to '+DATA/szdb/datafile/optmtbs1';
# 恢复数据文件
SQL> recover datafile '+DATA/szdb/datafile/optmtbs1';
# 将表空间在线
SQL> alter tablespace optmtbs online;
dbms_file_transfer
# 创建逻辑路径
SQL> create directory file_system as '/u01/app/oracle/oradata/szdb';
Directory created.
SQL> create directory share_system as '+DATA/szdb/datafile';
Directory created.
# 查看dbms_file_transfer执行过程
SQL> desc dbms_file_transfer
PROCEDURE COPY_FILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
PROCEDURE GET_FILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
SOURCE_DATABASE VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
PROCEDURE PUT_FILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
DESTINATION_DATABASE VARCHAR2 IN
# 传输文件
SQL> begin
2 dbms_file_transfer.copy_file (source_directory_object => 'FILE_SYSTEM', source_file_name => 'optmtbs.269.1091028875', destination_directory_object => 'SHARE_SYSTEM', destination_file_name => 'optmtbs');
3 end;
4 /
PL/SQL procedure successfully completed.
# 查看文件是否拷贝完成
[grid@rac2 ~]$ asmcmd ls +DATA/szdb/datafile
COPY_FILE.269.1091032923
SYSAUX.260.1089334417
SYSTEM.259.1089334417
UNDOTBS1.261.1089334419
UNDOTBS2.263.1089334425
USERS.264.1089334425
optmtbs
# 将表空间离线
SQL> alter tablespace OPTMTBS offline;
# 修改数据文件路径
SQL> alter database rename file '/u01/app/oracle/oradata/szdb/optmtbs.269.1091028875' to '+DATA/szdb/datafile/optmtbs';
# 将表空间上线
SQL> alter tablespace OPTMTBS online;
总结：迁移过程中，我们需要将表空间暂时offline，然后转换数据文件路径，在将表空间online的时候，需要将数据文件进行recover。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
THE END
二维码