Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: November 22, 2019 - 2:12 pm UTC

Answered by: Connor McDonald - Last updated: December 18, 2019 - 6:28 am UTC

Category: Database Administration - Version: 18.4

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Oracle Database 12c Release 2 is here !

You Asked

Hello, Ask Tom Team.

I have some many questions about Oracle RAC HA and Failover.

I was reading the info in below link and it help me a lot. But I still have some questions.
https://asktom.oracle.com/pls/apex/asktom.search?tag=failover-in-rac

I have Oracle RAC 18c (two nodes). All connection strings the app uses are the following:

--database service with DTP enabled to handle distributed transactions:
dbprod1_xa=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = scanprod.local)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbprod1_xa)
)
)

grid@dbprodserver1:~$ srvctl config service -db dbprod -service dbprod1_xa
Service name: dbprod1_xa
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: true
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: SESSION
Failover method: BASIC
Failover retries: 2
Failover delay: 10
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: dbprod1
Available instances: dbprod2
CSS critical: no
Service uses Java: false

--general connection string for the rest of connections that do not use distributed transactions.
dbprod =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = scanprod.local)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbprod) --> default database service (I know I have to change this)
)
)

1. What is an OCI client?
2. How Do I know if I have OCI or ODP.NET client?
3. I have DTP services (see tns entry above), which run on a preferred instance. Can these services be TAF enable despite they run on a preferred instance?
4. For the rest of the transactions (not distributed ones), is best practice that they point to a database service runnning on both instance (a created database service different from default)?
5. Can TAF, FAN and FCF be configured at the same time? What are the main differences between them?
6. What specific configuration Do I have to do to properly configure TAF? And How can I test it?
7. What specific configuration Do I have to do to properly configure FAN? And How can I test it?
8. What specific configuration Do I have to do to properly configure Application Continuity? And How can I test it?
9. Is enough to use simple tnsnames entries like the one above (I mean without additional parameters like LOAD_BALANCE, FAILOVER_MODE, RETRY_COUNT, RETRY_DELAY...)?
10. If my app is using a service running just on one instance, How to relocate this service to another instance without user interruption?
11. If my app is using a service running on both instances, How to shutdown one instance without user interruption?

Sorry for some many questions. I have read lots of Oracle documentation. I just want to make sure the best practice to configure my production environment.

Thanks in advanced.

Regards,

and we said...

I think that counts as .... 11 questions. (Seeing as you even numbered them!) :-)

1. What is an OCI client?

Oracle Call Interface (OCI) is an application programming interface (API) to talk to the Oracle database, either locally or across the network. It is one of a set of clients that can be used (such as Java etc).
The OCI client is the most "direct" interface, often other layers simply sit on top of the OCI layer. Because it is the most direct it typically offer the most features for clients (failover etc)

2. How Do I know if I have OCI or ODP.NET client?

The ODP.NET client is an OCI client, ie, sits above OCI.

3. I have DTP services (see tns entry above), which run on a preferred instance. Can these services be TAF enable despite they run on a preferred instance?

Yes. You create a "preferred" and an "available" instance. Connections will fail over to the "available" instance if the preferred crashes or is unavailable.

4. For the rest of the transactions (not distributed ones), is best practice that they point to a database service runnning on both instance (a created database service different from default)?

Your choice really. Directing application traffic to single instances *can* reduce cross-node chatter (which can be a scalability inhibitor), but that then also means you having to do more configuration to ensure you are not wasting resources on a node.

5. Can TAF, FAN and FCF be configured at the same time? What are the main differences between them?

6. What specific configuration Do I have to do to properly configure TAF? And How can I test it?

See MOS note 460982.1

7. What specific configuration Do I have to do to properly configure FAN? And How can I test it?

With the OCI client, additional configuration should not be required. Some good reading here https://www.oracle.com/technetwork/database/options/clustering/applicationcontinuity/learnmore/fastapplicationnotification12c-2538999.pdf

8. What specific configuration Do I have to do to properly configure Application Continuity? And How can I test it?

Application Continuity has specific requirements for the client and architecture being used. Details here https://www.oracle.com/technetwork/database/options/clustering/ac-with-oracle-database-5303807.pdf

9. Is enough to use simple tnsnames entries like the one above (I mean without additional parameters like LOAD_BALANCE, FAILOVER_MODE, RETRY_COUNT, RETRY_DELAY...)?

Yes, as long as the configuration has been defined in the service itself.

10. If my app is using a service running just on one instance, How to relocate this service to another instance without user interruption?

Make a second service a preferred instance as well, and make the current one non-preferred. Existing connections will stay, new connections will be created on the newly preferred instance. Then you just wait :-) You can of course *forcibly* move people over, but applications trypically need to be able to handle the errors they might receive.

11. If my app is using a service running on both instances, How to shutdown one instance without user interruption?

srvctl relocate should take care of this for you

and you rated our response

  (3 ratings)

Reviews

Followup

November 27, 2019 - 11:20 am UTC

Reviewer: A reader

Excellent. Thanks for the awesome response.

So,

How do I *relocate* connections to another node if app is using a service running on both instances?

Thanks.

Follow Up

November 27, 2019 - 12:47 pm UTC

Reviewer: Geraldo Peralta

1. I ran a query in sqlplus (select * from table where rownum<30000).

2. I tried to relocate my service with the command:

srvctl relocate service -database orange -service myservice -oldinst instance1 -drain_timeout 60 -stopoption IMMEDIATE -force

I read in the docs that the best way to relocate a service is using drain option because it waits for the transactions to complete and does not allow new connections in failed instance.

I expected that the query would keep running while relocating but instead I got the error: ORA-30006: resource busy; acquire with WAIT timeout expired

1. Is this expected?
2. How Can I avoid this error so the query can run without interruption?
Connor McDonald

Followup  

December 13, 2019 - 3:09 am UTC

drain timeout moves those connections that can be moved, others will be forcibly terminated after this timeout.

If a service is marked as DTP, then its possible to get an ORA-30006

Follow Up

December 16, 2019 - 6:43 pm UTC

Reviewer: Geraldo Peralta

Hmm. I got it.

So, is it not possible to move sessions without user interruption if I am using DTP services?
Connor McDonald

Followup  

December 18, 2019 - 6:28 am UTC

Long running sessions yes.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database