[20160912]另类ORA-01157.txt
--前一阵子帮别人解决的问题，当时没有记录，现在在自己的系统上再现。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE TABLESPACE tea DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 10M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
RMAN> backup as copy datafile 6 format '/u01/backup/%b.20160912';
Starting backup at 2016-09-12 09:38:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf
output file name=/u01/backup/tea01.dbf.20160912 tag=TAG20160912T093830 RECID=3 STAMP=922354710
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2016-09-12 09:38:31
Starting Control File and SPFILE Autobackup at 2016-09-12 09:38:31
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_09_12/o1_mf_s_922354711_cxd1rqs1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-09-12 09:38:32
$ cd /u01/backup
$ cp tea01.dbf.20160912 tea01.dbf.xxx
--//使用vi打开tea01.dbf.xxx,再选择:wq保存。
$ ls -l tea01.dbf.*
-rw-r----- 1 oracle oinstall 10493952 2016-09-12 09:38:30 tea01.dbf.20160912
-rw-r----- 1 oracle oinstall 10493953 2016-09-12 09:41:37 tea01.dbf.xxx
--可以发现文件大小发生了变化。被修改的文件多了一个字节。
2.好了假设现在修改是真实的数据文件看看情况会如何。
SCOTT@book> create table empx tablespace tea as select * from emp ;
Table created.
SCOTT@book> alter system checkpoint ;
System altered.
--使用vi 打开/mnt/ramdisk/book/tea01.dbf,选择:wq保存。
$ ls -l tea01.dbf
-rw-r----- 1 oracle oinstall 10493953 2016-09-12 09:48:54 tea01.dbf
--//可以发现大小发生了变化+1.
SCOTT@book> update empx set ename='xxxx' ;
14 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
SYS@book> shutdown immediate
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-01208: data file is an old version - not accessing current version
SYS@book> shutdown abort
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
$ ll
total 2136940
-rw-r----- 1 oracle oinstall 9781248 2016-09-12 09:56:18 control01.ctl
-rw-r----- 1 oracle oinstall 9781248 2016-09-12 09:56:18 control02.ctl
-rw-r----- 1 oracle oinstall 346038272 2016-09-12 09:52:34 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 2016-09-11 17:01:00 redo01.log
-rw-r----- 1 oracle oinstall 52429312 2016-09-11 22:48:07 redo02.log
-rw-r----- 1 oracle oinstall 52429312 2016-09-12 09:51:38 redo03.log
-rw-r----- 1 oracle oinstall 681582592 2016-09-12 09:52:34 sysaux01.dbf
-rw-r----- 1 oracle oinstall 786440192 2016-09-12 09:52:34 system01.dbf
-rw-r----- 1 oracle oinstall 10493953 2016-09-12 09:52:08 tea01.dbf
-rw-r----- 1 oracle oinstall 30416896 2016-09-11 22:08:12 temp01.dbf
-rw-r----- 1 oracle oinstall 120594432 2016-09-12 09:52:34 undotbs01.dbf
-rw-r----- 1 oracle oinstall 31465472 2016-09-12 09:52:34 users01.dbf
--你可以发现tea01.dbf的文件大小显示显示的奇数。
$ dd if=tea01.dbf of=tea01.dbfx count=1 bs=10493952
1+0 records in
1+0 records out
10493952 bytes (10 MB) copied, 0.0225614 seconds, 465 MB/s
$ mv tea01.dbf tea01.dbf_bad
$ mv tea01.dbfx tea01.dbf
SYS@book> recover datafile 6;
Media recovery complete.
SYS@book> alter database open ;
Database altered.
SYS@book> select * from scott.empx where rownum<=2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 xxxx CLERK 7902 1980-12-17 00:00:00 800 20
7499 xxxx SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
3.也许你会问我什么想到这个的，实际上很简单，不知道谁建立的数据文件的后缀是txt.也不知道谁使用vi打开了这个文件。
又很神奇的使用:wq保存了这个文件。于是出现了问题，我有很神奇的发现文件大小后面是奇数。如果不是txt的后缀，也许
我会忽视这个细节。再次说明规范管理很重要。而且这个数据库是测试数据库，根本没有备份。
--实际上如果你通过alert*.log再查询 dbw0的跟踪文件，可以发现如下内容。
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
--通过这个也可以定位文件大小存在问题。我建立的数据文件大小10M,前面有一个OS的头占用1块。
--8192+10*1024*1024=10493952
$ oerr ora 1157
01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"
// *Cause: The background process was either unable to find one of the data
// files or failed to lock it because the file was already in use.
// The database will prohibit access to this file but other files will
// be unaffected. However the first instance to open the database will
// need to access all online data files. Accompanying error from the
// operating system describes why the file could not be identified.
// *Action: Have operating system make file available to database. Then either
// open the database or do ALTER SYSTEM CHECK DATAFILES.
--补充dbv检查结果：
$ dbv file=tea01.dbf.xxx
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Sep 13 07:46:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBV-00600: Fatal Error - [21] [5] [0] [0]
$ oerr dbv 600
600, 1, "Fatal Error - [%d] [%d] [%d] [%d]"
--不知道表示什么。
https://maleshg.files.wordpress.com/2014/01/undo-recovery-archive-mode-with-last-shutdown-abort.pdf
--里面提到追加内容到结尾出现这个错误，具体内容没讲。
