Home » Infrastructure » Linux » Problems encountered while using Oracle 9i in linux enterprise edition
Problems encountered while using Oracle 9i in linux enterprise edition [message #148558] Fri, 25 November 2005 00:02 Go to next message
mcanier
Messages: 4
Registered: November 2005
Junior Member
Hi there!

i'm here coz im in the process of looking for answer re error we encountered with oracle.

Here's the error:

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: '/oracle/oradata/telcora/undotbs02.dbf'


I also want to know some of the problems encountered with oracle 9i and linux enterprise edition. Hope you guys can help me.

Thanks!
Re: Problems encountered while using Oracle 9i in linux enterprise edition [message #148565 is a reply to message #148558] Fri, 25 November 2005 01:14 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


What were you doing when you got this error ??

Check whether the file physically exist at the mentioned location i.e /oracle/oradata/telcora/undotbs02.dbf
Re: Problems encountered while using Oracle 9i in linux enterprise edition [message #148570 is a reply to message #148565] Fri, 25 November 2005 01:40 Go to previous messageGo to next message
mcanier
Messages: 4
Registered: November 2005
Junior Member
actually im not the one handling the administration of the database. it's my offmate who does. She said tablespace was not enough and end up deleting that tablespace and created a new one. but after a week or two receive that error. (we're creating new tables when the error appeared)

she also saw this:

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ ONLINE UNDOTBS1
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ OFFLINE UNDOTBS2
_SYSSMU13$ OFFLINE UNDOTBS2
_SYSSMU14$ OFFLINE UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ OFFLINE UNDOTBS2
_SYSSMU17$ OFFLINE UNDOTBS2
_SYSSMU18$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19$ OFFLINE UNDOTBS2
_SYSSMU20$ OFFLINE UNDOTBS2
Re: Problems encountered while using Oracle 9i in linux enterprise edition [message #148572 is a reply to message #148570] Fri, 25 November 2005 02:01 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
You created a new UNDO tablespace but i think you didnt make those changes in the spfile /pfile .

I understood this :

UNDOTBS2 -- Old Undo tablespace that you deleted physically.
UNDOTBS1 -- NEw Undo tablespace created.

so , now try using this command by logging as SYSDBA,

 Alter system set undo_tablespace='UNDOTBS1' scope=both;



And by any chance , did you restarted the database after creating this new UNDO tablespace.

[Updated on: Fri, 25 November 2005 03:15]

Report message to a moderator

Re: Problems encountered while using Oracle 9i in linux enterprise edition [message #148579 is a reply to message #148572] Fri, 25 November 2005 02:57 Go to previous messageGo to next message
mcanier
Messages: 4
Registered: November 2005
Junior Member
she already change the tablespace to the new one..but same problem occurs
Re: Problems encountered while using Oracle 9i in linux enterprise edition [message #148582 is a reply to message #148579] Fri, 25 November 2005 03:03 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


I am afraid, can you post the output of this command

Note : log in as SYS

 show parameter undo;




Re: Problems encountered while using Oracle 9i in linux enterprise edition [message #148583 is a reply to message #148582] Fri, 25 November 2005 03:12 Go to previous message
mcanier
Messages: 4
Registered: November 2005
Junior Member
actually here's what happened:

Action that caused the error:
- Created a table but failed with the following error:

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: '/oracle/oradata/telcora/undotbs02.dbf'

Background of '/oracle/oradata/telcora/undotbs02.dbf':

'/oracle/oradata/telcora/undotbs02.dbf' is the datafile of UNDOTBS2. This tablespace managed the UNDO process of all transactions. We have two UNDO tablespace, the UNDOTBS1 and UNDOTBS2.
Originally, oracle was pointing to UNDOTBS1. Sometime around September, I changed the pointer to UNDOTBS2 since UNDOTBS1 was eating a lot of space and did not reuse the tablespace. I dropped the UNDOTBS1 and created new and empty UNDOTBS1 and datafile. By October, I pointed it back the UNDO to UNDOTBS1, there were no errors and been creating and using the database successfully for MASCOM since then. Tina was also able to create the TRA_reference tables, this is the last successful creation.
But as of Monday late afternoon (Nov 14), Joann can no longer create anymore a table for her cdr normalization process.

Current Settings:
Currently the Oracle is pointing to UNDOTBS1, its datafile '/oracle/oradata/telcora/undotbs01.dbf' is online while UNDOTBS2 and its '/oracle/oradata/telcora/undotbs02.dbf' datafile are offline.


Applied possible solutions:

1. First possible solution:
Online the UNDOTBS2 and '/oracle/oradata/telcora/undotbs02.dbf' datafile and point the UNDO to UNDOTBS2
- Effect:
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/oracle/oradata/telcora/undotbs02.dbf'

This means that the process is unable to find the data file '/oracle/oradata/telcora/undotbs02.dbf'.
So I attempted to create the datafile '/oracle/oradata/telcora/undotbs02.dbf'. But again, I encountered another error:
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle/oradata/telcora/undotbs02.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 4000M;
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS2' already exists

This is because, we really have existing UNDOTBS2, its just that its not online.


2. Second possible solution:
Drop the UNDOTBS2 and '/oracle/oradata/telcora/undotbs02.dbf' datafile and Create it again.
- Effect:
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11$' found, terminate dropping tablespace

This means that it cannot drop the UNDOTBS2 since there was a pending rollback segments on it. Maybe when the UNDO
pointer change from UNDOTBS2 to UNDOTBS1 sometime October, the database has a pending process to UNDOTBS2 but
the pointer was not pointing anymore to UNDOTBS2. Seems ironic, since it should have an errors immediately, but we did
not encounter any errors from MASCOM time until TRA_references time. The error appears just recently.

So I made some queries to look for the pending rollback segments, I should find some segments that has a status "NEED RECOVERY".
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ ONLINE UNDOTBS1
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ OFFLINE UNDOTBS2
_SYSSMU13$ OFFLINE UNDOTBS2
_SYSSMU14$ OFFLINE UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ OFFLINE UNDOTBS2
_SYSSMU17$ OFFLINE UNDOTBS2
_SYSSMU18$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19$ OFFLINE UNDOTBS2
_SYSSMU20$ OFFLINE UNDOTBS2


We have 3 segments need to recover. _SYSSMU11$, _SYSSMU15$ and _SYSSMU18$ with the same tablespace which is UNDOTBS2. I am not sure if its possible to alter the tablespace_name UNDOTBS2 to UNDOTBS3 of these segments so that I can create new UNDO as UNDOTBS3 or alter the
tablespace_name UNDOTBD2 to UNDOTBS1 since its currently pointing to UNDOTBS1. But I will not ever alter it since it might escalate the problem.

So I tried the following steps to fix these segements instead:

1. Set _offline_rollback_segments = ('List of rollback segments') in the init.ora parameter
2. Restart the database
3. Drop the tablespace
4. Create a new tablespace

But there were no effect and still I encounter the same errors above.
Previous Topic: Problem with Developer 10g on Linux Susse
Next Topic: where to get p3006854_9204_LINUX.zip patch
Goto Forum:
  


Current Time: Fri Mar 22 04:59:44 CDT 2019