A DBA’s first steps in Jenkins

My Customer wanted an automated way to refresh an application database to a known state, to be done by non-technical personnel. As a DBA I know a lot of scripting, can build some small web interfaces, but why bother when there are ready available tools, like Jenkins. Jenkins is mostly a CI/CD developer thing that for a classical DBA is a bit of magic. I decided to try this tool to script the refreshing of my application.

Getting started

First, fetch the Jenkins distribution from https://jenkins-ci.org, I used the jenkins.war latest version. Place the jenkins.war file in a desired location and you’re almost set to go, set the environment variable JENKINS_HOME to a sane value, or else your Jenkins settings, data and workdir will be in $HOME/.jenkins/

Start Jenkins by using the following commandline:

java -jar jenkins.war --httpPort=8024

You may want to make a start script to automate this step. Please note the –httpPort argument: choose a available portnumber (and make sure the firewall is opened for this port)

When starting Jenkins for the first time it creates a password that it will show in the standard output. When you open the webinterface for Jenkins for the first time you need this password. After logging in, install the recommended plugins. In this set there should be at least the Pipeline plugin. The next step will create your admin user account.

Creating a Pipeline build job.

Navigate to “New Item” to start creating your first pipeline. Type a descriptive name, choose as type a Pipeline

After creating the job, you can start building the pipeline: In my case I needed about four steps: stopping the Weblogic servers,
clearing the schemas, importing the schemas and fixing stuff, and finally starting Weblogic again.

The Pipeline scripting language is quite extensive, I only used the bare minimum of the possibilities, but at least it gets my job done. The actual code can be entered in the configuration of the job, in the pipeline script field. A more advanced option could be to retrieve your Pipeline code (plus additional scripts) from a SCM like Git or Bitbucket.

The code below is my actual code to allow the refresh of the application:

pipeline {
    agent any
    stages {
        stage ('Stop Weblogic') {
            steps { 
                echo 'Stopping Weblogic'
                sh script: '/u01/app/oracle/product/wls12212/oracle_common/common/bin/wlst.sh /home/oracle/scripts/stopServers.py'
            }
        }
        stage ( 'Drop OWNER') {
            steps {
                echo "Dropping the Owner"
                sh script: 'ssh dbhost01 "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv -s ; sqlplus /@theSID @ scripts/drop_tables.sql"'
            }
        }
        stage ( 'Import OWNER' ) {
            steps {
                echo 'Importing OWNER'
                sh script: 'ssh dbhost01 "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv -s ; impdp /@@theSID directory=thedirforyourdump \
                            dumpfile=Youknowwhichfiletoimport.dmp \
                            logfile=import-`date +%F-%h%m`.log \
                            schemas=ONLY_OWNER,THE_OTHER_OWNER,SOME_OTHER_REQUIRED_SCHEMA \
                            exclude=USER,SYNONYM,VIEW,TYPE,PACKAGE,PACKAGE_BODY,PROCEDURE,FUNCTION,ALTER_PACKAGE_SPEC,ALTER_FUNCTION,ALTER_PROCEDURE,TYPE_BODY"', returnStatus: true

				 echo 'Fixing invalid objects'           
                 sh script: 'ssh dbhost01 "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv -s ; sqlplus / as sysdba @?/rdbms/admin/utlrp"'    
				 
                 echo 'Gathering statistics in the background'
                 sh script: 'ssh dbhost01 "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv -s ; sqlplus /@theSID @ scripts/refresh_stats.sql"'
            }
        }
        stage ( 'Start Weblogic' ) {
            steps {
                echo 'Starting Weblogic'
                sh script: '/u01/app/oracle/product/wls12212/oracle_common/common/bin/wlst.sh /home/oracle/scripts/startServers_turbo.py'
            }
        }
    }
}

In this script you can see the four global steps, but some steps are more involved. In this situation I decided not to completely drop the schemas associated with the application, the dump file could come from a different environment with different passwords. Additionally I only import here the known schemas, if the supplied dumpfile accidentally contains additional schemas the errors in the log would be enormous due to not creating the useraccounts in the import stage.

When the job is saved, you can try a Build, this will run your job, you can monitor the console output to see how your job is going.

SQL*Plus with wallet authentication

The observant types among you may have noticed that I used a wallet for authentication with SQL*Plus and impdp. As this tool would be used by people who should not get DBA passwords, using a password on the commandline is not recommended: note that all the command above and their output would be logged in plaintext. So I decided to start making use of a wallet for the account information. Most steps are well documented, but I found that the step of making the wallet autologin capable (not needing to type a wallet password all the time) was documented using the GUI tool, but not the commandline tool. Luckily there are ways of doing that on the command line.

mkdir -p $ORACLE_HOME/network/admin/wallet
mkstore -wrl $ORACLE_HOME/network/admin/wallet/ -create
mkstore -wrl $ORACLE_HOME/network/admin/wallet -createCredential theSID_system system 'YourSuperSekritPassword'
orapki wallet create -wallet $ORACLE_HOME/network/admin/wallet -auto_login

sqlnet.ora needs to contain some information so the wallet can be found:

WALLET_LOCATION =
  (SOURCE =    (METHOD = FILE)
   (METHOD_DATA =      (DIRECTORY = <<ORACLE_HOME>>/network/admin/wallet)    )  )
SQLNET.WALLET_OVERRIDE = TRUE

also make sure a tnsnames entry is added for your wallet credential name (above: theSID_system) now using sqlplus /@theSID_system should connect you to the database as the configured user.

Asking Questions

The first job was quite static: always the same dump, or I need to edit the pipeline code to change the named dumpfile… not as flexible as I would like… Can Jenkins help me here? Luckily, YES:

    def dumpfile
    def dbhost = 'theHost'
    def dumpdir = '/u01/oracle/admin/THESID/dpdump'

    pipeline {
    agent any
    stages {
        stage ('Choose Dumpfile') {
            steps {
                script {
                    def file_collection
                    file_collection = sh script: "ssh $dbhost 'cd $dumpdir; ls *X*.dmp *x*.dmp 2>/dev/null'", returnStdout: true
                    dumpfile = input message: 'Choose the right dump', ok: 'This One!', parameters: [choice(name: 'dump file', choices: "${file_collection}", description: '')]
                }
            }
        }
        stage ('Stop Weblogic') {
            steps { 
                echo 'Stopping Weblogic'
                sh script: '/u01/app/oracle/product/wls12212/oracle_common/common/bin/wlst.sh /home/oracle/scripts/stopServers.py'
            }
        }
        stage ( 'Drop OWNER') {
            steps {
                echo "Dropping Owner"
                sh script: 'ssh $dbhost "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv; sqlplus /@theSID @ scripts/drop_tables.sql"'
            }
        }
        stage ( 'Import OWNER' ) {
            steps {
                echo 'Import OWNER'
                sh script: "ssh $dbhost 'export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv; impdp /@theSID directory=dump \
                            dumpfile=$dumpfile \
                            logfile=import-`date +%F@%H%M%S`.log \
                            schemas=MYFAVOURITE_SCHEMA,SECONDOWNER \
                            exclude=USER,SYNONYM,VIEW,TYPE,PACKAGE,PACKAGE_BODY,PROCEDURE,FUNCTION,ALTER_PACKAGE_SPEC,ALTER_FUNCTION,ALTER_PROCEDURE,TYPE_BODY'", returnStatus: true
                            
                 sh script: 'ssh $dbhost "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv; sqlplus / as sysdba @?/rdbms/admin/utlrp"'
                            
                 sh script: 'ssh dbhost "export ORACLE_SID=theSID; export ORAENV_ASK=no;\
                            source oraenv; sqlplus /@theSID @ scripts/refresh_stats.sql"'
            }
        }
        stage ( 'Start Weblogic' ) {
            steps {
                echo 'Starting Weblogic'
                sh script: '/u01/app/oracle/product/wls12212/oracle_common/common/bin/wlst.sh /home/oracle/scripts/startServers_turbo.py'
            }
        }
    }
}

The first stage actually looks at the place where all the dumpfiles are to be found and does a ls on it. This listing is then stored in a variable that will be split into choices. The running job will wait for input, so no harm is done until the choice is made.

Starting a build like this will pause, you can see that when looking at the latest running build in the build queue.

When clicking the link the choice can be made (or the build can be aborted)