首页 > 其他分享 >ORA-01200

ORA-01200

时间：2023-08-02 23:07:05浏览次数：32  
标签：u01 backup tps01 dbf 01200 my ORA

客户XXX数据库服务器掉电，数据库 startup启动数据库遇到ORA-01200错误，信息如下：

SQL> startup;
ORACLE instance started.


Total System Global Area  997953536 bytes
Fixed Size                  2259400 bytes
Variable Size             515900984 bytes
Database Buffers          473956352 bytes
Redo Buffers                5836800 bytes
Database mounted.
ERROR at line 1:
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '+DG_DATA/orcl/my_tps01.dbf'
ORA-01200: actual file size of 12800 is smaller than correct size of 12928 blocks

问题原因

根据问题报错信息，可以判断是实际数据文件大小与控制文件和数据文件头中的信息不一致。

[oracle@node1 backup]$ oerr ora 01200
01200, 00000, "actual file size of %s is smaller than correct size of %s blocks"
// *Cause:  The size of the file as returned by the operating system is smaller
//         than the size of the file as indicated in the file header and the
//         control file. Somehow the file has been truncated. Maybe it is the
//         result of a half completed copy.
// *Action: Restore a good copy of the data file and do recovery as needed.

查看数据文件头中数据文件blocks数量和文件大小

SQL> select file#,name,blocks,bytes from v$datafile where file#=7;


     FILE# NAME                               BLOCKS      BYTES
---------- ------------------------------ ---------- ----------
         7 +DG_DATA/orcl/my_tps01.dbf          12928  105906176


SQL> select file#,name,blocks,bytes from v$datafile_header where file#=7;


     FILE# NAME                               BLOCKS      BYTES
---------- ------------------------------ ---------- ----------
         7 +DG_DATA/orcl/my_tps01.dbf          12928  105906176



ASMCMD> ls -l +DG_DATA/orcl/my_tps01.dbf
Type      Redund  Striped  Time             Sys  Name
                                            N    my_tps01.dbf => +DG_DATA/ORCL/DATAFILE/my_tps01.dbf.272.1123349667
 
ASMCMD> ls -ls +DG_DATA/ORCL/DATAFILE/my_tps01.dbf.272.1123349667
Type      Redund  Striped  Time             Sys  Block_Size  Blocks      Bytes      Space  Name
DATAFILE  UNPROT  COARSE   DEC 13 17:00:00  Y          8192   12801  104865792  106954752  my_tps01.dbf.272.1123349667

 

解决方案

弄清具体问题发生原因后，该问题有如下2种解决办法:

  • 方式1 ：通过dd命令填充数据文件实际大小为correct size of 12928中的值
  • 方式2 ：通过bbed 改数据文件头部偏移量44处的值为数据文件实际占用块数

方式1：dd命令填充数据文件

1. 将数据文件从ASM磁盘组中拷贝到本地文件系统上

[grid@node1 backup]$ asmcmd cp +DG_DATA/orcl/my_tps01.dbf /u01/backup/my_tps01.dbf
copying +DG_DATA/orcl/my_tps01.dbf -> /u01/backup/my_tps01.dbf

保留一个原始备份在文件系统

[grid@node1 backup]$ cp /u01/backup/my_tps01.dbf /u01/backup/my_tps01.dbf.org

[grid@node1 backup]$ chmod 775  /u01/backup/my_tps01.dbf

[grid@node1 backup]$ ls -l /u01/backup/my_tps01.dbf
-rwxrwxr-x 1 grid oinstall 104865792 Dec 13 17:38 /u01/backup/my_tps01.dbf

SQL> select 104865792 / 8192 from dual;


104865792/8192
--------------
        12801

每个数据文件的第一个块(block 0)是OS block header,因此数据文件实际大小比数据文件头记录的数量多一个block，也就是8K大小。

2.使用dd命令向数据文件中填入空块，使数据文件大小与文件头中记录的大小一致。

dd if=/dev/zero of=/u01/backup/my_tps01.dbf bs=8192 seek=12801 count=128 conv=notrunc


[oracle@node1 backup]$ ls -l /u01/backup/my_tps01.dbf
-rwxrwxr-x 1 grid oinstall 105914368 Dec 13 17:40 /u01/backup/my_tps01.dbf


SQL> select 105914368 / 8192 from dual;

         12929

3.将文件重名名到本地文件系统上的文件，并open打开数据库

SQL> alter database rename file '+DG_DATA/orcl/my_tps01.dbf' to '/u01/backup/my_tps01.dbf';


Database altered.


SQL> recover datafile 7;
Media recovery complete.


SQL> alter database open;


Database altered.

此时数据库可以open了，alert日志也无报错：

 

SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
Starting background process GTX0
Tue Dec 13 17:12:52 2022
GTX0 started with pid=39, OS id=29629 
Starting background process RCBG
Tue Dec 13 17:12:52 2022
RCBG started with pid=40, OS id=29631 
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Dec 13 17:12:52 2022
QMNC started with pid=41, OS id=29633 
Tue Dec 13 17:12:53 2022
minact-scn: Inst 1 is now the master inc#:2 mmon proc-id:18190 status:0x7
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
Completed: alter database open
Tue Dec 13 17:12:54 2022
Starting background process CJQ0
Tue Dec 13 17:12:54 2022
CJQ0 started with pid=42, OS id=29648

 

4.使用dbv校验数据文件，也无坏块，如下：

 

[oracle@node1 backup]$ dbv file=/u01/backup/my_tps01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Dec 13 17:13:52 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/backup/my_tps01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 12800
Total Pages Processed (Data) : 1243
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 155
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11402
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2297177 (0.2297177)

 

5.关闭数据库实例，把数据文件拷贝到ASM磁盘组中

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


[grid@node1 backup]$ asmcmd cp /u01/backup/my_tps01.dbf +DG_DATA/orcl/my_tps01.dbf
copying /u01/backup/my_tps01.dbf -> +DG_DATA/orcl/my_tps01.dbf

 

 

6.尝试使用rman backup as copy方式遇到ORA-19566坏块错误。

 

RMAN> backup as copy datafile 7 format '+DG_DATA/orcl/my_tps01.dbf';


Starting backup at 13-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/backup/my_tps01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/13/2022 17:26:13
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/backup/my_tps01.dbf

 

7.尝试使用备份集方式（跳过空块），同样遇到ORA-19566错误

 

RMAN>  backup as backupset datafile 7 format '/u01/backup/dbf7.bak';


Starting backup at 13-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/backup/my_tps01.dbf
channel ORA_DISK_1: starting piece 1 at 13-DEC-22
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/13/2022 17:53:45
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/backup/my_tps01.dbf

 

8.使用RMAN检查坏块，发现dd命令填充的数据块都被认作是坏块，如下：

 

RMAN> validate datafile 7;


Starting validate at 14-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=678 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u01/backup/my_tps01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              11402        12928           2297177   
  File Name: /u01/backup/my_tps01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1243            
  Index      0              0               
  Other      128            283             


validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_5350.trc for details
Finished validate at 14-DEC-22

 

由此可见，使用dd命令填充方式只能将数据库拉起，无法完美解决问题，还需要继续解决坏块问题。

 

方式2：bbed 修改文件头

1.删除修改过的文件，从原始备份拷贝一份

 

rm /u01/backup/my_tps01.dbf
cp /u01/backup/my_tps01.dbf.org /u01/backup/my_tps01.dbf

2.link bbed 过程省略。。。。。。

 

3.编辑bbed 参数文件

 

[oracle@node1 backup]$ cat filelist.txt 
7 /u01/backup/my_tps01.dbf 104857600


[oracle@node1 backup]$ cat bbed.par 
blocksize=8192
listfile=./filelist.txt
mode=edit
password=blockedit

4.使用bbed修改文件头blocks数量

 

oracle@node1 backup]$ bbed parfile=bbed.par 


BBED: Release 2.0.0.0.0 - Limited Production on Wed Dec 14 09:24:42 2022


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


************* !!! For Oracle Internal Use only !!! ***************


BBED> 
BBED> show all
        FILE#           7
        BLOCK#          1
        OFFSET          0
        DBA             0x01c00001 (29360129 7,1)
        FILENAME        /u01/backup/my_tps01.dbf
        BIFILE          bifile.bbd
        LISTFILE        ./filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No


BBED> set dba 7,1 offset 44
        DBA             0x01c00001 (29360129 7,1)
        OFFSET          44


BBED> p
kcvfh.kcvfhhdr.kccfhfsz
-----------------------
ub4 kccfhfsz                                @44       0x00003280




ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '+DG_DATA/orcl/my_tps01.dbf'
ORA-01200: actual file size of 12800 is smaller than correct size of 12928
blocks

现在需要将blocks数量12928改为12800，即0x3200

 

[grid@node1 ~]$ printf "%d\n" 0x3280
12928
 
[grid@node1 ~]$ printf "%x\n" 12800
3200




BBED> assign offset 44=0x3200    
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kccfhfsz                                @44       0x00003200


BBED> p
kcvfh.kcvfhhdr.kccfhfsz
-----------------------
ub4 kccfhfsz                                @44       0x00003200

或者使用 assign kcvfh.kcvfhhdr.kccfhfsz=0x3200

 

BBED> assign kcvfh.kcvfhhdr.kccfhfsz=0x3200
ub4 kccfhfsz                                @44       0x00003200


BBED> sum apply
Check value for File 7, Block 1:
current = 0xd4f1, required = 0xd4f1


BBED> exit

 

5.退出bbed，然后重启数据库，如下

 

SQL> startup;
ORACLE instance started.


Total System Global Area  997953536 bytes
Fixed Size                  2259400 bytes
Variable Size             515900984 bytes
Database Buffers          473956352 bytes
Redo Buffers                5836800 bytes
Database mounted.
Database opened.

6.使用DBV 和RMAN 校验数据块，均无坏块。

 

[oracle@node1 backup]$ dbv file=/u01/backup/my_tps01.dbf


DBVERIFY: Release 11.2.0.4.0 - Production on Wed Dec 14 09:33:51 2022


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


DBVERIFY - Verification starting : FILE = /u01/backup/my_tps01.dbf




DBVERIFY - Verification complete


Total Pages Examined         : 12800
Total Pages Processed (Data) : 1243
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 155
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11402
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2297177 (0.2297177)
[oracle@node1 backup]$ rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 14 09:33:57 2022


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: ORCL (DBID=1608114441)


RMAN> validate datafile 7;


Starting validate at 14-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=679 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u01/backup/my_tps01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              11402        12800           2297177   
  File Name: /u01/backup/my_tps01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1243            
  Index      0              0               
  Other      0              155             


Finished validate at 14-DEC-22

7.使用RMAN将数据文件7移动到ASM磁盘组

 

[grid@node1 ~]$ asmcmd rm -f +DG_DATA/orcl/my_tps01.dbf


[oracle@node1 backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 14 09:33:57 2022


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: ORCL (DBID=1608114441)


RMAN> backup as copy datafile 7 format '+DG_DATA/orcl/my_tps01.dbf';


Starting backup at 14-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/backup/my_tps01.dbf
output file name=+DG_DATA/orcl/my_tps01.dbf tag=TAG20221214T093552 RECID=2 STAMP=1123407354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-DEC-22


RMAN> sql 'alter database datafile 7 offline';


sql statement: alter database datafile 7 offline


RMAN> switch datafile 7 to copy;


datafile 7 switched to datafile copy "+DG_DATA/orcl/my_tps01.dbf"


RMAN> recover datafile 7;


Starting recover at 14-DEC-22
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:00


Finished recover at 14-DEC-22


RMAN> sql 'alter database datafile 7 online';


sql statement: alter database datafile 7 online






SQL> select file#,name,status,blocks,bytes from v$datafile;


     FILE# NAME                               STATUS      BLOCKS      BYTES
---------- ---------------------------------- ------- ---------- ----------
         1 +DG_DATA/orcl/system01.dbf         SYSTEM       98560  807403520
         2 +DG_DATA/orcl/sysaux01.dbf         ONLINE       71680  587202560
         3 +DG_DATA/orcl/undotbs01.dbf        ONLINE       13440  110100480
         4 +DG_DATA/orcl/users01.dbf          ONLINE         640    5242880
         5 +DG_DATA/orcl/example01.dbf        ONLINE       40080  328335360
         6 +DG_DATA/orcl/undotbs02.dbf        ONLINE        3200   26214400
         7 +DG_DATA/orcl/my_tps01.dbf         ONLINE       12800  104857600
         8 +DG_DATA/orcl/odc_tps01.dbf        ONLINE        1920   15728640

 

 

参考文档

When Performing Recover From Hot Backup ORA-01237 or ORA-01200 (Doc ID 1413507.1)

标签：u01,backup,tps01,dbf,01200,my,ORA
From： https://blog.51cto.com/u_13482808/6943946

相关文章

  • Amazon Aurora Serverless v2 正式发布：针对要求苛刻的工作负载的即时扩展
    我们非常兴奋地宣布，AmazonAuroraServerlessv2 现已面向AuroraPostgreSQL和MySQL正式发布。AuroraServerless是一种面向 AmazonAurora 的按需自动扩展配置，可让您的数据库根据应用程序的需求扩展或缩减容量。亚马逊云科技开发者社区为开发者们提供全球的开发技术......
  • 一个oracle视频资料比较丰富的地方
    一个oracle视频资料比较丰富的地方,推荐之,地址是http://www.boobooke.com/bbs/viewthread.php?tid=2926&extra=page%3D1以及其www.boobooke.com网站，也有不少linux的好视频......
  • 数据库事务的四种隔离性及Oracle\MySQL默认隔离级别和原因分析
    1事务一个事务中的一系列的处理操作要么全部成功，要么一个都不做。在数据库操作中，一项事务（Transaction）是由一条或多条操作数据库的SQL语句组成的一个不可分割的工作单元。事务的处理结果有两种：1）当事务中的所有步骤全部成功执行时，事务提交，成功；2）如果其中任何一个步骤失败，该事务......
  • Oracle备份数据
    存储过程CREATEORREPLACEPROCEDUREDATA_MIGRATION(endDayVARCHAR2)ISQUERY_SQLVARCHAR(10000);COM_STRVARCHAR(10000);BEGIN QUERY_SQL:='CREATETABLEGPS_OLD_'||REPLACE(endDay,'-','')||'ASSELECT*F......
  • oracle脚本
    ------------------------------------------------------------------------------------------------------------在查看数据的连接情况很有用，写完程序一边测试代码一边查看数据库连接的释放情况有助于分析优化出一个健壮的系统程序来。1、查看当前的数据库连接数selectcount......
  • 对Oracle数据库的一下操作脚本
    --进去oracle服务器数据库su-oraclesqlplus/assysdba--查看所有容器showpdbs--查看当前容器showcon_name;--打开相应容器alterpluggabledatabaseorclpdbopen;--切换容器altersessionsetcontainer=ORCLPDB1;--创建表空间，指定文件createtablespaceOPSCW_DAT......
  • Docker安装Oracle11g
    1.拉取oracle数据库镜像dockerpullregistry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g 2.启动oracle 自动启动镜像--restart=alwaysdockerrun-p1521:1521--nameoracle_11g-d--restart=alwaysregistry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g3......
  • Windows下Oracle11G定时备份
    首先我们先编写定时备份用到的脚本文件 我们新建txt文本文件，输入下列内容并保存为Oracle_bak.bat 把Oracle_bak.bat文件放到oracle的目录下（其他目录也可以）@echooffecho开始备份数据库#设置备份时间格式(使用cmd输入date查看服务器时间格式，按需设置)setvar=%date:~......
  • Windows服务器Oracle11G完全卸载详细教程
    Windows服务器Oracle11G安装详细教程（附Oracle11g安装程序）......
  • oracle坏块总结
    概念描述Oracle数据文件的坏块可以分为物理坏块（PhysicalBlockCorruptions）和逻辑坏块（LogicalBlockCorruptions）物理坏块指的是块格式本身已经损坏，块内的数据没有任何意义。物理坏块一般是由于内存问题、OS问题、I/O子系统问题或硬件引起的。物理块损坏也可以称为介质块损坏（Media......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99