Home » RDBMS Server » Backup & Recovery » How to get all the archive logs in a backup piece
How to get all the archive logs in a backup piece [message #358080] Sat, 08 November 2008 06:08 Go to next message
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 #358098 is a reply to message #358080] Sat, 08 November 2008 09:38 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The following should work:

select a.name
from 
V$ARCHIVED_LOG a,
V$BACKUP_REDOLOG b,
V$BACKUP_SET c,
V$BACKUP_PIECE d
where d.handle like '%<backuppiece_name>%' and
a.first_change# = b.first_change# and
b.set_stamp = c.set_stamp and
c.set_stamp = d.set_stamp;
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 Go to previous messageGo to next message
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 #520197 is a reply to message #519971] Fri, 19 August 2011 04:34 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
When ran this query, got the following output.
--------------------------------------------------------------------------------
TO_CHAR(A.FIRS TO_CHAR(A.NEXT (A.BLOCKS*A.BLOCK_SIZE) TO_CHAR(A.COMP
-------------- -------------- ----------------------- --------------
/u01/app/oracle/oradata/orcl/redo01.log
08192011111511 08192011111609                    5120 08192011112125

/u01/app/oracle/oradata/orcl/redo02.log
08192011111609                                3452928 08192011112125

/u01/app/oracle/oradata/orcl/redo03.log
08192011110820 08192011111511                 2494464 08192011112125


Not able to understand as to why redo logs are displayed when we are querying v$archived_log view? For redo02, next time value is not displayed and that is why an error
Quote:
Fetched Column value is NULL.


I would appreciate any help on this.

Regards.
Re: How to get all the archive logs in a backup piece [message #520201 is a reply to message #520197] Fri, 19 August 2011 04:41 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Sad

Is anybody there?

Please help!!
Re: How to get all the archive logs in a backup piece [message #520203 is a reply to message #520201] Fri, 19 August 2011 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you wait more than 7 minutes?

Regards
Michel
Re: How to get all the archive logs in a backup piece [message #520207 is a reply to message #520203] Fri, 19 August 2011 05:30 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member

I have been waiting from yesterday before my last post! So just don't count 7 min.
Anyways, if it troubled so much then I am extremely sorry. Just wanted answers as this is an urgent thing for me.

regards,
swaorclser
Re: How to get all the archive logs in a backup piece [message #520215 is a reply to message #520207] Fri, 19 August 2011 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to wait for someone that got the same problem.
Note that your yesterday post didn't contain sufficient information, this is why I didn't count it.

Regards
Michel
Re: How to get all the archive logs in a backup piece [message #520246 is a reply to message #520207] Fri, 19 August 2011 10:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you explain what you actually want to do? Does the title of this topic (from 3 years ago!) describe what you want? If so, why? I can't see any value to this information, so I hesitate to come up with a suggestion.
Re: How to get all the archive logs in a backup piece [message #520279 is a reply to message #520246] Sat, 20 August 2011 04:40 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Thanks for your reply! Smile

I am using the same query in my application, where we query the oracle to give me all the archive logs which are there in the piece handle mentioned in the query itself.
It was working very fine. Suddenly, it failed with
ORA-01405: fetched column value is NULL


When looked at the output, came to know that it returned NULL for column a.NEXT_TIME for /u01/app/oracle/oradata/orcl/redo02.log.

Also when I ran
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 YES
INACTIVE               1062326 19-AUG-11      1065345 19-AUG-11

         2          1          2   52428800        512          1 YES
INACTIVE               1065345 19-AUG-11      1065397 19-AUG-11

         3          1          3   52428800        512          1 NO
CURRENT                1065397 19-AUG-11   2.8147E+14



It shows that current redo does not have next_time value.
So I think either I should use NVL or I should not get redo logs in above(very 1st) query's output.
Also please tell me what is NEXT_TIME as applied to redo logs?

I am a newcomer in this field and on this forun too.
I would appreciate your help.

Thanks & Regards,
Swaorclser
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Hello!
Smile 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 #520378 is a reply to message #520373] Mon, 22 August 2011 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why something exists in a newer version and not in an older one?
Isn't this a silly question?

Regards
Michel
Re: How to get all the archive logs in a backup piece [message #520381 is a reply to message #520378] Mon, 22 August 2011 04:49 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Yes it may be a silly question.
Newcomer can have any question which can look silly to others who are more experienced.

Anything additional means enhancement if its not there in older.
I do understand that.
Wanted to know as to why this field is visible in 11g r2 still its online documentation doesn't show it.

Request you not to discourage people from asking the questions, please.

Thanks & Regards,
Swaorclser
Re: How to get all the archive logs in a backup piece [message #520386 is a reply to message #520381] Mon, 22 August 2011 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Wanted to know as to why this field is visible in 11g r2 still its online documentation doesn't show it.

Documentation bug.

There are some questions that should not be asked even for a beginner.
Even a child knows that the newer version of his toy is better, offers more stuff than his older one.

Regards
Michel
Re: How to get all the archive logs in a backup piece [message #520389 is a reply to message #520386] Mon, 22 August 2011 05:02 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
THANK YOU very much for your comments!
Re: How to get all the archive logs in a backup piece [message #520391 is a reply to message #520389] Mon, 22 August 2011 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are welcome.
If they can improve yourself, I'm quite happy.

Regards
Michel
Re: How to get all the archive logs in a backup piece [message #520393 is a reply to message #520391] Mon, 22 August 2011 05:18 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Fine!
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #520404 is a reply to message #520394] Mon, 22 August 2011 05:41 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Thank you John!

Actually after backup, we get backup pieces from oracle, so need the information as to what all is backed up in each piece.

I will read the views mentioned by you!

Also if you could tell me the use of NEXT_TIME in context of redos?

Thanks in advance!

Regards,
Swaorclser
Re: How to get all the archive logs in a backup piece [message #520406 is a reply to message #520404] Mon, 22 August 2011 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the first_time of the next used redo.

Regards
Michel

[Updated on: Mon, 22 August 2011 05:50]

Report message to a moderator

Re: How to get all the archive logs in a backup piece [message #520408 is a reply to message #520406] Mon, 22 August 2011 06:08 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Thank you Michel very much!

I am not using v$log.
Just wanted to know what all information can we get regarding redo.

As next_time is first_time of next redo, for CURRENT redo (active, in use) it does not have next_change# and next_time set?

As per my understanding, these fields must be filled in just before log switching happens. Is this understanding correct?

Thanks in advance!

regards,
SwaOrclSer
Re: How to get all the archive logs in a backup piece [message #520412 is a reply to message #520408] Mon, 22 August 2011 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As next_time is first_time of next redo, for CURRENT redo (active, in use) it does not have next_change# and next_time set?

Time is not set, change is set to a dummy very large value.

Quote:
As per my understanding, these fields must be filled in just before log switching happens. Is this understanding correct?

Yes, during the log switch event handling.

Regards
Michel
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #520419 is a reply to message #520413] Mon, 22 August 2011 07:46 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
I completely agree with you,
and I am reading views and trying to figure out how they can be used to serve our purpose.

It all started with
Quote:

Fetched column value is NULL.

error.

So this reverse engineering. Thanks for your inputs.

So now to summarize,

***1. query needs to be worked upon.

2. As in original query, we have not checked for 'ARCHIVED' flag, it also returns the redo ( current).
Quote:
(ARCHIVED : Indicates whether the online redo log was archived (YES) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (NO). )
]

3.Current redo log doesn't have next_time as it gets filled in the switch log event handling.So, somehow that comes NULL, and gets us this error.

***4. Rewrite the query!! Smile

Thank you all for your guidance!!

Regards,
swaorclser
Re: How to get all the archive logs in a backup piece [message #520428 is a reply to message #520281] Mon, 22 August 2011 09:03 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Hello John!

As per your post, you say that there is many to many relationship between v$backup_redolog b and v$backup_piece p. Agreed!

But, in query we are making sure that they have same set_count
by checking the condition
b.set_count = p.set_count


One piece belongs to one backupset. Thus we are sure that we get logs in piece p (backed up b )which are part of same backupset.

Please share your views on this.
I am trying to get as much as I can.


Thanks & regards,
Swaorclser
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: How to get all the archive logs in a backup piece [message #520475 is a reply to message #520429] Tue, 23 August 2011 00:35 Go to previous message
swaorclser
Messages: 56
Registered: August 2011
Member
Thanks a lot for your time and inputs.
Previous Topic: restore archivelog
Next Topic: Is non full (lesser than size of the log_buffer) redo is resumed after forceful log switch?
Goto Forum:
  


Current Time: Thu Mar 28 10:10:55 CDT 2024