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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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> 

1
2

SQL> recover managed standby database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[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

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

1
2
3
4
5
*** 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 

1
2
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';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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

1
2
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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.