Next: Listing Backups Up: Using RMAN Previous: Basic RMAN Backup Contents

Basic Recovery

To see how RMAN can be useful for recovery, lets take a database and damage it. Lets simulate a tablespace being deleted because of a bad script or stupid DBA and then try to recover the database.

[oracle@vixen oracle]$ mv /u02/oradata/cuddle/users01.dbf /u02/oradata/cuddle/users01.dbf.oops

Okey, there is our disaster. Lets connect to RMAN and look for suitable backups to recover.

[oracle@vixen oracle]$ rman nocatalog target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: CUDDLE (DBID=251015092)
using target database controlfile instead of recovery catalog
RMAN> list backup;

List of Backup Sets
===================
.........
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    528M       DISK        00:01:43     02-NOV-04      
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20041102T134437
        Piece Name: /u01/app/oracle/product/10.1.0/db_1/dbs/05g438u6_1_1
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1267667    02-NOV-04 /u02/oradata/cuddle/system01.dbf
  2       Full 1267667    02-NOV-04 /u02/oradata/cuddle/undotbs01.dbf
  3       Full 1267667    02-NOV-04 /u02/oradata/cuddle/sysaux01.dbf
  4       Full 1267667    02-NOV-04 /u02/oradata/cuddle/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    2M         DISK        00:00:03     02-NOV-04      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20041102T134437
        Piece Name: /u01/app/oracle/product/10.1.0/db_1/dbs/06g4391f_1_1
  Controlfile Included: Ckp SCN: 1267704      Ckp time: 02-NOV-04
  SPFILE Included: Modification time: 15-OCT-04

RMAN>

We can see that we have good and current backups of this database avalible. Lets now try to recover in the basic way.

MAN> restore datafile '/u02/oradata/cuddle/users01.dbf';

Starting restore at 02-NOV-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 00004 to /u02/oradata/cuddle/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.1.0/db_1/dbs/05g438u6_1_1 tag=TAG20041102T134437
channel ORA_DISK_1: restore complete
Finished restore at 02-NOV-04

RMAN> recover datafile '/u02/oradata/cuddle/users01.dbf';

Starting recover at 02-NOV-04
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 02-NOV-04

RMAN>

Here, because the controlfiles and spfile are in tact, we can simply tell RMAN to restore the missing datafile, specifying which datafile by it's fully qualified path (which you can also see in your "list backup;"). Once the datafile is restored, we can recover it to ensure it's consistant. Once your done, you'll either want to bring the datafile and tablespaces online using alter statements, or at the very least use SQL*Plus to verify that the tablespaces are online by looking at the Oracle data dictionary.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> select FILE#,STATUS,ENABLED,NAME from v$datafile;

     FILE# STATUS  ENABLED
---------- ------- ----------
NAME
---------------------------------
         1 SYSTEM  READ WRITE
/u02/oradata/cuddle/system01.dbf

         2 ONLINE  READ WRITE
/u02/oradata/cuddle/undotbs01.dbf

         3 ONLINE  READ WRITE
/u02/oradata/cuddle/sysaux01.dbf

         4 OFFLINE READ WRITE
/u02/oradata/cuddle/users01.dbf

In this above case the tablespace is online but we find the datafile is offline. Lets just fix that up by using an alter statement:

SQL> alter database datafile '/u02/oradata/cuddle/users01.dbf' online;
Database altered.

SQL> alter tablespace USERS online;
Tablespace altered.

We didn't need to alter the tablespace because it was already online, but I did it any way to demonstrate. Once you've successfully altered the database to bring both the datafile and the tablespaces online you'll want to run the queries above again to double check.

Next: Listing Backups Up: Using RMAN Previous: Basic RMAN Backup Contents

2005-02-10