Help -> ORA-03113

rhs98 · #1 (**permalink**) 02-22-02, 08:11

I am really stuck

;
I am running Oracle 9i on Win NT. I have a quiet large database, and was doing alot of deleting when I ran out of undo space.
Anyway; i shut down the database and then tried to start it up; every time i do i get the "ORA-03113 end-of-communication channel".

Don't really know what else anyone would need to know to help me (I am a newbie)! But it is quiet urgent.

Thanks for your help

Russell Smith
Software Projects
Serck Controls Ltd

Paul · #2 (**permalink**) 02-22-02, 09:02

Check the log files. What does it say in there.

I have encountered a corrupt datafile when running out of space before. The only way I could open the database was to 'offline drop' the affected tablespace.

rhs98 · #3 (**permalink**) 02-22-02, 09:14

Thanks; one problem - i am really a newbie to administering oracle! i.e. logfiles?!?!?!?

(i don't know where to find them; and every one else round here knows less than me.)

The error before about running out of tablespace was on the undo tablespace; should i try and drop this? or would the table i was deleting from be the one which is broken?

Also; I really don't want undo, also i don't have the disc space: how can i disable it?

Thanks

ps. sorry for the vast amount of newbie questions.

Paul · #4 (**permalink**) 02-22-02, 09:22

As you're talking about 'UNDO' rather than 'ROLLBACK', I'm assuming you're on 9i right?

I'm afraid you don't have a choice about having undo. You need either rollback segments or an undo tablespace.

As for the logfiles, what platform are you on? By default they should be under the %ORACLE_HOME%/admin/%SID%>/bdump In there you should have a file called %SID%alrt.log. Go to the bottom of this alert log ans see what it says.

rhs98 · #5 (**permalink**) 02-22-02, 09:27

Ok, will go and have a look in a minute for the logs;

with the undo / rollback what can i do? I have a 100 million row database which i need to delete just under 9 million rows from; i don't really need the undo option as i am sure: is there no way round this - for example forcing oracle to only beable to undo a certain number of steps?

thanks

rhs98 · #6 (**permalink**) 02-22-02, 09:36

Hi,
here is a zipped copy of the log; i had no idea how much of it to look at; it did not make much sense: something about cleanup lock conflict

what would happen if i dropped the undo tablespace?

Paul · #7 (**permalink**) 02-22-02, 10:00

Here's the lines we're interested in

Code:

Errors in file D:\oracle\admin\tvw\bdump\tvwPMON.TRC:
ORA-00601: cleanup lock conflict

Attach the trace file mentioned. It should tell us exactly what objects are causing the problem.

rhs98 · #8 (**permalink**) 02-22-02, 11:21

ok, will do; 10min - just moved the servers accross the building.

rhs98 · #9 (**permalink**) 02-22-02, 11:32

here we go! found it; though again reading through it leaves me confused!

Code:

Dump file D:\oracle\admin\tvw\bdump\tvwPMON.TRC
Fri Feb 22 12:23:46 2002
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows NT Version 4.0 Service Pack 6, CPU type 586
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Windows NT Version 4.0 Service Pack 6, CPU type 586
Instance name: tvw

Redo thread mounted by this instance: 1

Oracle process number: 2

Windows thread id: 385, image: ORACLE.EXE


*** 2002-02-22 12:23:46.000
*** SESSION ID:(1.1) 2002-02-22 12:23:46.000
error 601 detected in background process
ORA-00601: cleanup lock conflict
Dump file D:\oracle\admin\tvw\bdump\tvwPMON.TRC
Fri Feb 22 12:42:54 2002
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows NT Version 4.0 Service Pack 6, CPU type 586
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Windows NT Version 4.0 Service Pack 6, CPU type 586
Instance name: tvw

Redo thread mounted by this instance: 1

Oracle process number: 2

Windows thread id: 385, image: ORACLE.EXE


*** 2002-02-22 12:42:54.000
*** SESSION ID:(1.1) 2002-02-22 12:42:54.000
error 601 detected in background process
ORA-00601: cleanup lock conflict

thanks again!

Paul · #10 (**permalink**) 02-22-02, 11:53

It seems this may be a known bug. Here's the official word from Oracle regarding the 0601 problem:

Quote:

The Bug:2121637 is still in description phase and
the analysts are working on this issue. As you can see
that this Bug has last been updated on 17-JAN-02. you
cannot see the updates since it is commented out and
it is unpublished. You have to wait for the results
and we cannot state about the date on which this bug will be fixed.

You might want to try changing the UNDO_MANAGEMENT parameter from AUTO to MANUAL. You will need to create some rollback segments to replace the undo tablespace but at least it may let you open the database. Before you make those changes, you need to check whether you are using the old style init file or the new spfile format. If it was a fresh install of 9i, then you are probably using the spfile format. If so, the easiest way to make the changes is using the OEM GUI.

You'd really be better off talking to Oracle support on this. Especially if it's a production database. There are many things you can do to try and get the database up, but there are also many things that can go wrong which may make matters worse.

rhs98 · #11 (**permalink**) 02-22-02, 12:02

doh!

that does not sound good. It was a clean install a few weeks back. basically i am using oracle to convert data from one format to another; there are 50mil rows in that database and 100 on another (which works - for now).

What should i do? I really need to do this for monday (quickfix): It does not really matter about the long term stability of the database; as long as i can process it and remove it.

At worst case i can go back to a clean install: but whats not to say it wouldn't happen again? Have you any idea why this occurs - was it something i did?

what happened with the oracle: can't break in, can't break it campain??!?!?

Anyway, I am now worried about what to do with the other server (which has the 100mil rows in), as i am doing the same processing and deletion of large amounts of rows.
How can i control how much 'undo' oracle can undo? (if you see what i mean?) I really don't need it at all i think.

The only other thing was on the server which won't start there is some javasource in the database: is there anyway to retrieve this?

Paul · #12 (**permalink**) 02-22-02, 12:51

Quote:

What should i do? I really need to do this for monday (quickfix): It does not really matter about the long term stability of the database; as long as i can process it and remove it.

Do you have the Enterprise Manager Console installed (it's usually installed by default)? Call it up and go to the 'Configuration' item in the tree under 'Instance'. Then click on 'All Initialization Parameters' and change the UNDO_MANAGEMENT to MANUAL.

Quote:

Have you any idea why this occurs - was it something i did?

Nope... but it only happens if it needs to rollback due to an error, ie. running out of space.

Quote:

what happened with the oracle: can't break in, can't break it campain??!?!?

Quote:

How can i control how much 'undo' oracle can undo? (if you see what i mean?) I really don't need it at all i think.

You can't, at least not for DML operations.

Quote:

The only other thing was on the server which won't start there is some javasource in the database: is there anyway to retrieve this?

Not unless we can start the database.

If the UNDO settings don't change and you're not worried about losing the data (in case something goes wrong), there are some other things we can try.

rhs98 · #13 (**permalink**) 02-22-02, 13:00

ok,
i have just found that 5 minutes ago; also the undo retention time i have set to zero? good or bad thing?

just trying what you said...

whats DML operations???

thanks

rhs98 · #14 (**permalink**) 02-22-02, 13:08

BONUS!

It worked!

THANKS

would buy you a pint or something; but it seems your not quite round the corner...

Anyway;

How can i avoid the same mistake again; won't it still happen when i try and delete the rows?

Is there anyway to calculate the size of undo/rollback things neccessary to complete an operation
e.g. numberofrowstodelete/numberofrowsintable * 4 or something???

Again; many thanks for solving this! --> been running round looking for a solution...

rhs98 · #15 (**permalink**) 02-22-02, 13:14

just out of curiousity what kind of machines should i be using for this amount of data (100mill rows) when its being processed alot?

i have-
Compaq thing with 2x Xeon 733mhz 512kb cache
512mb ram
2x18gb raid array

dumbly enough the smaller database was put onto the faster machine;

Compaq (they like them here)
2x Xeon 933mhz 256kb cache
1gb ram
2x9gb raid array

have been told by a friend these are rubbish for the job?
anyway they seem really slow to do anything at all...count(*) takes ~5-6 minutes...

Internet Services

Web Development

Digital Marketing

Consumer Tech