| Find max seq# in v$archived_log [message #477393] |
Thu, 30 September 2010 08:17  |
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 #477410 is a reply to message #477393] |
Thu, 30 September 2010 08:52  |
 |
Michel Cadot
Messages: 68641
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 |
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 #477422 is a reply to message #477419] |
Thu, 30 September 2010 09:50 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
