。。。
Mon Aug 01 20:05:03 2022
Hex dump of (file 11, block 612108) in trace file /home/oracle/app/diag/rdbms/xyoa/xyoa/trace/xyoa_ora_21215.trc
Corrupt block relative dba: 0x02c9570c (file 11, block 612108)
Completely zero block found during backing up datafile
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Mon Aug 01 20:05:45 2022
Checker run found 1 new persistent data failures
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7F0AC57328A8] [PC:0x7F0ACE564B23, _Z12ndmpdPropSetPvS_()+995] [flags: 0x0, count: 1]
Errors in file /home/oracle/app/diag/rdbms/xyoa/xyoa/trace/xyoa_ora_21215.trc (incident=76366):
ORA-07445: exception encountered: core dump [_Z12ndmpdPropSetPvS_()+995] [SIGSEGV] [ADDR:0x7F0AC57328A8] [PC:0x7F0ACE564B23] [Address not mapped to object] []
Incident details in: /home/oracle/app/diag/rdbms/xyoa/xyoa/incident/incdir_76366/xyoa_ora_21215_i76366.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Aug 01 20:06:03 2022
Dumping diagnostic data in directory=[cdmp_20220801200603], requested by (instance=1, osid=21215), summary=[incident=76366].
。。。
Mon Aug 01 22:13:25 2022
Sweep [inc2][76230]: completed
Hex dump of (file 11, block 612108) in trace file /home/oracle/app/diag/rdbms/xyoa/xyoa/incident/incdir_76230/xyoa_m000_480_i76230_a.trc
Corrupt block relative dba: 0x02c9570c (file 11, block 612108)
Completely zero block found during validation
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612108, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Mon Aug 01 22:13:54 2022
Hex dump of (file 11, block 612109) in trace file /home/oracle/app/diag/rdbms/xyoa/xyoa/incident/incdir_76230/xyoa_m000_480_i76230_a.trc
Corrupt block relative dba: 0x02c9570d (file 11, block 612109)
Fractured block found during validation
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x0e6f0601
check value in block header: 0x0
block checksum disabled
Reread of blocknum=612109, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612109, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612109, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612109, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Reread of blocknum=612109, file=/home/oracle/app/oradata/xyoa/V3XSPACE5.ora. found same corrupt data
Checker run found 1 new persistent data failures
。。。
4.问题分析确定是数据损坏，还是索引损坏
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_extents where file_id=11 and 612107 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------------------ -----------------
V3X IDX_AFFAIR_M_S_A_T INDEX
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_extents where file_id=11 and 612108 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------------------ -----------------
V3X IDX_AFFAIR_M_S_A_T INDEX
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_extents where file_id=11 and 612109 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------------------ -----------------
V3X IDX_AFFAIR_M_S_A_T INDEX
看来是同一个索引。
由于损坏的块都是索引，可以通过删除索引，然后再重建索引来解决，但考虑到索引太大，重建索引过程中，无法使用这个索引，对业务有影响，只有在没有办法的情况下，才会使用这一招。
SQL> r
1* select index_owner,INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where index_name='IDX_AFFAIR_M_S_A_T'
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
215941 HIGH OPEN 01-AUG-22 Datafile 11: '/home/oracle/app/oradata/xyoa/V3XSPACE5.ora' contains one or more corrupt blocks
--advise failure;
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
215941 HIGH OPEN 01-AUG-22 Datafile 11: '/home/oracle/app/oradata/xyoa/V3XSPACE5.ora' contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1300 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
1. No backup of block 612108 in file 11 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
2. No backup of block 612109 in file 11 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
3. No backup of block 612107 in file 11 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
4. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
备份在磁带中，需要设置通道到磁带。
5.2 直接使用RMAN恢复
run {
allocate channel ch0 type 'sbt_tape';
send 'NBBSA_SOURCE_MACHINE_NAME=oadata.shineray';
recover datafile 11 block 612107,612107,612109;
release channel ch0;
}
using target database control file instead of recovery catalog
allocated channel: ch0
channel ch0: SID=1809 device type=SBT_TAPE
channel ch0: Symantec/BackupExec/1.1.0
sent command to channel: ch0
Starting recover at 03-AUG-22
channel ch0: restoring block(s)
channel ch0: specifying block(s) to restore from backup set
restoring blocks of datafile 00011
channel ch0: reading from backup piece BE_gp13ua0b_1_1
channel ch0: piece handle=BE_gp13ua0b_1_1 tag=TAG20220730T200114
channel ch0: restored block(s) from backup piece 1
channel ch0: block restore complete, elapsed time: 00:21:15
starting media recovery
media recovery complete, elapsed time: 00:00:15
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 133881 2149137 883930663
File Name: /home/oracle/app/oradata/xyoa/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1181879
Index 0 789620
Other 0 43740
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 336000 883936909
File Name: /home/oracle/app/oradata/xyoa/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 335999
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 33 641 921351
File Name: /home/oracle/app/oradata/xyoa/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 15
Index 0 2
Other 0 590
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 7 4194302 883942123
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE1.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1471004
Index 0 565042
Other 0 2158249
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 7 4194302 883942069
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE2.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1502611
Index 0 573335
Other 0 2118349
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 7 4194302 883942063
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE3.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 997466
Index 0 937330
Other 0 2259499
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 21047 131072 883939623
File Name: /home/oracle/app/oradata/xyoa/ema.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 107506
Index 0 1532
Other 0 987
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 1 1592320 883925382
File Name: /home/oracle/app/oradata/xyoa/undotbs02.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 1592319
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10 OK 0 31 4194302 883943619
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE4.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1275372
Index 0 1107654
Other 0 1811245
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11 OK 0 52 4194302 883941991
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE5.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 910028
Index 0 643602
Other 0 2640620
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
12 OK 0 250726 3970560 883941865
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE6.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 874922
Index 0 429536
Other 0 2415376
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
13 OK 0 1164442 1310720 883934930
File Name: /home/oracle/app/oradata/xyoa/V3XSPACE7.ora
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 20365
Index 0 11326
Other 0 114587
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 1308
Finished validate at 03-AUG-22
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-8-3 14:29:19
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对