Tag links generated by Zoundry Raven. Do not manually edit. http://www.zoundryraven.com

收到某客户的要求,需要帮忙恢复三个月前(9月3日)的备份数据。料想是个简单的rman恢复,便答应了下来。

实际恢复过程中,才发现情况不简单。在过去的半年,数据库归档日志完全没有备份,做完全恢复已经不可能了。

只能先尝试,是否能正确恢复控制文件和日志文件,这两者能恢复出来,数据库就有可能恢复。

more

  1. 恢复控制文件和数据文件

RMAN > list backup


978354  Incr 0  55.60G     SBT_TAPE    04:16:57     2012-08-26 09:18:39

BP Key: 978558   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DB_LEVEL_0

Handle: df492_1_792306102   Media:

List of Datafiles in backup set 978354

File LV Type Ckp SCN    Ckp Time            Name


1    0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/system01.dbf

2    0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/undotbs01.dbf

3    0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/sysaux01.dbf

4    0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/users01.dbf

5    0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/YWUSER.dbf

7    0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/YWUSER01.dbf

10   0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/CARSPACE.dbf

12   0  Incr 111301368  2012-08-26 05:01:43 /oradata/GISDBST/STATDB.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time


978355  Incr 0  6.25M      SBT_TAPE    00:05:43     2012-08-26 09:24:26

BP Key: 978559   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DB_LEVEL_0

Handle: df493_1_792321523   Media:

Control File Included: Ckp SCN: 111314545    Ckp time: 2012-08-26 09:18:43

SPFILE Included: Modification time: 2012-08-25 06:04:12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time


978356  Incr 0  55.60G     SBT_TAPE    24:55:50     2012-09-03 05:56:44

BP Key: 978560   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DB_LEVEL_0

Handle: df494_1_792910854   Media:

List of Datafiles in backup set 978356

File LV Type Ckp SCN    Ckp Time            Name


1    0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/system01.dbf

2    0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/undotbs01.dbf

3    0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/sysaux01.dbf

4    0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/users01.dbf

5    0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/YWUSER.dbf

7    0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/YWUSER01.dbf

10   0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/CARSPACE.dbf

12   0  Incr 111824580  2012-09-02 05:00:54 /oradata/GISDBST/STATDB.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time


978357  Incr 0  6.25M      SBT_TAPE    00:31:38     2012-09-03 06:28:29

BP Key: 978561   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DB_LEVEL_0

Handle: df495_1_793000611   Media:

Control File Included: Ckp SCN: 111880644    Ckp time: 2012-09-03 05:56:51

SPFILE Included: Modification time: 2012-09-01 00:00:33

BS Key  Type LV Size       Device Type Elapsed Time Completion Time


978358  Incr 0  59.15G     SBT_TAPE    12:40:09     2012-09-09 17:41:36

BP Key: 978562   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DB_LEVEL_0

Handle: df496_1_793515687   Media:

List of Datafiles in backup set 978358

File LV Type Ckp SCN    Ckp Time            Name


1    0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/system01.dbf

2    0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/undotbs01.dbf

3    0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/sysaux01.dbf

4    0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/users01.dbf

5    0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/YWUSER.dbf

7    0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/YWUSER01.dbf

10   0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/CARSPACE.dbf

12   0  Incr 995591566  2012-09-09 05:01:27 /oradata/GISDBST/STATDB.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time


978359  Incr 0  7.00M      SBT_TAPE    00:38:39     2012-09-09 18:20:25

BP Key: 978563   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DB_LEVEL_0

Handle: df497_1_793561306   Media:

Control File Included: Ckp SCN: 995641441    Ckp time: 2012-09-09 17:41:46

SPFILE Included: Modification time: 2012-09-07 21:01:30

rman target / catalog rman/rman@RCAT

run {

allocate channel ch00 device type 'sbt_tape';

restore controlfile from 'df494_1_792910854';

release channel ch00;

}

RMAN> run {

2> allocate channel ch00 device type 'sbt_tape';

3> SET UNTIL TIME "to_date('2012/9/05 00:00:00','YYYY/MM/DD HH24:MI:SS')";

4> restore database;

5> release channel ch00;

6> }

  1. 检查控制文件和数据文件的SCN是否一致。

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

FILE# TO_CHAR(CHECK


1     111864822

2     111864822

3     111864822

4     111864822

5     111864822

7     111864822

10     111864822

12     111864822

8 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

FILE# ONLINE_ TO_CHAR(CHANG


1 ONLINE      111824580

2 ONLINE      111824580

3 ONLINE      111824580

4 ONLINE      111824580

5 ONLINE      111824580

7 ONLINE      111824580

10 ONLINE      111824580

12 ONLINE      111824580

8 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

FILE# TO_CHAR(CHECK


1     111824580

2     111824580

3     111824580

4     111824580

5     111824580

7     111824580

10     111824580

12     111824580

8 rows selected.

检查得知,控制文件SCN比数据文件SCN低,重建控制文件使二者一致

SQL>  alter database backup controlfile to trace as '/tmp/ora.ctl';

从ora.ctl中截取RESETLOGS区块

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "GISDBST" RESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 1168

LOGFILE

GROUP 1 '/oradata/GISDBST/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/GISDBST/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/GISDBST/redo03.log'  SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/oradata/GISDBST/system01.dbf',

'/oradata/GISDBST/undotbs01.dbf',

'/oradata/GISDBST/sysaux01.dbf',

'/oradata/GISDBST/users01.dbf',

'/oradata/GISDBST/YWUSER.dbf',

'/oradata/GISDBST/YWUSER01.dbf',

'/oradata/GISDBST/CARSPACE.dbf',

'/oradata/GISDBST/STATDB.dbf'

CHARACTER SET ZHS16GBK

;

重建过程中会报redo日志组错误

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-00333: redo log read error block 1 count 1

ORA-01517: log member: '/oradata/GISDBST/redo01.log'

ORA-27091: unable to queue I/O

ORA-27069: attempt to do I/O beyond the range of the file

Additional information: 1

Additional information: 1

重建redo日志组

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

再次检查控制文件SCN和数据文件SCN,结论是二者一致

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

FILE# TO_CHAR(CHECK


1     111824580

2     111824580

3     111824580

4     111824580

5     111824580

7     111824580

10     111824580

12     111824580

8 rows selected.

SQL>  select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

FILE# ONLINE_ TO_CHAR(CHANG


1 ONLINE      111824580

2 ONLINE      111824580

3 ONLINE      111824580

4 ONLINE      111824580

5 ONLINE      111824580

7 ONLINE      111824580

10 ONLINE      111824580

12 ONLINE      111824580

8 rows selected.

SQL>  select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

FILE# TO_CHAR(CHECK


1     111824580

2     111824580

3     111824580

4     111824580

5     111824580

7     111824580

10     111824580

12     111824580

8 rows selected.

此时,数据库还不能直接打开,需要使用隐含参数打开

SQL> create pfile='/tmp/pfile' from spfile;

File created.

-------/tmp/pfile中加上----------

_allow_resetlogs_corruption= TRUE


SQL> startup mount pfile='/tmp/pfile' force

ORACLE instance started.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn / as sysdba

ERROR:

ORA-03135: connection lost contact

即使数据库能勉强置于Open状态,但是支撑不了多久,就会Down。

此时看alert.log

Errors in file /oracle/admin/GISDBST/bdump/gisdbst_m001_770142.trc:

ORA-00600: internal error code, arguments: [4194], [38], [37], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [38], [37], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [38], [37], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [38], [37], [], [], [], [], []

ORA-06512: at "SYS.PRVT_ADVISOR", line 4896

ORA-00600: internal error code, arguments: [4194], [38], [37], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [38], [37], [], [], [], [], []

ORA-06512: at line 1

Tue Dec 11 02:32:31 2012

Flush retried for xcb 0x70000008897c2e8, pmd 0x700000087213ae0

Doing block recovery for file 2 block 278

Block recovery from logseq 7, block 58 to scn 111955923

Tue Dec 11 02:32:31 2012

Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

Mem# 0: /oradata/GISDBST/redo01.log

Block recovery completed at rba 7.70.16, scn 0.111955924

Tue Dec 11 02:32:31 2012

Errors in file /oracle/admin/GISDBST/bdump/gisdbst_pmon_2981930.trc:

ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=ORA; arguments: [4194] [38] [37]

Tue Dec 11 02:32:32 2012

Errors in file /oracle/admin/GISDBST/bdump/gisdbst_pmon_2981930.trc:

ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=ORA; arguments: [4194] [38] [37]

PMON: terminating instance due to error 472

Instance terminated by PMON, pid = 2981930

爆出内部错误ORA-00600,主要特征是4194。

ORA-600[4194]内部错误一般由重做记录与回滚记录不匹配引发。Oracle在验证Undo record number时,会对比redo chang和回滚段中的undo record number,若发现2者存在差异则报该4194错误。其错误argument[a][b],a代表回滚块中的最大undo record number,b代表重做日志中记录的undo record number。这个错误可能由回滚段或者redo log日志文件讹误引起。

此错误不像ORA-600[2662]或ORA-600[4000]错误那样必然导致数据库无法打开,因为它很少出现在前滚阶段;当数据库被打开,smon开始执行事务恢复或一些回滚段的管理工作时则很有可能触发该错误。

SQL> alter system set event='10513 trace name context forever,level 2 : 10512 trace name context forever,level 1: 10511 trace name context forever,level 2: 10510 trace name context forever,level 1' scope=spfile;

/* 10513事件用以阻止SMON在启动数据库后执行事务恢复(transaction recovery) */

/* 10512事件用以阻止SMON shrink rollback segment */

/* 10511事件用以阻止SMON check to cleanup undo dictionary */

/* 10500事件用以阻止SMON check to offline pending offline rollback segment */

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/GISDBST/temp01.dbf' size 8G ;

SQL> startup

ORACLE instance started.

Total System Global Area 2097152000 bytes

Fixed Size                  2085360 bytes

Variable Size             872418832 bytes

Database Buffers         1207959552 bytes

Redo Buffers               14688256 bytes

Database mounted.

Database opened.

SQL> alter system set undo_management=MANUAL scope=spfile;

System altered.

SQL> shutdown immediate;

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

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2634022912 bytes

Fixed Size 2086288 bytes

Variable Size 2382367344 bytes

Database Buffers 234881024 bytes

Redo Buffers 14688256 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> create undo tablespace undoc datafile size 300M;

SQL> alter system set undo_management=AUTO scope=spfile;

System altered.

SQL> alter system set undo_tablespace=undoc scope=spfile;

System altered.

/* 通过重建undo表空间可以避免一些4194错误,但不是全部 */

/* 这个库目前处于随时会crash的不可控状态,我们必须要导出数据并导入到新库中 * /

/* 这种情况下direct方式 可能可以规避一些意外错误 */

$ exp jihe/jihe  file=jihe.dmp owner=jihe  direct=y statistics=none

以上情况适合完全丢失归档日志,所有DataFile的SCN一致的情况下。在其他情景下,有可能遇到DataFile的SCN不一致的情况,可以参考使用BBED修改Header。(http://www.xifenfei.com/3955.html

这里感谢惜分飞(http://www.xifenfei.com/2619.html)和Maclean大大(http://t.cn/zj9WNG0)的无私分享。

Technorati : Oracle, RMAN

Del.icio.us : Oracle, RMAN

Zooomr : Oracle, RMAN

Flickr : Oracle, RMAN