This discussion is archived
1 2 3 4 Previous Next 46 Replies Latest reply: Jan 6, 2013 7:35 AM by yxes2013 RSS

Redo Logs Sizing

yxes2013 Newbie
Currently Being Moderated
Hi All,

OEL 5.6

Oracle 9.2.0.6

Are my redo logs enough for its number and size based on the switching timing? Thanks
-rw-r--r-- 1 oraprod dba    10486272 Jan  3 23:24 log01a.dbf
-rw-r--r-- 1 oraprod dba    10486272 Jan  3 23:24 log01b.dbf
-rw-r--r-- 1 oraprod dba    10486272 Jan  3 23:09 log02a.dbf
-rw-r--r-- 1 oraprod dba    10486272 Jan  3 23:09 log02b.dbf
Switching frequency in alert log:
Beginning log switch checkpoint up to RBA [0x21186.2.10], SCN: 0x056e.66a6d05f
Thread 1 advanced to log sequence 135558
  Current log# 1 seq# 135558 mem# 0: /u02/oracle/oaproddata/log01a.dbf
  Current log# 1 seq# 135558 mem# 1: /u02/oracle/oaproddata/log01b.dbf
Thread 1 cannot allocate new log, sequence 135559
Checkpoint not complete
  Current log# 1 seq# 135558 mem# 0: /u02/oracle/oaproddata/log01a.dbf
  Current log# 1 seq# 135558 mem# 1: /u02/oracle/oaproddata/log01b.dbf
Wed Jan  2 20:37:01 2013
Completed checkpoint up to RBA [0x21186.2.10], SCN: 0x056e.66a6d05f
Wed Jan  2 20:37:01 2013
Beginning log switch checkpoint up to RBA [0x21187.2.10], SCN: 0x056e.66a6d5e4
Thread 1 advanced to log sequence 135559
  Current log# 2 seq# 135559 mem# 0: /u02/oracle/oaproddata/log02a.dbf
  Current log# 2 seq# 135559 mem# 1: /u02/oracle/oaproddata/log02b.dbf
Thread 1 cannot allocate new log, sequence 135560
Checkpoint not complete
  Current log# 2 seq# 135559 mem# 0: /u02/oracle/oaproddata/log02a.dbf
  Current log# 2 seq# 135559 mem# 1: /u02/oracle/oaproddata/log02b.dbf
Wed Jan  2 20:37:07 2013
Completed checkpoint up to RBA [0x21187.2.10], SCN: 0x056e.66a6d5e4
Wed Jan  2 20:37:07 2013
Beginning log switch checkpoint up to RBA [0x21188.2.10], SCN: 0x056e.66a6e3f2
Thread 1 advanced to log sequence 135560
  Current log# 1 seq# 135560 mem# 0: /u02/oracle/oaproddata/log01a.dbf
  Current log# 1 seq# 135560 mem# 1: /u02/oracle/oaproddata/log01b.dbf
Wed Jan  2 20:37:18 2013
Thread 1 cannot allocate new log, sequence 135561
Checkpoint not complete
  Current log# 1 seq# 135560 mem# 0: /u02/oracle/oaproddata/log01a.dbf
  Current log# 1 seq# 135560 mem# 1: /u02/oracle/oaproddata/log01b.dbf
Wed Jan  2 20:37:18 2013
Completed checkpoint up to RBA [0x21188.2.10], SCN: 0x056e.66a6e3f2
Wed Jan  2 20:37:18 2013
Beginning log switch checkpoint up to RBA [0x21189.2.10], SCN: 0x056e.66a6f0a4
Thread 1 advanced to log sequence 135561
  Current log# 2 seq# 135561 mem# 0: /u02/oracle/oaproddata/log02a.dbf
  Current log# 2 seq# 135561 mem# 1: /u02/oracle/oaproddata/log02b.dbf
Wed Jan  2 20:40:14 2013
Completed checkpoint up to RBA [0x21189.2.10], SCN: 0x056e.66a6f0a4
Wed Jan  2 20:40:53 2013
Beginning log switch checkpoint up to RBA [0x2118a.2.10], SCN: 0x056e.66a70cb2
Thread 1 advanced to log sequence 135562
  Current log# 1 seq# 135562 mem# 0: /u02/oracle/oaproddata/log01a.dbf
  Current log# 1 seq# 135562 mem# 1: /u02/oracle/oaproddata/log01b.dbf
Wed Jan  2 20:43:24 2013
Completed checkpoint up to RBA [0x2118a.2.10], SCN: 0x056e.66a70cb2
And also I see frequent repeating ORA-1555 on the same statement in alert log, How can I avoid this?
Is this cause also by small redo logs?
Beginning log switch checkpoint up to RBA [0x211b2.2.10], SCN: 0x056e.70580afc
Thread 1 advanced to log sequence 135602
  Current log# 1 seq# 135602 mem# 0: /u02/oracle/oaproddata/log01a.dbf
  Current log# 1 seq# 135602 mem# 1: /u02/oracle/oaproddata/log01b.dbf
Wed Jan  2 21:26:14 2013
Completed checkpoint up to RBA [0x211b2.2.10], SCN: 0x056e.70580afc
Wed Jan  2 21:26:16 2013
ORA-01555 caused by SQL statement below (Query Duration=2913 sec, SCN: 0x056e.66a6f853):
Wed Jan  2 21:26:16 2013
 INSERT INTO RA_INTERFACE_ERRORS
 (INTERFACE_LINE_ID,
  MESSAGE_TEXT,
  INVALID_VALUE)
SELECT
INTERFACE_LINE_ID,
:b_err_msg6,
'trx_number='||T.TRX_NUMBER||','||'customer_trx_id='||TL.CUSTOMER_TRX_ID
FROM RA_INTERFACE_LINES_GT IL, RA_CUSTOMER_TRX_LINES TL, RA_CUSTOMER_TRX T
WHERE  IL.REQUEST_ID = :b1
AND    IL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND    T.CUSTOMER_TRX_ID =TL.CUSTOMER_TRX_ID
AND  IL.INTERFACE_LINE_CONTEXT = TL.INTERFACE_LINE_CONTEXT
AND IL.INTERFACE_LINE_ATTRIBUTE1 = TL.INTERFACE_LINE_ATTRIBUTE1
AND IL.INTERFACE_LINE_ATTRIBUTE2 = TL.INTERFACE_LINE_ATTRIBUTE2
AND IL.INTERFACE_LINE_ATTRIBUTE3 = TL.INTERFACE_LINE_ATTRIBUTE3
AND IL.INTERFACE_LINE_ATTRIBUTE4 = TL.INTERFACE_LINE_ATTRIBUTE4
AND IL.INTERFACE_LINE_ATTRIBUTE5 = TL.INTERFACE_LINE_ATTRIBUTE5
AND IL.INTERFACE_LINE_ATTRIBUTE6 = TL.INTERFACE_LINE_ATTRIBUTE6
AND IL.INTERFACE_LINE_ATTRIBUTE7 = TL.INTERFACE_LINE_ATTRIBUTE7
AND IL.INTERFACE_LINE_ATTRIBUTE8 = TL.INTERFACE_LINE_ATTRIBUTE8
AND IL.INTERFACE_LINE_ATTRIBUTE9 = TL.INTERFACE_LINE_ATT
Wed Jan  2 21:26:22 2013
Beginning log switch checkpoint up to RBA [0x211b3.2.10], SCN: 0x056e.7062ba10
Thread 1 advanced to log sequence 135603
  Current log# 2 seq# 135603 mem# 0: /u02/oracle/oaproddata/log02a.dbf
  Current log# 2 seq# 135603 mem# 1: /u02/oracle/oaproddata/log02b.dbf
Wed Jan  2 21:26:22 2013
How do I setup automatic undo management in 9i?

Thanks
  • 1. Re: Redo Logs Sizing
    sb92075 Guru
    Currently Being Moderated
    make REDO logs 5 times larger.

    size of REDO is not involved with ORA-01555 errors.
  • 2. Re: Redo Logs Sizing
    vlethakula Expert
    Currently Being Moderated
    ORA-1555 errors related to UNDO.
    UNDO_MANAGEMENT=AUTO and UNDO_RETENTION configure accordingly
  • 3. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    SQL> show parameter undo
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management                      string      AUTO
    undo_retention                       integer     1800
    undo_suppress_errors                 boolean     FALSE
    undo_tablespace                      string      APPS_UNDOTS1
    Do I need to increase undo_retention? By how much?

    Thanks
  • 4. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    make REDO logs 5 times larger.
    You mean I will make it 50Mb size?

    Thanks
  • 5. Re: Redo Logs Sizing
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    SQL> show parameter undo
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management                      string      AUTO
    undo_retention                       integer     1800
    undo_suppress_errors                 boolean     FALSE
    undo_tablespace                      string      APPS_UNDOTS1
    Do I need to increase undo_retention? By how much?

    Thanks
    I bet that after increasing UNDO_RETENTION that ORA-01555 will still occur.
    remove COMMIT from inside of LOOP, then ORA-01555 no longer occurs
  • 6. Re: Redo Logs Sizing
    CKPT Guru
    Currently Being Moderated
    yxes2013 wrote:
    make REDO logs 5 times larger.
    You mean I will make it 50Mb size?

    Thanks
    Configure redo log file size by considering 3-4 log switches an hour. And for optimal redo log size you can use view "v$instance_recovery"
  • 7. Re: Redo Logs Sizing
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    make REDO logs 5 times larger.
    You mean I will make it 50Mb size?

    Thanks
    YES
  • 8. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    Thanks all,

    Can you please suggest other ways to resolve ora-01555? I can not modify the programs as this this already tuned from oracle apps ebs people.

    We have been encountering very slow performance inssues. And all I can see causing it is this ora-1555 and this frequent log switch.

    Can you help me find other cause of slow using this statspack report? Can you find any hint? Thanks

    STATSPACK report for
    
    DB Name         DB Id    Instance     Inst Num Release     Cluster Host
    ------------ ----------- ------------ -------- ----------- ------- ------------
    OAPROD        4204192100 OAPROD              1 9.2.0.6.0   NO      oel5.prod
                                                                       e.local
    
                  Snap Id     Snap Time      Sessions Curs/Sess Comment
                --------- ------------------ -------- --------- -------------------
    Begin Snap:       551 02-Jan-13 22:00:04      123      59.1
      End Snap:       553 03-Jan-13 00:00:02       97      48.7
       Elapsed:              119.97 (mins)
    
    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
                   Buffer Cache:       288M      Std Block Size:          8K
               Shared Pool Size:       288M          Log Buffer:     10,240K
    
    Load Profile
    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                       ---------------       ---------------
                      Redo size:             54,437.02             65,579.53
                  Logical reads:          2,053,336.52          2,473,626.15
                  Block changes:                383.39                461.86
                 Physical reads:             43,270.67             52,127.57
                Physical writes:                 46.63                 56.18
                     User calls:                 35.83                 43.16
                         Parses:                 25.53                 30.76
                    Hard parses:                  0.18                  0.21
                          Sorts:                186.25                224.38
                         Logons:                  0.08                  0.10
                       Executes:                108.76                131.02
                   Transactions:                  0.83
    
      % Blocks changed per Read:    0.02    Recursive Call %:     85.81
     Rollback per transaction %:    3.18       Rows per Sort:      3.77
    
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:   99.99       Redo NoWait %:     99.98
                Buffer  Hit   %:   97.90    In-memory Sort %:    100.00
                Library Hit   %:   99.87        Soft Parse %:     99.31
             Execute to Parse %:   76.52         Latch Hit %:     99.96
    Parse CPU to Parse Elapsd %:   37.01     % Non-Parse CPU:     99.98
    
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   95.13   94.82
        % SQL with executions>1:   83.58   95.42
      % Memory for SQL w/exec>1:   79.28   92.57
    
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                     % Total
    Event                                               Waits    Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    CPU time                                                       74,717    84.80
    latch free                                        181,788       8,101     9.19
    db file scattered read                         47,546,136       2,893     3.28
    db file sequential read                        23,092,208       2,006     2.28
    buffer busy waits                               1,812,920         193      .22
              -------------------------------------------------------------
    ^LWait Events for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
    -> s  - second
    -> cs - centisecond -     100th of a second
    -> ms - millisecond -    1000th of a second
    -> us - microsecond - 1000000th of a second
    -> ordered by wait time desc, waits desc (idle events last)
    
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    latch free                        181,788    180,378      8,101     45     30.4
    db file scattered read         47,546,136          0      2,893      0  7,957.5
    db file sequential read        23,092,208          0      2,006      0  3,864.8
    buffer busy waits               1,812,920          2        193      0    303.4
    log file switch (checkpoint           124         51         69    560      0.0
    log file parallel write            21,251          0         39      2      3.6
    log file sync                       4,072          0         21      5      0.7
    control file parallel write         3,061          0         12      4      0.5
    library cache load lock                 5          2          9   1806      0.0
    log file switch completion             54          6          8    148      0.0
    PX Deq: Execute Reply               7,268          0          6      1      1.2
    PX Deq: Signal ACK                    297        103          6     21      0.0
    PX qref latch                         310        279          5     16      0.1
    PX Deq: Msg Fragment                4,301          0          4      1      0.7
    PX Deq Credit: send blkd            1,256          0          2      2      0.2
    PX Deq: Parse Reply                   267          0          2      7      0.0
    process startup                        46          0          2     39      0.0
    PX Deq: Table Q Sample                 84          0          2     22      0.0
    library cache pin                     337          0          1      4      0.1
    kksfbc child completion               116         76          1     11      0.0
    write complete waits                    2          1          1    491      0.0
    PX Deq: Join ACK                      212          0          1      4      0.0
    SQL*Net break/reset to clien        2,152          0          1      0      0.4
    enqueue                                80          0          1      7      0.0
    db file parallel read                  38          0          0     13      0.0
    SQL*Net more data to client        24,272          0          0      0      4.1
    log file single write                 160          0          0      2      0.0
    PX Deq: Table Q qref                   62          0          0      4      0.0
    control file sequential read        4,336          0          0      0      0.7
    direct path read                   45,818          0          0      0      7.7
    library cache lock                      1          0          0     76      0.0
    direct path write                  22,821          0          0      0      3.8
    LGWR wait for redo copy             5,387          0          0      0      0.9
    PX Deq Credit: need buffer            206          0          0      0      0.0
    undo segment extension             40,035     40,028          0      0      6.7
    db file parallel write              3,904          0          0      0      0.7
    db file single write                   40          0          0      0      0.0
    log file sequential read              160          0          0      0      0.0
    async disk IO                          40          0          0      0      0.0
    SQL*Net message from client       269,626          0    337,613   1252     45.1
    pipe get                            5,313      5,286     21,063   3964      0.9
    queue messages                      1,466      1,464     14,023   9566      0.2
    wakeup time manager                   239        238      6,738  28191      0.0
    PX Idle Wait                        3,475      3,042      6,026   1734      0.6
    jobq slave wait                       961        960      2,824   2939      0.2
    PX Deq: Execution Msg               9,423          1        159     17      1.6
    PX Deq: Table Q Normal              1,362          4          8      6      0.2
    SQL*Net more data from clien       12,643          0          2      0      2.1
    SQL*Net message to client         269,616          0          0      0     45.1
    ^LWait Events for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
    -> s  - second
    -> cs - centisecond -     100th of a second
    -> ms - millisecond -    1000th of a second
    -> us - microsecond - 1000000th of a second
    -> ordered by wait time desc, waits desc (idle events last)
    
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
              -------------------------------------------------------------
    ^LBackground Wait Events for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
    -> ordered by wait time desc, waits desc (idle events last)
    
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file parallel write            21,251          0         39      2      3.6
    latch free                            407        353         13     32      0.1
    control file parallel write         2,981          0         12      4      0.5
    db file scattered read              3,464          0          5      1      0.6
    db file sequential read             1,756          0          2      1      0.3
    rdbms ipc reply                       137          0          2     16      0.0
    log file single write                 160          0          0      2      0.0
    enqueue                                17          0          0      6      0.0
    control file sequential read        3,346          0          0      0      0.6
    LGWR wait for redo copy             5,387          0          0      0      0.9
    db file parallel write              3,904          0          0      0      0.7
    direct path read                    1,833          0          0      0      0.3
    direct path write                   1,833          0          0      0      0.3
    log file sequential read              160          0          0      0      0.0
    buffer busy waits                      21          0          0      0      0.0
    rdbms ipc message                  41,753      9,476     33,069    792      7.0
    pmon timer                          4,438      2,164      7,021   1582      0.7
    smon timer                            522          1      6,820  13064      0.1
              -------------------------------------------------------------
    ^LSQL ordered by Gets for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
    -> End Buffer Gets Threshold:     10000
    -> Note that resources reported for PL/SQL includes the resources used by
       all SQL statements called within the PL/SQL code.  As individual SQL
       statements are also reported, it is possible and valid for the summed
       total % to exceed 100
                            
    
                                                         CPU      Elapsd
      Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
      5,636,117,159           28  201,289,898.5   38.1 ######## #########  835260576
    Module: RAXTRX
     INSERT INTO RA_INTERFACE_ERRORS  (INTERFACE_LINE_ID,   MESSAGE_
    TEXT,   INVALID_VALUE) SELECT  INTERFACE_LINE_ID, :b_err_msg6, '
    trx_number='||T.TRX_NUMBER||','||'customer_trx_id='||TL.CUSTOMER
    _TRX_ID FROM RA_INTERFACE_LINES_GT IL, RA_CUSTOMER_TRX_LINES TL,
     RA_CUSTOMER_TRX T WHERE  IL.REQUEST_ID = :b1 AND    IL.INTERFAC
    
        511,626,129           20   25,581,306.5    3.5  8155.90  15045.56  205460129
    Module: JDBC Thin Client
    select  TRANSACTION_BRANCH , BRANCH_ADDRESS_1 , BRANCH_ADDRESS_2
     , BRANCH_PHONES , BRANCH_FAX , BRANCH_TIN , TRX_NUMBER , TRX_NU
    MBER_DISP , TRX_DATE , TRX_DATE_DISP , BILL_TO_CUSTOMER_ID , BIL
    L_TO_CUSTOMER_NAME , SHIP_DATE_ACTUAL_DISP , SHIP_TO_NAME , SHIP
    _TO_ADDRESS1 , SHIP_TO_ADDRESS2 , SHIP_TO_ADDRESS3 , SHIP_TO_ADD
    
        114,013,000        8,544       13,344.2    0.8  2919.47   4527.54  803969757
    Module: JDBC Thin Client
    SELECT COALESCE(DELV_ADDR.CITY, DELV_ADDR.PROVINCE) FROM APPS.OE
    _ORDER_HEADERS_ALL OE_HEADERS , APPS.RA_SITE_USES_ALL DELV_SITE
    , APPS.RA_ADDRESSES_ALL DELV_ADDR WHERE TO_CHAR(ORDER_NUMBER) =
    :B1 AND DELV_SITE.SITE_USE_ID(+) = OE_HEADERS.DELIVER_TO_ORG_ID
    AND DELV_ADDR.ADDRESS_ID(+) = DELV_SITE.ADDRESS_ID
    
        113,972,897        8,550       13,330.2    0.8  2924.20   4550.22 3761220362
    Module: JDBC Thin Client
    SELECT DELV_ADDR.ADDRESS1 FROM APPS.OE_ORDER_HEADERS_ALL OE_HEAD
    ERS , APPS.RA_SITE_USES_ALL DELV_SITE , APPS.RA_ADDRESSES_ALL DE
    LV_ADDR WHERE TO_CHAR(ORDER_NUMBER) = :B1 AND DELV_SITE.SITE_USE
    _ID(+) = OE_HEADERS.DELIVER_TO_ORG_ID AND DELV_ADDR.ADDRESS_ID(+
    ) = DELV_SITE.ADDRESS_ID
    
        113,972,850        8,551       13,328.6    0.8  2919.13   4533.69 4273350236
    Module: JDBC Thin Client
    full at > http://www.datafilehost.com/download-60d3df1e.html
    If downloading this file...
    Uncheck [ ] Use our download manager and get recommended downloads

    Edited by: yxes2013 on 3.1.2013 7:57
  • 9. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    Hi all,

    Does statspack show I/O, cpu, memory contentions?

    I got a feeling that the issue is on the hardisk I/O contentions.

    Please help,

    Thanks
  • 10. Re: Redo Logs Sizing
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Hi all,

    Does statspack show I/O, cpu, memory contentions?

    I got a feeling that the issue is on the hardisk I/O contentions.
    post SQL & results that provide evidence that above is true.
  • 11. Re: Redo Logs Sizing
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    How large is your database cache? Is that right when it says 288MB? That seems incredibly small to me. How large is the database?

    288MB for the shared pool might be a little on the small side, too, but it depends on your application really.

    How many cores does your server have? I ask because we need to know that in order to determine how much CPU you are using out of the total available. See [url http://www.ora00600.com/scripts/owi/cputime_top5.html]CPU top in Statspack

    Increasing your REDO log file sizes will help, a lot. So do that first and see where you are at with another statspack report from the same time period on a comparable day of the week.

    Then look at the top SQL statement, it's 38% of the total so that's the next thing to look at.

    For ORA-01555, how long does the query which is failing run for? If it needs to read data which is older than your undo_retention then that data could be overwritten in the UNDO tablespace. You need to size it correctly for your application needs.
  • 12. Re: Redo Logs Sizing
    jgarry Guru
    Currently Being Moderated
    I'd go 10x larger on the redo logs, and see how often they switch. My personal preference is to size them for the most active times (like massive loads), then set a timeout to switch based on a service level agreement (that would be a recovery thing), or 15 minutes if no one knows. The instance recovery view is useful, it told me to make them 1.4G on my system.

    That top SQL does seem suspect, but let's see what happens after getting rid of the redo waits (and hopefully, control file waits).
  • 13. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    How large is your database cache? Is that right when it says 288MB? That seems incredibly small to me. How large is the database?
    How much do I increase the database cache? My database is 32bit only :( and It can not go beyond 1.6Gb SGA.
    SQL> show parameter sga
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 1688804248
    SQL> show sga
    
    Total System Global Area 1688804248 bytes
    Fixed Size                   453528 bytes
    Variable Size            1375731712 bytes
    Database Buffers          301989888 bytes
    Redo Buffers               10629120 bytes
    We have 12 cores and 12Gb memory, OEL 5.6

    Please tell me how much is the safest number to increase undo_retention? I am afraid if I increase it , it will cause worst event happening. The existing value of undo_retention is 1800 is it in seconds?
    Can I make it 1 hr?


    Thank you all
  • 14. Re: Redo Logs Sizing
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    How large is your database cache? Is that right when it says 288MB? That seems incredibly small to me. How large is the database?
    How much do I increase the database cache? My database is 32bit only :( and It can not go beyond 1.6Gb SGA.
    post SQL & results that show database cache needs to be increased.
    We have 12 cores and 12Gb memory, OEL 5.6

    Please tell me how much is the safest number to increase undo_retention? I am afraid if I increase it , it will cause worst event happening. The existing value of undo_retention is 1800 is it in seconds?
    Can I make it 1 hr?
    I give up.
    Can you?

    when all else fails, Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams262.htm#REFRN10225


    UNDO_RETENTION has NO impact on ORA-01555 errors
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points