Non-System tablespace recovery (online)

This test shows how to restore your non-system tablespace while the database is running.

Scenario

1. Create a user with the USERS tablespace it's default. Then add some test table and data.

2. Perfom your normal database backup

3. Add more data to the new schema

4. Now move the USERS tablespace e.g. mv users_01.dbf users_01.dbf.TMP

5. As SYSTEM resize users datafile, this will cause error since can't find tablespace e.g.

 SQL> alter database datafile '/u01/oracle/oradata/D1AR/users01.dbf' resize  105m;
 alter database datafile '/u01/oracle/oradata/D1AR/users01.dbf' resize  105m
 *
 ERROR at line 1:
 ORA-01565: error in identifying file '/u01/oracle/oradata/D1AR/users01.dbf'
 ORA-27037: unable to obtain file status
 Linux Error: 2: No such file or directory
 Additional information: 3

Recovery

6. Offline the tablespace, also no file 4 is not in the list

   SQL> alter tablespace users offline; -- or offline immediate;

 Tablespace altered.

 SQL> select * from v$backup;

      FILE# STATUS                CHANGE# TIME
 ---------- ------------------ ---------- ---------------
      1 NOT ACTIVE                  0
      2 NOT ACTIVE                  0
      3 NOT ACTIVE                  0

7. Restore the tablespace

  $ rman catalog rman/rman@rmandev

 connect target /

 restore tablespace users;
 recover tablespace users;

8. Restore and recover of TS done, now online TS

 SQL> alter tablespace users online;

 Tablespace altered.

9. Restart the db to make sure all is fine.

SQL> conn / as sysdba
Connected.
SQL> shut immediate;

SQL> startup

10. Do select on test table so see, all data are there.

11. Now do a backup if you wish