Oracle Database Discussions

The statement implies that the simplest way to rollback would be to restore a database backup taken just before the upgrade. This means that you would lose all transaction in the database since the upgrade.

If your goal is to not lose those transactions since the upgrade, then follow the steps in the MOS Doc

Srini - Hi. I definitely do not want to lose data -correct. But If I understand the downgrade capability itself, I should be able to downgrade without data loss. It does not say you can lose data. I would caution on that interpretation but that is not my current understanding. I should also ADD that I am also planning not to change the compatibility flag to 11.2.0.4 from 11.2.0.2 in order to enable a downgrade during the first week of upgrading in case of troubles with the new version and the application.

"If the goal is to have the instance back EXACTLY as it was pre-upgrade then other processes including a recovery to just before the upgrade"?  Central to my question is the word "EXACTLY" in the note. 

This will make the database exactly as it was prior to the upgrade, INCLUDING THE DATA! You won't get any new data generated after the upgrade. Restoring from the backup is the safest way of downgrading your database but at the expense of losing data.

If you want to downgrade but keep your data, then follow the steps in the Upgrade Guide:

http://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007

Cheers,
Brian

This raises a question :

How many sites practice database downgrades  as well as part of the upgrade testing ?

Hemant K Chitale

Adding for compatible parameter :

Use only the first 3 digits for the compatible parameter unless there would be some very specific instructions to do otherwise.

Patch sets are normally not intended to add actual feature, though 12c patchsets delivers new features, it is not necessary to increase the compatible parameter and set the 4th digit.ards,

Regards,

Himanshu

That is a very good question and I fear the answer, sadly, is....not enough.

Cheers,
Brian

I'm guilty too.

I've practiced database restores as part of upgrade testing ... but never a downgrade.

Hemant K Chitale

thanks for the tip. yes we will do this. agreed.

Srini - your reply was helpful. I had one nervous DBA that felt that perhaps there might be issues with a downgrade we might run into given the wording. Anyone heve troubles with a downgrade in your past experiences or travels? We did see the one note/warning about 11.2.0.2 catrelod.sql so we are aware of this issue. We are also aware that after downgrading we must apply any applied PSU and one-offs since the downgrade goes back to the base version of the version it was prior.

I have done my fair share of upgrades - and never had to downgrade even once.

It all comes down to testing your application - thorough testing of all of the critical parts will identify any issues and have them resolved before the production upgrade. There will always be issues that pop-up in production after the upgrade because it is impossible to test 100% of your application. Best to keep your Oracle account team informed of your upgrade timings,  and Support is typically good at resolving performance issues.

Here's a pool on the subject.

Cheers,
Brian

I have done my fair share of upgrades - and never had to downgrade even once.

I've never had to downgrade once either. I've been doing Oracle upgrades from v7.1 to every version since. That's lots and lots of upgrades over the years. I came close to pulling the downgrade-trigger on my last upgrade going from 11.2.0.4 to 12.1.0.2 in a critical production database. Issues cropped up that was not seen in testing (referencing your last paragraph). I ended up just working through the issues. But I came really close to saying to management that I needed to downgrade and revisit testing before upgrading again.

All that being said, this is no reason not to practice downgrades. I've never once had to perform a complete database restore in my career. I've had to restore a tablespace. I've had to restore a datafile. I've had to restore all files on a disk system that was lost. But I've never lost the entire database that I needed to restore from a backup. But it would be malpractice of me to refuse to practice it all because I've never needed to do it in my 20 years of being an Oracle DBA.

Painful fact of anyone in IT:   The moment we become complacent and willfully refuse to perform risk mitigation is the moment in time the wheels are set in motion and that risk becomes reality. It is that moment our careers hang in the balance. Will you be prepared to respond?

Cheers,
Brian

Well said - did not mean to imply that we should not "test" downgrades.

Although technically possible to perform a downgrade, it needs to be considered in the context of the business application and it's sensitivity to downtime, data loss etc

I put more of my thoughts on why it is important to be able to perform a downgrade in this blog post: Complacency leads to: Risk Becomes Reality » Peasland Database Blog

My apologies to the OP as I've completely hijacked this thread.

Cheers,
Brian

I've voted on your poll.  The current statistics show that 85% don't practice downgrades 

Hemant K Chitale