mysql日志丢失,记一次数据库在线日记全部丢失故障恢复处理

当前位置:我的异常网» 数据库 » 记一次数据库在线日记全部丢失故障恢复处理

记一次数据库在线日记全部丢失故障恢复处理

www.myexceptions.net  网友分享于&＃xff1a;2013-09-29  浏览&＃xff1a;11次

记一次数据库在线日志全部丢失故障恢复处理

数据库版本为

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

运行在linux系统中

[ora10g&＃64;hzmc admin]$ uname -a

Linux hzmc 2.6.18-53.el5xen #1 SMP Mon Nov 12 03:26:12 EST 2007 i686 i686 i386 GNU/Linux

由于数据库的online redolog全部丢掉&＃xff0c;导致数据库在open阶段时出现以下错误

alter database open

Thu Nov  3 16:55:02 2011

Beginning crash recovery of 1 threads

parallel recovery started with 2 processes

Thu Nov  3 16:55:02 2011

Started redo scan

Thu Nov  3 16:55:02 2011

Errors in file /ora10g/admin/drb/udump/drb_ora_14761.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: &＃39;/Tbackup/drb/redo01_1.log&＃39;

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: &＃39;/Tbackup/drb/redo01.log&＃39;

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thu Nov  3 16:55:02 2011

Aborting crash recovery due to error 313

Thu Nov  3 16:55:02 2011

Errors in file /ora10g/admin/drb/udump/drb_ora_14761.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: &＃39;/Tbackup/drb/redo01_1.log&＃39;

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: &＃39;/Tbackup/drb/redo01.log&＃39;

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

我们知道数据库在异常宕机&＃xff0c;再次open数据库时需要扫描online redolog&＃xff0c;从而确保数据不丢失。如果redlog丢失&＃xff0c;存储在数据库里的业务数据可能出现不一致状态。

(如果redolog完好&＃xff0c;数据库open过程中会进行事物恢复&＃xff0c;从而保证事物的一致性)但目前问题的难点是在线日志已经全部丢失的情况下怎么打开数据库&＃xff1f;从而导出业务数据。

在这种情况下&＃xff0c;我们首先想到了一个隐含参数_allow_resetlogs_corruption&＃xff0c;该隐含参数Oracle的解释是&＃xff1a;allow resetlogs even if it will cause corruption&＃xff0c;

也就是说在数据库打开过程中&＃xff0c;如果碰到处于current或者active状态的redlog损坏&＃xff0c;当该参数置为true时&＃xff0c;Oracle将直接跳过redolog恢复&＃xff0c;并打开数据库。当然&＃xff0c;在打开数据库的过程中&＃xff0c;

Oracle将会重建redolog。基于目前这个情况&＃xff0c;我们将参数_allow_resetlogs_corruption置为true&＃xff0c;并尝试打开数据库。

SQL> alter system set "_allow_resetlogs_corruption"&＃61;true scope&＃61;spfile;

System altered.

由于需要重建redolog&＃xff0c;所以需要将数据库进行不完全恢复&＃xff0c;这样数据库open的时候才能使用resetlogs选项&＃xff0c;所以我们在设好_allow_resetlogs_corruption参数并重启数据库至mount状态之后

对数据库做了不完全恢复。

SQL> recover database until cancel;

ORA-00279: change 11000485117844 generated at 11/03/2011 16:36:32 needed for

thread 1

ORA-00289: suggestion :

/ora10g/oracle/product/10.2.0/db_1/dbs/arch1_2_766254516.dbf

ORA-00280: change 11000485117844 for thread 1 is in sequence #2

Specify log: {&＃61;suggested | filename | AUTO | CANCEL}

cancel

再次尝试打开数据库&＃xff0c;这时候意想不到事情发生了&＃xff0c;数据库中竟然有部分数据文件的resetlogs_change#处于不一致状态。

SQL> alter database open RESETLOGS;

alter database open RESETLOGS

*

ERROR at line 1:

ORA-01190: control file or data file 11 is from before the last RESETLOGS

ORA-01110: data file 11: &＃39;/Tbackup/drb/wentest03.dbf&＃39;

SQL> select resetlogs_change#,file# from v$datafile_header;

RESETLOGS_CHANGE#      FILE#

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

11000485117845          1

11000485117845          2

11000485117845          3

11000485117845          4

。。。

11000485117845         10

456954         11

RESETLOGS_CHANGE#      FILE#

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

456954         12

456954         13

11000485117845         14

11000485117845         15

11000485117845         16

11000485117845         17

。。。

11000485117845         37

11000485117845         38

9745339313660         39

。。。

11000485117845         51

11000485117845         52

进一步查看数据文件状态&＃xff0c;可以看到11,12,13号数据文件处于online状态&＃xff0c;这几个数据文件必须要进行处理&＃xff0c;否则业务数据将丢失&＃xff0c;而39号数据文件在数据库正常使用时&＃xff0c;已经处于offline状态

可以暂时不管&＃xff0c;如果有时间&＃xff0c;再进行处理也不迟。

SQL> select file#,status from v$datafile where file# in (11,12,13,39);

FILE# STATUS

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

11 ONLINE

12 ONLINE

13 ONLINE

39 OFFLINE

SQL> select name from v$datafile where file# in (11,12,13);

NAME

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

/Tbackup/drb/wentest03.dbf

/Tbackup/drb/wentest02.dbf

/Tbackup/drb/wentest01.dbf

首先我们需要清晰一个概念&＃xff0c;就是Oracle在open数据库时&＃xff0c;需要对数据文件进行一系列检查&＃xff0c;主要检查该数据文件是不是本数据库的文件文件(主要检查db_id,db_name),

数据文件号和存储在滋控制文件的数据文件号是否一致(file#,rfile#),数据文件的checkpoint change和checkpoint count是否和控制文件的checkpoint change和checkpoint count一致&＃xff0c;

正常状态下数据文件的resetlogs change和resetlogs是否处于一致等等。

以下就是Oracle 10g下&＃xff0c;各个检查项在block 1中的位置&＃xff1a;

Block 1&＃xff1a;

offset&＃xff1a;0028――0031

这四个bytes存放的dbid

offset&＃xff1a;0032――0039

这个8个bytes存放的是数据库名的ascii码表示

offset&＃xff1a;0040――0041

这两个字节存放的是Control Seq

offset&＃xff1a;0052――0053

这两个字节存放的是文件号

offset&＃xff1a;0112――0115

这四个字节存放的是reset logs count

offset&＃xff1a;0116――0123

这六个字节存放的是reset logs scn

offset&＃xff1a;0148――0149

这两个字节表示数据文件ctl cnt

offset&＃xff1a;0368――00372

这四格字节表示相对文件号

offset&＃xff1a;0484――0489

这六个字节存放的是checkpoint scn

进一步我们通过dump数据文件头&＃xff0c;发现了一些问题&＃xff0c;resetlogs_change&＃xff0c;resetlog count&＃xff0c;file number都不准确

SQL> ALTER SESSION SET EVENTS &＃39;immediate trace name file_hdrs level 10&＃39;;

Session altered.

DATA FILE #11:

(name #19) /Tbackup/drb/wentest03.dbf

creation size&＃61;2560 block size&＃61;8192 status&＃61;0xf head&＃61;19 tail&＃61;19 dup&＃61;1

tablespace 11, index&＃61;12 krfil&＃61;12 prev_file&＃61;0

unrecoverable scn: 0x0883.33ab315d 01/01/1988 00:00:00

Checkpoint cnt:4 scn: 0x0883.33a89fae 04/16/2009 02:27:10

Stop scn: 0x0883.33a89fae 01/01/1988 00:00:00

Creation Checkpointed at scn:  0x0883.33a89f0c 04/16/2009 02:21:30

thread:0 rba:(0x0.0.0)

...

aux_file is NOT DEFINED

File 11 with tablespace ID 11 is plugged in read only

V10 STYLE FILE HEADER:

Compatibility Vsn &＃61; 169870080&＃61;0xa200300

Db ID&＃61;3342305182&＃61;0xc737879e, Db Name&＃61;&＃39;DRB&＃39;

Activation ID&＃61;0&＃61;0x0

Control Seq&＃61;93349&＃61;0x16ca5, File size&＃61;2560&＃61;0xa00

File Number&＃61;12, Blksiz&＃61;8192, File Type&＃61;3 DATA

Tablespace #11 - WEN  rel_fn:12

Creation   at   scn: 0x0883.33a89f0c 04/16/2009 02:21:30

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x2803df21 scn: 0x0a01.4001ff95 reset logs terminal rcv data:0x0 scn: 0x

0000.00000000

prev reset logs count:0x24293a18 scn: 0x0000.00000001 prev reset logs terminal rcv data:0

x0 scn: 0x0000.00000000

recovered at 01/01/1988 00:00:00

status:0x0 root dba:0x00000000 chkpt cnt: 4 ctl cnt:3

begin-hot-backup file size: 0

Checkpointed at scn:  0x0883.33a89fae 04/16/2009 02:27:10

thread:1 rba:(0x5d12.14eb5.10)

现在我们的目标很清晰&＃xff0c;就是首先需要用bbed修复数据文件11,12,13的resetlogs_change&＃xff0c;resetlog count&＃xff0c;file number。以修改11号数据文件为例&＃xff0c;12号&＃xff0c;13号修改方式类似

[ora10g&＃64;hzmc ~]$ bbed listfile&＃61;l blocksize&＃61;8192 password&＃61;blockedit

BBED> dump offset 112

BBED> modify 0xb724

BBED> dump offset 114

BBED> modify 0xac2d

BBED> dump offset 116

BBED> modify 0x95ff

BBED> dump offset 118

BBED> modify 0x0140

BBED> dump offset 52

BBED> modify 0x0b

BBED> sum apply

修改完成之后&＃xff0c;再次进行不完全恢复&＃xff0c;结果命令hang&＃xff0c;问题又再次出现。

SQL> recover database until cancel;

后台日志显示&＃xff1a;

/ora10g/admin/drb/udump/drb_ora_31042.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME &＃61; /ora10g/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      hzmc

Release:        2.6.18-53.el5xen

Version:        #1 SMP Mon Nov 12 03:26:12 EST 2007

Machine:        i686

Instance name: drb

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 31042, image: oracle&＃64;hzmc (TNS V1-V3)

*** SERVICE NAME:() 2011-11-03 17:23:58.006

*** SESSION ID:(159.3) 2011-11-03 17:23:58.006

*** 2011-11-03 17:23:58.006

Beginning recovery file header examination (51 files)

*** 2011-11-03 17:23:58.007

Completed recovery file header examination

*** 2011-11-03 17:23:58.007

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [2130], [0], [8], [2], [], [], [], []

Current SQL statement for this session:

ALTER DATABASE RECOVER  database until cancel

----- Call Stack Trace -----

calling              call     entry                argument values in hex

location             type     point                (? means dubious value)

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

ksedst()&＃43;27          call     ksedst1()            0 ? 1 ?

ksedmp()&＃43;557         call     ksedst()             0 ? 9B8E606 ? 9B8E606 ? 155 ?

0 ? 0 ?

ksfdmp()&＃43;19          call     ksedmp()             3 ? BFF69F8C ? AD0710C ?

CD49D00 ? 3 ? CCF9E38 ?

kgeriv()&＃43;188         call     00000000             CD49D00 ? 3 ?

kgeasi()&＃43;113         call     kgeriv()             CD49D00 ? B7F70020 ? 852 ?

3 ? BFF69FC8 ?

kccugg()&＃43;433         call     kgeasi()             CD49D00 ? B7F70020 ? 852 ?

2 ? 3 ? 0 ?

kcc_get_record()&＃43;32  call     kccugg()             1 ? BFF6B58C ? 0 ? BFF6A1F0 ?

2 ? 200 ?

kccgri()&＃43;31          call     kcc_get_record()     BFF6B58C ? 0 ? BFF6A1F0 ? 2 ?

BFF6A2F0 ?

kctgdc()&＃43;52          call     kccgri()             BFF6B58C ? 0 ? BFF6A1F0 ?

BFF6B58C ? BFF6A0AC ?

krdsmr()&＃43;16009       call     kctgdc()             BFF6B58C ? BFF6AF58 ?

同时查看数据等待事件,数据库处于enq: CF - contention等待&＃xff0c;也就是说数据库在不完全恢复时&＃xff0c;将控制文件加锁&＃xff0c;且没有释放

SQL> select event from v$session_wait;

EVENT

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

pmon timer

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

rdbms ipc message

EVENT

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

direct path read

smon timer

SQL*Net message to client

enq: CF - contention

这时如果进行控制文件备份&＃xff0c;命令同样挂起

SQL> alter database backup controlfile to trace resetlogs;

搜索metalink&＃xff0c;有相关文档说这是数据库在10.2.0.3的bug&＃xff0c;但是问题现象和我均不一样&＃xff0c;这时候抱着试试看的态度&＃xff0c;将数据库软件升级至10.2.0.5。

注意升级的时候需将10.2.0.3数据库软件进行备份&＃xff0c;这样就可以保证快速回退&＃xff0c;升级好之后&＃xff0c;结果还是令人失望的&＃xff0c;错误依旧。考验的时候来了&＃xff0c;网上无解决方案&＃xff0c;只能靠自己。

把数据库进行open reselogs打开&＃xff0c;归根到底有2种方式&＃xff1a;

1、进行不完全恢复命令恢复 。如

recover database until cancel&＃xff1b;

recover database until change;

2、进行备份的控制文件进行恢复

recover database using backup controlfile;

而用到 using backup controlfile的一个办法就是将控制文件进行reselogs选项重建&＃xff0c;现在目标又很明确&＃xff0c;开工&＃xff01;

将控制文件用resetlogs选项备份

SQL> alter database backup controlfile to trace resetlogs;

Database altered.

但是在进行控制文件重建时&＃xff0c;后台警告日志出现错误

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command

Default Temporary Tablespace will be necessary for a locally managed database in future release

Thu Nov  3 17:41:34 2011

Errors in file /ora10g/admin/drb/udump/drb_ora_17372.trc:

ORA-00600: internal error code, arguments: [kccscf_1], [9], [93440], [65535], [], [], [], []

Thu Nov  3 17:41:35 2011

Errors in file /ora10g/admin/drb/udump/drb_ora_17372.trc:

ORA-00600: internal error code, arguments: [kccscf_1], [9], [93440], [65535], [], [], [], []

还好这次metalink有相关解释&＃xff0c;只要将MAXLOGHISTORY选项从93440将为65536以下数字即可&＃xff0c;经过一番努力&＃xff0c;控制文件终于建成功&＃xff0c;于是再次进行不完全恢复.

悲剧的是数据库在open resetlogs时再次出现错误&＃xff0c;实例异常终止。

SQL> recover database using backup controlfile;

ORA-00279: change 11000485137852 generated at 11/03/2011 19:28:40 needed for

thread 1

ORA-00289: suggestion :

/ora10g/oracle/product/10.2.0/db_1/dbs/arch1_2_766265181.dbf

ORA-00280: change 11000485137852 for thread 1 is in sequence #2

Specify log: {&＃61;suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

马上查看后台日志&＃xff0c;出现熟悉的ora-600 [2662]&＃xff0c;看到这个错误就像看到久违的老朋友一样&＃xff0c;离我们目标就不远了&＃xff01;

ARC1: Becoming the heartbeat ARCH

Thu Nov  3 19:36:42 2011

SMON: enabling cache recovery

Thu Nov  3 19:36:42 2011

Errors in file /ora10g/admin/drb/udump/drb_ora_32054.trc:

ORA-00600: internal error code, arguments: [2662], [2561], [1073892802], [2561], [1073892833], [4194313], [], []

Thu Nov  3 19:37:46 2011

Shutting down instance (abort)

License high water mark &＃61; 3

Instance terminated by USER, pid &＃61; 5440

解决这个错误之前&＃xff0c;先简单的科普一下这个错误的来历。

ERROR:

ORA-600 [2662] [a] [b] [c] [d] [e]

Arg [a]  Current SCN WRAP

Arg [b]  Current SCN BASE

Arg [c]  dependent SCN WRAP

Arg [d]  dependent SCN BASE

Arg [e]  Where present this is the DBA where the dependent SCN came from.

这个错误的主要意思是数据库在open或者查询时&＃xff0c;发现数据块的内容比当前的current scn大。

知道这个意思之后&＃xff0c;解决起来也很容易

1、用bbed工具将数据块的scn改小&＃xff0c;如果涉及到块比较多的情况下&＃xff0c;这个方法可行性不大

2、将Oracle的current scn加大&＃xff0c;这个可行性较高

于是我们采用第二种方案进行修复&＃xff0c;但是又面临一个问题&＃xff0c;当前的current scn怎么该&＃xff1f;该多大&＃xff1f;这里再介绍一下修改算法

计算规则如下&＃xff1a;Arg [c]*4得出一个数值&＃xff0c;假设为V_Wrap,

如果Arg [d]&＃61;0&＃xff0c;则V_Wrap值为需要的level

Arg [d] <1073741824&＃xff0c;V_Wrap&＃43;1为需要的level

Arg [d] <2147483648&＃xff0c;V_Wrap&＃43;2为需要的level

Arg [d] <3221225472&＃xff0c;V_Wrap&＃43;3为需要的level

本案例的话&＃xff0c;Arg [d]为1073892802&＃xff0c;大于1073741824&＃xff0c;所以level值为2561*4&＃43;2。

设置隐含参数_minimum_giga_scn为10245&＃xff0c;终于成功将数据库打开&＃xff0c;后台日志显示&＃xff0c;scn已经递增至11001558728704

Thu Nov  3 19:39:42 2011

Completed crash recovery at

Thread 1: logseq 1, block 3, scn 11000485157857

0 data blocks read, 0 data blocks written, 0 redo blocks read

Advancing SCN to 11001558728704 according to _minimum_giga_scn

。。。

QMNC started with pid&＃61;20, OS id&＃61;21397

Thu Nov  3 19:46:36 2011

LOGSTDBY: Validating controlfile with logical metadata

Thu Nov  3 19:46:36 2011

LOGSTDBY: Validation complete

Thu Nov  3 19:46:51 2011

Completed: alter database open resetlogs

数据打开之后&＃xff0c;接下来就是收尾工作了&＃xff0c;检查发现表空间为READ ONLY状态

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where   TABLESPACE_NAME&＃61;&＃39;WEN&＃39;;

TABLESPACE_NAME                STATUS

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

WEN                            READ ONLY

将表空间置为read write时出现以下错误&＃xff0c;这个错误网上搜索&＃xff0c;又是没资料&＃xff0c;又得靠自己了

SQL> alter database datafile 13 online;

Database altered.

SQL> alter tablespace wen read write;

alter tablespace wen read write

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [kcpgucv1], [13], [], [], [], [],

[], []

我们知道表空间的状态其实是存储在ts$这张表格中&＃xff0c;其状态的意思在sql.bsq中写的很清楚

create table ts$                                         /* tablespace table */

( ts#           number not null,             /* tablespace identifier number */

name          varchar2("M_IDEN") not null,           /* name of tablespace */

owner#        number not null,                      /* owner of tablespace */

online$       number not null,                      /* status (see KTT.H): */

/*1 &＃61; ONLINE, 2 &＃61; OFFLINE, 3 &＃61; INVALID */

contents$     number not null,     /* TEMPORARY/PERMANENT                  */

undofile#     number,  /* undo_off segment file number (status is OFFLINE) */

undoblock#    number,               /* undo_off segment header file number */

blocksize     number not null,                   /* size of block in bytes */

inc#          number not null,             /* incarnation number of extent */

scnwrp        number,     /* clean offline scn - zero if not offline clean */

scnbas        number,              /* scnbas - scn base, scnwrp - scn wrap */

dflminext     number not null,       /*  default minimum number of extents */

dflmaxext     number not null,        /* default maximum number of extents */

dflinit       number not null,              /* default initial extent size */

dflincr       number not null,                 /* default next extent size */

dflminlen     number not null,              /* default minimum extent size */

dflextpct     number not null,     /* default percent extent size increase */

dflogging     number not null,

/* lowest bit: default logging attribute: clear&＃61;NOLOGGING, set&＃61;LOGGING */

/* second lowest bit: force logging mode */

affstrength   number not null,                        /* Affinity strength */

bitmapped     number not null,       /* If not bitmapped, 0 else unit size */

/* in blocks */

plugged       number not null,                               /* If plugged */

directallowed number not null,   /* Operation which invalidate standby are */

/* allowed */

flags         number not null,                            /* various flags */

/* 0x01 &＃61; system managed allocation */

/* 0x02 &＃61; uniform allocation        */

/* if above 2 bits not set then user managed */

/* 0x04 &＃61; migrated tablespace       */

/* 0x08 &＃61; tablespace being migrated */

/* 0x10 &＃61; undo tablespace           */

/* 0x20 &＃61; auto segment space management */

/* if above bit not set then freelist segment managed */

/* 0x40 &＃61; COMPRESS */

/* 0x80 &＃61; ROW MOVEMENT */

/* 0x100 &＃61; SFT */

/* 0x200 &＃61; undo retention guarantee */

/* 0x400 &＃61; tablespace belongs to a group */

/* 0x800 &＃61; this actually describes a group */

pitrscnwrp    number,                      /* scn wrap when ts was created */

pitrscnbas    number,                      /* scn base when ts was created */

ownerinstance varchar("M_IDEN"),                    /* Owner instance name */

backupowner   varchar("M_IDEN"),             /* Backup owner instance name */

groupname     varchar("M_IDEN"),                             /* Group name */

spare1        number,                                  /* plug-in SCN wrap */

spare2        number,                                  /* plug-in SCN base */

spare3        varchar2(1000),

spare4        date

)

命令不行&＃xff0c;那我们就直接修改基表&＃xff0c;注意此方法在正常场合不能使用

SQL> update ts$ set ONLINE$&＃61;1 where name&＃61;&＃39;WEN&＃39;;

1 row updated.

可以看到表空间wen已经处于online状态

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where   TABLESPACE_NAME&＃61;&＃39;WEN&＃39;;

TABLESPACE_NAME                STATUS

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

WEN                            ONLINE

并已经可以读写。

SQL> create table test111 tablespace wen as select * from v$datafile;

Table created.

此时建议数据库做一次重启操作&＃xff0c;如果正常 &＃xff0c;问题解决至此&＃xff0c;就暂时告一段落了。鼓掌&＃xff01;&＃xff01;&＃xff01;

文章评论