Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Saturday, January 7, 2017

Query failing with error ORA-01157: and ORA-01110: After clone

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