Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Thursday, April 14, 2011

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.








1 comment:

John Nash said...

Thx a lot For sharing such a nice information on
Oracle 11g DBA Online Training