Skip to content

How to Resolve ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

  • by

I got ORA-01547 when I was trying to recreate a standby database and make them sync.

In this post, I will explain the occasions to recreate a standby database in the beginning, and then reproduce ORA-01547 for you to show some facts about managed recovery process. In the last section, I propose two ways of recovery to solve it.

Occasions to Recreate Standby

There are several cases that you need to recreate the standby database:

  • After a failover:
  • A failover could make the current standby database (former primary database) deviates from the current primary database. You must recreate the standby database before going further.

  • After opening the standby database to read / write:
  • The standby database might be a good test environment for developers, but there is no way back to the standby role once you opened the standby database to read write.

    By the way, if you need a temporary test environment on the standby database, you don't have to open it to read / write. It's an irreversible process. Instead, you may consider to convert the standby database to a snapshot database and then convert it back at a later time.

  • Data Guard has been disabled for a long long time:
  • There could be a big apply gap between both databases. Recreating the standby database could be more economical than re-enabling the synchronization services.

Error ORA-01547

In the process of recreating the standby database, I got an error message like this:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/standb/datafile/system.273.799012455'

Let's take a look at the content of ORA-01547.

Description

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

Cause

Media recovery with one of the incomplete recovery options ended without error. However, if the ALTER DATABASE OPEN RESETLOGS command were attempted now, it would fail with the specified error. The most likely cause of this error is forgetting to restore one or more datafiles from a sufficiently old backup before executing the incomplete recovery.

Action

Rerun the incomplete media recovery using different datafile backups, a different control file, or different stop criteria.

It says that some data files could be not sufficiently old that caused the problem. But I don't think it's the root cause, the error message may mislead you away from the right track.

Reproducing ORA-01547

For reproducing ORA-01547 in the standby database, I take these steps:

  1. Stop the broker.
  2. SQL> alter system set dg_broker_start=FALSE scope=both;

    System altered.
  3. Cancel the managed recovery process(MRP), if any.
  4. SQL> alter database recover managed standby database cancel;

    Database altered.
  5. Restore the database by RMAN.
  6. You can assume that the standby database has a new control file and a full backup transported from the primary server if you doubt that.

    RMAN> restore database;

    Starting restore at 11-JAN-13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=68 instance=standb1 device type=DISK

    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to +DATA/standb/datafile/system.273.799012455
    channel ORA_DISK_1: restoring datafile 00002 to +DATA/standb/datafile/sysaux.272.799012459
    channel ORA_DISK_1: restoring datafile 00003 to +DATA/standb/datafile/undotbs1.271.799012465
    channel ORA_DISK_1: restoring datafile 00004 to +DATA/standb/datafile/users.268.799012479
    channel ORA_DISK_1: restoring datafile 00005 to +DATA/standb/datafile/example.270.799012469
    channel ORA_DISK_1: restoring datafile 00006 to +DATA/standb/datafile/undotbs2.269.799012475
    channel ORA_DISK_1: reading from backup piece +DATA/standb/backupset/2012_12_17/nnndf0_tag20121217t1106    .1079.802263975
    channel ORA_DISK_1: piece handle=+DATA/standb/backupset/2012_12_17/nnndf0_tag20121217t110610_0.1079.802263975 tag=TAG20121217T110610
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:04:38
    Finished restore at 11-JAN-13
  7. Start MRP right after RMAN restoring completes.
  8. SQL> alter database recover managed standby database;
    alter database recover managed standby database
    *
    ERROR at line 1:
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '+DATA/standb/datafile/system.273.799012455'

    Eventually, we got ORA-01547 after running a while and MRP was interrupted.

    For identifying which step triggered the problem, we need to know what MRP was doing before errors came out.

  9. Meanwhile, we checked the status of MRP while the above statement was running.
    1. The MRP MR(fg) started with WAIT_FOR_LOG.
    2. SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process like 'MR%';

         INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#  BLOCKS
      ---------- --------- ------------ ---------- ---------- ---------- -------
               1 MR(fg)    WAIT_FOR_LOG          0          0          0       0
    3. After a while, the MRP MR(fg) was applying the archived logs.
    4. Good! This is what we expected. But after a while...

      SQL> /

         INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#  BLOCKS
      ---------- --------- ------------ ---------- ---------- ---------- -------
               1 MR(fg)    APPLYING_LOG          2        134          0       0

      SQL> /

         INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#  BLOCKS
      ---------- --------- ------------ ---------- ---------- ---------- -------
               1 MR(fg)    APPLYING_LOG          1        143         32      39
    5. The process was gone just before errors came out.
    6. SQL> /

      no rows selected

    We have noticed that the MRP suddenly disappeared after it was applying archived logs for a while. The unreasoned termination of MRP could be the problem that generated ORA-01547. It seems that MRP is unreliable if the applying gap is too large to catch up with.

Theoretically, we can use MRP to recover the standby database until they are synchronized no matter how large the gap is as long as archived logs are available. But it sometimes fails.

Solutions to ORA-01547

Here are two solutions against ORA-01547. The key point is: You have to recover the database until the time that exceeds the point of time of the standby control file creation.

Cancel-based Recovery

Recover the standby database by cancel-based recovery, then MRP.

  1. Recover the standby database under SQL prompt.
  2. You can specify "AUTO" to let it to run as far as it can.
    SQL> recover standby database;
    ORA-00279: change 3364508 generated at 12/21/2012 21:42:38 needed for thread 2
    ORA-00289: suggestion :
    +DATA/standb/archivelog/2012_12_21/thread_2_seq_170.531.802647857
    ORA-00280: change 3364508 for thread 2 is in sequence #170

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    AUTO
    ...
  3. Then start the MRP.
  4. SQL> alter database recover managed standby database;
    ...

    Any ORA-01547? No, we don't see it.

  5. Check the MRP.
  6. SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process like 'MR%';

       INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#  BLOCKS
    ---------- --------- ------------ ---------- ---------- ---------- -------
             1 MR(fg)    WAIT_FOR_LOG          2        247          0       0
    It is waiting for new logs. The standby database is back to normal now.

RMAN Recovery

Recover the database by RMAN recover, then MRP.

  1. Recover the standby database under RMAN prompt.
  2. Let RMAN to run as far as it can.

    [oracle@standby01 ~]$ rman target /
    ...
    connected to target database: COMPDB (DBID=841830157, not open)

    RMAN> recover database;

    Starting recover at 11-JAN-13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=68 instance=standb1 device type=DISK

    starting media recovery

    archived log for thread 1 with sequence 136 is already on disk as file +DATA/standb/archivelog/2012_12_21/thread_1_seq_136.872.802624603
    archived log for thread 1 with sequence 137 is already on disk as file +DATA/standb/archivelog/2012_12_21/thread_1_seq_137.877.802624605

    ...
    archived log file name=+DATA/standb/archivelog/2012_12_22/thread_2_seq_245.452.802730401 thread=2 sequence=245
    archived log file name=+DATA/standb/archivelog/2012_12_22/thread_2_seq_246.450.802730413 thread=2 sequence=246
    unable to find archived log
    archived log thread=2 sequence=247
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 01/11/2013 19:44:12
    RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 247 and starting SCN of 3548861
    ...

    It shows some ignorable error messages which indicates that the recovery is finished without problems because the next sequence of redo log is not found.

  3. Start the MRP.
  4. SQL> alter database recover managed standby database;
    ...

    Again, we don't see any ORA-01547.

  5. Check the MRP.
  6. SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process like 'MR%';

       INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#  BLOCKS
    ---------- --------- ------------ ---------- ---------- ---------- -------
             1 MR(fg)    WAIT_FOR_LOG          2        247          0       0

They are synced now.

In fact, there're various approaches that can duplicate or recreate standby databases, not just through a full backup.

Leave a Reply

Your email address will not be published. Required fields are marked *