6 Replies Latest reply: Jan 23, 2013 6:42 AM by Sebastian Solbach -Dba Community-Oracle RSS

    Configuring extproc on clusterware installation (not RAC)

    Pyrocks
      Hi,

      please excuse me if I posted this in the wrong forum - i wasn't sure exactly where it belongs.
      I have configured the use of extproc (external procedures) for single instances many times before, but i'm unable to make it work on a clusterware installation.
      Note: This is not RAC nor RAC One Node - this is a single instance defined as a resource on a clusterware installation to provide active/passive HA.

      clusterware owner=grid
      clusterware home=/oracle/11.2.0/grid
      DB version=11.2.0.3
      OS=linux RHEL6 64bit

      contents of /oracle/11.2.0/grid/network/admin/listener.ora
      LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
      LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
      contents of /oracle/11.2.0/grid/network/admin/endpoints_listener.ora (I removed IP and changed server name):
      LISTENER_SERVER-X=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER-X-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)(IP=FIRST))))               # line added by Agent
      I need to add both EP_LIB_PATH and EXTPROC_DLLS to the listener.
      This is what I do in a single instance installation:
      tnsnames.ora
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      
      ORACLR_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
          (CONNECT_DATA =
            (SID = CLRExtProc)
            (PRESENTATION = RO)
          )
        )
      listener.ora
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(PORT = 1521))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
        )
      
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = /oracle/app/oracle/product/11.2.0.3/dbhome_1)
            (PROGRAM = extproc)
            (ENVS = "EXTPROC_DLLS=ANY,PATH=/oracle/app/dbutil_dlls,EP_LIB_PATH=/oracle/app/dbutil_dlls,LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0.3/dbhome_1/lib:/oracle/app/dbutil_dlls")
          )
          (SID_DESC =
            (SID_NAME = CLRExtProc)
            (ORACLE_HOME = /oracle/app/oracle/product/11.2.0.3/dbhome_1)
            (PROGRAM = extproc)
            (ENVS = "EXTPROC_DLLS=ANY,PATH=/oracle/app/dbutil_dlls,EP_LIB_PATH=/oracle/app/dbutil_dlls,LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0.3/dbhome_1/lib:/oracle/app/dbutil_dlls")
          )
        )
      I know that its a duplicate to have both PLSExtProc and CLRExtProc defined, but this is how it is defined in all of our installations and I want to keep it like that.
      I tried fiddling with /oracle/11.2.0/grid/network/admin/listener.ora trying to make it look similar to the single instance version, but with no luck.
      I was also not sure which definitions I need to change - LISTENER/LISTENER_SCAN1 on listner.ora or LISTENER_SERVER-X on endpoints_listener.ora, or which oracle_home I need to supply - the clusterware home or the DB home...

      can someone please guide me how to make it work correctly?

      Thanks in advance,
      -Mor
        • 1. Re: Configuring extproc on clusterware installation (not RAC)
          Pyrocks
          Anyone?
          My clusterware is in a production system, and I cannot perform too many tests.
          I don't have a non-prod clusterware installation at the moment so I have nowhere to test - otherwise I guess I could have resolved it myself after some serious trial and error.
          My last resort is Oracle support - but i'd like to avoid that if possible.

          Thanks.
          • 2. Re: Configuring extproc on clusterware installation (not RAC)
            Sebastian Solbach -Dba Community-Oracle
            Hi,

            you are paying for Oracle support, so why do you want to avoid it?
            Furthermore having a cluster without a test environment (at least a virtualized one) is something like a suicide mission. You want to run a critical database (hence clustered), but cannot afford a test system. How do you test things like patches etc.?

            Now to your questions: If you setup manual failover, you should also define your own listener to fail over. You should not/are not making use of the default listeners provided by RAC infrastructure.
            For this you will have created an additional resources, which will start the database the VIP and the listener together on the running node, and which will move in total to the second node on failure.
            You should not have used "srvctl add listener" to add another listener, otherwise you setup is probably bound to fail.

            If you have added your own listener, listening on the VIP, this listener should be mentioned in the local listener parameter of the database.
            Add the extroc properties to exactly that listener (listener name) to which the local listener parameter of the DB is pointing to.

            If you would use RAC One Node, where all this is managed for you, then the solution of EXTPROC would be to integrate this into the listener.ora for each local listener (same thing, the listener the local_listener parameter is pointing to).

            Make sure you alter the listener.ora in GI home and tnsnames.ora in DB Home.
            If you are working with user role separation (other user installed GI, than DB home), then probably you should add a listener.ora in the db home and create a listener out of the DB Home, otherwise the exproc will be with the user of the GI.

            Regards
            Sebastian
            • 3. Re: Configuring extproc on clusterware installation (not RAC)
              Pyrocks
              Hi,

              Thanks for the reply.
              you are paying for Oracle support, so why do you want to avoid it?
              because sometimes Oracle support takes 3 weeks to understand and provide the solution, and i thought this is going to be faster.
              Maybe I should :|
              Furthermore having a cluster without a test environment (at least a virtualized one) is something like a suicide mission. You want to run a critical database (hence clustered), but cannot afford a test system. How do you test things like patches etc.?
              Nothing i don't already know, but have to provide solutions for the current situation... We will have a demo clusterware here in a 1-2 months, but I can't wait that long.
              Now to your questions: If you setup manual failover, you should also define your own listener to fail over. You should not/are not making use of the default listeners provided by RAC infrastructure.
              For this you will have created an additional resources, which will start the database the VIP and the listener together on the running node, and which will move in total to the second node on failure.
              Why? we are using the listeners that came with the installation and they are working fine.
              I have a private listener resource on each node and a SCAN listener running on the active node (this is the default). for a matter of fact, even if the SCAN listener was on the passive server - it still works since the virtual IP works. crsctl stat res -t returns:
              ora.LISTENER.lsnr
              ONLINE ONLINE node-a
              ONLINE ONLINE node-b
              and
              ora.LISTENER_SCAN1.lsnr
              1 ONLINE ONLINE node-a
              You should not have used "srvctl add listener" to add another listener, otherwise you setup is probably bound to fail.
              I have not added any manual listener yet.
              >
              If you have added your own listener, listening on the VIP, this listener should be mentioned in the local listener parameter of the database.
              Add the extroc properties to exactly that listener (listener name) to which the local listener parameter of the DB is pointing to.
              If you would use RAC One Node, where all this is managed for you, then the solution of EXTPROC would be to integrate this into the listener.ora for each local listener (same thing, the listener the local_listener parameter is pointing to).
              Make sure you alter the listener.ora in GI home and tnsnames.ora in DB Home.
              If you are working with user role separation (other user installed GI, than DB home), then probably you should add a listener.ora in the db home and create a listener out of the DB Home, otherwise the exproc will be with the user of the GI.
              Now I only have remote_listener configured (to the scan listener) in the DB, local_listener is empty.
              And yes we have role separation (grid home and oracle home are with different users).
              The only manual thing we added on top of the default is the resource for the instance - so that it will automatically failover to the other node (or allow us to relocate it manually) - that's it.

              So - if I understand correctly - according to what you are suggesting I need to do the following:
              Create a listener on the oracle home in each of the nodes
              configure the new listener.ora in the DB home like I would configure a single instance listener with extproc
              set local_listener to the new listener? but its the same instance and same storage hopping from one server to another (ASM) so i'm confused.

              2 more questions - just to be sure:
              1. Are you sure there is no way of using the predefined listeners to make it work? It seems like a hassle when I have the default listeners working.
              2. I read something about configuring $ORACLE_HOME/hs/admin/extrpoc.sql instead of modifying listener.ora. can't it be used instead, and if so - how exactly?

              I wasn't able to find examples that are not RAC.
              Thanks again.
              -Mor
              • 4. Re: Configuring extproc on clusterware installation (not RAC)
                Sebastian Solbach -Dba Community-Oracle
                Hi,

                for a setup of a single manual failover see here:
                http://www.oracle.com/technetwork/products/clusterware/overview/si-db-failover-11g-134623.pdf

                This describes the failover with VIP and LISTENER.

                However it is true that with 11.2 and the introduction of SCAN Listener this is not necessarily needed, because the SCAN Listener will forward the client connect to the corresponding local listener with the database.

                Furthermore having local_listener empty, does not mean it is not set. It is set to the local listener. However I have recently seen a problem, where the workaround was to set it explicitly, to register on the local listener.
                The problem with this in your szenario ist, that you cannot set it explicitly with a (DESCRIPTION...) entry (since this will be bound to one node), but will need a TNS alias to use for.

                In any case (no matter how you set it up), the exproc entry must be for the local listener the database registeres to.
                I personally still would advice using RAC One Node instead of manual failover, since this is presetup and supported by Oracle.

                Also the database should have been registered totally as a manual resource for your manual failover (it seems you did that since you have one resource created for the instance, not sure about the db resource however).

                To your questions:
                => It should also work with the predefined listeners. However make sure that the local_listener parameter after switching points to the correct listener.
                => Never heared about extproc.sql.

                Regards
                Sebastian
                • 5. Re: Configuring extproc on clusterware installation (not RAC)
                  Pyrocks
                  Hi,

                  Thanks for the help. I'm still not entirely sure, since I wasn't able to make it work using the predefined listener - and as stated in the original post - I have configured it on a single instance many times before.

                  I meant extproc.ora in the same location (hs/admin). it is listed in the documentation, I just wasn't sure whether it can help or not.

                  I think I will open an SR to be sure. thanks again for your time :)
                  • 6. Re: Configuring extproc on clusterware installation (not RAC)
                    Sebastian Solbach -Dba Community-Oracle
                    Hi,

                    extproc.ora is for the environment of the EXTProc programs.

                    Maybe a good place to put your environment, instead of listener.ora.
                    However as far as I know, this will not change the default exproc configuration that is needed that extproc can be called.
                    Additionally I am not sure, if you need this in the GI or DB home.

                    Regards
                    Sebastian