You have certainly also received this error once in you Oracle DBA career: “ORA-01665: control file is not a standby control file” while trying to create a standby database with RMAN through a full database backup. In fact, the feature itself is not very new. It exists since at least RMAN 10g, it is called the “Restore Failover”.

In order to create a Data Guard configuration, you need a standby database on the standby server.

For this purpose you might use a “duplicate for standby” with RMAN. However in some cases, RMAN might complain since the the used backup controlfile is not a “standby controlfile”. This can appear even if the “backup current controlfile for standby” commandwas included in the the RMAN script.

The reason for this trouble is that after the “backup current controlfile for standby” command, another controlfile backup or backup copy has been started.

Let’s analyze the script:

run
{
   ALLOCATE_CHANNELS
   backup filesperset 10 archivelog all delete input format 'your_backup_path/your_backup_format';
   backup incremental level 0 filesperset 10 database format 'your_backup_path/your_backup_format';
   sql "alter system archive log current";
   backup filesperset 10 archivelog all delete input format 'your_backup_path/your_backup_format';
   backup current controlfile for standby format 'your_backup_path/ctl_stdby.ctl';
   sql "create pfile=''your_backup_path/initSID.ora'' from spfile";
   sql "alter database backup controlfile to ''your_backup_path/controlfile.ctl''";
   sql "alter database backup controlfile to trace as ''your_backup_path/cre_controlfile.trc''";
   
}

As you can see after the “backup current controlfile for standby”, we also have a backup controlfile copy:

sql “alter database backup controlfile to ”your_backup_path/controlfile.ctl””;

As a consequence, during the duplicate for standby, the database mount operation will fail since it will try to access to the controlfile copy which is NOT a standby controlfile ! Since 11.2 RMAN should be able to convert a control file in a standby controlfile during a duplication for standby, but with such a controlfile backup (through sql) it seems to fail:

RMAN> connect target /
RMAN> connect auxiliary sys/[email protected]_STDBY
RMAN> duplicate target database for standby dorecover nofilenamecheck;
 
....
....
Starting restore at 14-FEB-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input file name=/u00/app/oracle/admin/ORACLESID/backup/controlfile.ctl
output file name=/u00/oradata/ORACLESID/control01ORACLESID.dbf
output file name=/u01/oradata/ORACLESID/control02ORACLESID.dbf
output file name=/u02/oradata/ORACLESID/control03ORACLESID.dbf
Finished restore at 14-FEB-12
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/14/2012 14:37:45
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 02/14/2012 14:37:45
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01665: control file is not a standby control file
....
....

Since I experienced this issue several times, I decided to invest some minutes to find out another solution than:

  •  Duplicate from active (disturbing the production)
  •  Perform a new backup without “sql “alter database backup controlfile to ”controlfile__””;”

Below is a possible solution. First of all, perform a new backup of the standby control file:

RMAN> run {
2> allocate channel disk1 device type disk ;
3> backup current controlfile for standby format '/u00/app/oracle/admin/ORACLESID/std_ctl_%d_s%s_p%p_last.ctl';
4> }

As we can see on the output, in fact two backups are performed, a standby controlfile and a normal controlfile. While connected to the target we RMAN we recognize them:

RMAN> list backup of controlfile completed after 'sysdate - 1/24';
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
44289   Full    49.11M     DISK        00:00:01     14-FEB-2012 14:35:30
        BP Key: 44292   Status: AVAILABLE  Compressed: NO  Tag: TAG20120214T143529
        Piece Name: /u00/app/oracle/admin/ORACLESID/std_ctl_ORACLESID_s353_p1_last.ctl
  Standby Control File Included: Ckp SCN: 5369136371   Ckp time: 14-FEB-2012 14:35:29
BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
44298   Full    49.14M     DISK        00:00:00     14-FEB-2012 14:35:32
        BP Key: 44300   Status: AVAILABLE  Compressed: NO  Tag: TAG20120214T143532
        Piece Name: /u00/app/oracle/admin/ORACLESID/backup/c-1290365060-20120214-02
  Control File Included: Ckp SCN: 5369136386   Ckp time: 14-FEB-2012 14:35:32

 

We should copy the standby controlfile backup from the primary server to the standby server:

scp /std_ctl_ORACLESID_s353_p1_last.ctl server_standby:$PWD 

 

Then, once connected to the primary database and to the catalog with RMAN we try to identify the controlfile copy which is required by the “duplicate for standby” procedure (see the logs above). The required control file is stored in the file:

/u00/app/oracle/admin/ORACLESID/backup/controlfile.ctl

Let’s identify this item (controlfile copy) in the RMAN catalog:

RMAN> list copy of controlfile completed after 'sysdate - 1/2';
List of Control File Copies
===========================
Key     S Completion Time      Ckp SCN    Ckp Time            
------- - -------------------- ---------- --------------------
14      A 14-FEB-2012 09:09:05 5368236664 14-FEB-2012 09:09:04
        Name: /u00/app/oracle/admin/ORACLESID/backup/controlfile.ctl
        Tag: TAG20120214T090905

We now delete this copy to prevent RMAN to use it:

RMAN> delete copy of controlfile TAG 'TAG20120214T090905';
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
List of Control File Copies
===========================
Key     S Completion Time      Ckp SCN    Ckp Time            
------- - -------------------- ---------- --------------------
14      A 14-FEB-2012 09:09:05 5368236664 14-FEB-2012 09:09:04
        Name: /u00/app/oracle/admin/ORACLESID/backup/controlfile_ORACLESID_20120214_085409
        Tag: TAG20120214T090905

Do you really want to delete the above objects (enter YES or NO)? YES
deleted control file copy
control file copy file name=/u00/app/oracle/admin/ORACLESID/backup/controlfile_ORACLESID_20120214_085409 RECID=14 STAMP=775213745
Deleted 1 objects

Now, we should restart a duplicate for standby:

RMAN> duplicate target database for standby dorecover nofilenamecheck;

RMAN focuses now on the controlfile backup. As explained previously we get two controlfile backups (one “normal” and one “standby” controlfile). However the “normal” control file backup has not been copied to the standby server, therefore RMAN “fails over” to the “standby” controlfile backup:

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u00/app/oracle/admin/ORACLESID/backup/c-1290365060-20120214-02
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /u00/app/oracle/admin/ORACLESID/backup/c-1290365060-20120214-02
ORA-19505: failed to identify file "/u00/app/oracle/admin/ORACLESID/backup/c-1290365060-20120214-02"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u00/app/oracle/admin/ORACLESID/std_ctl_ORACLESID_s353_p1_last.ctl
channel ORA_AUX_DISK_1: piece handle=/u00/app/oracle/admin/ORACLESID/std_ctl_ORACLESID_s353_p1_last.ctl tag=TAG20120214T143529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u00/oradata/ORACLESID/control01ORACLESID.dbf
output file name=/u01/oradata/ORACLESID/control02ORACLESID.dbf
output file name=/u02/oradata/ORACLESID/control03ORACLESID.dbf
Finished restore at 14-FEB-2012 14:45:31
.......

The message about missing archives at the end of the duplicate can be ignored as usual, since the Data Guard configuration will solve the problem:

RMAN-06025: no backup of archived log for thread 1 with sequence 1683 and starting SCN of 5369129120 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1682 and starting SCN of 5369088625 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1681 and starting SCN of 5369047176 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1680 and starting SCN of 5369025699 found to restore
....

As you can see , Oracle failed over from the backup set (BS Key) “44298” to the “44289” since only the file /u00/app/oracle/admin/ORACLESID/std_ctl_ORACLESID_s353_p1_last.ctl (standby control file) has been copied to the standby server.

Finally we can also conclude that a simple remove of the file “your_backup_path/controlfile.ctl” from the first backup would have had the same result since the “previous” control file backup was a standby controlfile. This post finally show how to delete some backup sets :-).

As a conclusion, deleting entires in the catalog and/or providing the wished backup sets to the standby side allowed us to influe on RMAN to take the right decisions.


Thumbnail [60x60]
by
Yann Neuhaus