分类目录归档：Oracle

收集统计信息报ORA-00600 16515问题解决

发表于 2024 年 5 月 18 日由惜分飞

在一套win 11.2.0.1的数据库在收集统计信息报ORA-600 16515错误

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1


SQL> exec dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL');
BEGIN dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL'); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 25335
ORA-06512: ?? "SYS.DBMS_STATS", line 25511
ORA-06512: ?? "SYS.DBMS_STATS", line 25467
ORA-06512: ?? line 1

确认报错对象

SQL> select owner, object_name, object_type
  2  from sys.dba_objects
  3  where object_id =6375;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SYS
WRI$_SQLMON_USAGE
TABLE

对对象进行处理,确认对象本身没有问题,尝试收集统计信息依旧报错

SQL> alter table WRI$_SQLMON_USAGE move;

表已更改。

SQL> select index_name from dba_indexes where table_name='WRI$_SQLMON_USAGE';

未选定行

SQL> SELECT COUNT(1) FROM WRI$_SQLMON_USAGE;

  COUNT(1)
----------
         1

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

删除统计再尝试

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'WRI$_SQLMON_USAGE');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

使用ANALYZE收集统计信息正常

SQL> ANALYZE TABLE  WRI$_SQLMON_USAGE  COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

表已分析。

删除hist_head$中对应报错行记录,ORA-00600 16515问题解决

SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=6376 AND intcol#=1;

ROWID                    OBJ#    INTCOL# TIMESTAMP#
------------------ ---------- ---------- --------------
AAAAHAAABAAAIELABQ       6376          1 01-4?  -24

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

SQL>  DELETE FROM hist_head$ WHERE ROWID='AAAAHAAABAAAIELABQ';

已删除 1 行。

SQL> COMMIT;

提交完成。

SQL> begin
  2  dbms_stats.delete_table_stats ( ownname => user, tabname => 'WRI$_SQLMON_USAGE', cascade_parts => true, cascade_col
umns => true, cascade_indexes => true, force => true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();

PL/SQL 过程已成功完成。

参考文档:DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1)

发表在 Oracle | 标签为 ORA-600 16515 | 评论关闭

rm -rf误删Oracle数据库恢复

发表于 2024 年 5 月 16 日由惜分飞

有客户把虚拟化环境中装有oracle数据库的linux操作系统,由于操作失误在/下面执行了rm -rf *，导致所有文件被删除,系统无法启动.客户希望要求恢复出其中的Oracle数据库.由于是虚拟化环境,然后客户直接从虚拟化平台下载下来磁盘文件,通过工具加载和分析确认是一个xfs的文件系统

使用工具进一步扫描分析,找到部分数据文件

这里可以获取到两个信息:
1. 尝试恢复oracle的control01.ctl文件,然后通过该文件尝试分析其他数据文件位置,运气不错该文件恢复出来是好的,直接加载到新库查询v$datafile,分析出来所有数据文件信息
2. 这里有一个非常不幸的信息,oracle最核心的system01.dbf文件大小明显异常,进一步分析该文件信息,结论是该文件无法通过反删除方式进行恢复

先把可以os层面可以恢复的数据恢复出来,并且检查坏块情况

对于异常的system文件,有两个处理方法:
1. 通过阅览被删除的文件，发现客户有5月14日1点左右的rman备份,通过恢复软件中完整度提示,大概率应该没有什么问题,但是分析发现部分归档日志损坏无法完整恢复
2. 通过对磁盘做碎片,恢复出来该数据文件,参考以往文章:
dbca删除库和rm删库恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
通过这个方法运气不错,恢复出来该库的system01.dbf文件非常完整0丢失

[oracle@localhost oradata]$ dbv file=system01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Thu May 15 23:26:57 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oradata/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 199680
Total Pages Processed (Data) : 113988
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 26869
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 40253
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18570
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658228557 (0.658228557)

完成上述恢复工作之后,目前确认只有sysaux01.dbf有8026个block损坏,但是该表空间不涉及业务数据,尝试在新的系统中直接修改路径并open库

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback off;

Database altered.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

运气不错,数据库直接open成功,现在处理sysaux01.dbf中的损坏文件:
1. 确认该文件具体坏块开始位置:

2. 由于坏块在文件中比较靠后,分析实际存储数据最后的位置

SQL> select max(block_id+blocks) from dba_extents where file_id=3;

MAX(BLOCK_ID+BLOCKS)
--------------------
             3493120

最后存储数据的位置小于坏块的位置,证明坏块部分是没有存储数据的,直接resize掉坏块部分

SQL> alter database datafile '/u01/oradata/sysaux01.dbf' resize 27290m;

Database altered.

然后dbv该数据文件,确认没有任何问题

[oracle@localhost trace]$ dbv file=/u01/oradata/sysaux01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Wed May 15 22:43:00 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oradata/sysaux01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 3493120
Total Pages Processed (Data) : 1516833
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1868832
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 56577
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 32107
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18771
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658223915 (0.658223915)

使用rman检测全库,也确定没有任何问题

[oracle@localhost trace]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 22:43:58 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XIFENFEI (DBID=2912535091)

RMAN> 

RMAN> 

RMAN> backup validate check logical database skip inaccessible;

Starting backup at 15-MAY-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=278 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
………………
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
32   OK     0              6273         6400            370625094 
  File Name: /u01/oradata/xff_com.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              127             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              163752       832000          627920639 
  File Name: /u01/oradata/XFF_DATA_202312231.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              374296          
  Index      0              285002          
  Other      0              8950            

Finished backup at 15-MAY-24

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 15 22:47:44 2024
Version 19.15.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> select * from v$database_block_corruption ;

no rows selected

SQL>

至此对于这次rm -rf /*的故障实现了Oracle数据库完美恢复,数据0丢失.

发表在非常规恢复 | 标签为 oracle删除恢复, oracle碎片, rm -rf恢复 | 评论关闭

分布式存储故障导致数据库无法启动故障处理

发表于 2024 年 5 月 11 日由惜分飞

国内xx医院使用了国外医疗行业龙头的pacs系统,由于是一个历史库,存放在分布式存储中,由于存储同时多个节点故障,导致数据库多个文件异常,数据库无法启动,三方维护人员尝试通通过rman归档进行应用日志,结果发现日志有损坏报ORA-00354 ORA-00353,无法记录恢复,希望我们给予支持

Mon Apr 29 13:28:40 2024
Media Recovery failed with error 354
Mon Apr 29 13:28:40 2024
Errors in file F:\XXXXXX_DB\ORACLE\ADMIN\diag\rdbms\xxx\msxxx1\trace\xxx_pr00_4568.trc:
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 487424 change 8737273868 time 04/01/2024 01:38:25
ORA-00334: archived log: 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'
ORA-283 signalled during: alter database recover logfile 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'...

接手故障之后,通过尝试恢复发现除该错误之外,还有ORA-600 4552之类错误