TEMPFILE ERROR ON ORACLE 10r2/LINUX
Hi folks. Here is one of the errors you can encounter on your job as a Database Java1istrator.
I was running a full export of a database (software) and I ran into this error. I hope sharing this error and the solution will help someone out there.
Environment
Linux 4.9
Oracle 10.2
Database name: software
Error
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6234
----- PL/SQL Call Stack -----
object line object
handle number name
0x34ec54e8 14916 package body SYS.KUPW$WORKER
0x34ec54e8 6293 package body SYS.KUPW$WORKER
0x34ec54e8 2339 package body SYS.KUPW$WORKER
0x34ec54e8 6854 package body SYS.KUPW$WORKER
0x34ec54e8 1259 package body SYS.KUPW$WORKER
0x5803b23c 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_06" stopped due to fatal error at 18:54:47
[oracle@software ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 26 18:58:12 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production
SQL> desc v$tempfile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
SQL> select name, status from v$tempfile;
NAME STATUS
-------------------------------------
/u10/oradata/software/temp01.dbf ONLINE
/u10/oradata/software/java1temp01.dbf ONLINE
/u10/oradata/software/java2temp01.dbf ONLINE
/u10/oradata/software/java3temp01.dbf ONLINE
4 rows selected.
Here comes the errors . From here you have a clue where the problem is and what you need to do to resolve this issue
SQL> desc dba_tempfile;
ERROR:
ORA-04043: object dba_tempfile does not exist
SQL> desc dba_tempfiles;
ERROR:
ORA-04043: object dba_tempfiles does not exist
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
ERROR:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u10/oradata/software/java301.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
no rows selected
Solution
From this error, you can tell that there is a problem with a tempfle named '/u10/oradata/software/java301.dbf'. What we need to do is figure out how to get that tempfile online and alter the user presently using this tempfile to use the default tempfile from TEMP tablespace.
Crosscheck the default and temporary tablespaces in the database. This will give you an idea of where to go to get the issue resolved
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
JAVA1
JAVA1TEMP
JAVA2
JAVA2TEMP
JAVA3
JAVA3TEMP
Check for the user(s) presently using the JAVA3TEMP that has issues
SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';
USERNAME
------------------------------
JAVA3
Change the temporary tablespace of user JAVA3 from JAVA3TEMP to TEMP. This will ensure that the user can use the default temporary tablespace
SQL> alter user JAVA3
2 temporary tablespace TEMP;
User altered.
Check to ensure that the user is properly altered to use TEMP instead of JAVA3TEMP
SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';
no rows selected
Take the problematic tempfile offline
SQL> alter database tempfile '/u10/oradata/software/java3temp01.dbf' offline;
Database altered.
Please drop a comment if you have any questions. Thanks and watch this space for more posts!
No comments:
Post a Comment