10 Replies Latest reply on Nov 21, 2016 5:13 AM by CloudDB

    Oracle Database 12.1.0.2 Service's Unexpected Behaviour

    Smit Patel

      Hi Experts,

      I observed strange behavior regarding oracle database services:

       

      My setup :

      Primary :NODDB : 2 node RAC 12.1.0.2

      Physical Standby :NODDR : 2 node RAC 12.1.0.2

       

      My service configuration is :

      $ srvctl config service -s nodpms -d noddb

      Service name: nodpms                                                        

      Server pool:                                                                

      Cardinality: 1                                                              

      Disconnect: false                                                           

      Service role: PRIMARY                                                       

      Management policy: AUTOMATIC                                                

      DTP transaction: false                                                      

      AQ HA notifications: true                                                   

      Global: false                                                               

      Commit Outcome: false                                                       

      Failover type: SELECT                                                       

      Failover method: BASIC                                                      

      TAF failover retries: 180                                                   

      TAF failover delay: 5                                                       

      Connection Load Balancing Goal: LONG                                        

      Runtime Load Balancing Goal: NONE                                           

      TAF policy specification: NONE                                              

      Edition:                                                                    

      Pluggable database name: nodpm                                              

      Maximum lag time: ANY                                                       

      SQL Translation Profile:                                                    

      Retention: 86400 seconds                                                    

      Replay Initiation Time: 300 seconds                                         

      Session State Consistency:                                                  

      GSM Flags: 0                                                                

      Service is enabled                                                          

      Preferred instances: noddb1                                                 

      Available instances: noddb2                                                 

       

      So My preferred node is noddb1 and Available node is noddb2. And the role for this service is PRIMARY.

       

      So as per my understanding this service should not be up, when datbase role is physical standby.

       

       

      Now My observations are:

      I Switchover to NODDR database. So NODDB will become Physical standby and nodpms service is going down automatically as expected.

       

      Now If I will reboot NODDB-2 (which is currently in physical standby role), then nodpms service becomes online on NODDB-1 server. This is not expected behaviour of database service. Because still database NODDB is physical standby and service role is PRIMARY.

       

      Please provide your inputs/solution on this behavior.

       

      Thanks and Regards,

      Smit Patel

        • 1. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
          Salman Qureshi

          Hi,

          On your standby RAC, what is status of physical standby database? Is it in mount state or OPEN? Has "service role been" changed to PHYSICAL STANDBY after the switchover? I am supposing that if your standby is OPEN, service would become online.

           

          Salman

          • 2. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
            Smit Patel

            Hi,

             

            Service role does not change after switchover. It remains PRIMARY only.

            And for second query, we are using active dataguard with DG Broker, so standby database will be opened in read only mode.

             

            Thanks,

            Smit Patel

            • 3. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
              Salman Qureshi

              Hi,

              Yes I know that, i actually meant whether you have changed it to PHYSICAL STANDBY and also OPEN mode to MOUNT? Try these both and then do a reboot, and see if you still see your services running (which i hope not)

               

              Salman

              • 4. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                Robin.A-Oracle

                Hi  Smit


                Can you share

                 

                srvctl config database -d noddb

                srvctl status database -d noddb

                srvctl config database -d noddr

                srvctl status  database -d noddr

                 

                Please see the below article for configuring role-based service and verify your setup

                 

                How To Configure Client Failover For Data Guard Connections Using Database Services (Doc ID 1429223.1)

                 

                Regards

                Robin

                • 5. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                  Smit Patel

                  Hi Robin,

                   

                   

                  Please find below outputs:

                   

                   

                  srvctl config database -d noddb

                  Database unique name: noddb

                  Database name: noddb

                  Oracle home: /u01/app/oracle/product/12.1.0/db_1

                  Oracle user: oranod

                  Spfile: +SSDDATA1/noddb/PARAMETERFILE/spfile.271.923402517

                  Password file: +SSDDATA1/noddb/PASSWORD/pwdnoddb.256.923402037

                  Domain:

                  Start options: open

                  Stop options: immediate

                  Database role: PRIMARY

                  Management policy: AUTOMATIC

                  Server pools:

                  Disk Groups: SSDDATA2,SSDDATA1,SASDATA1

                  Mount point paths:

                  Services: nodpms

                  Type: RAC

                  Start concurrency:

                  Stop concurrency:

                  OSDBA group: dba

                  OSOPER group: dba

                  Database instances: noddb1,noddb2

                  Configured nodes: nodprs1,nodprs2

                  Database is administrator managed

                  srvctl status database -d noddb

                  Instance noddb1 is running on node nodprs1

                  Instance noddb2 is running on node nodprs2

                   

                   

                   

                   

                  =======================================================================

                   

                   

                   

                   

                  srvctl config database -d noddr

                  Database unique name: noddr

                  Database name:

                  Oracle home: /u01/app/oracle/product/12.1.0/db_1

                  Oracle user: orapun

                  Spfile: +SSDDATA1/spfilenoddr.ora

                  Password file: +SSDDATA1/noddr/PASSWORD/pwdnoddr

                  Domain:

                  Start options: read only

                  Stop options: immediate

                  Database role: PHYSICAL_STANDBY

                  Management policy: AUTOMATIC

                  Server pools:

                  Disk Groups: SSDDATA1,SSDDATA2

                  Mount point paths:

                  Services: nodpms

                  Type: RAC

                  Start concurrency:

                  Stop concurrency:

                  OSDBA group: dba

                  OSOPER group: dba

                  Database instances: noddr1,noddr2

                  Configured nodes: noddrs1,noddrs2

                  Database is administrator managed

                  srvctl status  database -d noddr

                  Instance noddr1 is not running on node noddrs1

                  Instance noddr2 is running on node noddrs2

                   

                   

                  Please let me know if you require any further details.

                   

                   

                  Regards,

                  Smit Patel

                  • 6. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                    CKPT

                    Surprised if you have configured any triggers related to the service? can you please check and confirm?

                    Also in case are you using Global data services?

                     

                    Thanks.

                    • 7. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                      Smit Patel

                      Hi CKPT,

                       

                      We are not using any Global services and please find below trigger code.

                       

                      create or replace trigger manage_dgrole after startup on PLUGGABLE

                      database

                      declare

                      v_role varchar(50);

                      v_unqame varchar(50);

                      begin

                      select database_role,DB_UNIQUE_NAME into v_role,v_unqame

                      from v$database;

                      if ( v_role = 'PRIMARY') AND (v_unqame = 'noddb') then

                      dbms_service.start_service('nodpms',1);

                      elsif ( v_role != 'PRIMARY') AND (v_unqame = 'noddb') then

                      dbms_service.stop_service('nodpms');

                      elsif ( v_role != 'PRIMARY') AND (v_unqame = 'noddr') then

                      dbms_service.stop_service('nodpms');

                      elsif ( v_role = 'PRIMARY') AND (v_unqame = 'noddr') then

                      dbms_service.start_service('nodpms',2);

                      end if;

                      end;

                      /

                       

                      Please review above code and suggest correction if any.

                       

                      Thanks and Regards,

                      Smit Patel

                      • 8. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                        CKPT

                        Hello Smit,

                        after you shutdown of nodedb2 can you send

                        srvctl config service -s nodpms -d noddb

                        srvctl config service -s nodpms -d noddr

                        • 9. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                          SVM 1225

                          Hi,

                          Interesting. Did you check the behaviour after creating a trigger at CDB level?

                           

                          e.g.

                          create or replace trigger manage_dgrole after startup on database

                          declare

                          v_role varchar(50);

                          v_unqame varchar(50);

                          begin

                          select database_role,DB_UNIQUE_NAME into v_role,v_unqame

                          from v$database;

                          if ( v_role = 'PRIMARY') AND (v_unqame = 'noddb') then

                          dbms_service.start_service('nodpms',1);

                          elsif ( v_role != 'PRIMARY') AND (v_unqame = 'noddb') then

                          dbms_service.stop_service('nodpms');

                          elsif ( v_role != 'PRIMARY') AND (v_unqame = 'noddr') then

                          dbms_service.stop_service('nodpms');

                          elsif ( v_role = 'PRIMARY') AND (v_unqame = 'noddr') then

                          dbms_service.start_service('nodpms',2);

                          end if;

                          end;

                          /

                          • 10. Re: Oracle Database 12.1.0.2 Service's Unexpected Behaviour
                            CloudDB

                            Hi Smit,

                             

                            My service configuration is :

                            $ srvctl config service -s nodpms -d noddb

                            Service name: nodpms                                                      

                            Server pool:                                                              

                            Cardinality: 1                                                            

                            Disconnect: false                                                         

                            Service role: PRIMARY                                                     

                            Management policy: AUTOMATIC                                              

                            DTP transaction: false                                                    

                            AQ HA notifications: true                                                 

                            Global: false                                                             

                            Commit Outcome: false                                                     

                            Failover type: SELECT                                                     

                            Failover method: BASIC                                                    

                            TAF failover retries: 180                                                 

                            TAF failover delay: 5                                                     

                            Connection Load Balancing Goal: LONG                                      

                            Runtime Load Balancing Goal: NONE                                         

                            TAF policy specification: NONE                                            

                            Edition:                                                                  

                            Pluggable database name: nodpm                                            

                            Maximum lag time: ANY                                                     

                            SQL Translation Profile:                                                  

                            Retention: 86400 seconds                                                  

                            Replay Initiation Time: 300 seconds                                       

                            Session State Consistency:                                                

                            GSM Flags: 0                                                              

                            Service is enabled                                                        

                            Preferred instances: noddb1                                               

                            Available instances: noddb2 

                             

                            Behaviour you got is the correct behaviour as per this service configuration at dr side.