oracle-01122,记一次ORA-01122/ORA-01110/ORA-01200错误

SQL> startup

ORACLE instance started.

Total System Global Area  213909504 bytes

Fixed Size                  1266656 bytes

Variable Size             159386656 bytes

Database Buffers           50331648 bytes

Redo Buffers                2924544 bytes

Database mounted.

ORA-01122: database file 7 failed verification check

ORA-01110: data file 7: '/u02/oradata/crmdb/test01.dbf'

ORA-01200: actual file size of 99 is smaller than correct size of 1280 blocks

方法一：有备份直接还原

方法二：没有备份，而且这个文件没用

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> select status from v$Instance;

STATUS

------------

MOUNTED

SQL> alter database open;

Database altered.

方法三：没有备份，数据文件丢失的部分都是没有使用过的

[oracle@localhost crmdb]$ dd if=/dev/zero f= test01.dbf bs=8k seek=100 count=1181

读入了 1181+0 个块

输出了 1181+0 个块

SQL> alter database open;

Database altered.

当使用第三种方式修复后，使用DBV或者做rman时候将会提示：

RMAN> backup datafile 7;

Starting backup at 21-JAN-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00007 name=/u02/oradata/crmdb/test01.dbf

channel ORA_DISK_1: starting piece 1 at 21-JAN-10

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/21/2010 09:44:00

ORA-19566: exceeded limit of 0 corrupt blocks for file /u02/oradata/crmdb/test01.dbf

DBV:

DBVERIFY - Verification complete

Total Pages Examined         : 1280

Total Pages Processed (Data) : 1126

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 34

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 0

Total Pages Marked Corrupt   : 120

Total Pages Influx           : 0

Highest block SCN            : 2332545757 (2576.2332545757)

此时执行：create table for_fix2(a char(2)) tablespace test

SQL> begin

2  loop

3     insert into for_fix2 values(1);

4     commit;

5  end loop;

6  end;

7  /

alter system checkpoint;

DBVERIFY - Verification complete

Total Pages Examined         : 1280

Total Pages Processed (Data) : 1236

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 44

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 0

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 2332687823 (2576.2332687823)

RMAN> backup datafile 7;

Starting backup at 21-JAN-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00007 name=/u02/oradata/crmdb/test01.dbf

channel ORA_DISK_1: starting piece 1 at 21-JAN-10

channel ORA_DISK_1: finished piece 1 at 21-JAN-10

piece handle=/u01/app/oracle/dbs/19l40pm1_1_1 tag=TAG20100121T103439 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 21-JAN-10

Starting Control File and SPFILE Autobackup at 21-JAN-10

piece handle=/usr/Oracle/backup/script/full_back/auto_c_c-3778963517-20100121-00.bak comment=NONE

Finished Control File and SPFILE Autobackup at 21-JAN-10

RMAN>