ORA-00205问题处理

Posted on 2011 年 08 月 23 日 by 惜分飞

1、启动数据库异常
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
ORA-00205: error in identifying control file, check alert log for more info

alert.log日志中记录
ALTER DATABASE MOUNT
Mon Jul 18 17:32:58 2011
ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/oradata/xienfei/control01.ctl]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory

2、根据提示缺少控制文件，第一步是看看有没有冗余的控制文件，然后修改pfile或者复制控制文件处理，如果没有利用备份控制文件恢复
RMAN> restore controlfile from ‘/tmp/rman_1kmhorc2_1_1’;

Starting restore at 18-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/oradata/xienfei/control01.ctl
Finished restore at 18-JUL-11

3、恢复控制文件后，数据库至于mount状态
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

4、添加归档日志到控制文件
RMAN> catalog start with ‘/u01/archive’;

Starting implicit crosscheck backup at 18-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
Crosschecked 29 objects
Crosschecked 12 objects
Finished implicit crosscheck backup at 18-JUL-11

Starting implicit crosscheck copy at 18-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
Crosschecked 1 objects
Finished implicit crosscheck copy at 18-JUL-11

searching for all files in the recovery area
cataloging files…
no files cataloged

searching for all files that match the pattern /u01/archive

List of Files Unknown to the Database
=====================================
…………
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
…………
RMAN> exit

[oracle@node2 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jul 18 17:43:47 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

5、使用备份控制文件恢复
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008’
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008’

6、发现控制文件后又新增数据文件，需要重命名控制文件中的新增数据文件
SQL> select file#,name from v$datafile where file#=8;

FILE# NAME
———- ———————————————-
8 /u01/oracle/dbs/UNNAMED00008

SQL> alter database rename file ‘/u01/oracle/dbs/UNNAMED00008’ to ‘/u01/oradata/xienfei/cfxff01.dbf’;

Database altered.

7、继续进行不完成恢复（利用归档日志）
SQL> recover database using backup controlfile;
ORA-00279: change 1158476 generated at 07/18/2011 16:43:27 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_1_756837539.arc
ORA-00280: change 1158476 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1158594 generated at 07/18/2011 16:44:01 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_2_756837539.arc
ORA-00280: change 1158594 for thread 1 is in sequence #2
ORA-00278: log file ‘/u01/archive/1_1_756837539.arc’ no longer needed for this
recovery

…………

ORA-00308: cannot open archived log ‘/u01/archive/1_7_756837539.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

8、根据提示，应该是归档日志恢复完成，需要利用联机日志继续恢复
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo03.log
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: ‘/u01/oradata/xienfei/redo03.log’

SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo01.log
Log applied.
Media recovery complete.

9、resetlogs打开数据库
SQL> alter database open resetlogs;

Database altered.

重建控制文件

Posted on 2011 年 08 月 23 日 by 惜分飞

#  使用alter database backup controlfile to trace;产生控制文件的trace file

-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
#  需要重建控制文件的话， 如果online redo 是可用的, 使用NORESETLOGS 创建新控制文件打开数据库
#  如果online redo是不可用的, 那么需要使用RESETLOGS 创建新控制文件打开数据库, 
#  联机日志被重置，数据有可能丢失
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
#   下面的命令用来创建一个新的控制文件来开启数据库。 用于RMAN的数据将会丢失。
#   对于offline datafiles 可能需要额外的归档日志用来做介质恢复， 
#   当当前版本的所有online redo log都没有问题的情况下使用noresetlogs。

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIENFEI" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2921
LOGFILE
  GROUP 1 '/u01/oradata/xienfei/redo01.log'  SIZE 5M,
  GROUP 2 '/u01/oradata/xienfei/redo02.log'  SIZE 5M,
  GROUP 3 '/u01/oradata/xienfei/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/xienfei/system01.dbf',
  '/u01/oradata/xienfei/undotbs01.dbf',
  '/u01/oradata/xienfei/sysaux01.dbf',
  '/u01/oradata/xienfei/users01.dbf',
  '/u01/oradata/xienfei/example01.dbf',
  '/u01/oradata/xienfei/xff01.dbf',
  '/u01/oradata/xienfei/cf01.dbf',
  '/u01/oradata/xienfei/cfxff01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_1_756837539.arc';
#  有些时候可能需要先注册归档日志，再才能够恢复
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
#  恢复数据库
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
#  归档所有日志
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
#  打开数据库
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
#  添加临时表空间
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/xienfei/temp01.dbf'
     SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

##    下面的RESETLOGS 重建语句， online redo log 中的内容都会丢失，所有的备份将无效 
##    仅仅在online log 损坏的情况下使用 resetlogs 创建控制文件开启数据库。  
##    resetlog创建控制文件后的开启步骤与noresetlog有一些不一样，请注意下面步骤

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIENFEI" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2921
LOGFILE
  GROUP 1 '/u01/oradata/xienfei/redo01.log'  SIZE 5M,
  GROUP 2 '/u01/oradata/xienfei/redo02.log'  SIZE 5M,
  GROUP 3 '/u01/oradata/xienfei/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/xienfei/system01.dbf',
  '/u01/oradata/xienfei/undotbs01.dbf',
  '/u01/oradata/xienfei/sysaux01.dbf',
  '/u01/oradata/xienfei/users01.dbf',
  '/u01/oradata/xienfei/example01.dbf',
  '/u01/oradata/xienfei/xff01.dbf',
  '/u01/oradata/xienfei/cf01.dbf',
  '/u01/oradata/xienfei/cfxff01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
#  注册归档日志
-- ALTER DATABASE REGISTER LOGFILE '/u01/archive/1_1_756837539.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
#  使用历史控制文件恢复数据库（因为redo log是新建，所以对于redo 而言，控制文件是新建的）
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
#  使用RESETLOGS方式打开数据库
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
#  添加临时表空间
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/xienfei/temp01.dbf'
     SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

ORA-01578坏块解决(2)

Posted on 2011 年 08 月 16 日 by 惜分飞

ORA-01578坏块解决(1)续集
如果在坏块之前，有rman备份，可以使用rman的备份来进行恢复，确保数据不会被丢失

1、使用rman进行恢复
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 22:21:13 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST (DBID=2056006906)

RMAN> blockrecover datafile 6 block 1477;

Starting blockrecover at 2011-08-14 22:21:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /tmp/0fmk0ii5_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/0fmk0ii5_1_1 tag=TAG20110814T213357
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

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

Finished blockrecover at 2011-08-14 22:21:23

2、检查坏块是否被恢复
RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:22:11
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=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:22:12

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:17 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,block#,blocks from v$database_block_corruption;

no rows selected

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:38 2011

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 2560
Total Pages Processed (Data) : 1372
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1256690 (0.1256690)

3、验证数据是否正确
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:34:18 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from t_rep;

COUNT(*)
———-
49857

和ORA-01578坏块解决(1)中的模拟环境比较，数据恢复正确，坏块问题解决

ORA-01578坏块解决(1)

Posted on 2011 年 08 月 15 日 by 惜分飞

一、创建测试表

SQL> create table t_rep as

2 select * from all_objects;

Table created.

SQL> select count(*) from t_rep;

COUNT(*)

———-

49857

二、使用bbed修改数据块

具体见bbed破坏数据文件

三、错误现象

1、sqlplus窗口

SQL> select count(*) from t_rep;

select count(*) from t_rep

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)

ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’

2、alert.log文件中

Sun Aug 14 22:01:14 2011

Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x28ef0602

check value in block header: 0x493

computed block checksum: 0x44b9

Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data

Sun Aug 14 22:01:15 2011

Corrupt Block Found

TSN = 6, TSNAME = XFF

RFN = 6, BLK = 1477, RDBA = 25167301

OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =

SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment

四、验证是否真的坏块

1、dbv验证

[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

Page 1477 is marked corrupt

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x28ef0602

check value in block header: 0x493

computed block checksum: 0x44b9

DBVERIFY – Verification complete

Total Pages Examined : 2560

Total Pages Processed (Data) : 1371

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 48

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 1140

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 1256043 (0.1256043)

2、rman验证

RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:09:51

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf

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

Finished backup at 2011-08-14 22:09:53

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,block#,blocks from v$database_block_corruption;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

FILE# BLOCK# BLOCKS

———- ———- ———-

6 1477 1

五、跳过坏块读取其他数据

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;

SKIP_COR

——–

ENABLED

SQL> select count(*) from t_rep;

COUNT(*)

———-

49794

说明：数据发生丢失6号文件的1477块中的数据丢失

bbed破坏数据文件

Posted on 2011 年 08 月 14 日 by 惜分飞

一、安装bbed

[oracle@ECP-UC-DB1 bin]$ cd $ORACLE_HOME/rdbms/lib

[oracle@ECP-UC-DB1 lib]$ ls -al *bb*

-rw-r–r– 1 oracle oinstall 1863 Mar 11 2008 sbbdpt.o

-rw-r–r– 1 oracle oinstall 1191 Mar 11 2008 ssbbded.o

[oracle@ECP-UC-DB1 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

[oracle@ECP-UC-DB1 lib]$ mv bbed $ORACLE_HOME/bin

[oracle@ECP-UC-DB1 lib]$ cd ~

二、bben入门

[oracle@ECP-UC-DB1 ~]$ bbed

Password: blockedit（默认该密码）

BBED: Release 2.0.0.0.0 – Limited Production on Sun Aug 14 19:56:10 2011

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

BBED> help all

SET DBA [ dba | file#, block# ]

SET FILENAME ‘filename’

SET FILE file#

SET BLOCK [+/-]block#

SET OFFSET [ [+/-]byte offset | symbol | *symbol ]

SET BLOCKSIZE bytes

SET LIST[FILE] ‘filename’

SET WIDTH character_count

SET COUNT bytes_to_display

SET IBASE [ HEX | OCT | DEC ]

SET OBASE [ HEX | OCT | DEC ]

SET MODE [ BROWSE | EDIT ]

SET SPOOL [ Y | N ]

SHOW [ <SET parameter> | ALL ]

INFO

MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]

</Nuf>:

N – a number which specifies a repeat count.

u – a letter which specifies a unit size:

b – b1, ub1 (byte)

h – b2, ub2 (half-word)

w – b4, ub4(word)

r – Oracle table/index row

f – a letter which specifies a display format:

x – hexadecimal

d – decimal

u – unsigned decimal

o – octal

c – character (native)

n – Oracle number

t – Oracle date

i – Oracle rowid

FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]

MODIFY[/x|d|u|o|c] numeric/character string

ASSIGN[/x|d|u|o] <target spec>=<source spec>

<source spec> : [ value | <target spec options> ]

SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]

PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]

POP [ALL]

REVERT [ DBA | FILE | FILENAME | BLOCK ]

UNDO

HELP [ <bbed command> | ALL ]

VERIFY [ DBA | FILE | FILENAME | BLOCK ]

CORRUPT [ DBA | FILE | FILENAME | BLOCK ]

三、创建测试表

[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 19:58:46 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_bbed (id number,name varchar2(100)) tablespace xff;

Table created.

SQL> insert into t_bbed values(1,’xifenfei’);

1 row created.

SQL> insert into t_bbed values(2,’xff’);

1 row created.

SQL> insert into t_bbed values(3,’chengfei’);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba

Connected.

SQL> alter system checkpoint;

System altered.

SQL> conn chf/xifenfei

Connected.

SQL> set serveroutput on

SQL> declare

ridtype number;

objnum number;

relfno number;

blno number;

rowno number;

rid rowid;

begin

select rowid into rid from t_bbed where id=1;

dbms_rowid.rowid_info(rid,ridtype,objnum,relfno,blno,rowno,’SMALLFILE’);

dbms_output.put_line(‘Row Typ-‘||to_char(ridtype));

dbms_output.put_line(‘Obj No-‘||to_char(objnum));

dbms_output.put_line(‘RFNO-‘||to_char(relfno));

dbms_output.put_line(‘Block No-‘||to_char(blno));

dbms_output.put_line(‘Row No-‘||to_char(rowno));

end;–查看rowid对应的文件号，块号等信息

Row Typ-1

Obj No-52721

RFNO-6

Block No-780

Row No-1

PL/SQL procedure successfully completed.

四、bben准备工作

1、创建参数文件

[oracle@ECP-UC-DB1 ~]$ cat parfile.conf

blocksize=8192

listfile=list

mode=edit

2、创建数据文件列表

[oracle@ECP-UC-DB1 ~]$ cat list

1 /opt/oracle/oradata/test/xifenfei01.dbf 2097160192

五、修改数据块中内容

[oracle@ECP-UC-DB1 ~]$ bbed parfile=parfile.conf

Password:

BBED: Release 2.0.0.0.0 – Limited Production on Sun Aug 14 20:35:41 2011

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

1、定位需要修改块

BBED> set dba 1,780

DBA 0x0040030c (4195084 1,780)

2、查看相关内容

BBED> find /c xifenfei

File: /opt/oracle/oradata/test/xifenfei01.dbf (1)

Block: 780 Offsets: 8180 to 8191 Dba:0x0040030c

————————————————————————

78696665 6e666569 01062bce

<32 bytes per line>

3、dump文件中内容

BBED> dump /v dba 1,780 offset 8180 count 32

File: /opt/oracle/oradata/test/xifenfei01.dbf (1)

Block: 780 Offsets: 8170 to 8191 Dba:0x0040030c

——————————————————-

7866662c 010202c1 02087869 66656e66 l xff,……xifenf

65690106 2bce l ei..+.

<16 bytes per line>

4、修改数据块内容

BBED> modify 500 dba 1,780

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /opt/oracle/oradata/test/xifenfei01.dbf (1)

Block: 780 Offsets: 8180 to 8191 Dba:0x0040030c

————————————————————————

01f46665 6e666569 01062bce

<32 bytes per line>

BBED> find /c xienfei

BBED-00212: search string not found

BBED> exit

六、验证修改是否成功

1、dbv验证

[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 20:30:56 2011

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

Page 780 is marked corrupt

Corrupt block relative dba: 0x0180030c (file 6, block 780)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x0180030c

last change scn: 0x0000.0012ce2b seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xce2b0601

check value in block header: 0x6382

computed block checksum: 0x1c

DBVERIFY – Verification complete

Total Pages Examined : 1280

Total Pages Processed (Data) : 690

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 31

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 558

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 1232338 (0.1232338)

2、rman验证

[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 20:39:55 2011

connected to target database: TEST (DBID=2056006906)

RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 20:40:06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf

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

Finished backup at 2011-08-14 20:40:08

RMAN> exit

Recovery Manager complete.

3、直接查询验证

[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 20:40:14 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,block#,blocks from v$database_block_corruption;

FILE# BLOCK# BLOCKS

———- ———- ———-

6 780 1

SQL> conn / as sysdba

Connected.

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> select * from chf.t_bbed;

select * from chf.t_bbed

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 780)

ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’

13429648788

qq咨询

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Category Archives: Oracle备份恢复

ORA-00205问题处理

重建控制文件

ORA-01578坏块解决(2)

ORA-01578坏块解决(1)

bbed破坏数据文件

2026 年十一月
S	M	T	W	T	F	S
« Oct
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30