Duplicate Target Database in 11gR2

In 11gR2 oracle made lot of changes & one of the great feature is: we don't have to connect to either Target or Catlog database while cloning anymore. We can clone now by just connecting to the auxiliary instance.

Get the auxiliary instance in nomount.

#!/bin/ksh
ORACLE_HOME=
ORACLE_SID=ICMDEV
export ORACLE_HOME
export ORACLE_SID
$ORACLE_HOME/bin/rman msglog rest1.log append< connect auxiliary /
run
{
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
DUPLICATE DATABASE TO MYCLONE UNTIL TIME "TO_DATE('04/10/2011 22:32:00','MM/DD/YYYY HH24:MI:SS')" BACKUP LOCATION 'where the backup is';
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
}
EOF



Now after the clone I had a strange issue while querying dba_temp_files:

SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'


In Alert log:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'
Errors in file /icm01/u0001/oracle/diag/rdbms/icmdev/ICMDEV/trace/ICMDEV_j000_12561.trc: - TRACE FILE SHOWS some query related to grid control

---- Checked all the datafiles are online and consistent
select * from v$datafile where file#=513;

select * from v$tempfile where file#=513;
gives no result ---

SQL> alter database datafile 513 offline;
alter database datafile 513 offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "513"


Since I am querying the DBA_TEMP_FILES I know for sure something is not right with the temporary tablespaces.

select TABLESPACE_NAME,CONTENTS from dba_tablespaces a where a.contents like '%TEMP%';

TABLESPACE_NAME CONTENTS
TEMP1 TEMPORARY
TEMP2 TEMPORARY
GCTEMP TEMPORARY


SQL>
SQL> CREATE TEMPORARY TABLESPACE TEMP3;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP3;

Database altered.

SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'


SQL> DROP TABLESPACE TEMP1;

Tablespace dropped.

SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+ICMDEV_DATA_01'


SQL> DROP TABLESPACE TEMP2;

Tablespace dropped.

SQL> DROP TABLESPACE TEMP;
DROP TABLESPACE TEMP
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist


SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 517 - see DBWR trace file
ORA-01110: data file 517: '+ICMDEV_DATA_01'


GCTEMP 8192 1048576 1048576 1 2147483645 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO HOST NO
SQL> DROP TABLESPACE GCTEMP;

Tablespace dropped.

SQL> SELECT * FROM DBA_TEMP_FILES;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
+ICMDEV_DATA_01/icmdev/tempfile/temp3.367.748374189
6 TEMP3 104857600 12800 ONLINE
1 YES 3.4360E+10 4194302 12800 103809024 12672


SQL> EXIT

So after dropping the GCTEMP tablespace, the above query worked fine. My guess is during the backup gc temp file might have some contents which were not recorded.