A Good DBA Always Has a Deployment Plan

    By: Diana Robete on Oct 30, 2017

    Master (Class) Discussion (3).png

    By Diana Robete, DBAParadise.com | Simon Pane, Editor 

    “It’s not what you know, it’s what you do consistently” (Tim Ferriss - Tools of Titans. p.185)

    Why am I bringing this quote up? Because we all know that deploying changes should require a deployment plan, that we should deploy changes into test environment first, and not production first, and that deploying changes should require a rollback plan as well.

    It seems that we (DBAs) all know this stuff. The fact that we know what we should do, doesn’t really matter, the only thing that matters is what we do on a consistent basis. Over the years, in my career I have noticed the following patterns when it comes to deployments: 

    • Changes get deployed without a proper deployment plan
    • Deployment plans are not tested
    • Rollback plans are non-existent
    • Rollback plans, if they exist, are not tested

    If any of you have read my articles on dbaparadise.com, you know I provide advice or instructions from real life that you can apply instantly. My articles address real problems that DBAs are facing, and provide a solutions for them. This article is no different.

    After reading this article, you’ll be able to start creating awesome deployment plans that will guarantee successful deployments or rollbacks. This is what I’ll cover today: 

    1. Why Not Everyone Uses Deployment Plans
    2. Deployment Plan - Why Is It So Important?
    3. The Three Questions Every Deployment Plan Should Answer
    4. Rollback Plan - The Piece That Gets Forgotten
    5. Examples From Real Life

    1. Why Not Everyone Uses Deployment Plans

    DBAs might have the deployment plans but they can be misleading or not have complete information. Working with different DBAs, and overhearing conversations, these are some of the most common reasons why this is happening:

    • A deployment plan requires effort, actually a lot of effort in the beginning.
    • A deployment plan requires information sharing. Not everyone is up for that. Even in today’s information age, I’ve seen DBAs that do not like to share solutions to problems, scripts, and you guest it, deployment plans.
    • Awareness and importance. Not everyone is even aware of the importance of a deployment plan and what the content should be.

    2. Deployment Plan - Why Is It So Important?

    What comes to your mind when I say “Deployment Plan”?

    The answer I get most of the times is: “Change Management Process”. Most of the large companies must comply with some type of legislation, regulations, or standards such as: SOX, CSOX, ISO and so on. In order to comply, a Change Management Process should be in place. And this process, usually requires that changes are implemented through some kind of a deployment plan.

    I say “some kind of a deployment plan”, because there is no definitive guide that must be followed precisely when it comes to deployment plans. Creating deployment plans require effort on the DBA side, therefore, often the Change Management Process is viewed as a barrier that prevents the DBA from doing the work.

    How many times were you thinking about the change process being annoying, and time consuming? I’ve been guilty of that too! I am not here to contradict you, but actually, I totally agree that it is time consuming. However, I also believe that it makes our thinking more clear, it slows us down from just doing doing doing, to thinking thinking doing (which is a great thing). And most important, the outcome of the change process is a deployment plan!

    I am a strong believer that a deployment plan is important whether or not you need to comply with regulation. A deployment plan is here to serve the DBA. For any type of change, and especially the more complex ones, a DBA should have a deployment plan.

    Having a deployment plan has the following positive side effects:

    • Boosts confidence. When you have a step by step blueprint of what you are going to do, you feel more confident in yourself.
    • Promotes better sleep at night. This goes hand in hand with the confidence. When you know what you’re doing, you are more calm, and composed.
    • Increases your level of knowledge and understanding. While creating the deployment plan, you do more research, thus your knowledgeand understanding of the task increases.
    • Reduces the amount of effort you need to put in in the future, by being reusable. Any deployment plan can be reused, or partially reused,for similar tasks.

    3. The Three Questions Every Deployment Plan Should Answer

    I came up with this test for the deployment plans I needed to create. I know for a fact that if these 3 questions are answered, then the probability of my deployment plan passing approvals, and also being a kickass plan, is very high!

    So what are the 3 questions, that every plan needs to answer?

    It’s easy, here are the 3Ws:

    1. Where
    2. Who
    3. What

    Let’s go into more detail, to get a better understanding.

    Explicitly list where you are going to deploy the change, server name and database name. These two things are crucial in a deployment plan. One or the other is not enough.

    Make the deployment plan easy to understand, and make it “deployable” by any other IT professional, even if they are not a DBA.


    Server: oraprd1

    Database: HRPRD

    Explicitly list who the user is running the scripts. Do the scripts need to be run as your username, or SYS/SYSTEM? It is never obvious who needs to run the scripts. Just because you have SYSDBA access, doesn’t mean you can or should run the deployment as that user.


    Server: oraprd1; User: oracle

    Database: HRPRD; User: DBA_ADMIN

    -----------------or -----------------------------------

    Login to oraprd1 as oracle

    Connect to HRPRD database as DBA_ADMIN

    The “WHAT” is the reason the change and the deployment plan exist, because you need to execute something or perform an action. That “something” needs to be detailed in this section.

    Usually you run a set of scripts, or a set of commands. Detailed descriptions needs to be included, such as the path to the script location, or commands you are going to execute.

    Mentioning the action only is not enough. Statements such as “backup database” or “extract role DDL” are incomplete.








    Putty: oraprd1 as oracle

    . oraenv HRPRD

    $ sqlplus DBA_ADMIN/***






    If a deployment plan fails to address these 3 questions, it is an incomplete deployment plan. You need to go back and revise it.

    If this information is captured in the plan, then really anyone can run with it (assuming they have the appropriate privileges). Even if the plan is written in a way that anyone can run with it, the DBA should be the one deploying it, as the DBA is the one who can deal with the unexpected, should the unexpected occur (errors during deployment, unexpected rollback)! 

    4. Rollback Plan - The Piece That Gets Forgotten


    The rollback strategy within a deployment plan, is many times forgotten because there are  more changes that succeed than fail (which by the way is a great thing).

    You thought it was hard to convince someone to create a deployment plan? Then you haven’t tried to convince them of creating a rollback plan.

    When I mention the rollback strategy to some of my clients, I get a laugh from them and the answer ”We don’t rollback, we just keep on going...until we fix it.”

    The only three questions I need to ask them in order to change their minds are:

    • How long is the outage?
    • What if there is no fix by the time the outage is over?
    • What is the cost of unplanned downtime? (The latter is more of a visual image of time and money)

    We always want the deployment to succeed, and we don’t start it with the idea of rolling it back.

    However, there is always that question, what if we need to go back, what if we are hitting a bug that we don’t know yet how to fix? What if after successfully deploying your database change, a component in the application tier, outside of your control no longer works or had a failed deployment (this got missed during another team’s testing), and you need to roll back because of that? What IF?

    A Good DBA always has a rollback plan, no matter what the client says, or other people say!

    Having a detailed rollback plan, will ensure that you can bring back the database or application to the initial state as when you started your change, in a reasonable time.

    Having a detailed rollback plan, will ensure you meet the outage times. The rollback plan, has to be as detailed as the deployment plan itself. The rollback plan has to answer the same 3 questions: WHERE, WHO, WHAT, otherwise you need to revise it.

    You can’t be general with the rollback, such as “restore database from backup” or “drop tables” or “rollback patch”. These statements give you a general idea as to what you need to do, but have you tried doing a point in time restore of a database at 2:00 am from the last backup, in a Data Guard setup? Not as straightforward as you think...Plus it’s more to it than just restoring the database. Including these steps will give you a plan to follow in detail without having to look up and test again.

    Another crucial part of the rollback plan is testing it out. If you don’t test your rollback plan, how do you know it will work? The same way you test the deployment plan, you need to test the rollback plan as well. No taking shortcuts!

    5. Examples From Real Life

    Now that we discussed what a deployment plan is and what it should contain, let me show you the difference between a GOOD and a BAD Deployment Plan.

    Description of the Change:

    Rename a role in the database from HRAPP_RO to HRAPP_QUERY


    BAD Deployment Plan GOOD Deployment Plan

    Pre Deployment Steps:

    1. Extract the DDL for the Role HRAPP_RO
    2. Replace the HRAPP_RO with HRAPP_QUERY in the generated script above

    Pre Deployment Steps for DBA:

    Server: oraprd1

    Username: oracle

    Database: HRTST

    Username: / as sysdba

    Scripts Location: /home/oracle/Change/12345/script


    1. Extract the DDL for the Role HRAPP_RO


        SQL>@get_role_ddl.sql HRAPP_RO


        Generated File: 



    2. Create a copy of this file:


    $ cp role_HRAPP_RO.sql role_HRAPP_QUERY.sql


    3. Edit script role_HRAPP_QUERY.sql and replace HRAPP_RO with HRAPP_QUERY. Save File. There are many ways to achieve this, one of them would be to use ”sed -i” command in Unix. This is the script you will use to create the new role and assign the appropriate grants.


    Deployment Steps

    1. Run the statements from above to create HRAPP_QUERY role and run the grants
    Drop HRAPP_RO role

    Deployment Steps for DBA:

    Server: oraprd1

    Username: oracle

    Database: HRTST

    Username: / as sysdba

    Scripts Location: /home/oracle/Change/12345/script


    1. Create new role and run grants




    2. Drop HRAPP_RO role


        SQL>drop role HRAPP_RO


    1. Recreate HRAPP_RO role and grants
    Drop HRAPP_QUERY role


    Rollback Steps for DBA:

    Server: oraprd1

    Username: oracle

    Database: HRTST

    Username: / as sysdba

    Scripts Location: /home/oracle/Change/12345/script


    1. Recreate the HRAPP_RO role, using the script generated in pre-deployment step 1




    2. Drop the role HRAPP_QUERY


    SQL>drop role HRAPP_QUERY;


    Do you see the difference between the GOOD and the BAD deployment plan? Does a good deployment plan make you feel more confident? Does it increase the probability of the change to be successful? Of course it does!

    Looking at the above BAD deployment plan, the following questions remain unanswered:

    • Where is this change going to be deployed, against which database?
    • Who should run the change, what username?
    • How do I extract the DDL role? (There are many ways to do it, but in this particular situation how am I supposed to do it?)
    • How many statements do I need to run?
    • Am I supposed to save the output somewhere?
    • As for rollback, how do I recreate the HRAPP_RO role, what grants are within the role?

    All these questions are answered in the GOOD Deployment Plan.

    • We know exactly WHERE we are running the deployment.
    • We know exactly WHO is running the deployment.
    • We know exactly WHAT scripts or statements we need to run. 
    • We can hand this deployment plan to any DBA in the team, and they will know exactly what to do and how to do it!


    Diana Robete a senior DBA consultant and founder of DBAParadise.com.

    Released: October 30, 2017, 7:51 am | Updated: November 15, 2017, 12:53 pm
    Keywords: Department | dba

    Copyright © 2019 Communication Center. All Rights Reserved
    All material, files, logos and trademarks within this site are properties of their respective organizations.
    Terms of Service - Privacy Policy - Contact

    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    IOUG Logo

    Copyright © 1993-2019 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy