OraFAQ Forum: Server Administration » Database upgrades

Home » RDBMS Server » Server Administration » Database upgrades - with No downtime (12c)

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

Database upgrades - with No downtime [message #678163]

Thu, 14 November 2019 00:05

deepakdot
Messages: 91
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

Report message to a moderator

Re: Database upgrades - with No downtime [message #678164 is a reply to message #678163]

Thu, 14 November 2019 02:47

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have a look at editions

Report message to a moderator

Re: Database upgrades - with No downtime [message #678168 is a reply to message #678164]

Thu, 14 November 2019 05:06

deepakdot
Messages: 91
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.

Report message to a moderator

Re: Database upgrades - with No downtime [message #678170 is a reply to message #678168]

Thu, 14 November 2019 06:23

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

See if this is any help: https://blogs.oracle.com/oraclemagazine/edition-based-redefinition-part-2

Report message to a moderator

Re: Database upgrades - with No downtime [message #678172 is a reply to message #678163]

Thu, 14 November 2019 06:49

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: Database upgrades - with No downtime [message #678214 is a reply to message #678172]

Sun, 17 November 2019 23:14

deepakdot
Messages: 91
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 ).

Report message to a moderator

Re: Database upgrades - with No downtime [message #678215 is a reply to message #678214]

Mon, 18 November 2019 00:19

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

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.

Report message to a moderator

Re: Database upgrades - with No downtime [message #678531 is a reply to message #678215]

Wed, 11 December 2019 07:47

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

Senior Member
Account Moderator

What option did you then choose to achieve your goal?

Report message to a moderator

Re: Database upgrades - with No downtime [message #678551 is a reply to message #678531]

Thu, 12 December 2019 22:44

deepakdot
Messages: 91
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

Report message to a moderator

Re: Database upgrades - with No downtime [message #678553 is a reply to message #678551]

Fri, 13 December 2019 00:57

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

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.

Report message to a moderator

Re: Database upgrades - with No downtime [message #678557 is a reply to message #678551]

Fri, 13 December 2019 02:07

John Watson
Messages: 8960
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

Report message to a moderator

Re: Database upgrades - with No downtime [message #678560 is a reply to message #678553]

Sat, 14 December 2019 12:21

deepakdot
Messages: 91
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

Report message to a moderator

Re: Database upgrades - with No downtime [message #678561 is a reply to message #678557]

Sat, 14 December 2019 12:22

deepakdot
Messages: 91
Registered: July 2015

Member

I am not aware of quiesceing. I will go thru this topic.

Report message to a moderator

Previous Topic:	Location of alert log file
Next Topic:	Oracle TimesTen In-Memory Database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 08:46:45 CST 2024