RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

The most common error I’ve come across users posting in OTN forums when you are restoring a controlfile from autobackup is “RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece”

This error comes up when you either do not have the controlfile autobackup to restore the controlfile or the controlfile autobackups are
placed in a different location other than the Flash Recovery Area. By default, RMAN looks for the controlfile autobackup at location
“$ORACLE_HOME/dbs” if Flash Recovery Area (FRA) is not configured. If FRA is configured, then RMAN looks for the controlfile autobackup in
the FRA location. Here is an example I would like to simulate where the controlfile autobackup is taken to a non-default location.

Database Name: SRPRIM

Here you can see below that I am taking the controlfile autobackup to location ‘/u01/autobackup/’.

[oracle@ora1-1 ~]$ rman target sys/oracle@srprim

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 1411:39:09 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (DBID=216679430)

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name SRPRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u01/autobackup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db1/dbs/snapcf_srprim.f'; # default

Now, let me try to simulate the issue. For demonstrative purpose, I’ve shutdown the database, dropped the controlfile and started the
instance in nomount mode. Now, lets try to restore the controlfile from autobackup.

[oracle@ora1-1 ~]$ rman target sys/oracle@srprim

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 14 11:58:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (not mounted)

RMAN> set dbid=216679430

executing command: SET DBID

RMAN> restore controlfile from autobackup;

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

recovery area destination: +FRA
database name (or database unique name) used for search: SRPRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130114
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130113
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130112
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130111
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130110
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130109
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130108
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =======
RMAN-00571: ===================================================
RMAN-03002: failure of restore command at 01/14/2013 11:59:10
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

You can see above that RMAN threw the error “RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece” as it tried to search the autobackup in FRA as I had configured FRA for this instance. This can be seen from below set of RMAN configuration where the “CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F'” is not pointing to the specific location, instead its pointing to the default location.

RMAN> show all;
RMAN configuration parameters for database with db_unique_name SRPRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

To overcome this, we need to explicitly set the controlfile autobackup location to the location where the autobackups are placed. Below is what I’ve done and then restored the controlfile successfully.

RMAN> set controlfile autobackup format for device type disk to'/u01/autobackup/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 14-JAN-13
using channel ORA_DISK_1

recovery area destination: +FRA
database name (or database unique name) used for search: SRPRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130114
channel ORA_DISK_1: AUTOBACKUP found: /u01/autobackup/c-216679430-20130114-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/autobackup/c-216679430-20130114-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/srprim/controlfile/current.264.804686387
output file name=+FRA/srprim/controlfile/current.256.804686393
Finished restore at 14-JAN-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Here you go !!

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.