Skip to content

RMAN: RECOVERY SCENARIOS RELATED TO DATABASE

RMAN  performs backup and recovery tasks on the databases and automates administration of the backup strategies. It greatly simplifies backing up, restoring, and recovering database files.

Some of the database recovery scenarios are listed below.

 

Below cases are discussed in this post:

 

Case 1: ALL DATA FILES ARE LOST

Case 2: FEW BLOCKS IN DATAFILE GOT CORRUPTED

Case 3: ONLY ONE DATA FILE IS LOST/CORRUPTED

Case 4:  ALL DATAFILES OF A TABLESPACE GOT CORUPTED/LOST 

Case 5: SPFILE/PFILE LOST

 

 


 

CASE 1 : ALL DATA FILES ARE LOST

Assumption :

you have good backups of your database
you have access to all redo archive files generated after the backup was taken.
you have all the redo required to recover the database datafiles.
You can perform a complete database-level recovery in this situation with either the current control file or a backup control file.

i) Using current control file

In this situation, we simply start up the database in mount mode, issue the restore and recover commands, and then open the database:

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Mon Feb 24 10:04:14 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> restore database;

Starting restore at 24-FEB-2014 10:04:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/DB11G/oradata/brij/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/DB11G/oradata/brij/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/DB11G/oradata/brij/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/DB11G/oradata/brij/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/DB11G/oradata/brij/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T140141_9jdyt91f_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T140141_9jdyt91f_.bkp tag=TAG20140220T140141
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 24-FEB-2014 10:06:35

 

RMAN> recover database;

Starting recover at 24-FEB-2014 15:23:16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:09

Finished recover at 24-FEB-2014 15:23:26

RMAN> alter database open;

database opened

Remember that RMAN will look in older backups if it can’t find a backup piece or if corruption is detected. RMAN will search through backup history until it locates a good backup or until it exhausts all possibilities. This feature is called restore failover.


 

What about Read only and temporary tablespaces?

Read only tablespaces:

NOTE: By default, the restore command skips datafiles associated with read-only tablespaces. If you want read-only tablespaces restored, then you must use the check readonly command.

RMAN> restore database check readonly;

 

Temporary Tablespaces:

You don’t have to restore or re-create missing locally managed temporary tablespace tempfiles. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace tempfiles.


 

If you wanna restore form some specific backups which you tagged:

RMAN> restore database from tag WEEKLY_BACK


 

How to use until clause with RMAN restore command

You can also tell RMAN to restore datafiles from a point in the past using the until clause of the restore command in one of the following ways:

A) Until SCN

If you know the SCN in a backup piece that you want to restore from

Example:

RMAN> restore database until SCN 5634174;

 

B) Until sequence

If you know the log sequence number that you want to restore up to

RMAN> restore database until sequence 17;

 

C) Until restore point

If you’ve created restore points, then you can also use the restore point name

RMAN> restore database until restore point WEEKLY_BACK;

 

D) Until time

You can also specify a point in time from which you want RMAN to restore an older backup.
RMAN> restore database until time ‘sysdate – 5′;

RMAN> restore database until time “to_date(’08-jan-2014 13:00:00′, dd-mon-rrrr hh24:mi:ss’)”;


 

ii) Using backup control file

 

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

You are required to open your database with the open resetlogs command anytime you use a backup control file during a recovery operation.


 

CASE 2: FEW BLOCKS IN DATAFILE ARE CORRUPT

There can be a corrupt block in a large datafile in your database which RMAN can easily report. Now it would take significant effort and time to perform the traditional restore and recover of a large datafile. So instead we will just recover the corrupt block and not the entire datafile.

For Oracle Database 10g or Oracle9i Database, use the blockrecover command to perform block media recovery.

As for Oracle Database 11g or newer, we will use the recover datafile … block command as shown below:
ONLY FOR TESTING FIRST WE CREATED SOME DB BLOCK CORRUTION 🙂

 

SYS@brij > SELECT header_block FROM dba_segments WHERE segment_name=’EMP’;

HEADER_BLOCK
————
146

$ dd of=/u01/oracle/DB11G/oradata/brij/users01.dbf bs=8192 conv=notrunc seek=147 << EOF
> corruption test
> EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000159796 s, 100 kB/s

 

SYS@brij > ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

 

SYS@brij > select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’

A) CHECK CORRUPTION USING RMAN

RMAN will automatically detect corruption in blocks whenever a backup or backup validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view.

Using RMAN command:

RMAN> backup validate database archivelog all;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 FAILED 0 18 667 1086086
File Name: /u01/oracle/DB11G/oradata/brij/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 90
Index 0 39
Other 1 493
In Alert Log:

Wed Feb 26 13:53:28 2014
Hex dump of (file 4, block 147) in trace file /u01/oracle/DB11G/diag/rdbms/brij/brij/trace/brij_ora_6734.trc
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during validation
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/brij/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/brij/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/brij/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/brij/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/brij/users01.dbf. found same corrupt data

In V$DATABASE_BLOCK_CORRUPTION view:

RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.

SYS@brij > select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE#              CORRUPTIO
———- ———- ———- —————— ———
4     147    1             0                         CORRUPT

Using DBV:

dbv file=/u01/oracle/DB11G/oradata/brij/users01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 – Production on Wed Feb 26 14:06:26 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = /u01/oracle/DB11G/oradata/brij/users01.dbf
Page 147 is marked corrupt
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during dbv:
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled

DBVERIFY – Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 90
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 492
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1086086 (0.1086086)

B) CORRECT DATA BLOCK CORRUPTION

Database can be in mounted or open when you are performing block-level recovery. Also you do not have to take the datafile being recovered offline. Block-level media recovery allows you to keep your database available hence reducing the mean time to recovery since only the corrupt blocks are offline during the recovery.

RMAN CAN NOT perform block-level recovery on block 1 (datafile header) of the datafile.

RMAN> recover datafile 4 block 147;

Starting recover at 26-FEB-2014 14:07:41
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T134738_9jwr7wj3_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T134738_9jwr7wj3_.bkp tag=TAG20140226T134738
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-FEB-2014 14:07:45

C) VERIFY

SYS@brij > select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SYS@brij > select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20


 

 

CASE 3: ONLY ONE DATA FILE IS LOST

You have one datafile that has experienced media failure. You don’t want to restore and recover the entire database or all datafiles associated with the tablespace. You just want to restore and recover the datafile that experienced media failure.

In this example we lost /u01/oracle/DB11G/oradata/brij/users01.dbf

SCENARIO 1: DATABASE IS NOT OPEN

Here you restore/recover datafile from backup

A) CHECK IF YOU HAVE BACKUP PRESENT

RMAN> list backup;

using target dtabase control file instead of recovery catalog

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————–
1 Full 1.10G DISK 00:01:51 10-FEB-2014 07:43:40
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140210T074149
Piece Name: /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_10/o1_mf_nnndf_TAG20140210T074149_9hkwsydt_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————– —-
1 Full 1104989 10-FEB-2014 07:41:50 /u01/oracle/DB11G/oradata/brij/system01.dbf
2 Full 1104989 10-FEB-2014 07:41:50 /u01/oracle/DB11G/oradata/brij/sysaux01.dbf
3 Full 1104989 10-FEB-2014 07:41:50 /u01/oracle/DB11G/oradata/brij/undotbs01.dbf
4 Full 1104989 10-FEB-2014 07:41:50 /u01/oracle/DB11G/oradata/brij/users01.dbf
5 Full 1104989 10-FEB-2014 07:41:50 /u01/oracle/DB11G/oradata/brij/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————–
2 Full 9.36M DISK 00:00:04 10-FEB-2014 07:43:49
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140210T074149
Piece Name: /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_10/o1_mf_ncsnf_TAG20140210T074149_9hkwxnn9_.bkp
SPFILE Included: Modification time: 10-FEB-2014 07:41:01
SPFILE db_unique_name: BRIJ
Control File Included: Ckp SCN: 1105044 Ckp time: 10-FEB-2014 07:43:45

B) MOUNT DATABASE

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Tue 04-MAR-2014 14:39:39

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

 

C) RESTORE DATAFILE

 

RMAN> restore datafile 4;

Starting restore at 04-MAR-2014 14:41:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/DB11G/oradata/brij/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_03_04/o1_mf_nnndf_TAG20140304T143708_9kdodwo1_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_03_04/o1_mf_nnndf_TAG20140304T143708_9kdodwo1_.bkp tag=TAG20140304T143708
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 04-MAR-2014 14:41:14

 

D) RECOVER DATAFILE

 

RMAN> recover datafile 4;

Starting recover at 04-MAR-2014 14:41:50
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 04-MAR-2014 14:41:51

 

E) OPEN DATABASE

RMAN> alter database open;

database opened

 

 

SCENARIO 2 : DATABASE IS OPEN

Scenario 2 CASE 1: Datafile image copy from FRA

Here we will use datafile image copy from Flash Recovery Area (FRA), if it is enabled. This is FASTER WAY to recover a datafile.

A) check present files of the database:

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BRIJ

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 760 SYSTEM *** /u01/oracle/DB11G/oradata/brij/system01.dbf
2 560 SYSAUX *** /u01/oracle/DB11G/oradata/brij/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/oracle/DB11G/oradata/brij/undotbs01.dbf
4 5 USERS *** /u01/oracle/DB11G/oradata/brij/users01.dbf
5 341 EXAMPLE *** /u01/oracle/DB11G/oradata/brij/example01.dbf

So ‘users’ datafile is file#4 and we are assuming that this file got damaged somehow.

B) Check image copies of the damaged datafile:

In our scenario datafile 4 is damaged.

RMAN> list copy of datafile 4;

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time
——- —- – ——————– ———- ——————–
6 4 A 10-FEB-2014 12:05:11 1119428 10-FEB-2014 12:05:10
Name: /u01/oracle/DB11G/fast_recovery_area/BRIJ/datafile/o1_mf_users_9hld7pv8_.dbf
Tag: TAG20140210T120249

 

C) Take ‘damaged’ datafile ‘offline’

RMAN> sql ‘alter database datafile 4 offline’;

sql statement: alter database datafile 4 offline

 

D) Switch to the copy of the file in the FRA

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy “/u01/oracle/DB11G/fast_recovery_area/BRIJ/datafile/o1_mf_users_9hld7pv8_.dbf”

 

E) Recover the datafile copy and make it online

RMAN> recover datafile 4;

Starting recover at 11-FEB-2014 10:36:39
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-FEB-2014 10:36:40

 

RMAN> sql ‘alter database datafile 4 online’;

sql statement: alter database datafile 4 online

F) Check the datafiles once again:

RMAN> report schema;

Report of database schema for database with db_unique_name BRIJ

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 760 SYSTEM *** /u01/oracle/DB11G/oradata/brij/system01.dbf
2 560 SYSAUX *** /u01/oracle/DB11G/oradata/brij/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/oracle/DB11G/oradata/brij/undotbs01.dbf
4 5 USERS *** /u01/oracle/DB11G/fast_recovery_area/BRIJ/datafile/o1_mf_users_9hld7pv8_.dbf
5 341 EXAMPLE *** /u01/oracle/DB11G/oradata/brij/example01.dbf

You see the database is now using file#4 from the flash recovery area

AT THIS STAGE DATAFILE IS ONLINE AND DATABASE CAN BE USED NORMALLY.

When you bring the datafile back online, the tablespace will be brought online as well. The tablespace is now operational. But we don’t want to leave the database using a file in the flash recovery area, though, especially not for the long term.

BELOW STEPS ARE ADVISED TO BE DONE CAN BE DONE LATER ON (say weekend or off-business hours)

In below steps we will move back the data file to its original location.

 

G) Remove the ‘original damaged’ file at the OS level from the original location, if present

$ /u01/oracle/DB11G/oradata/brij

$ mv users01.dbf users01.dbf.bkp

H) Create an image copy of the file & place it in the file’s original location

RMAN> backup as copy datafile 4 format ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’;

Starting backup at 11-FEB-2014 10:47:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle/DB11G/fast_recovery_area/BRIJ/datafile/o1_mf_users_9hld7pv8_.dbf
output file name=/u01/oracle/DB11G/oradata/brij/users01.dbf tag=TAG20140211T104743 RECID=12 STAMP=839242064
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-FEB-2014 10:47:45

Starting Control File and SPFILE Autobackup at 11-FEB-2014 10:47:45
piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/autobackup/2014_02_11/o1_mf_s_839242065_9hnw2kwm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-FEB-2014 10:47:48

 

I) Take the datafile offline:

RMAN> sql ‘alter database datafile 4 offline’;

sql statement: alter database datafile 4 offline

J) Switch to the copy of the file

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy “/u01/oracle/DB11G/oradata/brij/users01.dbf”

K) Recover the datafile copy and make it online

RMAN> recover datafile 4;

Starting recover at 11-FEB-2014 10:50:59
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-FEB-2014 10:50:59

 

RMAN> sql ‘alter database datafile 4 online’;

sql statement: alter database datafile 4 online

 

L) Check the datafiles once again:

RMAN> report schema;

Report of database schema for database with db_unique_name BRIJ

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 760 SYSTEM *** /u01/oracle/DB11G/oradata/brij/system01.dbf
2 560 SYSAUX *** /u01/oracle/DB11G/oradata/brij/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/oracle/DB11G/oradata/brij/undotbs01.dbf
4 5 USERS *** /u01/oracle/DB11G/oradata/brij/users01.dbf
5 341 EXAMPLE *** /u01/oracle/DB11G/oradata/brij/example01.dbf

so datafile 4 is now back to its standard location.

As a best practice, you should now create a fresh image copy of the file and place it in the flash recovery area:

 

RMAN> backup as copy datafile 4;

Starting backup at 11-FEB-2014 10:52:36
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle/DB11G/oradata/brij/users01.dbf
output file name=/u01/oracle/DB11G/fast_recovery_area/BRIJ/datafile/o1_mf_users_9hnwcnbc_.dbf tag=TAG20140211T105236 RECID=14 STAMP=839242356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-FEB-2014 10:52:37

Starting Control File and SPFILE Autobackup at 11-FEB-2014 10:52:37
piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/autobackup/2014_02_11/o1_mf_s_839242357_9hnwcp10_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-FEB-2014 10:52:40

Scenario 2 CASE 2: Datafile restore from backup

For non-system and non-undo datafiles, you have the option of keeping the database open while performing the recovery. When your database is open, you’re required to take offline any datafiles you’re attempting to restore and recover.

 

$ RMAN

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 26 11:09:19 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: BRIJ (DBID=1279650270)
connected to recovery catalog database

RMAN> sql “alter database datafile ”/u01/oracle/DB11G/oradata/brij/users01.dbf” offline”;

sql statement: alter database datafile ”/u01/oracle/DB11G/oradata/brij/users01.dbf” offline

RMAN> restore datafile ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’;

Starting restore at 26-FEB-2014 11:10:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/DB11G/oradata/brij/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T100410_9jwc4yg2_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T100410_9jwc4yg2_.bkp tag=TAG20140226T100410
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-FEB-2014 11:10:18

RMAN> recover datafile ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’;

Starting recover at 26-FEB-2014 11:10:26
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-FEB-2014 11:10:27

RMAN> sql “alter database datafile ”/u01/oracle/DB11G/oradata/brij/users01.dbf” online”;

sql statement: alter database datafile ”/u01/oracle/DB11G/oradata/brij/users01.dbf” online

NOTE: When using the RMAN sql command, if there are single quote marks within the SQL statement, then you are required to use double quotes to enclose the entire SQL statement and then also use two single quote marks where you would ordinarily just use one quote mark.

If you want to put the restored datafile to new location, use below RMAN commands instead

RMAN>run
{
sql “alter database datafile ”/u01/oracle/DB11G/oradata/brij/users01.dbf” offline”;
set newname for datafile ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’ to ‘/backups/users01.dbf’;
restore datafile ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’;
switch datafile all;
recover datafile ‘/backups/users01.dbf’;
sql “alter database datafile ”/backups/users01.dbf” online”;
}

Remember that set command needs to be used inside a run block

SYS@brij > select file_name from dba_data_files;

FILE_NAME
——————————————————–
/u01/oracle/DB11G/oradata/brij/users02.dbf
/u01/oracle/DB11G/oradata/brij/system02.dbf
/u01/oracle/DB11G/oradata/brij/undotbs02.dbf
/backups/users01.dbf
/u01/oracle/DB11G/oradata/brij/undotbs01.dbf
/u01/oracle/DB11G/oradata/brij/sysaux01.dbf
/u01/oracle/DB11G/oradata/brij/system01.dbf
/u01/oracle/DB11G/oradata/brij/example01.dbf

8 rows selected.

 


 

CASE 4: ALL DATAFILES OF A TABLESPACE GOT CORUPTED/LOST

One of the tablespace is giving errors. the tablespace has multiple datafiles and all got corrupted.

If only one datafiles had error, we would have gone for datafile level recovery but since all datafiles associated with have corruption so it is better to go for tablespace level recovery .

Scenario 1: Database NOT Open

This solution works for any tablespace in your database (including system and undo tablesapce).

In this example, we are restoring the users tablespace:

There are two data files in this tablespace and both got corrupted somehow.

datafile file number=00006 name=C/users02.dbf
datafile file number=00004 name=/u01/oracle/DB11G/oradata/brij/users01.dbf

Below were the errors that were encountered in alert log because of this corruption:

Wed Feb 26 10:16:49 2014

Read of datafile ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’ (fno 4) header failed with ORA-01210
Hex dump of (file 4, block 1) in trace file /u01/oracle/DB11G/diag/rdbms/brij/brij/trace/brij_ora_4138.trc
Corrupt block relative dba: 0x01000001 (file 4, block 1)
Bad header found during datafile header read
Data in bad block:
type: 0 format: 0 rdba: 0xa20b0000
last change scn: 0x0100.00010000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x0
block checksum disabled
Rereading datafile 4 header failed with ORA-01210

ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: ‘/u01/oracle/DB11G/oradata/brij/users01.dbf’
ORA-01210: data file header is media corrupt

Database was shutdown before correcting the corruption.

$ RMAN

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 26 10:19:36 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

 

RMAN> restore tablespace users;

Starting restore at 26-FEB-2014 10:20:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/DB11G/oradata/brij/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/DB11G/oradata/brij/users02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T100410_9jwc4yg2_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T100410_9jwc4yg2_.bkp tag=TAG20140226T100410
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 26-FEB-2014 10:20:27

 

RMAN> recover tablespace users;

Starting recover at 26-FEB-2014 10:20:35
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-FEB-2014 10:20:36

 

RMAN> alter database open;

database opened

 

Scenario 2: Database is Open

Sometimes it is not possible to take the database down. You can take a tablespace offline, restore, and recover it while your database is open. This works for any tablespace except the system and undo tablespaces.

This example takes users offline and then restores and recovers before bringing it back online:

Here also we got the same error in users tablesace as showin in scenario 1 forcing us to go for restore/recover of users tabespace.

$ RMAN

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 26 10:31:47 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: BRIJ (DBID=1279650270)
connected to recovery catalog database

 

RMAN> SQL ‘ALTER TABLESPACE USERS OFFLINE IMMEDIATE’;

sql statement: ALTER TABLESPACE USERS OFFLINE IMMEDIATE

 

RMAN> RESTORE TABLESPACE USERS;

Starting restore at 26-FEB-2014 10:32:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/DB11G/oradata/brij/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/DB11G/oradata/brij/users02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T100410_9jwc4yg2_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_26/o1_mf_nnndf_TAG20140226T100410_9jwc4yg2_.bkp tag=TAG20140226T100410
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-FEB-2014 10:32:29

 

RMAN> RECOVER TABLESPACE USERS;

Starting recover at 26-FEB-2014 10:32:37
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-FEB-2014 10:32:39

 

RMAN> sql ‘alter tablespace users online’;

sql statement: alter tablespace users online

Below is how alert log will record above recovery  activity

Wed Feb 26 10:32:12 2014
ALTER TABLESPACE USERS OFFLINE IMMEDIATE
Completed: ALTER TABLESPACE USERS OFFLINE IMMEDIATE
Wed Feb 26 10:32:29 2014
Full restore complete of datafile 6 /u01/oracle/DB11G/oradata/brij/users02.dbf. Elapsed time: 0:00:00
checkpoint is 1349054
last deallocation scn is 925704
Full restore complete of datafile 4 /u01/oracle/DB11G/oradata/brij/users01.dbf. Elapsed time: 0:00:02
checkpoint is 1349054
last deallocation scn is 3
Wed Feb 26 10:32:38 2014
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace USERS
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 5 Seq 5 Reading mem 0
Mem# 0: /u01/oracle/DB11G/oradata/BRIJ/onlinelog/o1_mf_5_9hl934pt_.log
Mem# 1: /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_5_9hl934xp_.log
Recovery of Online Redo Log: Thread 1 Group 6 Seq 6 Reading mem 0
Mem# 0: /u01/oracle/DB11G/oradata/BRIJ/onlinelog/o1_mf_6_9hl972k3_.log
Mem# 1: /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_6_9hl972tr_.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 7 Reading mem 0
Mem# 0: /u01/oracle/DB11G/oradata/BRIJ/onlinelog/o1_mf_4_9hl96kyw_.log
Mem# 1: /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_4_9hl96l67_.log
Media Recovery Complete (brij)
Completed: alter database recover if needed
tablespace USERS
Wed Feb 26 10:33:04 2014
alter tablespace users online
Completed: alter tablespace users online

 


 

CASE 5: SPFILE/PFILE LOST

 

Sometimes spfile gets accidentally deleted or you are not able to open database using current spfile

 

Scenario 1: Using a Recovery Catalog

We will start Oracle instance without parameter file for retrieval of spfile

$ RMAN

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 26 14:52:27 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

 

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/oracle/DB11G/product/11.2.0/dbhome_1/dbs/initbrij.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes

RMAN> restore spfile;

Starting restore at 26-FEB-2014 14:56:46
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=/u01/oracle/DB11G/product/11.2.0/dbhome_1/dbs/spfilebrij.ora
channel ORA_DISK_1: reading from backup piece /backups/brij/controlfile_brij_c-1279650270-20140226-06
channel ORA_DISK_1: piece handle=/backups/brij/controlfile_brij_c-1279650270-20140226-06 tag=TAG20140226T134919
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-FEB-2014 14:56:48

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

 

Scenario 2: Not Using a Recovery Catalog

Here you must tell the DBID of database to RMAN process.

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 26 15:02:27 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

 

RMAN> connect target /

connected to target database (not started)

RMAN> set dbid 1279650270;

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/oracle/DB11G/product/11.2.0/dbhome_1/dbs/initbrij.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes

 

RMAN> restore spfile from ‘/backups/brij/controlfile_brij_c-1279650270-20140226-06′;

Starting restore at 26-FEB-2014 15:04:31
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backups/brij/controlfile_brij_c-1279650270-20140226-06
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-FEB-2014 15:04:33

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

NOTE: If our spfile autobackup was in default location, we could have used below command:

RMAN> restore spfile from autobackup;

 

[Post Views: 8143]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

10 Comments

  1. rajeev rajeev

    great explanation ..its very easy and better way for under standing 🙂

  2. Mohan Mohan

    Very concise and informative. Thanks so much for putting it together.

  3. Ashok Kumar Roy Ashok Kumar Roy

    Thanks for posting such handy notes. Please upload more to share your experience.

  4. reply reply

    it’s good & great .

    a simpler understanding way …

  5. Radhakanta Radhakanta

    This notes are very easy to understand and apply. thank you so much.

  6. Roy Roy

    Very Good !

  7. lucky lucky

    very good documentaion this is helpfull for me.

  8. Sham Sham

    I cant close any link … Superb !!

  9. abi abi

    excellent
    thank you so much for your notes

  10. abi abi

    what will happen …when spfile and pfile both lost?

Leave a Reply

Required fields are marked *