8 Replies Latest reply on Nov 15, 2016 8:10 AM by rthampi

    Oracle 10g | EBS R12 | job queue processes

    rthampi

      Hi guys

      We have one EBS 12 (12.0.6) instance that has Oracle 10g database (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0). Recently we made a clone of the instance and realized that the database experienced multiple issues while shutting down, mostly referring to

      ksvcreate: Process(m000) creation failed

       

      During cloning the database instance is created with job queue processes enabled and the processes count is 2 & we have setup the processes value to 10 as the instance was barely accessed and hardware resources meaky against the production instance. Along with it, we changed the concurrent standard manager work shifts also to a lower value

       

      Later as a part of our remedial exercises, we increased the job_queue_processes count to 20 and the database started shutting down normally, without producing the above errors. It looks very strange as our development instance machine has almost the same hardware specs and the value of job queue processes are set as 10 and we haven't had these errors throughout last 6.5 years

       

      Both development and the new clone are configured on enterprise Linux and patched with the same packages. Someone has some explanations?

       

      Thank you

        • 1. Re: Oracle 10g | EBS R12 | job queue processes
          asifkabirdba

          Before cloning the system may be new batch process which using job introduced in the prod ebs.  You did not get the error for the last 6.5 years but now its time to increase the parameter because load has been increased. As this is a test environment set job_queue_processes  = 15 and check for the errors.

           

          Regards

          Asif Kabir

          1 person found this helpful
          • 2. Re: Oracle 10g | EBS R12 | job queue processes
            rthampi

            Hello asifkabirdba

             

            Both the clones are against the latest cold backup from the Production instance. While the development instance database shuts down without making the "ksvcreate: Process(m000) creation failed " complaints, our Test instance does.

            My eagerness is to know whether the job queue processes has any significance towards shutting down the database (I am not a DBA), and I read the ksvcreate is a part of ADDM.

            One of the blogs suggested that this issue could be due to the DNS related issues and I have made sure that both the physical servers have the same DNS setups and networking parameters are replicated the same.

             

            Thanks for the reply Asif.

             

            regards,

            • 3. Re: Oracle 10g | EBS R12 | job queue processes
              Pavan Kumar

              Hi,

               

              1. Check the v$resource_limit and verify process parameter if its less please configure it correctly (modify process parameter)

              2.  Mean while you can verify from system nproc at OS level

               

              - Pavan Kumar N

              1 person found this helpful
              • 4. Re: Oracle 10g | EBS R12 | job queue processes
                rthampi

                Hello Praveen

                 

                 

                 

                Both the instances are setup with 131072 for nproc for Oracle user

                 

                 

                 

                Resource limit is set as 3600 on both instances.

                 

                 

                 

                As increasing the job queue processes has stopped the database getting into troubles during shutdown, I will try to reset the job queue processes to minimum and try to reproduce the errors.

                 

                 

                 

                Thanks buddy

                • 5. Re: Oracle 10g | EBS R12 | job queue processes
                  rthampi

                  Further examining the system parameters I found the following:

                   

                  # Oracle-Validated setting for kernel.shmall is 1073741824
                  kernel.shmall = 1073741824

                  # Oracle-Validated setting for kernel.shmmax is 4398046511104 on x86_64 and 4294967295 on i386 architecture. Refer Note id 567506.1
                  kernel.shmmax = 4398046511104

                   

                  Both the sysctl.conf and limits.conf files were copied from the development instance to the TEST instance that we setup last. I have adjusted the shmmax with half of the physical memory available (8GB) =4 and shmall 2GB

                   

                  The database instance shuts down without creating the "ksvcreate: Process(m000) creation failed" errors & the total application experience is 10 times faster than previously experienced. One more thing I wanted to mention here is, most of the times while I tried to shutdown the database, it were to switch to spfile and there were pending concurrent jobs. Should this be a problem? I ain't sure and may be some experts may comment.

                   

                  Anyway the dreaded errors are stopped now and I feel glad that I can safely shutdown and restart the instance without aborting it.

                   

                  regards,

                  • 6. Re: Oracle 10g | EBS R12 | job queue processes
                    rthampi

                    Another clone & I was able to pin point the exact time when the "ksvcreate" are being generated. Once the instance comes up and if I try to shutdown the instance while all the pending scheduled jobs are running I am running into ksvcreate failed notifications in the alert file. If I let all the concurrent jobs to complete gracefully and try to shutdown the database, it shuts down normally without complaining.

                    I think I have some kind of wrong configurations either with the database or the kernel parameters. If someone can look at them and suggest me the changes I will be truly grateful as I am struggling to find out the real causes!

                     

                    sysctl.conf

                    # Kernel sysctl configuration file for Red Hat Linux
                    #
                    # For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
                    # sysctl.conf(5) for more details.

                    # Disabled IPV6 on all interfaces for JAVA 1.5

                    # net.ipv6.conf.all.disable_ipv6 = 1

                    net.ipv6.conf.default.disable_ipv6 = 1
                    net.ipv6.conf.all.disable_ipv6 = 1
                    # Controls IP packet forwarding
                    net.ipv4.ip_forward = 0

                    # Controls source route verification
                    net.ipv4.conf.default.rp_filter = 1

                    # Do not accept source routing
                    net.ipv4.conf.default.accept_source_route = 0

                    # Controls the System Request debugging functionality of the kernel

                    # Controls whether core dumps will append the PID to the core filename
                    # Useful for debugging multi-threaded applications
                    kernel.core_uses_pid = 1

                    # Controls the use of TCP syncookies
                    net.ipv4.tcp_syncookies = 1

                    # Controls the maximum size of a message, in bytes

                    # Controls the default maxmimum size of a mesage queue

                    # Controls the maximum shared segment size, in bytes

                    # Controls the maximum number of shared memory segments, in pages

                    # For 11g, Oracle-Validated setting for fs.file-max is 6815744
                    # For 10g, uncomment 'fs.file-max = 327679', and comment 'fs.file-max = 6553600' entry and re-run sysctl -p
                    fs.file-max = 327679
                    #fs.file-max = 6815744

                    # Oracle-Validated setting for kernel.msgmni is 2878
                    kernel.msgmni = 2878

                    # Oracle-Validated setting for kernel.msgmax is 8192
                    kernel.msgmax = 8192

                    # Oracle-Validated setting for kernel.msgmnb is 65536
                    kernel.msgmnb = 65536

                    # Oracle-Validated setting for kernel.sem is '250 32000 100 142'
                    kernel.sem = 250 32000 100 142

                    # Oracle-Validated setting for kernel.shmmni is 4096
                    kernel.shmmni = 4096

                    # Oracle-Validated setting for kernel.shmall is 1073741824

                    kernel.shmall = 2097152 

                    # Oracle-Validated setting for kernel.shmmax is 4398046511104 on x86_64 and 4294967295 on i386 architecture. Refer Note id 567506.1
                    kernel.shmmax = 4294967296 << half of the physical memory which 8GB

                    # Oracle-Validated setting for kernel.sysrq is 1
                    kernel.sysrq = 1

                    # Oracle-Validated setting for net.core.rmem_default is 262144
                    net.core.rmem_default = 262144

                    # For 11g, Oracle-Validated setting for net.core.rmem_max is 4194304
                    # For 10g, uncomment 'net.core.rmem_max = 2097152', comment 'net.core.rmem_max = 4194304' entry and re-run sysctl -p
                    net.core.rmem_max = 2097152
                    #net.core.rmem_max = 4194304

                    # Oracle-Validated setting for net.core.wmem_default is 262144
                    net.core.wmem_default = 262144

                    # For 11g, Oracle-Validated setting for net.core.wmem_max is 1048576
                    # For 10g, uncomment 'net.core.wmem_max = 262144', comment 'net.core.wmem_max = 1048576' entry for this parameter and re-run sysctl -p
                    net.core.wmem_max = 262144
                    #net.core.wmem_max = 1048576

                    # Oracle-Validated setting for fs.aio-max-nr is 3145728
                    fs.aio-max-nr = 3145728

                    # For 11g, Oracle-Validated setting for net.ipv4.ip_local_port_range is 9000 65500
                    # For 10g, uncomment 'net.ipv4.ip_local_port_range = 1024 65000', comment 'net.ipv4.ip_local_port_range = 9000 65500' entry and re-run sysctl -p
                    net.ipv4.ip_local_port_range = 1024 65000
                    #net.ipv4.ip_local_port_range = 9000 65500

                    # Oracle-Validated setting for vm.min_free_kbytes is 51200 to avoid OOM killer
                    vm.min_free_kbytes = 51200

                     

                    limits.conf

                    * hard nofile 65535

                    * soft nofile 4096

                    * hard nproc 16384

                    * soft nproc 2047

                     

                    Target parameters

                     

                    SQL> show parameter target;

                    NAME         TYPE  VALUE
                    ------------------------------------ ----------- ------------------------------
                    archive_lag_target       integer  0
                    db_flashback_retention_target      integer  1440
                    fast_start_io_target       integer  0
                    fast_start_mttr_target       integer  0
                    pga_aggregate_target       big integer 1G
                    sga_target        big integer 2G
                    SQL> show parameter sga_max_size;

                    NAME         TYPE  VALUE
                    ------------------------------------ ----------- ------------------------------
                    sga_max_size        big integer 4G

                     

                     

                    & after the cloning of database I have setup the SGA_MAX_SIZE to 4GB.

                     

                    While the performance is excellent from this single processor TEST server, the disappoint remains exclusively when I want to shutdown the database. Any clues?

                     

                    Thanks in advance

                    • 7. Re: Oracle 10g | EBS R12 | job queue processes
                      Pavan Kumar

                      1. Performance of shutdown ? how much time and what all you need from alert log, after your initiated shutdown immediate

                      2. what is the order of shutdown you perform ?

                       

                      - Pavan Kumar N

                      • 8. Re: Oracle 10g | EBS R12 | job queue processes
                        rthampi

                        Pavan Kumar

                        Hello

                        I think I have fixed the issues with database shutdown. Recently I had to rebuild the clone instance for which I formatted the box with Oracle Linux 6 & noticed the R12 oracle-ebs-server-R12-preinstall didn't update the /etc/security/limits.conf & however adding

                         

                         

                        options attempts:5

                         

                        options timeout:15

                         

                        to /etc/resolv.conf

                         

                        Further the search value was set as my.home (as the FQDN of the test server is hosted in an isolated network and the hostname was set as hostname.my.home)

                        I have commented the options and search, made the resolve.conf as read only by setting the attribute, for the reasons that the login page was taking more than 2-3 minutes to load. Not only the above helped the login page to load faster (4-5 seconds) and I realized the database shutdown was happening instantly

                         

                        I really don't know why and how, however I think there are few elements related to the network was causing the dreaded errors I mentioned with my initial post(s)

                        If you have any thoughts, please update me.

                        1.PNG

                         

                        regards,