OraFAQ Forum: Server Administration » Schema Undo

Home » RDBMS Server » Server Administration » Schema Undo (12.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Schema Undo [message #678528]

Wed, 11 December 2019 06:14

deepakdot
Messages: 93
Registered: July 2015

Member

Hello,

we have a requirement, In Production we upgrade a schema with DDL and DML, if anything goes wrong, we should undo all the changes. DDL changes will be few which we can prepare a undo scripts. But we deliver a lots of DML statements. For DML Undo, we have this two options now.

option-1 : Prepare UNDO scripts for all the Upgrade DML we deliver (There might be really huge number of scripts)
option-2 : we will just prepare a backup tables for those tables we deliver the data. in case undo is required, we can just copy back from backup tables to the actual table.

we are going to adopt option-2 as this will be simple and less effort. Is this option-2 is acceptable in Production ? is there a best practice how we perform Undo in productions? Any other better options ?

Deepak

Report message to a moderator

Re: Schema Undo [message #678529 is a reply to message #678528]

Wed, 11 December 2019 06:25

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

If you have Enterprise Edition licences, Database Flashback might be the easiest, quickest, and most reliable method to take the whole database back to before the upgrade. Or if it is just one schema you could use RMAN to restore and recover the tables from a backup, also straightforward and reliable. In SE2, your options are more limited.

Report message to a moderator

Re: Schema Undo [message #678532 is a reply to message #678528]

Wed, 11 December 2019 08:30

JPBoileau
Messages: 88
Registered: September 2017

Member

I do "undo" (we call it "unapply") at my current job.

Every script that gets run during a push will have a matching unapply. It has worked very well for us.

Flashback is not good for us because some of our systems are online during the push and data is getting updated.

JP

Report message to a moderator

Re: Schema Undo [message #678552 is a reply to message #678532]

Thu, 12 December 2019 22:55

deepakdot
Messages: 93
Registered: July 2015

Member

Flashback is not an option for us, as DB will be online and data are getting updated. restore to the flashback point will loose all the data.

Here is what we proposed now.
1. DDL: we will prepare the UNDO scripts.
2. DML: there are so many table which does not affect during online. Data inserted only from backend. for those table create a backup table. Other transnational table , need to prepare a UNDO scripts.

Deepak

Report message to a moderator

Re: Schema Undo [message #678554 is a reply to message #678552]

Fri, 13 December 2019 01:02

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Editions are the solution as I explained in your previous topic.

Report message to a moderator

Re: Schema Undo [message #678555 is a reply to message #678552]

Fri, 13 December 2019 01:12

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

So why not restore the tables with RMAN? Much easier and more reliable than writing a shed load of scripts. Or are you not really open to any suggestions at all?

Report message to a moderator

Re: Schema Undo [message #678556 is a reply to message #678555]

Fri, 13 December 2019 01:19

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I understand, there are DML from the upgrade scripts and DML from online/normal users.
Are those DML on the same tables or not?
Are the upgrade DML for new data or to upgrade the old data into new ones?

Report message to a moderator

Re: Schema Undo [message #678562 is a reply to message #678555]

Sat, 14 December 2019 12:41

deepakdot
Messages: 93
Registered: July 2015

Member

John,

I am always open for suggestions. This forum help me a lot. I am learning and taking the suggestions from here .

RMAN : we can not take complete backup of the DB or schema as the system will be online. many transactions would have taken place during the Schema upgrade. So we can not restore from the backup.

can we take Table level backup from RMAN? Can i say RAMAN to take only 30 specific table backup ?
there will be few table changes which are transnational table. we need to prepare undo scripts manually , as those tables can not be restored from a backup.

Deepak

Report message to a moderator

Re: Schema Undo [message #678563 is a reply to message #678556]

Sat, 14 December 2019 12:45

deepakdot
Messages: 93
Registered: July 2015

Member

Michel,

Are those DML on the same tables or not?
we are delivering data sometime more than 100 tables. Its a lots of DML goes into the DB when we upgrade the schema.

Are the upgrade DML for new data or to upgrade the old data into new ones?
Maximum will be new data. we will also have upgrade the Old data to new data. So lots of INSERT/ UPDATE / DELETE happens here.

Deepak

Report message to a moderator

Re: Schema Undo [message #678565 is a reply to message #678562]

Sun, 15 December 2019 02:40

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

RMAN : we can not take complete backup of the DB or schema as the system will be online. many transactions would have taken place during the Schema upgrade. So we can not restore from the backup.

Of course you can take a backup while the system is in use. No-one shuts down a datbase for backups.

Quote:

can we take Table level backup from RMAN? Can i say RAMAN to take only 30 specific table backup ?

Certainly not. You backup files, not tables.

Quote:

there will be few table changes which are transnational table. we need to prepare undo scripts manually , as those tables can not be restored from a backup.

If you look up how to use RMAN, you will see how simple it is extract tables from a backup. Any competent DBA will be able to restore a set of tables to whatever point in time (or system change number) you want. It is just one command.

However, you seem determined to use your scripts. I wonder how you are going to ensure transactional integrity. Pretty difficult across many tables.

Report message to a moderator

Re: Schema Undo [message #678566 is a reply to message #678565]

Sun, 15 December 2019 09:22

deepakdot
Messages: 93
Registered: July 2015

Member

My Mistake .. Where I say "can not" , actually it is "should not" . I think I did not put it clear . Here it is:

Lets say, I am adding lot of DML to 50 Tables. There are 30 tables where data is modified thru scripts only directly in back end. So we can restore these 30 Tables , from RMAN Backup, as you mentioned, tables can be restored from the backup files. we will be good here.

Other 20 Tables is a concern. While we modify the data for these 20 tables, same time there is very high chance that these tables also would have modified online thru other channels. So in this scenario if we need to revert back the changes, we SHOULD NOT restore these tables from Backup, because we will also the data which is updated thru online also. we should revert the data what we have updated manually in back end, not the Transnational data which is modified online. So we are thinking to provide UNDO scripts which will revert back the data what we have modified manually.

Hope I made it clear.

Report message to a moderator

Re: Schema Undo [message #678573 is a reply to message #678566]

Mon, 16 December 2019 08:23

JPBoileau
Messages: 88
Registered: September 2017

Member

That is exactly what we do. I have apply and undo scripts. During the apply, if we modify or delete current data, we create a backup table that contains the rows that will be modified/deleted.

If the apply needs to be undone, we delete any added rows, we revert any updated rows, and we insert any deleted rows.

Yes, it's a fair bit of work, but you're in control of the DB.

JP

[Updated on: Mon, 16 December 2019 08:26]

Report message to a moderator

Re: Schema Undo [message #678579 is a reply to message #678573]

Mon, 16 December 2019 14:40

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And what if another application update an updated or inserted row?

Report message to a moderator

Re: Schema Undo [message #678592 is a reply to message #678579]

Tue, 17 December 2019 06:52

deepakdot
Messages: 93
Registered: July 2015

Member

>> And what if another application update an updated or inserted row?

we have complete control here. The data we deliver directly in the backend, those data will never modified by the online application / channel.

Report message to a moderator

Re: Schema Undo [message #678593 is a reply to message #678592]

Tue, 17 December 2019 06:55

deepakdot
Messages: 93
Registered: July 2015

Member

Thank you all. I have a plan now to project on this.

Report message to a moderator

Re: Schema Undo [message #678595 is a reply to message #678579]

Tue, 17 December 2019 07:40

JPBoileau
Messages: 88
Registered: September 2017

Member

That would then be data mitigation. You have to decide whether the new data is kept or the old data is kept.

JP

Report message to a moderator

Previous Topic:	Oracle TimesTen In-Memory Database
Next Topic:	DB Creation process stuck to 85%

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 20:43:35 CDT 2025