RE: Question on NEXT_CHANGE#

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 29 Dec 2013 22:54:52 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DD371D_at_exmbx05.thus.corp>

1. You could try dumping the log file (which could result in a rather large trace file) to see what you can find in the last few redo records in the file. I'd guess that there are some system change records that aren't displayed in the log miner views - e.g. setting bits in object bitmaps or file bitmaps, records related to index leaf block splits,
2. I'd be a little surprised if there were.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Nassyam Basha [nassyambasha_at_gmail.com] Sent: 29 December 2013 16:34
To: ORACLE-L
Subject: Question on NEXT_CHANGE#

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 - 23:54:52 CET