Home » RDBMS Server » Backup & Recovery » Archive log Sequence number not in order (Oracle12c)
Archive log Sequence number not in order [message #656415] Wed, 05 October 2016 10:21 Go to next message
shrinika
Messages: 283
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 #656416 is a reply to message #656415] Wed, 05 October 2016 10:22 Go to previous messageGo to next message
shrinika
Messages: 283
Registered: April 2008
Senior Member
My Primary database is RAC two node. The standby database is non RAC database.
Re: Archive log Sequence number not in order [message #656418 is a reply to message #656416] Wed, 05 October 2016 10:32 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
If you project the THREAD# column as well, it should become clear.
Re: Archive log Sequence number not in order [message #656420 is a reply to message #656418] Wed, 05 October 2016 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And if you use code tags if you would be even clearer.

Re: Archive log Sequence number not in order [message #656421 is a reply to message #656418] Wed, 05 October 2016 10:58 Go to previous messageGo to next message
shrinika
Messages: 283
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 Go to previous messageGo to next message
shrinika
Messages: 283
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 #656423 is a reply to message #656422] Wed, 05 October 2016 11:05 Go to previous messageGo to next message
shrinika
Messages: 283
Registered: April 2008
Senior Member
Michael - Sorry for not using code tag. Smile

Re: Archive log Sequence number not in order [message #656424 is a reply to message #656422] Wed, 05 October 2016 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

order by thread#, FIRST_CHANGE#

Re: Archive log Sequence number not in order [message #656425 is a reply to message #656424] Wed, 05 October 2016 11:17 Go to previous messageGo to next message
shrinika
Messages: 283
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 Go to previous messageGo to next message
shrinika
Messages: 283
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.
Re: Archive log Sequence number not in order [message #656427 is a reply to message #656426] Wed, 05 October 2016 11:21 Go to previous messageGo to next message
shrinika
Messages: 283
Registered: April 2008
Senior Member
It would be great if you have any better recommendation or suggestion on the query.
Re: Archive log Sequence number not in order [message #656429 is a reply to message #656427] Wed, 05 October 2016 11:32 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
This looks like a complicated way to do something that is already automated. All you need do is

CONFIGURE ARCHIVELOG DELETION POLICY TO whatever_is_appropriate_for_you;
DELETE ARCHIVELOG ALL;

Re: Archive log Sequence number not in order [message #656431 is a reply to message #656429] Wed, 05 October 2016 11:41 Go to previous message
shrinika
Messages: 283
Registered: April 2008
Senior Member
Thank you John!
Previous Topic: RMAN Restore failed
Next Topic: How i can startup database without undo tablespace, without backup and noarchivlog
Goto Forum:
  


Current Time: Thu Nov 23 20:01:49 CST 2017

Total time taken to generate the page: 0.02181 seconds