Re: log_archive_interval

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 01 Dec 2004 07:10:50 +1100
Message-ID: <41acd3d1$0$8929$afc38c87@news.optusnet.com.au>

Hans Erik Busk wrote:
> On Tue, 30 Nov 2004 19:09:15 +1100, "Howard J. Rogers"
> <hjr_at_dizwell.com> wrote:
>
>

>>If you must, then use Windows tools to schedule a periodic copying 
>>across of a local destination. Do not use Oracle to archive to a remote 

>
>
> Just be a little carefull about that.
> The archive files are not locked while they are written, so you could
> actually copy a half completed i.e. completely useless archive log !!
>
> H.E.Busk
> Denmark

That is indeed true if one were to be daft enough simply to copy an archiving directory in its entirety. But if you were to source your information about what archives existed from v$archived_log, for example, then that could not happen, because the view only records *completed* archives.

The example Windows Scripting Host Backup Script I have on my website, for example, does:

Sub BkpArchives

	archivedest="C:\ORACLE\ORA92\RDBMS\"
	strFileOut="archives.sql"
	strBp="set trimspool on" & chr(10) & "set heading off" & chr(10) &"set 

feedback off" & chr(10) & "set echo off" & chr(10) & "spool archives.txt" & chr(10)

        set objFS=CreateObject("Scripting.FileSystemObject")

        set objOutFile=objFS.OpenTextFile(strFileOut,2,1)

        strOutput=strBp & "select name from v$archived_log;" & chr(10) & "spool off" & chr(10) & "exit"

        objOutFile.WriteLine strOutput

	Set WshShell=CreateObject("WScript.Shell")
	strErrorReturned=WshShell.Run("sqlplus system/dizwell 

@archives.sql",1,True)

	strFileIn="archives.txt"
	set objFS=CreateObject("Scripting.FileSystemObject")
	set objFile=objFS.OpenTextFile(strFileIn,1,0)

	While Not objFile.AtEndOfStream
		strInput=objFile.ReadLine
		if strInput="" Then
		Else
			intArchDestPos=Len(strInput)-Len(archivedest)
			strFileName=Right(strInput,intArchDestPos)
			newname="C:\BACKUP\" & strFileName
			On Error Resume Next
			objFS.COPYFile strInput, newname
			objFS.DeleteFile strInput
			On Error Goto 0
		End If
	wend

End Sub

That is, produce a text file listing all archives which have been definitely created and closed by dynamically querying v$archived_log, then use that as the source for a copy file operation, and a 'delete the original' operation after that.

But yes, it was an assumption that I'd made that no-one would just start copying Oracle-related directories which might actually be in use. So I should indeed have been "a little [more] careful".

Regards
HJR Received on Tue Nov 30 2004 - 14:10:50 CST