RE: Strange TAF behaviour

From: Jko - Google <jacques.kostic_at_gmail.com>
Date: Mon, 12 Dec 2016 14:45:21 +0100
Message-ID: <002401d2547d$fc1622c0$f4426840$_at_gmail.com>



According to Oracle Support, this is an expected behaviour!  

Since when? Just discovered that since July PSU

It is still the case with OCT PSU

And will be the case with 12.2  

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
Received on Mon Dec 12 2016 - 14:45:21 CET

Original text of this message