Workaround for RMAN-11003 and ORA-32010 during cloning

At the end of the duplication procedure, my script threw the following error:

RMAN-06162: sql statement: alter system reset  db_unique_name scope=spfile
 RMAN-08031: released channel: c1
 RMAN-08031: released channel: c2
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of Duplicate Db command at 06/12/2014 17:40:14
 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 06/12/2014 17:40:14
 RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile
 ORA-32010: ORA-32010: cannot find entry to delete in SPFILE 

 Recovery Manager complete.

After a brief search I found that my init.ora was the culprit: I had accidently left out single quotes in the db_file_name_convert parameter:

*.db_file_name_convert=('/u02/oradata/proddb01/datafile','/u02/oradata/testdb01','/u02/oradata/proddb01/tempfile',/u02/oradata/testdb01')

Oracle Support Dok ID 1384262.1 "ORA-32010: cannot find entry to delete in SPFILE" points this out.

The spfile was not in $OH/dbs physically, although Oracle believes it does:

SQL> show parameter spfile

NAME        TYPE        VALUE
 ---------- ----------- ---------------------------------------------
 spfile     string      /u01/oracle/product/11204/dbs/spfiletestdb01.ora

To prove it, I attempted to create an spfile:

SQL> create spfile from pfile;
 create spfile from pfile
 *
 ERROR at line 1:
 ORA-32002: cannot create SPFILE already being used by the instance 

When attempting to mount:

SQL> alter database mount;
 alter database mount
 *
 ERROR at line 1:
 ORA-01103: database name PRODDB01 in control file is not TESTDB01

So the control files are incorrect, written to during the duplication, and RMAN was unfortunately interrupted before the duplication procedure completed.

Solution:
Recreate the controlfile from the Source database, and open the auxiliary database with this new controlfile instead of the one that was generated during duplication.


1. Log onto the source server on which the PRODDB01 database is hosted, and execute

SQL> alter database backup controlfile to trace as '/tmp/proddb01_cntrfile.sql';

2. Transfer the file /tmp/proddb01_cntrfile.sql to the testserver
3. Shutdown the auxiliary database
4. Edit the script:
* Go to the section that says "Set #2. RESETLOGS case". You can strip away anything above this section, as you won't be needing it.
* Use the physical paths valid on the auxiliary server
* Exchange "REUSE DATABASE" with "SET DATABASE"
From Source database:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE DATABASE "PRODDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/proddb01/redo1.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/proddb01/redo2.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/proddb01/redo3.log'  SIZE 1000M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/proddb01/redo4.log'  SIZE 1000M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
   '/u02/oradata/proddb01/datafile/system.428.688578763',
   '/u02/oradata/proddb01/datafile/undotbs1.489.688574787',
   '/u02/oradata/proddb01/datafile/sysaux.436.688579377'
.
.
.
CHARACTER SET AL32UTF8
;

The new edited version:

STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE
 SET DATABASE "TESTDB01" RESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 200
     MAXINSTANCES 8
     MAXLOGHISTORY 1168
 LOGFILE
   GROUP 1 '/u02/oradata/testdb01/redo1.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 2 '/u02/oradata/testdb01/redo2.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 3 '/u02/oradata/testdb01/redo3.log'  SIZE 1024M BLOCKSIZE 512,
   GROUP 4 '/u02/oradata/testdb01/redo4.log'  SIZE 1024M BLOCKSIZE 512
 -- STANDBY LOGFILE
 DATAFILE
  '/u02/oradata/testdb01/system.428.688578763',
   '/u02/oradata/testdb01/undotbs1.489.688574787',
   '/u02/oradata/testdb01/sysaux.436.688579377',
   '/u02/oradata/testdb01/users.476.688577593',
   '/u02/oradata/testdb01/undotbs1.510.688564845',
   '/u02/oradata/testdb01/sl_part_data_2006.473.688577939',
.
.
.
CHARACTER SET AL32UTF8
;

5. Run the script on the newly cloned database:

SQL> sqlplus / as sysdba @proddb01_cntrfile.sql
Oracle instance started

 Total System Global Area    5344731136 bytes

 Fixed Size                     2255784 bytes
 Variable Size               1459618904 bytes
 Database Buffers            3875536896 bytes
 Redo Buffers                   7319552 bytes

Control file created.

6. Mount the database

SQL> alter database mount;

Database mounted.

7. Open the database

SQL> alter database open resetlogs;

Database opened.

Finally I had to handle TEMP tablespaces, as the alert log would tell you:

Dictionary check beginning
 Tablespace 'TEMP' #3 found in data dictionary,
 but not in the controlfile. Adding to controlfile.
 *********************************************************************
 WARNING: The following temporary tablespaces contain no files.
          This condition can occur when a backup controlfile has
          been restored.  It may be necessary to add files to these
          tablespaces.  That can be done using the SQL statement:

          ALTER TABLESPACE  ADD TEMPFILE

          Alternatively, if these temporary tablespaces are no longer
          needed, then they can be dropped.
            Empty temporary tablespace: TEMP

Add a file to the temporary tablespace using the REUSE clause:

SQL> alter tablespace temp add tempfile '/u02/oradata/testdb01/temp01.dbf' size 1024M reuse autoextend on next 32M maxsize unlimited;