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;
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
great explanation ..its very easy and better way for under standing 🙂
Very concise and informative. Thanks so much for putting it together.
Thanks for posting such handy notes. Please upload more to share your experience.
it’s good & great .
a simpler understanding way …
This notes are very easy to understand and apply. thank you so much.
Very Good !
very good documentaion this is helpfull for me.
I cant close any link … Superb !!
excellent
thank you so much for your notes
what will happen …when spfile and pfile both lost?
Hi ,
Please use database alert log (parameter during startup db ) to create pfile then use .