Oracle RAC数据文件建到本地处理步骤

问题背景：

在进行asm管理的rac表空间增加时，忘记添加+到对应磁盘组，导致建在了节点的本地，好在技术大哥提供方案，本次对事件进行记录整理。

SQL> alter tablespace XXX_name add datafile 'XXX_DATA' size 30G;


Tablespace altered.


SQL> /

alter tablespace XXX_name add datafile 'XXX_DATA' size 30G

*

ERROR at line 1:

ORA-01537: cannot add file 'XXX_DATA' - file already part of database

--发现报错 文件添加错误

SQL> select file_name,file_id from dba_data_files where tablespace_name='XXX_name';


FILE_NAME

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

+XXX_DATA/XXX/DATAFILE/XXX_name.353.1070795283

+XXX_DATA/XXX/DATAFILE/XXX_name.354.1070795309

+XXX_DATA/XXX/DATAFILE/XXX_name.1418.1084619327

+XXX_DATA/XXX/DATAFILE/XXX_name.2347.1084619379

+XXX_DATA/XXX/DATAFILE/XXX_name.1959.1084619405

+XXX_DATA/XXX/DATAFILE/XXX_name.1257.1084619431

+XXX_DATA/XXX/DATAFILE/XXX_name.3088.1084619455

+XXX_DATA/XXX/DATAFILE/XXX_name.3098.1095361573

+XXX_DATA/XXX/DATAFILE/XXX_name.1275.1095361599

/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA

--将本地文件resize到最小 并关闭自动扩展

SQL> alter database  datafile '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA' resize 5M;


Database altered.



SQL> alter database  datafile '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA' autoextend off;


Database altered.

--查询数据文件里面无数据为空

SQL> select t.file_name,t1.owner,t1.segment_name,t1.segment_type,t1.tablespace_name from dba_data_files t,dba_extents t1 where t.file_id=t1.file_id and file_name='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA';


no rows selected

--将问题文件offline

SQL> alter database datafile 920 offline;


Database altered.

--将问题文件拷贝到共享存储重命名

RMAN> copy datafile '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA'  to '+XXX_DATA/XXX/DATAFILE/XXX_name_38.dbf';


Starting backup at 2022-10-25 20:35:28

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00920 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA

output file name=+XXX_DATA/XXX/DATAFILE/XXX_name_38.dbf tag=TAG20221025T203530 RECID=1 STAMP=1119040530

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2022-10-25 20:35:31


Starting Control File and SPFILE Autobackup at 2022-10-25 20:35:31

piece handle=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/c-2015207192-20221025-06 comment=NONE

Finished Control File and SPFILE Autobackup at 2022-10-25 20:35:32

--更新控制文件

SQL> alter  tablespace XXX_name rename datafile '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/XXX_DATA' to '+XXX_DATA/XXX/DATAFILE/XXX_name_38.dbf';


Tablespace altered.

--恢复下数据文件

SQL> recover datafile 920 ;      
Media recovery complete.

--将数据文件online

SQL> alter database datafile 920 online;

--查询数据文件状态

+XXX_DATA/XXX/DATAFILE/XXX_name_38.dbf               AVAILABLE

+XXX_DATA/XXX/DATAFILE/XXX_name.2363.1119038345      AVAILABLE


FILE_NAME                                                    STATUS

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

+XXX_DATA/XXX/DATAFILE/XXX_name.1767.1119038419      AVAILABLE

+XXX_DATA/XXX/DATAFILE/XXX_name.1844.1119038447      AVAILABLE

+XXX_DATA/XXX/DATAFILE/XXX_name.1968.1119038523      AVAILABLE

+XXX_DATA/XXX/DATAFILE/XXX_name.1280.1119038549      AVAILABLE

+XXX_DATA/XXX/DATAFILE/XXX_name.2544.1119038589      AVAILABLE

--将问题文件resize到30G 同时查看节点2和备库数据文件状态正常

SQL> alter database datafile '+XXX_DATA/XXX/DATAFILE/XXX_name_38.dbf' resize 30G;


Database altered.