Recovering non-critical files

Recovering non-critical files would include temporary tablespaces, redo log files, index tablespaces, read-only tablespace, password file.

Temporary tablespaces

Temporary tablespace are written to by the server processes servicing the sessions, if the temporary tablespace is damaged or missing the database would still open fully but you would get a error message in the alert log, remember that as soon as a user uses the temporary tablespace he would get an error. Temporary tablespaces do not regenerate redo log information.

There are two ways to recover as temporary tablespaces are never backed up:

  1. Add another tempfile to the damaged temporary tablespace
  2. Take the damaged temporary tablespace offline
  3. Drop the damaged file
  4. Put the temporary tablespace back online


  1. Create a new temporary tablespace
  2. Switch users over to the new temporary tablespace (should be able to switch default temporary tablespace)
            sql> alter database default temporary tablespace temp2;
            sql> select property_value from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;
  3. Drop the damaged tablespace

Note: to see the default tablespace values default temp tbs, default tbs and default tbs type for the database use the below command

                  sql> select property_name, property_value from database_properties where property_name like '%DEFAULT%’;

Redo log files

As long as one member of the redo log file group exists then the instance will be ok. An error will be first identified within the alert log. It will also be reported in the V$LOGFILE view as an invalid file.

drop alter database drop logfile member ‘c:\oracle\oradata\redo1a.log’;
add alter database add logfile member ‘c:\oracle\oradata\redo1c.log’ to group 1;

Note: you cannot add to current group
clear alter database clear logfile group 1;
alter database clear unarchived logfile group 3;
archive alter database archive logfile group 1;

For more information on redo click here.

Index tablespace

If an index is not available, the table will be locked for nearly all DML operations, you will not be able to do any inserts, deletes or updates, you can query the table with select but performance will be degraded without an index.

Check to see if any indexes have been created on a tablespace:

    sql> select owner, segment_name, segment_type from dba_segments where tablespace_name='TEST' and segment_type <> 'INDEX';


   sql> select name, status from v$datafile where file# = 7; (obtain the file number from alert.log)
   sql> select online_status, error from v$recover_file where file# = 7;


  1. take the damaged tablespace offline (not normally required due to step 3)
  2. determine which indexes were in the tablespace (see above command) and extract using datadump
        c:/> expdp test/test directory=datadump dumpfile=indexes.dmp include=index
        c:/> impdp test/test directory=datapump dumpfile=indexes.dmp sqlfile=indexes.sql
  3. drop the tablespace and delete its datafiles (including contents and datafiles)
  4. create a new tablespace
  5. generate all the indexes in it
         sql> create index test.rname_idx on test.employee(emp_no) tablespace indx nologging;
        Or use the above sqlfile created by datapump

The best way to rebuild the objects in the tablespace is to rebuild the indexes in parallel with NOLOGGING.

Read-only tablespaces

Normally read-only tablespaces are backed up once, remember a read-only tablespace cannot be put into backup mode. To enable RMAN to backup read-only tablespaces configure the following option:

   rman> configure backup optimisation on;

Note: if you ever change a tablespace from read-only to read/write always backup after its status has changed.

Recover a damaged read-only tablespace as you would a normal tablespace, remember no recovery is need on a read-only tablespace as no changes have been made. You can only use DROP against the read-only tablespace as this affects the data dictionary not the tablespace itself.

If a read-only tablespace is missing during startup the database will go into mount mode after an ORA-01157 error message (missing datafile).

Password File

RMAN cannot backup system files (only database files) the password file is an operating system file. It is always possible to connect to a database with the sysdba privilege with operating authentication, the password file provides a secondary means.

If you have problems starting a database because of a damaged password make sure you set the following:

   sql> alter system set remote_login_passwordfile=none scope=spfile;

Note: if REMOTE_LOGIN_PASSWORDFILE parameter is set to shared or exclusive the password file is ignored.

To recreate the password file, which is stored in $oracle_home/dbs or c:\$oracle_home\database, the database should normally be shutdown first:

   c:\> orapwd file=<filename> password=<sys password> entries=<max users>

   sql> select * from v$pwfile_users;