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

查看ADG同步情况

SQLsetline999SQLselectinst_id,thread,blockPROCESSPIDSTATUSCLIENT_PROCESSCLIENT_PIDSEQUENCEACTIVE_AGENTSKNOWN_AGENTS---------------------------------------------------------------------------------------------------------------------------------------12ARCH75175CLOSINGARCH75000DGRD75179ALLOCATEDN/AN/A00000DGRD75183ALLOCATEDN/AN/A00002ARCH75191CLOSINGARCH75192ARCH75195CLOSINGARCH75195591001ARCH75199CLOSINGARCH75199721000RFS98051IDLEUNKNOWN3762000000RFS100998IDLEUNKNOWN142400002RFS101005IDLELGWR000RFS97935IDLEUNKNOWN3763200001RFS97933IDLELGWR3767073131240001RFS97941IDLEArchival3760200002RFS101035IDLEArchival1412000020ARCH121781CONNECTEDARCH12178100000DGRD121789ALLOCATEDN/AN/A00000DGRD121793ALLOCATEDN/AN/A00000ARCH121809CONNECTEDARCH12180900000ARCH121813CONNECTE_id,name,value,time_computed,DATUM_TIME,sysdatefromgv$dataguard_statsorderbyinst_id;IDNAMEVALUETIME_COMPUTEDLAST_RECEIVED_TIMESYSDATE--------------------------------------------------------------------------------------------------------------------------1applyfinishtime+0000:18:11.92509/08/202110:52:232021-09-0810:52:23applylag+0320:51:3609/08/202110:52:2309/08/202110:52:222021-09-0810:52:23transportlag+0000:00:0009/08/202110:52:2309/08/202110:52:222021-09-0810:52:23estimatedstartuptime4209/08/202110:52:232021-09-0810:52:232applyfinishtime09/08/202110:52:232021-09-0810:52:23applylag09/08/202110:52:232021-09-0810:52:23transportlag+0000:00:0009/08/202110:52:2309/08/202110:52:222021-09-0810:52:23estimatedstartuptime4609/08/202110:52:232021-09-0810:52:238rowsselected.

MRP0进程没启动

重新启动一下

SQLalterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnect;

还是没有MRP0进程

看看alert日志

alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectERROR:ORA-00600:internalerrorcode,arguments:[3020];doNOTattempttobypassthiserrorbycopyingblocksordatafilesfromtheprimarydatabasetothestandbydatabase/home/db/oracle/diag/rdbms/TESTDBdg/TESTDB1/trace/TESTDB1_pr01_55707.trc(incident=150042):ORA-00600:internalerrorcode,arguments:[3020],[3],[307241],[12890153],[],[],[],[],[],[],[],[]ORA-10567:Redoisinconsistentwithdatablock(file307241,fileoffsetis2516918272bytes)ORA-10564:tablespaceSYSAUXORA-01110:datafile3:'+DATA/TESTDBDG/DATAFILE/'ORA-10561:blocktype'TRANSACTIONMANAGEDDATABLOCK',dataobject3,block3315512021-09-08T10:58:47.330512+08:00Dumpingdiagnosticdataindirectory=[cdmp_20210908105847],requestedby(instance=1,osid=55707(PR01)),summary=[incident=150042].2021-09-08T10:58:47.339785+08:00Errorsinfile/home/db/oracle/diag/rdbms/TESTDBdg/TESTDB1/trace/TESTDB1_mrp0_55537.trc(incident=149889):ORA-00600:internalerrorcode,arguments:[3020],[3],[307241],[12890153],[],[],[],[],[],[],[],[]ORA-10567:Redoisinconsistentwithdatablock(file307241,fileoffsetis2516918272bytes)ORA-10564:tablespaceSYSAUXORA-01110:datafile3:'+DATA/TESTDBDG/DATAFILE/'ORA-10561:blocktype'TRANSACTIONMANAGEDDATABLOCK',dataobject3,block3315512021-09-08T10:58:52.104916+08:00BackgroundMediaRecoveryprocessshutdown(TESTDB1)

数据文件有坏快

尝试修复

RMANblockrecoverdatafile3block307241;Startingrecoverat08-SEP-21usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=1165instance=TESTDB1devicetype=DISKstartingmediarecoverymediarecoverycomplete,elapsedtime:00:00:00Finishedrecoverat08-SEP-21

启动下MRP，检查下日志

SQLalterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnect;2021-09-08T11:14:17.325866+08:00Errorsinfile/home/db/oracle/diag/rdbms/TESTDBdg/TESTDB1/trace/TESTDB1_mrp0_64635.trc:ORA-00600:internalerrorcode,arguments:[3020],[3],[307241],[12890153],[],[],[],[],[],[],[],[]ORA-10567:Redoisinconsistentwithdatablock(file307241,fileoffsetis2516918272bytes)ORA-10564:tablespaceSYSAUXORA-01110:datafile3:'+DATA/TESTDBDG/DATAFILE/'ORA-10561:blocktype'TRANSACTIONMANAGEDDATABLOCK',dataobject,process,pid,status,client_process,client_pid,sequence,active_agents,known_agentsfromgv$managed_standby;IDTHREADBLOCK#ACTIVE_AGENTSKNOWN_AGENTS---------------------------------------------------------------------------------------------------------------------------------------------------20ARCH25489CONNECTEDARCH2548900000DGRD25493ALLOCATEDN/AN/A00000DGRD25500ALLOCATEDN/AN/A00000ARCH25512CONNECTEDARCH2551200000ARCH25524CONNECTEDARCH2552400000ARCH25531CONNECTEDARCH25531000010ARCH4917CONNECTEDARCH491700000DGRD4921ALLOCATEDN/AN/A00000DGRD4927ALLOCATEDN/AN/A00000ARCH4937CONNECTEDARCH493700000ARCH4945CONNECTEDARCH494500000ARCH4952CONNECTEDARCH495200001RFS6728IDLEArchival141200001RFS6730IDLEArchival3760200001RFS6743IDLELGWR73002RFS6749RECEIVINGLGWR9783661101874002MRP033125APPLYING_LOGN/AN/_id,name,value,time_computed,DATUM_TIME,sysdatefromgv$dataguard_statsorderbyinst_id;IDNAMEVALUETIME_COMPUTEDLAST_RECEIVED_TIMESYSDATE--------------------------------------------------------------------------------------------------------------------------1applyfinishtime+0000:00:00.00009/08/202117:22:012021-09-0817:21:59applylag+0000:00:0009/08/202117:22:0109/08/202117:21:592021-09-0817:21:59transportlag+0000:00:0009/08/202117:22:0109/08/202117:21:592021-09-0817:21:59estimatedstartuptime4209/08/202117:22:012021-09-0817:21:592applyfinishtime09/08/202117:22:002021-09-0817:21:59applylag09/08/202117:22:002021-09-0817:21:59transportlag+0001:33:0409/08/202117:22:0009/08/202117:21:582021-09-0817:21:59estimatedstartuptime4609/08/202117:22:002021-09-0817:21:598rowsselected.

同步进程正常

现在启动数据库

SQLalterdatabaseopen;alterdatabaseopen*ERRORatline1:ORA-10456:cannotopenstandbydatabase;mediarecoverysessionmaybeinprogressSQLalterdatabaserecovermanagedstandbydatabasecancel;;edisconnect;Databasealtered.

张海，云和恩墨西区技术顾问，OracleOCP，OGCA。现负责金融行业Oracle数据库驻场维保工作。