remote dba support
More
    HomeOracle TroubleshootingORA-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}
    

    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.

    - Advertisement -
    dbametrix

    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:

    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:

    - Advertisement -
    dbametrix
    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:

    oracle@oraculo: /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.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

    This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix
    - Advertisment -
    remote dba services

    Most Popular