Home » RDBMS Server » Server Administration » Database upgrades - with No downtime (12c)
Database upgrades - with No downtime [message #678163] Thu, 14 November 2019 00:05 Go to next message
deepakdot
Messages: 36
Registered: July 2015
Member
Hi,

for the banking application database, What is the Best way to Upgrade the database with the new DDL/ DML changes going in, with the No downtime ?

we have a online database and a DR database. The requirement is not to have any downtime while upgrading the database. Any document / process followed as a best practice ?

Deepak
Re: Database upgrades - with No downtime [message #678164 is a reply to message #678163] Thu, 14 November 2019 02:47 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have a look at editions
Re: Database upgrades - with No downtime [message #678168 is a reply to message #678164] Thu, 14 November 2019 05:06 Go to previous messageGo to next message
deepakdot
Messages: 36
Registered: July 2015
Member
I think Table is noneditionable. we dont have editionable objects like views , triggers etc in the database.
we upgrade only tables.
Re: Database upgrades - with No downtime [message #678170 is a reply to message #678168] Thu, 14 November 2019 06:23 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
See if this is any help: https://blogs.oracle.com/oraclemagazine/edition-based-redefinition-part-2
Re: Database upgrades - with No downtime [message #678172 is a reply to message #678163] Thu, 14 November 2019 06:49 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
deepakdot wrote on Thu, 14 November 2019 00:05
Hi,

for the banking application database, What is the Best way to Upgrade the database with the new DDL/ DML changes going in, with the No downtime ?

we have a online database and a DR database. The requirement is not to have any downtime while upgrading the database. Any document / process followed as a best practice ?

Deepak
you said "upgrade the database", but it sounds like what you mean is to "upgrade" the application and its schemas. Please clarify. "Upgrade the database" normally means to apply a version upgrade or Release Update provided by Oracle - an update to the database software itself.
Re: Database upgrades - with No downtime [message #678214 is a reply to message #678172] Sun, 17 November 2019 23:14 Go to previous messageGo to next message
deepakdot
Messages: 36
Registered: July 2015
Member
Here for upgrade I mean, the application and the schema. Where I will be upgrading the schema object ( New DDL, Alter DDL, DMLs etc ).
Re: Database upgrades - with No downtime [message #678215 is a reply to message #678214] Mon, 18 November 2019 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Editions are then the correct solution but you have to first build editioning views upon your tables which means the first time you can't avoid downtime.

Re: Database upgrades - with No downtime [message #678531 is a reply to message #678215] Wed, 11 December 2019 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What option did you then choose to achieve your goal?

Re: Database upgrades - with No downtime [message #678551 is a reply to message #678531] Thu, 12 December 2019 22:44 Go to previous messageGo to next message
deepakdot
Messages: 36
Registered: July 2015
Member
we are still in discussion with the production team. So for every release before production need to simulate this with no downtime and confirm. This will be case by case, what are the changes going into production. we are refining the architecture to ensure 99% no downtime Smile . As it stands now, there might be a downtime but very minimal.

1. Install the new code with a new POD
2. Upgrade the schema
a. DDL: New table dont need a down time. If no altering table, dont need downtime. If there is table alter, then will set DDL_LOCK_TIMEOUT and try to proceed. If still have issue, then we may need to stop the app for the time we alter the tables. Still under discussion.
b. DML : Mostly its a new insert. So we dont need a down time for the DB. But with old codebase but new schema upgrade might be at risk, till some test is conducted and roll out the new codebase.


Deepak
Re: Database upgrades - with No downtime [message #678553 is a reply to message #678551] Fri, 13 December 2019 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Well, as I said, the best way is:
0/ Only the first time, small downtime, rename your tables and revoke privileges on these ones, build editioning views upon your tables with the former names of these ones and grant the privileges on the views (possibly recompile PL/SQL stored objects).
1/ Create a new edition
2/ Make your application upgrade while user are working
3/ Switch your user to the new edition (could be made in 2 times, a) only some pilots and b) when you are sure of the new application code, all users)
4/ Possibly but not mandatory, drop the old edition

Steps 1 to 3 will be the only steps for all your future upgrades with no downtime.

Re: Database upgrades - with No downtime [message #678557 is a reply to message #678551] Fri, 13 December 2019 02:07 Go to previous messageGo to next message
John Watson
Messages: 8086
Registered: January 2010
Location: Global Village
Senior Member
Quote:
a. DDL: New table dont need a down time. If no altering table, dont need downtime. If there is table alter, then will set DDL_LOCK_TIMEOUT and try to proceed. If still have issue, then we may need to stop the app for the time we alter the tables. Still under discussion.
I am not sure that DDL_LOCK_TIMEOUT is intended to be used in this way.How about quiesceing the database? THat is designed top give you a period of quiet to do things like this.
https://www.orafaq.com/node/2943
Re: Database upgrades - with No downtime [message #678560 is a reply to message #678553] Sat, 14 December 2019 12:21 Go to previous messageGo to next message
deepakdot
Messages: 36
Registered: July 2015
Member
Michel,

A short backgroup: This product same time can be deployed in Oracle, DB2, Postgres, SQL Server etc. Its to the client which DBMS they choose. So we can not use the Edition view here as this need a architecture changes. we have some limitations. Instead of modifying anything in the codebase, we are looking for database specific administrations.

If it is Only for Oracle, then this may be an options.

Deepak
Re: Database upgrades - with No downtime [message #678561 is a reply to message #678557] Sat, 14 December 2019 12:22 Go to previous message
deepakdot
Messages: 36
Registered: July 2015
Member
I am not aware of quiesceing. I will go thru this topic.
Previous Topic: Out of Memory - Runaway Memory, not releasing ORA-04036: PGA memory used by the instance exceeds
Next Topic: Schema Undo
Goto Forum:
  


Current Time: Mon Dec 16 03:56:53 CST 2019