故障简述：
使用asm磁盘组时，当添加数据文件，因操作不当，误将数据文件添加至文件系统，可以使用如下方式解决
模拟环境：19c俩节点的一套rac集群
ora.mn.db
1 ONLINE ONLINE ora19c01 Open,HOME=/u01/app/oracle/product/19.0.0/dbhome_1,STABLE
2 ONLINE ONLINE ora19c02 Open,HOME=/u01/app/oracle/product/19.0.0/dbhome_1,STABLE
Version 19.3.0.0.0
故障模拟：
一节点：
为表空间添加数据文件：
—添加数据文件前查询：
05:58:52 SYS@mn1>select total.tablespace_name, round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB, round(free.MB, 2) as Free_MB,
round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB USED_PCT
UNDOTBS1 420 419.38 .63 99.85%
UNDO_2 255 251 4 98.43%
SYSTEM 280 273.44 6.56 97.66%
SYSAUX 325 305.94 19.06 94.13%
DB_DATA 4096 1512 2584 36.91%
UNDOTBS02 1024 260.94 763.06 25.48%
USERS 5 1 4 20%
05:56:26 SYS@mn1>SET pages 200
05:56:26 SYS@mn1>SELECT d.file_name,
05:56:26 2 d.file_id,
05:56:26 3 d.AUTOEXTENSIBLE,
05:56:26 4 d.BYTES/1024/1024/1024,
05:56:26 5 d.MAXBYTES/1024/1024/1024,
05:56:26 6 t.CREATION_TIME
05:56:26 7 FROM dba_data_files d,
05:56:26 8 v$datafile t
05:56:26 9 WHERE d.file_id = t.file#
05:56:26 10 AND tablespace_name=upper(’&tablespace_name’) order by 6;
Enter value for tablespace_name: DB_DATA
old 10: AND tablespace_name=upper(’&tablespace_name’) order by 6
new 10: AND tablespace_name=upper(‘DB_DATA’) order by 6
FILE_NAME FILE_ID AUTOEXT D.BYTES/1024/1024/1024 D.MAXBYTES/1024/1024/1024 CREATION_TIME
+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.307.1040786097 14 NO 4 0 20200519 03:16:04
05:59:21 SYS@mn1>alter tablespace DB_DATA add datafile ‘DATE01’ size 30M;
Tablespace altered.
二节点：
06:03:59 SYS@mn2>set serveroutput on size 1000000
06:04:02 SYS@mn2>col tablespace_name format a20
06:04:02 SYS@mn2>col autoextensible format a7
06:04:02 SYS@mn2>set pages 999 linesize 300
06:04:02 SYS@mn2>select a.tablespace_name,
06:04:02 2 round(a.s, 2) “CURRENT_TOTAL(MB)”,
06:04:02 3 round((a.s - f.s), 2) “USED(MB)”,
06:04:02 4 f.s “FREE(MB)”,
06:04:02 5 round(f.s / a.s * 100, 2) “FREE%”,
06:04:02 6 g.autoextensible,
06:04:02 7 round(a.ms, 2) “MAX_TOTAL(MB)”
06:04:02 8 from (select d.tablespace_name,
06:04:02 9 sum(bytes / 1024 / 1024) s,
06:04:02 10 sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms
06:04:02 11 from dba_data_files d
06:04:02 12 group by d.tablespace_name) a,
06:04:02 13 (select f.tablespace_name, sum(f.bytes / 1024 / 1024) s
06:04:02 14 from dba_free_space f
06:04:02 15 group by f.tablespace_name) f,
06:04:02 16 (select distinct tablespace_name, autoextensible
06:04:02 17 from DBA_DATA_FILES
06:04:02 18 where autoextensible = ‘YES’
06:04:02 19 union
06:04:02 20 select distinct tablespace_name, autoextensible
06:04:02 21 from DBA_DATA_FILES
06:04:02 22 where autoextensible = ‘NO’
06:04:02 23 and tablespace_name not in
06:04:02 24 (select distinct tablespace_name
06:04:02 25 from DBA_DATA_FILES
06:04:02 26 where autoextensible = ‘YES’)) g
06:04:02 27 where a.tablespace_name = f.tablespace_name
06:04:02 28 and g.tablespace_name = f.tablespace_name
06:04:02 29 order by “FREE%”;
select a.tablespace_name,
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 17 - see DBWR trace file
ORA-01110: data file 17: '+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.352.1045721009’
06:12:14 SYS@mn2>SET lines 300
06:12:14 SYS@mn2>SET pages 200
06:12:14 SYS@mn2>SELECT d.file_name,
06:12:14 2 d.file_id,
06:12:14 3 d.AUTOEXTENSIBLE,
06:12:14 4 d.BYTES/1024/1024/1024,
06:12:14 5 d.MAXBYTES/1024/1024/1024,
06:12:14 6 t.CREATION_TIME,
06:12:14 7 T.STATUS
06:12:14 8 FROM dba_data_files d,
06:12:14 9 v$datafile t
06:12:14 10 WHERE d.file_id = t.file#
06:12:14 11 AND tablespace_name=upper(’&tablespace_name’) order by 6;
Enter value for tablespace_name: DB_DATA
old 11: AND tablespace_name=upper(’&tablespace_name’) order by 6
new 11: AND tablespace_name=upper(‘DB_DATA’) order by 6
FROM dba_data_files d,
*
ERROR at line 8:
ORA-01157: cannot identify/lock data file 17 - see DBWR trace file
ORA-01110: data file 17: ‘+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.352.1045721009’
故障排查：
06:13:25 SYS@mn2>SELECT FILE#, STATUS, NAME FROM V$DATAFILE;
FILE# STATUS NAME复制
9 SYSTEM +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/system.308.1040453627 10 ONLINE +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/sysaux.309.1040453627 11 ONLINE +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/undotbs1.307.1040453625 12 ONLINE +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/undo_2.311.1040453823 13 ONLINE +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/users.312.1040453827 14 ONLINE +DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.307.1040786097 15 ONLINE +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/undotbs01.288.1040787313 16 ONLINE +MGMT/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/undotbs02.289.1040787643 17 ONLINE +DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.352.1045721009复制
查看数据文件
06:00:15 SYS@mn1>SET lines 300
06:00:15 SYS@mn1>SET pages 200
06:00:15 SYS@mn1>SELECT d.file_name,
06:00:15 2 d.file_id,
06:00:15 3 d.AUTOEXTENSIBLE,
06:00:15 4 d.BYTES/1024/1024/1024,
06:00:15 5 d.MAXBYTES/1024/1024/1024,
06:00:15 6 t.CREATION_TIME
06:00:15 7 FROM dba_data_files d,
06:00:15 8 v$datafile t
06:00:15 9 WHERE d.file_id = t.file#
06:00:15 10 AND tablespace_name=upper(’&tablespace_name’) order by 6;
Enter value for tablespace_name: DB_DATA
old 10: AND tablespace_name=upper(’&tablespace_name’) order by 6
new 10: AND tablespace_name=upper(‘DB_DATA’) order by 6
FILE_NAME FILE_ID AUTOEXT D.BYTES/1024/1024/1024 D.MAXBYTES/1024/1024/1024 CREATION_TIME
+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.307.1040786097 14 NO 4 0 20200519 03:16:04
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DATE01 17 NO .029296875 0 20200714 05:59:59
解决方案
06:02:30 SYS@mn1>ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DATE01’ TO ‘+DATE01’;
06:03:49 SYS@mn1>SET lines 300
06:03:49 SYS@mn1>SET pages 200
06:03:49 SYS@mn1>SELECT d.file_name,
06:03:49 2 d.file_id,
06:03:49 3 d.AUTOEXTENSIBLE,
06:03:50 4 d.BYTES/1024/1024/1024,
06:03:50 5 d.MAXBYTES/1024/1024/1024,
06:03:50 6 t.CREATION_TIME
06:03:50 7 FROM dba_data_files d,
06:03:50 8 v$datafile t
06:03:50 9 WHERE d.file_id = t.file#
06:03:50 10 AND tablespace_name=upper(’&tablespace_name’) order by 6;
Enter value for tablespace_name: DB_DATA
old 10: AND tablespace_name=upper(’&tablespace_name’) order by 6
new 10: AND tablespace_name=upper(‘DB_DATA’) order by 6
FILE_NAME FILE_ID AUTOEXT D.BYTES/1024/1024/1024 D.MAXBYTES/1024/1024/1024 CREATION_TIME
+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.307.1040786097 14 NO 4 0 20200519 03:16:04
+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.352.1045721009 17 NO 1 0 20200714 05:59:59
06:15:41 SYS@mn2>select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round(free.MB, 2) as Free_MB,
round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
(
select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round(free.MB, 2) as Free_MB,
round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 17 - see DBWR trace file
ORA-01110: data file 17: ‘+DATE01/mn/A5AE9F4DFCCE4887E053B32BA8C00A87/DATAFILE/db_data.352.1045721009’
06:19:21 SYS@mn2>ALTER SYSTEM CHECK DATAFILES;
System altered.
06:19:39 SYS@mn2>select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round(free.MB, 2) as Free_MB,
round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB USED_PCT
UNDOTBS1 420 419.38 .63 99.85%
UNDO_2 255 251 4 98.43%
SYSTEM 280 273.44 6.56 97.66%
SYSAUX 325 305.94 19.06 94.13%
DB_DATA 5120 1513 3607 29.55%
UNDOTBS02 1024 260.94 763.06 25.48%
USERS 5 1 4 20%
总结：
我们可以在高版本中，当数据文件添加到文件系统中，可利用ALTER DATABASE MOVE DATAFILE ‘*********’ TO ‘+dg_name’;在其他节点执行 ALTER SYSTEM CHECK DATAFILES;
这种方法进行处理。
