标签：11g rac app datafile name orcl YES DATA 数据文件
文档课题：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,
app,
datafile,
name,
orcl,
YES,
DATA,
数据文件
From： https://blog.51cto.com/u_12991611/5747879