Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Database 12.1.0.2 Service's Unexpected Behaviour

Smit PatelOct 26 2016 — edited Nov 21 2016

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

Comments

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

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

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

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

2 people found this helpful
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

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.

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

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

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;

/

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.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 19 2016
Added on Oct 26 2016
#data-guard, #performance-availability, #rac
10 comments
2,003 views