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

Quite often we run into “RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece” when restoring a controlfile from autobackup, especially when trying to restore the backup from a differnt machine and a recovery catalog — a database schema that contains the RMAN repository data for one or more target databases, is not used. In that case, the RMAN repository stored in the control file of each target database.

Here is the procedures we usually take.

  1. Start up the database with a pfile/spfile in nomount state
  2. In RMAN, set dbid to the target database dbid. You can see it from the name of autobackup control files
  3. Run the RMAN command “restore controlfile from autobackup;”

Then you see the RMAN-06172 error. Why? Comon reasons are:

  • You put the autobackup somewhere else instead of under the fast recovery area.
  • Or it’s under the fast recovery area, but under a different location instead of default one.
  • Or the file names is not the default one defined by “%F”.
  • Or the file permission/ownership is wrong.

When database is not mounted and no recovery catalog is used, RMAN’s default setting for autobackup is:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

With “# default”, it means that is the default configuration of RMAN. “%F” here with “# default” has a different meaning than it has as described (and see below) in the guide. (Update on 2020.06.23 — I didn’t realize it until I wrote another post later. Or check this post to see the difference.)

%F’ combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

  • IIIIIIIIII stands for the DBID. The DBID is printed in decimal so that it can be easily associated with the target database.
  • YYYYMMDD is a time stamp in the Gregorian calendar of the day the backup is generated
  • QQ is the sequence in hexadecimal number that starts with 00 and has a maximum of ‘FF’ (256)
  • HINT: %F is valid only in the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT command.
  • The fast recovery area is an Oracle Database managed space that can be used to hold RMAN disk backups, control file autobackups and archived redo log files. The files placed in this location are maintained by Oracle Database and the generated file names are maintained in Oracle Managed Files (OMF) format.

So if you have configured your autobackup file format other than default “%F”, you need to use the set command to override the default one. For example, if you copy the autoback from another machine and it has the name like “cf_autobak_c-IIIIIIIIII-YYYYMMDD-QQ.ctl” and is under “tmp”, you need to run the following command first:

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/cf_autobak_%F.ctl';

Without setting this, even you specify the current path or correct path with a specific correct file name, you will still get “RMAN-06172” just because RMAN uses “%F” by default with a not mounted database, unless your file name just contain strings defined by “%F”.

Note, by default RMAN only looks for autobackup within 7 days, if you need to restore an autobackup older than 7 days, you can use “maxdays” as showed below which make RMAN to search for files within 30 days.

RESTORE CONTROLFILE FROM AUTOBACKUP MAXDAYS 30;

Reference:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s