Every Day of a DBA，第147期: Oracle rac 错误的添加数据文件

用户3107127

发布于 2026-07-09 16:08:26

160

摘要：还需要DBA吗？DBA有未来吗？这种神奇的操作还有很多的。

1.故障现场

数据库无法正常启动，打开数据库提示 "ORA-01157: cannot identify/lock data file 171 - see DBWR trace fileORA-01110: data file 171: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF'"

[root@wnjj-01 trace]# tail -n 500 alert_wnjj1.log
Fri Jul 03 10:18:27 2026
Decreasing number of real time LMS from 8 to 0
Fri Jul 03 10:28:20 2026
ALTER DATABASE RECOVER datafile 171  
Media Recovery Start
Serial Media Recovery started
Fri Jul 03 10:28:20 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj1/trace/wnjj1_dbw0_24023.trc:
ORA-01157: cannot identify/lock data file 171 - see DBWR trace file
ORA-01110: data file 171: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Media Recovery failed with error 1110
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 171 ...
Fri Jul 03 10:29:37 2026
ALTER DATABASE RECOVER datafile 171  
Media Recovery Start
Serial Media Recovery started
Fri Jul 03 10:29:37 2026

2.故障根因

数据库启动时找不到171号数据文件，文件路径是Windows格式路径G:\ORADATA\wnrm4\SYSAUX05.DBF，却存放在Linux本地文件系统目录/u01/app/oracle/product/11.2.0/dbhome_1/dbs/下，文件已迁移至ASM，控制文件记录的旧路径失效，打开数据库报ORA-01157。

3.故障修复

step 1.启动数据库到MOUNT状态

# 登录sqlplus
sqlplus /as sysdba
shutdown immediate;
startup mount;

step 2.把本地旧文件迁移到ASM：

场景1：如果本地还有原文件

[root@wnjj-02 ~]# ls -la /u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF
-rw-r----- 1 oracle asmadmin 31457288192 Jul 3 18:50 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF
[root@wnjj-02 ~]# cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[root@wnjj-02 dbs]# ls -la
total 30720028
drwxr-xr-x   2 oracle oinstall         115 Jul 3 18:51 .
drwxr-xr-x. 76 oracle oinstall       4096 Jun 7 2023 ..
-rw-r-----   1 oracle asmadmin 31457288192 Jul 3 18:50 G:ORADATAwnrm4SYSAUX05.DBF
-rw-rw----   1 oracle asmadmin       1544 Jul 3 18:53 hc_wnjj2.dat
-rw-r--r--   1 oracle oinstall       2851 Jun 7 2023 init.ora
-rw-r-----   1 oracle oinstall         35 Jun 7 2023 initwnjj2.ora
-rw-r-----   1 oracle oinstall       1536 Jun 7 2023 orapwwnjj2
[root@wnjj-02 dbs]#

两种正确拷贝方案

方案1：RMAN copy（推荐，数据库文件拷贝最优，支持校验）

登录Oracle数据库用户（oracle）执行RMAN：

rman target /
copy datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF' to '+DATA/wnjj/datafile/SYSAUX05.DBF';

RMAN> copy datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF' to '+DATA/wnjj/datafile/SYSAUX05.DBF';

Starting backup at 2026-07-04 09:40:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1057 instance=wnjj2 device type=DISK
channel ORA_DISK_1: starting datafile copy
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/04/2026 09:40:20
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

RMAN>

说明：RMAN不会把G:识别为远程串，专门处理数据文件拷贝，自带完整性校验。

方案2：ASMCMD 规避盘符冒号报错

操作系统层面先复制到无冒号路径，再进ASMCMD

# oracle/grid用户均可
cp"/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF" /u01/app/oracle/product/11.2.0/dbhome_1/dbs/SYSAUX05.DBF

chown oracle:asmadmin SYSAUX05.DBF

asmcmd

ASMCMD>cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/SYSAUX05.DBF+DATA/wnjj/datafile/SYSAUX05.DBF
copying /u01/app/oracle/product/11.2.0/dbhome_1/dbs/SYSAUX05.DBF->+DATA/wnjj/datafile/SYSAUX05.DBF

ASMCMD>

文件权限问题：

ASMCMD> cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/SYSAUX05.DBF +DATA/wnjj/datafile/SYSAUX05.DBF
ASMCMD-8014: file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/SYSAUX05.DBF' does not exist
ASMCMD>

补充说明

ASMCMD cp 对路径中: 有特殊解析逻辑，只要路径含X:就判定为远程连接字符串，无法直接使用；
生产环境优先用 RMAN copy，ASMCMD仅适合小文件、归档、日志拷贝，数据文件推荐RMAN。

查看控制文件，该文件已经在控制文件：

alter database backup controlfile to trace as '/home/oracle/ctl_trace.sql';

场景2：本地旧文件已删除，仅ASM有文件

直接执行alter database rename即可，不需要copy。

场景3：rename执行报错路径不匹配

核对报错日志里完整旧文件名：/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF字符串必须一字不差复制到rename左侧，不能漏字符、不能改分隔符。

step 3.RMAN修改数据文件171路径至ASM

rman target /
# 修改171号文件路径
RMAN> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF' to '+DATA/wnjj/datafile/SYSAUX05.DBF';

# sqlplus
alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/G:ORADATAwnrm4SYSAUX05.DBF' to '+DATA/wnjj/datafile/SYSAUX05.DBF';

# 重启实例后，查看文件
select name from v$datafile where name like '%sysaux05%';

说明：

左边是报错里完整旧路径原样复制
右边 +DATA/wnjj/datafile/SYSAUX05.DBF 替换为你真实ASM路径

step 4.校验控制文件数据文件信息

select file#, name, status from v$datafile where file#=171;

正常结果：NAME列显示ASM路径，不再是旧Linux本地路径。

RMAN校验文件（可选，确保ASM文件存在）

RMAN> crosscheck datafile 171;
RMAN> list datafile 171;

step 5.打开数据库

alterdatabaseopen;

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-04，如有侵权请联系 cloudcommunity@tencent.com 删除

dba

本文分享自 ByteHouse 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度