The database is open, and some but not all of the datafiles are damaged. You want to restore and recover the damaged tablespace, while leaving the database open so that the rest of the database remains available. You discover that the damaged datafiles are from the tablespaces users.
Some examples are listed below.
- Create Scenario to Recover datafiles.
- Recover datafiles to their default location.
- Recover datafiles to new location.
- Recover system tablespace.
- Make sure the database is mounted or open
- A Closed or open DB backup and archivelog is required
1- Create Scenario to Recover datafiles.
SQL> connect sys/srdc
SQL> select file_name from dba_data_files
where tablespace_name ='SYSTEM';
SQL> shutdown immediate;
ORA-01157: cannot identify/lock data file 1- see DBWR trace file
ORA-01110:data file 1:'C:\ORACLE_DATA_FILES\DEVTEST\SYSTEM01.DBF'
2- Recover datafiles to their default location.
#Connect to the target database
C:\RMAN TARGET SYS/SRDC
#Take the affected tablespaces offline if they are not already offline.
RMAN> SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
#Restore the tablespace or datafile
RMAN> RESTORE TABLESPACE users;
#Rrecover the tablespace or datafile
RMAN> RECOVER TABLESPACE users;
#If RMAN reported no errors, then bring the tablespace back online:
RMAN> SQL 'ALTER TABLESPACE users ONLINE';
3- Recover datafiles to new location
Assume that the old datafiles were stored in directory /olddisk and the new ones will be stored in /newdisk.
SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
SQL 'ALTER TABLESPACE tools OFFLINE IMMEDIATE';
# specify the new location for each datafile
SET NEWNAME FOR DATAFILE '/olddisk/users01.dbf'TO'/newdisk/users01.dbf';
SET NEWNAME FOR DATAFILE '/olddisk/tools01.dbf'TO'/newdisk/tools01.dbf';
RESTORE TABLESPACE users, tools;
SWITCH DATAFILE ALL; # update control file with new filenames
RECOVER TABLESPACE users, tools;
#If recovery is successful, then bring the tablespaces online:
SQL 'ALTER TABLESPACE users ONLINE';
SQL 'ALTER TABLESPACE tools ONLINE';
4- Recover system tablespace
if a non-system tablespace is missing or corrupted while database is open, recovery can be performed while DB remains open.
if the system tablespace is missing or corrupted the DB can not be started up so a complete closed based recovery must be performed