文档课题：11g rac添加数据文件至本地文件系统的异常处理演练.
系统：centos 7.9 64位
数据库：11.2.0.4 64位
环境：rac(双节点) + dg
应用场景：巡检客户一套核心数据库时，发现存在一个数据文件添加错误，正确的路径应该在磁盘组，但该数据文件的路径却为本地文件系统.造成的异常现象为节点2无法查询该数据文件，而且备份也出现异常.
1、模拟场景
节点1执行.
SQL> create tablespace SIG_DISA DATAFILE 'D:\app\Administrator\oradata\DRGA\SIG_DISA.DBF' SIZE 50m autoextend on;
Tablespace created.
SQL> col file_name for a90
SQL> col tablespace_name for a15
SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files
FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
------------------------------------------------------------------------------------------ --------------- --- ---------------
+DATA/orcl/datafile/system.259.1117373983 SYSTEM YES 1350
+DATA/orcl/datafile/sysaux.260.1117373985 SYSAUX YES 600
+DATA/orcl/datafile/undotbs1.261.1117373985 UNDOTBS1 YES 810
+DATA/orcl/datafile/undotbs2.263.1117373991 UNDOTBS2 YES 200
+DATA/orcl/datafile/users.264.1117373991 USERS YES 5
+DATA/orcl/datafile/users.277.1117483957 USERS YES 50
/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGASIG_DISA.DBF SIG_DISA YES 50
7 rows selected.
节点2执行.
SQL> col file_name for a90
SQL> col tablespace_name for a15
SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7:
'/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGAZL_SIG_D
ISEASE.DBF'
no rows selected
备库执行.
SQL> set line 200
SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files
FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
------------------------------------------------------------------------------------------ --------------- --- ---------------
/u01/app/oracle/oradata/system.259.1117373983 SYSTEM YES 1350
/u01/app/oracle/oradata/sysaux.260.1117373985 SYSAUX YES 600
/u01/app/oracle/oradata/undotbs1.261.1117373985 UNDOTBS1 YES 810
/u01/app/oracle/oradata/undotbs2.263.1117373991 UNDOTBS2 YES 200
/u01/app/oracle/oradata/users.264.1117373991 USERS YES 5
/u01/app/oracle/oradata/users.277.1117483957 USERS YES 50
/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGASIG_DISA.DBF SIG_DISA YES 50
7 rows selected.
SQL> show parameter db_cre
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
小结：异常成功模拟，可以看到节点1会生成?/dbs/D:appAdministratororadataDRGASIG_DISA.DBF文件，节点2查询报错ORA-01110，备库也会生成相应的数据文件.
2、解决方案
2.1、直接drop
有时为误操作导致，并不是真的想添加表空间，此时直接drop该表空间即可.
节点1执行.
SQL> drop tablespace SIG_DISA including contents and datafiles cascade constraints;
Tablespace dropped.
节点1&2&备库查询.
SQL> col file_name for a90
SQL> col tablespace_name for a15
SQL> set line 200
SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
------------------------------------------------------------------------------------------ --------------- --- ---------------
/u01/app/oracle/oradata/system.259.1117373983 SYSTEM YES 1350
/u01/app/oracle/oradata/sysaux.260.1117373985 SYSAUX YES 600
/u01/app/oracle/oradata/undotbs1.261.1117373985 UNDOTBS1 YES 810
/u01/app/oracle/oradata/undotbs2.263.1117373991 UNDOTBS2 YES 200
/u01/app/oracle/oradata/users.264.1117373991 USERS YES 5
/u01/app/oracle/oradata/users.277.1117483957 USERS YES 50
6 rows selected.
2.2、修改路径
节点1执行.
SQL> create tablespace SIG_DISA DATAFILE 'D:\app\Administrator\oradata\DRGA\SIG_DISA.DBF' SIZE 50m autoextend on;
Tablespace created.
SQL> col file_name for a90
SQL> col tablespace_name for a15
SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files
FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
------------------------------------------------------------------------------------------ --------------- --- ---------------
+DATA/orcl/datafile/system.259.1117373983 SYSTEM YES 1350
+DATA/orcl/datafile/sysaux.260.1117373985 SYSAUX YES 600
+DATA/orcl/datafile/undotbs1.261.1117373985 UNDOTBS1 YES 810
+DATA/orcl/datafile/undotbs2.263.1117373991 UNDOTBS2 YES 200
+DATA/orcl/datafile/users.264.1117373991 USERS YES 5
+DATA/orcl/datafile/users.277.1117483957 USERS YES 50
/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGASIG_DISA.DBF SIG_DISA YES 50
SQL> alter tablespace SIG_DISA offline;
Tablespace altered.
RMAN> copy datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGASIG_DISA.DBF' to '+data';
Starting backup at 11-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGASIG_DISA.DBF
output file name=+DATA/orcl/datafile/SIG_DISA.279.1117806753 tag=TAG20221011T135232 RECID=2 STAMP=1117806753
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-OCT-22
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataDRGASIG_DISA.DBF' to '+DATA/orcl/datafile/SIG_DISA.279.1117806753';
Database altered.
SQL> alter tablespace SIG_DISA online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.259.1117373983
+DATA/orcl/datafile/sysaux.260.1117373985
+DATA/orcl/datafile/undotbs1.261.1117373985
+DATA/orcl/datafile/undotbs2.263.1117373991
+DATA/orcl/datafile/users.264.1117373991
+DATA/orcl/datafile/users.277.1117483957
+DATA/orcl/datafile/SIG_DISA.279.1117806753
7 rows selected.
节点2执行.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.259.1117373983
+DATA/orcl/datafile/sysaux.260.1117373985
+DATA/orcl/datafile/undotbs1.261.1117373985
+DATA/orcl/datafile/undotbs2.263.1117373991
+DATA/orcl/datafile/users.264.1117373991
+DATA/orcl/datafile/users.277.1117483957
+DATA/orcl/datafile/SIG_DISA.279.1117806753
7 rows selected.
结论：可以看到此数据文件在节点2能正常显示.
活动
天翼云最新优惠活动，涵盖免费试用，产品折扣等，助您降本增效！
11g rac添加数据文件至本地文件系统的异常处理演练
11g rac添加数据文件至本地文件系统的异常处理演练
2025-05-19 09:04:30 阅读次数：2
版权声明：本文内容来自第三方投稿或授权转载，原文地址：https://blog.51cto.com/u_12991611/5747879，作者：Liujun_Deng，版权归原作者所有。本网站转在其作品的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如因作品内容、版权等问题需要同本网站联系，请发邮件至ctyunbbs@chinatelecom.cn沟通。
上一篇：WCF 配置文件分解
作者介绍
天翼云用户
查看更多
最新文章
rac dg节点2在修改完alert_oracle_sid.log文件名，主库切换日志后备库节点2不产生新的日志文件
2025-05-13 09:51:17
【Linux 从基础到进阶】 文件系统的备份与恢复策略
2025-05-06 09:18:38
【Linux】磁盘分区|磁盘管理|/dev/sda|/dev/sdb|UUID
2025-02-21 08:58:12
【Linux】Linux内核学习--- 学习中
2025-02-21 08:56:43
文件系统和I/O设备管理
2024-12-24 10:25:10
oracle运用备库增量备份恢复另一个备库的gap—主库rac，备库文件系统
2024-12-23 09:16:28
查看更多
热门文章
linux中关于文件系统的简要介绍和一般操作
2023-05-18 09:30:25
弹性文件服务（Scalable File Service，SFS）
2024-06-12 09:30:16
Linux 文件系统基本介绍
2024-08-05 09:56:27
Linux内核之IO2：EXT文件系统详解(案例解析)
2024-08-05 09:52:39
Linux 的发展历史，设计哲学和一些常用的术语介绍
2024-08-05 09:56:27
Linux内核之IO3：文件系统一致性
2024-08-05 09:52:39
售前咨询热线
400-810-9889转1
关注天翼云
©2025
天翼云科技有限公司版权所有 增值电信业务经营许可证A2.B1.B2-20090001
公司地址：北京市东城区青龙胡同甲1号、3号2幢2层205-32室
