NAME

oraredo.ksh - make Oracle database archive log backup.

SYNOPSIS

oracold.ksh help=y

oracold.ksh MySID|sid:=MySid [chkfs|chkfs=y] [debug=y]

DESCRIPTION

oraredo.ksh find the highest sequence (THE_SEQ) number below which all the archive log files are archived. Or more precisely, the highest THE_SEQ that the following SQL statements return 0 rows:

  SELECT COUNT(1)
  FROM v$log
  WHERE sequence# < $THE_SEQ
  AND archived='NO'

The highest possible number is the current sequence number. However on busy systems, the non-current redo logs may still be in the process of archiving (copying files to disk). In this case, oraredo.ksh waits, and reduce THE_SEQ alternatively. (A simple listing by timestamp or alphabetic order are not reliable. With multiple archivers introduced in Oracle 8, an early started process may finish after a later started process.)

Once THE_SEQ is found, oraredo.ksh compresses the archived logs from THE_SEQ -1 and below until archived log file is no long found.

After the backup copy is verified to be good copy via cksum, the original copy in archive log destination is removed.

Features include:

Multiple filesystems (BKUP_DIRS in configuration file). Dynamic allocate filesystem per free disk space.
Compare the cksum of the copied file and original file before deleting.
Dynamically find the destination and format of the archive log files. It is more generic, and does not depends on any convention or site standard.
Blackout timezone (BLACKOUT in configuration file) to skip backup at certain day and certain hours. This is to avoid conflict with hot backup and make backup filesystem more static for central backup.
chkfs|chkfs=y command line option to check if the file system for archive log is over certain threshod (THRESHOLD in configuration file), and only make backup when the threshod is exceeded. This is suitable for hourly backup, it prevents the archive log destination from being full, and to avoid frequent small backups so that the backup filesystem more static for central backup.
Willing to wait for other redo backup or hot backup session.
You can choose how long to keep the archive logs in backup directories (KEEP_MINS in configuration file), or just delete the logs without backup (set KEEP_MINS to be a negative number). The latter is suitable for staging or development databases which need to be in archivelog mode but does not want to fill the archive log destination.

INSTALLATION

To install, follow these easy 1-2-3 steps:

Create an install directory for redo backup <job dir>/ora_redo. And under ora_redo, create following sub-directories:
```
  script
  etc
  operlog/<SID>
  temp/<SID>
```
Where SID is the actual ORACLE_SID name. If you have multiple SIDs, then just create them under operlog and temp.

  cp oraredo.ksh script/oraredo.ksh
  chmod 755 script/oraredo.ksh

Edit etc/oraredo_SID.cf from the sample configuration shown below. If you have multiple SIDs, then just create multiple files.


  BKUP_DIRS="/bkup01/redo /bkup02/redo"


  # *
  # * How long do you want to keep backup redo logs?
  # * If KEEP_MINS is minus number, then archive logs are deleted
  # * without being copies to backup directories. 
  # * default is 1440 minutes (24 hours).
  # *


  KEEP_MINS=1440    # in minutes


  # *
  # * With CHKFS option, at what high watermark 
  # * of archive_dest you want to run the backup?
  # * Default is 50 (50%).
  # *


  REDO_THRESHOLD=50


  # *
  # * Is there blackout perid that you do not want run backup?
  # * The default is no blackout. 
  # *


  BLACKOUT="00000-00000"
  # BLACKOUT="00000-00000 11830-20600 61830-62400 00000-00600 00000-62400"
  #   00000-00000: no blackout
  #   11830-20600: Monday 18:30 to Tuesday 06:00
  #   61830-62400 00000-00600: Saturday 18:30 to Sunday 06:00
  #   00000-62400: all blackout


  # *
  # * Does this database support a standby database?
  # * The default is N.
  # *


  STANDBY="N"


  # *
  # * IF STANDBY=Y, what file at archive_dest keeps the last shipped/applied
  # * archived log file sequence number? (I won't delete the file until it is
  # * no longer needed by the standby site.)
  # * The default is standby.done.
  # *


  STANDBY_DONE="standby.done"


  # *
  # * At what age of STANDBY_DONE file will I stop backup and report error?
  # * Default is 2160 (36 hours).
  # *


  STANDBY_TIMEOUT=2160    # in minutes


  # *
  # * What command do you use to compress a file to standard output?
  # * Default is "compress -c".
  # *


  COMPRESS_C="/usr/bin/compress -c"
  # COMPRESS_C="/usr/local/bin/gzip -c"
  # COMPRESS_C="/usr/local/bin/bzip2 -c"


  # *
  # * Where can I find perl?
  # * Default is /usr/local/bin/perl.
  # *


  PERL=/usr/local/bin/perl


  # *
  # * Where can I find "find" that supports -mmin option? 
  # * (GNU find and AT&T find supports -mmin option.) 
  # * Default is /usr/local/bin/find.
  # *


  FIND=/usr/local/bin/find


  # *
  # * How long you want to wait for non-current redo logs to be archived?
  # * The default is 600 (10 minutes).
  # *


  MAX_ARCH_TIME=600    # in seconds


  # *
  # * How long you want to wait for other redo/hot backup sessions to finish?
  # * The default is 60 (1 hour).
  # *


  REDO_WAIT_MINS=60    # in minutes

AUTHOR

Michael Wang <xw73@columbia.edu> with help from many others acknowledged in version notes.

This is free software. You may copy or redistribute it under the same terms as Perl. However, if you modify it, you need to send the modification to the author via email.

NAME

SYNOPSIS

DESCRIPTION

INSTALLATION

SEE ALSO

AUTHOR