22May/100
Oracle: Recovering a Tablespace
The situation:
The database was not shutdown cleanly and a tablespace needed recovery because of an outstanding transaction that wasn’t committed.
If you simply STARTUP the database, the tablespace will still be in RECOVER mode and won’t be available.
First, startup the database in restrict mode:
1 | SQL> startup restrict |
List all tablespaces and check the ONLINE_STATUS:
1 2 3 4 5 6 7 8 9 10 11 | SQL> SELECT tablespace_name, online_status FROM dba_data_files; TABLESPACE_NAME ONLINE_STATUS ------------------------------ --------------- USERS ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE SYSTEM SYSTEM TEST RECOVER 5 ROWS selected. |
In this case, the TEST tablespace requires recovery:
1 2 3 | SQL> RECOVER TABLESPACE TEST; Media recovery complete. |
Check tablespaces again:
1 2 3 4 5 6 7 8 9 10 11 | SQL> SELECT tablespace_name, online_status FROM dba_data_files; TABLESPACE_NAME ONLINE_STATUS ------------------------------ -------------- USERS ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE SYSTEM SYSTEM TEST OFFLINE 5 ROWS selected. |
The tablespace doesn’t need further recovery at this stage and can be placed ONLINE:
1 2 3 | SQL> ALTER TABLESPACE TEST ONLINE; TABLESPACE altered. |
Get the database out of restrict mode:
1 2 3 | SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; SYSTEM altered. |
Related posts: