意外在rac集群中将数据文件添加到本地文件系统

在检查集群表空间数据文件的情况时发现有一个数据文件放在本地文件系统上，会造成集群例外的一个节点无法写入数据，后台日志报错，需要将这个数据文件迁移到asm磁盘组，让集群中所有实例都能访问到这个数据文件。

[oracle@sourcedb1:/home/oracle rac1]$ orafile

Elapsed: 00:00:00.02
[oracle@sourcedb1:/home/oracle rac1]$


报错

Fri Sep 22 16:12:37 2023
Errors in file /oracle/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18072.trc:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/oracle/app/oracle/product/11.2.0/db_1/dbs/syw01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18072.trc:
ORA-01186: file 12 failed verification tests


将表空间设置为read only

[oracle@sourcedb1:/home/oracle rac1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 14 14:37:44 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
14:37:44 sys@@sourcedb1:RAC:rac1> ALTER TABLESPACE syw READ ONLY;
Tablespace altered.


使用 RMAN COPY 命令将数据文件复制到 ASM 磁盘组，当然也可以将数据文件offline直接手工拷贝到asm磁盘组中

[oracle@sourcedb1:/home/oracle rac1]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 14 14:37:51 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC (DBID=2722556546)
RMAN> COPY DATAFILE '/oracle/app/oracle/product/11.2.0/db_1/dbs/syw01.dbf' TO '+DATA';
Starting backup at 14-DEC-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/oracle/app/oracle/product/11.2.0/db_1/dbs/syw01.dbf
output file name=+DATA/rac/datafile/syw.275.1155566323 tag=TAG20231214T143842 RECID=1 STAMP=1155566323
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-DEC-23
RMAN>


在 SQL*Plus 中，指向新的 ASM 数据文件位置并将表空间切换回读写模式：

[oracle@sourcedb1:/home/oracle rac1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 14 14:39:47 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
14:39:47 sys@@sourcedb1:RAC:rac1> ALTER TABLESPACE syw OFFLINE;
Tablespace altered.
Elapsed: 00:00:00.24
14:39:49 sys@@sourcedb1:RAC:rac1> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/11.2.0/db_1/dbs/syw01.dbf' TO '+DATA/rac/datafile/syw.275.1155566323';
Database altered.
Elapsed: 00:00:00.10
14:40:07 sys@@sourcedb1:RAC:rac1> ALTER TABLESPACE syw ONLINE;
Tablespace altered.
Elapsed: 00:00:00.02
14:40:17 sys@@sourcedb1:RAC:rac1> ALTER TABLESPACE syw READ WRITE;
Tablespace altered.
Elapsed: 00:00:00.10
14:40:24 sys@@sourcedb1:RAC:rac1>