记一次oracle数据库恢复
某客户给sysaux表空间添加了数据文件，但有在操作系统层rm 删除了数据文件，更悲催的的是之前归档所在的磁盘损坏，恢复所需要的归档那是没指望了，也一年多没进行过数据库备份了，现记录恢复过程如下：（虚拟机上重新模拟该恢复过程）
1 数据文件状态如下
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /oracle/CRM/ZBCRM/system01.dbf SYSTEM
2 /oracle/CRM/ZBCRM/sysaux01.dbf ONLINE
3 /oracle/CRM/ZBCRM/undotbs01.dbf ONLINE
4 /oracle/CRM/ZBCRM/users01.dbf ONLINE
5 /oracle/CRM/ZBCRM/sysaux02.dbf RECOVER
2 重新生成操作系统层删除的数据文件
SQL> alter database create datafile '/oracle/CRM/ZBCRM/sysaux02.dbf';
Database altered.
注意，当我们用 alter database create datafile 创建数据文件时，数据文件头部scn以及rba.seq号均取自于该数据文件创建时控制文件中记录的scn和rba.seq号.
3 此刻数据文件头部信息如下：
SQL> select hxfil,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHSCN FHRBA_SEQ
---------- ---------------- ----------
1 1047892 18
2 1047892 18
3 1047892 18
4 1047892 18
5 1026926 1
这里可以明确看出5号数据文件恢复需要从seq号为1的归档开始恢复，但实际上seq#=1及其之后的归档已经不存在了，所以如下恢复失败：
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/oracle/CRM/ZBCRM/sysaux02.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/CRM/ZBCRM/sysaux02.dbf'
4 用bbed调整5号数据文件如下几个偏移量
ub4 kcvfhcpc @140 ------检查点计数
ub4 kcvfhccc @148 ------总是比检查点计数少1
ub4 kcvcptim @492 ------检查点时间
ub4 kscnbas @484 ------scn的低位
ub2 kscnwrp @488 ------scn的高位
$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Jul 25 14:26:45 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File#Name Size(blks)
--------- ----------
1/oracle/CRM/ZBCRM/system01.dbf 89600
2/oracle/CRM/ZBCRM/sysaux01.dbf 65280
3/oracle/CRM/ZBCRM/undotbs01.dbf 8960
4/oracle/CRM/ZBCRM/users01.dbf 640
5/oracle/CRM/ZBCRM/sysaux02.dbf 12800
对@140的更改如下：
BBED> dump /v dba 2,1 offset 140 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:140 to159Dba:0x00800001
-------------------------------------------------------
6a000000 d562fa30 69000000 00000000 l j...誦?i.......
00000000 l ....
BBED> dump /v dba 5,1 offset 140 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:140 to159Dba:0x01400001
-------------------------------------------------------
01000000 00000000 01000000 00000000 l ................
00000000 l ....
BBED> modify /x 6a
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:140 to159 Dba:0x01400001
------------------------------------------------------------------------
6a000000 00000000 01000000 00000000 00000000
对@148的更改如下：
BBED> dump /v dba 2,1 offset 148 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:148 to167Dba:0x00800001
-------------------------------------------------------
69000000 00000000 00000000 00000000 l i...............
00000000 l ....
BBED> dump /v dba 5,1 offset 148 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:148 to167Dba:0x01400001
-------------------------------------------------------
01000000 00000000 00000000 00000000 l ................
00000000 l ....
BBED> modify /x 69
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:148 to167 Dba:0x01400001
------------------------------------------------------------------------
69000000 00000000 00000000 00000000 00000000
对@492的更改如下：
BBED> dump /v dba 2,1 offset 492 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:492 to511Dba:0x00800001
-------------------------------------------------------
d762fa30 01000000 12000000 02000000 l 譩?............
10000000 l ....
BBED> dump /v dba 5,1 offset 492 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:492 to511Dba:0x01400001
-------------------------------------------------------
065ffa30 01000000 01000000 c2530500 l ._?........耂..
10000000 l ....
BBED> modify /x d762fa30
BBED-00209: invalid number (d762fa30)
BBED> modify /x d762
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:492 to511 Dba:0x01400001
------------------------------------------------------------------------
d762fa30 01000000 01000000 c2530500 10000000
对@484的偏移量更改如下：
BBED> dump /v dba 2,1 offset 484 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:484 to503Dba:0x00800001
-------------------------------------------------------
54fd0f00 00000000 d762fa30 01000000 l T?.....譩?....
12000000 l ....
BBED> dump /v dba 5,1 offset 484 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:484 to503Dba:0x01400001
-------------------------------------------------------
6eab0f00 00000000 d762fa30 01000000 l n?.....譩?....
01000000 l ....
BBED> modify /x 54fd
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:484 to503 Dba:0x01400001
------------------------------------------------------------------------
54fd0f00 00000000 d762fa30 01000000 01000000
5 对比2号文件和5号文件的相关偏移量值是否相等如下：
BBED> dump /v dba 2,1 offset 140 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:140 to159Dba:0x00800001
-------------------------------------------------------
6a000000 d562fa30 69000000 00000000 l j...誦?i.......
00000000 l ....
BBED> dump /v dba 5,1 offset 140 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:140 to159Dba:0x01400001
-------------------------------------------------------
6a000000 00000000 69000000 00000000 l j.......i.......
00000000 l ....
BBED> dump /v dba 2,1 offset 148 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:148 to167Dba:0x00800001
-------------------------------------------------------
69000000 00000000 00000000 00000000 l i...............
00000000 l ....
BBED> dump /v dba 5,1 offset 148 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:148 to167Dba:0x01400001
-------------------------------------------------------
69000000 00000000 00000000 00000000 l i...............
00000000 l ....
BBED> dump /v dba 2,1 offset 492 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:492 to511Dba:0x00800001
-------------------------------------------------------
d762fa30 01000000 12000000 02000000 l 譩?............
10000000 l ....
BBED> dump /v dba 5,1 offset 492 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:492 to511Dba:0x01400001
-------------------------------------------------------
d762fa30 01000000 01000000 c2530500 l 譩?........耂..
10000000 l ....
BBED> dump /v dba 2,1 offset 484 count 20
File: /oracle/CRM/ZBCRM/sysaux01.dbf (2)
Block: 1 Offsets:484 to503Dba:0x00800001
-------------------------------------------------------
54fd0f00 00000000 d762fa30 01000000 l T?.....譩?....
12000000 l ....
BBED> dump /v dba 5,1 offset 484 count 20
File: /oracle/CRM/ZBCRM/sysaux02.dbf (5)
Block: 1 Offsets:484 to503Dba:0x01400001
-------------------------------------------------------
54fd0f00 00000000 d762fa30 01000000 l T?.....譩?....
01000000 l ....
BBED> sum apply
Check value for File 5, Block 1:
current = 0xc4ad, required = 0xc4ad
BBED> exit
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 25 14:38:22 2013
Copyright (c) 1982, 2010, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col name for a40
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /oracle/CRM/ZBCRM/system01.dbf SYSTEM
2 /oracle/CRM/ZBCRM/sysaux01.dbf ONLINE
3 /oracle/CRM/ZBCRM/undotbs01.dbf ONLINE
4 /oracle/CRM/ZBCRM/users01.dbf ONLINE
5 /oracle/CRM/ZBCRM/sysaux02.dbf RECOVER
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oracle/CRM/ZBCRM/sysaux02.dbf'
ORA-01207: file is more recent than control file - old control file
关于ora-01207的错误详细处理过程见：http://jiujian.blog.51cto.com/444665/1276674
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/diag/rdbms/zbcrm/ZBCRM/trace/ZBCRM_ora_8828.trc
SQL> exit
$ vi /oracle/app/diag/rdbms/zbcrm/ZBCRM/trace/ZBCRM_ora_8828.trc 找到如下创建控制文件语句：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ZBCRM" NORESETLOGSARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/CRM/ZBCRM/redo01.log'SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oracle/CRM/ZBCRM/redo02.log'SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oracle/CRM/ZBCRM/redo03.log'SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/CRM/ZBCRM/system01.dbf',
'/oracle/CRM/ZBCRM/sysaux01.dbf',
'/oracle/CRM/ZBCRM/undotbs01.dbf',
'/oracle/CRM/ZBCRM/users01.dbf',
'/oracle/CRM/ZBCRM/sysaux02.dbf'
CHARACTER SET ZHS16GBK
;
apparchivearchive2bbed.parbifile.bbdcontrol.sqlCRMdataDesktoperp.dbffilelog.bbdoraInventoryredo01.logtemp01.dbftest
$ ls -l control.sql
-rw-r--r-- 1 oracle oinstall 623 Jul 25 14:42 control.sql
$
$
$
$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ZBCRM" NORESETLOGSARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/CRM/ZBCRM/redo01.log'SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oracle/CRM/ZBCRM/redo02.log'SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oracle/CRM/ZBCRM/redo03.log'SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/CRM/ZBCRM/system01.dbf',
'/oracle/CRM/ZBCRM/sysaux01.dbf',
'/oracle/CRM/ZBCRM/undotbs01.dbf',
'/oracle/CRM/ZBCRM/users01.dbf',
'/oracle/CRM/ZBCRM/sysaux02.dbf'
CHARACTER SET ZHS16GBK
;
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 25 14:43:26 2013
Copyright (c) 1982, 2010, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2225832 bytes
Variable Size 704645464 bytes
Database Buffers 436207616 bytes
Redo Buffers 9371648 bytes
Control file created.
SQL> col name for a40
SQL> /
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /oracle/CRM/ZBCRM/system01.dbf SYSTEM
2 /oracle/CRM/ZBCRM/sysaux01.dbf ONLINE
3 /oracle/CRM/ZBCRM/undotbs01.dbf ONLINE
4 /oracle/CRM/ZBCRM/users01.dbf ONLINE
5 /oracle/CRM/ZBCRM/sysaux02.dbf RECOVER
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
页:
[1]