| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Anymore possibilities - disaster recovery
I don't really think this is *disaster* recovery ...
how about > "the building burns down and all tapes and hardware are in the
same building ?"
This is just basic backup and recovery ...
Disaster recovery in my eyes is :
- backup media cloning and storing in vault in secundairy location
- standby setup :
anyhow - if you want a list of backup/recovery scenario's maybe you can start from a list found on www.lazydba.com BACKUP PROCEDURES : It is a very impotant aspect of any database which should be planned carefully as recovery depends upon the back up strategy which are being followed. Backup strategy depends upon the mode of database. Different methods are adopted for the database running in archivelog mode or database running in no-archivelog mode .
Criteria :
[1] When database is running in archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find
the names
of datafiles and redo logfiles associated with the database. Use the
operating system
command to take the backup of these files. *
In init.ora, search for parameter log_archive_dest to find the location of archived files.
Use the operating system command to take the backup of these files.
Frequency: This backup has to be taken weekly.
Only Oracle database related file should be backedup. It will solve two
problems
[a] Downtime of database will be less.
[b] Retrival from cartridge will take less time.
[c] Less numbers of cartridges will be required .
File System Backup : Generally its frequency should be low. It will act as a backup for all the files (Oracle+O.S.+Other). It will be needed if all the disks crash. If you are creating important files on server then its frequency should be increased as decided by the site incharge.
Hot backup:
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles views to find the names of
datafiles associated with the database.
Use the operating system command to take the backup of these files.*
In init.ora, search for parameter log_archive_dest to find the location of archived files.
Use the operating system command to take the backup of these files. *
Frequency: This backup has to be taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.
It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .
For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.
Recovery :
In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems.
For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery).
For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.
[2] When database is running in no archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find
the names
of datafiles and redo logfiles associated with the database. Use the
operating system
command to take the backup of these files. Ideally this backup should be
taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.
It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .
For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.
Recovery :
It is a very important process and it should be done very carefully. In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems. For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery). For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.
In Unix :
[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
find / -name [pattern] -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
[b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
tar -cvf /
Complete file system backup :
In Unix :
[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
or
[b] To copy all files of unix to backup device
tar -cvf /
In Window NT :
Use backup utility to copy the necessary files.
Commands to be used in restoring file(s) from backup device :
In Unix :
[a] cpio -icBv < [/dev/rmt0.1|/dev/rmt0]
or
cpio -icBv "[pattern]" < [/dev/rmt0.1|/dev/rmt0]
[b] tar -xvf < [/dev/rmt0.1|/dev/rmt0]
In Window NT :
Use restore utility to copy the necessary files from backup device.
Annexure - 1
REQUIREMENT
[1] The script which will recreate the objects in the datafile like script
which will create indexex.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN (5 MIN+ TIME TAKEN TO CREATE INDEXES)
NON-ESSENTIAL DATAFILES
DATAFILE OF INDEX TABLESPACE, TEMPORARY TABLESPACE.
SOLUTION
Shutdown the database.(shutdown immediate).
Take complete backup of current database.
Startup mount
Query the v$recover_file view along with v$datafile with a join on file# and
note down the name of file
say it is /prodebs/test/ind.dbf.
Alter database datafile '/prodebs/test/ind.dbf' offline;
(if database is in noarchivwlog mode command will be
Alter database datafile '/prodebs/test/ind.dbf' offline drop; )
Alter database open;
Drop tablespace user_index including contents;
Create tablespace user_index
datafile '/prodebs/test/ind.dbf' size 1M;
Run the script which will built indexes*.
Shutdown the database and take backup if necessary.
Startup.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 MIN.
SOLUTION
Shutdown the database.(shutdown).
Startup mount
Query v$logfile view and find which member has become invalid.
Query v$log view and find which group is current and size of group members
(say it is b).
If the member of current log group (say it is 1) is corrupted issue the
following commands :
Alter system switch logfile
If you can add one more member to corrupted log group ie maximum log member
is not reached add one more
member to that group
Alter database add logfile member 'filespec' to group 1;
Shutdown the database
Startup the database
If you can not add one more member to corrupted log group
create one more log group with equal members and size of non corrupted log
group.
Alter database group 3 ('filespec','filespec') size b;
Drop corrupted log group.
alter database drop logfile group 1;
Manually remove other members of this corrupted log group (ie rm in unix)
Shut down the database
Startup the database
REQUIREMENT
For full recovery, database should be in archivelog mode.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION
Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is
'/prodebs/test/user_odc.dbf')
Now issue the following commands in the given order :
alter database datafile '/prodebs/test/user_odc.dbf' offline;
alter database create datafile '/prodebs/test/user_odc.dbf' as
'/prodebs/test/user_odc1.dbf';
(removed file) (new file)
alter database datafile '/prodebs/test/user_odc1.dbf' online;
alter database recover datafile '/prodebs/test/user_odc1.dbf'; or recover
database
It will generally recover the database if you have all the archived file
with you
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
REQUIREMENT
For full recovery, database should be in archivelog mode.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 mins.
SOLUTION
Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is
'/prodebs/test/user_odc.dbf')
Copy the archived datafile (old one that is in backup) and give following
commands :
recover database;
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
REQUIREMENT
For recovery, database should be in archivelog mode.
CONDITION
Recovery will be incomplete.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION
Shutdown the database.(shutdown).
Copy all your datafiles from backup except control file.
Startup mount;
Copy the archived datafile (old one that is in backup) and give following
commands :
recover database until cancel
alter database open resetlogs;
Shutdown the database and take necessary backup if required.
Start the database.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION when Problem has occured on (a) on monday morning
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the
backup.
Start the database. There is no data loss.
SOLUTION when Problem has occured on (a) on thursday morning. Here data loss will occur.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the
backup.
Start the database. There is data loss for monday, tuesday and wednessday.
Ask user to reenter the data.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[i] SOLUTION when Problem has occured on thursday morning and incremental
backup is taken.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the
backup.
Start the database.
Apply incremental export using import file starting from monday to
wednesday.There is no data loss.
[ii] SOLUTION when Problem has occured on thursday morning and daily
complete database logical
backup is taken.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the
backup.
Start the database.
Apply wednesday complete export backup. There is no data loss.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing system database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -1 (DATABASE RECOVERY)
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.
SOLUTION -2 (DATAFILE RECOVERY)*
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile '' offline;
Alter database open;
recover datafile ''
Alter database datafile '' online;
Database is ready for use.
SOLUTION -3 (TABLESPACE RECOVERY)
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile '' offline;
Alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online ;
Database is ready for use.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -
Shutdown the database.(shutdown).
Take complete backup of current database.
Comment the ROLLBACK_SEGMENT parameter or assign ROLLBACK_SEGMENT=(SYSTEM)
in init.ora file before startup.
Copy the missing rollback segment database file(s).
Startup mount exclusive;
alter database datafile '' offline;
alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online;
Query dba_rollback_segs (column name segment_name, status) and note down the
name of segment
name having status recovery (say they are r01, r02,r03).
alter rollback segment r01 online; alter rollback segment r02 online; alter rollback segment r03 online;
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the all the database files from latest offline or online backup.
Startup mount exclusive;
recover database until cancel;
alter database open resetlogs;
shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION - (Oracle Version is 7.2 or more)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$backup and get file # having status active. Now from
v$datafile get the name of
file which is active in v$backup
alter database datafile '' end backup;
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
SOLUTION - (Oracle Version is 7.1 )
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived
logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
NB : Loss of control file when there is a backup and it is not mirrored but
it is before last reset log option.
(suppose database is open on day x with alter database startup resetlogs.So
your control file should
be before xth day )
startup/recover database using backup controlfile
Oracle error no is ora 1190 :control file or data file 1 is from before the
last RESETLOGS
ora 1110 :name of system datafile.
[b] Loss of control file when there is a backup and it is not mirrored but
not before last reset log option.
(suppose database is open on day x with alter database startup resetlogs.So
your control file should
be after xth day )
ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/prodebs/test/sys_odc.dbf' ORA-01207: file is more recent than control file - old control file
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[c] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION - (Loss of control file when it is mirrored)
Shutdown the database.(shutdown).
Copy the second control file to this disk.
Rename this control file to the lost one.
Start the database.
Database is ready for use.
SOLUTION - (Loss of all the control file(s) )
Shutdown the database.(shutdown).
run backup of controlfile which you might have taken using the following
command :
alter database backup controlfile to trace;
It creates a script. After editing that script it looks like this :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'/prodebs/test/redo_odc11.dbf',
'/prodebs/test/redo_odc12.dbf'
) SIZE 50K,
GROUP 2 (
'/prodebs/test/redo_odc21.dbf',
'/prodebs/test/redo_odc22.dbf'
) SIZE 50K
DATAFILE
'/prodebs/test/sys_odc.dbf', '/prodebs/test/sys_odc1.dbf', '/prodebs/test/user_odc.dbf', '/prodebs/test/temp_odc.dbf', '/prodebs/test/rbs_odc.dbf', '/prodebs/test/ind.dbf'
SOLUTION - (Loss of control file when there is a backup and it is not mirrored)
Shutdown the database.(shutdown).
Copy the old control file to this disk.
startup mount exclusive;
If you have any tablespace which is read only, take all the datafile
offiline related to this tablespace.
recover database using backup controlfile;
Offline datafile should bring to online status. (alter database datafile '<
name of datafile>' online;)
(for read only tablespace)
alter database open resetlogs;
Shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.
Oracle Error is : ora 00376 file # can not be read at this time. ora 01110 name of datafile.
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 5 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION - (Oracle Version is 7.2 or more)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup open;
Query the view v$datafile get the name of file which you want to resize.
alter database datafile '' resize [m/k];
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.
SOLUTION - (Oracle Version is 7.1 )
[a] Restore the datafile and apply recovery. Resizing is not possible.
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the deleted datafile
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived
logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
[b] If deleted datafile is not available in backup .
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database add datafile '' as '';
recover database ;
( This may take significant amount of time if a large number of archived
logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
[c] Rebuild tablespace
Requirement : Logical backup is there.
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile '' offline;
alter database open;
alter tablespace offline;
drop tablespace ;
create tablespace datafile '' size [m/k];
alter tablespace online;
use export / import method to recover the loss data.
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.
Events
[1]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is
taken.
c- Loss of data file
[2]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is not
taken.
c- Loss of data file .
SOLUTION - [1]
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafiles.
Startup mount exclusive;
Recover database;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Advantage : All the data will be recovered.
SOLUTION - [2]-i
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile '' offline;
alter database open;
Export all the data from the tablespace (all objects in missing file will be
inaccessible),
Drop and recreate the tablespace.
Import all the data taken from tablespace.
recover datafile '' ;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Disadvantage : All the data will be lost that was entered in datafile 5;
SOLUTION - [2]-ii
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafile and control file only. Do not
copy redo log file.
Startup mount exclusive;
Recover database;
alter database open resetlogs;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Disadvantage : All the data will be lost that after event b.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database create datafile '' ;
recover datafile '' ;
alter database open;
shutdown the database.
Start the database.
Database is ready for use.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database until time '' ;
alter database open resetlogs;
shutdown the database.
Take a cold backup of database;
Start the database.
Database is ready for use.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION - 1
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
alter database open ;
recover tablespace ;
alter tablespace online;
Shutdown the database.
Start the database.
Database is ready for use.
SOLUTION - 2 (better)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$datafile and note which datafile is offline say it is .
alter database datafile <'a'> online;
recover database;
alter database open ;
shutdown the database
Start the database.
Database is ready for use.
"godmann" <allanwtham_at_yahoo.com> wrote in message news:95cd51c.0202172334.79e466e_at_posting.google.com...
> Hi there, > > I am helping to compile a *all-possible* crash scenarios. Do you think > there is anything more than the following? Once compiled, I think it will > help a lot people out there to recover their database with moreconfidence!
> > The faq is located at Jonathan's site by the way! > > > > How do I recover from ... > > loss of a datafile that belongs to the SYSTEM tablespace > loss of a datafile that belongs to Rollback Segment tablespace > loss of a datafile that belongs to an Index-only tablespace > loss of a datafile that belongs to a TEMP tablespace > loss of a datafile that belongs to READ-ONLY tablespace > loss of a datafile that belongs to a typical data tablespace > How do I recover in a situation where there is no datafile backup > loss of one member of online redo log group > loss of an inactive redo log group > loss of an active redo log group > loss of an unarchived online log file > loss of a multiplexed control file member > complete loss of a control file > > > > Allan W. Tham > DBAReceived on Mon Feb 18 2002 - 13:29:56 CST
 |
 |