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:
- After opening the standby database to read / write:
- Data Guard has been disabled for a long long time:
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.
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.
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:
- Stop the broker.
- Cancel the managed recovery process(MRP), if any.
- Restore the database by RMAN.
- Start MRP right after RMAN restoring completes.
- Meanwhile, we checked the status of MRP while the above statement was running.
- The MRP MR(fg) started with WAIT_FOR_LOG.
- After a while, the MRP MR(fg) was applying the archived logs.
- The process was gone just before errors came out.
SQL> alter system set dg_broker_start=FALSE scope=both;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered.
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
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.
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
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
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.
- Recover the standby database under SQL prompt. You can specify "AUTO" to let it to run as far as it can.
- Then start the MRP.
- Check the MRP.
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
...
SQL> alter database recover managed standby database;
...
Any ORA-01547? No, we don't see it.
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.
- Recover the standby database under RMAN prompt.
- Start the MRP.
- Check the MRP.
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.
SQL> alter database recover managed standby database;
...
Again, we don't see any ORA-01547.
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.