暂无图片
暂无图片
2
暂无图片
暂无图片
2
暂无图片
首页 / Oracle集群环境建立asm数据文件位置错误导致ORA-01157、ORA-01110问题处理

Oracle集群环境建立asm数据文件位置错误导致ORA-01157、ORA-01110问题处理

原创 狗剩儿 2022-03-01
1558

前言

客户应用人员操作建立表空间时，因没有是使用过rac的数据库，所以建立表空间指定了本地路径，甚至linux下指认路径为windows路径模式

遇到好几次这样的问题，应用对Oracle rac数据库建表空间时出现了将表空间建到文件系统下而导致故障，数据库一旦重启将无法正常启动，且系统表中开始报错，要恢复的信息只能通过系统视图来获取

提示：以下是本篇文章正文内容，下面案例可供参考（注：以下数据已进行脱敏处理，请注意区分）

一、背景环境介绍

本次为实际生产环境，数据库版本为19C，是其中小的pdb表空间发生问题
在这里插入图片描述

二、确认CDB信息以及本地数据文件位置

1.CDB是否开启归档

[oracle@dbm0dbadm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 31 17:52:05 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> 
SQL> 
SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     96
Next log sequence to archive   101
Current log sequence	       101
SQL> 
SQL> 
SQL> exit

复制

确定本地文件系统位置

因为存在2个节点RAC模式下，需要确定数据文件被建立在了哪个节点下(本次案例数据文件在节点二)：

SQL> alter session set container=***;

Session altered.

SQL> 
SQL> @tbs
order by "USED_RATE(%)" desc
                           *
ERROR at line 35:
ORA-01157: cannot identify/lock data file 303 - see DBWR trace file
ORA-01110: data file 303: '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsqycxl_data.dbf'
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
[oracle@dbm0dbadm01 ~]$ ssh dbm0dbadm02 
[oracle@dbm0dbadm02 ~]$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs
[oracle@dbm0dbadm02 dbs]$ ll
total 22575188
-rw-r----- 1 oracle asmadmin 13631496192 Aug 31 18:22 E:appAdministratororadataorclbsadmin_data.dbf
-rw-r----- 1 oracle asmadmin  3145736192 Aug 31 18:22 E:appAdministratororadataorclbsemr_data.dbf
-rw-r----- 1 oracle asmadmin  1048584192 Aug 31 18:22 E:appAdministratororadataorclbsenr_data.dbf
-rw-r----- 1 oracle asmadmin  1048584192 Aug 31 18:22 E:appAdministratororadataorclbshss_data.dbf
-rw-r----- 1 oracle asmadmin  1048584192 Aug 31 18:22 E:appAdministratororadataorclbsmob_data.dbf
-rw-r----- 1 oracle asmadmin  1048584192 Aug 31 18:22 E:appAdministratororadataorclbsportal_data.dbf
-rw-r----- 1 oracle asmadmin  1048584192 Aug 31 11:48 E:appAdministratororadataorclbsqylis_data.dbf
-rw-r----- 1 oracle asmadmin  1048584192 Aug 31 18:22 E:appAdministratororadataorclbszdgx_data.dbf
-rw-rw---- 1 oracle asmadmin        1544 May 31 17:01 hc_db1db12.dat
-rw-rw---- 1 oracle asmadmin        1544 Aug 31 16:55 hc_******bg2.dat
-rw-rw---- 1 oracle asmadmin        1544 Aug 31 17:04 hc_******ts2.dat
-rw-rw---- 1 oracle asmadmin        1544 Aug 31 18:28 hc_******yl2.dat
-rw-r----- 1 oracle asmadmin     2097152 May 31 17:01 id_db1db12.dat
-rw-r----- 1 oracle asmadmin     2097152 Aug 31 18:40 id_******bg2.dat
-rw-r----- 1 oracle asmadmin     2097152 Aug 31 18:40 id_******ts2.dat
-rw-r----- 1 oracle asmadmin     2097152 Aug 31 18:40 id_******yl2.dat
-rw-r--r-- 1 oracle oinstall        3079 Apr 17 20:42 init.ora
-rw-r----- 1 oracle asmadmin    19906560 Jun 15 18:29 snapcf_******bg2.f
-rw-r----- 1 oracle asmadmin    19939328 Aug 31 18:22 snapcf_******yl2.f
[oracle@dbm0dbadm02 dbs]$ 

复制

三、rman 恢复数据文件

确认数据文件FILE编号

因系统表开始报错无法使用，我们只能用系统视图执行查询

SQL> desc v$datafile;
 Name									  Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 FILE#										   NUMBER
 CREATION_CHANGE#								   NUMBER
 CREATION_TIME									   DATE
 TS#										   NUMBER
 RFILE# 									   NUMBER
 STATUS 									   VARCHAR2(7)
 ENABLED									   VARCHAR2(10)
 CHECKPOINT_CHANGE#								   NUMBER
 CHECKPOINT_TIME								   DATE
 UNRECOVERABLE_CHANGE#								   NUMBER
 UNRECOVERABLE_TIME								   DATE
 LAST_CHANGE#									   NUMBER
 LAST_TIME									   DATE
 OFFLINE_CHANGE#								   NUMBER
 ONLINE_CHANGE# 								   NUMBER
 ONLINE_TIME									   DATE
 BYTES										   NUMBER
 BLOCKS 									   NUMBER
 CREATE_BYTES									   NUMBER
 BLOCK_SIZE									   NUMBER
 NAME										   VARCHAR2(513)
 PLUGGED_IN									   NUMBER
 BLOCK1_OFFSET									   NUMBER
 AUX_NAME									   VARCHAR2(513)
 FIRST_NONLOGGED_SCN								   NUMBER
 FIRST_NONLOGGED_TIME								   DATE
 FOREIGN_DBID									   NUMBER
 FOREIGN_CREATION_CHANGE#							   NUMBER
 FOREIGN_CREATION_TIME								   DATE
 PLUGGED_READONLY								   VARCHAR2(3)
 PLUGIN_CHANGE# 								   NUMBER
 PLUGIN_RESETLOGS_CHANGE#							   NUMBER
 PLUGIN_RESETLOGS_TIME								   DATE
 CON_ID 									   NUMBER

SQL> select file#,ts#,name from v$datafile;

     FILE#	  TS# NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------
       154	    0 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/system.965.1079623727
       155	    0 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/system.967.1079623727
       156	    0 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/system.968.1079623727
       157	    0 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/system.969.1079623727
       158	    1 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/sysaux.966.1079623727
       159	    1 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/sysaux.970.1079623727
       160	    1 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/sysaux.971.1079623727
       161	    1 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/sysaux.972.1079623727
       162	    2 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/undotbs1.964.1079623727
       163	    2 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/undotbs1.973.1079623727
       164	    5 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/undo_2.963.1079623727

     FILE#	  TS# NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------
       165	    5 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/undo_2.974.1079623727
       166	    6 +DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/users.962.1079623727
       221	    7 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsportal_data.dbf
       222	    8 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsadmin_data.dbf
       223	    9 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsemr_data.dbf
       224	   10 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsmob_data.dbf
       225	   11 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsenr_data.dbf
       226	   12 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbshss_data.dbf
       227	   13 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbszdgx_data.dbf

20 rows selected.

复制

我们发现file#号221~227数据文件是全部建立错误的！

rman copy datafile

[oracle@dbm0dbadm02 dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 31 18:20:15 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XXXXXXX (DBID=1578450313)

RMAN> 

RMAN> backup as copy datafile 221,222,223,224,225,226,227 format '+DATAC1';

Starting backup at 31-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4247 instance=****yl2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00222 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsadmin_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsadmin_data.1118.1082053343 tag=TAG20210831T182222 RECID=4 STAMP=1082053349
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00223 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsemr_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsemr_data.1119.1082053359 tag=TAG20210831T182222 RECID=5 STAMP=1082053360
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00221 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsportal_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsportal_data.1120.1082053361 tag=TAG20210831T182222 RECID=6 STAMP=1082053361
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00224 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsmob_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsmob_data.1121.1082053361 tag=TAG20210831T182222 RECID=7 STAMP=1082053362
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00225 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsenr_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsenr_data.1122.1082053363 tag=TAG20210831T182222 RECID=8 STAMP=1082053363
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00226 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbshss_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bshss_data.1123.1082053365 tag=TAG20210831T182222 RECID=9 STAMP=1082053364
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00227 name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbszdgx_data.dbf
output file name=+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bszdgx_data.1124.1082053365 tag=TAG20210831T182222 RECID=10 STAMP=1082053365
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-21

Starting Control File and SPFILE Autobackup at 31-AUG-21
piece handle=+RECOC1/******YL/AUTOBACKUP/2021_08_31/s_1082053366.626.1082053367 comment=NONE
Finished Control File and SPFILE Autobackup at 31-AUG-21

RMAN> 

复制

pdb下将数据文件offline

[oracle@dbm0dbadm01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 31 17:52:05 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> 
SQL> alter database datafile 221,222,223,224,225,226,227 offline;

复制

switch datafile&recover datafile

RMAN> 

RMAN> switch datafile 221,222,223,224,225,226,227 to copy;

datafile 221 switched to datafile copy "+DATAC1/******EYL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsportal_data.1120.1082053361"
datafile 222 switched to datafile copy "+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsadmin_data.1118.1082053343"
datafile 223 switched to datafile copy "+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsemr_data.1119.1082053359"
datafile 224 switched to datafile copy "+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsmob_data.1121.1082053361"
datafile 225 switched to datafile copy "+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bsenr_data.1122.1082053363"
datafile 226 switched to datafile copy "+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bshss_data.1123.1082053365"
datafile 227 switched to datafile copy "+DATAC1/******YL/C8A3A2DFCCFD9096E053E1FEA00AA686/DATAFILE/bszdgx_data.1124.1082053365"

RMAN> recover datafile 221,222,223,224,225,226,227;

Starting recover at 31-AUG-21
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 31-AUG-21

RMAN> 
RMAN> exit

Recovery Manager complete.

复制

pdb下将数据文件online

SQL> alter database datafile 221,222,223,224,225,226,227 online;

复制

删除本地文件

[oracle@dbm0dbadm02 dbs]$ rm E:appadmin*

复制

四、结果确认

在这里插入图片描述

附：其他处理方法-删库

正好也是有环境可以进行测试删库操作，如下：
dbca去管理pdb，选择删除pdb，发现出现错误提示，但还可以继续，最终显示pdb删除成功
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
登录数据库进行验证发现此库只是mounted状态！
在这里插入图片描述
尝试用命令删除：

SQL> drop pluggable database *** including datafiles;
drop pluggable database *** including datafiles
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 303 - see DBWR trace file
ORA-01110: data file 303:
'/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/E:appAdministratororadataorclbsqy
***_data.dbf'


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

复制

想不出其他办法，只能问问大哥了:

在这里插入图片描述

SQL> 
SQL> alter session set container=***;

Session altered.

SQL> 
SQL> 
SQL> alter pluggable database datafile 303 offline drop;

Pluggable database altered.

SQL> conn / as sysdba
Connected.
SQL> drop pluggable database *** including datafiles;

Pluggable database dropped.

SQL> 

复制

总结

老奶奶擤鼻涕，手拿把掐

墨力计划ora-01110oracle asmoracle racoracle 19c
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

筱悦星辰
暂无图片
11月前
评论
暂无图片 0
有空多运动，给身心充能，以强健的体魄迎接生活中的各种挑战。
11月前
暂无图片 点赞
评论
墨天轮福利君
暂无图片
3年前
评论
暂无图片 0
您好，您的文章已入选合格奖，10墨值奖励已经到账请查收！ ❤️我们还会实时派发您的流量收益。
3年前
暂无图片 点赞
评论

相关阅读

Oracle 19c RAC更换IP实战，运维必看！
szrsu
393次阅读
2025-04-08 23:57:08
Oracle 19c for OEL8.9 4节点RAC安装部署（静默方式）
zwtian
250次阅读
2025-03-28 16:13:14
Oracle19C低版本一天遭遇两BUG（ORA-04031/ORA-00600）
潇湘秦
230次阅读
2025-04-16 17:05:16
DeepSeek 携手 Oracle in-memory 新特性：统计对象存储访问情况的神奇之旅
JiekeXu
206次阅读
2025-03-31 18:02:28
oracle rac 添加 多个scan ip，你不可不知道的坑！
szrsu
204次阅读
2025-04-13 23:56:22
数据库服务器操作系统升级后Oracle 19c crs无法启动
Xiaofei Huangfu
116次阅读
2025-03-31 15:00:07
Oracle 11g RAC手动打补丁详细步骤
Digital Observer
97次阅读
2025-04-20 21:12:42
19c&21c单机/RAC手工清理标准化文档
Digital Observer
75次阅读
2025-03-27 11:04:42
Oracle RAC
恩恩霸
41次阅读
2025-03-28 21:54:28