DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Oracle9i UNDO

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    I guess I am bit confused when I tried to create the database in Oracle 9i manually.

    On Oracle8i, we have:
    ROLLBACK_SEGMENTS=(RB1,RB2,RB3,RB4) in the parameter file.

    I am going to create the database in Oracle9i, and I won't use OMF option, and UNDO_MANAGEMENT I set it to manual instead of AUTO(since UNDO_MANAGEMENT will use SYS_UNDO plus the name convention is not nice) since we don't use rollback and I set UNDO_MANAGEMENT=manual so the question is:

    Do I have to have UNDO_SEGMENTS on my parameter file??? I add this on my init.ora and I got error.

    Everything works fine if I leave as

    ROLLBACK_SEGMENTS=(RB1,RB2,RB3,RB4)

    another word, If I create the UNDO tablespace and create rollback segment to UNDO tablespace, everything works fine. Is this correct ????



    can someone give me some advises???
    Thanks


    [Edited by ashley75 on 06-13-2002 at 11:18 AM]

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    You have a choice. You can use manual or automatic undo management. Manual uses rollback segments like the old days. Auto uses an undo tablespace with no rollback segments defined in it. My guess is you are trying to do something in between, hence you problem.

    You can't define rollback segments in your init.ora is you are using automatic. you'll probably only have something like this:

    UNDO_MANAGEMENT = AUTO # Default is MANUAL
    UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
    UNDO_RETENTION = 900 # The time undo is retained.
    # Default is 900 seconds.
    UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin
    # SQL statements are issued.

    Take a look at this article and try again:

    http://www.oracle-base.com/Articles/...Management.asp

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Jan 2002
    Posts
    474
    Tim,

    Thanks for your responce, this is that I have in my init.ora

    undo_management = auto
    undo_tablespace = undotbs
    undo_retention = 600

    and below is the sample of my create scripts:

    create database MYTEST
    controlfile reuse
    maxdatafiles 1022
    maxlogfiles 32
    maxlogmembers 5
    maxinstances 10
    character set "WE8ISO8859P1"
    datafile 'C:\ORADATA\MYTEST\system01.dbf' size 400m
    logfile ('C:\ORADATA\MYTEST\log1MYTEST_A.rdo',
    'C:\ORADATA\MYTEST\log1MYTEST_B.rdo') size 10M,
    ('C:\ORADATA\MYTEST\log2MYTEST_A.rdo',
    'C:\ORADATA\MYTEST\log2MYTEST_B.rdo') size 10M,
    ('C:\ORADATA\MYTEST\log3MYTEST_A.rdo',
    'C:\ORADATA\MYTEST\log3MYTEST_B.rdo') size 10M
    UNDO TABLESPACE undotbs_01 DATAFILE 'C:\ORADATA\MYTEST\undo0101.dbf';

    I got the foloowing error:

    create database MYTEST
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced


    could you please let me know what I did wrong ???





  4. #4
    Join Date
    Jan 2002
    Posts
    474
    Tim,

    I found out the problem is you have to specify the size of the UNDO tablespace. On your web site, you didn't specify the size of the UNDO tablespace so that I got the error above.

    Could you please confirm that we have to specify the size of the UNDO tablespace??? Is somehting I did wrong ??? Another questions I have is: When you use undo_management = auto . Oracle will create the UNDO segment by its own name convention , right ????

    Please let me know if I do this right


    Thanks

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    You have your undo tablespace called undotbs_01 but your init.ora parameter undo_tablespace = undotbs. This should be undotbs_01.

    Yes, the sizing was missing since it was only a small section of the create database command. I've added it in now to save any further confusion. Of course, if your using OMF you don't need the size or the file path to be specified.

    If you use the Database Configuration Assistant (DBCA) you can get it to save the creation script rather than run it. You will then see exactly what the statement should look like.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  6. #6
    Join Date
    Jan 2002
    Posts
    474
    Thanks Tim,

    I am bit clearer now with this new function, one last question, So if you set UNDO_MANAGEMENT=AUTO, Oracle will automatically create the UNDO segment with its own naming convention??? Its naming convention is not nice at all, is there a way for you to change it????

    Thanks

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by ashley75
    Thanks Tim,

    I am bit clearer now with this new function, one last question, So if you set UNDO_MANAGEMENT=AUTO, Oracle will automatically create the UNDO segment with its own naming convention??? Its naming convention is not nice at all, is there a way for you to change it????

    Thanks
    First, you have to create the undo tablespace. Oracle will create the undo segments. If the datafile(s) is OMF, then you can rename but with a lot of restrictions. You know for one that a file is considered OMF if its base file name had:
    - an "ora_" prefix
    - and a ".dbf", ".tmp", ".log" or ".ctl" extension
    What would you like to rename? From what to what?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Jan 2002
    Posts
    474
    Julian,

    Please correct me if I am wrong.

    I didn't have want to have OMF on my database, I only want to turn on UNDO_MANAGEMENT=auto. couple questions , please correct me if I misunbderstand:

    1. If you have OMF, Oracle will manage on of your file(control file, redo log file, and datafile), what about Rollback???
    2. Oracle will manage undo tablespace automatically IF you set UNDO_MANAGEMENT=AUTO, right??? Another word, OMF is seperate function with UNDO_MANAGEMENT, right???

    On my database now, I don't use OMF but I set UNDO_MANAGEMENT=AUTO, is it ok ??? what do you use for your production database, how every other DBA use for their database???

    when I use UNDO_MANAGEMENT=AUTO, Oracle created the follwing rollback segment:

    _SYSSSMU1$
    ......
    .....


    The bottom line I really like to undertsand is OMF and UNDO_MANAGEMENT si two seperate function in 9i right???

    Sorry I am bit confused now.

    thanks

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by ashley75
    1. If you have OMF, Oracle will manage on of your file(control file, redo log file, and datafile), what about Rollback???
    Could you please reformulate the question?

    2. Oracle will manage undo tablespace automatically IF you set UNDO_MANAGEMENT=AUTO, right??? Another word, OMF is seperate function with UNDO_MANAGEMENT, right???
    Right.

    On my database now, I don't use OMF but I set UNDO_MANAGEMENT=AUTO, is it ok ???
    It is OK.

    what do you use for your production database, how every other DBA use for their database???
    Use what?


    The bottom line I really like to undertsand is OMF and UNDO_MANAGEMENT si two seperate function in 9i right???
    They are separate things, yes.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Jan 2002
    Posts
    474
    what would you use if you have 9i on your production database???

    What is the best way for DBA to manage their database???

    Thanks

Posting Permissions

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


Click Here to Expand Forum to Full Width