DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: how to enable undo_management=auto after i upgrade 8i to 10g now that spfile is used?

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456

    how to enable undo_management=auto after i upgrade 8i to 10g now that spfile is used?

    hi,
    i hv upgraded my 8i test db to 10g. I wanted to switch to 10g's undo tablespace management and i hv created the undo tablespace...but i am at a lost how to switch the upgraded database to 10g's undo management as I cant seem to find a place to set the initialisation parameter UNDO_MANAGEMENT=AUTO as 10g is not using the text init.ora anymore...and i cant set it using 'Alter system set undo_management=auto' either it returns an error....wonder if anyone could help?

    many thanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm what do you mean 10g does not use pfile anymore...? delete your spfile and you will see how it uses plain pfile

    when you issue alter system you should add scope = BOTH

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    1. CREATE PFILE FROM SPFILE.

    2. Edit pfile and set following
    UNDO_MANAGEMENT
    UNDO_TABLESPACE
    UNDO_RETENTION

    3. STARTUP pfile=your_initora_file

    4. CREATE SPFILE FROM PFILE

    Next time you can startup without specifying pfile.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    pando/sanjay,

    thks for the enlightenment...i gave a try...new to 10g pardon my ignorance

  5. #5
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hi,

    I have tried Sanhay's method and manage to get the undo initialisation parameters set as follow:

    SQL> show parameters undo

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string undotbs_01

    and i m sure that my undo ts is valid....however i created a table and inserted some values...and then drop it...i run "show recyclebin" and there's nothing...wonder where did i miss anything?? any other parameters i hv to set?
    ngwh,
    Singapore.

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I don't think UNDO is problem here. Make sure you are not into space pressure.

    http://download-west.oracle.com/docs...sh.htm#1019427
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hi sanjay,

    attached my free tablespace list for your reference:

    TABLESPACE_NAME ALLOCATED_MB FREE_MB
    ------------------------------ ------------ ----------
    DRSYS 40 15
    INDX 20 20
    RBS 600 22
    SYSAUX 500 406.1
    SYSTEM 6600 3
    TEMP 1960 20
    TOOLS 150 6.8
    UNDOTBS_01 10 7.5
    USERS 20 19.9


    i hv created juz a 2 rows content table in USER ts and my undo ts is undotbs_01....how can there be space pressure? any comments?

    thks

  8. #8
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    i found out that after upgrading 8i to 10g even with the proper undo parameters set...flashback is still not possible is due to the fact that tablespaces are in EXTENT_MANAGEMENT=DICTIONARY instead of LOCAL.

    wonder if the rest encounter this before?
    so my next qn is how do i set the Extent Management to LOCAL from DICT ??? any guru can assist??

    thank in advance

  9. #9
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hi,

    anyway i hv found out how to switch DICT to LOCAL manage using DBMS_SPACE_ADMIN package.

    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