| Archive log Sequence number not in order [message #656415] |
Wed, 05 October 2016 10:21  |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
My database is oracle12c. The archive log numbers are in sequence. Any one can explain why it is not in the order?
The below screenshot is from standby database. The seqeunce# 861 is coming after 1154. I am not sure why.
Quote:
SQL> select dest_id,sequence#,applied,to_char(completion_time,'dd-mon-yy:hh24:mi:ss'),deleted
from v$archived_log where completion_time > trunc(sysdate-5) order by completion_time;
DEST_ID SEQUENCE# APPLIED TO_CHAR(COMPLETION DEL
---------- ---------- --------- ------------------ ---
1 1135 YES 29-sep-16:01:34:37 YES
1 849 YES 29-sep-16:01:34:40 YES
1 850 YES 29-sep-16:01:34:42 YES
1 1136 YES 29-sep-16:01:34:43 YES
1 1137 YES 29-sep-16:01:34:56 YES
1 1138 YES 30-sep-16:01:35:08 YES
1 852 YES 30-sep-16:01:35:08 YES
1 851 YES 30-sep-16:01:35:08 YES
1 1139 YES 30-sep-16:01:35:16 YES
1 1140 YES 30-sep-16:01:35:17 YES
1 1142 YES 01-oct-16:01:36:53 YES
1 854 YES 01-oct-16:01:36:53 YES
1 1141 YES 01-oct-16:01:36:54 YES
1 1143 YES 01-oct-16:01:36:56 YES
1 853 YES 01-oct-16:01:36:56 YES
1 1144 YES 02-oct-16:01:33:40 YES
1 1145 YES 02-oct-16:01:33:40 YES
1 855 YES 02-oct-16:01:33:41 YES
1 856 YES 02-oct-16:01:33:41 YES
1 1146 YES 02-oct-16:01:33:41 YES
1 1148 YES 03-oct-16:01:33:38 YES
1 1147 YES 03-oct-16:01:33:39 YES
1 858 YES 03-oct-16:01:33:40 NO
1 857 YES 03-oct-16:01:33:40 YES
1 1149 YES 03-oct-16:01:33:40 YES
1 1151 YES 04-oct-16:01:33:35 NO
1 1150 YES 04-oct-16:01:33:35 NO
1 859 YES 04-oct-16:01:33:37 NO
1 860 YES 04-oct-16:01:33:37 NO
1 1152 YES 04-oct-16:01:33:38 NO
1 1153 YES 04-oct-16:10:25:01 NO
1 1154 YES 04-oct-16:10:25:04 NO
1 861 IN-MEMORY 04-oct-16:10:25:06 NO
33 rows selected.
SQL>
|
|
|
|
|
|
|
|
|
|
| Re: Archive log Sequence number not in order [message #656421 is a reply to message #656418] |
Wed, 05 October 2016 10:58  |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Thanks John. Now i understand why after listing the thread#.
Thread 1 and Thread 2 are in two difference sequence. Sequence# 1154 comes from thread 1.
The seq# 864 comes from thread 2.
In nutshell, The sequence# are not in sequence in physical standby if Primary is
RAC.
|
|
|
|
| Re: Archive log Sequence number not in order [message #656422 is a reply to message #656421] |
Wed, 05 October 2016 11:01 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
SQL> select thread#,sequence#,applied,to_char(completion_time,'dd-mon-yy:hh24:mi :ss'),deleted
from v$archived_log where completion_time > trunc(sysdate-5) order by completion _time;
2
THREAD# SEQUENCE# APPLIED TO_CHAR(COMPLETION DEL
---------- ---------- --------- ------------------ ---
1 1138 YES 30-sep-16:01:35:08 YES
2 852 YES 30-sep-16:01:35:08 YES
2 851 YES 30-sep-16:01:35:08 YES
1 1139 YES 30-sep-16:01:35:16 YES
1 1140 YES 30-sep-16:01:35:17 YES
1 1142 YES 01-oct-16:01:36:53 YES
2 854 YES 01-oct-16:01:36:53 YES
1 1141 YES 01-oct-16:01:36:54 YES
1 1143 YES 01-oct-16:01:36:56 YES
2 853 YES 01-oct-16:01:36:56 YES
1 1144 YES 02-oct-16:01:33:40 YES
1 1145 YES 02-oct-16:01:33:40 YES
2 856 YES 02-oct-16:01:33:41 YES
2 855 YES 02-oct-16:01:33:41 YES
1 1146 YES 02-oct-16:01:33:41 YES
1 1148 YES 03-oct-16:01:33:38 YES
1 1147 YES 03-oct-16:01:33:39 YES
2 858 YES 03-oct-16:01:33:40 NO
2 857 YES 03-oct-16:01:33:40 YES
1 1149 YES 03-oct-16:01:33:40 YES
1 1151 YES 04-oct-16:01:33:35 NO
1 1150 YES 04-oct-16:01:33:35 NO
2 859 YES 04-oct-16:01:33:37 NO
2 860 YES 04-oct-16:01:33:37 NO
1 1152 YES 04-oct-16:01:33:38 NO
1 1153 YES 04-oct-16:10:25:01 NO
1 1154 YES 04-oct-16:10:25:04 NO
2 861 YES 04-oct-16:10:25:06 NO
1 1155 YES 04-oct-16:17:35:04 NO
1 1156 YES 04-oct-16:17:35:36 NO
1 1157 YES 04-oct-16:17:35:45 NO
1 1158 YES 04-oct-16:17:35:46 NO
1 1159 YES 04-oct-16:17:35:46 NO
2 862 YES 04-oct-16:17:35:48 NO
1 1160 YES 05-oct-16:01:32:46 NO
1 1161 YES 05-oct-16:01:32:46 NO
2 863 YES 05-oct-16:01:32:47 NO
1 1162 YES 05-oct-16:01:32:51 NO
2 864 IN-MEMORY 05-oct-16:01:32:52 NO
39 rows selected.
SQL>
|
|
|
|
|
|
|
|
| Re: Archive log Sequence number not in order [message #656425 is a reply to message #656424] |
Wed, 05 October 2016 11:17 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Here is the revised query.
SQL> select thread#,sequence#,applied,to_char(completion_time,'dd-mon-yy:hh24:mi:ss'),deleted,first_change#
from v$archived_log where completion_time > trunc(sysdate-5) order by thread#,first_change#
;
2 3
THREAD# SEQUENCE# APPLIED TO_CHAR(COMPLETION DEL FIRST_CHANGE#
---------- ---------- --------- ------------------ --- -------------------
1 1138 YES 30-sep-16:01:35:08 YES 22378484880
1 1139 YES 30-sep-16:01:35:16 YES 22378995264
1 1140 YES 30-sep-16:01:35:17 YES 22378995282
1 1141 YES 01-oct-16:01:36:54 YES 22378995290
1 1142 YES 01-oct-16:01:36:53 YES 22379740177
1 1143 YES 01-oct-16:01:36:56 YES 22379740188
1 1144 YES 02-oct-16:01:33:40 YES 22379740213
1 1145 YES 02-oct-16:01:33:40 YES 22380289828
1 1146 YES 02-oct-16:01:33:41 YES 22380289843
1 1147 YES 03-oct-16:01:33:39 YES 22380289881
1 1148 YES 03-oct-16:01:33:38 YES 22380680400
1 1149 YES 03-oct-16:01:33:40 YES 22380680414
1 1150 YES 04-oct-16:01:33:35 NO 22380680424
1 1151 YES 04-oct-16:01:33:35 NO 22381290807
1 1152 YES 04-oct-16:01:33:38 NO 22381290818
1 1153 YES 04-oct-16:10:25:01 NO 22381290849
1 1154 YES 04-oct-16:10:25:04 NO 22381420910
1 1155 YES 04-oct-16:17:35:04 NO 22381420934
1 1156 YES 04-oct-16:17:35:36 NO 22381636243
1 1157 YES 04-oct-16:17:35:45 NO 22381636417
1 1158 YES 04-oct-16:17:35:46 NO 22381636446
1 1159 YES 04-oct-16:17:35:46 NO 22381636456
1 1160 YES 05-oct-16:01:32:46 NO 22381636461
1 1161 YES 05-oct-16:01:32:46 NO 22381798918
1 1162 YES 05-oct-16:01:32:51 NO 22381798929
2 851 YES 30-sep-16:01:35:08 YES 22378484875
2 852 YES 30-sep-16:01:35:08 YES 22378995273
2 853 YES 01-oct-16:01:36:56 YES 22378995297
2 854 YES 01-oct-16:01:36:53 YES 22379740193
2 855 YES 02-oct-16:01:33:41 YES 22379740216
2 856 YES 02-oct-16:01:33:41 YES 22380289846
2 857 YES 03-oct-16:01:33:40 YES 22380289870
2 858 YES 03-oct-16:01:33:40 NO 22380680409
2 859 YES 04-oct-16:01:33:37 NO 22380680431
2 860 YES 04-oct-16:01:33:37 NO 22381290827
2 861 YES 04-oct-16:10:25:06 NO 22381290842
2 862 YES 04-oct-16:17:35:48 NO 22381420939
2 863 YES 05-oct-16:01:32:47 NO 22381636466
2 864 IN-MEMORY 05-oct-16:01:32:52 NO 22381798934
39 rows selected.
SQL> SQL>
|
|
|
|
| Re: Archive log Sequence number not in order [message #656426 is a reply to message #656425] |
Wed, 05 October 2016 11:19 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
I am using the below query to delete the archive log files. But it works
in non RAC database. Just realized, this does not work for RAC system
due to incorrect seqeunce# order due to multi thread.
select
case
when
(select database_role from v$database)='PRIMARY' then
(select min(seq#)
from (
select
nvl(max(sequence#)-100,1) seq#
from
v$archived_log
where
backup_count > 0
and standby_dest <> 'YES'
and resetlogs_change# = (select max(resetlogs_change#) from v$archived_log)
UNION ALL
select
nvl(min(sequence#)-100,1) seq#
from
v$archived_log l, V$ARCHIVE_DEST_STATUS s
where l.applied <> 'YES' and l.standby_dest = 'YES'
and s.dest_id = l.dest_id
and s.status = 'VALID'
and l.resetlogs_change# = (select max(resetlogs_change#) from v$archived_log)
)
)
else
(select
nvl(max(sequence#)-80,1)
from
v$archived_log
where
applied = 'YES'
and resetlogs_change# = (select max(resetlogs_change#) from v$archived_log))
end seq#
from dual;
delete noprompt archivelog until sequence $num;
$num will be out put of the above query.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
