How to drop an ARCHIVED-LOG file.

[madhes]

Hi,
I want to drop/delete an archived-log file from an instance. we can see the
list of online and archived log files from the views, V$LOGFILE and V$ARCHIVED_LOG respectively.

Oracle supports dropping an online log file using the foll. statement.
ALTER DATABASE DROP LOGFILE 'file-name';

But, this doesnt delete/drop the archived-log file. Also i m not able to delete the entry from the V$ARCHIVED_LOG.

How can i drop an archived log file?

tia,
madhes

 

[karluk]

Simply delete whatever archive log files you wish, preferably after backing them up to tape. There's no reason to try to update Oracle's internal records. In fact it's much better to let Oracle retain this information. Then, if you ever have to do a recovery, Oracle will know what logs to apply and you can restore them from tape.

 

[solara]

Hi,

Karluk has supplied you the very answer to solve the problem. But don't delete all.


It should work but make sure you backup before deleting and also you can leave up to 4 to 5 days worth of log.

Solara

 

[madhes]

Thanks.
But, i wanted to delete that file name from the view too. Because, from a Java program, i m accessing those files, after reading their names from that view. So, i ll get an IOException, saying that FileNotFound.

So, i need to delete that file-name entry from the view. Pls suggest some way to delete the entry.

--madhes

 

[solara]

Hi madhes,

What are you trying to do? What view are you accessing the log file from? and what is their relationship i.e between the view and the arc log files.


Olu

 

[carp]

If you use RMAN, you have a DELETED column in the v$archived_log view. However, since you are asking how to delete the file, I have to conclude that either you are NOT using RMAN or you are not having RMAN remove the archive logs.

 

[madhes]

Okay! My exact requirement is something like this:
I need to read all the log files and apply the same statements on another target database. For this I use, LogMiner utility provided by Oracle. Before starting a logminer session, the log files that are need to be analysed have to be added with the Logminer. For this, i read the view V$ARCHIVED_LOG, where the list of log file names will be stored, and add those files to the Logminer.

Now, Since I ve a lot of log files that I ve read already, i dont want to analyze them again. So, I need to delete them.
Hope i ve explained properly. Pls let me know if i were wrong somewhere.

I used RMAN utility too, to delete the file. It clearly deletes the file from the Folder, but not from that view V$ARCHIVED_LOG. So, when i read the filename from that view and add it to the LogMiner, it gives error, that 'File is not found at the location'.

Thats why i needed to remove that file name from the view too.
--madhes

 

[karluk]

I'm afraid you're just going to have to accept that Oracle will not let you delete rows from v$archived_log. Carp's suggestion is the most plausible approach. Check the STATUS or DELETED column to find out if the files are actually present on your server. If necessary you can define a view on v$archived_log so that only files that are available will show up.

 

[Guest_imported]

How do you set the deleted column in V$ARCHIVED_LOG to YES? I am new to RMAN and some logs have been deleted. Our backup software is looking at V$ARCHIVED_LOG and because they have not been set to deleted is looking for the 1st log in the list.
Cheers
Phil