6.3 Recover a single non-critical Datafile/Tablespace in ARCHIVELOG mode – Database ONLINE !
6.3.1 Preparing db and creating the recovery case
– Create tablespace and delete the related datafile
SQL> @cr_ts
RMAN> backup database plus archivelog delete input;
SQL> alter tablespace hh_test offline immediate;
% asmcmd rm BIG_DATA/DB1DUP/DATAFILE/HH_TEST\*
6.3.2 Error after recovery case:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: ‘+BIG_DATA/db1dup/datafile/hh_test.258.807733867’
6.3.3 Recover datafile
RMAN> report schema;
Report of database schema for database with db_unique_name DB1DUP
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 325 SYSTEM YES +BIG_DATA/db1dup/datafile/system.260.807722583
2 325 SYSAUX NO +BIG_DATA/db1dup/datafile/sysaux.261.807722585
3 200 UNDOTBS1 YES +BIG_DATA/db1dup/datafile/undotbs1.259.807722585
4 125 HH_TEST NO +BIG_DATA/db1dup/datafile/hh_test.258.807733867
–> here we know to restore datafile 4
RMAN> sql ‘alter database datafile 4 offline’;
RMAN> restore datafile 4;
–> Check the new ASM file:
% asmcmd ls BIG_DATA/DB1DUP/DATAFILE/HH_TEST\*
HH_TEST.258.807734245
RMAN> recover datafile 4;
RMAN> sql ‘alter database datafile 4 online’;
RMAN> sql ‘ alter tablespace hh_test online’;
Full output from used Recovery commands !
6.3.4 Lessons learned in this session
- As a first step take the involved datafile(s) OFFLINE using RMAN
- Use report schema to get an overview of all the datafiles
- Tablespace recovery is really a datafile recovery if all involved datafiles for a specific tablespace