Oracle ADG备库SYSAUX数据文件坏块恢复处理（ORA-00600,ORA-10567,ORA-10564......

最新推荐文章于 2024-07-30 09:35:41 发布

数据和云

最新推荐文章于 2024-07-30 09:35:41 发布

阅读量1.2k

点赞数

文章标签：数据库 hadoop linux 大数据运维

本文链接：https://blog.csdn.net/enmotech/article/details/120775448

版权

本文详细介绍了Oracle Active Data Guard (ADG) 备库中SYSAUX数据文件遇到坏块时的恢复过程，包括如何处理ORA-00600等错误，以及通过备份恢复、切换数据文件和调整控制文件等步骤解决该问题。

摘要由CSDN通过智能技术生成

点击上方"蓝字"

关注我们，享更多干货！

本文阐述了Oracle ADG备库SYSAUX数据文件坏块恢复处理（ORA-00600,ORA-10567,ORA-10564,ORA-01110,ORA-10561）的思路、步骤、解决方案。

查看ADG同步情况

SQL> set line 999
SQL> select inst_id,thread#,process,pid,status,client_process,client_pid,sequence#,block#,active_agents,known_agents from gv$managed_standby;

 ID    THREAD# PROCESS            PID        STATUS                   CLIENT_PROCESS   CLIENT_PID       SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--- ---------- ------------------ ---------- ------------------------ ---------------- --------- ---------- ---------- ------------- ------------
  1          2 ARCH               75175      CLOSING                  ARCH             75175             57     356352             0            0
             0 DGRD               75179      ALLOCATED                N/A              N/A                0          0             0            0
             0 DGRD               75183      ALLOCATED                N/A              N/A                0          0             0            0
             2 ARCH               75191      CLOSING                  ARCH             75191             58     352256             0            0
             2 ARCH               75195      CLOSING                  ARCH             75195             59          1             0            0
             1 ARCH               75199      CLOSING                  ARCH             75199             72          1             0            0
             0 RFS                98051      IDLE                     UNKNOWN          37620              0          0             0            0
             0 RFS                100998     IDLE                     UNKNOWN          1424               0          0             0            0
             2 RFS                101005     IDLE                     LGWR             1439              60      31391             0            0
             0 RFS                97935      IDLE                     UNKNOWN          37632              0          0             0            0
             1 RFS                97933      IDLE                     LGWR             37670             73     131240             0            0
             1 RFS                97941      IDLE                     Archival         37602              0          0             0            0
             2 RFS                101035     IDLE                     Archival         1412               0          0             0            0
  2          0 ARCH               121781     CONNECTED                ARCH             121781             0          0             0            0
             0 DGRD               121789     ALLOCATED                N/A              N/A                0          0             0            0
             0 DGRD               121793     ALLOCATED                N/A              N/A                0          0             0            0
             0 ARCH               121809     CONNECTED                ARCH             121809             0          0             0            0
             0 ARCH               121813     CONNECTED                ARCH             121813             0          0             0            0
             0 ARCH               121822     CONNECTED                ARCH             121822             0          0             0            0
19 rows selected.

SQL> select inst_id,name,value,time_computed,DATUM_TIME,sysdate from gv$dataguard_stats order by inst_id;  

 ID NAME                           VALUE                          TIME_COMPUTED        LAST_RECEIVED_TIME   SYSDATE
--- ------------------------------ ------------------------------ -------------------- -------------------- -------------------
  1 apply finish time              +00 00:18:11.925               09/08/2021 10:52:23                       2021-09-08 10:52:23
    apply lag                      +03 20:51:36                   09/08/2021 10:52:23  09/08/2021 10:52:22  2021-09-08 10:52:23
    transport lag                  +00 00:00:00                   09/08/2021 10:52:23  09/08/2021 10:52:22  2021-09-08 10:52:23
    estimated startup time         42                             09/08/2021 10:52:23                       2021-09-08 10:52:23
  2 apply finish time                                             09/08/2021 10:52:23                       2021-09-08 10:52:23
    apply lag                                                     09/08/2021 10:52:23                       2021-09-08 10:52:23
    transport lag                  +00 00:00:00                   09/08/2021 10:52:23  09/08/2021 10:52:22  2021-09-08 10:52:23
    estimated startup time         46                             09/08/2021 10:52:23                       2021-09-08 10:52:23
8 rows selected.

MRP0进程没启动

重新启动一下

SQL> alter database recover managed standby database using current logfile disconnect;

还是没有MRP0进程

看看alert日志

alter database recover managed standby database using current logfile disconnect
ERROR: ORA-00600: internal error code, arguments: [3020] recovery detected a data block with invalid SCN. This could be caused by a lost write on the primary; do NOT attempt to bypass this error by copying blocks or datafiles from the primary database to the standby database because that would propagate the lost write from the primary to the standby.
Errors in file /home/db/oracle/diag/rdbms/TESTDBdg/TESTDB1/trace/TESTDB1_pr01_55707.trc  (incident=150042):
ORA-00600: internal error code, arguments: [3020], [3], [307241], [12890153], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 307241, file offset is 2516918272 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: '+DATA/TESTDBDG/DATAFILE/sysaux.268.1081620609'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 331551
Incident details in: /home/db/oracle/diag/rdbms/TESTDBdg/TESTDB1/incident/incdir_150042/TESTDB1_pr01_55707_i150042.trc
2021-09-08T10:58:34.421283+08:00
*****