1 2 3 Previous Next 40 Replies Latest reply: Dec 23, 2009 3:42 AM by Vijayaraghavan Krishnan RSS

    ORA-00020: maximum number of processes (%s) exceeded

    718029
      Hello All,

      I am trying to make a Standby database where orcl is my primary database name and stan is my standby database name the following is my Pfile of standby database

      **.audit_file_dest='D:\oracle\product\10.2.0\admin\STAN\adump'*

      **.background_dump_dest='D:\oracle\product\10.2.0\admin\STAN\bdump'*

      **.core_dump_dest='D:\oracle\product\10.2.0\admin\STAN\cdump'*

      **.user_dump_dest='D:\oracle\product\10.2.0\admin\STAN\udump'*

      **.compatible='10.2.0.1.0'*

      control_files='D:\oracle\product\10.2.0\oradata\STAN\controlfile\stan.ctl','D:\oracle\product\10.2.0\flash_recovery_area\STAN

      *\CONTROLFILE\STAN.CTL'*

      db_name=orcl

      db_unique_name=stan

      LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,STAN)'

      orcl.__db_cache_size=427819008

      orcl.__java_pool_size=4194304

      orcl.__large_pool_size=4194304

      orcl.__shared_pool_size=163577856

      orcl.__streams_pool_size=0

      **.log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\STAN\ARCHIVING*
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
      DB_UNIQUE_NAME=STAN'

      LOG_ARCHIVE_DEST_2='SERVICE=STAN LGWR ASYNC
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=orcl'

      LOG_ARCHIVE_DEST_STATE_1=ENABLE

      LOG_ARCHIVE_DEST_STATE_2=ENABLE

      LOG_ARCHIVE_FORMAT=%T_%S_%R.ARC

      LOG_ARCHIVE_MAX_PROCESSES=30

      FAL_SERVER=orcl

      FAL_CLIENT=stan

      remote_login_passwoRdfile='EXCLUSIVE'

      DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl\DATAFILE','D:\oracle\product\10.2.0\oradata\stan\DATAFILE'

      LOG_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl\ONLINELOG','D:\oracle\product\10.2.0\oradata\stan\ONLINELOG'

      STANDBY_FILE_MANAGEMENT=AUTO

      but when i am trying to start my database it is giving me the following error...

      SQL> startup mount pfile='D:\oracle\product\10.2.0\db_1\database\stanpfile.ora'
      ORACLE instance started.

      Total System Global Area  117440512 bytes
      Fixed Size                  1247588 bytes
      Variable Size              58721948 bytes
      Database Buffers           50331648 bytes
      Redo Buffers                7139328 bytes
      Database mounted.
      SQL> create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\stanpfile.
      ora'
      *2 ;*

      File created.

      SQL> shutdown immediate;
      ORA-01109: database not open


      Database dismounted.
      ORACLE instance shut down.
      SQL> startup mount;
      ORACLE instance started.

      Total System Global Area  117440512 bytes
      Fixed Size                  1247588 bytes
      Variable Size              58721948 bytes
      Database Buffers           50331648 bytes
      Redo Buffers                7139328 bytes
      Database mounted.
      ORA-24324: service handle not initialized
      ORA-24323: value not allowed
      ORA-00020: maximum number of processes (%s) exceeded

      now according to me the problem is in the processes size ....
      but how to resolve it...

      Thanks & Regards
      Pratik Lakhpatwala
      Jr Oracle DBA
        • 1. Re: ORA-00020: maximum number of processes (%s) exceeded
          Robert Geier
          oerr ora 00020
          00020, 00000, "maximum number of processes (%s) exceeded"
          // *Cause:  All process state objects are in use.
          // *Action: Increase the value of the PROCESSES initialization parameter.

          Suggest you increase the "processes" init.ora parameter.

          show parameter processes
          • 2. Re: ORA-00020: maximum number of processes (%s) exceeded
            Pavan Kumar
            Hi,

            Check the below param value and update and check.
            show parameter processes

            - Pavan Kumar N
            Oracle 9i/10g - OCP
            http://oracleinternals.blogspot.com/
            • 3. Re: ORA-00020: maximum number of processes (%s) exceeded
              718029
              Hello Sir,

              When i am firing the query show parameter processess i am getting the following error...

              SQL> show parameter processess
              ORA-00604: error occurred at recursive SQL level 1
              ORA-04031: unable to allocate 4108 bytes of shared memory ("shared
              pool","select NUM , NAME , TYPE , ...","Typecheck","kgghteInit")

              Thanks & Regards
              Pratik Lakhpatwala
              Jr Oracle DBA
              • 4. Re: ORA-00020: maximum number of processes (%s) exceeded
                Robert Geier
                I suggest you learn to read the manuals and check error messages for yourself. Check your SGA / shared_pool_size.

                oerr ora 04031
                04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
                // *Cause:  More shared memory is needed than was allocated in the shared
                // pool.
                // *Action: If the shared pool is out of memory, either use the
                // DBMS_SHARED_POOL package to pin large packages,
                // reduce your use of shared memory, or increase the amount of
                // available shared memory by increasing the value of the
                // initialization parameters SHARED_POOL_RESERVED_SIZE and
                // SHARED_POOL_SIZE.
                // If the large pool is out of memory, increase the initialization
                // parameter LARGE_POOL_SIZE.
                • 5. Re: ORA-00020: maximum number of processes (%s) exceeded
                  Girish Sharma
                  Pratik,

                  Its process word. You have typo in show parameter.
                  SQL> show parameter process;
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- --------
                  aq_tm_processes                      integer     0
                  db_writer_processes                  integer     1
                  gcs_server_processes                 integer     0
                  job_queue_processes                  integer     10
                  log_archive_max_processes            integer     2
                  processes                            integer     150
                  Regards
                  Girish Sharma
                  • 6. Re: ORA-00020: maximum number of processes (%s) exceeded
                    Pavan Kumar
                    Hi,

                    show parameters shared%

                    Try to increase the SGA Size, but present SGA is not sufficient.

                    - Pavan Kumar N
                    Oracle 9i/10g - OCP
                    http://oracleinternals.blogspot.com/
                    • 7. Re: ORA-00020: maximum number of processes (%s) exceeded
                      718029
                      Hello Sir,

                      Sorry for late reply as there was a network failure at my office..

                      and i am getting the same response even if i fire the query you suggested

                      SQL> startup mount;
                      ORACLE instance started.

                      Total System Global Area 117440512 bytes
                      Fixed Size 1247588 bytes
                      Variable Size 58721948 bytes
                      Database Buffers 50331648 bytes
                      Redo Buffers 7139328 bytes
                      Database mounted.
                      SQL> show parameter process;
                      ORA-00604: error occurred at recursive SQL level 1
                      ORA-04031: unable to allocate 4108 bytes of shared memory ("shared
                      pool","select NUM , NAME , TYPE , ...","Typecheck","kgghteInit")


                      Thanks & Regards
                      Pratik Lakhpatwala
                      Jr Oracle DBA
                      • 8. Re: ORA-00020: maximum number of processes (%s) exceeded
                        718029
                        One more question i want to ask ....

                        I am performing Standby database so the following query should be fire on the primary database or the standby database in mount mode....

                        alter database recover managed standby database using current logfile disconnect

                        Thanks & Regards
                        Pratik Lakhpatwala
                        Jr Oracle DBA
                        • 9. Re: ORA-00020: maximum number of processes (%s) exceeded
                          Girish Sharma
                          Pratik,

                          Ok, it means you are not able to run show parameter at mount stage. Can you tell us following parameter in looking pfile :

                          1.shared_pool_size
                          2.sga_target
                          3.sga_max_size
                          4.shared_pool_reserved_size
                          5.process

                          I think you have to increase shared_pool_size or use of automatic SGA management by setting sga_target.

                          Between, please do'nt call me sir... Girish is enough.. "Sir" word for those who are really deserve it (i am zero in oracle)

                          Regards
                          Girish Sharma
                          • 10. Re: ORA-00020: maximum number of processes (%s) exceeded
                            sb92075
                            alter database recover managed standby database using current logfile disconnect
                            issue on STANDBY only
                            • 11. Re: ORA-00020: maximum number of processes (%s) exceeded
                              718029
                              Hello Sir,

                              when i am firing this query i am getting the following error


                              SQL> alter database recover managed standby database using current logfile disconnect;
                              alter database recover managed standby database using current logfile disconnect

                              *
                              ERROR at line 1:
                              ORA-00604: error occurred at recursive SQL level 1
                              ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select
                              count(*) from v$stand...","sql area","kglhin: temp")


                              Thanks & Regards
                              Pratik Lakhpatwala
                              Jr Oracle DBA
                              • 12. Re: ORA-00020: maximum number of processes (%s) exceeded
                                sb92075
                                from where is spfile file for STANDBY originate?
                                It should have started as a copy of the pfile from PRIMARY & then manually modified to pertain to its new role.
                                All "major" parameters should be same for PRIMARY & STANDBY.
                                Post contents of pfile from each back here
                                • 13. Re: ORA-00020: maximum number of processes (%s) exceeded
                                  718029
                                  **I have created the spfile from the pfile and then opened the stand by database in mount stage...**

                                  The following is the Pfile for both primary and standby database....


                                  PRIMARY DATABASE:-*

                                  db_name=orcl
                                  db_unique_name=orcl
                                  LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,STAN)'
                                  orcl.__db_cache_size=427819008
                                  orcl.__java_pool_size=4194304
                                  orcl.__large_pool_size=4194304
                                  orcl.__shared_pool_size=163577856
                                  orcl.__streams_pool_size=0
                                  **.log_archive_dest_1='LOCATION=D:\Archiving*
                                  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                                  DB_UNIQUE_NAME=ORCL'
                                  LOG_ARCHIVE_DEST_2='SERVICE=STAN LGWR ASYNC
                                  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                  DB_UNIQUE_NAME=STAN'
                                  LOG_ARCHIVE_DEST_STATE_1=ENABLE
                                  LOG_ARCHIVE_DEST_STATE_2=ENABLE
                                  LOG_ARCHIVE_FORMAT=%T_%S_%R.ARC
                                  LOG_ARCHIVE_MAX_PROCESSES=30
                                  remote_login_passwoRdfile='EXCLUSIVE'
                                  FAL_SERVER=STAN
                                  FAL_CLIENT=ORCL
                                  STANDBY_FILE_MANAGEMENT=AUTO
                                  DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\STAN\DATAFILE','D:\oracle\product\10.2.0\oradata\ORCL\DATAFILE'
                                  LOG_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\STAN\ONLINELOG','D:\oracle\product\10.2.0\oradata\ORCL\ONLINELOG'
                                  **.log_archive_start=TRUE*
                                  **.SPFILE='D:\oracle\product\10.2.0\db_1/dbs/spfileorcl.ora'*


                                  STANDBY DATABASE :-_

                                  **.audit_file_dest='D:\oracle\product\10.2.0\admin\STAN\adump'*
                                  **.background_dump_dest='D:\oracle\product\10.2.0\admin\STAN\bdump'*
                                  **.core_dump_dest='D:\oracle\product\10.2.0\admin\STAN\cdump'*
                                  **.user_dump_dest='D:\oracle\product\10.2.0\admin\STAN\udump'*
                                  **.compatible='10.2.0.1.0'*
                                  control_files='D:\oracle\product\10.2.0\oradata\STAN\controlfile\stan.ctl','D:\oracle\product\10.2.0\flash_recovery_area\STAN\CONTROLFILE\STAN.CTL'
                                  db_name=orcl
                                  db_unique_name=stan
                                  LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,STAN)'
                                  orcl.__db_cache_size=427819008
                                  orcl.__java_pool_size=4194304
                                  orcl.__large_pool_size=4194304
                                  orcl.__shared_pool_size=163577856
                                  orcl.__streams_pool_size=0
                                  **.log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\STAN\ARCHIVING*
                                  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                                  DB_UNIQUE_NAME=STAN'
                                  LOG_ARCHIVE_DEST_2='SERVICE=STAN LGWR ASYNC
                                  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                  DB_UNIQUE_NAME=orcl'
                                  LOG_ARCHIVE_DEST_STATE_1=ENABLE
                                  LOG_ARCHIVE_DEST_STATE_2=ENABLE
                                  LOG_ARCHIVE_FORMAT=%T_%S_%R.ARC
                                  LOG_ARCHIVE_MAX_PROCESSES=30
                                  FAL_SERVER=orcl
                                  FAL_CLIENT=stan
                                  remote_login_passwoRdfile='EXCLUSIVE'
                                  DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl\DATAFILE','D:\oracle\product\10.2.0\oradata\stan\DATAFILE'
                                  LOG_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl\ONLINELOG','D:\oracle\product\10.2.0\oradata\stan\ONLINELOG'
                                  STANDBY_FILE_MANAGEMENT=AUTO

                                  Thanks & Regards
                                  Pratik Lakhpatwala
                                  Jr Oracle DBA
                                  • 14. Re: ORA-00020: maximum number of processes (%s) exceeded
                                    sb92075
                                    my head hurts

                                    below is excerpt from STANDBY file

                                    db_unique_name=stan
                                    DB_UNIQUE_NAME=STAN'
                                    DB_UNIQUE_NAME=orcl'

                                    So which REALLY, Really, really is used?
                                    I have NO idea.

                                    Similar nonsense exists in PRIMARY.

                                    You're On Your Own (YOYO)!
                                    1 2 3 Previous Next