13 Replies Latest reply: Feb 28, 2013 6:02 AM by Arunan.KL RSS

    Huge Archive geneartion

    941776
      Hi All,

      When we start the Oracle Apps Concurrent manager then there are few sessions and queries that are taking huge space and because of that archive directory gets full so fast that we are unable to control it..

      Below is the query:

      Following is the sql used by the sessions:
      23:58:42 sys@ITGPRD> @hash
      Enter value for hash: 3687172293
      old 1: select sql_text from v$sqltext where hash_value=&hash order by piece
      new 1: select sql_text from v$sqltext where hash_value=3687172293 order by piece
      SQL_TEXT
      ----------------------------------------------------------------
      begin fndcp_tmsrv.read_R_pipe(:ec, :to, :typ, :enddt, :rid, :C_p
      ipe, :nl, :nc, :dl, :secgrp, :usr, :rspap, :rsp, :log, :app, :pr
      g, :argc, :a1, :a2, :a3, :a4, :a5, :a6, :a7, :a8, :a9,
      :a10, :a11, :a12, :a13, :a14, :a15, :a16, :a17, :a18, :a19, :a20
      ); end;
      5 rows selected.


      Please help on this , what needs to be done to resolve this thing.

      Thanks!!
      Mohit
        • 1. Re: Huge Archive geneartion
          sb92075
          Mohit Ag wrote:
          Hi All,

          When we start the Oracle Apps Concurrent manager then there are few sessions and queries that are taking huge space and because of that archive directory gets full so fast that we are unable to control it..

          Below is the query:

          Following is the sql used by the sessions:
          23:58:42 sys@ITGPRD> @hash
          Enter value for hash: 3687172293
          old 1: select sql_text from v$sqltext where hash_value=&hash order by piece
          new 1: select sql_text from v$sqltext where hash_value=3687172293 order by piece
          SQL_TEXT
          ----------------------------------------------------------------
          begin fndcp_tmsrv.read_R_pipe(:ec, :to, :typ, :enddt, :rid, :C_p
          ipe, :nl, :nc, :dl, :secgrp, :usr, :rspap, :rsp, :log, :app, :pr
          g, :argc, :a1, :a2, :a3, :a4, :a5, :a6, :a7, :a8, :a9,
          :a10, :a11, :a12, :a13, :a14, :a15, :a16, :a17, :a18, :a19, :a20
          ); end;
          5 rows selected.


          Please help on this , what needs to be done to resolve this thing.

          Thanks!!
          Mohit
          DML generates REDO records
          • 2. Re: Huge Archive geneartion
            941776
            Thanks for your replly..

            Can you please provide the information in detail. And how can we resolve this thing?

            Thanks!!
            • 3. Re: Huge Archive geneartion
              Rob_J
              Hi,

              Looks like that SQL is just a call to execute a procedure within a package? You need to look at the actual SQL which is being executed.

              How much REDO is it generating?

              Rob
              • 5. Re: Huge Archive geneartion
                Helios-GunesEROL
                Hi,;

                Please review:
                monitor redo log (archiving )


                Regard
                Helios
                • 6. Re: Huge Archive geneartion
                  941776
                  Hi All,

                  when we stop the Conc Manager then archival stops and no archive files are generated. the moment we start it, the archive dir gets full..

                  How to find the concurrent ID which is resposible for generating the huge archive logs....

                  --What this below query does?  And how do we find the SQL TEXT for a SID....
                  select v$session.sid, username, value/1024/1024 redo_size_MB
                  from v$sesstat, v$statname, v$session
                  where v$sesstat.STATISTIC# = v$statname.STATISTIC#
                  and v$session.sid = v$sesstat.sid
                  and name = 'redo size'
                  and value > 0
                  and username is not null
                  order by value;

                  ---What the below query does
                  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


                  -- what this query does
                  select *
                  from fnd_concurrent_requests
                  where status_code in ('Q','I')
                  and requested_start_date > SYSDATE
                  and hold_flag = 'N';

                  Can anybody help me on this . it would be really helpful...

                  Thanks!
                  Mohit
                  • 7. Re: Huge Archive geneartion
                    941776
                    Hi All,

                    We just shut down the Concurrent Manager. Then I just logged on to Oralce EBS with SYSADMIN and went to Request -> View...

                    There is one request as shown below....

                    Name: Migrate Concurrent Programs
                    Status: On Hold
                    Phase: Inactive
                    Requestor: SYSADMIN
                    Priority: 50
                    Run the Job: As Soon As Possible

                    I have no idea about Oracle Apps. So Can I cancel this request? May be this is creating the problem...would there be any impact of cancelling this?

                    Thanks!!
                    Mohit Agrawal
                    • 8. Re: Huge Archive geneartion
                      941776
                      Can anyone help me on this?
                      • 9. Re: Huge Archive geneartion
                        Osama_Mustafa
                        Mohit Ag wrote:
                        Can anyone help me on this?
                        you can cancel request using GUi by press Cancel button or using sql plus
                        Update fnd_concurrent_requests SET PHASE_CODE=’C’ ,STATUS_CODE=’E’ WHERE request_id=’<request-id>′;
                        notice if you don't have any experience in oracle apps it's prefer to let DBA Apps do that._
                        • 10. Re: Huge Archive geneartion
                          941776
                          Hi Mustafa,

                          Many thanks for your reply. We do not have any Apps DBA so we only have to do it...
                          I just wanted to check if there would be any impact of cancelling this requets....and do you think it might be causing the huge archive logs problem?

                          Thanks!!
                          Mohit
                          • 11. Re: Huge Archive geneartion
                            Arunan.KL
                            ofcourse it affects the functionality. if you stop the process.

                            As of now.. try to schedule a script to move the archive logs to different mount point or schedule a RMAN backup for the archivelogs, with delete input clause.

                            so you have the backup safe.

                            further you can analyse the issue.
                            • 12. Re: Huge Archive geneartion
                              941776
                              Arunan.KL wrote:
                              ofcourse it affects the functionality. if you stop the process.

                              As of now.. try to schedule a script to move the archive logs to different mount point or schedule a RMAN backup for the archivelogs, with delete input clause.

                              so you have the backup safe.

                              further you can analyse the issue.
                              Actually the archival is so fast that it fills up before being deleted by script as we only have 5 GB in the arvhival mount.

                              So we have stopped CM as of now...as soon as we start the CM, the application goes down...plz suggest any thing that we can do..
                              • 13. Re: Huge Archive geneartion
                                Arunan.KL
                                if you have any other mount point with more space, you can direct the archivelog to it.

                                else some one from apps DBA group should help you soon..