2 Replies Latest reply: Dec 31, 2012 10:57 PM by 857396 RSS

    how to use the default database service name on creating procedure for data

    857396
      how to use the default database service name on creating procedure for datagaurd client failover ??? all oracle doc says create a new service as below and enable at DB startup. but our client is using/wanted database default service to connect from application on the datagaurd environment (rac to non rac setup).please help.


      Db name is = prod.

      exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'prod',network_name =>'prod',failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);

      says already the service available.


      CREATE OR REPLACE TRIGGER manage_dgservice after startup on database DECLARE role
      VARCHAR(30);BEGIN SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
      IF role = 'NO' THEN DBMS_SERVICE.START_SERVICE('prod');
      END IF;
      END;


      says trigger created, but during a swithover still the service is listeneing on listener.

      tns entry.

      prod =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (LOAD_BALANCE = YES)
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod2)(PORT = 1521)) ---> primary db entry
      )
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = proddr)(PORT = 1521)) --> DR DB entry
      )
      (CONNECT_DATA =
      (SERVICE_NAME = prod)
      )
      )


      thanks in advance.

      Edited by: 854393 on Dec 29, 2012 11:52 AM
        • 1. Re: how to use the default database service name on creating procedure for data
          mseberg
          Hello;

          So in the example below replace "ernie" with the alias you want the client to use.

          I can show you how I do it :

          First an entry need to be added to the client tnsnames.ora that uses a SERVICE_NAME instead of a SID.

          ernie =
           (DESCRIPTION =
              (ADDRESS_LIST =
                 (ADDRESS = (PROTOCOL = TCP)(HOST = Primary.host)(PORT = 1521))
                 (ADDRESS = (PROTOCOL = TCP)(HOST = Standby.host)(PORT = 1521))
                 )
                 (CONNECT_DATA =
                 (SERVICE_NAME = ernie)
              )
           )
          Next the service 'ernie' needs to be created manually on the primary database.
           
          
          BEGIN
           
             DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
          
          END;
          /
          After creating the service needs to be manually started.
           BEGIN
           
             DBMS_SERVICE.START_SERVICE('ernie');
           
           END;
           /
          Several of the default parameters can now be set for 'ernie'.
          BEGIN
             DBMS_SERVICE.MODIFY_SERVICE
             ('ernie',
             FAILOVER_METHOD => 'BASIC',
             FAILOVER_TYPE => 'SELECT',
             FAILOVER_RETRIES => 200,
             FAILOVER_DELAY => 1);
          END;
          /
          Finally a database STARTUP trigger should be created to ensures that this service is only offered if the database is primary.



          CREATE TRIGGER CHECK_ERNIE_START AFTER STARTUP ON DATABASE
          DECLARE
           V_ROLE VARCHAR(30);
          BEGIN
           SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE;
           IF V_ROLE = 'PRIMARY' THEN
           DBMS_SERVICE.START_SERVICE('ernie');
           ELSE
           DBMS_SERVICE.STOP_SERVICE('ernie');
           END IF;
          END;
          /
          lsnrctl status - should show the new service.


          When I do this the Database will still register with the listener. I don't give that to the clients. That one will still be available but nobody knows about it. Meanwhile "ernie" moves with the database role.

          So in my example the default just hangs out in the background.

          Best Regards

          mseberg

          Edited by: mseberg on Dec 29, 2012 3:51 PM