Database SOS – 第27页 – Professional Database Recovery Support@+8617813235971

fdisk分区导致asm disk破坏数据库恢复

尝试mount data磁盘组

SQL> alter diskgroup DATADG mount 
NOTE: cache registered group DATADG number=1 incarn=0xbc43fafd
NOTE: cache began mount (first) of group DATADG number=1 incarn=0xbc43fafd
NOTE: Assigning number (1,0) to disk (/dev/raw/raw2)
Thu Jun 02 10:14:33 2022
NOTE: GMON heartbeating for grp 1
GMON querying group 1 at 27 for pid 27, osid 3853
NOTE: Assigning number (1,1) to disk ()
GMON querying group 1 at 28 for pid 27, osid 3853
NOTE: cache dismounting (clean) group 1/0xBC43FAFD (DATADG) 
NOTE: messaging CKPT to quiesce pins Unix process pid: 3853, image: oracle@node1 (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0xBC43FAFD (DATADG) 
NOTE: cache ending mount (fail) of group DATADG number=1 incarn=0xbc43fafd
NOTE: cache deleting context for group DATADG 1/0xbc43fafd
GMON dismounting group 1 at 29 for pid 27, osid 3853
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
ERROR: diskgroup DATADG was not mounted
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "1" is missing from group number "1" 
ERROR: alter diskgroup DATADG mount
Thu Jun 02 10:14:33 2022
ASM Health Checker found 1 new failures

报错信息比较明显 datadg的disk number 为1的磁盘丢失了。通过fdisk确认磁盘情况

Disk /dev/sdb: 42.9 GB, 42949672960 bytes
64 heads, 32 sectors/track, 40960 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0006c2be

   Device Boot      Start         End      Blocks   Id  System

Disk /dev/sda: 53.7 GB, 53687091200 bytes
64 heads, 32 sectors/track, 51200 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00061443

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           2        2049     2097152   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2            2050       10241     8388608   82  Linux swap / Solaris
Partition 2 does not end on cylinder boundary.
/dev/sda3           10242       12289     2097152   83  Linux
Partition 3 does not end on cylinder boundary.
/dev/sda4           12290       51200    39844864    5  Extended
Partition 4 does not end on cylinder boundary.
/dev/sda5           12291       14338     2097152   83  Linux
/dev/sda6           14340       50178    36699136   83  Linux
/dev/sda7           50180       51200     1045504   83  Linux

Disk /dev/sdc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x1b3fba6b

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        1045     8393931   83  Linux
/dev/sdc2            1046       26108   201318547+  83  Linux

Disk /dev/sdd: 536.9 GB, 536870912000 bytes
255 heads, 63 sectors/track, 65270 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x4c63ecad

   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1       65270   524281243+  83  Linux

Disk /dev/sde: 536.9 GB, 536870912000 bytes
255 heads, 63 sectors/track, 65270 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sdf: 536.9 GB, 536870912000 bytes
255 heads, 63 sectors/track, 65270 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

根据客户反馈,异常的应该是一个500G的磁盘,而其中sdb为分区,通过kfed命令分析,确认sdc1为ocr磁盘,sdc2为datadg的一块磁盘,另外一块磁盘应该在sdd,sde,sdf三者之中,通过kfed分析sde,sdf均不可能是asm disk(一块是文件系统,一块是彻底没有使用的空盘),如果datadg的磁盘没有丢失,那应该就是sdd这块磁盘,通过dd 磁盘100M空间,然后通过kfed进行分析确认

E:\TEMP\xff>kfed read sdd.dd
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: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
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
006648400 00000000 00000000 00000000 00000000  [................]
        Repeat 26 times
0066485B0 00000000 00000000 4C63ECAD 01000000  [..........cL....]
0066485C0 FE830001 003FFFFF CB370000 00003E7F  [......?...7..>..]
0066485D0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
0066485F0 00000000 00000000 00000000 AA550000  [..............U.]
006648600 00000000 00000000 00000000 00000000  [................]
  Repeat 223 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

E:\TEMP\xff>kfed read sdd1.dd
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: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
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
006768400 00000000 00000000 00000000 00000000  [................]
        Repeat 26 times
0067685B0 00000000 00000000 70D364B4 FE000000  [.........d.p....]
0067685C0 FE83FFFF D13FFFFF BB7603EB 00003A93  [......?...v..:..]
0067685D0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
0067685F0 00000000 00000000 00000000 AA550000  [..............U.]
006768600 02038201 00000008 80000001 826037C1  [.............7`.]
006768EA0 00000079 00800105 0000007A 00800105  [y.......z.......]
006768EB0 0000007C 00800105 0000007D 00800105  [|.......}.......]
0067693C0 0000015C 00800105 0000015D 00800105  [\.......].......]
0067693D0 0000015F 00800105 00000160 00800105  [_.......`.......]
0067693E0 00000161 00800105 00000163 00800105  [a.......c.......]
0067693F0 00000164 00800105 00000166 00800105  [d.......f.......]
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

E:\TEMP\xff>kfed read sdd.dd blkn=1|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            2 ; 0x002: KFBTYP_FREESPC
kfbh.datfmt:                          2 ; 0x003: 0x02
kfbh.block.blk:                       1 ; 0x004: blk=1
kfbh.block.obj:              2147483649 ; 0x008: disk=1
kfbh.check:                  2197087544 ; 0x00c: 0x82f4e538
kfbh.fcn.base:                   616391 ; 0x010: 0x000967c7
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdfsb.aunum:                         0 ; 0x000: 0x00000000
kfdfsb.max:                         254 ; 0x004: 0x00fe
kfdfsb.cnt:                         254 ; 0x006: 0x00fe
kfdfsb.bound:                         0 ; 0x008: 0x0000
kfdfsb.flag:                          1 ; 0x00a: B=1
kfdfsb.ub1spare:                      0 ; 0x00b: 0x00
kfdfsb.spare[0]:                      0 ; 0x00c: 0x00000000
kfdfsb.spare[1]:                      0 ; 0x010: 0x00000000
kfdfsb.spare[2]:                      0 ; 0x014: 0x00000000

通过上述信息分析,基本上可以确认sdd磁盘以前是asm disk,但是被fdisk进行了分区,基于这种情况,通过对磁盘组进行修复

E:\TEMP\xff>kfed read sdd.ok
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483649 ; 0x008: disk=1
kfbh.check:                   424926402 ; 0x00c: 0x1953dcc2
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        1 ; 0x024: 0x0001
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:             DATADG_0001 ; 0x028: length=11
kfdhdb.grpname:                  DATADG ; 0x048: length=6
kfdhdb.fgname:              DATADG_0001 ; 0x068: length=11
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             33074858 ; 0x0a8: HOUR=0xa DAYS=0x15 MNTH=0xb YEAR=0x7e2
kfdhdb.crestmp.lo:           2375520256 ; 0x0ac: USEC=0x0 MSEC=0x1e4 SECS=0x19 MINS=0x23
kfdhdb.mntstmp.hi:             33074858 ; 0x0b0: HOUR=0xa DAYS=0x15 MNTH=0xb YEAR=0x7e2
kfdhdb.mntstmp.lo:           2375522304 ; 0x0b4: USEC=0x0 MSEC=0x1e6 SECS=0x19 MINS=0x23
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                  512000 ; 0x0c4: 0x0007d000
kfdhdb.pmcnt:                         6 ; 0x0c8: 0x00000006
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             33072461 ; 0x0e4: HOUR=0xd DAYS=0xa MNTH=0x9 YEAR=0x7e2
kfdhdb.grpstmp.lo:           3452534784 ; 0x0e8: USEC=0x0 MSEC=0x260 SECS=0x1c MINS=0x33

磁盘组mount成功,数据库open成功,实现数据0丢失

使用rman对数据库进行备份,并且重建磁盘组实现数据0丢失

ORA-600 kcvent_internal_02故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 kcvent_internal_02故障处理

数据库启动报ORA-00600: internal error code, arguments: [kcvent_internal_02]错,无法正常open

Reconfiguration complete
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 22775 KB redo, 5055 data blocks need recovery
Started redo application at
 Thread 2: logseq 166395, block 88
Recovery of Online Redo Log: Thread 2 Group 3 Seq 166395 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_3.283.1036687245
  Mem# 1: +FLASH/orcl/onlinelog/group_3.264.1036687257
Recovery of Online Redo Log: Thread 2 Group 4 Seq 166396 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_4.284.1036687257
  Mem# 1: +FLASH/orcl/onlinelog/group_4.265.1036687257
Completed redo application of 15.97MB
Completed instance recovery at
 Thread 2: logseq 166396, block 15854, scn 27533037896
 5055 data blocks read, 5055 data blocks written, 22775 redo k-bytes read
Thread 2 advanced to log sequence 166397 (thread recovery)
Redo thread 2 internally disabled at seq 166397 
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_35652472.trc  (incident=195549):
ORA-00600: internal error code, arguments: [kcvent_internal_02], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_195549/orcl1_ora_35652472_i195549.trc

对应的trace文件信息

Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_35652472.trc
ORA-00600: internal error code, arguments: [kcvent_internal_02], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 195549 (ORA 600 [kcvent_internal_02]) ========

*** 2022-06-06 22:17:48.743
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5fmpzya54p4hf) -----
ALTER DATABASE OPEN /* db agent *//* {1:38339:2} */

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
skdstdst()+40        bl       0000000109B1E77C     000000000 ? 000000001 ?
                                                   000000003 ? 000000000 ?
                                                   000000000 ? 000000001 ?
                                                   000000003 ? 000000000 ?
ksedst1()+112        call     skdstdst()           16F60DC8B26FAB02 ?
                                                   4846284100000000 ?
                                                   FFFFFFFFFFE46D0 ?
                                                   283C6E7C6A9A6 ? 10A6B923C ?
                                                   000000000 ? 110737880 ?
                                                   2050033FFFE46D8 ?
ksedst()+40          call     ksedst1()            000000000 ? 00000000A ?
                                                   07FFFFFFF ? 700000000003670 ?
                                                   000000000 ? 000000000 ?
                                                   000002004 ? 000000001 ?
dbkedDefDump()+1516  call     ksedst()             000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 300000003 ?
ksedmp()+72          call     dbkedDefDump()       310737880 ? 110000D40 ?
                                                   FFFFFFFFFFE4EE0 ? 1106AB740 ?
                                                   100124BB8 ? 000000000 ?
                                                   700011D7387FF08 ? 1106AB740 ?
ksfdmp()+100         call     ksedmp()             000000002 ? 000000000 ?
                                                   000000002 ? 10AF01CA8 ?
                                                   10A041C38 ? 000000000 ?
                                                   11073C760 ? 110737880 ?
dbgexPhaseII()+1904  call     ksfdmp()             000000000 ? 00000000A ?
                                                   000000002 ? 000000000 ?
                                                   000000002 ? 10A041C30 ?
                                                   000000000 ? 001050005 ?
dbgexProcessError()  call     dbgexPhaseII()       110737880 ? 11073A970 ?
+1556                                              00002FBDD ? 200000000 ?
                                                   FFFFFFFFFFE5DF8 ? 00000006C ?
                                                   200000000 ? 1000000000 ?
dbgeExecuteForError  call     dbgexProcessError()  110737880 ? 11073C760 ?
()+72                                              100000703 ? 000004000 ?
                                                   000000000 ? FFFFFFFFFFE9608 ?
                                                   000000001 ? 11073E4A8 ?
dbgePostErrorKGE()+  call     dbgeExecuteForError  FFFFFFFFFFE92B0 ?
2044                          ()                   700011D61558BB8 ? 102878B5C ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFE9608 ? 000000000 ?
                                                   000000000 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   07FFFFFFF ? 700000000003670 ?
68                                                 25800000001 ? 109E4A618 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFEA0B0 ? 1109C0040 ?
kgeadse()+380        call     dbkePostKGE_kgsf()   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 069186EAB ?
kgerinv_internal()+  call     kgeadse()            000000002 ? 000000002 ?
48                                                 000000001 ? FFFFFFFFFFEAB58 ?
                                                   10A4E02F0 ? 000000002 ?
                                                   FFFFFFFFFFE9FE0 ? 000000000 ?
kgerinv()+48         call     kgerinv_internal()   200000002 ? 000000002 ?
                                                   FFFFFFFFFFEA060 ? 000000000 ?
                                                   102860EB0 ? FFFFFFFFFFEA458 ?
                                                   10285CE74 ? FFFFFFFFFFEA358 ?
kgeasnmierr()+72     call     kgerinv()            38400000001 ? 000000000 ?
                                                   10A4E0D20 ? 497F0A29CAE0 ?
                                                   000000001 ? FFFFFFFFFFEA1C0 ?
                                                   10A4E0D20 ? 110000D78 ?
kcvent_internal()+1  call     kgeasnmierr()        FFFFFFFFFFEA1C0 ? 200000002 ?
532                                                1F0410001F041 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000004 ?
kctenb_internal()+2  call     kcvent_internal()    FFFFFFFFFFEB378 ? 200000002 ?
772                                                FFFFFFFFFFEB448 ?
                                                   FFFFFFFFFFEB2E8 ?
                                                   41F6C57900000000 ?
                                                   000000000 ? FFFFFFFFFFEB330 ?
                                                   1106AB740 ?
kcfopd()+1508        call     kctenb_internal()    07FFFFFFF ? 000000000 ?
                                                   000000018 ? FFFFFFFFFFEC380 ?
                                                   000000000 ? 110A39050 ?
                                                   FFFFFFFFFFEC390 ? 000000000 ?
adbdrv()+8028        call     kcfopd()             081F0AD00 ? 00000000F ?
                                                   0FFFED4C0 ? 000000000 ?
                                                   FFFFFFFFFFED548 ? 100000000 ?
                                                   000000000 ? 1000100000000 ?
opiexe()+16048       call     adbdrv()             2300000023 ? 100000001 ?
                                                   000000000 ? FFFFFFFFFFF6960 ?
                                                   000000000 ? FFFFFFFFFFF6B60 ?
                                                   FFFFFFFFFFF6A98 ? 200000002 ?
opiosq0()+3984       call     opiexe()             700011E117B3B20 ? 000000000 ?
                                                   FFFFFFFFFFF7ED8 ? 110000D78 ?
                                                   000000001 ? 1109FA438 ?
                                                   FFFFFFFFFFF7E70 ?
                                                   2216414400000001 ?
kpooprx()+316        call     opiosq0()            300000000 ? 000000000 ?
                                                   000000000 ? A4000000000000 ?
                                                   000000000 ? FFFFFFFFFFF87F0 ?
                                                   28104221FFFF86F0 ?
                                                   1109FAB08 ?
kpoal8()+872         call     kpooprx()            1000CE68C ? 000000001 ?
                                                   FFFFFFFFFFFAD14 ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   109EB6D00 ? 000000000 ?
opiodr()+908         call     kpoal8()             100000000 ? 9001000A0091108 ?
                                                   000000FFF ? 07FFFFFF8 ?
                                                   FFFFFFFFFFF8F10 ? 000000018 ?
                                                   000000000 ? 000072FFF ?
ttcpip()+1028        call     opiodr()             5EFFFFA480 ? 1C00200048 ?
                                                   FFFFFFFFFFFA9F8 ? 000530058 ?
                                                   1108BEE30 ? 000000028 ?
                                                   FFFFFFFFFFFA3A0 ? 1108BEC70 ?
opitsk()+1612        call     ttcpip()             110135440 ? 000002078 ?
                                                   000000000 ? 110000D78 ?
                                                   110005210 ? 000000000 ?
                                                   FFFFFFFFFFFAA20 ?
                                                   2222208009EF13C0 ?
opiino()+940         call     opitsk()             110024C58 ? 000000000 ?
                                                   11079B550 ? 1107A0850 ?
                                                   110737880 ? FFFFFFFFFFFCAE0 ?
                                                   FFFFFFFFFFFEB3C ? 000000101 ?
opiodr()+908         call     opiino()             3C006C787C ?
                                                   BFF0000000000000 ?
                                                   FFFFFFFFFFFEF60 ?
                                                   FFFFFFFFFFFD5E9 ?
                                                   FFFFFFFFFFFD630 ? 1106AB740 ?
                                                   FFFFFFFFFFFD650 ?
                                                   9FFFFFFF000E608 ?
opidrv()+1132        call     opiodr()             3C0AFBC600 ? 410134340 ?
                                                   FFFFFFFFFFFEF60 ? 07530312F ?
                                                   108820CE4 ? 1106AB740 ?
                                                   7264626D732F6F72 ?
                                                   1106AB740 ?
sou2o()+136          call     opidrv()             3C0882A9D0 ? 41170031F ?
                                                   FFFFFFFFFFFEF60 ?
                                                   110017002A0000 ? 0E0DDF00D ?
                                                   1106AB740 ?
                                                   BADC0FFEE0DDF00D ?
                                                   BADC0FFEE0DDF00D ?
opimai_real()+560    call     sou2o()              FFFFFFFFFFFEFD0 ?
                                                   BADC0FFEE0DDF00D ?
                                                   90000000008BE3C ?
                                                   BADC0FFEE0DDF00D ?
                                                   000000002 ? 9001000A0091108 ?
                                                   A0000000A000000 ? 10B671248 ?
ssthrdmain()+276     call     opimai_real()        10B6B1D74 ? 9001000A0095260 ?
                                                   FFFFFFFFFFFF0B0 ? 10B6B1598 ?
                                                   FFFFFFFFFFFF0D0 ?
                                                   FFFFFFFFFFFF428 ?
                                                   900000000100968 ?
                                                   9001000A0091108 ?
main()+204           call     ssthrdmain()         240000000 ? FFFFFFFFFFFF418 ?
                                                   8FFFFFFF0000090 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   BADC0FFEE0DDF00D ?
                                                   BADC0FFEE0DDF00D ?
__start()+112        call     main()               000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
 

--------------------- Binary Stack Dump ---------------------

该错误在mos,互联网上没有任何信息,不过在alert日志中发现类似信息

Mon Jun 06 23:03:58 2022
Error: Controlfile sequence number in file header is different from the one in memory
       Please check that the correct mount options are used if controlfile is located on NFS

初步判断可能和这个错误有关系,解决相关问题后,尝试open库

SQL> recover database;

ORA-00279: change 27533037896 generated at 06/06/2022 22:17:46 needed for
thread 2
ORA-00289: suggestion :
+FLASH/orcl/archivelog/2022_06_06/thread_2_seq_166396.6532.1106691471
ORA-00280: change 27533037896 for thread 2 is in sequence #166396


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01216: thread 2 is expected to be disabled after CREATE CONTROLFILE

SQL> !oerr ora 01216
01216, 00000, "thread %s is expected to be disabled after CREATE CONTROLFILE"
// *Cause:  A thread that was given during CREATE CONTROLFILE is enabled, but
//          the datafiles indicate that it should be disabled.  This is
//          probably because the logs supplied to the CREATE CONTROLFILE
//          are old (from before the disabling of the thread).
// *Action: This thread is not required to run the database.  The CREATE
//          CONTROLFILE statement can be reissued without the problem thread,
//          and, if desired, the thread can be recreated after the database
//          is open.

ORA-01216这个错误比较也比较少见,但是感觉和thread有关系,大概的意思是thread 被disable了

SQL> select thread#,STATUS FROM V$THREAD;

   THREAD# STATUS
---------- ------------------
         1 CLOSED
         2 CLOSED

通过人工强制把thread个open,然后数据库启动成功

SQL> select thread#,status from v$thread;

   THREAD# STATUS
---------- ------------------
         1 OPEN
         2 CLOSED

SQL> alter database open;

Database altered.

然后启动thread 2,open 第二个节点

--需要open节点
QL> startup
ORACLE instance started.

Total System Global Area 1.2961E+11 bytes
Fixed Size                  2262400 bytes
Variable Size            3.3018E+10 bytes
Database Buffers         9.6368E+10 bytes
Redo Buffers              221818880 bytes
ORA-01618: redo thread 2 is not enabled - cannot mount


--已经open节点
SQL> ALTER DATABASE ENABLE THREAD 2;

Database altered.

--需要open节点
SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

xifenfei1:/home/grid$crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
ora.FLASH.dg
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
ora.OCR.dg
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
ora.asm
               ONLINE  ONLINE       xifenfei1                  Started             
               ONLINE  ONLINE       xifenfei2                  Started             
ora.gsd
               OFFLINE OFFLINE      xifenfei1                                      
               OFFLINE OFFLINE      xifenfei2                                      
ora.net1.network
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
ora.ons
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
ora.registry.acfs
               ONLINE  ONLINE       xifenfei1                                      
               ONLINE  ONLINE       xifenfei2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xifenfei1                                      
ora.cvu
      1        OFFLINE OFFLINE                                                   
ora.xifenfei1.vip
      1        ONLINE  ONLINE       xifenfei1                                      
ora.xifenfei2.vip
      1        ONLINE  ONLINE       xifenfei2                                      
ora.oc4j
      1        ONLINE  ONLINE       xifenfei2                                      
ora.orcl.db
      1        ONLINE  ONLINE       xifenfei1                  Open                
      2        ONLINE  ONLINE       xifenfei2                  Open                
ora.scan1.vip
      1        ONLINE  ONLINE       xifenfei1

PostgreSQL恢复系列:pg_filedump基本使用

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL恢复系列:pg_filedump基本使用

当PostgreSQL遇到重大故障,使用各种方法都无法直接启动数据库，可以考虑使用类似oracle dul工具,直接离线方式读取文件进行恢复.这个工具为pg_filedump
pg_filedump安装

[root@xifenfei ~]# yum install pg_filedump_14.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pg_filedump_14.x86_64 0:14.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================
 Package                            Arch      Version                 Repository                                 Size
======================================================================================================================
Installing:                                 
 pg_filedump_14                     x86_64    14.1-1.rhel7            pgdg14                                     43 k
                                            
Transaction Summary                         
======================================================================================================================
Install  1 Package

Total download size: 43 k
Installed size: 81 k
Is this ok [y/d/N]: y
Downloading packages:
pg_filedump_14-14.1-1.rhel7.x86_64.rpm                                              |  43 kB  00:00:02     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_filedump_14-14.1-1.rhel7.x86_64                                                      1/1 
  Verifying  : pg_filedump_14-14.1-1.rhel7.x86_64                                                      1/1 

Installed:
  pg_filedump_14.x86_64 0:14.1-1.rhel7                                                                     

Complete!
-bash-4.2$ pg_filedump 

Version 14.1 (for PostgreSQL 8.x .. 14.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2022, PostgreSQL Global Development Group

Usage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file

Display formatted contents of a PostgreSQL heap/index/control file
Defaults are: relative addressing, range of the entire file, block
               size as listed on block 0 in the file

The following options are valid for heap and index files:
  -a  Display absolute addresses when formatting (Block header
      information is always block relative)
  -b  Display binary block images within a range (Option will turn
      off all formatting options)
  -d  Display formatted block content dump (Option will turn off
      all other formatting options)
  -D  Decode tuples using given comma separated list of types
      Supported types:
        bigint bigserial bool char charN date float float4 float8 int
        json macaddr name numeric oid real serial smallint smallserial text
        time timestamp timestamptz timetz uuid varchar varcharN xid xml
      ~ ignores all attributes left in a tuple
  -f  Display formatted block content dump along with interpretation
  -h  Display this information
  -i  Display interpreted item details
  -k  Verify block checksums
  -o  Do not dump old values.
  -R  Display specific block ranges within the file (Blocks are
      indexed from 0)
        [startblock]: block to start at
        [endblock]: block to end at
      A startblock without an endblock will format the single block
  -s  Force segment size to [segsize]
  -t  Dump TOAST files
  -v  Ouput additional information about TOAST relations
  -n  Force segment number to [segnumber]
  -S  Force block size to [blocksize]
  -x  Force interpreted formatting of block items as index items
  -y  Force interpreted formatting of block items as heap items

The following options are valid for control files:
  -c  Interpret the file listed as a control file
  -f  Display formatted content dump along with interpretation
  -S  Force block size to [blocksize]
Additional functions:
  -m  Interpret file as pg_filenode.map file and print contents (all
      other options will be ignored)

Report bugs to <pgsql-bugs@postgresql.org>

创建测试表

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# create table pg_xifenfei(id int,name varchar(100));
CREATE TABLE
postgres=# insert into pg_xifenfei values(1,'www.xifenfei.com');
INSERT 0 1
postgres=# insert into pg_xifenfei values(2,'xienfei_pg_recovery');
INSERT 0 1
postgres=# select * from pg_xifenfei;
 id |        name         
----+---------------------
  1 | www.xifenfei.com
  2 | xienfei_pg_recovery
(2 rows)

postgres=#

pg_filedump恢复数据

-bash-4.2$ pg_filedump /var/lib/pgsql/14/data/base/14487/16384

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /var/lib/pgsql/14/data/base/14487/16384
* Options used: None
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid      0 recoff 0x16299cf0      Special  8192 (0x2000)
 Items:    2                      Free Space: 8064
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

<Data> -----
 Item   1 -- Length:   45  Offset: 8144 (0x1fd0)  Flags: NORMAL
 Item   2 -- Length:   48  Offset: 8096 (0x1fa0)  Flags: NORMAL


*** End of File Encountered. Last Block Read: 0 ***

-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
COPY: 1 www.xifenfei.com
COPY: 2 xienfei_pg_recovery
-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
> |awk '{$1=null;print $0}'>/tmp/pg_xifenfei_rec
-bash-4.2$ sed -i 's/^[ ]*//g' /tmp/pg_xifenfei_rec

导入数据验证

postgres=# truncate table pg_xifenfei;
TRUNCATE TABLE
postgres=# select * from pg_xifenfei;
 id | name 
----+------
(0 rows)
postgres=# copy pg_xifenfei from '/tmp/pg_xifenfei_rec'(DELIMITER ' ');
COPY 2
postgres=# select * from pg_xifenfei;
 id |        name         
----+---------------------
  1 | www.xifenfei.com
  2 | xienfei_pg_recovery
(2 rows)

通过上述简单测试证明,在PG数据库出现极端情况下，可以使用该方法进行最后的数据恢复,减少因为数据丢失带来的损失.

Oracle断电故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle断电故障处理

异常断电导致数据库异常恢复文件报ORA-00283 ORA-00742 ORA-00312

 
D:\check_db>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 31 00:38:42 2022

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover datafile 1;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 3 线程 1:
'D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG'

这个错误比较明显是由于异常断电引起的写丢失导致.而且这种故障在没有备份的情况下，没有什么好处理方法,只能屏蔽一致性强制拉库,尝试强制拉库报错如下

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 2.0310E+10 bytes
Fixed Size                  2290000 bytes
Variable Size            3690991280 bytes
Database Buffers         1.6576E+10 bytes
Redo Buffers               40837120 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 18755939194213 (在  生成) 对于线程 1 是必需的


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG
ORA-00600: internal error code, arguments: [3020], [2], [78824], [8467432], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 78824, file
offset is 645726208 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 80834


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [krsi_al_hdr_update.15], [4294967295], [], [],[], [], [], [], [], [], [], []

ORA-600 krsi_al_hdr_update.15错误,主要是由于redo异常导致无法resetlogs成功,具体参考：Alter Database Open Resetlogs returns error ORA-00600: [krsi_al_hdr_update.15], (Doc ID 2026541.1)描述,处理这个问题之后,再次resetlogs库，报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [4366], [4112122046],
[4366], [4112228996], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [4366], [4112122045],
[4366], [4112228996], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [4366], [4112122040],
[4366], [4112228996], [12583040], [], [], [], [], [], []
进程 ID: 4644
会话 ID: 1701 序列号: 3

这个问题比较简单,通过修改scn即可绕过去,之后数据库open报ORA-600 4194等错误

SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [4194], [

SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc  (incident=322982):
ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322982\orcl_smon_5112_i322982.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3340.trc  (incident=323030):
ORA-00600: 内部错误代码, 参数: [4194], [
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_323030\orcl_ora_3340_i323030.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 31 09:05:04 2022
Sweep [inc][322982]: completed
ORACLE Instance orcl (pid = 13) - Error 600 encountered while recovering transaction (10, 33).
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc:
ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], []
Checker run found 1 new persistent data failures
Tue May 31 09:05:05 2022
Sweep [inc][323030]: completed
Sweep [inc2][322982]: completed
Tue May 31 09:05:14 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc  (incident=322983):
ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [0], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322983\orcl_smon_5112_i322983.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 31 09:05:14 2022
ORA-600 signalled during: alter database open...
Block recovery stopped at EOT rba 2.61.16
Block recovery completed at rba 2.61.16, scn 4366.4112429058
Block recovery from logseq 2, block 60 to scn 18755939643393
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_K9BSVC11_.LOG
Block recovery completed at rba 2.61.16, scn 4366.4112429058
Dumping diagnostic data in directory=[cdmp_2022053],requested by(instance=1,osid=5112(SMON)),summary=[incident=322983].
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc:
ORA-01595: error freeing extent (3) of rollback segment (1))
ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [3], [], [], [], [], [], [], [], [], []

对异常undo进行处理,数据库正常open成功

SQL> shutdown immediate;
ORA-00600: 内部错误代码, 参数: [4193], [


SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 2.0310E+10 bytes
Fixed Size                  2290000 bytes
Variable Size            3690991280 bytes
Database Buffers         1.6576E+10 bytes
Redo Buffers               40837120 bytes
数据库装载完毕。
SQL> alter database open;

数据库已更改。

hcheck检测有一些字典不一致,建议客户逻辑导出,然后导入到新库中

HCheck Version 07MAY18 on 31-5月 -2022 09:12:22
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: ORCL

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp      Resul
t
------------------------------ ... ---------- -- ---------- -------------- -----
-
.- LobNotInObj                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- MissingOIDOnObjCol          ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- SourceNotInObj              ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- OversizedFiles              ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- PoorDefaultStorage          ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- PoorStorage                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- TabPartCountMismatch        ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- OrphanedTabComPart          ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- MissingSum$                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- MissingDir$                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- DuplicateDataobj            ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- ObjSynMissing               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjSeqMissing               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedUndo                ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndex               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexPartition      ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexSubPartition   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedTable               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedTablePartition      ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedTableSubPartition   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- MissingPartCol              ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedSeg$                ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndPartObj#         ... 1102000400 <=  *All Rel* 05/31 09:12:23 FAIL

HCKE-0024: Orphaned Index Partition Obj# (no OBJ$) (Doc ID 1360935.1)
ORPHAN INDPART$: OBJ#=149167 BO#=6378 - no OBJ$ row
ORPHAN INDPART$: OBJ#=149168 BO#=6378 - no OBJ$ row

.- DuplicateBlockUse           ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- FetUet                      ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- Uet0Check                   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- SeglessUET                  ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadInd$                     ... 1102000400 <=  *All Rel* 05/31 09:12:23 FAIL

HCKE-0030: OBJ$ INDEX entry has no IND$ or INDPART$/INDSUBPART$ entry (Doc ID 13
60528.1)
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148278 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16462
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148920 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16678

.- BadTab$                     ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadIcolDepCnt               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjIndDobj                  ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- TrgAfterUpgrade             ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjType0                    ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadOwner                    ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- StmtAuditOnCommit           ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadPublicObjects            ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadSegFreelist              ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadDepends                  ... 1102000400 <=  *All Rel* 05/31 09:12:23 WARN

HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects (Doc ID 1361045.1)

[E] - P_OBJ#=6376 D_OBJ#=6765

.- CheckDual                   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjectNames                 ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadCboHiLo                  ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- ChkIotTs                    ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- NoSegmentIndex              ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- BadNextObject               ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- DroppedROTS                 ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- FilBlkZero                  ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- DbmsSchemaCopy              ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- OrphanedObjError            ... 1102000400 >  1102000000 05/31 09:12:24 PASS
.- ObjNotLob                   ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- MaxControlfSeq              ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- SegNotInDeferredStg         ... 1102000400 >  1102000000 05/31 09:12:24 PASS
.- SystemNotRfile1             ... 1102000400 >   902000000 05/31 09:12:24 PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- OrphanTrigger               ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- ObjNotTrigger               ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
---------------------------------------
31-5月 -2022 09:12:24  Elapsed: 2 secs
---------------------------------------
Found 4 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL 过程已成功完成。

PostgreSQL恢复系列:pg_control异常恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL恢复系列:pg_control异常恢复

在PG中pg_control文件类似oracle数据库的control文件(控制文件),在Oracle中如果该文件丢失/损坏,可以通过alter database create controlfile命令进行创建,对于PG数据库来说也可以通过pg_resetwal命令来实现创建,由于pg_control文件损坏,需要人工指定一些参数完成pg_resetwal相关操作
pg_resetwal 使用说明

-bash-4.2$ pg_resetwal --help
pg_resetwal resets the PostgreSQL write-ahead log.

Usage:
  pg_resetwal [OPTION]... DATADIR

Options:
  -c, --commit-timestamp-ids=XID,XID
                                   set oldest and newest transactions bearing
                                   commit timestamp (zero means no change)
 [-D, --pgdata=]DATADIR            data directory
  -e, --epoch=XIDEPOCH             set next transaction ID epoch
  -f, --force                      force update to be done
  -l, --next-wal-file=WALFILE      set minimum starting location for new WAL
  -m, --multixact-ids=MXID,MXID    set next and oldest multitransaction ID
  -n, --dry-run                    no update, just show what would be done
  -o, --next-oid=OID               set next OID
  -O, --multixact-offset=OFFSET    set next multitransaction offset
  -u, --oldest-transaction-id=XID  set oldest transaction ID
  -V, --version                    output version information, then exit
  -x, --next-transaction-id=XID    set next transaction ID
      --wal-segsize=SIZE           size of WAL segments, in megabytes
  -?, --help                       show this help, then exit

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

确认现在业务表记录情况

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# select count(1) from ac_event;
 count  
--------
 246266
(1 row)

模拟pg_control文件异常

-bash-4.2$ ps -ef|grep postgres
postgres  37178      1  0 09:58 ?        00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres  37179  37178  0 09:58 ?        00:00:00 postgres: logger 
postgres  37181  37178  0 09:58 ?        00:00:00 postgres: checkpointer 
postgres  37182  37178  0 09:58 ?        00:00:00 postgres: background writer 
postgres  37183  37178  0 09:58 ?        00:00:00 postgres: walwriter 
postgres  37184  37178  0 09:58 ?        00:00:00 postgres: autovacuum launcher 
postgres  37185  37178  0 09:58 ?        00:00:00 postgres: stats collector 
postgres  37186  37178  0 09:58 ?        00:00:00 postgres: logical replication launcher 
root      41368  41314  0 11:06 pts/1    00:00:00 su - postgres
postgres  41369  41368  0 11:06 pts/1    00:00:00 -bash
postgres  45071  41369  0 12:07 pts/1    00:00:00 ps -ef
postgres  45072  41369  0 12:07 pts/1    00:00:00 grep --color=auto postgres
-bash-4.2$ kill -9 37178
-bash-4.2$ ps -ef|grep postgres
root      41368  41314  0 11:06 pts/1    00:00:00 su - postgres
postgres  41369  41368  0 11:06 pts/1    00:00:00 -bash
postgres  45095  41369  0 12:08 pts/1    00:00:00 ps -ef
postgres  45096  41369  0 12:08 pts/1    00:00:00 grep --color=auto postgres
-bash-4.2$ pwd
/var/lib/pgsql/14/data/global
-bash-4.2$ ls -l pg_control 
-rw-------. 1 postgres postgres 8192 May 30 12:04 pg_control
-bash-4.2$ rm -rf pg_control 
-bash-4.2$ ls -l pg_control 
ls: cannot access pg_control: No such file or directory

PG启动失败

-bash-4.2$ pg_ctl start 
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....postgres: could not find the database system
Expected to find it in the directory "/var/lib/pgsql/14/data",
but could not open file "/var/lib/pgsql/14/data/global/pg_control": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

创建空pg_control文件启动依旧失败

-bash-4.2$ touch /var/lib/pgsql/14/data/global/pg_control
-bash-4.2$ pg_ctl start 
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-05-30 12:09:43.953 CST [45215] PANIC:  
   could not read file "global/pg_control": read 0 of 296
 stopped waiting
pg_ctl: could not start server
Examine the log output.

设置next-wal-file
-l, –next-wal-file=WALFILE,这个参数设置下一个新的WAL文件的最小值，这个值可以从$PGDATA/pg_wal目录下去看最后一个WAL 文件，这个文件的id+1即可

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -l
total 16384
-rw-------. 1 postgres postgres 16777216 May 30 12:04 000000010000000000000014
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-bash-4.2$

这个文件+1，-l 000000010000000000000015
设置next-transaction
-x, –next-transaction-id=XID,这个参数设置pg_control中的下一个XID的值，这个值可以从pg_xact目录下的文件中查询

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_xact
-bash-4.2$ ls -ltr
total 8
-rw-------. 1 postgres postgres 8192 May 30 12:03 0000

最后一个是0000，那么下一个XID就是0001，然后乘以 1048576 (0×100000)，实际上后面直接加5个0就行了。注意，这个值是16进制的。-x 0×000100000
multixact-ids设置
-m, –multixact-ids=MXID1,MXID2，这个参数包含两个部分，MXID1和MXID2，都可以从$PGDATA/pg_multixact/offsets目录下获得。MXID1的值，首先找到最大值，+1，再乘以 65536 (0×10000，相当于后面加4个0)作为这个参数的前半部分。找到最小的值，后面加4个0，作为MXID2的值

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_multixact/offsets
-bash-4.2$ ls -ltr
total 8
-rw-------. 1 postgres postgres 8192 May 29 22:06 0000
-bash-4.2$

-m 0×00010000, 0×00000000（由于oldest multitransaction ID不能为0,因此后续这个值需要适当调整）
multixact-offset设置
-O, –multixact-offset=OFFSET,这个参数可以从$PGDATA/pg_multixact/members目录下获得。找到最大值，+1，乘以 52352 (0xCC80)

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_multixact/members
-bash-4.2$ ls -ltr
total 8
-rw-------. 1 postgres postgres 8192 May 24 02:20 0000

-O 0xCC80
尝试执行pg_resetwal

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 $PGDATA
pg_resetwal: error: oldest multitransaction ID (-m) must not be 0

multixact-ids值不对,进行调整后处理
postmaster.pid文件需要清理
由于PG库异常关闭,需要人工清理掉该文件

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 $PGDATA
pg_resetwal: error: lock file "postmaster.pid" exists
-bash-4.2$ rm -rf postmaster.pid

pg_resetwal结果预览

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Guessed pg_control values:

pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7103392535324046312
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID:          0:3
Latest checkpoint's NextOID:          12000
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        3
Latest checkpoint's oldestXID's DB:   0
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 0
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0


Values to be changed:

First log segment after reset:        000000010000000000000015
NextMultiXactId:                      131072
OldestMultiXid:                       65536
OldestMulti's DB:                     0
NextMultiOffset:                      52352
NextXID:                              1048576
OldestXID:                            3
OldestXID's DB:                       0

If these values seem acceptable, use -f to force reset.

pg_resetwal进行创建pg_control并启动PG

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 -f $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Write-ahead log reset
-bash-4.2$ pg_ctl start
waiting for server to start....2022-05-30 13:33:28.266 CST [51437] LOG:  
redirecting log output to logging collector process
2022-05-30 13:33:28.266 CST [51437] HINT:  Future log output will appear in directory "log".
 done
server started

验证数据

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=#  select count(1) from ac_event;
 count  
--------
 245275
(1 row)

这种方法恢复之后,建议理解dump数据,然后导入到新库中

PostgreSQL恢复系列:wal日志丢失恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL恢复系列:wal日志丢失恢复

WAL是Write Ahead Log的简写，和oracle的redo日志类似，存放在$PGDATA/pg_xlog中，10版本以后在$PGDATA/pg_wal目录.在oracle数据库中,如果redo丢失,分为active/current和inactive的redo,分别有不同的处理方式,对于oracle需要实例恢复的redo丢失，需要屏蔽数据库一致性，强制打开数据库,对于PG数据库这部分日志丢失该如何恢复,主要是通过pg_resetwal/pg_resetxlog（10以前版本）命令来实现，这里通过一个测试来验证
创建测试表并强制kill数据库

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# create table t_xifenfei as select * from pg_database;
SELECT 4
postgres=# select count(1) from t_xifenfei;
 count 
-------
     4
(1 row)

postgres=# \q
-bash-4.2$ ps -ef|grep post
root       1819      1  0 May28 ?        00:00:00 /usr/libexec/postfix/master -w
postfix    1838   1819  0 May28 ?        00:00:00 qmgr -l -t unix -u
postgres  11102      1  0 05:49 ?        00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres  11103  11102  0 05:49 ?        00:00:00 postgres: logger 
postgres  11105  11102  0 05:49 ?        00:00:00 postgres: checkpointer 
postgres  11106  11102  0 05:49 ?        00:00:00 postgres: background writer 
postgres  11107  11102  0 05:49 ?        00:00:00 postgres: walwriter 
postgres  11108  11102  0 05:49 ?        00:00:00 postgres: autovacuum launcher 
postgres  11109  11102  0 05:49 ?        00:00:01 postgres: stats collector 
postgres  11110  11102  0 05:49 ?        00:00:00 postgres: logical replication launcher 
root      22743  22300  0 18:26 pts/3    00:00:00 su - postgres
postgres  22744  22743  0 18:26 pts/3    00:00:00 -bash
postgres  22937  22744  0 18:28 pts/3    00:00:00 psql
postgres  22938  11102  0 18:28 ?        00:00:00 postgres: postgres postgres [local] idle
postfix   32623   1819  0 21:10 ?        00:00:00 pickup -l -t unix -u
root      33032  32912  0 21:15 pts/2    00:00:00 su - postgres
postgres  33033  33032  0 21:15 pts/2    00:00:00 -bash
postgres  35210  33033  0 21:51 pts/2    00:00:00 ps -ef
postgres  35211  33033  0 21:51 pts/2    00:00:00 grep --color=auto post
-bash-4.2$ kill -9 11102

删除wal日志

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -ltr
total 311296
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000014
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000015
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000016
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000017
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000018
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000019
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001A
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001B
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001C
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001D
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001E
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001F
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000020
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000021
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000022
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000023
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000024
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000025
-rw-------. 1 postgres postgres 16777216 May 29 21:51 000000010000000000000013
-bash-4.2$ rm -rf 0000000100000000000000*
-bash-4.2$ ls
archive_status

查询当时数据库需要的最小wal记录

-bash-4.2$ pg_controldata 
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7100998319216817119
Database cluster state:               in production
pg_control last modified:             Sat 28 May 2022 09:36:11 PM CST
Latest checkpoint location:           0/13692F80
Latest checkpoint's REDO location:    0/13692F48
Latest checkpoint's REDO WAL file:    000000010000000000000013   <===需要的记录
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:17824
Latest checkpoint's NextOID:          32769
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        727
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  17824
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 28 May 2022 09:31:41 PM CST

尝试启动PG

-bash-4.2$ pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-05-29 21:52:22.926 CST [35270] LOG:  
redirecting log output to logging collector process
2022-05-29 21:52:22.926 CST [35270] HINT:  Future log output will appear in directory "log".
. stopped waiting
pg_ctl: could not start server
Examine the log output.

启动pg失败,查看日志记录

2022-05-29 21:52:22.926 CST [35270] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, 
            compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-29 21:52:22.927 CST [35270] LOG:  listening on IPv6 address "::1", port 5432
2022-05-29 21:52:22.927 CST [35270] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-29 21:52:22.929 CST [35270] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-29 21:52:22.931 CST [35270] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-29 21:52:22.936 CST [35272] LOG:  database system was interrupted; last known up at 2022-05-28 21:36:11 CST
2022-05-29 21:52:23.049 CST [35272] LOG:  invalid primary checkpoint record
2022-05-29 21:52:23.049 CST [35272] PANIC:  could not locate a valid checkpoint record
2022-05-29 21:52:24.211 CST [35270] LOG:  startup process (PID 35272) was terminated by signal 6: Aborted
2022-05-29 21:52:24.211 CST [35270] LOG:  aborting startup due to startup process failure
2022-05-29 21:52:24.218 CST [35270] LOG:  database system is shut down

错误比较明显,无法定位到有效的checkpoint记录,在oracle里面的意思可以理解为无法进行实例恢复,pg启动失败

重设wal
由于数据库为不一致状态,需要使用-f进行强制重设

-bash-4.2$ pg_resetwal $PGDATA
The database server was not shut down cleanly.
Resetting the write-ahead log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.
-bash-4.2$ pg_resetwal -f $PGDATA
Write-ahead log reset

启动PG成功

-bash-4.2$ pg_ctl start -D $PGDATA
waiting for server to start....2022-05-29 22:01:02.647 CST [37178] LOG:  
redirecting log output to logging collector process
2022-05-29 22:01:02.647 CST [37178] HINT:  Future log output will appear in directory "log".
 done
server started

日志记录

2022-05-29 22:01:02.647 CST [37178] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, 
                    compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-29 22:01:02.648 CST [37178] LOG:  listening on IPv6 address "::1", port 5432
2022-05-29 22:01:02.648 CST [37178] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-29 22:01:02.649 CST [37178] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-29 22:01:02.651 CST [37178] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-29 22:01:02.653 CST [37180] LOG:  database system was shut down at 2022-05-29 22:00:47 CST
2022-05-29 22:01:02.661 CST [37178] LOG:  database system is ready to accept connections

查看wal日志,产生新记录

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -ltr
total 16384
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-rw-------. 1 postgres postgres 16777216 May 29 22:01 000000010000000000000014

验证刚刚创建测试表

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# select count(1) from t_xifenfei;
ERROR:  relation "t_xifenfei" does not exist
LINE 1: select count(1) from t_xifenfei;
                             ^

由于需要进行实例恢复的wal日志丢失导致这表记录也丢失.由此可见这类操作可能导致数据丢失风险,对于生产环境,需要慎重,

pvcreate asm disk导致asm磁盘组异常恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：pvcreate asm disk导致asm磁盘组异常恢复

一客户asm磁盘组异常，无法正常mount

SQL> alter diskgroup datadg mount 
2022-05-28T19:08:55.114960+08:00
NOTE: cache registered group DATADG 1/0x2B504997
NOTE: cache began mount (first) of group DATADG 1/0x2B504997
NOTE: Assigning number (1,3) to disk (/dev/oracleasm/disks/DATA05)
NOTE: Assigning number (1,2) to disk (/dev/oracleasm/disks/DATA03)
NOTE: Assigning number (1,1) to disk (/dev/oracleasm/disks/DATA02)
2022-05-28T19:08:55.150062+08:00
ERROR: no read quorum in group: required 1, found 0 disks
2022-05-28T19:08:55.150684+08:00
NOTE: cache dismounting (clean) group 1/0x2B504997 (DATADG)
NOTE: messaging CKPT to quiesce pins Unix process pid: 15103, image: oracle@XFF01 (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: LGWR not being messaged to dismount
NOTE: cache dismounted group 1/0x2B504997 (DATADG)
NOTE: cache ending mount (fail) of group DATADG number=1 incarn=0x2b504997
NOTE: cache deleting context for group DATADG 1/0x2b504997
2022-05-28T19:08:55.191073+08:00
GMON dismounting group 1 at 36 for pid 37, osid 15103
2022-05-28T19:08:55.191258+08:00
NOTE: Disk DATA02 in mode 0x8 marked for de-assignment
NOTE: Disk DATA03 in mode 0x8 marked for de-assignment
NOTE: Disk DATA05 in mode 0x8 marked for de-assignment
ERROR: diskgroup DATADG was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATADG" cannot be mounted
ORA-15040: diskgroup is incomplete

通过报错信息,初步判断是由于少了asm disk导致（依据:1. ORA-15040,2.asmlib中的DATA01丢失),初步判断由于某种原因导致asmlib的磁盘异常,从而使得asm磁盘组无法正常mount,通过对dd 到本地的asm磁盘进行分析

C:\Users\XFF>kfed read H:\TEMP\asmdd\sdb6-o.dd
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: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
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
0066E8200 00000000 00000000 00000000 00000000  [................]
        Repeat 31 times
0066E8400 4542414C 454E4F4C 00000001 00000000  [LABELONE........]
0066E8410 4E06D490 00000020 324D564C 31303020  [...N ...LVM2 001]
0066E8420 34535542 476A7667 42546C48 6D384675  [BUS4gvjGHlTBuF8m]
0066E8430 7A385273 4B495777 73336242 33637449  [sR8zwWIKBb3sItc3]
0066E8440 48001000 000001E8 00100000 00000000  [...H............]
0066E8450 00000000 00000000 00000000 00000000  [................]
0066E8460 00000000 00000000 00001000 00000000  [................]
0066E8470 000FF000 00000000 00000000 00000000  [................]
0066E8480 00000000 00000000 00000002 00000000  [................]
0066E8490 00000000 00000000 00000000 00000000  [................]
  Repeat 214 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

通过这部分信息可以确认，一个asm disk被创建了pv,进一步分析pv信息

对于这样的情况，表示asm disk被创建了pv但是pv没有加入到任何vg中,也就意味着该disk没有太大破坏,通过信息确认

主要是这两个部分信息被损坏,可以通过一些方法对这两个block信息进行重构

C:\Users\XFF>kfed read H:\TEMP\asmdd\sdb6.dd|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3196491921 ; 0x00c: 0xbe869891
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:   ORCLDISKDATA01 ; 0x000: length=14
kfdhdb.driver.reserved[0]:   1096040772 ; 0x008: 0x41544144
kfdhdb.driver.reserved[1]:        12592 ; 0x00c: 0x00003130
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                203424000 ; 0x020: 0x0c200100
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:                  DATA01 ; 0x028: length=6
kfdhdb.grpname:                  DATADG ; 0x048: length=6
kfdhdb.fgname:                   DATA01 ; 0x068: length=6
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             33083792 ; 0x0a8: HOUR=0x10 DAYS=0xc MNTH=0x4 YEAR=0x7e3
kfdhdb.crestmp.lo:           2268043264 ; 0x0ac: USEC=0x0 MSEC=0x3e6 SECS=0x32 MINS=0x21
kfdhdb.mntstmp.hi:             33134479 ; 0x0b0: HOUR=0xf DAYS=0x1c MNTH=0x5 YEAR=0x7e6
-- More  --

C:\Users\XFF>kfed read H:\TEMP\asmdd\sdb6.dd blkn=1|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            2 ; 0x002: KFBTYP_FREESPC
kfbh.datfmt:                          2 ; 0x003: 0x02
kfbh.block.blk:                       1 ; 0x004: blk=1
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  2177715180 ; 0x00c: 0x81cd4bec
kfbh.fcn.base:                  3721754 ; 0x010: 0x0038ca1a
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdfsb.aunum:                         0 ; 0x000: 0x00000000
kfdfsb.max:                        1014 ; 0x004: 0x03f6
kfdfsb.cnt:                        1014 ; 0x006: 0x03f6
kfdfsb.bound:                         0 ; 0x008: 0x0000
kfdfsb.flag:                          1 ; 0x00a: B=1
kfdfsb.ub1spare:                      0 ; 0x00b: 0x00
kfdfsb.spare[0]:                      0 ; 0x00c: 0x00000000
kfdfsb.spare[1]:                      0 ; 0x010: 0x00000000
kfdfsb.spare[2]:                      0 ; 0x014: 0x00000000
kfdfse[0].fse:                        0 ; 0x018: FREE=0x0 FRAG=0x0
kfdfse[1].fse:                        0 ; 0x019: FREE=0x0 FRAG=0x0
kfdfse[2].fse:                        0 ; 0x01a: FREE=0x0 FRAG=0x0
kfdfse[3].fse:                        0 ; 0x01b: FREE=0x0 FRAG=0x0
kfdfse[4].fse:                        0 ; 0x01c: FREE=0x0 FRAG=0x0
kfdfse[5].fse:                        0 ; 0x01d: FREE=0x0 FRAG=0x0
kfdfse[6].fse:                        0 ; 0x01e: FREE=0x0 FRAG=0x0
kfdfse[7].fse:                        0 ; 0x01f: FREE=0x0 FRAG=0x0
kfdfse[8].fse:                        0 ; 0x020: FREE=0x0 FRAG=0x0

通过dd写入到原磁盘,通过oracleasm scandisks扫描磁盘

磁盘组mount成功

数据库顺利open

这个案例能够完美恢复,主要是客户没有做进一步破坏，没有把这个pv加入到vg中并且写入数据,以前有过类似案例因为写入了数据,恢复比这个难多了,效果也没有这个好asm disk被加入vg恢复
如果不幸有类似oracle asm disk被破坏(格式化,dd部分,做成lv等),需要进行恢复支持,可以联系我们,做专业的恢复评估,最大限度,最快速度抢救数据,减少损失
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
恢复过部分asm异常案例:
删除分区 oracle asm disk 恢复
asm disk 磁盘部分被清空恢复
又一例asm格式化文件系统恢复
一次完美的asm disk被格式化ntfs恢复
oracle asm disk格式化恢复—格式化为ext4文件系统
oracle asm disk格式化恢复—格式化为ntfs文件系统
分享oracleasm createdisk重新创建asm disk后数据0丢失恢复案例

O/S-Error: (OS 23) 数据错误(循环冗余检查) 数据库恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：O/S-Error: (OS 23) 数据错误(循环冗余检查) 数据库恢复

有客户数据库运行过程中突然crash,检测发现ORA-27070 OSD-04016 O/S-Error: (OS 23) 等报错

Thu May 12 11:25:53 2022
KCF: write/open error block=0x19e95f online=1
     file=57 H:\ORADATA\xifenfei\XFF51.DBF
     error=27070 txt: 'OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。'
Thu May 12 11:25:53 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw0_3532.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
ORA-01114: 将块写入文件 57 时出现 IO 错误 (块 # 1698143)
ORA-01110: 数据文件 57: 'H:\ORADATA\xifenfei\XFF51.DBF'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。

DBW0: terminating instance due to error 1242
Thu May 12 11:25:54 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_mman_3528.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:25:54 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_lgwr_3544.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw1_3536.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_psp0_3524.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_ckpt_3548.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_pmon_3520.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:26:06 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_q002_37468.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:26:08 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_reco_3556.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:26:08 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_smon_3552.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式

Thu May 12 11:26:10 2022
Instance terminated by DBW0, pid = 3532

再次重启数据库报错 ORA-27070: 异步读取/写入失败 OSD-04016: 异步 I/O 请求排队时出错。类似错误

dbv检查数据文件报异常

通过以上信息基本上可以确认是由于底层故障(文件系统或者硬件故障),导致数据库文件访问异常,检查系统日志发现异常

通过专业恢复软件对异常文件进行恢复,实现数据库正常open(跳过坏块)

数据库open报ORA-00959: tablespace ‘UNDOTBS1′ does not exist分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：数据库open报ORA-00959: tablespace ‘UNDOTBS1′ does not exist分析

有一个朋友找到我,说数据库重启之后无法正常启动,提示ORA-00959 UNDOTBS1表空间不存在

在数据库的启动过程中,这个是一个很常见的错误,一般出现这类错误的原因是由于undo_tablespace指定的undo表空间不存在导致.但是这个库比较明显,设置了undo_management=manual, undo_tablespace=system,依旧数据库需要找undotbs1表空间,进一步分析数据库当前表空间情况

该数据库确实没有undotbs1表空间,基于以上信息,初步怀疑很可能是undo回滚段异常,通过对于oracle基表进行分析,发现信息

基于上面的信息,可以确认回滚段中确实有四条记录指向被删除的undotbs1,而且还有一条undo回滚段信息为need recovery状态.进一步分析数据库alert日志

--创建undotbs2表空间,并重启数据库
Sun Sep 26 14:42:16 2021
create undo tablespace UNDOTBS2 datafile '/data/oradata/xifenfei/undotbs001.dbf' size 120G
reuse autoextend on next 500m maxsize unlimited
Sun Sep 26 14:46:46 2021
Completed: create undo tablespace UNDOTBS2 datafile '/data/oradata/xifenfei/undotbs001.dbf' size 120G
reuse autoextend on next 500m maxsize unlimited
Sun Sep 26 14:47:13 2021
[16927] Successfully onlined Undo Tablespace 10.
[16927] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state.
[16927] active transactions found/affinity dissolution incompletein undo tablespace 2 during switch-out.
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
Sun Sep 26 14:49:25 2021
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC

---第一次尝试删除undotbs1失败
Sun Sep 26 15:06:30 2021
drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
Sun Sep 26 15:47:26 2021
ORA-1013 signalled during: drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
...
Sun Sep 26 15:48:40 2021
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC

--加上该隐含参数,继续重启库删除undotbs1,依旧删除失败
  _corrupted_rollback_segments= "_SYSSMU28_1306132068$"

Sun Sep 26 15:53:34 2021
QMNC started with pid=31, OS id=20454 
Completed: ALTER DATABASE OPEN
Sun Sep 26 15:55:32 2021
 drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
…………
Sun Sep 26 16:45:47 2021
ORA-1013 signalled during:  drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS...
Sun Sep 26 16:46:00 2021
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC

---加上以下参数，并尝试重启数据库删除undotbs1成功
  _corrupted_rollback_segments= "_SYSSMU31_201790566$"
  _corrupted_rollback_segments= "_SYSSMU30_2395098326$"
  _corrupted_rollback_segments= "_SYSSMU29_306369076$"
  _corrupted_rollback_segments= "_SYSSMU28_1306132068$"

Sun Sep 26 16:47:24 2021
QMNC started with pid=31, OS id=23421 
Completed: ALTER DATABASE OPEN
Sun Sep 26 16:47:40 2021
drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
…………
Mon Sep 27 01:44:56 2021
Deleted file /data/oradata/xifenfei/undotbs01.dbf
Deleted file /data/oradata/xifenfei/undotbs02.dbf
Deleted file /data/oradata/xifenfei/undotbs03.dbf
Deleted file /data/oradata/xifenfei/undotbs04.dbf
Deleted file /data/oradata/xifenfei/undotbs05.dbf
Deleted file /data/oradata/xifenfei/undotbs06.dbf
Completed: drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

从这里基本上可以看出来,操作之人非常草率,在oracle回滚段还在被占用状态,直接尝试删除老undo表空间,在无法删除之后,直接暴力的使用undo回滚段异常参数,然后进行undo表空间删除.这样操作的后果屏蔽了事务的一致性,导致后续可能导致数据库一系列异常.对当前库启动过程进行跟踪发现

数据库启动的过程中查询undo$中的undotbs1信息,而该表空间不存在,所以出现此类报错,从而导致数据库无法正常启动.知道问题原因所在,那通过undo$记录,数据库即可正常启动.

ASM删除表空间恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ASM删除表空间恢复

前几天刚刚恢复了一个文件系统层面drop 表空间的case(分享运气超级好的一次drop tablespace 数据恢复),又一客户删除表空间(认为是不要的表空间),结果发现业务上丢失了很多表数据,通过分析和回顾以往事件,确认由于在以前数据迁移过来的过程中,数据写入了和原库一致的表空间,而没有恢复到本该恢复的新表空间中,这次删除该空间导致很多表数据丢失.而且该客户是asm环境,drop tablespace带上了including contents and datafiles语句,导致该表空间对应的数据文件也丢失.对于这类数据的恢复,一般情况下先通过asm层面恢复出来被删除的数据文件,然后再对被删除的数据文件按照丢失system的方式恢复里面的表数据(这个客户有历史备份便于整合)
在恢复被删除的文件之前,需要先确认对应的被删除的表空间信息和对应的文件信息,通过对底层字典分析file$,ts$,结合alert日志,可以确认被删除文件的文件号,文件名称等信息

由于文件已经从asm磁盘组中删除,无法直接恢复,通过对asm磁盘组进行扫描找出对应的block信息,参考:asm磁盘组操作不当导致数据文件丢失恢复类似处理方法,分析文件是否异常

初步判断文件恢复效果应该不错,恢复出来数据文件，然后进行dbv检查

后续的操作比较简单,使用oracle dul恢复出来按照类似方法:dul恢复drop表测试数据即可,业务进行核对即可.如果你遭遇到此类情况,而且无有效备份,尽可能保护现场(不要对asm/文件系统系统进行写入操作)，然后联系我们进行处理,最大限度恢复数据