<p>Yes, it is possible, but it takes a bit of work. I assume that the concern with duplicating the database is 1) time and 2) size/storage, otherwise it seems like it would be trivial to spawn databases for testing against. Based on the fact that you are targeting PL/SQL i'm guessing you are working with Oracle.</p>
<p>You will either need: 1) a smaller-scale dataset that can act as a representative sample of you data which is sufficient for testing all of your corner cases or 2) to clone the <em>whole</em> production database. Honestly, I recommend option #2. There are numerous cases where things operate correctly at a smaller scale, but run into issues once scaled up - especially with database data. Ideally, you could store and replay a sample of queries for load testing using a database proxy like ScaleArc (if Oracle won't let you store your queries).</p>
<p>Saying you need to clone your whole dataset may make me sound like a kook and like it will take forever, but you may find this answer in which I detail a methodology for <a href="https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases/1220#1220">cloning multi-terabyte databases in seconds</a> without significantly increasing your storage costs due to duplication and delta-snapshotting useful. This may be harder if you are using Oracle's proprietary storage system. Be forewarned, the tech isn't cheap, but if you wanted cheap, you wouldn't be using Oracle. The best way to sell that to your upper management is to prove that the tech is cheaper than the outage/data mangling it would prevent. It's your insurance policy against outages - so how much insurance are you willing to buy?</p>

CC BY-SA 4.0
2024-05-27T21:20:55.260Z
Do you perhaps know how to answer this question as well? Here:  https://devops.stackexchange.com/questions/19340/green-blue-deployments-with-multiple-development-teams
CodeMonkey
CC BY-SA 3.0
2017-09-19T13:27:35.780Z
We have a smaller-scale dataset, but how does it relate to blue-green deployments? I am talking about switching users from app version N to app version N+1 without apparent downtime by maintaining two production applications.
Alexey
CC BY-SA 3.0
2017-09-19T15:16:57.720Z
@Alexey You might want to clarify your question then.
James Shewey
CC BY-SA 4.0
2017-09-18T16:19:30.457Z
2021-01-15T18:36:39.227Z
2021-01-15T18:36:39.227Z
James Shewey
<p>This is very easily possible, with no change to any code (neither the PL/SQL code nor your application code, if there is some other application like Java or whatever):</p>

<ol>
<li>Put all PL/SQL in one (or more) separate Oracle schema. If your application today uses only one "myapp" schema for tables and PL/SQL, then in the future you will have "myapp" (tables, indexes etc.) and "mycode" (or whatever) which <em>only</em> contains PL/SQL procedures.</li>
<li>In "myapp", create synonyms for all the objects that now live in "mycode".</li>
<li>In "mycode", you can create stub tables - i.e., the same tables as in "myapp", but without any data. You do not need any indexes or whatnot, just the tables. These are only used at compile time; they are especially not used by the optimizer, later.</li>
</ol>

<p>That's it for separating your code and data. The next step obviously is to create "mycode2" as a clone of "mycode", and from then on you simply need to switch the synonyms in "myapp" between those two. You can have as many code schemas as you want, and switch between them freely with some rather trivial tooling.</p>

<p>(Setting the proper options, grants, making sure that the PL/SQL uses the tables of the caller instead of the compile unit left as an exercise to the reader... - feel free to ask if anything is really unclear.)</p>

<p>Practical hints:  It is true that you have to change 5000 synonyms (well, hopefully you do not have 5000 PL/SQL packages :) ), but that is an uncritical operation insofar as there is nothing that can go wrong (both <code>drop synonym</code> and <code>create synonym</code> can not go wrong for any reason I am aware of; unlike e.g. <code>drop table</code> which can lock if the table is in use). You will obviously script those drops; and you will also probably query the synonyms you have to set by <code>select object_name from all_objects where user_name='MYCODE2' and object_type in ('PACKAGE', 'PROCEDURE')</code> or something like that to automatically catch all of them. </p>

CC BY-SA 3.0
2017-09-18T20:06:06.920Z
So, what's the downvote for?
AnoE
CC BY-SA 3.0
2017-09-19T13:23:40.060Z
So when the "frontend" connects to the myapp schema, it calls "mypackage", but calls it with invoker rights so it uses the tables in "myapp"? Interesting. What I don't like is that there's now 5000 synonyms to be changed per deployment instead of a single switch.
Alexey
CC BY-SA 3.0
2017-09-19T20:41:55.163Z
Yes, @Alexey. It is true that you have to change 5000 synonyms (well, hopefully you do not have 5000 PL/SQL packages :) ), but that is an uncritical operation insofar as there is nothing that can go wrong (both `drop synonym` and `create synonym` *can not* go wrong for any reason I am aware of; unlike e.g. `drop table` which can lock if the table is in use). You will obviously script those drops; and you will also probably query the synonyms you have to set by `select object_name from all_objects where...` to automatically catch all of them. It may not be beautiful, but it *is* blue-green.
AnoE
CC BY-SA 3.0
2017-09-20T09:11:46.487Z
Actually, there are almost 12000 packages in the system.
Alexey
CC BY-SA 3.0
2017-09-20T11:09:39.423Z
@Alexey: cool. Well, you know your system best. You'll have to test if such an approach works for you, performance wise, anyways.
AnoE
CC BY-SA 4.0
2024-05-27T21:21:07.543Z
Do you perhaps know how to answer this question as well? Here:  https://devops.stackexchange.com/questions/19340/green-blue-deployments-with-multiple-development-teams
CodeMonkey
CC BY-SA 3.0
2017-09-18T19:37:34.103Z
2017-09-19T20:44:05.303Z
2017-09-19T20:44:05.303Z
AnoE
<p>For non-data components such as DDLs or PL/SQL I would recommend using Liquibase to manage them.  However, to do Blue Green deployment with sub-second downtime is likely an impossibility because you need to stop the requests to the old database and route them to an exact copy of the new database at the time with your delta being applied.</p>
<p>The closest I can think about doing it is as follows.  For the purposes of the sequence I have the liquibase migration scripts as part of the application startup to avoid having multiple steps for application startup.</p>
<h2>Verification</h2>
<ol>
<li>Copy Green DB and other data artifacts such as file systems etc. to Blue.  This is a time and size factor.</li>
<li>Deploy and start Green application blue.</li>
<li>Deploy and start the new application on Blue which applies liquibase scripts.</li>
<li>Verify</li>
</ol>
<h2>Switch over</h2>
<ol>
<li>Pause the Green application (this prevents any future access to the data).  This begins the downtime.</li>
<li>Copy Green DB and other data artifacts such as file systems etc. to Blue (again).  This is a time and size factor.</li>
<li>Deploy and start Green application blue.</li>
<li>Deploy and start the new application on Blue which applies liquibase scripts.</li>
<li>Switch route to Blue</li>
</ol>
<h2>Switch over and Fallback (note new data loss)</h2>
<p>(from above)</p>
<ol start="6">
<li>Restart green application</li>
<li>Switch route back to Green</li>
</ol>
<h2>Reducing cost</h2>
<ul>
<li>Instead of a new database or ElasticSearch or Kafka instance, just use a different schema and credentials between blue and green.</li>
<li>Instead of a new storage, use a different root folder between blue and green.</li>
</ul>

CC BY-SA 4.0
2024-05-27T21:21:22.367Z
Do you perhaps know how to answer this question as well? Here:  https://devops.stackexchange.com/questions/19340/green-blue-deployments-with-multiple-development-teams
CodeMonkey
CC BY-SA 4.0
2021-01-13T17:00:26.370Z
2021-01-13T17:00:26.370Z
Archimedes Trajano
<p>Is it possible to do blue-green deployment when your software is mostly a set of PL/SQL packages?</p>

<p>I don't really want to duplicate the database, the financial risks in case of any desyncs are too big. I understand that step 1 is to decouple schema changes from package changes, but how to proceed further?</p>

<p>Is splitting packages into a separate DB that I can spawn multiple copies of the only option?</p>

<deployment><databases>
How to do blue-green deployment with PL/SQL code?
Alexey
# How to do blue-green deployment with PL/SQL code?

(Asked by: username_0 on 2017-09-18)

Is it possible to do blue-green deployment when your software is mostly a set of PL/SQL packages?

I don't really want to duplicate the database, the financial risks in case of any desyncs are too big. I understand that step 1 is to decouple schema changes from package changes, but how to proceed further?

Is splitting packages into a separate DB that I can spawn multiple copies of the only option?

**Tags:** deployment, databases


---

## 3 answers

### Answer by username_1 on 2017-09-18. Score: 3

Yes, it is possible, but it takes a bit of work. I assume that the concern with duplicating the database is 1) time and 2) size/storage, otherwise it seems like it would be trivial to spawn databases for testing against. Based on the fact that you are targeting PL/SQL i'm guessing you are working with Oracle.

You will either need: 1) a smaller-scale dataset that can act as a representative sample of you data which is sufficient for testing all of your corner cases or 2) to clone the *whole* production database. Honestly, I recommend option #2. There are numerous cases where things operate correctly at a smaller scale, but run into issues once scaled up - especially with database data. Ideally, you could store and replay a sample of queries for load testing using a database proxy like ScaleArc (if Oracle won't let you store your queries).

Saying you need to clone your whole dataset may make me sound like a kook and like it will take forever, but you may find this answer in which I detail a methodology for [cloning multi-terabyte databases in seconds](https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases/1220#1220) without significantly increasing your storage costs due to duplication and delta-snapshotting useful. This may be harder if you are using Oracle's proprietary storage system. Be forewarned, the tech isn't cheap, but if you wanted cheap, you wouldn't be using Oracle. The best way to sell that to your upper management is to prove that the tech is cheaper than the outage/data mangling it would prevent. It's your insurance policy against outages - so how much insurance are you willing to buy?

> **Comments:**
> **username_0**: We have a smaller-scale dataset, but how does it relate to blue-green deployments? I am talking about switching users from app version N to app version N+1 without apparent downtime by maintaining two production applications.  
> **username_1**: @username_0 You might want to clarify your question then.  
> **username_2**: Do you perhaps know how to answer this question as well? Here:  https://devops.stackexchange.com/questions/19340/green-blue-deployments-with-multiple-development-teams  

---

### Answer by username_3 on 2017-09-18. Score: 1

This is very easily possible, with no change to any code (neither the PL/SQL code nor your application code, if there is some other application like Java or whatever):

1. Put all PL/SQL in one (or more) separate Oracle schema. If your application today uses only one "myapp" schema for tables and PL/SQL, then in the future you will have "myapp" (tables, indexes etc.) and "mycode" (or whatever) which *only* contains PL/SQL procedures.
2. In "myapp", create synonyms for all the objects that now live in "mycode".
3. In "mycode", you can create stub tables - i.e., the same tables as in "myapp", but without any data. You do not need any indexes or whatnot, just the tables. These are only used at compile time; they are especially not used by the optimizer, later.

That's it for separating your code and data. The next step obviously is to create "mycode2" as a clone of "mycode", and from then on you simply need to switch the synonyms in "myapp" between those two. You can have as many code schemas as you want, and switch between them freely with some rather trivial tooling.

(Setting the proper options, grants, making sure that the PL/SQL uses the tables of the caller instead of the compile unit left as an exercise to the reader... - feel free to ask if anything is really unclear.)

Practical hints: It is true that you have to change 5000 synonyms (well, hopefully you do not have 5000 PL/SQL packages :) ), but that is an uncritical operation insofar as there is nothing that can go wrong (both `drop synonym` and `create synonym` can not go wrong for any reason I am aware of; unlike e.g. `drop table` which can lock if the table is in use). You will obviously script those drops; and you will also probably query the synonyms you have to set by `select object_name from all_objects where user_name='MYCODE2' and object_type in ('PACKAGE', 'PROCEDURE')` or something like that to automatically catch all of them.

> **Comments:**
> **username_3**: So, what's the downvote for?  
> **username_0**: So when the "frontend" connects to the myapp schema, it calls "mypackage", but calls it with invoker rights so it uses the tables in "myapp"? Interesting. What I don't like is that there's now 5000 synonyms to be changed per deployment instead of a single switch.  
> **username_3**: Yes, @username_0. It is true that you have to change 5000 synonyms (well, hopefully you do not have 5000 PL/SQL packages :) ), but that is an uncritical operation insofar as there is nothing that can go wrong (both `drop synonym` and `create synonym` *can not* go wrong for any reason I am aware of; unlike e.g. `drop table` which can lock if the table is in use). You will obviously script those drops; and you will also probably query the synonyms you have to set by `select object_name from all_objects where...` to automatically catch all of them. It may not be beautiful, but it *is* blue-green.  
> **username_0**: Actually, there are almost 12000 packages in the system.  
> **username_3**: @username_0: cool. Well, you know your system best. You'll have to test if such an approach works for you, performance wise, anyways.  
> **username_2**: Do you perhaps know how to answer this question as well? Here:  https://devops.stackexchange.com/questions/19340/green-blue-deployments-with-multiple-development-teams  

---

### Answer by username_4 on 2021-01-13. Score: 0

For non-data components such as DDLs or PL/SQL I would recommend using Liquibase to manage them. However, to do Blue Green deployment with sub-second downtime is likely an impossibility because you need to stop the requests to the old database and route them to an exact copy of the new database at the time with your delta being applied.

The closest I can think about doing it is as follows. For the purposes of the sequence I have the liquibase migration scripts as part of the application startup to avoid having multiple steps for application startup.

## Verification

1. Copy Green DB and other data artifacts such as file systems etc. to Blue. This is a time and size factor.
2. Deploy and start Green application blue.
3. Deploy and start the new application on Blue which applies liquibase scripts.
4. Verify

## Switch over

1. Pause the Green application (this prevents any future access to the data). This begins the downtime.
2. Copy Green DB and other data artifacts such as file systems etc. to Blue (again). This is a time and size factor.
3. Deploy and start Green application blue.
4. Deploy and start the new application on Blue which applies liquibase scripts.
5. Switch route to Blue

## Switch over and Fallback (note new data loss)

(from above)

6. Restart green application
7. Switch route back to Green

## Reducing cost

* Instead of a new database or ElasticSearch or Kafka instance, just use a different schema and credentials between blue and green.
* Instead of a new storage, use a different root folder between blue and green.

> **Comments:**
> **username_2**: Do you perhaps know how to answer this question as well? Here:  https://devops.stackexchange.com/questions/19340/green-blue-deployments-with-multiple-development-teams  

---

