oracleoracle11goracle10grman

Which SCN should i refer if there are two thread# in v$archive_log oracle

I am taking oracle backup using RMAN and saving current scn number i am getting scn number from below command

select max(next_change#) from v$archived_log where archived = 'YES' group by thread#;

It gives outut as below 

MAX(NEXT_CHANGE#)
-----------------
          3911392
          3903950

i found from oracle documentation is , If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name.

Que 1)Which SCN should i refer while restore in SET Until SCN commnd

Que 2)There is one more command for getting SCN as below

select current_scn from v$database;

its output is 

CURRENT_SCN
-----------
    3914145

Whats the difference between these two commands output SCN?

Que 3) I have a RAC setup which has two oracle machine, Does those two thread# has something to do with this?

Solution

  • Let's start with the fact that Oracle has only one system SCN (it does not matter how many nodes you have in your RAC). Each transactions has it's own SCN that is why you have different SCNs on different threads (this is obvious as each thread manage it's own transactions). Now in reply to you questions:

    1. This question is a little strange because you should refer the SCN you need, if you want "point in time" restore from 3 days ago you refer the SCN from 3 days ago. If you want to restore/recover your DB up to last commited transaction then you do not have to refer to any SCN at all.
    2. Yes - "select dbms_flashback.get_system_change_number from dual" and maybe some other method exists. The difference between select max(next_change#) from v$archived_log where archived = 'YES' group by thread#; and select current_scn from v$database; is that in first case you get max(SCN) which was archived (into archivelog) the second is current DB SCN which always will be greater than first SELECT.
    3. Yes, each thread manage its own transaction (and implicit SCNs)

    In general you should refer SCN in your restore/recovery scenario only when you want "point in time" recovery. select max(next_change#) from v$archived_log where archived = 'YES' group by thread#; does not means you latest, system wide SCN this means MAX archived SCN (take into consideration that you also have a lot of SCNs in your current online redo logs). Also think about if your DB were in NO_ARCHIVE mode - in this case that select will return nothing...