Question on NEXT_CHANGE#

From: Nassyam Basha <nassyambasha_at_gmail.com>
Date: Sun, 29 Dec 2013 22:04:23 +0530
Message-ID: <CAABGLu+QpuXKQSnpmF9KZoVtE3uhMgGe=OrV-vkjSgr1y_+osg_at_mail.gmail.com>

Hi Friends,

I have doubts about the next_change# column from v$archived_log.

This is my query and its corresponding output:

SQL> select sequence#,first_change#,first_time,next_change#,next_time from v$archived_log where sequence# >= 30 and dest_id=1;  SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

---------- ------------- -------------------- ------------
--------------------
        30       2432456 27-DEC-2013 17:23:46      2432612 27-DEC-2013
17:25:34


*        31       2432612 27-DEC-2013 17:25:34      2432676 27-DEC-2013

17:25:51*
        32       *2432676* *27-DEC-2013 17:25:51*      2435036 27-DEC-2013
18:20:12
        33       2435036 27-DEC-2013 18:20:12      2455760 27-DEC-2013

18:33:44

My attention is focused on the sequence 31 where the next_change# is
*2432676.*This next_change# refers to the first_change# of sequence 32

I then decided to use Log Miner in order to analyze these two sequences as shown below using their corresponding first_time and next_time information

 exec dbms_logmnr.start_logmnr(dictfilename => '/u02/backup/build_dict_31.ora',starttime => TO_DATE('27-DEC-2013 17:25:34','DD-MON-YYYY HH24:MI:SS'),endtime => TO_DATE('27-DEC-2013 17:25:51','DD-MON-YYYY HH24:MI:SS')); SQL> SELECT FILENAME AS name, LOW_TIME, LOW_SCN, HIGH_TIME,NEXT_SCN FROM V$LOGMNR_LOGS;

NAME                                               LOW_TIME
LOW_SCN HIGH_TIME              NEXT_SCN
-------------------------------------------------- --------------------
---------- -------------------- ----------
/u02/backup/arch_31/o1_mf_1_31_9cttp77l_.arc       27-DEC-2013 17:25:34

2432612 27-DEC-2013 17:25:51 2432676 As shown via the above query the next_scn (2432676) is in accordance with what the select against v$archived_log gave.

However, when I get the max (scn) from log miner,

SQL> select max(scn) from v$logmnr_contents;   MAX(SCN)

---

   2432672

I find a different SCN

'

I also used "strings" command over the archive log sequence and when i convert from Hex to Decimal, indeed it is same as 2432612 - 2432676 as expected.

>> Thread 0001, Seq# 0000000031, SCN 0x000000251e64-0x000000251ea4

Question 1) Why there is no information between scn 2432672[max(scn)] to 2432676[next_change#] from the archive log sequence 31 ?

 Question 2) Is there any column or any views to show the last_change# of sequence 31 instead of checking the next_change#? (any dedicated column in any x$ view if in case) ?

 Thanks in advance

-- 
Best Regards,
Nassyam Basha.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Dec 29 2013 - 17:34:23 CET