Pavan DBA's Blog

#############################################################

windows script for deleting applied archives in standby database

#############################################################

windows:

There is a configuration file “E:\dba\orahome\mntdir\config\dba.xml” (you should replace { and } with the ones used in standard xml. I can not use them in this post, corrupts page)

 

{?xml version=”1.0″ encoding=”utf-8″ ?}

{config}

{general}

{archive_format}ARC_0628272535_001_SEQUENCE.ARC{/archive_format}

{archive_dir}E:\oracle\oradata\archive{/archive_dir}

{/general}

{stby}

{prodUser}dbuser{/prodUser}

{prodPass}password{/prodPass}

{prodTNS}PROD{/prodTNS}

{/stby}

{/config}

 

There is a file “E:\dba\orahome\stby\archive_last_deleted.txt” in which last deleted archived log’s sequence number is kept. At first it can be set as 1.

My scheduled vbs script “E:\dba\orahome\stby\stby_archclean.vbs” uses configuration file to connect primary site, query dataguard status and delete applied archived logs at standby server:

 

‘********************* CODE TO PARSE DBA.XML FILE ****************************

Set oXMLDoc = CreateObject(“MSXML2.DOMDocument.3.0”)

oXMLDoc.async = False

If Not oXMLDoc.load(“mntdir\config\dba.xml”) Then

Set oErr = oXMLDoc.parseError

sErrMsg = “XML Parsing Error. File: ” & oErr.url & ” Reason : ” & oErr.reason & ” Line: ” & oErr.line & “, Character: ” & oErr.linepos & “,Text: ” & oErr.srcText

Err.Raise 999, , sErrMsg

End If

‘******************************************************************************

 

Set WshShell = CreateObject(“WScript.Shell”)

pUSER = oXMLDoc.selectSingleNode(“/config/stby/prodUser”).text

pPASS = oXMLDoc.selectSingleNode(“/config/stby/prodPass”).text

ARCHIVE_FORMAT = oXMLDoc.selectSingleNode(“/config/general/archive_format”).text

ARCHIVE_DIR = oXMLDoc.selectSingleNode(“/config/general/archive_dir”).text

pTNS = oXMLDoc.selectSingleNode(“/config/stby/prodTNS”).text

 

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

set archive_last_deleted = objFSO.OpenTextFile(“stby\archive_last_deleted.txt”, 1) ‘for reading

DIM LAST_DELETED

if not archive_last_deleted.AtEndOfStream then

LAST_DELETED = cInt(archive_last_deleted.ReadLine)

else

WScript.Echo “Error on stby\archive_last_deleted file”

WScript.exit

end if

archive_last_deleted.close

NEXTTODELETE=LAST_DELETED+1

 

 

 

‘ADO Connection object

Set con = CreateObject(“ADODB.Connection”)

con.CursorLocation = 3

If con.State = 1 Then con.close

con.Open “PROVIDER=MSDAORA;DATA SOURCE=”&pTNS&”;USER ID=”&pUSER&”;PASSWORD=”&pPASS&”;”

 

‘ADO Command object

Set cmd = CreateObject(“ADODB.Command”)

cmd.CommandText=”select APPLIED_SEQ#-3 from v$ARCHIVE_DEST_STATUS where DEST_ID=2 ”

cmd.CommandType=1

cmd.ActiveConnection=con

 

‘ADO Recordset object

set rs=CreateObject(“ADODB.Recordset”)

Set rs=cmd.Execute

 

 

if rs.EOF then

WScript.Echo “Error: No APPLIED_SEQ# record for DEST_ID 2”

rs.Close

con.Close

WScript.exit

else

DIM MAX_SEC_APPLIED

rs.MoveFirst

MAX_SEC_APPLIED = cInt(rs.Fields(0))

rs.Close

con.Close

end if

 

WScript.Echo “Archive Sequence Range to Clean:” & cStr(NEXTTODELETE) & ” – ” & cStr(MAX_SEC_APPLIED)

do while MAX_SEC_APPLIED > NEXTTODELETE

WScript.Echo “removing… ” & ARCHIVE_DIR & “\” & Replace(ARCHIVE_FORMAT,”SEQUENCE”,cStr(NEXTTODELETE))

‘WshShell.run(“del /Q ” & ARCHIVE_DIR & “\” & Replace(ARCHIVE_FORMAT,”SEQUENCE”,cStr(NEXTTODELETE)))

objFSO.DeleteFile ARCHIVE_DIR & “\” & Replace(ARCHIVE_FORMAT,”SEQUENCE”,cStr(NEXTTODELETE))

set archive_last_deleted = objFSO.OpenTextFile(“stby\archive_last_deleted.txt”, 2) ‘for writing

archive_last_deleted.Write cStr(NEXTTODELETE)

archive_last_deleted.close

NEXTTODELETE = NEXTTODELETE+1

loop