** An interesting question **

[sudip]

Hi All,
May be this is relevant since we are talking abt recoverability,
In Oracle9i, while recovering Oracle reads the Logs twice
Firtst time it decides which all relevant data it needs to recover and in the second pass it actually applies those changes.
Thought just t share these with U guys

Regards
Sudip Sarkar

[prakashs43]

Hi ,
i guess oracle has a unique way of identifying each transaction in the archive logs ( each transaction may be uniquely identified by sequence# , switch_change# ) . while recovering oracle does move archive logs contents in redolog buffer in sequence ( all the transactions , whether they are committed or not ) then in redo log buffer, flag is set, if commit is present for a particular transaction id it is further moved to SGA or datafiles . if not they are ignore in redo log buffer stage . this loop continues .

SCN is present only for commited transactions . i don't think if there is any number for uncommitted transactions ?

i guess single archival log can have without any SCN Number or a single log can have more than 1 SCN .

if anybody can post sample archival logs contents from logminer could be useful, to trace exactly what values are stored in the archival logs .

[pando]

everything must have a scn otherwise I dont know how Oracle can recover uncommitted transactions in an instance recovery?

[quester]

I am confused a bit with this piece of explaination I read in one of the Oracle manuals: It reads -

"Redo Logs contain both committed and uncommitted transactions. In 8i, distinguishing the committeds from the uncommitteds was a bit tricky, because the only thing tying a SQL Statement to its eventual commit was that both rows in v$logmnr_contents had the same Serial Number. You were reduced, therefore, to issuing this sort of query:

select serial#, sql_redo from v$logmnr_contents where operation='UPDATE' and serial# in (select serial# from v$logmnr_contents where operation='COMMIT');

Version 2 Oracle 9i New Features for Administrators
Copyright ©Howard Rogers 2002 5/03/2002 Page 23 of 115

Now 9i natively includes a new option for the start_logmnr procedure,
COMMITTED_DATA_ONLY, which groups transactions together by their serial number, and excludes any which don.t have a .committed. identifier. Note that this could be misleading: it.s possible to issue an Update statement whilst we.re in Log 103, and a commit once we.ve switched to Log 104. If you only analyse Log 103 with this new option, that update will not be listed, because its matching
commit is not included within the analysed log.
Note, too, that this does not cure the problem of how rolled back transactions look. A rollback is as final as a commit (you can.t roll forward after issuing that command, after all!) and hence a statement issued in Log 103 and rolled back in Log 103 will still be listed, even with the COMMITTED_DATA_ONLY option set.








Can someone please explain me this, as this is again in a way related to this topic in the eye of the Log Miner.

[pando]

since you dont say what you dont understand I cant help much

in Oracle 9i you can choose to see comitted data only, in 8i not