Data-Guard configuration / deletion of redo.logs files on standby

[Former Member]

One question: has someone an idea how to configure the standby database to delete the recovered offline redo logs on the standby side?

Data-Guard Manager has the control over the instances. so i cannot use brarchive to recover the standby side.

Now i have no idea how i can delete the recovered offline redo.logs?

Has someone an idea?

Thanks in advance

Greetings Michael

[Former Member · Accepted solution]

Michael,

You could script it to first read the database and then delete the logs that have been applied or in our case, we have a cron job that runs daily and deletes archive log files on standby server that are more then a day old.

find.. mtime +1 exec rm

As we have regular notifications setup to inform us of the logshipping status.

Cheers,

Nisch

[Former Member · Accepted solution]

Hi

Thanks for your help. I thougt Oracle or SAP has a solution in brarchive and I don't know something about it. I not really like solutions like this because it's not standard implementation with sap tools.

Has someone an example of a script?

Thanks!

Regards Michael

[Former Member · Accepted solution]

Here is the script i used then, of course i can give no support on it. I do also not have the time to translate the german commentarish ))

It was tested under HP-UX 11.23.

#!/usr/bin/ksh

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

1. #

2. standbylog_clean.sh #

3. ------------------- #

4. #

5. Loescht Logfiles die auf der Standby Datenbank applied sind #

6. #

7. Aufruf: ./standbylog_clean.sh (als oracle user) #

8. Version: 0.1, 19.07.2005, mho #

9. #

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

1. Nebeneffekte:

2. - einige aeltere Versionen von tail koennen nur etwa 500

3. Zeilen ausgeben, dadurch kann das Logfile kuerzer werden

4. als eigentlich gewollt

5. - Falls die Log Namen nicht das Format *_<Sequenznummer>.dbf

6. haben werden die Zeilen mit den SED Kommandos nicht

7. funktionieren! Das Script sollte trotzdem funktionieren,

8. ist allenfalls bei hohen Sequenznummern langsamer

1. Systemabhaengige Variablen - bitte anpassen

logfile="/oracle/SID/scripts_basis/standbylog_clean.log"

standbylogdir="/oracle/SID/saparch/standby/"

orauser=orac11

maxloglines=1000

1. externe Binaries - eventuell anpassen

ECHO="/usr/bin/echo"

LS="/usr/bin/ls"

WC="/usr/bin/wc"

WHOAMI="/usr/bin/whoami"

SED="/usr/bin/sed"

SORT="/usr/bin/sort"

HEAD="/usr/bin/head"

RM="/usr/bin/rm"

MV="/usr/bin/mv"

TAIL="/usr/bin/tail"

CAT="/usr/bin/cat"

DATE="/usr/bin/date"

GREP="/usr/bin/grep"

1. Datum merken

dat=`$DATE '+%d.%m.%Y %H:%M'`

1. Logfile trimmen

if [ -f $logfile ] && [ `$CAT $logfile | $WC -l` -gt $maxloglines ]; then

newloglines=$(( $maxloglines - 10 ))

$TAIL -$newloglines $logfile > $.tmp $MV $.tmp $logfile

fi

1. sind wir oracle user?

if [ `$WHOAMI` != "$orauser" ]; then

$ECHO "$dat Bitte als $orauser starten - exit" >> $logfile

exit

fi

1. haben wir ueberhaupt irgendwelche Logs?

if [ `$LS -1 $standbylogdir/*.dbf | $WC -l` -eq 0 ]; then

$ECHO "$dat Keine Logfiles vorhanden - exit" >> $logfile

exit

fi

1. ist die DB im MOUNT status (ansonsten ist sie entweder unten,

2. oder sogar offen, in beiden Faellen machen wir nix)?

sql='SELECT STATUS FROM V$INSTANCE;'

status=`sqlplus -s "/ as sysdba" <<EOF

set feed off

set heading off

set pagesize 0

set linesize 1000

$sql

exit

EOF`

if [ "$status" != "MOUNTED" ]; then

$ECHO "$dat Die Datenbank ist nicht gemounted - exit" >> $logfile

exit

fi

1. schauen welches das tiefste Log ist

lognum=`$LS -1 /oracle/C11/saparch/standby/.dbf | $SED -e 's/._//' -e 's/\.dbf//' | $SORT -n | $HEAD -1`

if [ `$ECHO $lognum | $GREP '^[0-9][0-9]*$' | $WC -l` -eq 0 ]; then

1. da ist was faul mit der Nummer

lognum=1

fi

1. v$archived_logs abfragen

sql='SELECT NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# >= '"$lognum AND APPLIED = 'YES';"

files=`sqlplus -s "/ as sysdba" <<EOF

set feed off

set heading off

set pagesize 0

set linesize 1000

$sql

exit

EOF`

1. Files loeschen

for file in $files; do

if [ -f $file ]; then

loglastnum=`$ECHO $file | $SED -e 's/.*_//' -e 's/\.dbf//'`

$RM $file

fi

done

if [ "$loglastnum" = "" ]; then

$ECHO "$dat Keine Logs zum loeschen, warte bis diese APPLIED sind" >> $logfile

else

$ECHO "$dat Habe die Logs $lognum - $loglastnum geloescht" >> $logfile

fi

[Former Member]

Hi Michael

I had the same issue when testing data guard with 9i. I wrote a little shell script

It basically does this on the standby side:

- check if there is one or more archive redo logs

- check whether the log is already applied:

[code]SELECT NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# >= $lognum AND APPLIED = 'YES';[/code]

- if yes, then delete

Regards

Michael