Resetting unrecoverable_time in v$datafile

When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you run in ARCHIVELOG mode. If you cannot afford to lose tables or indexes created with NOLOGGING, then make a backup after the unrecoverable table or index is created. 

Be aware that when you perform media recovery, and some tables or indexes are created normally whereas others are created with the NOLOGGING option, the NOLOGGING objects are marked logically corrupt by the RECOVER operation. Any attempt to access the unrecoverable objects returns an ORA-01578 error message. Drop the NOLOGGING objects and re-create them if needed.

Detect unrecoverable operations

We can detect/locate the database objects affected by these unrecoverable operations by retrieving these objects with the following script:

SELECT file#,

       name,

       unrecoverable_time,

       unrecoverable_change#

  FROM v$datafile

FILE#	NAME	UNRECOVERABLE_TIME	UNRECOVERABLE_CHANGE#
1	+DT_IUB/iubdb/datafile/system.259.834338319		0
2	+DT_IUB/iubdb/datafile/sysaux.260.834338325		0
4	+DT_IUB/iubdb/datafile/undotbs2.326.866650713		0
5	+DT_IUB/iubdb/datafile/users.264.834338349		0
6	+DT_IUB/iubdb/datafile/iub01.323.835097247	11/28/2014 10:00:42 PM	4208958828
7	+DT_IUB/iubdb/datafile/iub01.324.838746981	11/28/2014 10:00:42 PM	4208958810
14	+DT_IUB/iubdb/datafile/iublog.331.841587145	11/28/2014 10:00:44 PM	4208960080
15	+DT_IUB/iubdb/datafile/iub01.332.841739039	11/28/2014 10:00:42 PM	4208958801
16	+DT_IUB/iubdb/datafile/iub01.333.845975507	11/28/2014 10:00:42 PM	4208958790

SELECT DISTINCT dbo.owner,

                dbo.object_name,

                dbo.object_type,

                dfs.tablespace_name,

                dbt.logging table_level_logging,

                ts.logging tablespace_level_logging

  FROM v$segstat ss,

       dba_tablespaces ts,

       dba_objects dbo,

       dba_tables dbt,

       v$datafile df,

       dba_data_files dfs,

       v$tablespace vts

 WHERE     ss.statistic_name = 'physical writes direct'

       AND dbo.object_id = ss.obj#

       AND vts.ts# = ss.ts#

       AND ts.tablespace_name = vts.name

       AND ss.VALUE != 0

       AND df.unrecoverable_change# != 0

       AND dfs.file_name = df.name

       AND ts.tablespace_name = dfs.tablespace_name

       AND dbt.owner = dbo.owner

       AND dbt.table_name = dbo.object_name

OWNER	OBJECT_NAME	OBJECT_TYPE	TABLESPACE_NAME	TABLE_LEVEL_LOGGING	TABLESPACE_LEVEL_LOGGING
IUBBKP	IUB_P_5245	TABLE	IUBLOG	YES	LOGGING
IUBBKP	IUB_PC5098	TABLE	IUBLOG	YES	LOGGING
IUBBKP	IUB_P_C5040	TABLE	IUBLOG	YES	LOGGING
IUBARCH	IUB_P_TRANSACTION_LOG_ARCH	TABLE	IUBARCH01	NO	LOGGING
IUBBKP	IUB_P_C5098	TABLE	IUBLOG	YES	LOGGING
IUBBKP	IUB_LK_VALS	TABLE	IUBLOG	YES	LOGGING

Get  tablespace info
select df.tablespace_name,
           df.file_name,
           v.unrecoverable_time,
           v.unrecoverable_change#,
               b.time
      from dba_data_files df,
           v$datafile     v,
               v$backup b
     where v.file# = df.file_id
      and v.file# = b.file#
      and v.unrecoverable_change# > 0
      and (b.time is null or b.change# < v.unrecoverable_change#)
   order by tablespace_name, file_name

TABLESPACE_NAME	FILE_NAME	UNRECOVERABLE_TIME	UNRECOVERABLE_CHANGE#	TIME
IUB01	+DT_IUB/iubdb/datafile/iub01.323.835097247	11/28/2014 10:00:42 PM	4208958828
IUB01	+DT_IUB/iubdb/datafile/iub01.324.838746981	11/28/2014 10:00:42 PM	4208958810
IUB01	+DT_IUB/iubdb/datafile/iub01.379.860926053	11/28/2014 10:00:42 PM	4208958831
IUBARCH01	+DT_IUB/iubdb/datafile/iubarch01.380.864384227	11/23/2014 4:35:33 PM	4139194161
IUBLOG	+DT_IUB/iubdb/datafile/iublog.331.841587145	11/28/2014 10:00:44 PM	4208960080

Fix unrecoverable data files

One way to force these nologging objects operation to be captured in the redo log, is to implement FORCE_LOGGING to allow Oracle to force logging in redo even if nologging operations are performed, however, this statement will not repair a database that is already invalid.  Force logging can be enforced at the database and tablespace level. It is important to evaluate the benefit of force logging versus those of nologging as logging may produce huge redo logs in some databases eg; EBS Database.

If a decision has been made to keep the database and tablespace in NO force logging mode, it is imperative for the business owner and database administrator to collectively ensure a good backup is taken immediately prior to any patching activities, direct load, and other nologging operations take place.

After taking the backup if you still see the datafiles as unrecoverable using the above mentioned queries , try below for the associated tablespaces and run the queries above to verify.




 SQL> alter tablespace IUBARCH01 begin backup;

Tablespace altered.

SQL> alter tablespace IUBARCH01 end backup;

Tablespace altered.

RUN the query above again

select df.tablespace_name,
           df.file_name,
           v.unrecoverable_time,
           v.unrecoverable_change#,
               b.time
      from dba_data_files df,
           v$datafile     v,
               v$backup b
     where v.file# = df.file_id
      and v.file# = b.file#
      and v.unrecoverable_change# > 0
      and (b.time is null or b.change# < v.unrecoverable_change#)
   order by tablespace_name, file_name

TABLESPACE_NAME	FILE_NAME	UNRECOVERABLE_TIME	UNRECOVERABLE_CHANGE#	TIME
IUB01	+DT_IUB/iubdb/datafile/iub01.323.835097247	11/28/2014 10:00:42 PM	4208958828
IUB01	+DT_IUB/iubdb/datafile/iub01.324.838746981	11/28/2014 10:00:42 PM	4208958810
IUB01	+DT_IUB/iubdb/datafile/iub01.378.860926047	11/28/2014 10:00:42 PM	4208958834
IUB01	+DT_IUB/iubdb/datafile/iub01.379.860926053	11/28/2014 10:00:42 PM	4208958831
IUBLOG	+DT_IUB/iubdb/datafile/iublog.331.841587145	11/28/2014 10:00:44 PM	4208960080

Please observe that tablespace IUBARCH01 is not in the list now, Perform Alter Tablespace step for all the tablespaces involved.