Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Question Unanswered: Help -> ORA-03113

    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

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    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.

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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.
    Last edited by rhs98; 02-22-02 at 10:17.

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    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.

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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

  6. #6
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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?
    Attached Files Attached Files
    Last edited by rhs98; 02-22-02 at 10:44.

  7. #7
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    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.

  8. #8
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    ok, will do; 10min - just moved the servers accross the building.

  9. #9
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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!

  10. #10
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    It seems this may be a known bug. Here's the official word from Oracle regarding the 0601 problem:
    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.

  11. #11
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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?

  12. #12
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    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.
    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.
    what happened with the oracle: can't break in, can't break it campain??!?!?

    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.
    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.

  13. #13
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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

  14. #14
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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...

  15. #15
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Question Curiousity?

    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...

    Last edited by rhs98; 02-22-02 at 14:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •