Link to home
Create AccountLog in
Avatar of marrowyung
marrowyung

asked on

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 !
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of marrowyung
marrowyung

ASKER

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 ?
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.
"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 ?
Don't know.  I'm not sure what all is involved in setting it up.  I've only read about it.
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?
"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.
>>you know, our Oracle system can't even cross site ! j

I don't know what this means.
"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.
"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!
All of that appears to be covered by Data Guard?
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).
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.
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.
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&anchorAnswerId=42714731#a42714731
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!
"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.
If you're obviously not wanting to use Oracle, why oh why we're even talking about this at all?????
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.....
Honestly, it is very hard to understand/read what you post and even harder to get what you really want...
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.
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
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.
"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.
tks all.