<转>ora-01112

  1. 云栖社区>
  2. 博客>
  3. 正文

<转>ora-01112

狂斩一条龙 2012-12-20 00:05:00 浏览722
展开阅读全文

日他妈的会宁的数据在测试健康龙卡的时候搞坏了,今天再日鬼不好就不睡觉了。。。。妈的。不过还是胡日鬼好了。。转载一下。

年关事情多,最近设备运行极度不正常,好像是过年了,也想罢工休息一样!昨日一台oracle db主板坏掉,数据库也随之崩溃,oracle服务器重启后,无法open,以下是恢复过程!

 

/home/oracle$sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 15:50:26 2008

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  616562688 bytes

Fixed Size                  1220868 bytes

IXDBA.NET技术社区

Variable Size             167775996 bytes

Database Buffers          440401920 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

SQL>  

检查alter日志,发现如下错误:

 

Fri Jan 18 09:57:19 2008

ALTER DATABASE RECOVER    LOGFILE '/opt/oracle/oradata/oradb/redo03.log'

Fri Jan 18 09:57:19 2008

Media Recovery Log /opt/oracle/oradata/oradb/redo03.log

Fri Jan 18 09:57:19 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_p002_6556.trc:

ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [4099], [6101], [], [], [], []

Fri Jan 18 09:57:19 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_p000_6552.trc:

ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [3770], [6255], [], [], [], []

Fri Jan 18 09:57:19 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_p002_6556.trc:

ORA-10562: Error occurred while applying redo to data block (file# 1, block# 4099)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 517

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [4099], [6101], [], [], [], []

Fri Jan 18 09:57:20 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_p000_6552.trc:

ORA-10562: Error occurred while applying redo to data block (file# 1, block# 3770)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 482

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [3770], [6255], [], [], [], []

Recovery interrupted!

Recovered data files to a consistent state at change 2433115

Fri Jan 18 09:57:24 2008

Media Recovery failed with error 12801

ORA-283 signalled during: ALTER DATABASE RECOVER    LOGFILE '/opt/oracle/oradata/oradb/redo03.log'  ...

Fri Jan 18 09:57:24 2008

ALTER DATABASE RECOVER CANCEL

ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

Shutting down instance: further logons disabled

 

初步断定是当前redo由于突然断电造成损坏,重启db后,造成系统表空间不一致,db无法起动。

 

经过询问和检查,数据库没有备份,并且处于非归档模式。

 

解决办法只有一个加上隐含参数起动试试!

 

_ALLOW_RESETLOGS_CORRUPTION = TRUE

 

SQL> show parameter spfile

 

NAME                                 TYPE                      VALUE

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

spfile                              string          /opt/oracle/product/10.2.0.1/d

b_1/dbs/spfileoradb.ora

 

SQL> show parameter '_all'*

 

NAME                                 TYPE

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

VALUE

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

_allow_resetlogs_corruption          boolean

FALSE

fast_start_parallel_rollback         string

LOW

parallel_adaptive_multi_user         boolean

TRUE

parallel_automatic_tuning            boolean

FALSE

parallel_execution_message_size      integer

 

NAME                                 TYPE

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

VALUE

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

2148

parallel_instance_group              string

 

parallel_max_servers                 integer

80

parallel_min_percent                 integer

0

parallel_min_servers                 integer

0

 

NAME                                 TYPE

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

VALUE

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

parallel_server                      boolean

FALSE

parallel_server_instances            integer

1

parallel_threads_per_cpu             integer

2

recovery_parallelism                 integer

0

SQL>

SQL> select status,name from v$datafile;

 

STATUS

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

NAME

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

SYSTEM

/opt/oracle/oradata/oradb/bak/system01.dbf

 

RECOVER

/opt/oracle/oradata/oradb/undotbs01.dbf

 

RECOVER

/opt/oracle/oradata/oradb/sysaux01.dbf

 

 

STATUS

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

NAME

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

RECOVER

/opt/oracle/oradata/oradb/users01.dbf

 

RECOVER

/opt/oracle/oradata/oradb/tools01.dbf

 

RECOVER

/opt/oracle/oradata/oradb/indx01.dbf

 

 

STATUS

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

NAME

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

RECOVER

/opt/oracle/oradata/oradb/cicrodb.dbf

 

 

7 rows selected.

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

 

SQL> recover database using  BACKUP CONTROLFILE;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

SQL> recover database using  BACKUP CONTROLFILE;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

 

上面显示系统表空间需要恢复

 

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

 

SQL> recover database using  BACKUP CONTROLFILE until cancel;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

 

 

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

 

仍然提示要恢复系统表空间!

 

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

 

加入隐含参数!

 

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

 

SQL> shutdown immediate

IXDBA.NET技术社区

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

SQL>      quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

/home/oracle$sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 15:56:44 2008

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  616562688 bytes

Fixed Size                  1220868 bytes

Variable Size             167775996 bytes

Database Buffers          440401920 bytes

Redo Buffers                7163904 bytes

Database mounted.

SQL> select name,status from v$datafile;

 

NAME

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

STATUS

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

/opt/oracle/oradata/oradb/bak/system01.dbf

SYSTEM

 

/opt/oracle/oradata/oradb/undotbs01.dbf

RECOVER

 

/opt/oracle/oradata/oradb/sysaux01.dbf

RECOVER

 

 

NAME

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

STATUS

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

/opt/oracle/oradata/oradb/users01.dbf

RECOVER

 

/opt/oracle/oradata/oradb/tools01.dbf

RECOVER

 

/opt/oracle/oradata/oradb/indx01.dbf

RECOVER

 

 

NAME

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

STATUS

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

/opt/oracle/oradata/oradb/cicrodb.dbf

RECOVER

 

 

7 rows selected.

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

 

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc

ORA-00280: change 2433115 for thread 1 is in sequence #42

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log

'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

 

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

SQL> alter database open RESETLOGS;

 

此时,系统好像hang住了一样,很长一段时间没有反映,过了一会,报错如下!

 

alter database open RESETLOGS

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

 

此时查看alter日志信息:又出现2662错误,

 

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10369.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []

Fri Jan 18 15:58:21 2008

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10369.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []

Fri Jan 18 15:58:21 2008

Error 600 happened during db open, shutting down database

USER: terminating instance due to error 600

Instance terminated by USER, pid = 10369

ORA-1092 signalled during: alter database open RESETLOGS...

 

 

 

SQL>

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

再次重启登录:

 

/home/oracle$sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 15:58:56 2008

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  616562688 bytes

Fixed Size                  1220868 bytes

Variable Size             167775996 bytes

Database Buffers          440401920 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'

 

SQL> recover database;

Media recovery complete.

SQL> alter database open;

 

 

alter database open

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

 

 

检查日志如下:爽呀,又出现了ORA-00600 中的4194错误,

 

Database Characterset is UTF8

Fri Jan 18 16:00:14 2008

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10443.trc:

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

Fri Jan 18 16:00:14 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:

ORA-00600: internal error code, arguments: [4194], [59], [57], [], [], [], [], []

Doing block recovery for file 2 block 1135

Block recovery from logseq 2, block 43 to scn 2453192

Fri Jan 18 16:00:15 2008

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

  Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log

Fri Jan 18 16:00:15 2008

Doing block recovery for file 2 block 281

Block recovery from logseq 2, block 45 to scn 2453193

Fri Jan 18 16:00:15 2008

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

  Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log

Fri Jan 18 16:00:15 2008

Block recovery stopped at EOT rba 2.47.16

Block recovery completed at rba 2.47.16, scn 0.2453190

Fri Jan 18 16:00:15 2008

Block recovery stopped at EOT rba 2.47.16

Block recovery completed at rba 2.47.16, scn 0.2453190

Fri Jan 18 16:00:15 2008

Doing block recovery for file 2 block 153

Block recovery from logseq 2, block 43 to scn 2453187

Fri Jan 18 16:00:15 2008

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

  Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log

Fri Jan 18 16:00:15 2008

Doing block recovery for file 2 block 105

Block recovery from logseq 2, block 45 to scn 2453189

Fri Jan 18 16:00:15 2008

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

  Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log

Fri Jan 18 16:00:15 2008

Block recovery completed at rba 2.45.16, scn 0.2453189

Fri Jan 18 16:00:15 2008

Block recovery completed at rba 2.47.16, scn 0.2453190

Fri Jan 18 16:00:15 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [4194], [59], [57], [], [], [], [], []

Fri Jan 18 16:00:15 2008

Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10443.trc:

ORA-00600: internal error code, arguments: [4193], [688], [1013], [], [], [], [], []

Fri Jan 18 16:00:15 2008

DEBUG: Replaying xcb 0x43625908, pmd 0x4379add8 for failed op 8

Doing block recovery for file 2 block 612

No block recovery was needed

Fri Jan 18 16:00:16 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:

ORA-00600: internal error code, arguments: [4194], [58], [56], [], [], [], [], []

 

 

 

 

Fri Jan 18 16:00:17 2008

Doing block recovery for file 2 block 930

Block recovery from logseq 2, block 47 to scn 2453198

Fri Jan 18 16:00:17 2008

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

  Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log

Block recovery stopped at EOT rba 2.48.16

Block recovery completed at rba 2.48.16, scn 0.2453197

Doing block recovery for file 2 block 9

Block recovery from logseq 2, block 47 to scn 2453196

Fri Jan 18 16:00:18 2008

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

  Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log

Block recovery completed at rba 2.48.16, scn 0.2453197

Fri Jan 18 16:00:18 2008

Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:

ORA-01595: error freeing extent (2) of rollback segment (1))

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [4194], [58], [56], [], [], [], [], []

 

 

4149错误一般根undo有关系,因为当前redo损坏,undo数据也大都不一致了

 

设置隐含参数:
undo改变成手工管理的,然后重启数据库。

 

/home/oracle$sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on星期五 1月 18 16:11:02 2008

 

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  616562688 bytes

Fixed Size                  1220868 bytes

Variable Size             167775996 bytes

Database Buffers          440401920 bytes

Redo Buffers                7163904 bytes

Database mounted.

SQL> show parameter undo

 

NAME                                 TYPE

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

IXDBA.NET技术社区

VALUE

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

undo_management                      string

AUTO

undo_retention                       integer

900

undo_tablespace                      string

UNDOTBS1

SQL> alter system set undo_management='manual' scope=spfile;

 

System altered.

 

然后重启数据库

 

SQL> shutdown immediate

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

/home/oracle$sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 16:13:22 2008

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  616562688 bytes

Fixed Size                  1220868 bytes

Variable Size             167775996 bytes

Database Buffers          440401920 bytes

Redo Buffers                7163904 bytes

Database mounted.

SQL> col name format a30

SQL> select status,name from v$datafile;

 

STATUS          NAME

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

SYSTEM         /opt/oracle/oradata/oradb/bak/system01.dbf

 

ONLINE         /opt/oracle/oradata/oradb/undotbs01.dbf

 

ONLINE         /opt/oracle/oradata/oradb/sysaux01.dbf

 

ONLINE         /opt/oracle/oradata/oradb/users01.dbf

 

STATUS          NAME

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

 

ONLINE         /opt/oracle/oradata/oradb/tools01.dbf

 

ONLINE         /opt/oracle/oradata/oradb/indx01.dbf

 

ONLINE         /opt/oracle/oradata/oradb/cicrodb.dbf

 

 

7 rows selected.

 

SQL> show parameter undo

 

NAME                                 TYPE

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

VALUE

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

undo_management                      string

MANUAL

undo_retention                       integer

900

undo_tablespace                      string

UNDOTBS1

SQL> alter database open;

 

Database altered.

 

SQL> !top

 

正常起动!

 

总结:

 

系统掉电一般引起redo损坏,最糟糕的就是引起当前redo损坏,针对当前redo损坏,又没有备份,数据库处于非归档模式时,只能通过加入隐含参数方式恢复!

 

一般步骤如下:

 

1:首先加入_ALLOW_RESETLOGS_CORRUPTION = TRUE隐含参数,最后resetlogs打开数据库,如果无法打开,进入第二步。

 

2:查看日志信息,根据具体报错信息,确认方法,本例是出现ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []

ORA-600 [2662] "Block SCN is ahead of Current SCN",说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的dependent SCN进行比较,如果当前的SCN小于它,数据库就会产生这个ORA-600 [2662]的错误了.于是想到使用ADJUST_SCN事件来调整当前的SCN,使其大于dependent SCN. 此时我们可以通过Oracle的内部事件来调整SCN:

解决这个问题一般方法是:
调整SCN有两种常用方法:
1.通过immediate trace name方式(在数据库Open状态下,即通过用_allow_resetlogs_corruption=TRUE隐含参数打开数据库之后报错600)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
2.通过10015事件(在数据库无法打开,mount状态下)
alter session set events '10015 trace name adjust_scn level x';
注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。

 

本文通过此种方法设置无效,scn没有得到提升!

 

3:一般情况下redo损坏的时候,undo数据也大都不一致,因此通过scn调整完毕,resetlogs数据库后还会出现ORA-00600: internal error code, arguments: [4194]错误,这个错误可以通过设置undo解决:

设置隐含参数:_corrupted_rollback_segments
将undo改变成手工管理的,然后重启数据库,

本例只是将undo改变成手工管理,问题解决。

 

 

 

网友评论

登录后评论
0/500
评论
狂斩一条龙
+ 关注