文件系统与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

# 创建数据文件存放路径
[[email protected] ~]$ 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

# 将表空间离线
[[email protected] ~]$ sqlplus / as sysdba
SQL> alter tablespace OPTMTBS offline;

# 创建数据文件存储路径
[[email protected] oracle]$ mkdir -p /u01/app/oracle/oradata/szdb/

# 将asm文件拷贝到文件系统
[[email protected] 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

# 修改数据文件权限
[[email protected] ~]$ ll /u01/app/oracle/oradata/szdb/optmtbs01.dbf
-rw-r----- 1 grid oinstall 1992302592 Dec 11 14:55 /u01/app/oracle/oradata/szdb/optm
[[email protected] ~]# chown oracle.oinstall /u01/app/oracle/oradata/szdb/optmtbs01.dbf

# 重命名数据文件路径
[[email protected] ~]$ 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.

# 查看文件是否拷贝完成
[[email protected] ~]$ 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

[[email protected] ~]$ 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共享盘上
[[email protected] ~]$ 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.

# 查看文件是否拷贝完成
[[email protected] ~]$ 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
分享
二维码
< <上一篇

)">
下一篇>>