Restore and Recover the tablespace with RMAN

Restore and recover the tablespace with RMAN backup.

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

Steps

Note: Take a cold backup of the completed database as a precautions

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

RMAN> Report schema;

2. Check the alert log file for drop tablespace:

3. Offline the tablespace that having issues such as corruption/ datafiles lost
Suppose tablespace USERS having an 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 the tablespace present in the database

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