备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458

2025-04-13 技术教程

SQL>selectstatusfromv$instance;STATUS------------MOUNTEDSQL>recovermanagedstandbydatabaseusingcurrentlogfiledisconnect;Mediarecoverycomplete.SQL>SQL>recovermanagedstandbydatabasecancel;Mediarecoverycomplete.SQL>SQL>alterdatabaseopen;alterdatabaseopen*ERRORatline1:ORA-10458:standbydatabaserequiresrecoveryORA-01152:file1wasnotrestoredfromasufficientlyoldbackupORA-01110:datafile1:'+SDE_DATA/sdedg/datafile/system.283.984230107'SQL>

查看日志，发现所有主库的归档日志都没有被应用，手动恢复日志

SQL>recovermanagedstandbydatabase

查看告警日志，发现错误

[oracle@sde1trace]$tail-falert_sde1.logFAL[client]:FailedtorequestgapsequenceGAP-SCNrange:0x0e57.4d6ec257-0x0e57.4d6ec257DBID2155281896branch984123832FAL[client]:AlldefinedFALservershavebeenattempted.------------------------------------------------------------CheckthattheCONTROL_FILE_RECORD_KEEP_TIMEinitializationparameterisdefinedtoavaluethat'ssufficientlylargeenoughtomaintainadequatelogswitchinformationtoresolvearchiveloggaps.------------------------------------------------------------WedAug1514:47:102018Recoveryinterrupted!MediaRecoveryfailedwitherror448Errorsinfile/u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc:ORA-00283:recoverysessioncanceledduetoerrorsORA-00448:normalcompletionofbackgroundprocessSlaveexitingwithORA-283exception

查看错误文件；

[oracle@sde1~]$tail-fn200/u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc

里边有错误指示：

***2018-08-1514:34:10.634MediaRecoveryaddredothread2***2018-08-1514:34:10.7214320krsh.cMediaRecoveryWaitingforthread1sequence129Redoshippingclientperformingstandbylogin

是日志文件没有被应用，查看备库的日志组文件，发现日志组过多，先删除过多的备库日志组，然后重建备库日志组

SQL>selectgroup#,member,typefromv$logfile;GROUP#MEMBERTYPE-----------------------------------------------------------------------------6+SDE_FRA/redo06.logONLINE5+SDE_FRA/redo05.logONLINE2+SDE_FRA/redo02.logONLINE1+SDE_FRA/redo01.logONLINE3+SDE_DATA/sdedg/onlinelog/group_3.284.984232869ONLINE3+dataONLINE4+SDE_DATA/sdedg/onlinelog/group_4.302.984232871ONLINE4+dataONLINE7+SDE_DATA/sdedg/onlinelog/group_7.301.984232871ONLINE8+SDE_DATA/sdedg/onlinelog/group_8.281.984232871ONLINE9+SDE_DATA/sdedg/onlinelog/group_9.280.984232871ONLINEGROUP#MEMBERTYPE-----------------------------------------------------------------------------10+SDE_DATA/sdedg/onlinelog/group_10.300.984232873ONLINE11+SDE_DATA/sdedg/onlinelog/group_11.299.984232873ONLINE12+SDE_DATA/sdedg/onlinelog/group_12.271.984232873ONLINE13+SDE_DATA/sdedg/onlinelog/group_13.274.984232873ONLINE14+SDE_DATA/sdedg/onlinelog/group_14.284.984230143STANDBY15+SDE_DATA/sdedg/onlinelog/group_15.284.984230145STANDBY16+SDE_DATA/sdedg/onlinelog/group_16.284.984230147STANDBY17+SDE_DATA/sdedg/onlinelog/group_17.284.984230149STANDBY18+SDE_DATA/sdedg/onlinelog/group_18.284.984230151STANDBY19+SDE_DATA/sdedg/onlinelog/group_19.284.984230153STANDBY20+SDE_DATA/sdedg/onlinelog/group_20.284.984230155STANDBYGROUP#MEMBERTYPE-----------------------------------------------------------------------------21+SDE_DATA/sdedg/onlinelog/group_21.284.984230157STANDBY22+SDE_DATA/sdedg/onlinelog/group_22.284.984230159STANDBY23+SDE_DATA/sdedg/onlinelog/group_23.284.984230161STANDBY24+SDE_DATA/sdedg/onlinelog/group_24.284.984230163STANDBY25+SDE_DATA/sdedg/onlinelog/group_25.284.984230165STANDBY26+SDE_DATA/sdedg/onlinelog/group_26.284.984230167STANDBY27+SDE_DATA/sdedg/onlinelog/group_27.284.984230169STANDBY28+SDE_DATA/sdedg/onlinelog/group_28.284.984230171STANDBY7+dataONLINE8+dataONLINE9+dataONLINEGROUP#MEMBERTYPE-----------------------------------------------------------------------------10+dataONLINE11+dataONLINE12+dataONLINE13+dataONLINE14+dataSTANDBY15+dataSTANDBY16+dataSTANDBY17+dataSTANDBY18+dataSTANDBY19+dataSTANDBY20+dataSTANDBYGROUP#MEMBERTYPE-----------------------------------------------------------------------------21+dataSTANDBY22+dataSTANDBY23+dataSTANDBY24+dataSTANDBY25+dataSTANDBY26+dataSTANDBY27+dataSTANDBY28+dataSTANDBY52rowsselected.SQL>alterdatabasedroplogfilegroup14;Databasealtered.SQL>alterdatabasedroplogfilegroup15;Databasealtered.SQL>alterdatabasedroplogfilegroup16;Databasealtered.SQL>alterdatabasedroplogfilegroup17;Databasealtered.SQL>alterdatabasedroplogfilegroup18;Databasealtered.SQL>alterdatabasedroplogfilegroup19;Databasealtered.SQL>SQL>alterdatabasedroplogfilegroup20;Databasealtered.SQL>alterdatabasedroplogfilegroup21;Databasealtered.SQL>alterdatabasedroplogfilegroup22;Databasealtered.SQL>alterdatabasedroplogfilegroup23;Databasealtered.SQL>alterdatabasedroplogfilegroup24;Databasealtered.SQL>alterdatabasedroplogfilegroup25;Databasealtered.SQL>alterdatabasedroplogfilegroup26;Databasealtered.SQL>alterdatabasedroplogfilegroup27;Databasealtered.SQL>alterdatabasedroplogfilegroup28;Databasealtered.SQL>selectgroup#,member,typefromv$logfile;GROUP#MEMBERTYPE-----------------------------------------------------------------------------6+SDE_FRA/redo06.logONLINE5+SDE_FRA/redo05.logONLINE2+SDE_FRA/redo02.logONLINE1+SDE_FRA/redo01.logONLINE3+SDE_DATA/sdedg/onlinelog/group_3.284.984232869ONLINE3+dataONLINE4+SDE_DATA/sdedg/onlinelog/group_4.302.984232871ONLINE4+dataONLINE7+SDE_DATA/sdedg/onlinelog/group_7.301.984232871ONLINE8+SDE_DATA/sdedg/onlinelog/group_8.281.984232871ONLINE9+SDE_DATA/sdedg/onlinelog/group_9.280.984232871ONLINEGROUP#MEMBERTYPE-----------------------------------------------------------------------------10+SDE_DATA/sdedg/onlinelog/group_10.300.984232873ONLINE11+SDE_DATA/sdedg/onlinelog/group_11.299.984232873ONLINE12+SDE_DATA/sdedg/onlinelog/group_12.271.984232873ONLINE13+SDE_DATA/sdedg/onlinelog/group_13.274.984232873ONLINE7+dataONLINE8+dataONLINE9+dataONLINE10+dataONLINE11+dataONLINE12+dataONLINE13+dataONLINE22rowsselected.

然后通过添加日志组的脚本重新添加日志组

重新执行同步

SQL>recovermanagedstandbydatabaseusingcurrentlogfiledisconnect;Mediarecoverycomplete.SQL>SQL>SQL>recovermanagedstandbydatabasecancel;Mediarecoverycomplete.SQL>SQL>SQL>alterdatabaseopen;alterdatabaseopen*ERRORatline1:ORA-10458:standbydatabaserequiresrecoveryORA-01152:file1wasnotrestoredfromasufficientlyoldbackupORA-01110:datafile1:'+SDE_DATA/sdedg/datafile/system.283.984230107'

日志组的问题已经解决，现在还需要恢复数据库，考虑主备库同步的问题，查看主备库参数是否正常

SQL>showparameterlog_archive_configNAMETYPEVALUE-----------------------------------------------------------------------------log_archive_configstringSQL>showparameterlog_archive_dest_2NAMETYPEVALUE-----------------------------------------------------------------------------log_archive_dest_17stringlog_archive_dest_18stringlog_archive_dest_19stringlog_archive_dest_2string

发现主库的转换参数都没有设置，重新设置这两个参数

SQL>altersystemsetlog_archive_config='dg_config=(sde,sdedg)';SQL>altersystemsetlog_archive_dest_2='service=sde_newlgwrasyncvalid_for=(online_logfiles,primary_role)db_unique_name=sdedg';

SQL>showparameterlog_archive_NAMETYPEVALUE-----------------------------------------------------------------------------log_archive_configstringdg_config=(sde,sdedg)log_archive_deststringlog_archive_dest_1stringlocation=use_db_recovery_file_destlog_archive_dest_10stringlog_archive_dest_11stringlog_archive_dest_12stringlog_archive_dest_13stringlog_archive_dest_14stringlog_archive_dest_15stringlog_archive_dest_16stringNAMETYPEVALUE-----------------------------------------------------------------------------log_archive_dest_17stringlog_archive_dest_18stringlog_archive_dest_19stringlog_archive_dest_2stringservice=sde_newlgwrasyncvalid_for=(online_logfiles,primary_role)db_unique_name=sdedg

参数设置正常了，备库重新开同步看能否正常

SQL>recovermanagedstandbydatabaseusingcurrentlogfiledisconnect;Mediarecoverycomplete.

备库查看日志应用进度，在主库查看已经归档的sequence号

SQL>SELECTal.thrd"Thread",almax"LastSeqReceived",lhmax"LastSeqApplied"FROM(selectthread#thrd,MAX(sequence#)almaxFROMv$archived_logWHEREresetlogs_change#=(SELECTresetlogs_change#FROMv$database)GROUPBYthread#)al,(SELECTthread#thrd,MAX(sequence#)lhmaxFROMv$log_historyWHEREresetlogs_change#=(SELECTresetlogs_change#FROMv$database)GROUPBYthread#)lhWHEREal.thrd=lh.thrd;ThreadLastSeqReceivedLastSeqApplied-------------------------------------------11431432138138SQL>archiveloglistDatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestinationUSE_DB_RECOVERY_FILE_DESTOldestonlinelogsequence138Nextlogsequencetoarchive144Currentlogsequence144

等同步完成，开启ADG

SQL>recovermanagedstandbydatabasecancel;Mediarecoverycomplete.SQL>SQL>alterdatabaseopen;Databasealtered.SQL>SQL>recovermanagedstandbydatabaseusingcurrentlogfiledisconnect;Mediarecoverycomplete.SQL>SQL>setlines1000SQL>selectPROCESS,PID,STATUS,GROUP#,RESETLOG_ID,THREAD#,SEQUENCE#fromv$managed_standby;PROCESSPIDSTATUSGROUP#RESETLOG_IDTHREAD#SEQUENCE#------------------------------------------------------------------------------------------------------ARCH22841CONNECTEDN/A000ARCH22843CONNECTEDN/A000ARCH22845CLOSING159841238321143ARCH22847CLOSING229841238322138MRP03116APPLYING_LOGN/A9841238322139RFS2722IDLEN/A000RFS2892IDLEN/A000RFS2894IDLE69841238321144RFS2896IDLEN/A000RFS2928IDLEN/A000RFS2930IDLEN/A000PROCESSPIDSTATUSGROUP#RESETLOG_IDTHREAD#SEQUENCE#------------------------------------------------------------------------------------------------------RFS2932IDLEN/A000RFS2948IDLE3984123832213913rowsselected.

现在DG同步正常了。