实验环境：Linux 6.4 + Oracle 11gR2 +RAC
（一）环境准备1.分别查询两节点数据库实例归档状态，"No Archive Mode"为非归档：
- SQL> archive log list;
- Database log mode No Archive Mode
- Auto某公司 archival Disabled
- Archive destination /u01/app/oracle/product/11.2.0.3/db_1/dbs/arch
- Oldest online log sequence 12
- Current log sequence 13
2.创建表空间及测试表，节点1操作
- SQL> create tablespace sxf_data datafile '+DATA_DATADG' size 15M autoextend off;
- Tablespace created.
- SQL> create table TT1 tablespace sxf_data AS select * from dba_objects;
- Table created.
3.继续插入数据，报表空间不足，目的为使第二次添加的数据文件(datafile)中分布有数据，节点1操作
- SQL> insert into TT1 select * from dba_objects;
- insert into TT1 select * from dba_objects
- *
- ERROR at line 1:
- ORA-01653: 表 SYS.TT1 无法通过 128 (在表空间 SXF_DATA 中) 扩展
- SQL> alter tablespace sxf_data add datafile '/home/oracle/sxf_data02' size 15M autoextend off;
- Tablespace altered.
- SQL> insert into TT1 select * from dba_objects;
- 86952 rows created.
- SQL> commit;
- Commit complete.
4.此时查看测试表空间的datafile，第二次添加数据文件为本地文件：
- set linesize 120
- col name format a60
- col file# format 9999
- col size_mb format 99999 --以上为格式制表符
- select f.file#, F.NAME, TRUNC(f.BYTES/1048576,2) SIZE_MB , f.CREATION_TIME, status
- FROM V$DATAFILE F,V$TABLESPACE T
- WHERE F.ts#=T.ts# AND T.NAME = 'SXF_DATA'
- order by f.CREATION_TIME;
- 节点1：
- FILE# NAME SIZE_MB CREATION_TIME STATUS
- ----- ------------------------------------------------------------ ------- ------------------- -------
- 8 +DATA_DATADG/ctidb/datafile/sxf_data.265.1010587371 15 2019-06-10 14:42:50 ONLINE
- 9 /home/oracle/sxf_data02 15 2019-06-10 14:45:35 ONLINE
5.节点2查询测试表：
- SQL> select count(1) from tt1;
- select count(1) from tt1
- *
- ERROR at line 1:
- ORA-01157: 无法标识/锁定数据文件 9 - 请参阅 DBWR 跟踪文件
- ORA-01110: 数据文件 9: '/home/oracle/sxf_data02'
6.查看节点2后台报错日志
- Errors in file /u01/app/oracle/diag/rdbms/ctidb/ctidb2/trace/ctidb2_dbw0_20428.trc:
- ORA-01186: file 9 failed verification tests
- ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
- ORA-01110: data file 9: '/home/oracle/sxf_data02'
注意：
因数据库为非归档模式，该报错发现越早越好。
1.发现该报错，立即停止业务，防止频繁的增删改使redo日志频繁切换；
2.设置表空间为只读，并offline数据文件(注：数据文件编号"9"为第一步环境准备第4小步"file#"得到)：
- SQL> alter database datafile 9 offline;
- alter database datafile 9 offline
- *
- ERROR at line 1:
- ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机
- SQL> alter tablespace SXF_DATA read only;
- Tablespace altered.
- SQL> alter database datafile 9 offline;
- Database altered.
3.重建数据文件，并查询数据文件状态(查询语句见第一.4不再重复)
- SQL> ALTER DATABASE CREATE DATAFILE 9 AS '+DATA_DATADG' size 15m ;
- Database altered.
4.在线恢复数据文件
注：此步依赖于在线日志（非归档），千万不可手动切换日志
- SQL> recover datafile 9;
- Media recovery complete.
5.数据文件ONLINE
- SQL> alter database datafile 9 online;
- Database altered.
6.数据文件ONLINE
- FILE# NAME SIZE_MB CREATION_TIME STATUS
- ----- ------------------------------------------------------------ ------- ------------------- -------
- 8 +DATA_DATADG/ctidb/datafile/sxf_data.265.1010587371 15 2019-06-10 14:42:50 ONLINE
- 9 +DATA_DATADG/ctidb/datafile/sxf_data.266.1010588435 15 2019-06-10 14:45:35 ONLINE
///////////////////////////*******///////////////////////////////
（三）当未停止业务导致日志切换频繁或者很长时间(假设2小时以上)才发现，会导致在线恢复时恢复不了，因为找不到归档日志了，报错：
- SQL> recover datafile 9;
- ORA-00279: change 1686019 generated at 06/10/2019 21:06:30 needed for thread 1
- ORA-00289: suggestion : +LOG
- ORA-00280: change 1686019 for thread 1 is in sequence #24
- Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
- auto
- ORA-00308: cannot open archived log '+LOG'
- ORA-17503: ksfdopn:2 Failed to open file +LOG
- ORA-15045: ASM file name '+LOG' is not in reference form
- ORA-00308: cannot open archived log '+LOG'
- ORA-17503: ksfdopn:2 Failed to open file +LOG
- ORA-15045: ASM file name '+LOG' is not in reference form</ret>
建议使用Chrome浏览器访问！
