Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Dataguard’ Category

script to delete applied archives on standby database on windows OS

Posted by Pavan DBA on March 26, 2012


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

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

 

Advertisements

Posted in Dataguard, Scripts | Tagged: | Leave a Comment »

script for deleting applied archive on standby database on unix OS

Posted by Pavan DBA on March 26, 2012


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

unix script for deleting applied archives in standby database

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

#!/usr/bin/ksh

ARCH_DIR=/archive/orcl

 

#take the log number to be applied

LogNo=`tail -30000 /oracle/app/oracle/product/10.2.0/admin/orcl/bdump/alert* | grep “Media Recovery Log” | cut -d ” ” -f 4 | cut -d “_” -f 5 | tail -1 `

echo “Oracle applied LogNo is $LogNo”

 

#extract 10

let SecLogNo=${LogNo}-10

echo “new backlog log No: $SecLogNo”

 

#delete small numbers from this in arch dir

cd $ARCH_DIR

for i in `ls *.arc`

do

 

Newi=`echo $i | cut -d “_” -f 4`

if [ $Newi -lt $SecLogNo ] ; then

echo “$i to be deleted…”

rm $i

fi

done

Posted in Dataguard, Scripts | Tagged: , | Leave a Comment »

 
%d bloggers like this: