Restore and Recover the tablespace with RMAN

Restore and recovery the tablespace with RMAN backup.

Following is method to restore and recovery tablespace without shutting down the complete database

Steps

Note: Take cold backup of completed database as precautions

1. Report the schema command to check datafiles and tablespace present

RMAN> Report schema;


2. Check alert log file for drop tablespace:
 
3. Offline the tablespace which having issue such as corruption/ datafiles lost
Suppose tablespace USERS having issue, we need to recover, taking offline the users tablespace:

RMAN> SQL 'ALTER TABLESPACE users OFFLINE';


4. Recover the tablespace:

RUN
{
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
}


5. Make it online to USERS

RMAN> SQL 'ALTER TABLESPACE users ONLINE';


6. Check the status of tablespace present in database

select file_name,bytes from dba_data_files where tablespace_name='USERS';