Non-Critical Files in terms of Recovery

Non-critical losses are failure events that can be resolved without significantly impacting the operation of the database if performed properly. A good DBA should be able to resolve non-critical
losses quickly and leave the database in operational state throughout the recovery process. Though during the recovery process there may be performance perform but it should be operational state.

Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised. If recovery is performed properly, these files can be recovered or rebuilt in some cases with minimal impact to database operations.

Non-Critical Files in Oracle Database.
-------------------------------------------------
1)Temporary tablespaces: If temporary tablespace or its datafiles are deleted accidentally then they can be recreated easily within any database shut down.

In order to create new one issue,
SQL>create temporary tablespace temp2 tempfile '/oradata/temp01.dbf' size 100M;

Then explicitly assign users to this temporary tablespace, like

SQL>ALTER USER ARJU TEMPORARY TABLESPACE TEMP;

In order to make database default temporary tablespace to temp2 use which will affect of all users whether they are explicitly set or not.,
An example:
i)create a Temporary Tablespace.

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 10m;
Tablespace created.

SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.

ii)Look at database default tablespace and temporary tablespace of user ARJU.

SQL> SELECT PROPERTY_VALUE,PROPERTY_NAME FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TABLESPACE%';
PROPERTY_VALUE PROPERTY_NAME
------------------------------ ------------------------------
TEMP DEFAULT_TEMP_TABLESPACE
USERS DEFAULT_PERMANENT_TABLESPACE

SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP

iii)Now change the database default temporary tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Database altered.

iv) You will see all schema users' tablespace is set to default.

SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP2

v)However now you can change a user to point another tablespace.

SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.

SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP


2)Non-current redo log files: If the status of redo log group is not current then dropping them will continue work in database.
In order to drop a redo log group query from V$LOG.
If the status is current then you can't drop it. Drop a non-current redo log group by,
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;

To add new group with one memeber,
SQL> ALTER DATABASE ADD LOGFILE '/oradata2/data1/dbase/redo04.log' SIZE 5m;

To add a member in a group issue,

alter database add logfile member
'/oradata2/data1/dbase/redo01_01.log' to group 1;

To delete a member from a log group issue where status is no-current,

alter database drop logfile member
'/oradata2/data1/dbase/redo01_01.log';



3)Index tablespaces: If the tablespace containing index or index datafile is dropped then database performance might suffer. Users can continue work. However in order to recover them it is needed to rebuild the index.


4)Indexes: If any index is deleted then recover them by simply creating script and run them.

5)Read-only tablespaces: Read-only tablespaces are by nature non-critical database files. These tablespaces are static or do not have data modifications like normal tablespaces. This allows recovery to be a fairly straightforward process under most circumstances. No redo log information needs to be applied to recover read-only tablespaces if they stayed in read only mode since backup. Simply restore them from backup.

Like in RMAN,
RMAN>RESTORE TABLESPACE TBS_READ_ONLY;
or from OS,
$scp source_location location_in_control_file