after clone while doing sanity received below error on running query as :
SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
select FILE_NAME,TABLESPACE_NAME from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1025 - see DBWR trace file
ORA-01110: data file 1025: '+TEST_DATA'
Alert log were having below error:
Cannot re-create tempfile +TEST_DATA, the same name file exists
On verifying the temp file at database level :
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+TEST_DATA
+TEST_DATA/testdb1/tempfile/temp.468.932365707
Decided to drop existing and recreate TEMP Tablespace:
1. Create Temporary Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+TEST_DATA' SIZE 200m;
2. Move Default Database temp tablespace to temp2:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3. Be sure that no sessions using your Old tablespace "Temp".
i.Session details from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
ii.Session ID from V$SESSION:
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;
iii. Kill Session.
ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;
4. Drop old temp tablespace:
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
5. Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+TEST_DATA' SIZE 4g;
6. Move Default Database temp tablespace to temp:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
7.Drop new created temp2 tablespace:
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Restart the database and issue rsolved....!!!
No comments:
Post a Comment