Tuesday, October 27, 2020
dbametrix
More
    Home Oracle Troubleshooting ORA-01194: file 1 needs more recovery to be consistent

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to troubleshoot ORA-01194: file 1 needs more recovery to be consistent error?

    Related to my previous post, another thing that occurs frequently when updating an Oracle test database with a production backup, is the need to perform incomplete recovery UNTIL CANCEL. In this case, the RECOVER statement will ask us what archivelogs should be applied to leave the database in a consistent state.

    09:16:05 SYS @ test> recover database until cancel using backup controlfile;
    ORA-00279: change 2601063412 generated at 10/04/2020 03:40:16 needed for thread 1
    ORA-00289: suggestion: /u01/app/oracle/flash_recovery_area/test/archivelog
    /2020_04_10/o1_mf_1_58248_%u_.arc
    ORA-00280: change 2601063412 for thread 1 is in sequence # 58643
    09:17:34 Specify log: {= suggested | filename | AUTO | CANCEL}
    
    - Advertisement -
    dbametrix

    If we are recovering at the time the backup was taken, it includes archivelogs and we already copy them from the backup to the suggested directory (/ u01 / app / oracle / flash_recovery_area / test / archivelog / 2020_04_10), then we can indicate AUTO to apply them without major problems.

    For reference, restoring archivelogs with RMAN is done with the ‘restore archivelog …’ command.

    When it is necessary to go to a later time, using additional backups of archivelogs, and without having exactly defined the time we want to reach, we must manually accept each archivelog and then see if that is where we want to go (for example If you are interested in recovering until the previous moment in which data was deleted in a table, and we do not have the FLASHBACK functionality).

    On pressing enter and accepting the suggestion, if the file exists in the destination it applies:

    - Advertisement -
    dbametrix
    09:31:17 Specify log: {= suggested | filename | AUTO | CANCEL}
    ORA-00279: change 2601076177 generated at 04/12/2020 03:04:31 needed for thread 1
    ORA-00289: suggestion: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2020_04_10/o1_mf_1_51744_%u_.arc
    ORA-00280: change 2601076177 for thread 1 is in sequence # 58124
    ORA-00278: log file '/u01/app/oracle/flash_recovery_area/test/archivelog/2020_04_10/o1_mf_1_51743_8n0rj3kr_.arc' no
    longer needed for this recovery

    If we have not brought this archivelog from the backup yet, or we leave it in another directory, it will give this error:

    09:31:19 Specify log: {= suggested | filename | AUTO | CANCEL}
    ORA-00308: cannot open archived log
    '/u01/app/oracle/flash_recovery_area/test/archivelog/2020_04_10/o1_mf_1_51645_%u_.arc'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    After we apply all the archivelogs that we think are necessary to reach the time that interests us, we enter CANCEL. And this is where the problem can appear: the base is not yet at a consistent point.

    09:31:20 Specify log: {= suggested | filename | AUTO | CANCEL}
    
    cancel
    
    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: '/u02/oradata/test/system01.dbf'

    This is a warning that we missed applying archivelogs, or that we missed the point we were interested in (if we have several backups of archivelogs and we brought them all, we may already be applying the next group of files).

     

    When we cancel recovery and hit a consistent point, the message is clear:

     

    09:31:20 Specify log: {= suggested | filename | AUTO | CANCEL}
    
    cancel
    
    Media recovery canceled.

    Now the database is ready to be used after opening it with resetlogs.

     

    09:31:25 SYS @ test> alter database open resetlogs;

     

    Database altered.

    If we have many archivelogs to apply, being aware of the question to press enter is somewhat tedious, and can be easily automated with a bash script, which continues the application as long as the result of canceling the recovery is not successful:

    #/bin/bash
    ###############################
    # reco.sh
    #
    # ncalero 5/2010 - recover applying
    # archivelogs until CANCEL is successful
    #
    ###############################

     

    RESULT = / tmp / reco.txt
    LOG = / tmp / reco-full.txt

    # flag that counts if these errors appear. Ends when it’s zero
    SIGO = 1
    while [$ SIGO -gt 0]; do
    threw out “…”
    sqlplus / as sysdba <$ RESULT 2> & 1
    recover database until cancel using backup controlfile;
    CANCEL
    exit;
    EOF

    # We are looking for message “ORA-01194: file 1 needs more recovery to be consistent”

    SIGO = `grep -c 01194 $ RESULT`
    echo “ora-1194 = $ SIGO”
    if [[$ SIGO -gt 0]]; then
    # the error occurred, we double validation with the other error
    # ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    SIGO = `grep -c 01547 $ RESULT`
    echo “ora-1547 = $ SIGO”
    fi
    cat $ RESULT >> $ LOG
    done

    echo “### Can be retrieved !!! ###”

    When executing this script, two lines are displayed for try to cancel errors, and it ends when the database is left in a consistent state:

    [email protected]: /local/work/scripts> ./reco.sh


    ora-1194 = 1
    ora-1547 = 1

    ora-1194 = 1
    ora-1547 = 1

    ora-1194 = 1
    ora-1547 = 1

    ora-1194 = 1
    ora-1547 = 1

    ora-1194 = 1
    ora-1547 = 1

    ### It can be recovered !!! ###

    One last consideration: if when we enter cancel it tells us that it needs more recovery and we try to solve the problem by leaving the database at a point earlier in time than the last applied archivelog, we will get the following error:

    RMAN> recover database until scn 2691031614;

    Starting recover at 12 / APR / 2020 08:12:22
    using channel ORA_DISK_1
    RMAN-00571: ============================================= ==============
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ============================================= ==============
    RMAN-03002: failure of recover command at 12/04/2020 09:31:20
    RMAN-06556: datafile 1 must be restored from backup older than scn 2691031614

    This is because we cannot undo the archivelogs application that we made earlier with the RECOVER command. We have to bring the datafiles back from the backup with which we started this maneuver and apply the archivelogs up to that point, without going over again.

     

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Differences Raw Device, ASM Device and CFS

    The article explains basic differences of ASM device, raw device and Oracle cluster file system cfs with fundamentals

    Recent Comments