备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458
发表于：2025-01-21 作者：千家信息网编辑
千家信息网最后更新 2025年01月21日，备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458SQL> select status from v$instance;STATUS------------MOUNTED
千家信息网最后更新 2025年01月21日备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458
备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458
SQL> select status from v$instance;STATUS------------MOUNTEDSQL> recover managed standby database using current logfile disconnect;Media recovery complete.SQL> SQL> recover managed standby database cancel;Media recovery complete.SQL> SQL> alter database open ;alter database open*ERROR at line 1:ORA-10458: standby database requires recoveryORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '+SDE_DATA/sdedg/datafile/system.283.984230107'SQL>
查看日志，发现所有主库的归档日志都没有被应用，手动恢复日志
SQL> recover managed standby database
查看告警日志，发现错误
[oracle@sde1 trace]$ tail -f alert_sde1.log FAL[client]: Failed to request gap sequence GAP - SCN range: 0x0e57.4d6ec257 - 0x0e57.4d6ec257 DBID 2155281896 branch 984123832FAL[client]: All defined FAL servers have been attempted.------------------------------------------------------------Check that the CONTROL_FILE_RECORD_KEEP_TIME initializationparameter is defined to a value that's sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps.------------------------------------------------------------Wed Aug 15 14:47:10 2018Recovery interrupted!Media Recovery failed with error 448Errors in file /u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc:ORA-00283: recovery session canceled due to errorsORA-00448: normal completion of background processSlave exiting with ORA-283 exception
查看错误文件；
[oracle@sde1 ~]$ tail -fn200 /u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc
里边有错误指示：
*** 2018-08-15 14:34:10.634Media Recovery add redo thread 2*** 2018-08-15 14:34:10.721 4320 krsh.cMedia Recovery Waiting for thread 1 sequence 129Redo shipping client performing standby login
是日志文件没有被应用，查看备库的日志组文件，发现日志组过多，先删除过多的备库日志组，然后重建备库日志组
SQL> select group#,member,type from v$logfile;GROUP# MEMBER TYPE---------- ------------------------------------------------------------ ------- 6 +SDE_FRA/redo06.log ONLINE 5 +SDE_FRA/redo05.log ONLINE 2 +SDE_FRA/redo02.log ONLINE 1 +SDE_FRA/redo01.log ONLINE 3 +SDE_DATA/sdedg/onlinelog/group_3.284.984232869 ONLINE 3 +data ONLINE 4 +SDE_DATA/sdedg/onlinelog/group_4.302.984232871 ONLINE 4 +data ONLINE 7 +SDE_DATA/sdedg/onlinelog/group_7.301.984232871 ONLINE 8 +SDE_DATA/sdedg/onlinelog/group_8.281.984232871 ONLINE 9 +SDE_DATA/sdedg/onlinelog/group_9.280.984232871 ONLINEGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873 ONLINE11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873 ONLINE12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873 ONLINE13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873 ONLINE14 +SDE_DATA/sdedg/onlinelog/group_14.284.984230143 STANDBY15 +SDE_DATA/sdedg/onlinelog/group_15.284.984230145 STANDBY16 +SDE_DATA/sdedg/onlinelog/group_16.284.984230147 STANDBY17 +SDE_DATA/sdedg/onlinelog/group_17.284.984230149 STANDBY18 +SDE_DATA/sdedg/onlinelog/group_18.284.984230151 STANDBY19 +SDE_DATA/sdedg/onlinelog/group_19.284.984230153 STANDBY20 +SDE_DATA/sdedg/onlinelog/group_20.284.984230155 STANDBYGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------21 +SDE_DATA/sdedg/onlinelog/group_21.284.984230157 STANDBY22 +SDE_DATA/sdedg/onlinelog/group_22.284.984230159 STANDBY23 +SDE_DATA/sdedg/onlinelog/group_23.284.984230161 STANDBY24 +SDE_DATA/sdedg/onlinelog/group_24.284.984230163 STANDBY25 +SDE_DATA/sdedg/onlinelog/group_25.284.984230165 STANDBY26 +SDE_DATA/sdedg/onlinelog/group_26.284.984230167 STANDBY27 +SDE_DATA/sdedg/onlinelog/group_27.284.984230169 STANDBY28 +SDE_DATA/sdedg/onlinelog/group_28.284.984230171 STANDBY 7 +data ONLINE 8 +data ONLINE 9 +data ONLINEGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------10 +data ONLINE11 +data ONLINE12 +data ONLINE13 +data ONLINE14 +data STANDBY15 +data STANDBY16 +data STANDBY17 +data STANDBY18 +data STANDBY19 +data STANDBY20 +data STANDBYGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------21 +data STANDBY22 +data STANDBY23 +data STANDBY24 +data STANDBY25 +data STANDBY26 +data STANDBY27 +data STANDBY28 +data STANDBY52 rows selected.SQL> alter database drop logfile group 14;Database altered.SQL> alter database drop logfile group 15;Database altered.SQL> alter database drop logfile group 16;Database altered.SQL> alter database drop logfile group 17;Database altered.SQL> alter database drop logfile group 18;Database altered.SQL> alter database drop logfile group 19;Database altered.SQL> SQL> alter database drop logfile group 20;Database altered.SQL> alter database drop logfile group 21;Database altered.SQL> alter database drop logfile group 22;Database altered.SQL> alter database drop logfile group 23;Database altered.SQL> alter database drop logfile group 24;Database altered.SQL> alter database drop logfile group 25;Database altered.SQL> alter database drop logfile group 26;Database altered.SQL> alter database drop logfile group 27;Database altered.SQL> alter database drop logfile group 28;Database altered.SQL> select group#,member,type from v$logfile;GROUP# MEMBER TYPE---------- ------------------------------------------------------------ ------- 6 +SDE_FRA/redo06.log ONLINE 5 +SDE_FRA/redo05.log ONLINE 2 +SDE_FRA/redo02.log ONLINE 1 +SDE_FRA/redo01.log ONLINE 3 +SDE_DATA/sdedg/onlinelog/group_3.284.984232869 ONLINE 3 +data ONLINE 4 +SDE_DATA/sdedg/onlinelog/group_4.302.984232871 ONLINE 4 +data ONLINE 7 +SDE_DATA/sdedg/onlinelog/group_7.301.984232871 ONLINE 8 +SDE_DATA/sdedg/onlinelog/group_8.281.984232871 ONLINE 9 +SDE_DATA/sdedg/onlinelog/group_9.280.984232871 ONLINEGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873 ONLINE11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873 ONLINE12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873 ONLINE13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873 ONLINE 7 +data ONLINE 8 +data ONLINE 9 +data ONLINE10 +data ONLINE11 +data ONLINE12 +data ONLINE13 +data ONLINE22 rows selected.
然后通过添加日志组的脚本重新添加日志组
SQL> select group#,member,type from v$logfile;GROUP# MEMBER TYPE---------- ------------------------------------------------------------ ------- 6 +SDE_FRA/redo06.log ONLINE 5 +SDE_FRA/redo05.log ONLINE 2 +SDE_FRA/redo02.log ONLINE 1 +SDE_FRA/redo01.log ONLINE 3 +SDE_DATA/sdedg/onlinelog/group_3.284.984232869 ONLINE 3 +data ONLINE 4 +SDE_DATA/sdedg/onlinelog/group_4.302.984232871 ONLINE 4 +data ONLINE 7 +SDE_DATA/sdedg/onlinelog/group_7.301.984232871 ONLINE 8 +SDE_DATA/sdedg/onlinelog/group_8.281.984232871 ONLINE 9 +SDE_DATA/sdedg/onlinelog/group_9.280.984232871 ONLINEGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873 ONLINE11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873 ONLINE12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873 ONLINE13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873 ONLINE14 +SDE_FRA/sdedg/onlinelog/group_14.276.984234319 STANDBY15 +SDE_FRA/sdedg/onlinelog/group_15.277.984234319 STANDBY16 +SDE_FRA/sdedg/onlinelog/group_16.270.984234321 STANDBY17 +SDE_FRA/sdedg/onlinelog/group_17.271.984234321 STANDBY18 +SDE_FRA/sdedg/onlinelog/group_18.278.984234321 STANDBY19 +SDE_FRA/sdedg/onlinelog/group_19.269.984234321 STANDBY20 +SDE_FRA/sdedg/onlinelog/group_20.267.984234323 STANDBYGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------21 +SDE_FRA/sdedg/onlinelog/group_21.264.984234323 STANDBY22 +SDE_FRA/sdedg/onlinelog/group_22.279.984234323 STANDBY23 +SDE_FRA/sdedg/onlinelog/group_23.280.984234323 STANDBY24 +SDE_FRA/sdedg/onlinelog/group_24.281.984234325 STANDBY25 +SDE_FRA/sdedg/onlinelog/group_25.282.984234325 STANDBY26 +SDE_FRA/sdedg/onlinelog/group_26.283.984234325 STANDBY27 +SDE_FRA/sdedg/onlinelog/group_27.284.984234325 STANDBY28 +SDE_FRA/sdedg/onlinelog/group_28.404.984234327 STANDBY 7 +data ONLINE 8 +data ONLINE 9 +data ONLINEGROUP# MEMBER TYPE---------- ------------------------------------------------------------ -------10 +data ONLINE11 +data ONLINE12 +data ONLINE13 +data ONLINE37 rows selected.SQL>
重新执行同步
SQL> recover managed standby database using current logfile disconnect;Media recovery complete.SQL> SQL> SQL> recover managed standby database cancel;Media recovery complete.SQL> SQL> SQL> alter database open ;alter database open*ERROR at line 1:ORA-10458: standby database requires recoveryORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '+SDE_DATA/sdedg/datafile/system.283.984230107'
日志组的问题已经解决，现在还需要恢复数据库，考虑主备库同步的问题，查看主备库参数是否正常
SQL> show parameter log_archive_configNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_config string SQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 string
发现主库的转换参数都没有设置，重新设置这两个参数
SQL> alter system set log_archive_config='dg_config=(sde,sdedg)';SQL> alter system set log_archive_dest_2='service=sde_new lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sdedg';
SQL> show parameter log_archive_NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_config string dg_config=(sde,sdedg)log_archive_dest stringlog_archive_dest_1 string location=use_db_recovery_file_ destlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 string service=sde_new lgwr async val id_for=(online_logfiles,primar y_role) db_unique_name=sdedg
参数设置正常了，备库重新开同步看能否正常
SQL> recover managed standby database using current logfile disconnect;Media recovery complete.
备库查看日志应用进度，在主库查看已经归档的sequence号
SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;Thread Last Seq Received Last Seq Applied---------- ----------------- ---------------- 1 143 143 2 138 138SQL> archive log list Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 138Next log sequence to archive 144Current log sequence 144
等同步完成，开启ADG
SQL> recover managed standby database cancel;Media recovery complete.SQL> SQL> alter database open ;Database altered.SQL> SQL> recover managed standby database using current logfile disconnect;Media recovery complete.SQL> SQL> set lines 1000SQL> select PROCESS,PID,STATUS, GROUP# , RESETLOG_ID,THREAD#,SEQUENCE# from v$managed_standby;PROCESS PID STATUS GROUP# RESETLOG_ID THREAD# SEQUENCE#--------- ---------- ------------ ---------------------------------------- ----------- ---------- ----------ARCH 22841 CONNECTED N/A 0 0 0ARCH 22843 CONNECTED N/A 0 0 0ARCH 22845 CLOSING 15 984123832 1 143ARCH 22847 CLOSING 22 984123832 2 138MRP0 3116 APPLYING_LOG N/A 984123832 2 139RFS 2722 IDLE N/A 0 0 0RFS 2892 IDLE N/A 0 0 0RFS 2894 IDLE 6 984123832 1 144RFS 2896 IDLE N/A 0 0 0RFS 2928 IDLE N/A 0 0 0RFS 2930 IDLE N/A 0 0 0PROCESS PID STATUS GROUP# RESETLOG_ID THREAD# SEQUENCE#--------- ---------- ------------ ---------------------------------------- ----------- ---------- ----------RFS 2932 IDLE N/A 0 0 0RFS 2948 IDLE 3 984123832 2 13913 rows selected.
现在DG同步正常了。
日志
同步
参数
文件
错误
应用
数据
数据库
过多
问题
时报
两个
所有主
手动
指示
脚本
进度
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
大理计算机网络技术培训学院
污水厂服务器
网页数据库库存在哪里
有关网络安全的军民共建
安徽网络技术比赛
软件开发经历的流程
我的世界国际版服务器生存推荐
连接谷歌服务器的vpn
敏实集团软件开发
FLASH广告设计软件开发
长沙诺特网络技术服务有限公司
数据库实现代码是什么
服务器分区如何看
医院网络安全保卫工作总结
淼鑫网络技术有限公司怎么样
网络安全有害程序应急预案
java将文件存到数据库
参观计算机网络技术实验室
天堂2 服务器端
常熟名优软件开发服务电话
java 读取数据库乱码
陕西省网络安全责任制实施办法
访问数据库有痕迹吗
mysql 数据库 授权
怎么降低数据库高水位线
投影仪怎么无线连接服务器
网络安全书籍推荐 2018
软件开发费是固定资产吗
浪潮服务器内存插槽多少个
常熟名优软件开发服务电话
