Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Recovery Scenarios

Re: Oracle Recovery Scenarios

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Tue, 19 Aug 2003 14:52:03 GMT
Message-ID: <3F423993.35BDE478@remove_spam.peasland.com>


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.

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"
Received on Tue Aug 19 2003 - 09:52:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US