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@xxxxxxxxx [mailto:ilsuonogiallo@xxxxxxxxx] ;
Sent: 06 December 2016 01:08
To: jacques.kostic@xxxxxxxxx
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@xxxxxxxxx>
Inviato: lunedì 5 dicembre 2016 16:41
A: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
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@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@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@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@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@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
