From: kml@cellar.org (Scott Tiger)
Newsgroups: comp.databases.oracle
Subject: DBA FAQ, Part I: Backup Strategy
Date: 29 Sep 92 13:30:04 GMT
Organization: The Cellar BBS and public access system

DBA FAQ
(Frequently Asked Questions for/from Database Administrators)
by Kevin M. Loney, aka kml@cellar.org (username "Scott Tiger")

First in a Series.

Part 1.  Backup Strategy

This is several pages long, you may want to just print it out and
read it offline.

Q: What backup strategy should I use to best protect myself
        against media and node failures?  How should I structure
        my backup procedures to minimize the vulnerabilities they
        cause?  And how can I have them check for both physical
        and logical errors?

A:
I will assume throughout this discussion that the reader has at
least a passing knowledge of the backup options available in Oracle.
The recovery options and procedures are well documented, both in the
DBA Guide and in the online RECOVERY.DOC file in the RDBMS directory.
The procedures described here will allow the user to best exploit
the tools available to him/her.

There are three valid ways of backing up an Oracle (V6-7) database.
They are:
        1.  Shut the database down and backup up all tablespace,
                log, and control files.
        2.  While the database is up, export the entire database.
        2.  While the database is up, running in ARCHIVELOG mode,
                set the tablespaces into backup state and back up
                their files, then get the control files and the
                archived redo logs.

I'll be integrating all three of these.  Even if you are already
familiar with them, read the sections below.


1.  The Cold Backup.
====================
Once a week, shut down the database and perform a cold backup by
going disk by disk and backing up all files in the [oracle]
directories.  When complete, restart the database.  Why perform a
shutdown/startup?  Well, it's good to make sure the database can
be restarted.  There are certain parameters that are only checked on
startup; errors with rollback segments in particular may only show
up during startup operations.


2.  The Export.
===============
A full system export picks up information that user exports do not;
this includes third-party indexes and grants.  Why perform an export
at all?  Well, regular backups check that the database files are
physically acceptable; export checks that they are logically readable.
[Aside: This does not imply that they are logically importable;
corrupt records may be exported into the dump file, preventing import.
See my paper, #111, "Editing Corrupted Export Files (When Import Fails)"
in the proceedings from the 1992 IOUW.]

For example, export will detect data structure errors in tables (these
are particularly prevalent in tables containing LONG datatypes).
Export is of limited use for full-system backups; in order to recreate
from an export file, you'd have to re-create the database from scratch
first.  However, it is worthwhile to periodically validate every
record in the database via export.  The dump file can then be used to
retrieve particular tables/users if needed.

Immediately following the export, run scripts to map the tablespaces
to owners, and owners to tablespaces.  This will allow you to see who
owns what where.  In the event of a tablespace loss, you would then
be able to quickly determine what users/systems will be affected.
Here are two scripts I use:

rem user object locations
set pagesize 60
break on owner on tablespace_name
column owner format A20
column tablespace_name format A32
column objects format A20

select substr(owner,1,20) owner,
        substr(tablespace_name,1,32) tablespace_name,
        count(*)||' tables' objects
from sys.dba_tables
group by
        substr(owner,1,20),
        substr(tablespace_name,1,32)
union
select substr(owner,1,20) owner,
        substr(tablespace_name,1,32) tablespace_name,
        count(*)||' indexes' objects
from sys.dba_indexes
group by
        substr(owner,1,20),
        substr(tablespace_name,1,32)


spool user_locs.lst
/
spool off
clear columns
clear breaks

rem  ts_users.sql
rem  user list by tablespace for export reference
column tablespace_name format A32
column owner format A20
column objects format A20
break on tablespace_name on owner
set pagesize 60

select
        substr(tablespace_name,1,32) tablespace_name,
        substr(owner,1,20) owner,
        count(*)||' tables' objects
from sys.dba_tables
group by
        substr(tablespace_name,1,32),
        substr(owner,1,20)
union
select
        substr(tablespace_name,1,32) tablespace_name,
        substr(owner,1,20) owner,
        count(*)||' indexes' objects
from sys.dba_indexes
group by
        substr(tablespace_name,1,32),
        substr(owner,1,20)

spool ts_users.lst
/
spool off

These will create the tablespace/user mapping files.


3.  The Hot Backup.
===================
The ARCHIVELOG function that came in OracleV6 opened up a whole new set
of possible backup options, but also caused a lot of heartache until they
got it working right.

Good parts about Archivelog:
        1.  It gives you 24-hour availability.
        2.  It gives you point-in-time recovery.
        3.  It allows you to restore without losing any data at all.
        4.  It lets you keep the database up, preserving the data in
                your SGA.

Bad parts about Archivelog:
        1.  Administration can be difficult.
        2.  Repetitive failed load attempts can create massive logs.
        3.  When it gets stuck, the database comes to a halt.


Implementing Archivelog:
------------------------
To start using Archivelog, simply alter your startup command file:

startup mount <SID> exclusive;
connect internal;
alter database archivelog;
archive log start;
alter database open;

and startup the database.  From then on, the database will be in
Archivelog mode until you turn it off (alter database noarchivelog).
Note that a failed transaction is still a transaction; Archivelog keeps
track of rollback segment extents, so rolled back inserts or deletes
affect it just like completed transactions.  Turn it off (shutdown,
restart with noarchivelog mode) before doing big loads from SQL*Loader.

When you start archiving, archived logs will be written every time the
redo operation is about to overwrite a previously written logfile.  It
will write it to the directory indicated by the log_archive_dest parameter
in your init.ora file.  They will all be the same size (in V6; V7 can have
variably sized archive logs) as your redo logs.  They will increase in
number until they run out of space on their destination device.  At that
point the database will freeze until you clear more space for them in the
log_archive_dest location. SO, have a second location ready to receive them.


Backup strategy for Archivelogs:
--------------------------------
1.  Do each tablespace one at a time.  That is, rather than setting them
all offline, then backing them up, then setting them back online, do them
each separately.  You don't want to risk having a system crash while the
entire database is in begin backup state; recovery is a mess.  Minimize
your window of vulnerability by having only one tablespace in backup state
at any one time.

2.  Before you backup the control file, force an archive log switch.
This will update the header information in the control file.

3.  Don't do it during user activity.  When in backup state, a tablespace's
activity is still written to the archive logs.  However, it's written
block-by-block rather than byte-by-byte.  So changing one record in a
tablespace that's being backed up will result in that record's entire
block being written to the archive area.


Sample Archive log command file for VMS:
----------------------------------------
$ dup = "backup/ignore=(noback,interl,label)/log"
$ sqldba
        CONNECT INTERNAL
        alter tablespace system begin backup;
        exit
$ dup u01:[oracle]ora_system.dbs tape1:ora_system.bck/sav
$ sqldba
        CONNECT INTERNAL
        alter tablespace system end backup;
        alter tablespace appl1 begin backup;
        exit
$ dup u02:[oracle]appl1.dbs tape1:appl1.bck/sav
$ sqldba
        CONNECT INTERNAL
        alter tablespace appl1 end backup;
        exit
$!
$! get archive logs
$ rename/log u03:[oracle.arcs]*.arc *.arclogs
$ rename/log u04:[oracle.arcs2]*.arc *.arclogs  !secondary arcs dir
$ sqldba
        CONNECT INTERNAL
        alter system switch logfile;
        exit
$ dup u03:[oracle.arcs]*.arclogs,u04:[oracle.arcs2]*.arclogs 
tape1:logs.bck/sav
$ del/log u03:[oracle.arcs]*.arclogs;0
$ del/log u04:[oracle.arcs2]*.arclogs;0
$!
$! get control file
$ sqldba
        CONNECT INTERNAL
        alter database backup controlfile to 'u01:[oracle]control.bkp' reuse;
        exit
$ dup u01:[oracle]control.bkp tape1:control.bck/sav


Note: The "alter system switch logfile" command is all but undocumented,
        (see pg 3-15 of the DBA guide.  It refers you to a nonexistent
        cross-reference).  It will NOT show up in the alert log.  Don't
        be alarmed by that; it does actually work.


Integrating the three methods.
==============================
Shutdown once a week.  If possible, run a full export immediately prior
to the shutdown.  If this is not possible because of time constraints,
perform the export on the previous night.  While down, perform a cold
backup of all Oracle-related files, then restart.  On all other nights,
perform a hot backup while the database is up using the methodology
described above.


=======
If at all possible in your O/S, it is also a good idea to shadow the 
disks on which your realtime and archived redo logs reside.
=======
Kevin Loney (kml@cellar.org) regularly writes the User Help column for
Oracle magazine.  In his spare time, he is an Oracle DBA in southeast PA.
Comments, questions, and job offers are always welcome.
=======

              Also, Charlottesville must be destroyed.

   These may not even be my opinions, let alone anyone else's.
  kml%cellar@tredysvr.tredydev.unisys.com - My other account is a Mercedes.