Recovering non-critical files
Recovering non-critical files would include temporary tablespaces, redo log files, index tablespaces, read-only tablespace, password file.
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:
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.
|alter database drop logfile member ‘c:\oracle\oradata\redo1a.log’;
|alter database add logfile member ‘c:\oracle\oradata\redo1c.log’ to group 1;
Note: you cannot add to current group
|alter database clear logfile group 1;
alter database clear unarchived logfile group 3;
|alter database archive logfile group 1;
For more information on redo click here.
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;
The best way to rebuild the objects in the tablespace is to rebuild the indexes in parallel with NOLOGGING.
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).
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;