Email: service@parnassusdata.com 7 x 24 online support!

ORA-01194: When Opening Database After Restoring Backup

ORA-01194: When Opening Database After Restoring Backup

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

 

 

 

Problem Description:
====================
 
You have restored backup 

a) which you believe is a valid offline backup
OR
b) a valid online backup and then applied some archivelogs
yet you receive the following error when opening the database:

 ORA-01194: file <name> needs more recovery to be consistent 
     Cause: An incomplete recovery session was started, but an insufficient 
            number of redo logs were applied to make the file consistent.
            The named file was not closed cleanly when it was last opened by 
            the database.
            The most likely cause of this message is forgetting to restore the 
            file from a backup before doing incomplete recovery.
    Action: The file must be recovered to a time when it was not being updated.
            Either apply more redo logs until the file is consistent or restore 
            the file from an older backup and repeat recovery.
            For more information about recovery, see the index entry "recovery"
            in the <Oracle7 Server Administrator's Guide>.


Problem Explanation:
====================

A proper offline backup is with the database shutdown cleanly prior to the 
backup using either immediate or normal shutdown option. This flushes out all 
dirty blocks to the datafiles and make sure that they do not require any form 
of recovery when restored.

If you fail to do this for an offline backup, then the current online redolog will
be required to get the datafiles consistent. If you really did shutdown the database,
and backed up the online redologs, then you can restore them and recover using the
current redolog. If the database was not shutdown, then you cannot recover using this
backup.

The state of a datafile when the database is open is called Online Fuzzy, and is 
cleared when the database is shutdown cleanly.


A proper online backup requires the database (10g and above) or tablespaces to be in 
backup mode when the files are backed up. What happens is that Oracle marks the 
datafile header that the file is in hot backup mode, and then allows updates to the
file during the backup. The datafile will require media recovery from that time, and
at to either a) a time past the end backup in the archivelogs or b) to the current
redolog. 

The state of a datafile in hot backup mode is called Hot Backup In Progress fuzzy.
It is set when the datafile enters hot backup mode, and is cleared when it is taken
out of backup mode, or we reach the end-of-redo marker in the current redolog, ie by
doing a complete recovery.


There are two other fuzzy flags, but they are not discussed here.


Solution Description:
=====================

In order for any database to open resetlogs, three conditions have to be met:

1. All online, read-write datafiles must be checkpointed at the same time.
2. No datafiles can be fuzzy. That means that none of the fuzzy flags can be set.
3. No datafiles can be in hot backup mode

The following queries can be run with the database in mount to determine this:

1. SELECT DISTINCT (TO_CHAR (CHECKPOINT_CHANGE#)) FROM V$DATAFILE_HEADER;
This should return just one row for all online, read-write datafiles. 
As media recover is applied on datafiles, Oracle will advance the checkpoint of
the files, so that we don't have to start recovery over, but it will resume from
where it left off. We can use this to monitor the recovery process and see that 
the older checkpointed files are approaching the most recent file.

2. SELECT DISTINCT (FUZZY) FROM V$DATAFILE_HEADER;
This should return just one row: NO
Note that this doesn't tell you what fuzzy flag is set, it just tells you if any
fuzzy flag is set. 

3. SELECT DISTINCT (STATUS) FROM V$BACKUP;
This should return just one row: NOT ACTIVE
         

Only when the above conditions are met, are you allowed to open the database with
resetlogs. The column OPEN_RESETLOGS in V$DATABASE can also be used returning either
REQUIRED, NOT ALLOWED or ALLOWED.

Solution Explanation:
=====================

For an offline backup, the usual problem if you get ORA-1194 is that the database
was open, or not properly shutdown during the backup. This renders the backup 
unusable, as it will require access to the current redolog at the time, and all 
subsequent redologs in order to clear the online fuzzy flag. You can check the 
alert.log of the database around the time of the backup to verify how the database
was shutdown, and that it was shutdown during the entire time of the backup.

For online backup, you normally get this because you haven't applied enough 
archivelogs to bring the database past the time of the end backup marker. You can
check this also in the alert.log as begin/end backup commands are logged there. 
Make sure you apply at least enough archives to bring it past the log sequence that
was active at the time the backup ended. 

You can also get this error if your backup was not in backup mode. The scenario 
then will be as for offline backup, except that you will have the option of using
the archivelogs and the current redolog in order to do a complete recovery to clear
the fuzzy flag. 


Search Words:
=============

recover restore fuzzy backup resetlogs

 

 

Bug 19791146 - startup fails with ORA-1194 because 'end backup' on physical standby

 

 

Bug 19791146  startup fails with ORA-1194 because 'end backup' on physical standby

 This note gives a brief overview of bug 19791146. 

 The content was last updated on: 29-NOV-2015

 Click here for details of each of the sections below.


Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.2
Versions confirmed as being affected
Platforms affected Generic (all / most platforms affected)

Fixed:

The fix for 19791146 is first included in

Interim patches may be available for earlier versions - click here to check.

Symptoms:

Related To:

Description

On Physical STANDBY database, STARTUP of the database might fail with ORA-1194.
It can affect FAILOVER or SWITCHOVER operation.
 
The standby database alert log shows an "end backup" was succesfully executed on the standby, 
eg look for these alert log messages:
   Physical Standby Database mounted.
   alter ... end backup
   Completed: alter ... end backup
 
After the above "end backup", the standby database can no longer be opened in read only mode. For evidence of this, look for the following kind of sequence of messages in the alert log:
   alter database open read only
   Beginning Standby Crash Recovery.
   Serial Media Recovery started
   Media Recovery Log ...
   Media Recovery of Online Log ...
   Recovery of Online Redo Log: ...
   Standby Crash Recovery aborted due to error 1013.
   ORA-01013: user requested cancel of current operation
   Recovery interrupted!
   Completed Standby Crash Recovery.
   ORA-10458: standby database requires recovery
   ORA-01194: file <n> needs more recovery to be consistent
   ORA-01110: data file <n>: '<datafile_pathname>'
   ORA-10458 signalled during: alter database open read only
 
and also:
   "select fhfno, to_char(fhsta,'XXXXXXXX') from x$kcvfh;" on the standby shows bit 0x4 is set for the problematic datafiles, which means these datafiles are in an online fuzzy state
 
Workaround
 
To prevent the problem from happening in the first place, do NOT execute either of these commands on the physical standby:
 
  ALTER DATABASE ... END BACKUP;
  ALTER TABLESPACE ... END BACKUP;
 
If the problem has already happened then the workaround is to do the following:
 
1. on the standby, identify which datafiles are affected, this can
   be done via the following query on the mounted standby:
     select fhfno, to_char(fhsta,'XXXXXXXX') from x$kcvfh;
   bit 0x4 will be set for the problemtic datafiles, which means these
   datafiles are in an online fuzzy state
2. shutdown the standby
3. on the primary:
     a) put the identified set of datafile(s) into hot backup mode, eg
	  alter tablespace <ts> begin backup; or
	  alter database begin backup;
     b) for the identified set of datafile(s), copy them to the standby
	ie overwriting the original datafiles on the standby
     c) take the datafile(s) out of hot backup mode, eg:
	  alter tablespace <ts> end backup; or
	  alter database end backup;
4. on the standby, do the following:
     a) startup mount
     b) start media recovery and so allow the standby to get into sync with the primary
 
This fix raises an ORA-01649 for an 'end backup' on a physical standby which has datafiles in hotbackup mode. 
Prior to this fix, the 'end backup' would succeed and then all attempts to open the standby read only would fail with ORA-01194.
 
ORA-01649: operation not allowed with a backup control file

 

OERR: ORA-1194 "file %s needs more recovery to be consistent" Reference Note

 

 

PURPOSE

This is a brief reference note to show the meaning of database error "ORA-1194". It includes the error text, "Cause" and "Action" from message files for each database version from 9.2 onwards, along with search links and details of any database bug issues related to the error.

SCOPE

This note is intended for general audience as a starting point to check the meaning of "ORA-1194". It does not give detailed information about the error and does not give error message text for versions below 9.2 .

DETAILS

Error Text, Cause and Action from Message File/s for ORA-01194

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1

Error:  ORA-01194 file %s needs more recovery to be consistent 
---------------------------------------------------------------------------
Cause:  An incomplete recovery session was started, but an insufficient number 
	of logs were applied to make the file consistent. The reported file was 
	not closed cleanly when it was last opened by the database. It must be 
	recovered to a time when it was not being updated. The most likely 
	cause of this error is forgetting to restore the file from a backup 
	before doing incomplete recovery. 
Action: Either apply more logs until the file is consistent or restore the 
	file from an older backup and repeat recovery. 

Search Links for ORA-01194

The links below can be used to locate ORA-1194 in the documentation, and to search for documents that give more information about the error.

Database Bugs Related to ORA-01194

Errors are usually due to usage, application or configuration issues but in some cases they may be caused by a bug issue. This section lists any database bugs that have been linked to error "ORA-1194" .

 

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 
       

 

 
NB Prob Bug Fixed Description
  I 19791146 12.2.0.0 startup fails with ORA-1194 because 'end backup' on physical standby
  I 22360720   RMAN duplicate on Standby fails with RMAN-06136 ORA-1194
+ - 1683613 8.1.7.2, 9.0.1.0

RMAN BACKUP or COPY datafile might not be usable for recovery - ORA-1194 / ORA-1547

 

 

 

 

 

Open Database failed - DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110

 

 

 

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later

Information in this document applies to any platform.

***Checked for relevance on 27-Feb-2015***


PURPOSE

The goal of this article is to assist DBAs who encounter the following errors at database open:

ORA-1194 "file %s needs more recovery to be consistent"
ORA-1547 "warning: RECOVER succeeded but OPEN RESETLOGS would get error below"
ORA-1110 "data file %s: '%s'"

 

 

SCOPE

This article is meant for database administrators and backup and recovery specialists tasked with the restore and recovery of a database

DETAILS

Assuming that all datafiles were either successfully restored, and/or recovery was done with existing database datafiles, but failed to open with the below:

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/V1123/system01.dbf'

 

The ORA-01547 before database open indicates that Oracle sees the datafiles as inconsistent. This is resolved by applying more recovery, to make the datafiles consistent.

Checks to Perform

1.  Check the current status of the datafiles:


set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

 

The goal is to have the above query return 1 row and fuzzy column value as NO.

 

NOTE:  if checkpoint_change# in query returns 0, this indicates that Oracle cannot read the file header.  This is possibly because the location and name of the datafile within the controlfile is not the one on disk.  

 

 2.  Check the archivelog sequence numbers needed to recover the files.  Execute:

select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

This query will show the smallest and largest sequence needed by the datafiles.    To make the datafiles consistent, you must apply all archivelog files within the above range of sequence numbers.  This can be one or many archivelog files.  

NOTE:  This query will not be valid in a RAC environment.  In RAC, find the necessary sequences from all threads using the smallest (and largest) checkpoint_change# returned by the query in #1 and execute:

select thread#, first_change#, next_change# from v$archived_log where <smallest/largest checkpoint_change#> between first_change# and next_change#;

once the above is executed with the smallest and largest checkpoint_change#, you have the sequence range needed to be applied from all threads of the RAC database.  See Note 243760.1 'RMAN: RAC Backup and Recovery using RMAN' for more details.

3.  Once further recovery is applied, confirm the datafiles are consistent with query in #1, above, until the goal is accomplished.  

4.  Once all datafiles are consistent and fuzzy=NO, the database can be opened and the ORA-01547 should no longer be returned.
 

 

Scenario 1: Current Controlfile is Used for Recovery

If the CURRENT CONTROLFILE is in use, and all online redo logs are available, then you can simply run RECOVER DATABASE. This will apply all archivelogs, and the current online redolog.

1) Ensure instance is MOUNTed and ALL datafiles  are ONLINE (SYSTEM datafiles will have the status of SYSTEM)

SQL> select name, controlfile_type from v$database ;

   NAME      CONTROL
   --------- -------
   V1123     CURRENT  

   SQL> select status from v$instance;

   STATUS
   ------------
   MOUNTED

   SQL> select distinct(status) from v$datafile;

   STATUS
   -------
   ONLINE
   SYSTEM
 

 

2) Recover and open the database:

   SQL> recover automatic database ;  
    ..
   Media recovery complete     
   SQL> alter database open
   Database altered.

 

 

Scenario 2: Backup Controlfile is Used for recovery

Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.

 

SQL> select name, controlfile_type from v$database ;

NAME      CONTROL
--------- -------
V1123     BACKUP       -- controlfile_type is "Backup" Controlfile

SQL> select status,
  2         resetlogs_change#,
  3         resetlogs_time,
  4         checkpoint_change#,
  5         to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
  6         count(*)
  7    from v$datafile_header
  8   group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
  9   order by status, checkpoint_change#, checkpoint_time ;

STATUS  RESETLOGS_CHANGE# RESETLOGS_TIME       CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
------- ----------------- -------------------- ------------------ -------------------- ----------
ONLINE             995548 15-FEB-2012:17:17:20            2446300 13-FEB-2013 15:09:44          1  -- Datafile(s) are at different checkpoint_change# (scn), so not consistent
ONLINE             995548 15-FEB-2012:17:17:20            2472049 13-FEB-2013 16:02:22          6

SQL>
SQL>
SQL> -- Check for datafile status, and fuzziness
SQL> select STATUS, ERROR, FUZZY,  count(*)     from v$datafile_header group by STATUS, ERROR, FUZZY   ;

STATUS  ERROR                                                             FUZ   COUNT(*)
------- ----------------------------------------------------------------- --- ----------
ONLINE                                                                    YES          7

SQL>
SQL>
SQL> -- Check for MIN, and MAX SCN in Datafiles
SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;

MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
                2446300                 2472049 

SQL>
SQL> select     substr(L.GROUP#,1,6)       GROUP#
  2         ,substr(L.THREAD#,1,7)         THREAD#
  3         ,substr(L.SEQUENCE#,1,10)      SEQUENCE#
  4         ,substr(L.MEMBERS,1,7)         MEMBERS
  5         ,substr(L.ARCHIVED,1,8)        ARCHIVED
  6         ,substr(L.STATUS,1,10)         STATUS
  7         ,substr(L.FIRST_CHANGE#,1,16)  FIRST_CHANGE#
  8         ,substr(LF.member,1,60)        REDO_LOGFILE
  9   from GV$LOG L, GV$LOGFILE LF
 10  where L.GROUP# = LF.GROUP# ;

GROUP# THREAD# SEQUENCE#  MEMBERS ARC STATUS     FIRST_CHANGE#    REDO_LOGFILE
------ ------- ---------- ------- --- ---------- ---------------- ------------------------------------------------------------
1      1       454        1       NO  CURRENT    2471963          /u01/app/oracle/oradata/V1123/redo01.log   <-- This is CURRENT log containing most recent redo, and is available 
3      1       453        1       YES INACTIVE   2471714          /u01/app/oracle/oradata/V1123/redo03.log
2      1       452        1       YES INACTIVE   2451698          /u01/app/oracle/oradata/V1123/redo02.log

SQL>
 

-- Use MIN(CHECKPOINT_CHANGE#) 2446300 as found before, then use it with this query to find the 
-- first SEQ# 'number' and archivelog file needed for recover to start with.
-- All SEQ# up to the online Current Redolog SEQ# must be available without any gap for successful recovery

-- MIN(CHECKPOINT_CHANGE#) 2446300

SQL> select thread#, sequence#, substr(name,1,80) from v$Archived_log
      where 2446300 between first_change# and next_change#;

   THREAD#  SEQUENCE# SUBSTR(NAME,1,80)
---------- ---------- --------------------------------------------------------------------------------
         1        449 /u01/app/oracle/oradata/V1123/arch1/arch_1_449_775329440.arc
         1        449 /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_449_8kq7oc6y_.arc
         1        450 /u01/app/oracle/oradata/V1123/arch1/arch_1_450_775329440.arc
         1        450 /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc

SQL>

SQL> select * from v$recover_file ;     -- Checking for Datafile(s) which needs recovery

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------------
         6 ONLINE  ONLINE                                                                       2446300 13-FEB-2013:15:09:44

SQL>

 

If you use a "BACKUP CONTROLFILE", or previously used a CANCEL based recover command, then we need to recover, and finally apply the online current redolog. 
   
Example:
 

SQL> select name, controlfile_type from v$database ;

NAME      CONTROL
--------- -------
V1123     BACKUP

SQL>
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

ORA-00279: change 2446300 generated at 02/13/2013 15:09:44 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc
ORA-00280: change 2446300 for thread 1 is in sequence #450

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2451694 generated at 02/13/2013 16:00:25 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_451_8kqbnbmh_.arc
ORA-00280: change 2451694 for thread 1 is in sequence #451
ORA-00278: log file '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc' no longer needed for this recovery
...
 < all required logs applied > 
...
ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc
ORA-00280: change 2471963 for thread 1 is in sequence #454
ORA-00278: log file '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_453_8kqbqvrk_.arc' no longer needed for this recovery   <-- All Redo, up to and including SEQ# 453 is applied 

ORA-00308: cannot open archived log '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc'    <<<-- "SEQ# 454" requested, which is in ONLINE REDOLOG as seen before 
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/V1123/system01.dbf'

SQL>
SQL> select * from v$recover_file ;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------------
         6 ONLINE  ONLINE                                                                       2471963 13-FEB-2013:16:02:19

SQL> 

SQL> alter database open resetlogs ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/V1123/system01.dbf'

SQL>

 

The following query will show you the SCN to which we must at least recover to, to get all datafiles consistent.

SQL> select  min(FHSCN) "LOW FILEHDR SCN"
           , max(FHSCN) "MAX FILEHDR SCN"
           , max(FHAFS) "Min PITR ABSSCN"
       from X$KCVFH ;

LOW FILEHDR SCN  MAX FILEHDR SCN  Min PITR ABSSCN
---------------- ---------------- ----------------
2446300          2472049          0

-- Example output explained: 
--
-- "LOW FILEHDR SCN"  - this is the SCN at which recovery process starts
-- "MAX FILEHDR SCN"  - this is the SCN we must recover to to get all datafiles consistent 
--
--   IF    "Min PITR ABSSCN"  != 0 AND  >  "MAX FILEHDR SCN"
--   THEN  "Min PITR ABSSCN"  is the SCN we must recover to to get all datafiles consistent
 

ABSSCN = Absolute SCN 

 

Comment:

In the above output/sample we see, redo (archivelogs)  was applied and datafile 6 was rolled forward but still needs more recovery and redo to be applied.
Database Recovery did not complete, because the online 'current' redolog is not applied automatically with a 'BACKUP' controlfile. 
As we use a Backup Controlfile we must 'manually' apply the online 'current' redolog '/u01/app/oracle/oradata/V1123/redo01.log' having SEQ# 454  ( SCN 2472049)  !
 

 

Additional Note:

As this is recover with a Backup Controlfile, or controlfile create from  Tracefile (sql> alter database backup to trace;) ,
query of v$log/v$logfile will possibly not give the correct information, about which logfile contains which sequence number (seq#).

# Options to find the Online log to be used

   a: Check the Alert.log file for the last sequences used with 'Online Redolog files'

   b: If Alert.log is lost you may simply try all online redolog files, if the wrong logfile is chosen nothing will be applied, 
      but you will see in in the output message which sequence is in that online redolog file. 
      Then simply try the next online redolog file until you get 'media recovery complete' message.

   c: You may also dump the file log file headers for Online redolog file(s)   
      Example:
      --------
      sql> alter system dump logfile '/u01/app/oracle/oradata/V1123/redo01.log' scn min 1 scn max 1 ; 
   
      -- This will write a tracefile with the header dump to your 'trace' (11g) [ or udump (<=10g) ] directory
      -- Check the tracefile for similar entry like...
      ~~~
      .. 
       descrip:"Thread 0001, Seq# 0000000454 ... 
       ..
       Low  scn: 0x.....
       Next scn: 0x.....
      ..
      ~~~

 

 

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
.
ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc
ORA-00280: change 2471963 for thread 1 is in sequence #454

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/u01/app/oracle/oradata/V1123/redo01.log'                  <-- specify the online redologfile having SEQ# 454to be manually applied 
Log applied.
Media recovery complete.
SQL> alter database open resetlogs ;

Database altered.

SQL>
 

 

Note:

If after applying all archive logs and online redo logs the database does not open 
please provide the following script output to Oracle support to assist with the recovery. 
( Please upload spooled file: recovery_info.txt )

SQL> set pagesize 20000
     set linesize 180
     set pause off
     set serveroutput on
     set feedback on
     set echo on
     set numformat 999999999999999

     Spool recovery_info.txt
     select substr(name, 1, 50), status from v$datafile;
     select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
     select GROUP#,substr(member,1,60) from v$logfile;
     select * from v$recover_file;
     select distinct status from v$backup;
     select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
     select distinct (fuzzy) from v$datafile_header;
     spool off
     exit;