Re: Oracle Recovery Scenarios

In any case, the advice is hokey. They suggest that to recover from loss of

the init.ora, you should restore from a backup or start editing the default 
init.ora. Not a mention of the fact that the alert log contains a backup of 
the init.ora every time you start your instance.

I didn't go much further: you just can't summarise recovery scenarios pat like that.

Regards
HJR On Tue, 19 Aug 2003 14:52:03 GMT, Brian Peasland <dba_at_remove_spam.peasland.com> wrote:

> Are you aware that by posting such a document taken directly from
> Metalink that you have violated the terms of your support agreement?
> Specifically, the support agreement says:
>
> "You agree that access to MetaLink, including access to the internet
> Technical Assistance Request (iTARs) function, will be granted only to
> your designated Oracle technical support contacts and that the Materials
> may be used solely in support of your authorized use of the Oracle
> Programs for which you hold a supported license from Oracle. Unless
> specifically provided in your licensing or distribution agreement with
> Oracle, the Materials may not be used to provide services for or to
> third parties and may not be shared with or accessed by third parties."
>
> This is most likely why you see people say things like "Note 230829.1 on
> Metalink can help you". If you have access to Metalink, you'll know
> precisely where to go. If you don't have access to Metalink, nothing has
> been violated.
>
> Cheers,
> Brian
>
> Orietta Valota wrote:
>>
>> look the attach...
>>
>> bye Orietta
>>
>> quarkman wrote:
>> > On 19 Aug 2003 01:33:16 -0700, Ed <ed_zep_at_ntlworld.com> wrote:
>> >
>> >> Hi.
>> >>
>> >> Does anyone know of a list of Oracle disaster/recovery scenarios with
>> >> their solutions. What I have in mind is a flow diagram with something
>> >> like
>> >>
>> >> "control file missing" -----> solution
>> >>
>> >> Ideally it could fit on a single sheet. This is sounding a naive
>> >> question, but should the worst happen it would be great to not have
>> to
>> >> look through manuals.
>> >>
>> >> Does anyone know of a document like this?
>> >>
>> >> Thanks.
>> >>
>> >> Ed.
>> >>
>> >
>> >
>> > No, and sorry to say, I wouldn't tell anyone about it even if I did.
>> You
>> > simply can't do recovery with flow diagrams. There's a billion and one
>> > possible variations, and no flow diagram is ever going to cover all
>> > eventualities.
>> >
>> > Seriously: the only way to master backup and recovery is to understand
>> > the mechanisms Oracle uses to ensure database synchronisation. If you
>> > can visualise where your data files are at, what agrees with what, and
>> > what the application of redo does to things, then it really becomes
>> > relatively straightforward. I'm not saying that *you* are trying to
>> > avoid this sort of low-level understanding by producing flow charts,
>> but
>> > if your junior were to try and follow a generic flow diagram without
>> > understanding what is going on, it'll be a recipe for total disaster
>> > except in the simplest of cases.
>> >
>> > There might well be legal implications to producing such a document,
>> > too. If I lost hundreds of thousands of dollars because a
>> > flowchart-follower had just trashed my database, I'd be gunning for
>> the
>> > flowchart producer. Probably.
>> >
>> > If you really, really want mindless recoveries, then use RMAN. The
>> > commands don't need searching in the manual (at least not in 9i:
>> restore
>> > database; recover database; ...works nearly every time!).
>> >
>> > Seriously: try and understand what is going on during normal database
>> > operations, and then it becomes painfully obvious what needs to happen
>> > during recoveries. The commands aren't that taxing, either: recover
>> > database, recover datafile, recover tablespace and recover database
>> > until TIME | SEQUENCE | CANCEL. That's about it, really, unless you
>> are
>> > getting into the really exotic stuff... and then no amount of
>> flowchart
>> > is going to help you.
>> >
>> > By the way, you'll never need anything very complex to know how to
>> deal
>> > with a "control file missing" situation, because if you multiplex your
>> > control files, as you're supposed to do, the solution is simply to
>> copy
>> > one of the duplicates back into the slot vacated by the missing one.
>> ;-)
>> >
>> > Regards
>> > HJR
>>
>> ------------------------------------------------------------------------
>>
>>
>> Bookmark Fixed font Go to End
>>
>> Doc ID: Note:230829.1
>> Subject: Recover database after disk loss
>> Type: BULLETIN
>> Status: REVIEWED
>> Content Type: TEXT/PLAIN
>> Creation Date: 26-FEB-2003
>> Last Revision Date: 19-MAR-2003
>>
>> ***
>> This article is being delivered in Draft form and may contain
>> errors. Please use the MetaLink "Feedback" button to advise
>> Oracle of any issues related to this article.
>> ***
>>
>> PURPOSE
>> -------
>>
>> This article aims at walking you through some of the common
>> recovery techniques after a disk failure
>>
>> SCOPE & APPLICATION
>> -------------------
>>
>> All Oracle support Analysts, DBAs and Consultants who have a role
>> to play in recovering an Oracle database
>>
>> Loss due to Disk Failure
>> ------------------------
>> What can we lose due to disk failure:
>> A) Control files
>> B) Redo log files
>> C) Archivelog files
>> D) Datafiles
>> E) Parameter file or SPFILE
>> F) Oracle software installation
>>
>> Detecting disk failure
>> -----------------------
>> 1) Run copy utilities like "dd" on unix
>> 2) If using RAID mechanisms like RAID 5, parity information may mask
>> the disk failure and more vigorous check would be needed
>> 3) As always, check the Operating system log files
>> 4) Another obvious case would be when the disk could not be seen
>> or mounted by the OS.
>> 5) On the Oracle side, run dbverify if the file affected is a datafile
>> 6) The best way to detect disk failure is by running Hardware
>> diagnostic tools and OS specific disk utilities.
>>
>> Next Action
>> ------------
>> Once the type of failure is identified, the next step is to rectify
>> them.
>> Options could be:
>> (1) Replace the corrupted disk with a new one and mount them with
>> the same name (say /oracle or D:\)
>> (2) Replace the corrupted disk with a new one and mount them with
>> a different name (say /oracle1 as the new mount point)
>> (3) Decide to use another existing disk mounted with a different name
>> (say /oracle2)
>>
>> The most common methods are (1) AND (3).
>>
>> Oracle Recovery
>> ---------------
>> Once the disk problem is sorted, the next step is to perform recovery
>> at the Oracle level. This would depend on the type of files that is lost
>> (see
>> "Loss due to Disk Failure" section) and also on the type of disk
>> recovery done
>> as mentioned in the "Next Action" section above.
>>
>> (A) Control Files
>> ------------------
>> Normally, we have multiplexing of controlfiles and they are expected to
>> be
>> placed in different disks.
>>
>> If one or more controlfile is/are lost,mount will fail as shown below:
>> SQL> startup
>> Oracle Instance started
>> ....
>> ORA-00205: error in identifying controlfile, check alert log for more
>> info
>>
>> You can verify the controlfile copies using:
>> SQL> select * from v$controlfile;
>>
>> **If atleast one copy of the controlfile is not affected by the disk
>> failure,
>> When the database is shutdown cleanly:
>> (a) Copy a good copy of the controlfile to the missing location
>> (b) Start the database
>>
>> Alternatively, remove the lost control file location specified in the
>> init parameter control_files and start the database.
>>
>> **If all copies of the controlfile are lost due to the disk failure,
>> then:
>> Check for a backup controlfile. Backup controlfile is normally taken
>> using
>> either of the following commands:
>> (a) SQL> alter database backup controlfile to '/backup/control.ctl';
>> -- This would have created a binary backup of the current controlfile --
>>
>> -->If the backup was done in binary format as mentioned above, restore
>> the
>> file to the lost controlfile locations using OS copying utilities.
>> --> SQL> startup mount;
>> --> SQL> recover database using backup controlfile;
>> --> SQL> alter database open;
>>
>> (b) SQL> alter database backup controlfile to trace;
>> -- This would have created a readable trace file containing create
>> controlfile
>> script --
>>
>> --> Edit the trace file created (check user_dump_dest for the location)
>> and
>> retain the SQL commands alone. Save this to a file say cr_ctrl.sql
>> --> Run the script
>>
>> SQL> @cr_ctrl
>>
>> This would create the controlfile, recover database and open the
>> database.
>>
>> ** If no copy of the controlfile or backup is available, then create a
>> controlfile
>> creation script using the datafile and redo log file information. Ensure
>> that the
>> file names are listed in the correct order as in FILE$.
>> Then the steps would be similar to the one followed with cr_ctrl.sql
>> script.
>>
>>
>> Note that all controlfile related SQL maintenance operations are done in
>> the
>> database nomount state
>>
>>
>> (B) Redo logs
>> ---------
>> In normal cases, we would not have backups of online redo log files. But
>> the
>> inactive logfile changes could already have been checkpointed on the
>> datafiles
>> and even archive log files may be available.
>>
>> SQL> startup mount
>> Oracle Instance Started
>> Database mounted
>> ORA-00313: open failed for members of log group 1 of thread 1
>> ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
>> ORA-27041: unable to open file
>> OSD-04002: unable to open file
>> O/S-Error: (OS 2) The system cannot find the file specified.
>>
>> ** Verify if the lost redolog file is Current or not.
>> SQL> select * from v$log;
>> SQL> select * from v$logfile;
>>
>> --> If the lost redo log is an Inactive logfile, you can clear the
>> logfile:
>>
>> SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
>>
>> Alternatively, you can drop the logfile if you have atleast two other
>> logfiles:
>> SQL> alter database drop logfile group 1;
>>
>>
>> --> If the logfile is the Current logfile, then do the following:
>> SQL> recover database until cancel;
>>
>> Type Cancel when prompted
>>
>> SQL>alter database open resetlogs;
>>
>>
>> The 'recover database until cancel' command can fail with the following
>> errors:
>> ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
>> below
>> ORA-01194: file 1 needs more recovery to be consistent
>> ORA-01110: data file 1: '/ORACLE/ORADATA/H817/SYSTEM01.DBF'
>>
>> In this case , restore an old backup of the database files and apply the
>> archive logs to perform incomplete recovery.
>> --> restore old backup
>> SQL> startup mount
>> SQL> recover database until cancel using backup controlfile;
>> SQL> alter database open resetlogs;
>>
>>
>> If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and
>> ORA-1110 errors
>> occur, then you would have restore from an old backup and start the
>> database.
>>
>>
>> Note that all redo log maintenance operations are done in the database
>> mount state
>>
>>
>> (C) Archive logs
>> -----------------
>> If the previous archive log files alone have been lost, then there is
>> not much
>> to panic.
>> ** Backup the current database files using hot or cold backup which
>> would ensure
>> that you would not need the missing archive logs
>>
>> (D) Datafiles
>> --------------
>> This obviously is the biggest loss.
>>
>> (1) If only a few sectors are damaged, then you would get ora-1578 when
>> accessing those blocks.
>> --> Identify the object name and type whose block is corrupted by
>> querying dba_extents
>> --> Based on the object type, perform appropriate recovery
>> --> Check metalink Note:28814.1 for resolving this error
>>
>> (2) If the entire disk is lost, then one or more datafiles may need to
>> be
>> recovered .
>> SQL> startup
>> ORACLE instance started.
>> ...
>> Database mounted.
>> ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
>> ORA-01110: data file 3: '/ORACLE/ORADATA/H817/USERS01.DBF'
>>
>> Other possible errors are ORA-00376 and ORA-1113
>>
>> The views and queries to identify the datafiles would be:
>> SQL> select file#,name,status from v$datafile;
>> SQL> select file#,online,error from v$recover_file;
>>
>>
>> ** If restoring to a replaced disk mounted with the same name, then :
>> (1) Restore the affected datafile(s) using OS copy/restore commands from
>> the
>> previous backup
>> (2) Perform recovery based on the type of datafile affected namely
>> SYSTEM,
>> ROLLBACK or UNDO, TEMP , DATA or INDEX.
>> (3) The recover commands could be 'recover database', 'recover
>> tablespace'
>> or 'recover datafile' based on the loss and the database state
>>
>> ** If restoring to a different mount point, then :
>> (1) Restore the files to the new location from a previous backup
>> (2) SQL> STARTUP MOUNT
>> (3) SQL> alter database rename file '/old path_name' to 'new path_name';
>> -- Do this renaming for all datafiles affected. --
>> (4) Perform recovery based on the type of datafile affected namely
>> SYSTEM,
>> ROLLBACK or UNDO, TEMP , DATA or INDEX.
>> (5) The recover commands could be 'recover database', 'recover
>> tablespace'
>> or 'recover datafile' based on the loss and the database state
>>
>> The detailed steps of recovery based on the datafile lost and the Oracle
>> error
>> are outlined in the following articles :
>> Note:184327.1
>> Note.198640.1
>> Note:183327.1
>> Note:183367.1
>>
>> NOARCHIVELOG DATABASE
>> =====================
>> The loss mentioned in (A),(B) and (D) would be different in this case
>> wherever archive logs are involved.
>>
>> We will discuss the datafile loss scenarios here:
>>
>> (a) If the datafile lost is a SYSTEM datafile, restore the complete
>> database from the previous backup and start the database.
>> (b) If the datafile lost is Rollback related datafile with active
>> transactions,
>> restore from the previous backup and start the database.
>> (c) If the datafile contains rollback with no active rollback segments,
>> you can
>> offline the datafile (after commenting the rollback_segments parameter
>> assuming that they are private rollback segments) and open the database.
>> (d) If the datafile is temporary, offline the datafile and open the
>> database.
>> Drop the tablespace and recreate the tablespace.
>> (e) If the datafile is DATA or INDEX,
>> **Offline the tablespace and start the database.
>> **If you have a previous backup, restore it to a separate location.
>> **Then export the objects in the affected tablespace ( using User or
>> table level export).
>> **Create the tablespace in the original database.
>> **Import the objects exported above.
>>
>> If the database is 8i or above, you can also use Transportable
>> tablespace
>> feature.
>>
>>
>> (E) Parameter file
>> ---------------
>> This is not a major loss and can be easily restored. Options are:
>> (1) If there is a backup, restore the file
>> (2) If there is no backup, copy sample file or create a new file and add
>> the
>> required parameters. Ensure that the parameters db_name, control_files,
>> db_block_size, compatible are set correctly
>> (3) If the spfile is lost, you can create it from the init parameter
>> file
>>
>>
>> (F) Oracle Software Installation
>> ----------------------------
>> There are two ways to recover from this scenario:
>> (1) If there is a backup of the Oracle home and Oracle Inventory,
>> restore
>> them to the respective directories. Note if you change the Oracle Home,
>> the inventory would not be aware of thid new path and you would not be
>> able to apply patchsets. Also restore to the same OS user and group.
>>
>> (2) Perform a fresh Install
>>
>>
>> PRACTICAL SCENARIO
>> ==================
>>
>> In most cases, when a disk is lost, more than one type of file could be
>> lost.
>> The recovery in this scenario would be:
>> (1) A combination of each of these data loss recovery scenarios
>> (2) Perform entire database restore from the last backup and apply
>> archive logs to perform recovery. This is a highly preferred method
>> but could be time consuming.
>>
>>
>>
>>
>>
>> Note: For any issues or clarifications, call into Oracle Support
>>
>>
>> .
>> Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
>> Notices and Terms of Use.
>

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/