| Schema Undo [message #678528] |
Wed, 11 December 2019 06:14  |
 |
deepakdot
Messages: 91
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
|
|
|
|
| Re: Schema Undo [message #678529 is a reply to message #678528] |
Wed, 11 December 2019 06:25  |
John Watson
Messages: 8960
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.
|
|
|
|
|
|
|
|
|
|
| Re: Schema Undo [message #678555 is a reply to message #678552] |
Fri, 13 December 2019 01:12 |
John Watson
Messages: 8960
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?
|
|
|
|
|
|
|
|
|
|
| Re: Schema Undo [message #678565 is a reply to message #678562] |
Sun, 15 December 2019 02:40 |
John Watson
Messages: 8960
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.
|
|
|
|
| Re: Schema Undo [message #678566 is a reply to message #678565] |
Sun, 15 December 2019 09:22 |
|
deepakdot
Messages: 91
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.
|
|
|
|
| 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 |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
