Can somebody tell me what it means when I have entries in the v$archived_log table
where the column name is NULL? Is this normal?

I tried running the following to get rid of these entries but that does not work.

CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;



2367 748780212 1 1 2364 11755171456654 3/28/2011 8:55:26 AM 746960126 11764004041650 4/18/2011 10:00:12 AM 11764004047003 4/18/2011 10:30:12 AM 38538 512 ARCH ARCH NO YES NO YES D 4/18/2011 10:30:12 AM NO NO NO 0 1 2938200999 NO NO NO NO


2368 748782013 1 1 2365 11755171456654 3/28/2011 8:55:26 AM 746960126 11764004047003 4/18/2011 10:30:12 AM 11764004053671 4/18/2011 11:00:12 AM 40084 512 ARCH ARCH NO YES NO YES D 4/18/2011 11:00:13 AM NO NO NO 0 1 2938200999 NO NO NO NO

Hi Stu,

>> I have entries in the v$archived_log table where the column name is NULL?

Which column?

Run the script on this page please:

http://www.dba-oracle.com/t_sql_redo_log_switch_reports.htm

The v$archived_log view shows details of the progress of the managed recovery process for individual archived logs.

The following query can be used to see the status of an individual archived log on the standby site. ‘RFS’ in the REGISTRAR column shows that these logs are transferred from the primary site by log transfer services.

Donald,

It's the name column in the v$archived_log table where I am seeing some columns, which have
NULL values. This is not a standby database. As for the query, here is a sample of the output.

Th# Seq# APPLIED REGISTR

---- -------- --------- -------

1 186 NO ARCH
187 NO ARCH
188 NO ARCH
....
....
....
2,452 NO ARCH
2,453 NO ARCH
2,454 NO ARCH


Note: APPLIED=NO and REGISTRAR=ARCH for all the columns in the above-mentioned query.
One more thing I would like to mention. This DB was actively cloned from my production database.
Everything appears to be working fine... I have NO problem backing up archive files via RMAN.

To give you a bit of backgroud we use the following code to backup archive files.
rman<<END
...
...
sql "alter system archive log current";
backup filesperset 5 archivelog until time 'sysdate-$KEEP_ARCHIVE_HOURS/24'
format $FILE_DEST_ARCH
delete input ;
END

The variable KEEP_ARCHIVE_HOURS is passed in the cron and can be any number (0,1,2,...)
If there are no archive logs available based on sysdate-KEEP_ARCHIVE_HOURS/24 rman
throws an error.

I want to use the query below to deteremine if there are any .arc logs to backup

select count(1) from v$archived_log
where COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24
and backup_count = 0;

but as you can see based on the above-results of the query it will always find .arc files to backup
even though there are none on disk.

I do have a work around, by checking on disk and getting the .arc file and checking the time
but I would like to use the my query to drive the backup but I am unsure as to why I have all
these entries in the v$archived_log table.

Thanks in advance for your comments and thoughts

Hi,

>> It's the name column in the v$archived_log table where I am seeing some columns, which have
NULL values.

Sorry, I have no idea!

I would open a service request on MOSC, it may be a bug:

http://support.oracle.com

Let us know what Oracle says!

The description in the Reference Guide for this column states what null means in the NAME column.

Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).

http://download.oracle.com/docs/cd/E11882_...16.htm#I1030011

Steve,

Thanks for pointing me in the right direction. Here is the chunk of code we use to backup our archive
logs:


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;

As you can see it's using the the delete input command. We use a third party tool for replication and
this relies on the .arc files being present so that is the reason for the until time clause...

If I replace the delete input with "backup archivelog all delete input" would that honor my until clause
and keep the .arc files around for the specified time.

Secondly, do you know of any command, which I can use to remove these "null" name columns from
the v$archived_log file.

Donald/Steve thanks for your expertise and help!!

I wouldn't do anything in terms of trying to update a data dictionary view, other than to use whatever interface (archivelog mode, RMAN, Data Guard) doing it behind the scenes. But overall, what is the issue with having null in this view? To me, it kind of falls into the "so what" category of things to worry about.

As you saw my archive code above the issue is when there are no .arc files, which meet the time
criteria RMAN balks and throws an error. I want to change the code to use the v$archive_log
table to do something like this:

select count(1) from v$archived_log where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0;

Having these entries in the the view will return "yes" there are files to archive, when there
are really none on disk to archive. I have code that checks the FS for .arc files and than compares
the time of the files against sysdate-$KEEP_ARCHIVE_HOURS/24 but this is very awkward.

That is the reason I want to see if I can clean up the view. Hope that explains it!!

FYI, I inherited this so I have to try to clean it up.

Once again, thanks for all your help

Hi Stu,

Can't you just ignore the NULL rows?



*******************************************
>> That is the reason I want to see if I can clean up the view. Hope that explains it!!

Yeah, that makes sense, I wish I could help you!

Can you open an SR on MOSC? This may be a bug!

Also, check the catalog.sql to see how it is built, if you want to dig into the base x$ structures . . .

Donald,

I opened and SR with Oracle regarding this issue and I am still at the very top layer of support,
meaning I am going back and forth with them trying to explain my issue to them. When I have
a definitive answer I will certainly share the knowledge....

Thanks for your help.

Donald.

Just to follow up regarding this issue the code you suggested (ignoring the nulls) appears to work
in all my cases.

select count(1)
from v$archived_log
where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0
and name is not null

What threw me is why Oracle uses backup_count and a null name to say the file has been
removed from is archive location.

Thanks for your expertise. CASE CLOSED!!

Attention!!!

I have the same issues here, just a test-db so not that big issue but...
No recovery is possible anymore.
There is a RMAN-03002 followed by a ora-06502 (number precision too large) if i try to restore (or recover) anything.
Reason is as far as I found out until now that the name column is null, and so the underlying package (X$DBMS_RCVMAN) gets this pl/sql error.

So try a restore or recovery your db before you think that problem is fixed, hopefully it's not your production environment.

Manfred

Manfred, Thank you for your valuable contribution!

For quick reference, information on RMAN v$archived_log is NULL is here:

http://dba-oracle.com/t_rman_v_archived_log_null.htm

RHR

:-(

So, there seems to be a serious problem in my control file.

.) I restore the db to a dummy db (file-copy so its a perfect duplicate)
.) still get the error when doing anything in rman (e.g. list backup, restore database....)
.) create a new controlfile
.) works again
.) catalog a backupset
.) still works
.) TRY to catalog the controlfile for the backupset (the invalid one)
.) not able to use RMAN anymore

And believe me I really tested and retested this several times, I did not make any mistakes in my conclusion here.
I also tested this with a completely new db by changing the dbid to be able to catalog the backuppieces.

Any suggestions? I really don't know what I could try now.

Hi Manfred,

Yes, this is very frustrating . . .

******************************************

>> Suggestions?

Have you checked the bugs database?

We don't have access to the Oracle souece code, your only hope is to open a service request on MOSC:

http://support.oracle.com

Let us know the solution!

I'll also ask an RMAN expert to pop in . . .

Checked every bug I could find, so there are things that happen that make your controlfile invalid for any RMAN command.
I can live with that as long as I manage to Restore the backup.... (http://dbaforums.org/oracle/index.php?showtopic=21198). GEEE thought it will be a simple restore database from tag....

The update to the dictionary views would be a highly bad idea. I believe, Don's suggestion to get OSS involved is the best one in this case. I tried to reproduce the error myself but couldn't. Do let us what OSS responds back.

Aman....

No OSS, would take toooooooo long. I am happy with being able to restore the DB without a control file (see other thread).
The changing of the DBID is not that big problem and worked fine.