环境： OEL 5.9 + 双节点RAC 11.2.0.4 + ASM存储
故障： 在节点1中运行相关命令后报ORA-01157 cannot identify/lock data file 19 - see DBWR trace file、ORA-01110错误，节点2正常
原因： 客户连接到RAC上（2节点），执行添加表空间数据文件时，误将数据文件存放在节点2的文件系统中了
解决（以下内容为在测试环境中演练时的记录）# 在节点2上执行
alter tablespace jxc add datafile 'F:appAdministratororadataorclBDCDJ.ORA1' size 500m autoextend on next 500m maxsize unlimited;
#alter tablespace jxc drop datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1';
# 节点2上查看
col file_name for a82
select a.file_name,a.bytes/1024/1024 "TOTAL(M)",b.sb/1024/1024 "FREE(M)",100*b.sb/a.bytes "FREE%"
from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
where a.file_id=b.file_id order by a.file_name;
#------------------------------------------------------------------------------------------------------------------------
FILE_NAME TOTAL(M) FREE(M) FREE%
------------------------------------------------------------ ---------- ---------- ----------
+DATA/erppn/datafile/goldengate.275.979402605 1024 1023 99.9023438
+DATA/erppn/datafile/jxc.295.979400455 20480 1408 6.875
+DATA/erppn/datafile/jxc.296.979400845 20480 3397 16.5869141
+DATA/erppn/datafile/jxc.297.979401041 20480 3228 15.7617188
+DATA/erppn/datafile/jxc.299.979400259 20480 2196 10.7226563
+DATA/erppn/datafile/jxc.304.979400649 20480 3581.875 17.489624
+DATA/erppn/datafile/jxc.305.979401237 20480 3492 17.0507813
+DATA/erppn/datafile/jxc.306.979401433 20480 2920 14.2578125
+DATA/erppn/datafile/jxc_index.256.979401823 20480 8948.8125 43.6953735
+DATA/erppn/datafile/jxc_index.289.979402019 20480 8942.875 43.6663818
+DATA/erppn/datafile/jxc_index.290.979402215 20480 8912.6875 43.5189819
+DATA/erppn/datafile/jxc_index.291.979402409 20480 8940.0625 43.6526489
+DATA/erppn/datafile/jxc_index.298.979401627 20480 8997.1875 43.9315796
+DATA/erppn/datafile/sysaux.280.979402635 860 5.625 .654069767
+DATA/erppn/datafile/system.277.979402621 1070 40.0625 3.74415888
+DATA/erppn/datafile/users.278.979402645 5 3.6875 73.75
+SSD/erppn/datafile/undotbs1.258.979408369 20480 20451.9375 99.8629761
+SSD/erppn/datafile/undotbs1.265.979408387 20480 20445.75 99.8327637
+SSD/erppn/datafile/undotbs2.264.979408409 20480 20465.3125 99.9282837
+SSD/erppn/datafile/undotbs2.291.979408429 20480 20461.6875 99.9105835
/u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministrator 500 499 99.8 ##
oradataorclBDCDJ.ORA1
#------------------------------------------------------------------------------------------------------------------------
# 节点1上查看
col file_name for a60
select a.file_name,a.bytes/1024/1024 "TOTAL(M)",b.sb/1024/1024 "FREE(M)",100*b.sb/a.bytes "FREE%"
from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
where a.file_id=b.file_id order by a.file_name;
#------------------------------------------------------------------------------------------------------------------------
from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
*
ERROR at line 2:
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
ORA-01110: data file 19: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1'
#------------------------------------------------------------------------------------------------------------------------
# 在节点2上执行摸拟插入数据
# drop table BFBHDD9.BB_HTSDXSFX_20180627 purge;
create table BFBHDD9.BB_HTSDXSFX_20180627 as select * from BFBHDD9.BB_HTSDXSFX;
# 表创建后再次查看datafile的使用情况
col file_name for a82
select a.file_name,a.bytes/1024/1024 "TOTAL(M)",b.sb/1024/1024 "FREE(M)",100*b.sb/a.bytes "FREE%"
from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
where a.file_id=b.file_id order by a.file_name;
#------------------------------------------------------------------------------------------------------------------------
FILE_NAME TOTAL(M) FREE(M) FREE%
---------------------------------------------------------------------------------- ---------- ---------- ----------
+DATA/erppn/datafile/goldengate.275.979402605 1024 1023 99.9023438
+DATA/erppn/datafile/jxc.295.979400455 20480 639.875 3.12438965
+DATA/erppn/datafile/jxc.296.979400845 20480 2565 12.5244141
+DATA/erppn/datafile/jxc.297.979401041 20480 2396 11.6992188
+DATA/erppn/datafile/jxc.299.979400259 20480 1364 6.66015625
+DATA/erppn/datafile/jxc.304.979400649 20480 2749 13.4228516
+DATA/erppn/datafile/jxc.305.979401237 20480 2660 12.9882813
+DATA/erppn/datafile/jxc.306.979401433 20480 2088 10.1953125
+DATA/erppn/datafile/jxc_index.256.979401823 20480 8948.8125 43.6953735
+DATA/erppn/datafile/jxc_index.289.979402019 20480 8942.875 43.6663818
+DATA/erppn/datafile/jxc_index.290.979402215 20480 8912.6875 43.5189819
+DATA/erppn/datafile/jxc_index.291.979402409 20480 8940.0625 43.6526489
+DATA/erppn/datafile/jxc_index.298.979401627 20480 8997.1875 43.9315796
+DATA/erppn/datafile/sysaux.280.979402635 860 5.625 .654069767
+DATA/erppn/datafile/system.277.979402621 1070 40.0625 3.74415888
+DATA/erppn/datafile/users.278.979402645 5 3.6875 73.75
+SSD/erppn/datafile/undotbs1.258.979408369 20480 20451.9375 99.8629761
+SSD/erppn/datafile/undotbs1.265.979408387 20480 20445.75 99.8327637
+SSD/erppn/datafile/undotbs2.264.979408409 20480 20465.3125 99.9282837
+SSD/erppn/datafile/undotbs2.291.979408429 20480 20461.6875 99.9105835
/u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1 500 51 10.2 ##
#------------------------------------------------------------------------------------------------------------------------
col name for a82
select INST_ID,NAME,BYTES/1024/1024 size_m from gv$datafile;
#------------------------------------------------------------------------------------------------------------------------
INST_ID NAME SIZE_M
---------- ---------------------------------------------------------------------------------- ----------
2 +DATA/erppn/datafile/system.277.979402621 1070
2 +DATA/erppn/datafile/sysaux.280.979402635 860
2 +SSD/erppn/datafile/undotbs1.258.979408369 20480
2 +DATA/erppn/datafile/users.278.979402645 5
2 +DATA/erppn/datafile/jxc.299.979400259 20480
2 +DATA/erppn/datafile/jxc.295.979400455 20480
2 +DATA/erppn/datafile/jxc.304.979400649 20480
2 +DATA/erppn/datafile/jxc.296.979400845 20480
2 +DATA/erppn/datafile/jxc.297.979401041 20480
2 +DATA/erppn/datafile/jxc.305.979401237 20480
2 +DATA/erppn/datafile/jxc.306.979401433 20480
2 +DATA/erppn/datafile/jxc_index.298.979401627 20480
2 +DATA/erppn/datafile/jxc_index.256.979401823 20480
2 +DATA/erppn/datafile/jxc_index.289.979402019 20480
2 +DATA/erppn/datafile/jxc_index.290.979402215 20480
2 +DATA/erppn/datafile/jxc_index.291.979402409 20480
2 +DATA/erppn/datafile/goldengate.275.979402605 1024
2 +SSD/erppn/datafile/undotbs1.265.979408387 20480
2 /u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1 500 ###
2 +SSD/erppn/datafile/undotbs2.264.979408409 20480
2 +SSD/erppn/datafile/undotbs2.291.979408429 20480
1 +DATA/erppn/datafile/system.277.979402621 1070
1 +DATA/erppn/datafile/sysaux.280.979402635 860
1 +SSD/erppn/datafile/undotbs1.258.979408369 20480
1 +DATA/erppn/datafile/users.278.979402645 5
1 +DATA/erppn/datafile/jxc.299.979400259 20480
1 +DATA/erppn/datafile/jxc.295.979400455 20480
1 +DATA/erppn/datafile/jxc.304.979400649 20480
1 +DATA/erppn/datafile/jxc.296.979400845 20480
1 +DATA/erppn/datafile/jxc.297.979401041 20480
1 +DATA/erppn/datafile/jxc.305.979401237 20480
1 +DATA/erppn/datafile/jxc.306.979401433 20480
1 +DATA/erppn/datafile/jxc_index.298.979401627 20480
1 +DATA/erppn/datafile/jxc_index.256.979401823 20480
1 +DATA/erppn/datafile/jxc_index.289.979402019 20480
1 +DATA/erppn/datafile/jxc_index.290.979402215 20480
1 +DATA/erppn/datafile/jxc_index.291.979402409 20480
1 +DATA/erppn/datafile/goldengate.275.979402605 1024
1 +SSD/erppn/datafile/undotbs1.265.979408387 20480
1 /u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1 0 ###
1 +SSD/erppn/datafile/undotbs2.264.979408409 20480
1 +SSD/erppn/datafile/undotbs2.291.979408429 20480
#------------------------------------------------------------------------------------------------------------------------
# 对比查看两节点report schema
# 节点2
rman target /
report schema;
#------------------------------------------------------------------------------------------------------------------------
Report of database schema for database with db_unique_name ERPPN
List of Permanent Datafiles
#===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1070 SYSTEM *** +DATA/erppn/datafile/system.277.979402621
2 860 SYSAUX *** +DATA/erppn/datafile/sysaux.280.979402635
3 20480 UNDOTBS1 *** +SSD/erppn/datafile/undotbs1.258.979408369
4 5 USERS *** +DATA/erppn/datafile/users.278.979402645
5 20480 JXC *** +DATA/erppn/datafile/jxc.299.979400259
6 20480 JXC *** +DATA/erppn/datafile/jxc.295.979400455
7 20480 JXC *** +DATA/erppn/datafile/jxc.304.979400649
8 20480 JXC *** +DATA/erppn/datafile/jxc.296.979400845
9 20480 JXC *** +DATA/erppn/datafile/jxc.297.979401041
10 20480 JXC *** +DATA/erppn/datafile/jxc.305.979401237
11 20480 JXC *** +DATA/erppn/datafile/jxc.306.979401433
12 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.298.979401627
13 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.256.979401823
14 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.289.979402019
15 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.290.979402215
16 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.291.979402409
17 1024 GOLDENGATE *** +DATA/erppn/datafile/goldengate.275.979402605
18 20480 UNDOTBS1 *** +SSD/erppn/datafile/undotbs1.265.979408387
19 500 JXC *** /u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1 ###
23 20480 UNDOTBS2 *** +SSD/erppn/datafile/undotbs2.264.979408409
24 20480 UNDOTBS2 *** +SSD/erppn/datafile/undotbs2.291.979408429
List of Temporary Files
#=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20480 TEMP 32767 +SSD/erppn/tempfile/temp.261.979408867
3 20480 TEMP 32767 +SSD/erppn/tempfile/temp.262.979408869
4 20480 TEMP 32767 +SSD/erppn/tempfile/temp.259.979408871
#------------------------------------------------------------------------------------------------------------------------
# 节点1
rman target /
report schema;
#------------------------------------------------------------------------------------------------------------------------
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ERPPN
List of Permanent Datafiles
#===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1070 SYSTEM *** +DATA/erppn/datafile/system.277.979402621
2 860 SYSAUX *** +DATA/erppn/datafile/sysaux.280.979402635
3 20480 UNDOTBS1 *** +SSD/erppn/datafile/undotbs1.258.979408369
4 5 USERS *** +DATA/erppn/datafile/users.278.979402645
5 20480 JXC *** +DATA/erppn/datafile/jxc.299.979400259
6 20480 JXC *** +DATA/erppn/datafile/jxc.295.979400455
7 20480 JXC *** +DATA/erppn/datafile/jxc.304.979400649
8 20480 JXC *** +DATA/erppn/datafile/jxc.296.979400845
9 20480 JXC *** +DATA/erppn/datafile/jxc.297.979401041
10 20480 JXC *** +DATA/erppn/datafile/jxc.305.979401237
11 20480 JXC *** +DATA/erppn/datafile/jxc.306.979401433
12 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.298.979401627
13 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.256.979401823
14 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.289.979402019
15 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.290.979402215
16 20480 JXC_INDEX *** +DATA/erppn/datafile/jxc_index.291.979402409
17 1024 GOLDENGATE *** +DATA/erppn/datafile/goldengate.275.979402605
18 20480 UNDOTBS1 *** +SSD/erppn/datafile/undotbs1.265.979408387
19 0 JXC *** /u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1 ###
23 20480 UNDOTBS2 *** +SSD/erppn/datafile/undotbs2.264.979408409
24 20480 UNDOTBS2 *** +SSD/erppn/datafile/undotbs2.291.979408429
List of Temporary Files
#=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20480 TEMP 32767 +SSD/erppn/tempfile/temp.261.979408867
3 20480 TEMP 32767 +SSD/erppn/tempfile/temp.262.979408869
4 20480 TEMP 32767 +SSD/erppn/tempfile/temp.259.979408871
#------------------------------------------------------------------------------------------------------------------------
# 节点1和2都打开到mount状态（否则会报错）
shutdown immediate
startup mount
exit
# 节点2上执行
rman target /
backup as copy datafile 19 format '+DATA';
#------------------------------------------------------------------------------------------------------------------------
Starting backup at 2018-06-27 16:08:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=630 instance=erppn1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1
output file name=+DATA/erppn/datafile/jxc.1126.979920535 tag=TAG20180627T160855 RECID=36 STAMP=979920536
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2018-06-27 16:08:56
#------------------------------------------------------------------------------------------------------------------------
switch datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1' to copy;
#------------------------------------------------------------------------------------------------------------------------
datafile 19 switched to datafile copy "+DATA/erppn/datafile/jxc.267.979923261"
#------------------------------------------------------------------------------------------------------------------------
# 两个节点open数据库
alter database open;
# 如果OPEN报错，用以下方法恢复
RUN{
SET NEWNAME FOR DATAFILE 19 to '/u01/app/oracle/product/11.2.0.4/db_1/dbs/F:appAdministratororadataorclBDCDJ.ORA1';
RESTORE DATAFILE 19;
SWITCH DATAFILE 19;
RECOVER DATAFILE 19;
}