ALTER DATABASE OPEN /* db agent *//* {1:
55751:2} */
This instance was first to open
Errors in file /u01/oracle/diag/rdbms/mes/mes1/trace/mes1_dbw0_3585.trc:
ORA-
01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-
01110: data file 8: '+DBFS_DG/mes/datafile/dbfs_ogg_big.256.
833967963'
ORA-
17503: ksfdopn:2 Failed to open file +DBFS_DG/mes/datafile/dbfs_ogg_big.256.
833967963
ORA-
15001: diskgroup "DBFS_DG" does not exist or is not mounted
ORA-
15001: diskgroup "DBFS_DG" does not exist or is not mounted
Errors in file /u01/oracle/diag/rdbms/mes/mes1/trace/mes1_dbw0_3585.trc:
ORA-
01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-
01110: data file 9: '+DBFS_DG/mes/datafile/dbfs_ogg_small.257.
833968089'
ORA-
17503: ksfdopn:2 Failed to open file +DBFS_DG/mes/datafile/dbfs_ogg_small.257.
833968089
ORA-
15001: diskgroup "DBFS_DG" does not exist or is not mounted
ORA-
15001: diskgroup "DBFS_DG" does not exist or is not mounted
Abort recovery for domain 0
Errors in file /u01/oracle/diag/rdbms/mes/mes1/trace/mes1_ora_3645.trc:
ORA-
01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-
01110: data file 8: '+DBFS_DG/mes/datafile/dbfs_ogg_big.256.
833967963'
ORA-1157 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:
55751:2} */...
Fri Jan 17 14:36:55 2014
Shutting down instance (abort)
License high water mark = 1
USER (ospid: 3772): terminating the instance
Instance terminated by USER, pid = 3772
解决:
sysdba
startup 只能启动到mount状态
然后 sysdba用户执行
SQL> alter database datafile '+DBFS_DG/mes/datafile/dbfs_ogg_small.257.
833968089' offline;
Database altered.
使之open状态。
SQL> alter database open;
Database altered.
RAC + ASM单节点添加表空间的后果0
也许实际总要比想像更复杂,我们看一下以下测试。
首先在B节点创建ASM磁盘组:
SQL> create diskgroup testdg external redundancy disk '/dev/rdsk/c2t0d3s6';创建完成之后TESTDG磁盘组自动被装载。
Diskgroup created.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 ORADG MOUNTED 16384
2 TESTDG MOUNTED 927
此时在节点A,TESTDG并未加载:
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 ORADG MOUNTED 16384
0 TESTDG DISMOUNTED 0
如果在此状态下,在B节点创建表空间及数据表:
SQL> create tablespace eygle datafile '+TESTDG' size 10M;
Tablespace created.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 ORADG CONNECTED 16384
2 TESTDG CONNECTED 927
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+ORADG/eygle/datafile/system.256. 581100367
+ORADG/eygle/datafile/undotbs1.258. 581100367
+ORADG/eygle/datafile/sysaux.257. 581100367
+ORADG/eygle/datafile/users.259. 581100367
+ORADG/eygle/datafile/example.267. 581100507
+ORADG/eygle/datafile/undotbs2.268. 581100759
+TESTDG/eygle/datafile/eygle.256. 581636009
7 rows selected.
SQL> create table eygle tablespace eygle as select * from dba_users;
Table created.
SQL> select count(*) from eygle;
COUNT(*)
----------
27
此时在A节点查询会遇到如下错误:
SQL> select count(*) from eygle;
select count(*) from eygle
*
ERROR at line 1:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
我们可以在ASM中挂接磁盘组:
SQL> alter diskgroup testdg mount;
Diskgroup altered.
但是你会发现在数据库中仍然无法访问数据表:
SQL> select count(*) from eygle;
select count(*) from eygle
*
ERROR at line 1:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
告警日志文件记录了如下错误信息:
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
ORA- 17503: ksfdopn:2 Failed to open file +TESTDG/eygle/datafile/eygle.256. 581636009
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
Sun Feb 5 21:33:32 2006
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01186: file 7 failed verification tests
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
Sun Feb 5 21:33:32 2006
File 7 not verified due to error ORA- 01157
Sun Feb 5 21:33:32 2006
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
ORA- 17503: ksfdopn:2 Failed to open file +TESTDG/eygle/datafile/eygle.256. 581636009
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
Sun Feb 5 21:33:32 2006
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01186: file 7 failed verification tests
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
File 7 not verified due to error ORA- 01157
实际上也就是说DBWR无法锁定该文件,只能重启实例予以解决。
bash-2.05$ export ORACLE_SID=eygle1
bash-2.05$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 5 22:49:47 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1978336 bytes
Variable Size 385880096 bytes
Database Buffers 855638016 bytes
Redo Buffers 14794752 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+ORADG/eygle/datafile/system.256. 581100367
+ORADG/eygle/datafile/undotbs1.258. 581100367
+ORADG/eygle/datafile/sysaux.257. 581100367
+ORADG/eygle/datafile/users.259. 581100367
+ORADG/eygle/datafile/example.267. 581100507
+ORADG/eygle/datafile/undotbs2.268. 581100759
+TESTDG/eygle/datafile/eygle.256. 581636009
7 rows selected.
SQL> select count(*) from eygle;
COUNT(*)
----------
27
所以我们在RAC环境下的种种操作一定要认真慎重,不能马虎草率,切记切记。
RA-01157: 无法标识/锁定数据文件 10 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 10: 'D:\ORACLE\ORADATA\ORCL\USERS02.DBF'