| How to get all the archive logs in a backup piece [message #358080] |
Sat, 08 November 2008 06:08  |
ganeshtambat
Messages: 1
Registered: November 2008
|
Junior Member |
|
|
Is there any way I can get all the archive log files present in a
backup piece. I tried following but it always gives me all the log
files for each piece:
****
SQL> select s.set_stamp, s.set_count, s.PIECES, s.backup_type,
s.controlfile_included from v$backup_set s, v$backup_piece p where
s.set_stamp=p.set_stamp and s.set_count=p.set_count and
p.handle='5mjv4do0_1_1';
SQL> select unique resetlogs_change# from v$backup_redolog where
set_stamp=670185216 and set_count=1206;
SQL> select unique name, TO_CHAR(FIRST_TIME, 'MMDDYYYYHH24MISS'),
TO_CHAR(NEXT_TIME, 'MMDDYYYYHH24MISS'), (BLOCKS*BLOCK_SIZE),
TO_CHAR(COMPLETION_TIME, 'MMDDYYYYHH24MISS') from v$archived_log where
resetlogs_change#=569317 AND name IS NOT NULL;
****
Thanks in advance,
Ganesh Tambat
|
|
|
|
|
|
| Re: How to get all the archive logs in a backup piece [message #519971 is a reply to message #358098] |
Thu, 18 August 2011 00:38  |
 |
swaorclser
Messages: 56
Registered: August 2011
|
Member |
|
|
select unique a.name,
TO_CHAR(a.FIRST_TIME, 'MMDDYYYYHH24MISS'),
TO_CHAR(a.NEXT_TIME, 'MMDDYYYYHH24MISS'),
(a.BLOCKS*a.BLOCK_SIZE),
TO_CHAR(a.COMPLETION_TIME, 'MMDDYYYYHH24MISS')
from
v$backup_piece p,
V$ARCHIVED_LOG a,
V$BACKUP_REDOLOG b
where
a.first_change#=b.first_change# and
a.SEQUENCE#=b.SEQUENCE# and
b.set_stamp=p.set_stamp
and b.set_count=p.set_count
Result of executing this statement mentioned in above post, returns an error
ORA-01405: fetched column value is NULL
Not sure where to use NVL so as to get rid of this error.
Thanks in advance for your guidance on this.
-SwaOrclSer
[update by JW: I've added code tags and some line breaks to your code to make it more readable, please do it yourself next time]
[Updated on: Thu, 18 August 2011 01:08] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get all the archive logs in a backup piece [message #520281 is a reply to message #520279] |
Sat, 20 August 2011 06:07 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You have a problem in the design of your SQL: you haven't followed the normalization correctly. You are joining v$backup_piece to v$backup_redolog, and you can't do that reliably, because there is no direct relationship between them. The relationship between these views is (or can be) many-to-many. Ie, one archive logfile can be divided up between many pieces, and one piece can contain many archive logfiles. You have to resolve this, by going through the v$backup_set view.
To summarise, one backup set can contain many files, and one backup set consists of many pieces. If you re-write the query to handle this, I think you'll get reliable results.
But I still wonder why you are doing this. Why do you need to know what files are in which backup set? (Given that you cannot reliably determine which files are in which piece, as explained.)
|
|
|
|
| Re: How to get all the archive logs in a backup piece [message #520373 is a reply to message #520281] |
Mon, 22 August 2011 04:28 |
|
swaorclser
Messages: 56
Registered: August 2011
|
Member |
|
|
Hello!
 Thanks for your reply.
Actually we need to know what all files are backed up, so that query. I will work on the query as per your suggestion.
But, I was wondering why the NEXT_TIME field comes empty for CURRENT redo in v$log?
Also I did not find its description in documentation.
If you could kindly explain me NEXT_TIME field, it would be great.
I googled a lot, I didn't find this column in v$log.
Also I came across following. On 11g R1
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 22 11:03:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ---------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
on 11g r2
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
Why is NEXT_TIME not present in 10g? Is it creating a problem of NULL value return?
Thanks & Regards,
swaorclser
[Updated on: Mon, 22 August 2011 04:36] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get all the archive logs in a backup piece [message #520394 is a reply to message #520389] |
Mon, 22 August 2011 05:20 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:Actually we need to know what all files are backed up, so that query.
Does this mean that you want to know which files have been backed up? If so, I don't see why you query v$backup_piece at all. Try these:
v$backup_archivelog_details
v$backup_datafile_details
v$backup_controlfile_details
v$backup_spfile_details
and forget about v$log, remember that you CAN'T backup online logs.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get all the archive logs in a backup piece [message #520413 is a reply to message #520404] |
Mon, 22 August 2011 06:25 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:we get backup pieces from oracle, so need the information as to what all is backed up in each piece. Well, you can try, but the information is meaningless because (as I said previously) there is no direct relaztionship between files and pieces. Good luck.
|
|
|
|
|
|
|
|
| Re: How to get all the archive logs in a backup piece [message #520429 is a reply to message #520428] |
Mon, 22 August 2011 09:27 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Look, if you don't understand how to navigate a relational model after all this, I really can't help. In the second note in this topic, ebrian gave the correct query, which joins files to sets to pieces. I do not understand why you changed it.
But it is still stupid. You never need to know what is in each piece, you might need to know what is in each set.
I can't contribute any more to this discussion. Goodbye and good luck.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
