Transaction level failover in oracle 12c

hi,

From the launch of oracle 12c, there is a new feature called transaction level failover! everyone recall that.

this means if there is an active transaction STILL running during the write node die, during the write master failover to another node and after the same transaction will KEEP running  without any rerun of the transaction, which may cause duplicate of records as part of record already DMLed before it failover.

any information on this feature ? any URL can refer this feature of Oracle 12c really working ? or it means this: https://docs.oracle.com/database/121/JJDBC/ocitaf.htm#JJDBC28835?


I am not sure if Oracle 12c really can do it!  MariaDB can do it with its proxy !
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>or it means

TAF is one way but you might also want to read up on Application Continuity and Transaction Guard:
https://www.oracle.com/database/technologies/high-availability/app-continuity.html

I've never seen these products run in the real world so I have to believe they do what they say they can do.
Alex [***Alex140181***]Software DeveloperCommented:
You mean this?!
Application Continuity is a technology that will improve application availability. Before Oracle 12c, Transparent Application Failover (TAF) or Fast Connection Failover (FCF) coud help you to improve application availability without downtime, but developers usually have to implement a TAF or FCF aware code. And usually, it’s wasn’t the case for many reasons (testing and UAT platform which weren’t RAC platform, specifications weren’t written in this aim etc.).

In 12c, Oracle releases application continuity. This technology will do the same thing with more simplicity and has been developed to handle planned and unplanned outages.

Application Continuity works on Oracle 12c database and with application that use:

Oracle Weblogic Server
Oracle UCP (Unified Connection Pool)
Third party JAVA application server which used the PooledConnection Interface
JDBC Thin-driver
taken from https://laurent-leturgez.com/2015/06/01/oracle-12c-application-continuity-and-its-resources-usage/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
slightwv (䄆 Netminder),

"TAF is one way but you might also want to read up on Application Continuity and Transaction Guard:"

an extra component with RAC need cost ?

what I want is, when RAC oracle instance write mast failover to other instance as that write master offline for any reason, application, during the failover and connect to the new write master, application KEEP Writing data as if the failover wasn't happen at all!

is it what I need?

I only know that it is only since Oracle 12c has this kind of feature, am I right ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
I will defer you to the documentation and your Oracle Account Team for licensing:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

It appears that Application Continuity and Transaction Guard are part of Enterprise Edition and Application Continuity comes with the RAC license.

Again, only Oracle can confirm this.

>>I only know that it is only since Oracle 12c has this kind of feature, am I right ?

Those features are new with 12c.  Oracle has had TAF for several releases.  I'm not sure what other options were around before 12c.  Oracle can tell you.

All tat said:  I'm not sure any of these are fully automatic and without application code changes.  From what I've read, I believe the application needs to be fail-over aware.  Maybe not, I've only read about these features.  I've never implemented and tested them.
marrowyungSenior Technical architecture (Data)Author Commented:
"From what I've read, I believe the application needs to be fail-over aware."

if application need, why still need that component ? or just simply let application detect it and do the transaction again, right ?
slightwv (䄆 Netminder) Commented:
Don't know.  I'm not sure what all is involved in setting it up.  I've only read about it.
Mark GeerlingsDatabase AdministratorCommented:
And, if you are moving your application(s) to MariaDB (as your other recent questions indicate) why do these Oracle details matter?

Or, are actually trying to decide if moving to Maria DB is a good idea, and maybe staying with Oracle is a better idea?
marrowyungSenior Technical architecture (Data)Author Commented:
"And, if you are moving your application(s) to MariaDB (as your other recent questions indicate) why do these Oracle details matter?
"

hi, I checking if the same thing exists in MariaDB and MySQL InnodB cluster, I don't want to make a wrong conclusion.

I verified that mariaDB with Maxscale can handle this quite well. MySQL + MySQL router can't !

verifying with proxySQL owner and it seems also can't ! so why MariaDB with maxscale can? and MySQL + ProxySQL can't ?

for developer this feature is a very good feature ! they don' even have to take care about handshaking.

"Or, are actually trying to decide if moving to Maria DB is a good idea, and maybe staying with Oracle is a better idea"

if MariaDB can do it (e.g. horizontal scale out, oracle can't!) and it is free ! then why don't we save money to do sth else ! the point is, our system is not busy!

you know, our Oracle system can't even cross site ! just backup and restore to the DR site only.
slightwv (䄆 Netminder) Commented:
>>you know, our Oracle system can't even cross site ! j

I don't know what this means.
Mark GeerlingsDatabase AdministratorCommented:
"our Oracle system can't even cross site"

I also don't know what that means.

"MariaDB can do it (e.g. horizontal scale out, oracle can't!"

I also don't know exactly what you mean by that.  I thought Oracle RAC is a horizontal scale out solution.
marrowyungSenior Technical architecture (Data)Author Commented:
"our Oracle system can't even cross site"

I mean when primary site oralce down, our solution can't auto failover to DR site, we don't even have data guard .

" I thought Oracle RAC is a horizontal scale out solution."

horizontal scale out also means there are more than one copy of DB data, all DB nodes store a SEPARATE copy of database on its own storage.

and we can select some nodes to work together to form a round robin read only server farm. we can splite write and read between nodes.

in Oracle RAC, the phyical and logical storage is the same unit! while in horizontal scale out solution/design, the physical storage is not the SAME, copies of data in ALL DB nodes and DB nodes do not share physical storage!
slightwv (䄆 Netminder) Commented:
All of that appears to be covered by Data Guard?
marrowyungSenior Technical architecture (Data)Author Commented:
in horizontal scale out, all replication free of charge! no need data guard ! how can you solve the SPOF problem of share storage architecture using by RAC ?

so if we have 152 nodes, we need to buy a lot of other SW, right ?

horzontal scale out can across site and failover in MariaDB and MS SQL (but not MySQL InnoDB cluster).
Mark GeerlingsDatabase AdministratorCommented:
If this is the kind of system you want: "all DB nodes store a SEPARATE copy of database on its own storage." then you require a robust data-replication mechanism of some kind to keep all of these separate databases in sync with each other.  To me, that sounds a lot more complex than what Oracle offers.

That is not the way Oracle works.  Is that better (or worse) than the way Oracle works with Data Guard to offer off-site replication?  We cannot decide this for your organization.
slightwv (䄆 Netminder) Commented:
I understand your mission is to prove Oracle is evil.

I thought you were debating features.  Oracle can do it.  Cost is a different debate.

I've also not see that horizontal scale is removing SPOF but OK, if that is how you define it.
marrowyungSenior Technical architecture (Data)Author Commented:
slightwv (䄆 Netminder),

"I understand your mission is to prove Oracle is evil."

no ! you know , here a lot of medium to large company all stay away from oracle and DB2, as they are expensive!

and in oracle, the RAC , still using shared expensive SAN storage, it can't show it is not SPOF!

so we pay so much, still have SPOF ! why ?

horizontal scale out mean only relies on local storage and usign build in sync/async replication replicate data to more than one storage to keep a copy ! other office can read their local database as it is replicated from the primary.

"I've also not see that horizontal scale is removing SPOF but OK, "
the storage is not SPOF anymore by horizontal scale out.

but same problem also exists, in multi master WRITE mode conflict still exists!  in MariaDB and MySQL, they forced to rollback and reapply again!

with MariaDB with maxscale, can also do transaction level failiover.

and the DB is free !

Mark Geerlings,

"That is not the way Oracle works.  Is that better (or worse) than the way Oracle works with Data Guard to offer off-site replication?  "

I know it is not how it works, that RAC designed long time ago and phyical and logical storage MUST BE  the same for RAC to work.

in horizonal scale out, no need, that barrier out!

guy, please help on https://www.experts-exchange.com/questions/29121052/preparation-work-for-PoC-procedure-from-oracle-to-MariaDB.html?anchor=a42714731¬ificationFollowed=216327032#a42714731
Alex [***Alex140181***]Software DeveloperCommented:
I thought you were debating features.  Oracle can do it.  Cost is a different debate.
YES YES YES and Mark is not 100% right saying
That is not the way Oracle works.
!!
In the end, it's "just" a question of how much money are you willing to spend on Oracle to get what you want and NEED!!
Sure, it can be very much to pay, but in fact, technically speaking, Oracle can handle DR/HA and such stuff out-of-the-box with all its features very well!

with MariaDB with maxscale, can also do transaction level failiover.

and the DB is free !
Long story short: why do we need to talk about the very same stuff over and over if $$$ is a showstopper???!!!
IMHO just go with the free stuff. On the one hand you're saying MariaDB can do that for free and other stuff can do other things for free but our ORacle can not bla bla. And on the other hand, you're struggling with getting some kind of custom comparison???!!!
Alltogether, that doesn't make any sense for me at all!!
DR, HA and all the other nice stuff/features will not be free! Deal with it!
marrowyungSenior Technical architecture (Data)Author Commented:
"Sure, it can be very much to pay, but in fact, technically speaking, Oracle can handle DR/HA and such stuff out-of-the-box with all its features very well!"

yeah ! I believe so ! here Oracle and mainframe dynamically rise cost, all company step aways! I really can't see why except Oracle know horizontal scale out is the future and the RAC will die very soon ! and if they don't rise cost now they will have no chance later !! horizontal scale out is highly adopting! everyone use this kind of very low cost HA + DR tools ! no need expensive SAN any more! each SAN expension is very expensive ! fiber card, switch, SAN all expensive !

mainframe cost nearly tripled! only banking can't leave DB2 will keep paying it.

here a lot of financial firm ALSO stay aways from Oracle too as everyone cut cost and every one try to use big data in a low cost way  (Amazing!!), so they usually have 2 x choice, MSSQL , MySQL and MariaDB. MySQL can't natively cross site so this will not be the choice of a lot of company except the fact that MariaDB usually do not have LOCAL Support, they are limited on this but they are changing !

in my city, MariaDB start to have more and more LOCAL support !

"DR, HA and all the other nice stuff/features will not be free! Deal with it!"

MariaDB is! except the support ! only one version of binary! doesn't looks like MysQL enterprise and community edition, they are diff !

that's why even development team in a lot of company like that! just download and use it.

but one thing about that is , open source thing do not have report feature and ETL. this is the problem right now ! don't even talk about report and ETL scale out yet.
Alex [***Alex140181***]Software DeveloperCommented:
If you're obviously not wanting to use Oracle, why oh why we're even talking about this at all?????
marrowyungSenior Technical architecture (Data)Author Commented:
as I am giving example on the concept and I JUST to quote the same thing from oracle as from memory, oracle 12c has it. so people know what I am referring to.

I ask here as I want to check if the same thing available from MySQL InnodB cluster and it is not a NO!

and I ask the proxySQL (the best proxy for MySQL) if it can be done! it also seems a lot but MariaDB's maxscale !

from time to time searching from ANY brands web site is not fair !  they ALL say ANYONE else is bad except them! ProxySQL said to be better than Maxscale, but when use it ..... you know what I will say.....
Alex [***Alex140181***]Software DeveloperCommented:
Honestly, it is very hard to understand/read what you post and even harder to get what you really want...
marrowyungSenior Technical architecture (Data)Author Commented:
very little people has this feeling! but you already answered my question.

I am trying to migrate from oralce to mariaDB, this is what I want and I am in between the process and get all information for PoC.
slightwv (䄆 Netminder) Commented:
See this?

https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html

The problem was in large part due to Amazon's migration from Oracle's database to its own technology, the documents show. The outage underscores the challenge Amazon faces as it looks to move completely off Oracle's database by 2020, and how difficult it is to re-create that level of reliability. It also shows that Oracle's database is more efficient in some aspects than Amazon's rival software
johnsoneSenior Oracle DBACommented:
Just to add another anecdote.....

I used to work for a company that had 3 database technologies.  The developers loved the one that they just installed and used.  The other two were Oracle and Ingres (this was a long time ago and Ingres could do things that Oracle couldn't at the time).  They got rid of the DBAs because they had a database they could handle themselves.  I found out a year later that they had to re-hire "DBAs" (junior people they "trained"), it cost them a ton of money and consulting to get back to a normal running system, and the great database the developers were using caused a minimum of a day of downtime every time there was a server crash (not often but happened) or someone didn't shut this thing down in just the right way.  Then there were multiple days of clean up afterward.  Not really good for a company that relies on the database for its ordering system.  Try telling your customers (with orders in the 10s of thousands of dollars) that their order is going to be delayed a week because you are using a technology that isn't the best, but the developers like it.

Developers job is to write code and move on.  They don't want to maintain code, they don't want to fix code, they don't care about the database.  Seen it all too often.  I have had to stop projects in their tracks and take them to very high up in the organization because the developers were doing what was easy.  The application may have worked in the end, but it wouldn't be supportable and wouldn't scale to more than 2 users (not good when you have 400 concurrent users on a slow day).

The new philosophy of business is becoming "pace over perfection".  Get it done fast, we don't care if its right or not.

In the end, you get what you pay for.
marrowyungSenior Technical architecture (Data)Author Commented:
"The other two were Oracle and Ingres (this was a long time ago and Ingres could do things that Oracle couldn't at the time).  They got rid of the DBAs because they had a database they could handle themselves.  "

I started to hate cloud DB. hahhaha

"Developers job is to write code and move on.  They don't want to maintain code, they don't want to fix code, they don't care about the database. "

yeah !

"The new philosophy of business is becoming "pace over perfection".  Get it done fast, we don't care if its right or not."

that's why I prefer a DB has free very good debugging tools ! -> MS SQL

All company need to go to maintenance/operation/monitoring cycle, this is why we hired!

during project phrase data is small set and everything seems running good ! when data growing, thing get slow and this is why we hired !

agree ? only small company do not improve here.
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.