OraFAQ Forum: Backup & Recovery » querying v$archived

Home » RDBMS Server » Backup & Recovery » querying v$archived_logs to backup .arc files

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

querying v$archived_logs to backup .arc files [message #533174]

Mon, 28 November 2011 10:39

BeefStu
Messages: 208
Registered: October 2011

Senior Member

When we backup .arc files we issue a RMAN command similiar
to this:


KEEP_ARCHIVE_HOURS=2

rman <<EOT
connect target ....
connect catalog ...
    sql "alter system archive log current";
    backup filesperset 5 archivelog until time 'sysdate-$KEEP_ARCHIVE_HOURS/24'
    format $FILE_DEST_ARCH
    delete input ;
    resync catalog;
EOT

The problem is at times there may not be any archive files
that meet this criteria and RMAN throws an error


RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archived log in 
the recovery catalog

I was considering using the following code to see if there
are any available .arc files that meet the backup criteria.


set pagesize 0 feedback off verify off heading off echo off;
select count(1) from v$archived_log where 
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 
and backup_count = 0 and name is not null;

While testing on several systems, I found that the
above-mentioned query picked up some redo-logs (*.rdo) I
can only assume that these redo logs were used for a DB recovery.


/u05/oradata/dmartdev/redo_01a.rdo	1207	24-JUL-11	25-SEP-11
/u15/oradata/dmartdev/redo_01b.rdo	1207	24-JUL-11	25-SEP-11
/u06/oradata/dmartdev/redo_02a.rdo	1208	24-JUL-11	25-SEP-11
/u16/oradata/dmartdev/redo_02b.rdo	1208	24-JUL-11	25-SEP-11
/u07/oradata/dmartdev/redo_03a.rdo	1203	24-JUL-11	25-SEP-11
/u17/oradata/dmartdev/redo_03b.rdo	1203	24-JUL-11	25-SEP-11
/u05/oradata/dmartdev/redo_04a.rdo	1204	24-JUL-11	25-SEP-11
/u15/oradata/dmartdev/redo_04b.rdo	1204	24-JUL-11	25-SEP-11
/u06/oradata/dmartdev/redo_05a.rdo	1205	24-JUL-11	25-SEP-11
/u16/oradata/dmartdev/redo_05b.rdo	1205	24-JUL-11	25-SEP-11
/u07/oradata/dmartdev/redo_06a.rdo	1206	24-JUL-11	25-SEP-11
/u17/oradata/dmartdev/redo_06b.rdo	1206	24-JUL-11	25-SEP-11
/archive/arch/dmartdev/dmartdev_118_1_762799747.arc	118	25-SEP-11	23-NOV-11
/archive/arch/dmartdev/dmartdev_119_1_762799747.arc	119	25-SEP-11	23-NOV-11
/archive/arch/dmartdev/dmartdev_120_1_762799747.arc	120	25-SEP-11	24-NOV-11
/archive/arch/dmartdev/dmartdev_121_1_762799747.arc	121	25-SEP-11	25-NOV-11
/archive/arch/dmartdev/dmartdev_122_1_762799747.arc	122	25-SEP-11	26-NOV-11
/archive/arch/dmartdev/dmartdev_123_1_762799747.arc	123	25-SEP-11	27-NOV-11
/archive/arch/dmartdev/dmartdev_124_1_762799747.arc	124	25-SEP-11	28-NOV-11

I have a few questions:

1) Is my assumtion correct about the .rdo files
in the v$archived_logs table

2) I would hate to add special logic and look for something
like name substr(name, *.arc) (pardon the syntax).
Therefore, is there a way to get rid of these *.rdo files?
If so how, if not can somebody suggest a modification to
my query where it will only find archive files that
have not been backed up yet without. Please keep in
mind I want to have this query as generic as possible

Thanks to all who answer

Report message to a moderator

Re: querying v$archived_logs to backup .arc files [message #533178 is a reply to message #533174]

Mon, 28 November 2011 11:41

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

select count(1)

Stop using the silly count(1) and use the correct count(*).

*.rdo *.arc ... are meaningless to Oracle, it does not care about names.

Regards
Michel

[Updated on: Mon, 28 November 2011 11:41]

Report message to a moderator

Re: querying v$archived_logs to backup .arc files [message #533179 is a reply to message #533178]

Mon, 28 November 2011 11:48

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Not sure what you have against count(1) at one time it was
faster than count(*). This may or may not be true with 11g since
I have not gone back and tested it.

Secondly, not sure if I am getitng your point. If for example
my query did not return any .arc files it would have found
the .rdo files.. Since the count would be > 0 I would begin
the rman and this would cause the error. So what is your
suggestion?

thanks in advance for your help

Report message to a moderator

Re: querying v$archived_logs to backup .arc files [message #533183 is a reply to message #533179]

Mon, 28 November 2011 11:59

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Not sure what you have against count(1) at one time it was
faster than count(*). This may or may not be true with 11g since
I have not gone back and tested it.

count(1) was NEVER faster than count(*).
count(1) was SLOWER than count(*).
count(1) just proves you don't understand what you write and how SQL engine works.

Quote:

Secondly, not sure if I am getitng your point.

What don't you understand in "rdo, arc or any extension is meaningless to Oracle, Oracle does not care about extensiob, Oracle does not care about names"; you can name all your files BeefStu*.BeefStu, Oracle does not care about it and will work in the same way.

In the end, why do you care about this error? Just ignore it.

Regards
Michel

[Updated on: Mon, 28 November 2011 11:59]

Report message to a moderator

Re: querying v$archived_logs to backup .arc files [message #533185 is a reply to message #533174]

Mon, 28 November 2011 12:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>I found that the above-mentioned query picked up some redo-logs (*.rdo)
Above mentioned query will not produce this output.
And v$archived_log will have information on archived logs only.
You need to provide more proof if are claiming v$archived_log is displaying redolog files (actual ones. Not based on naming conventions).

As Michel is trying to convey, .arc or .rdo means nothing to Oracle.

Report message to a moderator

querying v$archived_logs to backup .arc files [message #533190 is a reply to message #533185]

Mon, 28 November 2011 12:41

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Mahesh

You say v$archived_log only contains info about .arc files.
As you can see I listed out my redo logs, can you tell me
how they wound up in v$archived_log? (see my first post)


V$LOGFILE contains information about redo log files.

select group#, member from v$logfile order by 1;

1	/u05/oradata/dmartdev/redo_01a.rdo
1	/u15/oradata/dmartdev/redo_01b.rdo
2	/u16/oradata/dmartdev/redo_02b.rdo
2	/u06/oradata/dmartdev/redo_02a.rdo
3	/u07/oradata/dmartdev/redo_03a.rdo
3	/u17/oradata/dmartdev/redo_03b.rdo
4	/u05/oradata/dmartdev/redo_04a.rdo
4	/u15/oradata/dmartdev/redo_04b.rdo
5	/u06/oradata/dmartdev/redo_05a.rdo
5	/u16/oradata/dmartdev/redo_05b.rdo
6	/u17/oradata/dmartdev/redo_06b.rdo
6	/u07/oradata/dmartdev/redo_06a.rdo

Michel, yes I can easily ignore the error but I am trying
to understand what caused this and why?

Report message to a moderator

Re: querying v$archived_logs to backup .arc files [message #533195 is a reply to message #533190]

Mon, 28 November 2011 12:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I still do not see the entries from v$archived_log

Report message to a moderator

Re: querying v$archived_logs to backup .arc files [message #533198 is a reply to message #533195]

Mon, 28 November 2011 13:10

BeefStu
Messages: 208
Registered: October 2011

Senior Member


set pagesize 0 feedback off verify off heading off echo off;
select name from v$archived_log where 
COMPLETION_TIME < sysdate-2/24 and backup_count = 0 and name is not null;

[[size=3][color=red]font=Arial]/u05/oradata/dmartdev/redo_01a.rdo
/u15/oradata/dmartdev/redo_01b.rdo
/u06/oradata/dmartdev/redo_02a.rdo
/u16/oradata/dmartdev/redo_02b.rdo
/u07/oradata/dmartdev/redo_03a.rdo
/u17/oradata/dmartdev/redo_03b.rdo
/u05/oradata/dmartdev/redo_04a.rdo
/u15/oradata/dmartdev/redo_04b.rdo
/u06/oradata/dmartdev/redo_05a.rdo
/u16/oradata/dmartdev/redo_05b.rdo
/u07/oradata/dmartdev/redo_06a.rdo
/u17/oradata/dmartdev/redo_06b.rdo[/font][/color][/size]/archive/arch/dmartdev/dmartdev_118_1_762799747.arc
/archive/arch/dmartdev/dmartdev_119_1_762799747.arc
/archive/arch/dmartdev/dmartdev_120_1_762799747.arc
/archive/arch/dmartdev/dmartdev_121_1_762799747.arc
/archive/arch/dmartdev/dmartdev_122_1_762799747.arc
/archive/arch/dmartdev/dmartdev_123_1_762799747.arc
/archive/arch/dmartdev/dmartdev_124_1_762799747.arc

Report message to a moderator