[20160912]另类ORA-01157-文件大小不对-阿里云开发者社区

开发者社区> 数据库> 正文

[20160912]另类ORA-01157-文件大小不对

简介: [20160912]另类ORA-01157.txt --前一阵子帮别人解决的问题,当时没有记录,现在在自己的系统上再现。 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION ...

[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

--里面提到追加内容到结尾出现这个错误,具体内容没讲。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章