Home » RDBMS Server » Backup & Recovery » Find max seq# in v$archived_log (9i to 11g)
Find max seq# in v$archived_log [message #477393] Thu, 30 September 2010 08:17 Go to next message
edgefree
Messages: 30
Registered: July 2010
Location: Toronto
Member
Hi Gurus,

I have a question about finding sequence number in archived log. I just want to do minimum backup of archived log, which means if the archived logs are backed and

requests:
1. Find the min seq# number which is *not* deleted. Or max seq# which is deleted.
2. Need to consider incarnations. That means if previous incarnation's archived logs are not deleted, I want to back it up.
3. Need to cover 9i to 11g

I have a draft, but it can not meet my request in some cases.

select max(sequence#) from v$archived_log where deleted='YES';

Could you give any suggestions?

Thank you all in advance.

Edgy
Re: Find max seq# in v$archived_log [message #477400 is a reply to message #477393] Thu, 30 September 2010 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided Test Case - http://www.orafaq.com/wiki/Test_case
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Find max seq# in v$archived_log [message #477410 is a reply to message #477393] Thu, 30 September 2010 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
select max(decode(deleted,'YES',sequence#)) max_deleted,
       min(decode(deleted,'NO',sequence#)) min_not_deleted
from v$archived_log
/


2/
select resetlogs_change#, 
       count(decode(deleted,'YES',sequence#)) deleted,
       count(decode(deleted,'NO',sequence#)) not_deleted
from v$archived_log
group by resetlogs_change#
/


Regards
Michel
Re: Find max seq# in v$archived_log [message #477415 is a reply to message #477400] Thu, 30 September 2010 09:02 Go to previous messageGo to next message
edgefree
Messages: 30
Registered: July 2010
Location: Toronto
Member
Thank you for reply. But I think I followed the guideline, such as be polite, etc. Anything wrong, please point it out directly, thanks.

My case just because I have no idea how to cover all scenarios. I knew how to get the seq# as my draft version. But it can not meet my requests since I need to consider different incarnation. And also, v$archived_log can not hold all since MAXLOGHISTORY parameter limitation. How to deal with it?
Re: Find max seq# in v$archived_log [message #477419 is a reply to message #477415] Thu, 30 September 2010 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to deal with it?

You can't, what is gone is gone.
And I think my second query indicates how to deal with your second query.
If not, precise what you want.

Regards
Michel
Re: Find max seq# in v$archived_log [message #477422 is a reply to message #477419] Thu, 30 September 2010 09:50 Go to previous messageGo to next message
edgefree
Messages: 30
Registered: July 2010
Location: Toronto
Member
Thank you Michel Cadot!

But for the 1st one, it does not consider incarnations. So if
1). incarnation 1 has seq#=300 which is deleted
2). incarnation 2 has seq#=3 which is deleted
Then, the max seq# will be 300. But, usually, I might more pay attention on seq#3 archived log since it is current incarnation.
Re: Find max seq# in v$archived_log [message #477432 is a reply to message #477422] Thu, 30 September 2010 11:26 Go to previous message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So merge methods used in queries 1/ and 2/ to get your result.

Regards
Michel

[Updated on: Thu, 30 September 2010 11:26]

Report message to a moderator

Previous Topic: reinstall oracle server using existing d:\oracle\product,,
Next Topic: Difference in Archivelog ENABLE/DISABLE in RMAN backup.
Goto Forum:
  


Current Time: Thu Oct 30 23:55:56 CDT 2014

Total time taken to generate the page: 0.13654 seconds