Category Archives: Oracle备份恢复

bbed恢复删除数据

Posted on by

一、创建模拟环境
SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;

Table created.

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

1 row created.

SQL> insert into hr.xifenfei values(2,’xff’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.xifenfei;

ID NAME
———- ——————–
1 xifenfei
2 xff

SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from hr.xifenfei;

ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查询file#，block，后面恢复要用

SQL> delete from hr.xifenfei where id=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from hr.xifenfei;

ID NAME
———- ——————–
1 xifenfei

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

二、bbed恢复删除数据
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:

BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

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

BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0x00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No

BBED> set dba 2,530
DBA 0x00800212 (8389138 2,530)

BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0x00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2

<32 bytes per line>

BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0x00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….

<16 bytes per line>

BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0x00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …<......xff,.. 02c10208 78696665 6e666569 010680e2 l ....xifenfei.... <16 bytes per line>

BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0x00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….

<16 bytes per line>

BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0x00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .<......xff,.... 02087869 66656e66 65690106 80e2 l ..xifenfei.... <16 bytes per line>

BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0x00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l <......xff,..... 08786966 656e6665 69010680 e2 l .xifenfei.... <16 bytes per line>
通过尝试，推断出来3c的offset

BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0x00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2

<32 bytes per line>
修改3c为2c

BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9

三、核对结果
SQL> startup
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;

ID NAME
———- ——————–
1 xifenfei
2 xff

说明：
1）如果数据未删除：row flag的值为 32+8+4=44或者0x2c
2）如果数据被删除：row flag的值为 32+16+8+4=60或者0x3c

bbed修改数据内容

Posted on by
1、创建模拟表
SQL> create table hr.b(id number,name varchar2(100)) tablespace xff;
Table created.
SQL> insert into hr.b values(1,’aaa’);
1 row created.
Commit complete.
2、查看数据位置
QL>  select   rowid,
2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3   dbms_rowid.rowid_block_number(rowid)blockno,
4   dbms_rowid.rowid_row_number(rowid) rowno
5   from hr.b;
ROWID                 REL_FNO    BLOCKNO      ROWNO
—————— ———- ———- ———-
AAAHytAACAAAAIKAAA          2        522          0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
虽然bbed 可以在db open 状态来进行修改，但是建议在做任何修改操作之前先shutdown db。 这样避免checkpoint 进程重写bbed 对block 的修改。 也避免oracle 在bbed 修改完成之前读block 或者申明block 为corrupt。
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf（/tmp/parfile.cnf文件见bbed破坏数据文件）
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Sat Aug 20 17:10:24 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE#           2
BLOCK#          1
OFFSET          0
DBA             0x00800001 (8388609 2,1)
FILENAME        /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE          bifile.bbd
LISTFILE        /tmp/list
BLOCKSIZE       8192
MODE            Edit
EDIT            Unrecoverable
IBASE           Dec
OBASE           Dec
WIDTH           80
COUNT           512
LOGFILE         log.bbd
SPOOL           No
3、设置dba位置
BBED> set dba 2,522
DBA             0x0080020a (8389130 2,522)
4、查询aaa位置
BBED> find /c aaa
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522              Offsets: 8185 to 8191           Dba:0x0080020a
————————————————————————
61616101 067735
<32 bytes per line>
5、查看该位置内容
BBED> dump /v offset 8185
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522     Offsets: 8185 to 8191  Dba:0x0080020a
——————————————————-
61616101 067735                     l aaa..w5
<16 bytes per line>
BBED> show all
FILE#           2
BLOCK#          522
OFFSET          8185
DBA             0x0080020a (8389130 2,522)
FILENAME        /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE          bifile.bbd
LISTFILE        /tmp/list
BLOCKSIZE       8192
MODE            Edit
EDIT            Unrecoverable
IBASE           Dec
OBASE           Dec
WIDTH           80
COUNT           512
LOGFILE         log.bbd
SPOOL           No
6、修改aaa为bbb
BBED> modify /c bbb
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522              Offsets: 8185 to 8191           Dba:0x0080020a
————————————————————————
62626201 067735
<32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522     Offsets: 8185 to 8191  Dba:0x0080020a
——————————————————-
62626201 067735                     l bbb..w5
<16 bytes per line>
7、应用变更
BBED> sum
Check value for File 2, Block 522:
current = 0xa285, required = 0xa286
此时 current checksum 是0xa285，requiredchecksum 是0xa286
BBED> sum apply
Check value for File 2, Block 522:
current = 0xa286, required = 0xa286
加上apply参数，使checksum一致。即之前的修改生效。
8、开启数据测试
SQL> startup
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> col name for a20
SQL> select * from hr.b;
ID NAME
———- ——————–
1 bbb

ORA-01244/ORA-01110解决

Posted on by

rman 恢复过程中出现以下错误

RMAN> recover database;

Starting recover at 20-AUG-11
using channel ORA_DISK_1

starting media recovery

archive log filename=/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/20/2011 03:54:30
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'

错误原因分析
在rman备份后，添加了数据文件，使用的是备份的控制文件进行恢复数据库导致（不能识别新的数据文件）

解决方法
通过sqlplus创建数据文件
SQL> alter database create datafile 2 as ‘/opt/oracle/oradata/xifenfei/xff01.dbf’;

Database altered.

然后继续在rman中执行恢复数据库操作

该情况说明
此中情况只有在oracle 9i中出现；在10g中，rman恢复过程会自动的创建新添加文件，见oracle 10g rman自动创建数据文件

undo异常处理步骤（9i）

Posted on by

1、启动数据库，发现错误
startup

2、查看是否是undo文件损坏引起，并查看是否是当前undo，不是当前undo直接offline，然后open数据库，再删除掉该数据该undo即可
select a.ts#,a.name,b.name from v$datafile a,v$tablespace b where a.ts#=b.ts#;
show parameter undo_tablespace;

3、损坏undo离线，创建pfile文件
alter database datafile n offline drop;
create pfile=’/tmp/pfile’ from spfile;

4、打开数据库,如果打开失败，请继续5，如果成功按照undo异常处理步骤（10g）方法处理
alter database open;

5、如果数据库不能正常打开，而是提示，如下错误：
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

6、检查日志文件，发现如下：
SMON: about to recover undo segment 21
SMON: mark undo segment 21 as needs recovery
SMON: about to recover undo segment 22
SMON: mark undo segment 22 as needs recovery
SMON: about to recover undo segment 23
SMON: mark undo segment 23 as needs recovery
SMON: about to recover undo segment 24
SMON: mark undo segment 24 as needs recovery
SMON: about to recover undo segment 25
SMON: mark undo segment 25 as needs recovery
SMON: about to recover undo segment 26
SMON: mark undo segment 26 as needs recovery
SMON: about to recover undo segment 27
SMON: mark undo segment 27 as needs recovery
SMON: about to recover undo segment 28
SMON: mark undo segment 28 as needs recovery
SMON: about to recover undo segment 29
SMON: mark undo segment 29 as needs recovery
SMON: about to recover undo segment 30
SMON: mark undo segment 30 as needs recovery

7、编辑pfile文件，内容为
*.undo_management=’MANUAL’
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,
_SYSSMU25$,_SYSSMU26$,_SYSSMU27$,_SYSSMU28$,_SYSSMU29$,_SYSSMU30$)
*.undo_tablespace=’SYSTEM’

8、退出当前sqlplus，重新登录，利用pfile启动数据库
startup

9、创建新undo表空间
CREATE UNDO TABLESPACE UNDOTBSNEW DATAFILE
‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf
SIZE 50M autoextend on next 10m maxsize 30G;

10、查询回滚段信息，为下面删除损坏undo的回滚段做到心中有底
select segment_name,status from dba_rollback_segs;

11、删除损坏undo
drop tablespace UNDOTBSOLD including contents and datafiles;

12、查看回滚段状态
select segment_name,status from dba_rollback_segs;

13、如果有损坏表空间回滚段还存在，手工删除
drop rollback segment “_SYSSMUx$”;

14、修改pfile内容
*.undo_management=’AUTO’
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,
_SYSSMU25$,_SYSSMU26$,_SYSSMU27$,_SYSSMU28$,_SYSSMU29$,_SYSSMU30$)
*.undo_tablespace=’UNDOTBSNEW’

15、重启数据库
shutdown immediate
startup

说明：可以先删除需要恢复的回滚段，再删除损坏的undo表空间

undo异常处理步骤（10g）

Posted on by

1、启动数据库，发现错误
startup

2、查看是否是undo文件损坏引起
select a.ts#,a.name,b.name from v$datafile a,v$tablespace b where a.ts#=b.ts#;

3、损坏undo离线
alter database datafile n offline drop;

4、打开数据库
alter database open;

5、查看当前undo表空间，并确认是否是当前undo损坏，如果不是当前undo，直接执行8，否则继续
show parameter undo_tablespace;

6、创建新undo表空间
CREATE UNDO TABLESPACE UNDOTBSNEW DATAFILE
‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf
SIZE 50M autoextend on next 10m maxsize 30G;

7、修改默认undo为新创建表空间
alter system set undo_tablespace=’UNDOTBSNEW’;

8、查询回滚段信息，为下面删除损坏undo的回滚段做到心中有底
select segment_name,status from dba_rollback_segs;

9、删除损坏undo，如果报ORA-01548错误，继续下面操作，否则跳到14
drop tablespace UNDOTBSOLD including contents and datafiles;

10、创建pfile文件
create pfile=’/tmp/pfile’ from spfile;

11、修改pfile文件，添加隐含参数
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,
_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

12、使用pfile启动数据库
startup pfile=’/tmp/pfile’

13、删除损坏undo
drop tablespace UNDOTBSOLD including contents and datafiles;

14、查看回滚段状态
select segment_name,status from dba_rollback_segs;

15、如果有损坏表空间回滚段还存在，手工删除
drop rollback segment “_SYSSMUx$”;

16、重启数据库
shutdown immediate
startup