一.背景

Oracle 11g rac数据库，给表空间添加数据文件加到本地。

二.处理步骤

1.offline对应数据文件。
2.拷贝数据文件到asm磁盘组。
3.recover对应数据文件。
4.online对应数据文件。

三.模拟详细过程

1.创建测试表空间，并写入数据100%

create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 1M autoextend off;
create table scott.test1 (id int,name char(2)) tablespace test;
insert into scott.test1 values(1,'a');
insert into scott.test1 select * from scott.test1;

SQL> /
insert into scott.test1  select * from scott.test1
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 8 in tablespace TEST

2.模拟新增数据文件到本地

set line 200
col file_name for a60
select file_id,tablespace_name,file_name from dba_data_files where tablespace_name='TEST';

   FILE_ID TABLESPACE_NAME		  FILE_NAME
---------- ------------------------------ ------------------------------------------------------------
	 9 TEST 			  +DATA/test/datafile/test01.dbf

SQL> 


1节点操作：
alter tablespace test add datafile '/u01/app/oracle/test.dbf' size 1m autoextend off;

set line 200
col file_name for a60
SQL> select file_id,tablespace_name,file_name from dba_data_files where tablespace_name='TEST';
   FILE_ID TABLESPACE_NAME		  FILE_NAME
---------- ------------------------------ ---------------------------------
	 9 TEST 			  +DATA/test/datafile/test01.dbf
	10 TEST 			  /u01/app/oracle/test.dbf
SQL>  

3.2节点（另外一个节点）此时会报错

2节点alert日志报错：
Tue Jul 12 21:53:57 2022
Errors in file /u01/app/oracle/diag/rdbms/test/test1/trace/test1_dbw0_4281.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/test.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory


2节点查询：
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

set line 200
col file_name for a60
SQL> select file_id,tablespace_name,file_name from dba_data_files where tablespace_name='TSP_SUPCON';
ERROR:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/test.dbf'

no rows selected

4.关闭2节点

因为此时数据库日志会报错，同时应用访问也会报错，所以暂时关闭2节点，先有1节点承担业务，等下班之后修复。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

5.offline新增数据文件

alter database datafile 10 offline;
SQL> alter database datafile 10 offline;
Database altered.

6.拷贝数据文件到ASM磁盘组

可以进入asm之后cp拷贝，也可以用rman拷贝。

RMAN> backup as copy datafile 10 format '+DATA/test/datafile/test.dbf';
Starting backup at 2022-07-12 23:05:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 instance=test1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/u01/app/oracle/test.dbf
output file name=+DATA/test/datafile/test.dbf tag=TAG20220712T230516 RECID=3 STAMP=1109891116
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2022-07-12 23:05:17
RMAN> 

验证是否拷贝成功

ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    EXAMPLE.279.1023519191
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    SYSAUX.272.1023518961
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    SYSTEM.271.1023518955
DATAFILE  UNPROT  COARSE   JUL 12 23:00:00  Y    TEST.264.1109891117
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    TEST.265.1109890309
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    TSP_SUPCON.270.1027356843
                                            N    TSP_SUPCON01.dbf => +DATA/ASM/DATAFILE/TSP_SUPCON01.dbf.260.1109887173
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    UNDOTBS1.273.1023518961
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    UNDOTBS2.280.1023519643
DATAFILE  UNPROT  COARSE   JUL 12 22:00:00  Y    USERS.274.1023518961
                                            N    test.dbf => +DATA/TEST/DATAFILE/TEST.264.1109891117
                                            N    test01.dbf => +DATA/TEST/DATAFILE/TEST.265.1109890309
ASMCMD> 
ASMCMD> 

7.rename新增数据文件

alter database rename file '/u01/app/oracle/test.dbf'  to  '+DATA/test/datafile/test.dbf';

8.online新增数据文件

alter database recover datafile 10;
alter database datafile 10 online;

9.验证

select file_id,tablespace_name,file_name from dba_data_files where tablespace_name='TSP_SUPCON';

SQL> set line 200 
SQL> col file_name for a60
SQL> select file_id,tablespace_name,file_name from dba_data_files where tablespace_name='TSP_SUPCON';

   FILE_ID TABLESPACE_NAME		  FILE_NAME
---------- ------------------------------ ------------------------------------------------------------
	 7 TSP_SUPCON			  +DATA/test/datafile/tsp_supcon.270.1027356843
	 8 TSP_SUPCON			  +DATA/asm/datafile/tsp_supcon01.dbf.260.1109887173

SQL> 


select file#,name,status from gv$datafile where file#=10;

SQL> select file#,name,status from gv$datafile where file#=10;

     FILE#                         NAME                STATUS
---------------------------------------------------------------------------
      10			+DATA/test/datafile/test.dbf    ONLINE

10.启动2节点