Re: Recovery Plan ???

This is a multi-part message in MIME format.

--------------37A95EC36CD7
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Sean Kubovcik wrote:

> 
> I am documenting a recovery plan and I have a couple of questions:
> 
> Can you recover a database that is in archivelog mode with a cold
> backup and the archive logs?
> 
> Does any one have a recovery plan documented that details different
> recovery procedures for different database failures, ie. process to
> recover from loss of datafiles or loss of control files?

Sean, a while ago we demostrated the following recovery procedures. The database is running in archivelog mode. All worked like a charm, and I have a lot more details if you need.

-- 
[ Simon Goland       B-)>     sg_at_mda.ca ]
[   Without action there is no change   ]

--------------37A95EC36CD7
Content-Type: text/plain; charset=us-ascii; name="DB-crash-ideas.TXT"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="DB-crash-ideas.TXT"


There are several situations where a need for recovery arises:

1. Loss of datafiles:
  a. datafiles of SYSTEM tablespace
  b. datafiles of other tablespace
2. Loss of an online redo log file
3. Loss of an archived redo log file
4. Loss of all control files
5. User error (like DROPped a table)


We will have to simulate these failures, in order to show proper recovery
functioning. Note that it is important to emphasize how a failure happened
(i.e. hardware, software, act of supreme powers). We just have to show that
if any of the above failures occur, we can bring the database back to
operational mode.


CRASH database components:

   Tablespaces SYSTEM, USERS, TEMP, RBS (each has its own datafile)
   Tables in USERS - PERSON, ADDRESS
   User SG


Recovery scenarios:
-------------------

1.a. Loss of a datafile of SYSTEM tablespace

Scenario: SYSTEM datafile gone, or SYSTEM table(s) DROPped.
How:      rm <SYSTEM-datafile-name>  or
          DROP <some SYSTEM tables>
Recovery:


   - Oracle shutdown (ABORT)

   - restore datafile from backup

   - Restart instance (STARTUP MOUNT)

   - Recovery using redo log files

   - OPEN database.


1.b. Loss of a datafile of USERS tablespace

Scenario: USERS datafile gone.
How:      rm <USERS-datafile-name>
Recovery:


   - Start a DB instance (only if previous was shutdown)

   - Restore datafile from backup

   - Recovery using redo log files


2. Loss of an online redo log

Scenario: Online redo log gone.
How:      rm <redo-log-file-name>
Recovery:


   - DROP the redo log and ADD a new one.


3. Loss of an archived redo log

Scenario: Archived redo logs gone.
How:      rm <archived-redo-log-file-name>
Recovery:


   - No immediate danger to the database.

   - Just perform a full backup of all datafiles to make sure the

     lost archived log is not needed.

4. Loss of all control files

Scenario: All control files gone.
How:      rm <all-control-file-names>
Recovery:


   - Shutdown ABORT current DB instance (if still running)

   - Restore/recreate control file from trace file

   - Restart database.


5. Power off failure

Scenario: Power Off
How:      Unplug Unix box, kill Oracle background process, SHUTDOWN ABORT
Recovery:


   - [SHUTDOWN ABORT might still be needed]

   - Restart DB (which will invoke Oracle's automatic recovery).


6. User error

Scenario: User table gone.
How:      DROP TABLE person
Recovery: 


   - Backup database.

   - Use last export to reimport the missing table for the user.


--------------37A95EC36CD7--