Oracle数据恢复、数据库恢复、灾难恢复

【Oracle ASMデータリカバリ】ORA-15042: ASM disk is missing after add disk took placeエラ解析

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

もし10.2.0.4あとのバーションでASM Diskgroupへ新たなディスクを追加したが、diskgroupがdismountされた場合に、mountしてみると、そのdiskgroupがORA-15042: ASM disk is missing after add disk took placeになった。解決策はこの文を参考してください。

Tue Feb 12 17:33:59 2013

NOTE: X->S down convert bast on F1B3 bastCount=2

Wed Feb 13 04:06:38 2013 < ALTER DISKGROUP DG1 ADD DISK

‘/dev/mapper/t1_asm03p1’,

‘/dev/mapper/t1_asm04p1’,

‘/dev/mapper/t1_asm05p1’,

‘/dev/mapper/t1_asm06p1’

rebalance power 4

Wed Feb 13 04:06:38 2013

NOTE: reconfiguration of group 1/0x53bffa1 (DG1), full=1

Wed Feb 13 04:06:39 2013

NOTE: initializing header on grp 1 disk DG1_0026

NOTE: initializing header on grp 1 disk DG1_0027

NOTE: initializing header on grp 1 disk DG1_0028

NOTE: initializing header on grp 1 disk DG1_0029

NOTE: cache opening disk 26 of grp 1: DG1_0026 path:/dev/mapper/t1_asm03p1

NOTE: cache opening disk 27 of grp 1: DG1_0027 path:/dev/mapper/t1_asm04p1

NOTE: cache opening disk 28 of grp 1: DG1_0028 path:/dev/mapper/t1_asm05p1

NOTE: cache opening disk 29 of grp 1: DG1_0029 path:/dev/mapper/t1_asm06p1

NOTE: PST update: grp = 1

NOTE: requesting all-instance disk validation for group=1

Wed Feb 13 04:06:39 2013

NOTE: disk validation pending for group 1/0x53bffa1 (DG1)

Wed Feb 13 04:06:40 2013

NOTE: requesting all-instance membership refresh for group=1

Wed Feb 13 04:06:40 2013

NOTE: membership refresh pending for group 1/0x53bffa1 (DG1)

SUCCESS: validated disks for 1/0x53bffa1 (DG1)

SUCCESS: refreshed membership for 1/0x53bffa1 (DG1)

Wed Feb 13 04:07:11 2013 < ALTER DISKGROUP DG1 ADD DISK

‘/dev/mapper/t1_asm03p1’,

‘/dev/mapper/t1_asm04p1’,

‘/dev/mapper/t1_asm05p1’,

‘/dev/mapper/t1_asm06p1’

rebalance power 4

NOTE: cache closing disk 26 of grp 1: DG1_0026 path:/dev/mapper/t1_asm03p1

NOTE: cache closing disk 27 of grp 1: DG1_0027 path:/dev/mapper/t1_asm04p1

NOTE: cache closing disk 28 of grp 1: DG1_0028 path:/dev/mapper/t1_asm05p1

NOTE: cache closing disk 29 of grp 1: DG1_0029 path:/dev/mapper/t1_asm06p1

Wed Feb 13 04:09:36 2013

SQL> ALTER DISKGROUP DG1 ADD DISK

‘/dev/mapper/t1_asm03p1’,

‘/dev/mapper/t1_asm04p1’,

‘/dev/mapper/t1_asm05p1’,

‘/dev/mapper/t1_asm06p1’

rebalance power 4

Wed Feb 13 04:09:36 2013

NOTE: reconfiguration of group 1/0x53bffa1 (DG1), full=1

Wed Feb 13 04:09:36 2013

NOTE: requesting all-instance membership refresh for group=1

Wed Feb 13 04:09:36 2013

NOTE: membership refresh pending for group 1/0x53bffa1 (DG1)

SUCCESS: validated disks for 1/0x53bffa1 (DG1)

NOTE: PST update: grp = 1, dsk = 26, mode = 0x4

NOTE: PST update: grp = 1, dsk = 27, mode = 0x4

NOTE: PST update: grp = 1, dsk = 28, mode = 0x4

NOTE: PST update: grp = 1, dsk = 29, mode = 0x4

Wed Feb 13 04:09:42 2013

ERROR: too many offline disks in PST (grp 1)

Wed Feb 13 04:09:42 2013

SUCCESS: refreshed membership for 1/0x53bffa1 (DG1)

ERROR: ORA-15040 thrown in RBAL for group number 1

Wed Feb 13 04:09:42 2013

Errors in file /opt/oracle/product/10.2.0/asm/admin/+ASM/bdump/+asm1_rbal_30556.trc:

ORA-15040: diskgroup is incomplete

ORA-15066: offlining disk “” may result in a data loss

ORA-15042: ASM disk “29” is missing

ORA-15042: ASM disk “28” is missing

ORA-15042: ASM disk “27” is missing

ORA-15042: ASM disk “26” is missing

Wed Feb 13 04:09:43 2013

ERROR: PST-initiated MANDATORY DISMOUNT of group DG1

Received dirty detach msg from node 3 for dom 1

Wed Feb 13 04:09:43 2013

Dirty detach reconfiguration started (old inc 12, new inc 12)

ここでASMの DISK DIRECTORY、PSTおよび DISK HEADERを分析してください:
kfddde[4].entry.incarn: 2 ; 0x724: A=0 NUMM=0x1

kfddde[4].entry.hash: 0 ; 0x728: 0x00000000

kfddde[4].entry.refer.number: 0 ; 0x72c: 0x00000000

kfddde[4].entry.refer.incarn: 0 ; 0x730: A=0 NUMM=0x0

kfddde[4].dsknum: 28 ; 0x734: 0x001c

kfddde[4].state: 8 ; 0x736: KFDSTA_ADDING <<<===============================

kfddde[4].ub1spare: 0 ; 0x737: 0x00

kfddde[4].dskname: DG1_0028 ; 0x738: length=8

kfddde[4].fgname: DG1_0028 ; 0x758: length=8

kfddde[4].crestmp.hi: 32983460 ; 0x778: HOUR=0x4 DAYS=0xd MNTH=0x2 YEAR=0x7dd

kfddde[4].crestmp.lo: 443710464 ; 0x77c: USEC=0x0 MSEC=0x9f SECS=0x27 MINS=0x6

kfddde[4].failstmp.hi: 0 ; 0x780: HOUR=0x0 DAYS=0x0 MNTH=0x0 YEAR=0x0

kfddde[4].failstmp.lo: 0 ; 0x784: USEC=0x0 MSEC=0x0 SECS=0x0 MINS=0x0

kfddde[4].timer: 0 ; 0x788: 0x00000000

kfddde[4].size: 307199 ; 0x78c: 0x0004afff

kfddde[3].entry.incarn: 2 ; 0x564: A=0 NUMM=0x1

kfddde[3].entry.hash: 0 ; 0x568: 0x00000000

kfddde[3].entry.refer.number: 0 ; 0x56c: 0x00000000

kfddde[3].entry.refer.incarn: 0 ; 0x570: A=0 NUMM=0x0

kfddde[3].dsknum: 27 ; 0x574: 0x001b

kfddde[3].state: 8 ; 0x576: KFDSTA_ADDING <<<===============================

kfddde[3].ub1spare: 0 ; 0x577: 0x00

kfddde[3].dskname: DG1_0027 ; 0x578: length=8

kfddde[3].fgname: DG1_0027 ; 0x598: length=8

kfddde[3].crestmp.hi: 32983460 ; 0x5b8: HOUR=0x4 DAYS=0xd MNTH=0x2 YEAR=0x7dd

kfddde[3].crestmp.lo: 443710464 ; 0x5bc: USEC=0x0 MSEC=0x9f SECS=0x27 MINS=0x6

kfddde[3].failstmp.hi: 0 ; 0x5c0: HOUR=0x0 DAYS=0x0 MNTH=0x0 YEAR=0x0

kfddde[3].failstmp.lo: 0 ; 0x5c4: USEC=0x0 MSEC=0x0 SECS=0x0 MINS=0x0

kfddde[4].entry.hash: 0 ; 0x728: 0x00000000

kfddde[4].entry.refer.number: 0 ; 0x72c: 0x00000000

kfddde[4].entry.refer.incarn: 0 ; 0x730: A=0 NUMM=0x0

kfddde[4].dsknum: 28 ; 0x734: 0x001c

kfddde[4].state: 8 ; 0x736: KFDSTA_ADDING <<<===============================

kfddde[4].ub1spare: 0 ; 0x737: 0x00

kfddde[4].dskname: DG1_0028 ; 0x738: length=8

kfddde[4].fgname: DG1_0028 ; 0x758: length=8

kfddde[4].crestmp.hi: 32983460 ; 0x778: HOUR=0x4 DAYS=0xd MNTH=0x2 YEAR=0x7dd

kfddde[4].crestmp.lo: 443710464 ; 0x77c: USEC=0x0 MSEC=0x9f SECS=0x27 MINS=0x6

kfddde[4].failstmp.hi: 0 ; 0x780: HOUR=0x0 DAYS=0x0 MNTH=0x0 YEAR=0x0

kfddde[4].failstmp.lo: 0 ; 0x784: USEC=0x0 MSEC=0x0 SECS=0x0 MINS=0x0

kfddde[4].timer: 0 ; 0x788: 0x00000000

kfddde[4].size: 307199 ; 0x78c: 0x0004afff

kfddde[5].entry.incarn: 2 ; 0x8e4: A=0 NUMM=0x1

kfddde[5].entry.hash: 0 ; 0x8e8: 0x00000000

kfddde[5].entry.refer.number: 0 ; 0x8ec: 0x00000000

kfddde[5].entry.refer.incarn: 0 ; 0x8f0: A=0 NUMM=0x0

kfddde[5].dsknum: 29 ; 0x8f4: 0x001d

kfddde[5].state: 8 ; 0x8f6: KFDSTA_ADDING <<<===============================

kfddde[5].ub1spare: 0 ; 0x8f7: 0x00

kfddde[5].dskname: DG1_0029 ; 0x8f8: length=8

kfddde[5].fgname: DG1_0029 ; 0x918: length=8

kfddde[5].crestmp.hi: 32983460 ; 0x938: HOUR=0x4 DAYS=0xd MNTH=0x2 YEAR=0x7dd

kfddde[5].crestmp.lo: 443710464 ; 0x93c: USEC=0x0 MSEC=0x9f SECS=0x27 MINS=0x6

kfddde[5].failstmp.hi: 0 ; 0x940: HOUR=0x0 DAYS=0x0 MNTH=0x0 YEAR=0x0

kfddde[5].failstmp.lo: 0 ; 0x944: USEC=0x0 MSEC=0x0 SECS=0x0 MINS=0x0

kfddde[5].timer: 0 ; 0x948: 0x00000000

File_name :: dg1_3.kfed

/dev/mapper/t1_asm05p1

kfbh.endian: 0 ; 0x000: 0x00

kfbh.hard: 0 ; 0x001: 0x00

kfbh.type: 0 ; 0x002: KFBTYP_INVALID

kfbh.datfmt: 0 ; 0x003: 0x00

kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0

kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0

kfbh.check: 0 ; 0x00c: 0x00000000

kfbh.fcn.base: 0 ; 0x010: 0x00000000

kfbh.fcn.wrap: 0 ; 0x014: 0x00000000

kfbh.spare1: 0 ; 0x018: 0x00000000

kfbh.spare2: 0 ; 0x01c: 0x00000000

/dev/mapper/t1_asm06p1

kfbh.endian: 0 ; 0x000: 0x00

kfbh.hard: 0 ; 0x001: 0x00

kfbh.type: 0 ; 0x002: KFBTYP_INVALID

kfbh.datfmt: 0 ; 0x003: 0x00

kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0

kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0

kfbh.check: 0 ; 0x00c: 0x00000000

kfbh.fcn.base: 0 ; 0x010: 0x00000000

kfbh.fcn.wrap: 0 ; 0x014: 0x00000000

kfbh.spare1: 0 ; 0x018: 0x00000000

kfbh.spare2: 0 ; 0x01c: 0x00000000

前のDISK DIRECTORYのstatusからKFDSTA_ADDINGが見られる。つまり、新たに追加したディスクはまだ追加する途中で、レバランスも完成していない。

PSTを探し出すスクリプトは以下の通り：

vi kfed_pst.sh

—–

#! /bin/sh

rm /tmp/kfed_PST.out

for i in `ls *`

echo $i >> /tmp/kfed_PST.out

./kfed read $i aun=1 blkn=2 >> /tmp/kfed_PST.out

done

—-

chmod u+x kfed_pst.sh

このトラブルに対して、人工的にASM metadataをPatchすることで解決してください。さもなければ、diskgroupが再びmountできなくなる。

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracle ASMデータリカバリ】 ORA-600 [kfcChkAio01]エラ解析

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

もしASMインスタンスがよくシャットダウンする。それにバックグラウンドログファイルalert.log以下のエラ情報が現れたら、この文を参考してください：

NOTE: starting recovery of thread=1 ckpt=201.9904 group=2

NOTE: starting recovery of thread=2 ckpt=139.4186 group=2

Tue Dec 16 03:00:51 2008

Errors in file /u01/app/oracle/product/10.2.0/asm/admin/+ASM/udump/+asm2_ora_15305.trc:

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

ORA-15196: invalid ASM block header [kfc.c:5552] [endian_kfbh] [2079] [2147483648] [1 != 0]

Abort recovery for domain 2

NOTE: crash recovery signalled OER-600

ERROR: ORA-600 signalled during mount of diskgroup FLASH

このエラは diskgroupをdismountさせる。一般な原因はbug 7589862 である。

Traceファイルのstack callで確かにそのトラブルかを確認できる：

kfcChkAio

関数kfxdrvMountはdiskgroupをmountするときに使われる。それはASMリカバリ層kfrcrvに属している。

このエラに主な表現は：

ORA-00600: internal error code, arguments: [kfcChkAio01], [], [], [], [], [], [], []
kfcChkAio01 はIO操作が無効のブロックによってエラになった

ORA-15196: invalid ASM block header [kfc.c:5552] [endian_kfbh] [2079] [2147483648] [1 != 0]
そのなか

endian_kfbhはblock headerのデータ
2079 ASM FILE NUMBER
2147483648 ASM BLOCK NUMBER
1 != 0 1 was the value found on the field referenced on the first argument, but 0 was the expected value.

このトラブルが起こったら、人工的にASM metadata をPatchすることで解決してください。ASM内部構造に詳しくない場合に、専門家に任せてください。

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

Oracle内部エラ:ORA-00600[2608]一例

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

ある10.2.0.4単ノートデータベースがデータファイルをリカバリするときにORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []内部エラになって、そのログは以下の通り：

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /s01/db_1

System name: Linux

Node name: rh2.oracle.com

Release: 2.6.18-194.el5

Version: #1 SMP Mon Mar 29 22:10:29 EDT 2010

Machine: x86_64

Instance name: G10R2

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 21360, image: oracle@rh2.oracle.com (TNS V1-V3)

*** 2011-04-27 21:20:40.979

*** ACTION NAME:() 2011-04-27 21:20:40.979

*** MODULE NAME:(sqlplus@rh2.oracle.com (TNS V1-V3)) 2011-04-27 21:20:40.979

*** SERVICE NAME:(SYS$USERS) 2011-04-27 21:20:40.979

*** SESSION ID:(159.3) 2011-04-27 21:20:40.979

kwqmnich: current time:: 13: 20: 40

kwqmnich: instance no 0 check_only flag 1

kwqmnich: initialized job cache structure

Recovery target incarnation = 2, activation ID = 0

Influx buffer limit = 52443 (50% x 104887)

Successfully allocated 2 recovery slaves

Using 550 overflow buffers per recovery slave

Start recovery at thread 1 ckpt scn 690423 logseq 1 block 3183

*** 2011-04-27 21:20:46.165

Media Recovery add redo thread 1

*** 2011-04-27 21:20:46.172

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []

Current SQL statement for this session:

ALTER DATABASE RECOVER datafile 9

—– Call Stack Trace —–

calling call entry argument values in hex

location type point (? means dubious value)

——————– ——– ——————– —————————-

ksedst()+31 call ksedst1() 000000000 ? 000000001 ?

ksedmp()+610 call ksedst() 000000000 ? 000000001 ?

7FFF6F1795E0 ? 7FFF6F179640 ?

7FFF6F179580 ? 000000000 ?

ksfdmp()+21 call ksedmp() 000000003 ? 000000001 ?

7FFF6F1795E0 ? 7FFF6F179640 ?

7FFF6F179580 ? 000000000 ?

kgeriv()+176 call ksfdmp() 000000003 ? 000000001 ?

7FFF6F1795E0 ? 7FFF6F179640 ?

7FFF6F179580 ? 000000000 ?

kgesiv()+119 call kgeriv() 0068966E0 ? 00A81E650 ?

000000000 ? 7FFF6F178F08 ?

7FFF6F179580 ? 000000000 ?

ksesic5()+215 call kgesiv() 0068966E0 ? 00A81E650 ?

000000A30 ? 000000005 ?

7FFF6F17A360 ? 000000000 ?

kcrfro()+6796 call ksesic5() 000000A30 ? 000000000 ?

000000001 ? 000000000 ?

000000000 ? 000000000 ?

kcramr()+7872 call kcrfro() 2B9DB2D29400 ? 000000000 ?

000000001 ? 000000000 ?

000000000 ? 000000000 ?

krddmr()+1290 call kcramr() 2B9DB2CF70E0 ? 00A7F8280 ?

000000000 ? 000000000 ?

adbdrv()+10248 call krddmr() 00A7F8280 ? 000000000 ?

7FFF6F182FC4 ? 000000000 ?

2B9DB2CF70E0 ?

A7F828000000001 ?

opiexe()+13505 call adbdrv() 00A7F8280 ? 000000000 ?

0A2806BD8 ? 000000000 ?

2B9DB2CF70E0 ?

A7F828000000001 ?

opiosq0()+3316 call opiexe() 000000004 ? 000000000 ?

7FFF6F184238 ? 000000012 ?

2B9DB2CF70E0 ?

A7F828000000001 ?

kpooprx()+315 call opiosq0() 000000003 ? 00000000E ?

7FFF6F1843A8 ? 0000000A4 ?

2B9DB2CF70E0 ?

A7F828000000001 ?

kpoal8()+799 call kpooprx() 7FFF6F187554 ? 7FFF6F185530 ?

000000024 ? 000000001 ?

000000000 ? A7F828000000001 ?

opiodr()+984 call kpoal8() 00000005E ? 000000017 ?

7FFF6F187550 ? 000000001 ?

000000001 ? A7F828000000001 ?

ttcpip()+1012 call opiodr() 00000005E ? 000000017 ?

7FFF6F187550 ? 000000000 ?

0059C0990 ? A7F828000000001 ?

opitsk()+1322 call ttcpip() 00689E3B0 ? 000000001 ?

7FFF6F187550 ? 000000000 ?

7FFF6F187048 ? 7FFF6F1876B8 ?

opiino()+1026 call opitsk() 000000003 ? 000000000 ?

7FFF6F187550 ? 000000001 ?

=========================================================

そのORA-00600[2608]の原因はデータファイルヘッダに記録されたcheckpoint scnが小さすぎたからだ。Oracleはそのcheckpoint scnとブロックのresetlogs scn及びログファイルのLow scnと比べる。もしファイルヘッダのcheckpoint scnが小さすぎた場合に、ORA-00600[2608]内部エラになる。

つぎはデータファイルヘッダのkcvfhckp構造に記録されたcheckpoint scnを小さい数値に変更することで、ORA-00600[2608]エラの場合を作成する:

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug dump file_hdrs 8;

Statement processed.

SQL> oradebug tracefile_name;

DATA FILE #11:

(name #17) /u01/data02.dbf

creation size=6400 block size=8192 status=0x1c head=17 tail=17 dup=1

tablespace 12, index=12 krfil=11 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:4 scn: 0x0000.000b01c2 04/27/2011 22:52:31

Stop scn: 0x0000.000b01c5 04/27/2011 22:52:39

Creation Checkpointed at scn: 0x0000.000b01a8 04/27/2011 22:52:24

thread:1 rba:(0xb.e.10)

…………………………………………….

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

V10 STYLE FILE HEADER:

Compatibility Vsn = 169870080=0xa200300

Db ID=2894437650=0xac859d12, Db Name=’G10R2′

Activation ID=0=0x0

Control Seq=740=0x2e4, File size=6400=0x1900

File Number=11, Blksiz=8192, File Type=3 DATA

Tablespace #12 – DATA02 rel_fn:11

Creation at scn: 0x0000.000b01a8 04/27/2011 22:52:24

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

reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000

prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000

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

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

begin-hot-backup file size: 0

Checkpointed at scn: 0x0000.000b01c2 04/27/2011 22:52:31

/* ここで11号データファイルヘッダのCheckpoint scnは0x0000.000b01c2 だが、resetlogs scnは0x0000.000a88f9 である。 */

/* Checkpoint scnを0x0000.000a88f7 に変更する */

[oracle@rh2 u01]$ bbed filename=data02.dbf blocksize=8192 password=blockedit mode=edit

BBED: Release 2.0.0.0.0 – Limited Production on Wed Apr 27 22:55:30 2011

************* !!! For Oracle Internal Use only !!! ***************

BBED> set blocksize 8192

BLOCKSIZE 8192

BBED> set block 2

BLOCK# 2

BBED> map

File: data02.dbf (0)

Block: 2 Dba:0x00000000

————————————————————

Data File Header

struct kcvfh, 676 bytes @0

ub4 tailchk @8188

BBED> p kcvfh

struct kcvfh, 676 bytes @0

struct kcvfhbfh, 20 bytes @0

ub1 type_kcbh @0 0x0b

ub1 frmt_kcbh @1 0xa2

ub1 spare1_kcbh @2 0x00

ub1 spare2_kcbh @3 0x00

ub4 rdba_kcbh @4 0x02c00001

ub4 bas_kcbh @8 0x00000000

ub2 wrp_kcbh @12 0x0000

ub1 seq_kcbh @14 0x01

ub1 flg_kcbh @15 0x04 (KCBHFCKV)

ub2 chkval_kcbh @16 0xb4a1

ub2 spare3_kcbh @18 0x0000

struct kcvfhhdr, 76 bytes @20

ub4 kccfhswv @20 0x00000000

ub4 kccfhcvn @24 0x0a200300

ub4 kccfhdbi @28 0xac859d12

text kccfhdbn[0] @32 G

text kccfhdbn[1] @33 1

text kccfhdbn[2] @34 0

text kccfhdbn[3] @35 R

text kccfhdbn[4] @36 2

text kccfhdbn[5] @37

text kccfhdbn[6] @38

text kccfhdbn[7] @39

ub4 kccfhcsq @40 0x000002e4

ub4 kccfhfsz @44 0x00001900

s_blkz kccfhbsz @48 0x00

ub2 kccfhfno @52 0x000b

ub2 kccfhtyp @54 0x0003

ub4 kccfhacid @56 0x00000000

ub4 kccfhcks @60 0x00000000

text kccfhtag[0] @64

text kccfhtag[1] @65

text kccfhtag[2] @66

text kccfhtag[3] @67

text kccfhtag[4] @68

text kccfhtag[5] @69

text kccfhtag[6] @70

text kccfhtag[7] @71

text kccfhtag[8] @72

text kccfhtag[9] @73

text kccfhtag[10] @74

text kccfhtag[11] @75

text kccfhtag[12] @76

text kccfhtag[13] @77

text kccfhtag[14] @78

text kccfhtag[15] @79

text kccfhtag[16] @80

text kccfhtag[17] @81

text kccfhtag[18] @82

text kccfhtag[19] @83

text kccfhtag[20] @84

text kccfhtag[21] @85

text kccfhtag[22] @86

text kccfhtag[23] @87

text kccfhtag[24] @88

text kccfhtag[25] @89

text kccfhtag[26] @90

text kccfhtag[27] @91

text kccfhtag[28] @92

text kccfhtag[29] @93

text kccfhtag[30] @94

text kccfhtag[31] @95

ub4 kcvfhrdb @96 0x00000000

struct kcvfhcrs, 8 bytes @100

ub4 kscnbas @100 0x000b01a8

ub2 kscnwrp @104 0x0000

ub4 kcvfhcrt @108 0x2cae0628

ub4 kcvfhrlc @112 0x2cade887

struct kcvfhrls, 8 bytes @116 resetlogs scn

ub4 kscnbas @116 0x000a88f9

ub2 kscnwrp @120 0x0000

ub4 kcvfhbti @124 0x00000000

struct kcvfhbsc, 8 bytes @128

ub4 kscnbas @128 0x00000000

ub2 kscnwrp @132 0x0000

ub2 kcvfhbth @136 0x0000

ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484 checkpoint scn

ub4 kscnbas @484 0x000b01c2

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x2cae062f

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x0000000b

ub4 kcrbabno @504 0x0000001b

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

ub1 kcvcpetb[1] @513 0x00

ub1 kcvcpetb[2] @514 0x00

ub1 kcvcpetb[3] @515 0x00

ub1 kcvcpetb[4] @516 0x00

ub1 kcvcpetb[5] @517 0x00

ub1 kcvcpetb[6] @518 0x00

ub1 kcvcpetb[7] @519 0x00

ub4 kcvfhcpc @140 0x00000004

ub4 kcvfhrts @144 0x00000000

ub4 kcvfhccc @148 0x00000003

struct kcvfhbcp, 36 bytes @152

struct kcvcpscn, 8 bytes @152

ub4 kscnbas @152 0x00000000

ub2 kscnwrp @156 0x0000

ub4 kcvcptim @160 0x00000000

ub2 kcvcpthr @164 0x0000

union u, 12 bytes @168

struct kcvcprba, 12 bytes @168

ub4 kcrbaseq @168 0x00000000

ub4 kcrbabno @172 0x00000000

ub2 kcrbabof @176 0x0000

ub1 kcvcpetb[0] @180 0x00

ub1 kcvcpetb[1] @181 0x00

ub1 kcvcpetb[2] @182 0x00

ub1 kcvcpetb[3] @183 0x00

ub1 kcvcpetb[4] @184 0x00

ub1 kcvcpetb[5] @185 0x00

ub1 kcvcpetb[6] @186 0x00

ub1 kcvcpetb[7] @187 0x00

ub4 kcvfhbhz @312 0x00000000

struct kcvfhxcd, 16 bytes @316

ub4 space_kcvmxcd[0] @316 0x00000000

ub4 space_kcvmxcd[1] @320 0x00000000

ub4 space_kcvmxcd[2] @324 0x00000000

ub4 space_kcvmxcd[3] @328 0x00000000

word kcvfhtsn @332 12

ub2 kcvfhtln @336 0x0006

text kcvfhtnm[0] @338 D

text kcvfhtnm[1] @339 A

text kcvfhtnm[2] @340 T

text kcvfhtnm[3] @341 A

text kcvfhtnm[4] @342 0

text kcvfhtnm[5] @343 2

text kcvfhtnm[6] @344

text kcvfhtnm[7] @345

text kcvfhtnm[8] @346

text kcvfhtnm[9] @347

text kcvfhtnm[10] @348

text kcvfhtnm[11] @349

text kcvfhtnm[12] @350

text kcvfhtnm[13] @351

text kcvfhtnm[14] @352

text kcvfhtnm[15] @353

text kcvfhtnm[16] @354

text kcvfhtnm[17] @355

text kcvfhtnm[18] @356

text kcvfhtnm[19] @357

text kcvfhtnm[20] @358

text kcvfhtnm[21] @359

text kcvfhtnm[22] @360

text kcvfhtnm[23] @361

text kcvfhtnm[24] @362

text kcvfhtnm[25] @363

text kcvfhtnm[26] @364

text kcvfhtnm[27] @365

text kcvfhtnm[28] @366

text kcvfhtnm[29] @367

ub4 kcvfhrfn @368 0x0000000b

struct kcvfhrfs, 8 bytes @372

ub4 kscnbas @372 0x00000000

ub2 kscnwrp @376 0x0000

ub4 kcvfhrft @380 0x00000000

struct kcvfhafs, 8 bytes @384

ub4 kscnbas @384 0x00000000

ub2 kscnwrp @388 0x0000

ub4 kcvfhbbc @392 0x00000000

ub4 kcvfhncb @396 0x00000000

ub4 kcvfhmcb @400 0x00000000

ub4 kcvfhlcb @404 0x00000000

ub4 kcvfhbcs @408 0x00000000

ub2 kcvfhofb @412 0x0000

ub2 kcvfhnfb @414 0x0000

ub4 kcvfhprc @416 0x2cadd4e7

struct kcvfhprs, 8 bytes @420

ub4 kscnbas @420 0x000a7f86

ub2 kscnwrp @424 0x0000

struct kcvfhprfs, 8 bytes @428

ub4 kscnbas @428 0x00000000

ub2 kscnwrp @432 0x0000

ub4 kcvfhtrt @444 0x00000000

BBED> set offset 484

OFFSET 484

BBED> p

pad

—

ub1 pad @484 0xc2

BBED> modify /x f788

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: data02.dbf (0)

Block: 2 Offsets: 484 to 995 Dba:0x00000000

————————————————————————

f7880b00 00000000 2f06ae2c 01000000 0b000000 1b000000 1000e880 02000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

0d000d00 0d000100 00000000 00000000 00000000 0200c002 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> set offset 486

OFFSET 486

BBED> p

pad

—

ub1 pad @486 0x00

BBED> modify /x 0x0a00

File: data02.dbf (0)

Block: 2 Offsets: 486 to 997 Dba:0x00000000

————————————————————————

0a000000 00002f06 ae2c0100 00000b00 00001b00 00001000 e8800200 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000d00

0d000d00 01000000 00000000 00000000 00000200 c0020000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x000a88f7

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x2cae062f

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x0000000b

ub4 kcrbabno @504 0x0000001b

ub2 kcrbabof @508 0x0010

ub1 kcvcpetb[0] @512 0x02

ub1 kcvcpetb[1] @513 0x00

ub1 kcvcpetb[2] @514 0x00

ub1 kcvcpetb[3] @515 0x00

ub1 kcvcpetb[4] @516 0x00

ub1 kcvcpetb[5] @517 0x00

ub1 kcvcpetb[6] @518 0x00

ub1 kcvcpetb[7] @519 0x00

BBED> sum

Check value for File 0, Block 2:

current = 0xb4a1, required = 0x3d95

BBED> sum apply

Check value for File 0, Block 2:

current = 0x3d95, required = 0x3d95

/* 如我们所期待地出现了ORA-00600[2608]内部错误 */

SQL> recover datafile 11;

ORA-00283: recovery session canceled due to errors

ORA-00600: internal error code, arguments: [2608], [2], [0], [690423], [0],

[721306], [], []

ここの690423は16進数の0x000a88f7で、つまり前に修正したcheckpoint scn。

721306等は0xb019aで、今のログファイルのLow scnである:

LOG FILE #1:

(name #5) /flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log

(name #6) /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_1_6v34jnst_.log

Thread 1 redo log links: forward: 2 backward: 0

siz: 0x19000 seq: 0x0000000a hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 2

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000b0193

Low scn: 0x0000.000b0196 04/27/2011 22:52:05

Next scn: 0x0000.000b019a 04/27/2011 22:52:10

LOG FILE #2:

(name #3) /flashcard/oradata/G10R2/onlinelog/o1_mf_2_6v34jokt_.log

(name #4) /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_2_6v34jotq_.log

Thread 1 redo log links: forward: 3 backward: 1

siz: 0x19000 seq: 0x0000000b hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000b0196

Low scn: 0x0000.000b019a 04/27/2011 22:52:10

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

LOG FILE #3:

(name #1) /flashcard/oradata/G10R2/onlinelog/o1_mf_3_6v34jpmp_.log

(name #2) /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_3_6v34jpyn_.log

Thread 1 redo log links: forward: 0 backward: 2

siz: 0x19000 seq: 0x00000009 hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 2

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000b0190

Low scn: 0x0000.000b0193 04/27/2011 22:52:04

Next scn: 0x0000.000b0196 04/27/2011 22:52:05

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracleデータリカバリ】SYSAUXテーブルスペースのオブジェクトをどうやって再構造できるか

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

SQL> select file_id,file_name from dba_data_files where tablespace_name=’SYSAUX’;

FILE_ID
———-
FILE_NAME
——————————————————————————————————————————————–
2
/s01/oradata/FIXIT/datafile/o1_mf_sysaux_85wkhmrk_.dbf
SQL> alter database datafile 2 offline;

Database altered.
仮にここのdatafile 2、つまりSYSAUXのすべてのデータファイルもなくした。それにどんなバックアップもないから、バックアップでテーブルスペースをリカバリできない。
けどSYSAUXテーブルスペースはデータベースに必要としているシステムテーブルスペースだから、 AWRなど大切なデータと補助データを格納している

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-00376: file ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/s01/oradata/FIXIT/datafile/o1_mf_sysaux_85wkhmrk_.dbf’
cannot be read at this time
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 99
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 122
ORA-06512: at line 1

SYSAUXすべてのデータファイルもバックアップも全部なくした場合にそのテーブルスペースのオブジェクトを再構造する。

データファイルを徹底的になくした場合を除いて、SYSAUXデータファイルが一部のロジック/物理的なベッドブロックがあるから、これもテーブルスペースを再構造する必要がある利用の一つである

例えば以下のエラ：

ORA-00600: [kcbz_check_objd_typ] from MMON slave or its process
ORA-00600: [kdsgrp1] while querying WR% tables from SYSAUX

違ったAWRテーブルに対して、 $ORACLE_HOME/rdbms/admin/ディクショナリーに関連するシステムテーブルの作成スクリプトを格納している、例えば：

WRI$_OPTSTAT catost.sql – Optimizer Statistics Tables
WRI$_ALERT catalrt.sql – Catalog script for server ALeRT
WRH$_* catawrtb.sql – Catalog script for AWR Tables
catawrvw.sql – Catalog script for AWR Views

続いて、SYSAUXのオブジェクトを再構造することに専念する。recreate sysauxのオブジェクトは正常の手段ではない。それもSYSAUXをリカバリする最後のステップである。製品環境に以下の方法勝手に使わないでください：

あとのオペレーションが危険過ぎたから、Oracle専門家しか操作できないから、ここでは紹介しない。

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracleデータリカバリ】ORA-01578エラ解決例

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

ORA-01578エラはOracleによく現れる物理的なベッドブロックエラ(Corruption)で、10gから完全なバックアップとアーカイブログを持っている場合に、blockrecover/recoverコマンドでそのベッドブロックをオンラインでリカバリできる。その前提はデータブロックを含むトラックがまだ使用可能である。

以下の内容はバックアップなしにORA-01578エラを解決する例である。一部ベッドブロックのデータがなくす。：

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;

BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 870212)

ORA-01110: data file 4:

‘/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf’

ORA-06512: at “SYS.DBMS_STATS”, line 15188

ORA-06512: at “SYS.DBMS_STATS”, line 15530

ORA-06512: at “SYS.DBMS_STATS”, line 15674

ORA-06512: at “SYS.DBMS_STATS”, line 15638

ORA-06512: at line 1

RMAN blockreocverコマンドで物理的なベッドブロックを修復する：

RMAN> blockrecover datafile 4 block 870212;

Starting blockrecover at 08-NOV-12

channel ORA_DISK_1: restored block(s) from backup piece 1

piece handle=/s01/flash_recovery_area/G10R25/backupset/2012_08_06/o1_mf_nnndf_TAG20120806T075500_81zd4njn_.bkp tag=TAG20120806T075500

channel ORA_DISK_1: block restore complete, elapsed time: 00:01:16

starting media recovery

archive log thread 1 sequence 467 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_10_31/o1_mf_1_467_893571cm_.arc

archive log thread 1 sequence 468 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_10_31/o1_mf_1_468_893pc84l_.arc

archive log thread 1 sequence 469 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_11_01/o1_mf_1_469_894zsbym_.arc

archive log thread 1 sequence 470 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_11_01/o1_mf_1_470_896b944y_.arc

4_.arc

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of blockrecover command at 11/08/2012 06:19:40

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of log thread 1 seq 466 lowscn 27762151 found to restore

RMAN-06025: no backup of log thread 1 seq 465 lowscn 27762145 found to restore

RMAN-06025: no backup of log thread 1 seq 464 lowscn 27762142 found to restore

必要なアーカイブログがないから。ブロックをリカバリすることが失敗した。

まずはデータブロックがどれのセグメントに属しているかを確認する。けどテーブルデータの場合に、ベッドブロックのデータをなくなる：

SQL> col tablespace_name for a20

SQL> col segment_type for a10

SQL> col segment_name for a20

SQL> col owner for a8

SQL> SELECT tablespace_name, segment_type, owner, segment_name

2 FROM dba_extents

3 WHERE file_id = &fileid

4 and &blockid between block_id AND block_id + blocks – 1;

Enter value for fileid: 4

old 3: WHERE file_id = &fileid

new 3: WHERE file_id = 4

Enter value for blockid: 870212

old 4: and &blockid between block_id AND block_id + blocks – 1

new 4: and 870212 between block_id AND block_id + blocks – 1

TABLESPACE_NAME SEGMENT_TY OWNER SEGMENT_NAME

——————– ———- ——– ——————–

USERS TABLE SYS CORRUPT_ME

SQL> select count(*) from CORRUPT_ME;

select count(*) from CORRUPT_ME

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 870212)

ORA-01110: data file 4:

‘/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf’

SQL> analyze table corrupt_me validate structure;

analyze table corrupt_me validate structure

ERROR at line 1:

ORA-01498: block check failure – see trace file

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/s01/admin/G10R25/udump/g10r25_ora_19749.trc

Corrupt block relative dba: 0x010d4744 (file 4, block 870212)

Bad header found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x000d4744

last change scn: 0x0000.00000000 seq: 0xff flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x000006ff

check value in block header: 0x6323

computed block checksum: 0x0

Reread of rdba: 0x010d4744 (file 4, block 870212) found same corrupted data

*** 2012-11-08 06:23:12.564

table scan: segment: file# 4 block# 870211

skipping corrupt block file# 4 block# 870212

*** 2012-11-08 06:23:36.955

table scan: segment: file# 4 block# 870211

skipping corrupt block file# 4 block# 870212

skipping corrupted block at rdba: 0x010d4744

次は10231 level 10トランザクションでORA-01578エラを避ける例で、もとのベッドブロックのテーブルをコピする：

SQL> alter session set events ‘10231 trace name context forever,level 10’;

Session altered.

SQL> select count(*) from CORRUPT_ME;

COUNT(*)

———-

50857

SQL> create table corrupt_me_copy tablespace users as select * from CORRUPT_ME;

Table created.

SQL> analyze table corrupt_me_copy validate structure;

Table analyzed.

後でテーブルを古いテーブルの名で再命名して、インディクスを再構造すればいい：

SQL> alter table corrupt_me rename to corrupt_me_copy1;

Table altered.

SQL> alter table corrupt_me_copy rename to corrupt_me;

Table altered.

SQL> rebuild indexs

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracleデータリカバリ】ORA-00600[6711]エラ一例

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

あるLinuxの10.2.0.4システムで、ログにORA-00600[6711]内部エラが何度も現れた:

Wed Sep 1 21:24:30 2010

Errors in file /s01/10gdb/admin/YOUYUS/bdump/youyus_smon_5622.trc:

ORA-00600: internal error code, arguments: [6711], [4256248], [1], [4256242], [0], [], [], []

Wed Sep 1 21:24:31 2010

Non-fatal internal error happenned while SMON was doing logging scn->time mapping.

MOSに6711内部エラ関するNoteがあったが、そのファイルが6711エラになった。原因を探ってみると、一部のクラスタのデータディクショナリーにエラがあるかも知れない。けど、そのNoteが未だにエラargumentバラメタの意味を教えてくれない。
corruptionに関するエラだから、obj#,file#,block#に関連している；4256248と4256242 二つの数値がData Block Addressに似ているから、dbaとして扱う。それで、1号データファイルの61938ブロックと61944データブロックに指定していると意味している。ではこれらのブロックがどこのオブジェクトに属しているか、探ってみよう：

SQL> set linesize 200;

SQL> select segment_name, segment_type

2 from dba_extents

3 where relative_fno = 1

4 and (61938 between block_id and block_id + blocks or

5 61944 between block_id and block_id + blocks);

SEGMENT_NAME SEGMENT_TYPE

——————————————————————————— ——————

SMON_SCN_TO_TIME CLUSTER

予想したとおり、clusterである。SMON_SCN_TO_TIMEはSMON_SCN_TIMEテーブルのクラスタで、SMON_SCN_TIMEテーブルはデータベースのscnに該当する時間ステップを記録するために存在している。データディクショナリーのsql.bsqファイルを作成するために、それを確認して、構造をより深刻に理解してください:

cat $ORACLE_HOME/rdbms/admin/sql.bsq|grep -A 24 “create cluster smon_scn_to_time”

create cluster smon_scn_to_time (

thread number /* thread, compatibility */

)

create index smon_scn_to_time_idx on cluster smon_scn_to_time

create table smon_scn_time (

thread number, /* thread, compatibility */

time_mp number, /* time this recent scn represents */

time_dp date, /* time as date, compatibility */

scn_wrp number, /* scn.wrp, compatibility */

scn_bas number, /* scn.bas, compatibility */

num_mappings number,

tim_scn_map raw(1200),

scn number default 0, /* scn */

orig_thread number default 0 /* for downgrade */

) cluster smon_scn_to_time (thread)

create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)

create unique index smon_scn_time_scn_idx on smon_scn_time(scn)

以上のスクリプトでこのクラスタに複数のインディクスが存在していることを確認できる。すべてのオブジェクトを検証してください:

SQL> analyze table SMON_SCN_TIME validate structure;

Table analyzed.

SQL>analyze table SMON_SCN_TIME validate structure cascade;

Table analyzed.

SQL> analyze cluster SMON_SCN_TO_TIME validate structure;

Cluster analyzed.

SQL> analyze cluster SMON_SCN_TO_TIME validate structure cascade;

analyze cluster SMON_SCN_TO_TIME validate structure cascade

ERROR at line 1:

ORA-01499: table/index cross reference failure – see trace file

ここで、トラブルがはっきりとした。トラブルはSMON_SCN_TO_TIMEのインディクスsmon_scn_to_time_idxにある。ロジックエラの可能性が高い。幸いに、トラブルが起きたのはインディクスだけで、トラブルの原因をわかれば解決するには容易くなる:

SQL> alter index smon_scn_to_time_idx rebuild ;

Index altered.

/* インディクスにトラブルが起きた場合に、ただ再構造するだけで何の役も立たない。再構造するときにアラームログにORA-00600[6711]エラになった !!! */

/* トラブルがあるインディクスを徹底的にdropして、再び構造する必要がある! */

SQL> drop index smon_scn_to_time_idx ;

Index dropped.

SQL> create index smon_scn_to_time_idx on cluster smon_scn_to_time;

Index created.

/* ここで、トラブルを解決した、アラームログにエラが報告していない! * /

/* That’s great! * /

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracle ASMデータリカバリ】recovering COD cache read a corrupted block

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

ユーザーがLUNをASM DISKGROUPに追加するときに、あるASM Disk header KFBTYP_DISKHEADが削除されたことに気づき、そのDiskgroupがmountできなくなった。そしてDBAはkfed mergeなどの方法でKFBTYP_DISKHEAD blockをリカバリしたが、まだdiskgroup をmount出来ない。ALERT.logに以下のようなログが現れた：

NOTE: F1X0 found on disk 0 fcn 0.0

NOTE: cache opening disk 1 of grp 1: VOL2 label:VOL2

NOTE: cache opening disk 2 of grp 1: VOL3 label:VOL3

NOTE: cache opening disk 3 of grp 1: VOL4 label:VOL4

NOTE: cache opening disk 4 of grp 1: VOL5 label:VOL5

NOTE: cache opening disk 5 of grp 1: VOL6 label:VOL6

NOTE: cache opening disk 6 of grp 1: VOL7 label:VOL7

NOTE: cache opening disk 7 of grp 1: VOL8 label:VOL8

NOTE: cache opening disk 8 of grp 1: VOL9 label:VOL9

NOTE: cache opening disk 9 of grp 1: VOL10 label:VOL10

NOTE: cache opening disk 10 of grp 1: VOL11 label:VOL11

NOTE: cache mounting (first) group 1/0x3A2C35D6 (DG)

* allocate domain 1, invalid = TRUE

kjbdomatt send to node 0

kjbdomatt send to node 2

Mon Jan 27 02:18:51 CST 2014

NOTE: attached to recovery domain 1

Mon Jan 27 02:18:51 CST 2014

NOTE: starting recovery of thread=1 ckpt=1712.152 group=1

NOTE: advancing ckpt for thread=1 ckpt=1712.153

NOTE: cache recovered group 1 to fcn 0.491275704

Mon Jan 27 02:18:51 CST 2014

NOTE: LGWR attempting to mount thread 1 for disk group 1

NOTE: LGWR mounted thread 1 for disk group 1

NOTE: opening chunk 1 at fcn 0.491275704 ABA

NOTE: seq=1713 blk=154

Mon Jan 27 02:18:51 CST 2014

NOTE: cache mounting group 1/0x3A2C35D6 (DG) succeeded

SUCCESS: diskgroup DG was mounted

Mon Jan 27 02:18:53 CST 2014

NOTE: recovering COD for group 1/0x3a2c35d6 (DG)

WARNING: cache read a corrupted block gn=1 dsk=0 blk=2817 from disk 0

NOTE: a corrupted block was dumped to the trace file

ERROR: cache failed to read dsk=0 blk=2817 from disk(s): 0

ORA-15196: invalid ASM block header [kfc.c:8281] [endian_kfbh] [2147483648] [2817] [173 != 1]

System State dumped to trace file /u01/app/oracle/admin/+ASM/bdump/+asm2_rbal_31204.trc

NOTE: cache initiating offline of disk 0 group 1

WARNING: process 31204 initiating offline of disk 0.3913073997 (VOL1) with mask 0x3 in group 1

WARNING: Disk 0 in group 1 in mode: 0x7,state: 0x2 will be taken offline

NOTE: PST update: grp = 1, dsk = 0, mode = 0x6

Mon Jan 27 02:18:54 CST 2014

ERROR: too many offline disks in PST (grp 1)

Mon Jan 27 02:18:54 CST 2014

WARNING: Disk 0 in group 1 in mode: 0x7,state: 0x2 was taken offline

Mon Jan 27 02:18:54 CST 2014

NOTE: halting all I/Os to diskgroup DG

NOTE: active pin found: 0x0x65faff60

NOTE: active pin found: 0x0x65fb0170

NOTE: active pin found: 0x0x65fb0010

NOTE: active pin found: 0x0x65fb0220

NOTE: active pin found: 0x0x65fb02d0

NOTE: active pin found: 0x0x65fb00c0

NOTE: active pin found: 0x0x65fb0380

Mon Jan 27 02:18:54 CST 2014

ERROR: ORA-15130 in COD recovery for diskgroup 1/0x3a2c35d6 (DG)

ERROR: ORA-15130 thrown in RBAL for group number 1

Mon Jan 27 02:18:54 CST 2014

Errors in file /u01/app/oracle/admin/+ASM/bdump/+asm2_rbal_31204.trc:

ORA-15130: diskgroup “DG” is being dismounted

Mon Jan 27 02:18:54 CST 2014

ERROR: PST-initiated MANDATORY DISMOUNT of group DG

NOTE: cache dismounting group 1/0x3A2C35D6 (DG)

Mon Jan 27 02:18:57 CST 2014

kjbdomdet send to node 0

detach from dom 1, sending detach message to node 0

kjbdomdet send to node 2

detach from dom 1, sending detach message to node 2

Mon Jan 27 02:18:57 CST 2014

Dirty detach reconfiguration started (old inc 23, new inc 23)

List of nodes:

0 1 2

Global Resource Directory partially frozen for dirty detach

* dirty detach – domain 1 invalid = TRUE

138 GCS resources traversed, 0 cancelled

6104 GCS resources on freelist, 6124 on array, 6124 allocated

Dirty Detach Reconfiguration complete

Mon Jan 27 02:18:57 CST 2014

freeing rdom 1

Mon Jan 27 02:18:57 CST 2014

WARNING: dirty detached from domain 1

Mon Jan 27 02:18:57 CST 2014

SUCCESS: diskgroup DG was dismounted

Mon Jan 27 02:18:57 CST 2014

WARNING: PST-initiated MANDATORY DISMOUNT of group DG not performed – group not mounted

Mon Jan 27 02:18:57 CST 2014

Errors in file /u01/app/oracle/admin/+ASM/bdump/+asm2_b001_31755.trc:

ORA-15001: diskgroup “DG” does not exist or is not mounted

Mon Jan 27 02:31:00 CST 2014

ここで、Diskgroupを mountするときに、recovering COD for group 1/0x3a2c35d6 (DG)段階で、あるロジックベッドブロックを見つけた：WARNING: cache read a corrupted block gn=1 dsk=0 blk=2817 from disk 0 NOTE: a corrupted block was dumped to the trace file ERROR: cache failed to read dsk=0 blk=2817 from disk(s): 0それに、このベッドブロックによってORA-15196: invalid ASM block header [kfc.c:8281] [endian_kfbh] [2147483648] [2817] [173 != 1]になった。。

ここの2817はエラになったASM metadataのblock numberである。173はendian_kfbh位置から読み出した数値である。173!=1 ここの1はその位置にあるはずの理論値だが、ブロックに誤ったendian_kfbh情報を読み取ったから、ASM ORA-600エラになった。

ここのrecovering COD for group 1/0x3a2c35d6 (DG) のCOD はasm metadata file number 4 CODと意味している。 Continuing Operation Directory (COD) はそのmetadata file 4 に記録された一つのmetadata blockで完成出来ないオペレーションをCODに記録する。これで、ASM のインスタンスがCrashした場合にリカバリできる。例えばresizeファイルを作成/削除する。その中で、file number 4 blkn=1はKFBTYP_COD_RBつまりロールバックデータで、後のデータはFBTYP_COD_DATAである。

ロールバックできるオペレーションopcodesは以下のようなことを含んでいる：

1 – Create a file

2 – Delete a file

3 – Resize a file

4 – Drop alias entry

5 – Rename alias entry

6 – Rebalance space COD

7 – Drop disks force

8 – Attribute drop

9 – Disk Resync

10 – Disk Repair Time

11 – Volume create

12 – Volume delete

13 – Attribute directory creation

14 – Set zone attributes

15 – User drop

ASM diskgroup がmountしてみるときにFILE number 4 CODのデータを読み取って、オペレーションが完成できない場合にロールバックすることを確保する。

このようなASM file number 4 CODにメータデータベッドブロックが現れた場合に、人工的に内部的なトランザクションを設定して、ASM metadataをPatchしてください。

このようなトラブルが起こったら、まずはASM disk header 100Mのデータをバックアップして、現場を破壊されないようにしてください。

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【データリカバリ】ORA-600[kccpb_sanity_check_2]

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

kccpb_sanity_check_2カーネル関数kernel functionがコントロールファイルの健康を確保する。そのORA-600[kccpb_sanity_check_2]が一般的にalter database mount段階で起こる；そのORA-600[kccpb_sanity_check_2]が起こる原因はコントロールファイル件controlfileブロックヘッダのseq#番号がコントロールファイルより大きいだから、その関数とコントロールファイルの間にロジックが一致していないと認定している。

そのkccpb_sanity_check_2関数は10gR2から導入された。つまり、9iにはこのようなコントロールファイル健康性検証など存在していない。lost writeとstale readを検出するために、その機能を導入した。

一般的にそのORA-600[kccpb_sanity_check_2]に二つのargumentコードを含んでいる:

ARGUMENTS:
Arg [a] seq# in control block header.
Arg [b] seq# in the control file header.
Arg [c] maclean

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [53672], [53643], [0x000000000], [], [], [], []
Current SQL statement for this session:
ALTER DATABASE   MOUNT
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? 700000010007FE0 ?
ksedmp+0290          bl       ksedst               104C1FCD0 ?
ksfdmp+02d8          bl       03F34734             
kgerinv+00dc         bl       _ptrgl               
kgeasnmierr+004c     bl       kgerinv              1105312C0 ? 110211598 ?
                                                   000000000 ? 000015018 ?
                                                   000000000 ?
kccpb_sanity_check+  bl       kgeasnmierr          11019B7D0 ? 1104A0040 ?
013c                                               104DFF150 ? 300000003 ?
                                                   000000000 ? 00000D1A8 ?
                                                   000000000 ? 00000D18B ?
kccbmp_get+00f8      bl       kccpb_sanity_check   FFFFFFFFFFFFFFFF ?
                                                   2700000027 ?
kccsed_rbl+008c      bl       kccbmp_get           1100745A0 ? 104E02124 ?
kccocx+08fc          bl       kccsed_rbl           100000068 ?
kccocf+0140          bl       kccocx               FFFFFFFFFFEB7C8 ? 0CBC08BD8 ?
                                                   16D694B2F ? 110231D90 ?
kcfcmb+0ab4          bl       kccocf               000000000 ? 000000000 ?
                                                   000000002 ? 10041C1F4 ?
kcfmdb+0054          bl       kcfcmb               0FFFED160 ? 7000000B9FF038E ?
                                                   000000003 ? 000000000 ?
                                                   000000003 ? 000000000 ?
                                                   000000000 ? 000000000 ?
adbdrv+06c0          bl       kcfmdb               110262390 ? 7000000BCFFA050 ?
                                                   7000000BCFFA470 ? 104F38E08 ?
opiexe+2d34          bl       adbdrv               
opiosq0+1ac8         bl       opiexe               000000001 ? 000000000 ?
                                                   FFFFFFFFFFF9148 ?
kpooprx+016c         bl       opiosq0              300000020 ? 110231D90 ?
                                                   7000000BD1038F8 ?
                                                   A400011019B7D0 ? 000000000 ?
kpoal8+03cc          bl       kpooprx              FFFFFFFFFFFB954 ?
                                                   FFFFFFFFFFFB700 ?
                                                   1600000016 ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   000000000 ? 1103ABA18 ?
opiodr+0b2c          bl       _ptrgl               
ttcpip+1020          bl       _ptrgl               
opitsk+117c          bl       01FC6908             
opiino+09d0          bl       opitsk               1EFFFFD920 ? 000000000 ?
opiodr+0b2c          bl       _ptrgl               
opidrv+04a4          bl       opiodr               3C102A89D8 ? 404C72CF0 ?
                                                   FFFFFFFFFFFF8E0 ? 0102A89D0 ?
sou2o+0090           bl       opidrv               3C02A2895C ? 400000020 ?
                                                   FFFFFFFFFFFF8E0 ?
opimai_real+01bc     bl       01FC3174             
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0090         bl       main                 000000000 ? 000000000 ?

    last wait for 'control file sequential read' wait_time=0.000511 sec, seconds since wait started=0
                file#=0, block#=27, blocks=1
                blocking sess=0x0 seq=23
    Dumping Session Wait History
     for 'control file sequential read' count=1 wait_time=0.000511 sec
                file#=0, block#=27, blocks=1
     for 'control file sequential read' count=1 wait_time=0.000957 sec
                file#=0, block#=1, blocks=1
     for 'CSS operation: action' count=1 wait_time=0.036477 sec
                function_id=41, =0, =0
     for 'CSS operation: action' count=1 wait_time=0.000200 sec
                function_id=41, =0, =0
     for 'CSS initialization' count=1 wait_time=0.060291 sec
                =0, =0, =0
     for 'control file sequential read' count=1 wait_time=0.000547 sec
                file#=0, block#=1, blocks=1
     for 'control file sequential read' count=1 wait_time=0.003763 sec
                file#=0, block#=3, blocks=20
     for 'control file heartbeat' count=1 wait_time=3.906271 sec
                =0, =0, =0
     for 'control file sequential read' count=1 wait_time=0.020452 sec
                file#=0, block#=3, blocks=20
     for 'control file sequential read' count=1 wait_time=0.000310 sec
                file#=0, block#=1, blocks=1

前の例はORA-600[kccpb_sanity_check_2]実際インスタンスである。エラになったが、controlfileを使っていたから、バラメタcontrol_filesを変更するだけで、二つ目の’コントロールファイルがmountするときにエラになっていないことを気づき、一つのコントロールファイルがトラブルが起きたと確認できる。故に、ddでコピすればいい。

このインスタンスmount段階のORA-600[kccpb_sanity_check_2]エラに対して、いくつの解決策もある：

1、コントロールファイルがいろんなパスに使われた場合に、すべてのコントロールファイルもこわれたとは限らない。control_filesバラメタを変更することで、一つ一つに試してください。無傷なコントロールファイルとこわれたコントロールファイルと一緒にcontrol_filesバラメタに存在している場合に、mountができなくなる。

2、バックアップから健全なコントロールファイルをrestoreする

3、バックアップがなければ人工的にコントロールファイルを再構造するしかない。

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracleデータリカバリ】ORACLEデータベース起動startup中止shutdownについてのファイル総集編

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

ORACLEデータベースを起動するときに、最初に起動するのはインスタンスで、つまり、nomount段階である。後でCONTROL_FILES バラメタで指定したコントロールファイル位置でデータベースMOUNT段階をロードする。次のステップはデータベースを起動する。このステップにはデータファイルとredoログを含んでいる。そして、前回のシャットダウンが乱暴しすぎた場合に、ロールフォワードしてください。実際には（apply redo）とロールバックするときに提出されていないトランザクションを完成する必要がある。

データベースの中止と起動にもいくつの階段に分けられる。まずはデータベースがクロスされて、データベースに変化しない。つまりデータファイルとredo logfileログファイルクロスされて、そしてdismount段階に入る。インスタンスがデータベースとのつながりがなくなる。データベースがunmountされたあとにOracleデータベースがコントロールファイルをクロスする。次のステップはSGAを削除して、バックグラウンドプロセスを中止する。これでインスタンスがクロスされた。

シャットダウンモードにもいろいろある。例えば、normal,immediate,transactionとabort.で、SHUTDOWN ABORTを除いて、データベースはSGAに必要としているデータをデータファイルとredo logfileに書き込む。もしSHUTDOWN ABORTあるいは意外中止が起こったが、SGAが必要としているデータをまだ抽出していない場合に、次データベースを起動するときに、crash recoveryしてください。これはOracleデータベースによって自主的に完成する。

Startup upgrade/migrateあるいは _system_trig_enabled = FALSEを設定するのは起動する途中でトリガーを禁止する

Startup Slow / Hang

Startup can hang in any of the stages like nomount,mount or open stage. Following are some of the known issues reported so far:

Note 1367724.1 Startup Hangs at Mount Stage after AIO is Enabled on Linux

Note 429390.1 Database Startup Takes Longer time After Upgrade To 10.x

Note 552019.1 New Install and Creation of a 10gR2 database Hangs at Startup Nomount

Note 344933.1 DB Startup Can Hang if USE_INDIRECT_DATA_BUFFERS=TRUE and AWE_WINDOW_MEMORY Is Set Too High

Note 838451.1 Startup Hangs When “Processes” Parameter is Higher Than 10800

Note 1076092.1 Instance Startup Hangs After Creating New Undo Tablespace and Switching Between Old and New

Note 1476526.1 Database STARTUP NOMOUNT very slow after migrate to T4-4 Servers SPARC 64-Bit

Note 1475621.1 Oracle Database Startup And SGA Value Change Takes A Very Long Time To Complete

ORA-27102 Errors

ORA-27102 errors normally occurs due to memory issues.The Common causes could due to Semaphore Kernel misconfigurations,Memory related Ulimit settings,RAM and swap configurations.

Note 274092.1 LOCK_SGA on Windows fails with ORA-27102

Note 219752.1 ORA-27102 OSD-00034 Starting Database on Windows 2000

Note 390547.1 ORA-27102 Cannot Startup Instance via sqlplus

Note 1060677.6 ORA-27100 ORA-27102 Trying to Start 8.0.4 Database

Note 399895.1 Database Startup On Solaris 10 Fails With Ora-27102 Out Of Memory Error

Note 467707.1 ORA-27102: Out Of Memory on Oracle 10g Solaris 10 (x86-64)

Note 7272646.8 Bug 7272646 – Linux-x86_64: ORA-27103 on startup when MEMORY_TARGET > 3g

Note 790205.1 UNABLE TO START INSTANCE WITH LARGE SGA ORA-27102 SVR4 ERROR: 22: INVALID ARGUMENT

Note 263537.1 ORA-27102 out of memory When Trying To Start Database With SGA> 4G

Note 842881.1 ORA-27102, OSD-00031 Unable To Extend Memory_max_target And Memory_target Past 2GB

Note 1449714.1 Startup Fails With ORA-27102 After Upgrade From 10.2.0.3 To 10.2.0.4

Note 461519.1 ORA-27102 Database Will Not Start With SHMMAX Set To 8589934592 (8GB)

Note 351930.1 Receiving ORA-27102 For 32-Bit Oracle While Allocating Sga_max_size Greater Than 4gb

Note 1351705.1 Startup Fails with ORA-27102 and ‘SVR4 Error 28: No space left on device’

Note 1292225.1 ORA-27102 OSD-00025 O/S-Error: (OS 1453) When Lock_sga is Set to True

Note 401077.1 Ora-27102: Out Of Memory: Linux Error: 12: Cannot Allocate Memory with LOCK_SGA=TRUE

Note 577898.1 ORA-27102 Received At Startup When LOCK_SGA Is Set Although Enough Memory Is Available

Note 301830.1 Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

Note 859898.1 The ORA-27102 error is generated on Solaris 10 having apparently correct settings of kernel parameters

Note 779861.1 ORA-27102: Out Of Memory And SVR4 Error: 22: Invalid argument During Startup On Solaris10 Server With Multiple DB Instances

ORA-27300 Errors

These errors are generally reported when the Operating System called for error or when there was a connection killed or a network interconnection failures or an OS configuration issue.The error ORA-27300 will also be accompanied by ora-27301 and ora-27302

Note 560309.1 Database Cannot Start Due to Lack of Memory

Note 579365.1 Troubleshooting ORA-27300 ORA-27301 ORA-27302 errors

Note 314179.1 Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302

Note 812115.1 Startup Fails With ORA-27300: Os System Dependent Operation:Fork Failed With Status:17

Note 814896.1 Startup Fails With ORA-27300: OS system dependent operation:IPC init failed with status: 65

Note 949468.1 Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-64 Errors

This error could occur when the database init.ora parameter calling for more resources than the Operating System is configured to provide.The parameters could be PROCESSES,DB_BLOCK_SIZE,SGA and more.

Note 470742.1 ORA-00064 Starting Database With Dispatchers

Note 556258.1 ORA-64 if Db_keep_cache_size is Set to 70 Gigs

Note 283980.1 ORA-00064: object is too large to allocate on this O/S

Note 1232463.1 ORA-00064: Object Is Too Large To Allocate On This O/S

Note 4466378.8 Bug 4466378 – ORA-64 does not report the caller description

Note 179301.1 Instance Startup Fails With ORA-00064 After Increasing Processes

Note 1457812.1 ORA-00064 Error Reported After Increasing Processes Parameter value

Note 18255.1 OERR: ORA 64 object is too large to allocate on this O/S <num, num>

Note 310838.1 Instance Startup failed with ORA-00064 when processes parameter set to High Value

Note 886312.1 Database startup can fail with ORA-00064 Errors with huge sga_target of over 40Gig

Note 1328620.1 ASM Instance Is Not Coming Up ORA-00064 (1,4468736,Kfchl Array) Kfchl Array

Note 815954.1 ORA-64 when starting ASM instance after changing db_cache_size and shared_pool_size

Note 7659217.8 Bug 7659217 – ORA-64 attempting to startup with a large SGA / buffer cache size

Note 386855.1 ‘startup migrate’ failed with ORA-64 while upgrading to 10.2.0.2 with DBUA

ORA-27123 Errors

Note 167250.1 ORA-27123 When Connecting As Non Oracle User

Note 207743.1 Getting ORA-27123 when trying to startup Oracle

Note 115753.1 UNIX: Resolving the ORA-27123 error

Note 307323.1 Ora-27123 When Creating New Database

Note 167250.1 ORA-27123 When Connecting As Non Oracle User

Note 207743.1 Getting ORA-27123 when trying to startup Oracle

Note 61912.1 OERR: ORA-27123 unable to attach to shared memory segment

Note 250966.1 ORA-27123 During Startup, Immediately after a Shutdown

Note 872532.1 ORA-27123 on RHEL5 (PAE) 32bit when SGA larger than 2Gb

Note 552633.1 Starting the Database with SGA_TARGET set Fails with ORA-27123

Note 437582.1 Export Fails With EXP-00056 ORA-01034 ORA-27123 EXP-00005

Note 733974.1 ORA-27123 During Startup Nomount in 11G on AIX, Failure in SHMAT()

Note 369262.1 Startup with Maximum SGA Fails With Ora-27123 Unable To Attach Sga

Note 735187.1 Cannot Create Database With DBCA – Startup Nomount Gives ORA-27123

Note 1268668.1 Oracle Database 10g R2 Version 10.2.0.3 – Receiving Ora-27123 Errors

Note 390766.1 ora-27123 on Solaris 10 with larger than 1.6 – 1.7Gb SGA

Note 384262.1 ORA-01034, ORA-27123, HP-UX Error 22 Connecting Via Oracle Net

Note 149070.1 VMS: Connections fail with ORA-1034 and ORA-27123 errors

Note 453930.1 Connecting to the database fails with ORA-12547, Ora-600 [Ksmlsge1], ORA-27123, Error 13

Note 207797.1 ORA-1034 ORA-27123 SVR4 Error: 13: Permission denied when other then Oracle user

Note 356957.1 OpenVMS: Client Connections Report ORA-1034, ORA-27123, %SYSTEM-W-REGISFULL

Note 1401726.1 ORA-27123 When Starting Instance With No Setting Of SGA_TARGET Or SGA_MAX_SIZE

Note 730107.1 Getting ORA-27123 & not able to run Oracle when logging to server by a user other than that installed Oracle although it belongs to the same group

ORA-1081 Errors

This error could occur when we try to startup an instance that is already running or if the shared segments/semaphores already exist.

Note 1010214.6 ORA-1081: Starting Instance

Note 18657.1 OERR: ORA 1081 cannot start already-running Oracle – shut it down first

NFS Related Issues

Note 8418190.8 Bug 8418190 – Direct NFS warnings during database startup

Note 236794.1 NFS Locking Problems Encountered During Database Startup

Note 971406.1 DATABASE STARTUP HANGS AT MOUNTING CONTROLFILE WHEN DNFS IS ENABLED

Note 1430654.1 Database Startup Failed with “Direct NFS: please check that oradism is setuid”

Note 430920.1 NetApp: Using ‘nolock’ NFS Mount Option with non-RAC Systems Results in Database Corruption

ORA-600 /ORA-7445 Errors

Note 435436.1 ORA-00600: [kccpb_sanity_check_2] During Instance Startup

Note 101589.1 Startup database returns ORA-00600 [ktpridestroy2]

Note 405602.1 ORA-600 [16305] While Starting Up the Database

Note 466596.1 Core Dumps In skgfqio() – Database Startup Hangs/Spins

Note 336447.1 Startup Database Produces ORA-00600: [Keltnfy-Ldminit]

Note 847786.1 Can Not Open Database After Shutdown get ORA-7445 [kewa_dump_time_diff]

Note 779071.1 Unable to Start Instance Due to ORA-600 [skgmhash] after a clean shutdown

Note 549000.1 ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON

Note 453775.1 Database Startup Fails With ORA-7445 [INVALID PERMISSIONS FOR MAPPED OBJECT] After Creation of User LBACSYS

Transaction Recovery Slowness

There could be slowness in the database during the open phase when the database is busy performing transaction recovery.

Note 1494886.1 Database Transaction Recovery

Note 414242.1 Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery

Note 12934890.8 Bug 12934890 – Startup hangs waiting for row cache lock due to open transaction against UNDO$

ORA-704 Errors

This is a general error reported at startup when there is some problem during processing of bootstrap information.There should be an accompanying error/s.

Note 18494.1 OERR: ORA 704 “bootstrap process failure”

Note 560417.1 Recovery Through Upgrade returns ORA-1092 on Open

Note 1349722.1 Ora-00704,Ora-39700: Database Must Be Opened With Upgrade Option

Note 435337.1 Unable To Open Database Before/After Upgrade – ORA-00704 ORA-39700 ORA-01092

Note 1383179.1 Unavailable Bootstrap Object ACCESS$ Causes ORA-704 ORA-604 ORA-942 When Opening Database

Note 1345417.1 After failed upgrade, startup from a restored backup fails on ORA-00704 and ORA-39700

ORA-9968 Errors

There are some client shadow processes hanging. Although the lk< SID> file is deleted the hanging processes still have a lock on the open file handle. This prevents the database to startup although a new lk file can be created successfully. An oracle process (background or shadow process) that exists while the instance is not started (crashed or not cleanly stopped) can have a lock on a file while this file is actually removed from the system. This is because on UNIX there is still a lock on the open file handle.

Note 467251.1 ORA-09968, ORA-01102 When Starting a Database

Note 160395.1 Database Startup Fails with ORA-1102 and ORA-9968

Note 1488147.1 Instance Startup Raises Error ORA-09968: unable to lock file (Doc ID 1488147.1)

ORA-12547 Errors

The error ORA-12547 indicates that the communication channel has been broken. It’s most often thrown because the other end of the process went away unexpectedly.

Note 1307075.1 Oracle Database Fails to Start with Error ORA-12547

Note 381566.1 connect / as sysdba Fails with Ora-12547 And Tns-12514

Note 744512.1 Ora-12547: Tns:Lost Contact Creating Database After Clean Installation

ORA-27125 Errors

Note 1067569.6 HP-UX: ORA-27125: NOT OWNER TRYING TO LOCK THE SGA IN MEMORY

Note 199068.1 OpenVMS: Instance startup fails with ORA-27125 and %SYSTEM-F-VA_IN_USE

Note 121983.1 Starting Database Fails on Solaris with ORA-27126 or ORA-27125 Using LOCK_SGA

Note 577428.1 OpenVMS: Following an Oracle RELINK, Database Instance Startup fails with ORA-27125 or ORA-7217

ORA-1157 Errors

The background process was not able to find one of the datafiles.The database will prohibit access to this file but other files will be unaffected.However, the first instance to open the database will need to access all online datafiles.Accompanying messages from the operating system will describe why the file was not found.

Note 184327.1 ORA-1157 Troubleshooting

Note 1035992.1 Oracle Troubleshooting

Note 212053.1 ORA-1157/ORA-1110 Trying To Open The Database

Note 145194.1 ORA-1157 ORA-1110 ORA-27086 Starting up Database

Note 444151.1 ORA-01157 on Database Startup After Dropping an Alias

Note 301635.1 ORA-01157, ORA-01110, ORA-27046 Starting A Restored Database

Note 429912.1 ORA-01157 ORA-01110 ORA-27086 after crash prevents database from opening

Note 256835.1 Database Startup Fails With ORA-1110, ORA-1157, ORA-27092 Trying Startup From ‘at’ or ‘cron’ on HP-UX

Other Issues

Note 1113864.1 MBIND: Cannot Allocate Memory On Startup

Note 578536.1 MBIND: CANNOT ALLOCATE MEMORY ON STARTUP

Note 6795133.8 Bug 6795133 – Startup delayed by QMNC queries

Note 301072.1 Dbstart Fails With Ora-01031 When Called From User Root

Note 1286665.1 ORA-00371: Not Enough Shared Pool Memory signalled on Startup

Note 779356.1 Database not starting up with errors ORA-01092 ORA-24324 ORA-01041

Note 1176443.1 ORA-4031 During Startup Nomount using RMAN without parameter file (PFILE)

Note 839789.1 ORA-12853 / ORA-4031 or ORA-4030 on Instance Startup With increased SGA size

Common Errors / Issues During Database Shutdown

The most common issue observed while bringing down the database is shutdown immediate hang. The main reasons for Shutdown immediate hang is:

– processes still continue to be connected to the database and do not terminate.

– SMON is cleaning temp segments or performing delayed block cleanouts.

– Uncommitted transactions are being rolled back.

The below section provides the consolidated list of known issues during shutdown. The documents mentioned in the below section can be specific to platform or database versions.

Shutdown Slow / Hang

Note 1197314.1 Shutdown Normal Hung On ORA_J00# Process

Note 309230.1 Database Doesn’t Shutdown Immediate During Server Boot

Note 1039389.6 Alert Log: Shutdown Waiting for Active Calls to Complete

Note 305666.1 Shutdown is Cancelled With ORA-1013 After Waiting for an Hour

Note 1194229.1 Database shutdown immediate Hangs: Startup can also hang

Note 1183213.1 Shutdown Normal or Immediate Hang Waiting for MMON process

Note 428688.1 Bug 5057695: Shutdown Immediate Very Slow To Close Database

Note 416658.1 Shutdown Immediate Hangs / Active Processes Prevent Shutdown

Note 437876.1 Database Does Not Shutdown Cleanly When Oracle Service Is Restarted

Note 304414.1 Shutdown hangs in 9i with: SHUTDOWN: waiting for logins to complete

Note 332177.1 Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort

Note 13440516.8 Bug 13440516 – Index skip scan cannot be interrupted – can block shutdown

Note 12879056.8 Bug 12879056 – Index skip scan cannot be interrupted – can block shutdown

ORA-1031 Errors

Note 309059.1 Oradim Command Fails to Shutdown Database(s) with ORA-01031 under 9.2.0.6

Note 846679.1 Ora-1031 Error Stopping Database Or Permission Denied Error Running Lsnrctl

Transaction Recovery

Note 375935.1 What To Do and Not To Do When ‘shutdown immediate’ Hangs

Note 1076161.6 Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery

Note 414242.1 Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery

Note 100054.1 Transaction Rollback after a failed operation or during Database Shutdown

ORA-600 / ORA-7445 Errors

Note 604067.1 Ora-600[3708] On Database Shutdown

Note 455181.1 ORA-00600[17302] During Shutdown Immediate

Note 470362.1 ORA-07445 With kpogup At Database Shutdown

Note 1135453.1 Database Hung On Shutdown After ORA-600 [KGHFRE3] Error

Note 359563.1 ORA-00600: Internal Error Code, Arguments: [17302], [2] During Shutdown

Note 435926.1 Shutdown Database Erroring ORA-600 [Librarycachenotemptyonclose], []

Note 8519322.8 Bug 8519322 – ORA-600 [17148] / ORA-600 [730] on database shutdown

Note 1326908.1 Ora-00600: [3708], ORA-600 [2103] When Database shutdown on IBM:Linux on System Z

ORA-24324 Errors

Note 794293.1 ORA-24324 During Shutdown

Note 1168554.1 Ora-24324 And Ora-1041 Errors Trying To Startup Or Shutdown The Database

DISM

Note 1001248.1 On Solaris 9 Systems, Oracle Shutdown May Hang If Utilizing Dynamic Initmate Shared Memory (DISM)

Memory Related Errors

Note 1319253.1 “ERROR: SGA memory leak detected” message in alert.log on database shutdown

Other Issues

Note 1017085.102 ORA-01122, ORA-01210, ORA-01110: On Database Shutdown

Note 429603.1 ORA-29702 During Automatic Shutdown of Database using ASM

Note 1022414.6 ORA-01033 DATABASE INITIALIZATION OR SHUTDOWN IN PROGRESS

Note 784754.1 11g – Receiving Ora-27167 Error On Database Startup or Shutdown

Note 419651.1 Event 10621 and Event 10626/10629 Causes Shutdown Immediate to Hang

Note 118228.1 ALERT: Hang During Startup/Shutdown on Unix When System Uptime > 248 Days

Note 343031.1 How to deal with an ORA-01033 ‘Oracle startup or shutdown in progress’ error

Note 18302.1 OERR: ORA 106 cannot startup/shutdown database when connected to a dispatcher

Note 763932.1 Shutdown Error In EM: Execution Failed Due To Binary Missing Or Permission Issues

Note 10194190.8 Bug 10194190 – Solaris: Process spin / ASM and DB crash if RAC instance up for > 248 days

Note 1001248.1 On Solaris 9 Systems, Oracle Shutdown May Hang If Utilizing Dynamic Initmate Shared Memory (DISM)

Note 760968.1 Database Startup, Shutdown Or New Connections Hang With Truss Showing OS Failing Semtimedop Call With Err#11 EAGAIN

Issues specific to automatic shutdown and startup

This is specific to the automatic shutdown and startup that can be configured with the dbora / dbshut / dbstart scripts.

Automatic Startup Failure

The key to diagnosing automatic startup failures is to determine where startup fails. This can be done via the following steps:

Determine if instance starts manually as Oracle software owner.

Determine if instance starts via dbstart command run as Oracle software owner.

Determine if instance starts when root runs following dbstart command:

su – $ORA_OWNER -c $ORA_HOME/bin/dbstart

where $ORA_OWNER is set to Oracle software owner.

Determine if instance starts when running as root the OS script which calls dbstart, ie “/etc/init.d/dbora start”. NOTE: Running via sh -x command will show each command as it is run from script to better see what is going on.

#> sh -x /etc/init.d/dbora start

Automatic Shutdown Failure

As with automatic startup, the key to diagnosing automatic shutdown failures is to determine where shutdown fails. This can be done via following steps:

Determine if instance stops manually as Oracle software owner.

Determine if instance stops via dbshut command run as Oracle software owner.

Determine if instance stops when root runs command

su – $ORA_OWNER -c $ORA_HOME/bin/dbshut

where $ORA_OWNER is set to Oracle software owner.

Determine if instance stops when running as root the OS script which calls dbshut, ie “/etc/init.d/dbora stop”. NOTE: Running via sh -x command will show each command as it is run from script to better see what is going on.

#> sh -x /etc/init.d/dbora stop

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

【Oracleデータリカバリ】ORA-00600[kdBlkCheckError]エラ解析

2014/01/25 by admin Leave a Comment

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

kdBlkCheckErrorはKernel Data Block Check Errorである。db_block_checking!=false が損害を見つけ出したらORA-600 [kddummy_blkchk] / ORA-600 [kdBlkCheckError] エラになる。そして中止される。

kdBlkCheckError/kddummy_blkchkに大量な検証コードを含んでいて、各検証コードにデータブロックもロジック分析に該当している。もしmismatchが存在しているならデータブロックにロジックトラブルが起きていると見なすべし。

例えば検証コード23001はWrong total extent countと意味している。。

もしORA-00600[kdBlkCheckError]が現れたら、一般的に、その原因はORACLEのBUG あるいはメモリーにエラがある。

ORA-00600[kdBlkCheckError]エラに関するは以下の通り：

NB	Bug	Fixed	Description
	17447078	12.1.0.2, 12.2.0.0	Diagnostic enhancement for ORA-600 [kdBlkCheckError] .. [18007] errors
	14400110	11.2.0.4, 12.2.0.0	Bad redo / ORA-600 [kdBlkCheckError] .. [6135] for opcode 19.1 redo
	12349316	11.2.0.4, 12.2.0.0	DBMS_SPACE_ADMIN.TABLESPACE_FIX_BITMAPS fails with ORA-600 [kddummy_blkchk] / ORA-600 [kdBlkCheckError] / ORA-607
	14351566	11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1	ORA-600 [kclchkblk_4] when doing flash back
	16347904	11.2.0.4, 12.2.0.0	Corrupt block with check code 6101, 6110 or 6255 on compressed table with DML workload
	17325413	12.1.0.2	Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption
	14741727	11.2.0.2.9, 11.2.0.2.BP19, 11.2.0.3.BP12, 11.2.0.3.BP13, 12.1.0.1	Fixes for bug 12326708 and 14624146 can cause problems – backout fix
	14551844	11.2.0.4, 12.1.0.1	ORA-600 [kdblkcheckerror] [6126] / ORA-600 [17182] for DELETE / INSERT (QMD / QMI) in COMPRESS BASIC table due to negative avsp
	13804294	11.2.0.3.4, 11.2.0.3.BP07, 11.2.0.4, 12.1.0.1	Internal errors, corruptions, using pipelined function whose rows raise exceptions
	13715932	11.2.0.4, 12.1.0.1	CREATE TABLE fails with ORA-600 [kddummy_blkchk] with large datafile
*	13605839	11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1	ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds]. Corruption in Rollback with Clusterwide Global Transactions in RAC
	12417369	11.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1	Block corruption from rollback on compressed table
*	10205230	11.2.0.1.6, 11.2.0.1.BP09, 11.2.0.2.2, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1	ORA-600 / corruption possible during shutdown in RAC
	10180121	11.2.0.3, 12.1.0.1	ORA-600 [kdBlkCheckError] .. [6251] / block corruption during parallel DML
+	9724970	11.2.0.1.BP08, 11.2.0.2.2, 11.2.0.2.BP02, 11.2.0.3, 12.1.0.1	Block Corruption with PDML UPDATE. ORA_600 [4511] OERI[kdblkcheckerror] by block check
*	9711859	10.2.0.5.1, 11.1.0.7.6, 11.2.0.2, 12.1.0.1	ORA-600 [ktsptrn_fix-extmap] / ORA-600 [kdblkcheckerror] during extent allocation caused by bug 8198906
	9541485	11.2.0.4, 12.1.0.1	Create materialized view on views based on CON$ fails with ORA-600 [kdBlkCheckError]
*	9406607	11.2.0.1.3, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1	Corrupt blocks in 11.2 in table with unique key. OERI[kdBlkCheckError] by block check
	9295217	11.2.0.2, 12.1.0.1	ORA-600 [ktsk_dba_to_hwm-1] / corruption during SHRINK of HWM
+	9019113	11.2.0.1.BP02, 11.2.0.2, 12.1.0.1	ORA-600 [17182] for OLTP COMPRESS table in OLTP Compression REDO during RECOVERY
	8720802	10.2.0.5, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.1	Add check for row piece pointing to itself (db_block_checking,dbv,rman,analyze)
*	8331063	11.2.0.3, 12.1.0.1	Corrupt Undo. ORA-600 [2015] during rollback in undo block for COMPRESS table with SUPPLEMENTAL LOGGING
	8277580	11.1.0.7.2, 11.2.0.1, 11.2.0.2, 12.1.0.1	Corruption on compressed tables during Recovery and Quick Multi Delete (QMD).
	6523037	11.2.0.1.BP07, 11.2.0.2.2, 11.2.0.2.BP01, 11.2.0.3, 12.1.0.1	Corruption / ORA-600 [kddummy_blkchk] [6110] on update
	8437213	10.2.0.4.3, 10.2.0.5, 11.1.0.7.7, 11.2.0.1	ASSM first level bitmap block corruption
	8360192	11.1.0.7.6, 11.2.0.1	ORA-600 [kdBlkCheckError] [6110] / corruption from insert
*	8198906	10.2.0.5, 11.2.0.1	OERI [kddummy_blkchk] / OERI [5467] for an aborted transaction of allocating extents
	7715244	11.1.0.7.2, 11.2.0.1	Corruption on compressed tables. Error codes 6103 / 6110

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供 携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com