Recovering Oracle Tablespaces

1. Start Oracle Enterprise Manager (OEM) by using SYSMAN as the administrator, SYSMAN as the password, and click localhost as the management server. Click OK.
2. The OEM screen is displayed. Click the Databases folder.
3. When you right-click the target database, as we have done for you here, the OEM displays the different options available for backup and recovery. Select Recovery from the Backup Management options.
4. This screen serves as a starting point for the recovery of a tablespace. Click Next.
5. Here, we specify that the recovery is for a tablespace and we do not want the data files to the restored. Click Next.
6. Here, we choose the tablespace that needs to be restored. In this case, the tablespace is temp. Click Next.
7. Here, we choose the backup configuration. This helps in identifying the location of backups. In this case, it is "backup1." Click Finish.
8. OEM summarizes the complete process of selection that we went through and inquires whether the process is correct. If so, it creates a job within OEM, which gets executed on the server side. This is the end of the simulation. Click Exit.

This example demonstrates how to restore and recover one or more tablespaces in the protected database after they are accidentally dropped or corrupted. The example assumes that the database is up and running and that you will restore only the affected tablespaces. To restore and recover one or more tablespaces:

1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
3. Restore and recover the affected tablespaces.

The following command restores and recovers the USERS tablespace:

RUN{
SQL 'ALTER TABLESPACE users OFFLINE';
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL 'ALTER TABLESPACE users ONLINE';
}