Re: backup/recovery of very dynamic DB

Jerry Murr (jdm2_at_lsdn.ksc.nasa.gov) wrote:
> I have a database where Oracle userid's and tablespaces (dedicated
> to holding data for those userid's) are added and deleted on a
> daily (even hourly) basis. We've written software to automate the
> creation/deletion of these userid's and tablespaces, so that
> a base of authorized clients can do this without any DBA intervention.
 

> Now that it's beginning to work, we are worrying about backup and
> recovery. If we take weekly cold backups and run in Archivelog Mode,
> will the creation/deletion of tablespaces be recorded, such that
> we could start recovery by restoring the cold backup and apply
> the redo logs to get back to the point of system failure? I'm sure
> this strategy works if you have static tablespaces (and datafiles),
> but what about this situation?
 

> Is there anything we should be doing in the create/delete software
> to ensure we can recover if the system dies (like recopying the
> control files after adding or deleting a tablespace)?

you need to backup of the controlfile everytime you add or delete a tablespace/datafile. the creation and deletion of tablespaces are recorded in the redo logs (since ultimately, such actions are simply DML operations on the base data dictionary tables.) However, the actual creation of a datafile is not recorded in the redo, so you may need to prep a db prior to recovery, depending on what you lost:

  o lost all controlfiles.

    you will need to restore the latest backup of your controlfile     which must have all the active datafiles at your instance     crashed b/c of the loss of all your controlfiles.

    after the restore, you will need to do the following in svrmgr:

      startup mount
      recover database using backup controlfile until cancel;
      < feed it the online redo log files until it says media recovery
        is complete. >
      alter database open resetlogs;

    do a full hot (or cold) backup of your db.

    Note that your controlfile MUST have all the entries of the     datafiles that belong to the database at the time of the crash.     If you use an older controlfile with a incomplete list, you will NOT     be able to recover those datafiles not listed in the controlfile.     So if you don't have a good controlfile backup after the last     datafile/tablespace added, you will have to create your controlfile     from scratch using the "CREATE CONTROLFILE..." command. So, if you     have already automated the tbspace creation/deletion, I strongly urge     you to add some coding to do a controlfile backup right after the     tbspace changes.

  o lost a datafile you created AFTER your latest weekly backup.

    since you don't have this datafile in your weekly backup (for     obvious reasons...) you will need to create an empty datafile and     allow Oracle to recreate the contents of the datafile using the archive     logs. So do the following in svrmgr:

      startup mount
      alter database create datafile '<filename>';
      recover database;
      alter database open;

  o lost a datafile you created BEFORE your latest weekly backup.

    this is a standard restore and recovery. So after you restore the     lost datafile from your weekly backup and use svrmgr:

      startup mount;
      recover database;
      alter database open;

  o lost all the members in a redo log group.

    this is a standard recovery after a redo log group loss, so how much     data you loss will be determined by what state the loss redo group     was in at the time of the loss...consult the Oracle7 Server Admin     guide for all the details.

The above assumes you always want a full recovery (or with the last senerio, as fully as you can get). It's not too difficult to extend the above into point in time recovery situations, since all that means is that you apply up to the redo/time/SCN that you want and then stop and open the db with resetlogs.

Hope this helps,

Johnny Chan
Independent Oracle Specialist Received on Thu Apr 10 1997 - 00:00:00 CDT