Ramin Hossaini

Oracle: Recovering a Tablespace

May
22

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.

Leave a Reply

Your email address will not be published. Required fields are marked *