7 Replies Latest reply: Feb 5, 2013 1:13 AM by Hussein Sawwan-Oracle RSS

    Huge Archived log files are generating at DB in R12

    user283850
      Hi all,

      Currently we are in R12.1.3 & DB 11.1.0.7 on RHEL 5.5

      Our DB size is 90gb and running in archive log mode,We have 4 redo log files each having 1gb size

      here issue is per one day there are nearly 100gb archive log files are generating at DB level from last one month onwards,So we can not move those files on daily basis to

      some other location.

      Aslo apps_undots1 tablespaces is not cleared automatically,We have to frequently increase the tablespace size by adding the datafiles currently it is 9000mb(3 data files each having 3gb size)

      Please help us how to stop more archive log file generation at DB level as well as apps_undots1 automatic clear

      Thanks in Advance
        • 1. Re: Huge Archived log files are generating at DB in R12
          Hussein Sawwan-Oracle
          925263 wrote:
          Hi all,

          Currently we are in R12.1.3 & DB 11.1.0.7 on RHEL 5.5

          Our DB size is 90gb and running in archive log mode,We have 4 redo log files each having 1gb size

          here issue is per one day there are nearly 100gb archive log files are generating at DB level from last one month onwards,So we can not move those files on daily basis to

          some other location.
          For archive log file, please see old threads for similar discussion.

          https://forums.oracle.com/forums/search.jspa?threadID=&q=archive+AND+logs&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001
          Aslo apps_undots1 tablespaces is not cleared automatically,We have to frequently increase the tablespace size by adding the datafiles currently it is 9000mb(3 data files each having 3gb size)

          Please help us how to stop more archive log file generation at DB level as well as apps_undots1 automatic clear

          Thanks in Advance
          For UNDO TS, please see:

          How to Manage Problem of UNDO Tablespace Reaching 100% of its Capacity [ID 1335964.1]
          Full UNDO Tablespace In 10gR2 [ID 413732.1]

          https://forums.oracle.com/forums/search.jspa?threadID=&q=Undo+AND+Tablespace+AND+Full&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

          Thanks,
          Hussein
          • 2. Re: Huge Archived log files are generating at DB in R12
            user283850
            Thanks alot Hussein for ur quick reply

            Those 2 1335964.1 & 413732.1 metalinks are related to 10G,Currently we are in 11.1.0.7...

            Yeaterday it self i added 2gb datafile to APPS_UNDOTS1 ts but today it got filled 100%

            SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'APPS_UNDOTS1' GROUP BY STATUS;

            STATUS SUM(BYTES)/1024/1024 COUNT(*)
            --------- -------------------- ----------
            UNEXPIRED 5458 1161
            EXPIRED 3499.9375 1291
            ACTIVE 1 1

            So in the aboce statment unexpired blocks are not clearing,Please let us know how to clear those expired blocks from the TS,keep on adding the datafiles to TS will not be the permanent solution,Please advice.

            Due to this TS 'APPS_UNDOTS1' filling frequently huge archive log files are generating,I shut down the concurrent managers and verified archive log file generation for 2 hr's.There is no single archive logfile got generated during this period.

            Please advice how to identify which concurrent program is generating more redo's

            Thanks in advance

            it
            • 3. Re: Huge Archived log files are generating at DB in R12
              Hussein Sawwan-Oracle
              Those 2 1335964.1 & 413732.1 metalinks are related to 10G,Currently we are in 11.1.0.7...
              It is 10g and above.
              Yeaterday it self i added 2gb datafile to APPS_UNDOTS1 ts but today it got filled 100%

              SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'APPS_UNDOTS1' GROUP BY STATUS;

              STATUS SUM(BYTES)/1024/1024 COUNT(*)
              --------- -------------------- ----------
              UNEXPIRED 5458 1161
              EXPIRED 3499.9375 1291
              ACTIVE 1 1

              So in the aboce statment unexpired blocks are not clearing,Please let us know how to clear those expired blocks from the TS,keep on adding the datafiles to TS will not be the permanent solution,Please advice.
              Do you see any errors in the database log file related to this? If not, then you can just ignore it.
              Due to this TS 'APPS_UNDOTS1' filling frequently huge archive log files are generating,I shut down the concurrent managers and verified archive log file generation for 2 hr's.There is no single archive logfile got generated during this period.

              Please advice how to identify which concurrent program is generating more redo's
              What concurrent program are running after you start the CM?

              Re: archive logs

              Thanks,
              Hussein
              • 4. Re: Huge Archived log files are generating at DB in R12
                user283850
                Hi Hussein,

                There are some custom concurrent programs are running but using some queries i found out sid's which are generating huge archives:

                SQL> select s.sid, s.value
                from v$sesstat s, v$statname n
                where s.statistic#=n.statistic#
                and n.name = 'redo size'
                order by 2
                / 2 3 4 5 6

                SID VALUE
                ---------- ----------
                332 108
                353 2244
                367 2248
                306 2268
                343 2316
                338 2320
                347 2408
                328 2788
                314 3536
                370 8500
                360 9192
                320 10712
                296 10740
                371 14684
                350 15460
                376 17892
                386 21860
                302 25764
                352 26756
                326 27520
                351 29832
                310 30124
                346 38532
                330 38840
                329 39580
                339 54256
                355 65596
                356 85460
                344 85860
                341 98428
                362 105960
                361 109692
                359 109812
                358 171304
                400 249340
                357 317828
                364 1994400
                388 2636992
                354 4381556
                392 35075900
                391 35357324



                IS there any way to find out details of those sid's like what it actually doing like any concurrent manager or program is generating huge archives,Please help us to resolve this.


                Thanks in advance
                • 5. Re: Huge Archived log files are generating at DB in R12
                  Hussein Sawwan-Oracle
                  Please see (Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running. [ID 735119.1]).

                  Thanks,
                  Hussein
                  • 6. Re: Huge Archived log files are generating at DB in R12
                    user283850
                    Thanks alot for your quick responce Hussein.......Issue got resolved :-)

                    By using this metalink note (735119.1) and sql query(select s.sid, s.value from v$sesstat s, v$statname n where s.statistic#=n.statistic# and n.name = 'redo size' order by 2 /) we found out one of the Concurrent requesting is causing huge undo data

                    We informed the same to owner of that program who submitted that request and he changed the code.After that Archive logfiles are

                    generated as expected.

                    Once again Thank you very much for your support....
                    • 7. Re: Huge Archived log files are generating at DB in R12
                      Hussein Sawwan-Oracle
                      Thanks for the update! And, you're welcome :)

                      Regards,
                      Hussein