RE: Strange TAF behaviour

Apparently it should work.

This is the first time I see this error number :)  

I will get more information from a colleague soon. Anyway the session has failed_over = yes during the select…  

jko    

From: ilsuonogiallo_at_gmail.com [mailto:ilsuonogiallo_at_gmail.com] Sent: 06 December 2016 01:08
To: jacques.kostic_at_gmail.com
Subject: R: Strange TAF behaviour  

Hello

In my understanding, TAF can be used together with RAC to protect your session/select from instance failure and cannot be used to protect your session/select from database role change.  

Hence single instance should mean no TAF at all :(  

If you found that you can use TAF with database role change, could you please share the related documentation?  

Thanks

Andrea  

Inviato dal mio telefono Windows 10  

Da: Jko - Google <mailto:jacques.kostic_at_gmail.com> Inviato: lunedì 5 dicembre 2016 16:41
A: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> Oggetto: Strange TAF behaviour  

Hi Guys,  

I am just validating my TAF configuration and I have a strange behaviour while running select during the switch over.  

Configuration Single Instance on Grid Infrastructure 12.1.2.0 PSU JULY

ODA1 srvodap01,srvodap03

ODA2 srvodap02,srvodap04  

Database is 11.2.0.4

Standby created DG config done    

DGMGRL> show configuration  

Configuration - jiraprd  

  Protection Mode: MaxPerformance

  Databases:

    jiraprd - Primary database

    jirastb - Physical standby database  

Fast-Start Failover: DISABLED  

Configuration Status:

SUCCESS   On Primary

srvctl add database -d JIRAPRD -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -x srvodap01 -p /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJIRAPRD.ora -c single -r PRIMARY -s open

srvctl modify database -d JIRAPRD -y automatic -j "/u01/app/oracle/oradata/datastore,/u02/app/oracle/oradata/datastore,/u01/app/oracle/fast_recovery_area/datastore"    

oracle_at_srvodap01:~/ [JIRAPRD] srvctl config database -d JIRAPRD

Database unique name: JIRAPRD

Database name:

Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_1

Oracle user: oracle

Spfile: /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJIRAPRD.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: JIRAPRD

Database instance: JIRAPRD

Disk Groups:

Mount point paths: /u01/app/oracle/oradata/datastore,/u02/app/oracle/oradata/datastore,/u01/app/oracle/fast_recovery_area/datastore

Services: JIRAPRD_RW

Type: SINGLE

Database is administrator managed    

On Standby

srvctl add database -d JIRASTB -i JIRAPRD -n JIRAPRD -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -x srvodap02 -p /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJIRAPRD.ora -c single -r PHYSICAL_STANDBY -s mount

srvctl modify database -d JIRASTB -y automatic -j "/u01/app/oracle/oradata/datastore,/u02/app/oracle/oradata/datastore,/u01/app/oracle/fast_recovery_area/datastore" -s open    

oracle_at_srvodap02:~/ [JIRAPRD] srvctl config database -d JIRASTB

Database unique name: JIRASTB

Database name: JIRAPRD

Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_1

Oracle user: oracle

Spfile: /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJIRAPRD.ora

Domain:

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools: JIRASTB

Database instance: JIRAPRD

Disk Groups:

Mount point paths: /u01/app/oracle/oradata/datastore,/u02/app/oracle/oradata/datastore,/u01/app/oracle/fast_recovery_area/datastore

Services: JIRAPRD_RW

Type: SINGLE

Database is administrator managed    

Services  

On primary

srvctl remove service -d JIRAPRD -s JIRAPRD_RW

srvctl add service -d JIRAPRD -s JIRAPRD_RW -l PRIMARY -y AUTOMATIC -P basic -e select -m BASIC -z 200 -w 1  

oracle_at_srvodap01:~/ [JIRAPRD] srvctl config service -d JIRAPRD -s JIRAPRD_RW

Service name: JIRAPRD_RW

Service is enabled

Server pool: JIRAPRD

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: SELECT

Failover method: BASIC

TAF failover retries: 200

TAF failover delay: 1

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Edition:

Preferred instances: JIRAPRD

Available instances:      

On Standby

srvctl remove service -d JIRASTB -s JIRAPRD_RW

srvctl add service -d JIRASTB -s JIRAPRD_RW -l PRIMARY -y AUTOMATIC -P basic -e select -m BASIC -z 200 -w 1  

oracle_at_srvodap02:~/ [JIRAPRD] srvctl config service -d JIRASTB -s JIRAPRD_RW

Service name: JIRAPRD_RW

Service is enabled

Server pool: JIRAPRD

Cardinality: 1

Disconnect: false

Service role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: SELECT

Failover method: BASIC

TAF failover retries: 200

TAF failover delay: 1

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Edition:

Preferred instances: JIRAPRD

Available instances:      

Tnsnames.ora entry  

JIRAPRD = (DESCRIPTION_LIST=    (LOAD_BALANCE=off)(FAILOVER=on)

   (DESCRIPTION=      (ADDRESS_LIST=        (ADDRESS=(PROTOCOL=TCP)(HOST=srvodap01-vip.int.imd.ch)(PORT=1521))

       (ADDRESS=(PROTOCOL=TCP)(HOST=srvodap02-vip.int.imd.ch)(PORT=1521))

     )(CONNECT_DATA=(SERVICE_NAME=JIRAPRD_RW))    )

)    

lsnrctl status

...

Service "JIRAPRD_RW" has 1 instance(s).

  Instance "JIRAPRD", status READY, has 1 handler(s) for this service...

...
   

sqlplus dgtst/xxxx_at_JIRAPRD

select * from dba_objects;

...
 

In the same time  

dgmgrl

switchover to jirastb;

...
   

The select gives the following.  

select * from dba_objects

*

ERROR at line 1:

ORA-16456: switchover to standby in progress or completed    

after about three second, rerunning the command works.

SQL> /     Why is the select interrupted??      

In addition, setting --> alter system set "_query_on_physical"=false scope=spfile;  

to prevent standby being opened produce an error and where doing  

srvctl start database -d JIRASTB fail  

Why? Because of error produced by the disabled feature

Any workaround?    

Cheers

jko    

--
http://www.freelists.org/webpage/oracle-l