DBA Tips Archive for Oracle

  


RESTORE / RECOVERY Examples

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Trial Recovery
  2. Complete Restore / Recover Example
  3. Block Media Recovery



Trial Recovery

You can determine the outcome of a recovery before actually doing it for real by appending the word "test" to any recover command. The following command will cause Oracle to perform a dry-run of a recovery without actually changing any data:
SQL> recover database until cancel test;

NOTE: There are several restrictions to keep in mind when performing a test recovery. First, the entire recovery must be able to complete in memory so you must configure a database buffer cache large enough. Also, any DDL statements which cause a control file update (e.g. ALTER TABLESPACE ... ADD DATAFILE;) will cause the trial recovery to halt.



Complete Restore / Recover Example

In this example, I want to walk through a complete restore and recover example of a lost database. The Oracle SID is "TARFDB" and I had been doing complete (full) online backups. Note that I simulated a complete lost database; including all datafiles, control files, online and archive redo log files. The backups I performed had the "CONTROLFILE AUTOBACKUP" feature turned on. Also, I would first perform a database backup and archive redo log backups.
% ORACLE_SID=TARGDB
% export ORACLE_SID

% rman target backup_admin/backup_admin

RMAN> set dbid=2498937635

executing command: SET DBID

RMAN> startup nomount

Oracle instance started

Total System Global Area     521228768 bytes

Fixed Size                      455136 bytes
Variable Size                486539264 bytes
Database Buffers              33554432 bytes
Redo Buffers                    679936 bytes

RMAN> set controlfile autobackup format for device type disk to '/db1/orabackup/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT 

RMAN> restore controlfile from autobackup;

Starting restore at 25-JAN-04

using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20040125
channel ORA_DISK_1: autobackup found: /db1/orabackup/c-2498937635-20040125-02
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=/u03/app/oradata/TARGDB/control01.ctl
output filename=/u04/app/oradata/TARGDB/control02.ctl
output filename=/u05/app/oradata/TARGDB/control03.ctl
Finished restore at 25-JAN-04

RMAN> mount database;

database mounted

RMAN> restore database;

Starting restore at 25-JAN-04

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u06/app/oradata/TARGDB/undotbs01.dbf
restoring datafile 00006 to /u10/app/oradata/TARGDB/xdb01.dbf
restoring datafile 00007 to /u09/app/oradata/TARGDB/indx01.dbf
restoring datafile 00008 to /u10/app/oradata/TARGDB/tools01.dbf
restoring datafile 00009 to /u10/app/oradata/TARGDB/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/db2/orabackup/backup_DB_TARGDB_S_3_P_1_T_516380104 tag=TAG20040125T145504 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u08/app/oradata/TARGDB/system01.dbf
restoring datafile 00003 to /u10/app/oradata/TARGDB/cwmlite01.dbf
restoring datafile 00004 to /u10/app/oradata/TARGDB/drsys01.dbf
restoring datafile 00005 to /u10/app/oradata/TARGDB/odm01.dbf
restoring datafile 00010 to /u10/app/oradata/TARGDB/perfstat01.dbf
restoring datafile 00011 to /u10/app/oradata/TARGDB/example01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/db1/orabackup/backup_DB_TARGDB_S_2_P_1_T_516380104 tag=TAG20040125T145504 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 25-JAN-04

RMAN> recover database;

Starting recover at 25-JAN-04
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restored backup piece 1
piece handle=/db1/orabackup/backup_DB_TARGDB_S_5_P_1_T_516381887 tag=TAG20040125T152447 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=/u07/app/oradata/TARGDB/archive/arch_t1_s11.dbf thread=1 sequence=11
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: restored backup piece 1
piece handle=/db2/orabackup/backup_DB_TARGDB_S_6_P_1_T_516381887 tag=TAG20040125T152447 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=/u07/app/oradata/TARGDB/archive/arch_t1_s12.dbf thread=1 sequence=12
unable to find archive log
archive log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2004 15:59:41
RMAN-06054: media recovery requesting unknown log: thread 1 scn 140830

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

NOTE:

The resetlogs option used to open the database in the above example, will create a new incarnation of the database. It is critical to take a complete backup of the database after performing a resetlogs.

You do, however, get to keep the same DBID. As in this example, the DBID remains to be set to (2498937635), as shown in the following:

% rman target backup_admin/backup_admin

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TARGDB (DBID=2498937635)



Block Media Recovery

Oracle introduced the ability to perform block level recovery in 9i. The following syntax can be used to perform block level recovery:
blockrecover [datafile | device | tablespace];

The following example will recovery 7 different blocks from two different data files:

RMAN> blockrecover datafile 14 block 1,2,3,4,5 datafile 34 block 1048, 1049;

Starting blockrecover at 04-JAN-05
using channel ORA_DISK_1


starting media recovery
media recovery complete

Finished blockrecover at 04-JAN-05


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 19-May-2009 14:25:02 EDT
Page Count: 11007