The backup database command will include the spfile and controlfile. When controlfile autobackup is set to ON, and only when it is set to ON, then RMAN will make additional copies of the controlfile and spfile to the autobackup directory inside the FRA.

RMAN-06172:  No autobackup found or specified handle is not a valid copy or piece
 Cause: A restore could not proceed because no autobackup was found or the specified handle is not a valid copy or backup piece.
 In case of restore from autobackup, it may be the case that a backup exists, but it does not satisfy the criteria specified in the user’s restore operands.
 In case of restore from handle, it may be the handle is not a backup piece or controlfile copy. It may be that it does not exist.
 Action: Modify autobackup search criteria or verify the handle.

Oracle looks by default in the %ORACLE_HOME%/database (or) $ORACLE_HOME/dbs directory and you need to change your RMAN command to point to the real spfile location

Backup SPFILE:-

RMAN> backup spfile;
Starting backup at 20-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20-FEB-12
channel ORA_DISK_1: finished piece 1 at 20-FEB-12
piece handle=C:ORACLEFLASH_RECOVERY_AREAORCLBACKUPSET2012_02_20O1_MF_NNSNF_TAG20120220T085314_7N3H52O5_.BKP tag=TAG20120220T085314 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-FEB-12
C:Usersbn2676>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 20 08:53:36 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Shutdown Instance & Removed SPFILE & Any Existing old PFILE’s.

C:Usersbn2676>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 20 09:23:41 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
RMAN> set dbid 1303021375;
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\DB_1DATABASE\INITORCL.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area     159019008 bytes
Fixed Size                     1373264 bytes
Variable Size                 75500464 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6647808 bytes
RMAN> restore spfile from autobackup;
Starting restore at 20-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120220
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120219
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120218
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120217
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120215
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120214
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 02/20/2012 09:24:16
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Now Forcefully point to look into FRA location & DB_NAME by below command

RMAN> restore spfile from autobackup db_recovery_file_dest='C:ORACLEFLASH_RECOVERY_AREA' db_name='orcl';
Starting restore at 20-FEB-12
using channel ORA_DISK_1
recovery area destination: C:ORACLEFLASH_RECOVERY_AREA
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP C:ORACLEFLASH_RECOVERY_AREAORCLAUTOBACKUP2012_02_20O1_MF_S_775732280_7N3J713L_.BKP found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120220
channel ORA_DISK_1: restoring spfile from AUTOBACKUP C:ORACLEFLASH_RECOVERY_AREAORCLAUTOBACKUP2012_02_20O1_MF_S_775732280_7N3J713L_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 20-FEB-12
RMAN> exit
C:Usersbn2676>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 20 09:24:48 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup force nomount
ORACLE instance started.
Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             276825512 bytes
Database Buffers          494927872 bytes
Redo Buffers                5259264 bytes
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\DB_1\DATABASE\SPFILEORCL.ORA
SQL>

Now SPFILE restore from FRA of ORCL database.

Website Comments

  1. judy
    Reply

    This article is great. I encountered exactly same error while I was doing a restore of db from a remote system. I googled and found your article. I used the method you mentioned and it resolved my issue.
    This is great help and very nice of you to post this issue here.
    Thanks again.

    • Nassyam Basha
      Reply

      Judy,
      This error not belongs to me, someone posted in OTN.. So i tested with those errors how to resolve it.
      Hope you enjoyed it. Thanks for your feedback. 🙂

Post a comment

*