ORA-01157、ORA-01110之集群环境建立asm数据文件错误到本地处理

---

前言

遇到好几次这样的问题，应用对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