Recover_tablespace_online

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

Leave a Reply

Your email address will not be published. Required fields are marked *