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;
