1 2 3 4 Previous Next 46 Replies Latest reply: Jan 6, 2013 9:35 AM by yxes2013 RSS

    Redo Logs Sizing

    yxes2013
      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
          make REDO logs 5 times larger.

          size of REDO is not involved with ORA-01555 errors.
          • 2. Re: Redo Logs Sizing
            vlethakula
            ORA-1555 errors related to UNDO.
            UNDO_MANAGEMENT=AUTO and UNDO_RETENTION configure accordingly
            • 3. Re: Redo Logs Sizing
              yxes2013
              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
                make REDO logs 5 times larger.
                You mean I will make it 50Mb size?

                Thanks
                • 5. Re: Redo Logs Sizing
                  sb92075
                  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
                    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
                      yxes2013 wrote:
                      make REDO logs 5 times larger.
                      You mean I will make it 50Mb size?

                      Thanks
                      YES
                      • 8. Re: Redo Logs Sizing
                        yxes2013
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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