recover – OracleBlog

一次dataguard坏块的修复

2016-11-18 3条评论

客户有个11g的active dataguard库，mrp进程停了，看alertlog，可以看到有关ora-7445[kdxlin]的报错：

cat alert*.log
....
Exception [type:SIGSEOV,Address not mapped to object] [ADDR:0xC] |PC：0x96504C7,kdxlin()+4153][flags: 0x0,count:1]
Errors in le /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/trace/partsm_pr18_21343.trc (incident=70353):
ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]
Incident details in: /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc
Use ADRCI or Support Workbench to package the incident.

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC][PC:0x96504C7,kdxlin()+4153][flags:0XO,count:1]
Incidenl 70353 created, dump file/aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc
ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]
...

cat alert*.log

....

Exception [type:SIGSEOV,Address not mapped to object] [ADDR:0xC] |PC：0x96504C7,kdxlin()+4153][flags: 0x0,count:1]

Errors in le /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/trace/partsm_pr18_21343.trc (incident=70353):

ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]

Incident details in: /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc

Use ADRCI or Support Workbench to package the incident.

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC][PC:0x96504C7,kdxlin()+4153][flags:0XO,count:1]

Incidenl 70353 created, dump file/aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc

ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]

...

cat /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc
...
Error 607 in redo applicalion callback
Dump of change vector
Typ:2 CLS:1 APN:5 DBA0x2598d645 OBJ:3S3792 SCN:0x0960.99d2655e SEQ:1 OP:10.2 ENC:0 RBL:0
index redo(kdxlin):insert leaf row
KTB Redo
op:0x01 ver:0x01
compat bit:4(post-11) padding:1
op:F xid:0x0001.01a.010d8f34 uba:0x00dc8e3.6bf5.20
REDO: SINGLE/NONKEY/--
itl:3, sno:255, row size 23
insert key:(14):05 c4 02 4e 31 4f 07 78 74 0b 09 11 27 29
nonkey (length: 5):
fb: --H-FL-- lb:0x0 cc:1
(2).01 80
Block after image is corrupt:
buffer tsn: 5 rdba:0x2598d645(1024/630773317)
scn:0x960.99d10d33 seq:0x01 flg:0x04 tail:0x0d330601
frmt:0x02 chkval:0xa1ae type:0x06=trans data
Hex dump of currupt header 3=CHKVAL
...

cat /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc

...

Error 607 in redo applicalion callback

Dump of change vector

Typ:2 CLS:1 APN:5 DBA0x2598d645 OBJ:3S3792 SCN:0x0960.99d2655e SEQ:1 OP:10.2 ENC:0 RBL:0

index redo(kdxlin):insert leaf row

KTB Redo

op:0x01 ver:0x01

compat bit:4(post-11) padding:1

op:F xid:0x0001.01a.010d8f34 uba:0x00dc8e3.6bf5.20

REDO: SINGLE/NONKEY/--

itl:3, sno:255, row size 23

insert key:(14):05 c4 02 4e 31 4f 07 78 74 0b 09 11 27 29

nonkey (length: 5):

fb: --H-FL-- lb:0x0 cc:1

(2).01 80

Block after image is corrupt:

buffer tsn: 5 rdba:0x2598d645(1024/630773317)

scn:0x960.99d10d33 seq:0x01 flg:0x04 tail:0x0d330601

frmt:0x02 chkval:0xa1ae type:0x06=trans data

Hex dump of currupt header 3=CHKVAL

...

从trace中，我们可以看 […]

..experience, Working case

Legato备份还原测试

2010-01-04 没有评论

最近的一段时间，某省的备份出了点问题，legato的备份在还原时检查不到索引，通过scanner扫描之后还是检查不到，问题已经开case提交到emc那边，目前还在处理。为了防止万一，还是对全网用legato备份的省进行了一次还原测试。以下是还原测试中遇到的一些问题小结。目前现网大部分机器都是采用h […]

..experience, Working case

基于catalog库进行部分表空间的还原

2009-01-04 7条评论

应某省的局方要求，在双机热备的备机上，将主机数据库的system、undo、temp和某个应用的表空间DATA_STATIC进行还原。环境如下：（1）HA主机：xj_db01 （2）HA备机：xj_db02 （3）备份软件：LEGATO 且legato的备份脚本为： [crayon-6772ae […]

..experience, Working case

一次redolog丢失的恢复

2008-04-18 5条评论

接到同事的电话，某省的一个用于监控的siteview数据库启动不了了，登录后检查alertlog发现：

Completed first pass scan
 9868 redo blocks read, 655 data blocks need recovery
Thu Apr 19 16:52:42 2007
Started recovery at
 Thread 1: logseq 700, block 194931, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 700 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\ORADATA\IMALLSV\REDO03.LOG
Thu Apr 19 16:52:43 2007
Ended recovery at
 Thread 1: logseq 700, block 204799, scn 0.147914177
 655 data blocks read, 655 data blocks written, 9868 redo blocks read
Crash recovery completed successfully
Thu Apr 19 16:52:43 2007
Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。

Thu Apr 19 16:52:43 2007
Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。

ORA-313 signalled during: alter database open...

Completed first pass scan

9868 redo blocks read, 655 data blocks need recovery

Thu Apr 19 16:52:42 2007

Started recovery at

Thread 1: logseq 700, block 194931, scn 0.0

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

Mem# 0 errs 0: C:\ORACLE\ORADATA\IMALLSV\REDO03.LOG

Thu Apr 19 16:52:43 2007

Ended recovery at

Thread 1: logseq 700, block 204799, scn 0.147914177

655 data blocks read, 655 data blocks written, 9868 redo blocks read

Crash recovery completed successfully

Thu Apr 19 16:52:43 2007

Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:

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

ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Thu Apr 19 16:52:43 2007

Errors in file c:\oracle\admin\imallsv\bdump\imallsv_lgwr_4180.trc:

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

ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\IMALLSV\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

ORA-313 signalled during: alter database open...

原来是redolog被干掉了，其实对于redolog的丢失，如果丢失的不是active或者current（这个在win中一般不会被 […]

未分类

undo表空间丢失之恢复

2007-07-10 一条评论

sys@ORALOCAL(10.1.19.11)> insert into hejianmin.kk values(222333);

已创建 1 行。

已用时间:  00: 00: 00.05
sys@ORALOCAL(10.1.19.11)> shutdown abort;
ORACLE 例程已经关闭。
sys@ORALOCAL(10.1.19.11)> exit;
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

sys@ORALOCAL(10.1.19.11)> insert into hejianmin.kk values(222333);

已创建 1 行。

已用时间: 00: 00: 00.05

sys@ORALOCAL(10.1.19.11)> shutdown abort;

ORACLE 例程已经关闭。

sys@ORALOCAL(10.1.19.11)> exit;

从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中断开

C:\Documents and Settings\Administrator>d:

D:\>cd D:\oracle\oradata\oralocal

D:\oracle\oradata\oralocal>ls
CONTROL01.CTL       DRSYS01.DBF         REDO0201.LOG        REDO101.LOG         REDO301.LOG         TS_DATA_USER01.DBF  TS_UNDO_02.DBF
CONTROL02.CTL       EXAMPLE01.DBF       REDO0202.LOG        REDO102.LOG         REDO302.LOG         TS_INDX_01.DBF      XDB01.DBF
CONTROL03.CTL       ODM01.DBF           REDO0301.LOG        REDO201.LOG         SYSTEM01.DBF        TS_RMAN_01.DBF
CWMLITE01.DBF       PERFSTAT.DBF        REDO0302.LOG        REDO202.LOG         TEMP.DBF            TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>rm TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>set nls_lang=american

D:\oracle\oradata\oralocal>sqlplus "/ as sysdba"

C:\Documents and Settings\Administrator>d:

D:\>cd D:\oracle\oradata\oralocal

D:\oracle\oradata\oralocal>ls

CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF

CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF

CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF

CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>rm TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>set nls_lang=american

D:\oracle\oradata\oralocal>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 5 13:48:49 2007

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

Connected to an idle instance.


> startup
ORACLE instance started.

Total System Global Area  133765984 bytes
Fixed Size                   453472 bytes
Variable Size              79691776 bytes
Database Buffers           50331648 bytes
Redo Buffers                3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'


> alter database datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF' offline drop;

Database altered.

Elapsed: 00:00:00.02
> alter database open;

Database altered.

Elapsed: 00:00:18.00
> create undo tablespace UNDOTBS03 datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_03.dbf' size 100M;

Tablespace created.

Elapsed: 00:00:09.08
> alter system set undo_tablespace=UNDOTBS03 scope=spfile;

System altered.

Elapsed: 00:00:00.01
> shutdown immediate;
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'


> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []


Elapsed: 00:00:02.00
> shutdown immediate;
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
> shutdown abort;
ORACLE instance shut down.
> startup
ORACLE instance started.

Total System Global Area  133765984 bytes
Fixed Size                   453472 bytes
Variable Size              79691776 bytes
Database Buffers           50331648 bytes
Redo Buffers                3289088 bytes
Database mounted.
Database opened.
> select SEGMENT_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1$                      OFFLINE
_SYSSMU2$                      NEEDS RECOVERY
_SYSSMU3$                      NEEDS RECOVERY
_SYSSMU4$                      NEEDS RECOVERY
_SYSSMU5$                      NEEDS RECOVERY
_SYSSMU6$                      NEEDS RECOVERY
_SYSSMU7$                      NEEDS RECOVERY
_SYSSMU8$                      NEEDS RECOVERY
_SYSSMU9$                      NEEDS RECOVERY
_SYSSMU10$                     NEEDS RECOVERY
_SYSSMU11$                     OFFLINE
_SYSSMU12$                     OFFLINE
_SYSSMU13$                     OFFLINE
_SYSSMU14$                     OFFLINE
_SYSSMU15$                     OFFLINE
_SYSSMU16$                     OFFLINE
_SYSSMU17$                     OFFLINE
_SYSSMU18$                     OFFLINE
_SYSSMU19$                     OFFLINE
_SYSSMU20$                     NEEDS RECOVERY
_SYSSMU21$                     ONLINE
_SYSSMU22$                     ONLINE
_SYSSMU23$                     ONLINE
_SYSSMU24$                     ONLINE
_SYSSMU25$                     ONLINE
_SYSSMU26$                     ONLINE
_SYSSMU27$                     ONLINE
_SYSSMU28$                     ONLINE
_SYSSMU29$                     ONLINE
_SYSSMU30$                     ONLINE

31 rows selected.

Elapsed: 00:00:02.00
> drop tablespace TS_UNDO_01 including contents and datafiles;
drop tablespace TS_UNDO_01 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace


Elapsed: 00:00:02.01
> shutdown immediate;
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'


> shutdown abort;
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
>exit

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 5 13:48:49 2007

Connected to an idle instance.

> startup

ORACLE instance started.

Total System Global Area 133765984 bytes

Fixed Size 453472 bytes

Variable Size 79691776 bytes

Database Buffers 50331648 bytes

Redo Buffers 3289088 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'

> alter database datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF' offline drop;

Database altered.

Elapsed: 00:00:00.02

> alter database open;

Database altered.

Elapsed: 00:00:18.00

> create undo tablespace UNDOTBS03 datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_03.dbf' size 100M;

Tablespace created.

Elapsed: 00:00:09.08

> alter system set undo_tablespace=UNDOTBS03 scope=spfile;

System altered.

Elapsed: 00:00:00.01

> shutdown immediate;

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'

> select status from v$instance;

select status from v$instance

ERROR at line 1:

ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

Elapsed: 00:00:02.00

> shutdown immediate;

ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

> shutdown abort;

ORACLE instance shut down.

> startup

ORACLE instance started.

Total System Global Area 133765984 bytes

Fixed Size 453472 bytes

Variable Size 79691776 bytes

Database Buffers 50331648 bytes

Redo Buffers 3289088 bytes

Database mounted.

Database opened.

> select SEGMENT_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME STATUS

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

SYSTEM ONLINE

_SYSSMU1$ OFFLINE

_SYSSMU2$ NEEDS RECOVERY

_SYSSMU3$ NEEDS RECOVERY

_SYSSMU4$ NEEDS RECOVERY

_SYSSMU5$ NEEDS RECOVERY

_SYSSMU6$ NEEDS RECOVERY

_SYSSMU7$ NEEDS RECOVERY

_SYSSMU8$ NEEDS RECOVERY

_SYSSMU9$ NEEDS RECOVERY

_SYSSMU10$ NEEDS RECOVERY

_SYSSMU11$ OFFLINE

_SYSSMU12$ OFFLINE

_SYSSMU13$ OFFLINE

_SYSSMU14$ OFFLINE

_SYSSMU15$ OFFLINE

_SYSSMU16$ OFFLINE

_SYSSMU17$ OFFLINE

_SYSSMU18$ OFFLINE

_SYSSMU19$ OFFLINE

_SYSSMU20$ NEEDS RECOVERY

_SYSSMU21$ ONLINE

_SYSSMU22$ ONLINE

_SYSSMU23$ ONLINE

_SYSSMU24$ ONLINE

_SYSSMU25$ ONLINE

_SYSSMU26$ ONLINE

_SYSSMU27$ ONLINE

_SYSSMU28$ ONLINE

_SYSSMU29$ ONLINE

_SYSSMU30$ ONLINE

31 rows selected.

Elapsed: 00:00:02.00

> drop tablespace TS_UNDO_01 including contents and datafiles;

drop tablespace TS_UNDO_01 including contents and datafiles

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace

Elapsed: 00:00:02.01

> shutdown immediate;

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'

> shutdown abort;

ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

>exit

vi INIToralocal.ORA *.aq_tm_processes=1 *.backgroun […]

未分类

shutdown后丢失某个数据文件的恢复

2007-07-10 2条评论

网上的朋友不小心删除了物理文件，发现startup不起来，我们怎么进行数据恢复？在archive模式下，这其实是一个很简单的过程： SQL> create tablespace “TS_DATA_TEST” logging datafile ‘D:oracleoradataoralocalTS_DA […]

未分类

基于scn的数据恢复

2007-07-10 没有评论

不小心delete掉一些数据，而且已经commit了，怎么办？我们可以基于回闪（flashback）和scn进行一些数据的恢复。 –查询当前scn SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_C […]