6 Replies Latest reply: Oct 3, 2013 7:55 AM by slateraus RSS

    user defined resource on cluster 11gr2

    SamTrashers
      Hi,

      i do not find the maximum user defined resource allowed ona 11.2.0.3. clusterware
      i will probably need to create 100 user defined resource, but don't know if this could be possible...

      does any one coul'd help me ?

      Thanks
        • 1. Re: user defined resource on cluster 11gr2
          Sebastian Solbach -Dba Community-Oracle
          Hi,

          the only restriction I am aware of, is that a database can only have 115 services (which will be a resource each).
          However you can have multiple databases.

          So I do not believe that there is a limit for user defined resources, though I did not try to create 100 myself...

          Regards
          Sebastian
          • 2. Re: user defined resource on cluster 11gr2
            JohnWatson
            Sebastian Solbach (DBA Community) wrote:
            Hi,

            the only restriction I am aware of, is that a database can only have 115 services (which will be a resource each).
            However you can have multiple databases.
            You can have 150 services now!
            orcl> begin for i in 1..200 loop
              2  dbms_service.create_service('s'||i,'s'||i);
              3  end loop;
              4  end;
              5  /
            begin for i in 1..200 loop
            *
            ERROR at line 1:
            ORA-44310: maximum number of services exceeded
            ORA-06512: at "SYS.DBMS_SERVICE", line 464
            ORA-06512: at "SYS.DBMS_SERVICE", line 229
            ORA-06512: at line 2
            
            
            orcl> select count(*) from dba_services;
            
              COUNT(*)
            ----------
                   150
            
            orcl> select * from v$version;
            
            BANNER
            ----------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
            PL/SQL Release 11.2.0.3.0 - Production
            CORE    11.2.0.3.0      Production
            TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            
            orcl>
            --
            John Watson
            http://skillbuilders.com
            • 3. Re: user defined resource on cluster 11gr2
              Sebastian Solbach -Dba Community-Oracle
              Hi John,

              that is in the database ;).

              Try the same with "srvctl add service"
              Then you will hit another limit...

              Regards
              Sebastian
              • 4. Re: user defined resource on cluster 11gr2
                JohnWatson
                There is indeed a limit, of 147:
                [oracle@iron1 ~]$  for i in {1..150}; do srvctl add service -d rdb -s s$i -r rdb1; done
                PRCD-1198 : Failed to add service s148 to database rdb because database is already at maximum services limit of 147
                PRCD-1198 : Failed to add service s149 to database rdb because database is already at maximum services limit of 147
                PRCD-1198 : Failed to add service s150 to database rdb because database is already at maximum services limit of 147
                [oracle@iron1 ~]$
                Well, that is today's bit of useless information :)
                --
                John Watson
                http://skillbuilders.com
                • 5. Re: user defined resource on cluster 11gr2
                  Jos van den Oord

                  see playing with Oracle resources other than databases http://joordsblog.vandenoord.eu/2011/07/rightavailabilityinracenvironment.html

                  If you create 100 DB's on one rac cluster then you get issues with restart of the services http://joordsblog.vandenoord.eu/2011/05/global-cache-service-processes-in.html

                  Oracle thinks big create one database with multiple user services to work with, for example Oraclwe services limited to around 115 see earlier post
                  The size and volume also depends on your hardware and resources,

                  Resource on the cluster, i think that there can be a problem as with multiple DB's. A 100 resource functionalities is a lot to ber managed on a two node RAC.
                  on a 20 node RAC this will be a other story

                  Cheers,

                  Jos van den Oord
                  < commercial spam links removed by moderator >

                  • 6. Re: user defined resource on cluster 11gr2
                    slateraus

                    From looking at Oracle Support, metalink 11.2.0.2 the limit is 118 inn 11.2.0.3 it is 150 but to be sure.

                     

                    The maximum number of services that can be created will depend on DB version, it is a hidden parameter.

                    But to check what you currently have set you have to query for the the hidden parameter _max_services, and to do that u can use the following:

                     

                    select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt,

                    decode (a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type,

                    a.ksppdesc description

                    from

                    sys.x$ksppi a,

                    sys.x$ksppcv b

                    where a.indx = b.indx

                    and a.ksppinm like '\_max_service%' escape '\'

                    order by name;

                     

                    Hope this helps.