备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458
短信预约 -IT技能 免费直播动态提醒
备库DUPLICATE后启用同步，开启ADG打开数据库时报错：ORA-10458
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> 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 recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-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 984123832
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------
Wed Aug 15 14:47:10 2018
Recovery interrupted!
Media Recovery failed with error 448
Errors in file /u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
Slave 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.634
Media Recovery add redo thread 2
*** 2018-08-15 14:34:10.721 4320 krsh.c
Media Recovery Waiting for thread 1 sequence 129
Redo 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 ONLINE
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873 ONLINE
11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873 ONLINE
12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873 ONLINE
13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873 ONLINE
14 +SDE_DATA/sdedg/onlinelog/group_14.284.984230143 STANDBY
15 +SDE_DATA/sdedg/onlinelog/group_15.284.984230145 STANDBY
16 +SDE_DATA/sdedg/onlinelog/group_16.284.984230147 STANDBY
17 +SDE_DATA/sdedg/onlinelog/group_17.284.984230149 STANDBY
18 +SDE_DATA/sdedg/onlinelog/group_18.284.984230151 STANDBY
19 +SDE_DATA/sdedg/onlinelog/group_19.284.984230153 STANDBY
20 +SDE_DATA/sdedg/onlinelog/group_20.284.984230155 STANDBY
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
21 +SDE_DATA/sdedg/onlinelog/group_21.284.984230157 STANDBY
22 +SDE_DATA/sdedg/onlinelog/group_22.284.984230159 STANDBY
23 +SDE_DATA/sdedg/onlinelog/group_23.284.984230161 STANDBY
24 +SDE_DATA/sdedg/onlinelog/group_24.284.984230163 STANDBY
25 +SDE_DATA/sdedg/onlinelog/group_25.284.984230165 STANDBY
26 +SDE_DATA/sdedg/onlinelog/group_26.284.984230167 STANDBY
27 +SDE_DATA/sdedg/onlinelog/group_27.284.984230169 STANDBY
28 +SDE_DATA/sdedg/onlinelog/group_28.284.984230171 STANDBY
7 +data ONLINE
8 +data ONLINE
9 +data ONLINE
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
10 +data ONLINE
11 +data ONLINE
12 +data ONLINE
13 +data ONLINE
14 +data STANDBY
15 +data STANDBY
16 +data STANDBY
17 +data STANDBY
18 +data STANDBY
19 +data STANDBY
20 +data STANDBY
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
21 +data STANDBY
22 +data STANDBY
23 +data STANDBY
24 +data STANDBY
25 +data STANDBY
26 +data STANDBY
27 +data STANDBY
28 +data STANDBY
52 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 ONLINE
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873 ONLINE
11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873 ONLINE
12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873 ONLINE
13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873 ONLINE
7 +data ONLINE
8 +data ONLINE
9 +data ONLINE
10 +data ONLINE
11 +data ONLINE
12 +data ONLINE
13 +data ONLINE
22 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 ONLINE
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873 ONLINE
11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873 ONLINE
12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873 ONLINE
13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873 ONLINE
14 +SDE_FRA/sdedg/onlinelog/group_14.276.984234319 STANDBY
15 +SDE_FRA/sdedg/onlinelog/group_15.277.984234319 STANDBY
16 +SDE_FRA/sdedg/onlinelog/group_16.270.984234321 STANDBY
17 +SDE_FRA/sdedg/onlinelog/group_17.271.984234321 STANDBY
18 +SDE_FRA/sdedg/onlinelog/group_18.278.984234321 STANDBY
19 +SDE_FRA/sdedg/onlinelog/group_19.269.984234321 STANDBY
20 +SDE_FRA/sdedg/onlinelog/group_20.267.984234323 STANDBY
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
21 +SDE_FRA/sdedg/onlinelog/group_21.264.984234323 STANDBY
22 +SDE_FRA/sdedg/onlinelog/group_22.279.984234323 STANDBY
23 +SDE_FRA/sdedg/onlinelog/group_23.280.984234323 STANDBY
24 +SDE_FRA/sdedg/onlinelog/group_24.281.984234325 STANDBY
25 +SDE_FRA/sdedg/onlinelog/group_25.282.984234325 STANDBY
26 +SDE_FRA/sdedg/onlinelog/group_26.283.984234325 STANDBY
27 +SDE_FRA/sdedg/onlinelog/group_27.284.984234325 STANDBY
28 +SDE_FRA/sdedg/onlinelog/group_28.404.984234327 STANDBY
7 +data ONLINE
8 +data ONLINE
9 +data ONLINE
GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
10 +data ONLINE
11 +data ONLINE
12 +data ONLINE
13 +data ONLINE
37 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 recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+SDE_DATA/sdedg/datafile/system.283.984230107'
日志组的问题已经解决，现在还需要恢复数据库，考虑主备库同步的问题，查看主备库参数是否正常
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_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 string
log_archive_dest_1 string location=use_db_recovery_file_
dest
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_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 138
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 138
Next log sequence to archive 144
Current 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 1000
SQL> 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 0
ARCH 22843 CONNECTED N/A 0 0 0
ARCH 22845 CLOSING 15 984123832 1 143
ARCH 22847 CLOSING 22 984123832 2 138
MRP0 3116 APPLYING_LOG N/A 984123832 2 139
RFS 2722 IDLE N/A 0 0 0
RFS 2892 IDLE N/A 0 0 0
RFS 2894 IDLE 6 984123832 1 144
RFS 2896 IDLE N/A 0 0 0
RFS 2928 IDLE N/A 0 0 0
RFS 2930 IDLE N/A 0 0 0
PROCESS PID STATUS GROUP# RESETLOG_ID THREAD# SEQUENCE#
--------- ---------- ------------ ---------------------------------------- ----------- ---------- ----------
RFS 2932 IDLE N/A 0 0 0
RFS 2948 IDLE 3 984123832 2 139
13 rows selected.
现在DG同步正常了。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/56.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
