Written by Nassyam Basha and Monowar Mukul

This article will learn how we can play Jenkins works with Oracle Database and understand Jenkins’s internal process/flow and its interpretation with the Oracle Database. This article will perform the complete setup of Oracle Database, including the installations and configuration. Let’s explore the major tasks we are going to show in this article.

• Downloads and installs the required RPM’s
• How to Install the Oracle 19c RDBMS Software
• Creating the Oracle Database
• Final database validation
• Test email system from Jenkins upon the job completion.

Note: We’ve tested this demo on training and development purposes (or) environments.

Jenkins and Its History

Jenkins is a popular open-source continuous integration server written in Java. Jenkins allows us to perform continuous integration and build automation with a predefined list of endless delivery steps. Jenkins, the former name was Hudson, which was made on Java. Later with the disputes of Oracle, the name changed to Jenkins in the year 2011.

Continuous Integration

Consider the developer commits the changes to the source code, which is shared in a central repository, for example, Github. Simultaneously, continuous development is in progress where the developers try to change or add more code at regular intervals. In parallel, the Jenkin server tries to identify the changes in code from the central repository. Jenkins will pull those changes and tries to start preparing the new Build. If the Build fails, then the responsible team will be notified. Thus it helps teams to identify and resolve the issues at the early stage of development. If the Build was successful, then the code will be deployed to the test server.

Why Jenkins

There are various benefits with the Jenkins to use:

• Open Source and user friendly
• Easy to use
• Simplified Installation
• Easy to configure
• Continuous software development
• Customization
• 400+ plugins
• Little Maintenance
• GUI based tool
• Excellent alerting system (email)
• Platform Independent

DBA Version

In this article, we consider we have to create a database in multiple servers. It is a challenge to execute several tasks for multiple servers at the time of code deployments within a small release window. If any steps fail during the execution, then Jenkins allows them to stop the process. We can also send out notifications in case of a build success or failure at any point in time. A DBA can use to automate deployment procedures. We decided to try this tool to script the installing Oracle database software and then create a database and validate it.

Products/Tools In Use

For this article, we have collaborated on various tools based on the demand for this complete exercise. They are

1. Ansible
2. Shell Script
3. SQL Script
4. GitHub
5. Jenkins
6. Oracle RDBMS Software

Plugin

Additional plugins can extend Jenkins. We can install plugins to support building and testing different applications. Below are the various plugins used in this demo/article.

1. Role-Based Authentication Strategy, To create Users and Roles
2. SQLPlus Script Runner, To execute SQL Scripts
3. Email Notification, To send an email upon success or failure
4. Git, For the Integration

The below image states the installed plugins as per the requirement.

As an example, below is the SQLPlus Script Runner Plugin.

Email Notification Plugin:

Creating a Pipeline Build Job

Jenkin Pipeline is a combination of plugins for implementing and integrating continuous delivery pipelines.

1. Login to the Git and gather the Source controlled GitHub repository, which is already created.
2. Copy the URL of the project “oradbjenkins,” which will be used while creating a Pipeline from Jenkins.
3. Connect to Jenkins and provide the Pipeline definition and select the Source code management as “Git.”
   • Jenkins Dashboard – New Item à
   • Provide the repository URL along with the credentials of its private repository.
   • Provide the Branch name as “/master” or mention the child branch name.
   • Provide the local repository location
   • Provide the script path, which consists of the actual RDBMS and Database setup.
4. Let’s explore the actual Source code management. It performs
   • Downloads the required RPM’s
   • Installs the downloaded RPM’s
   • Installs the Oracle 19c RDBMS software
   • Cleanup the RPM files
   • Creates the database.
5. Create new Pipeline Job with the name “02_add_pdb_tns” and mention the parent build trigger as the “01_createOracleDB” with the option “Trigger only if build is stable” – That refers if the project “01_createOracleDB” successful then only it will proceed to execute the pipeline job “02_add_pdb_tns.”
6. Move to the Pipeline section and add the below script.

Setup of Post Database Creation

1. Let’s create freestyle projects for the below tasks
   • Modify the password
   • Change to Archive Log Mode
   • Validating Database Components
2. Let’s set up the Pipeline for PostDBCreation for the above projects.
3. Finally, let’s add another ansible script to validate the database status.

Executing the Build Job

Before executing the build job, let’s review the overall projects we have created to complete Oracle RDBMS installation with Database creation.

Click on the CreateOracleDB Project and then click on the “Build Now” – After that, the complete tasks will be executed based on the sequence we have assigned with upstream/downstream concept.

Monitoring the Build Job

To monitor the build job, Navigate to Jenkins Dashboard > Select the project “01_createOracleDB” > Full stage view.

This option gives the detailed graphical output of each stage, the elapsed time, and how much percentage completed of the specific stage.

To view the text mode, then navigate to Jenkins Dashboard > Select the job 01_createOracleDB > Console output à view as Plain text.

We can view the build job status of each of the projects along with the submodules. Navigate to Jenkins Dashboard > Select the project 02_add_pdb_tns > Click on Full Stage View.

When the above both builds are completed, then automatically it follows the pipeline “PostDBCreation.”

If any of the build jobs were failed, then as per the Email notification settings, the email will be received with the complete error description below.

We can review and modify the projects/jobs/scripts, and it can be relaunched.

Cleanup of Old Builds

As per the process of continuous integration and continuous delivery – developers may create many builds and in order to clean up these builds we can configure to perform cleanup automatically based on the retention policy we assign.

• Select Discard Old Builds
• Mention Days to keep the Builds
• Maximum number of builds to keep for the entire retention policy
• Select “Restrict where this project can be run.”
  • Mention the Lable expression as the target server “ORA-X1.”

Summary

We’ve learned starting from Jenkins’s basics and history, including the various benefits of using Jenkins. We have also observed the various projects that can be created, such as freestyle or Pipeline, based on the demand.

Jenkins with Oracle Database: DBA’s always has to extend or add more add-ons to this new digital generation’s DBA career. Hence we decided to do some exercise with Jenkins over the Oracle Database 19c. We have integrated various DevOps and products to fulfill this article, such as Jenkins, Ansible, Shell Script, Oracle, SQL Script, etc.

Execution Flow: To better understand the execution flow, review the original diagram of this article. The entire article’s main script is “oradbJenkins,” which downloads the required RPM’s of Oracle RDBMS, Installation of RPM’s and Installation of the RDBMS software and creation of the Oracle database and rest of the shell/SQL scripts does the post database creation tasks and the validations.

We’ve also explored how to review the execution build logs to monitor the job status with graphical and text mode. Finally, we have also tested the email alerting system if in case of any job failures.

About the Authors

Nassyam Basha is a Database Administrator. He has more than 13 years of experience as a Production Oracle DBA, currently working as a Database Consultant. He holds a master’s degree in Computer Applications from the University of Madras. He is an Oracle 11g Certified Master and honored as Oracle ACE Director from Oracle Corp. He actively participates in Oracle-related forums such as ODevC/Hub with a status of Silver Crown, Oracle support awarded as “Guru” and acting as Oracle Developer Community Moderator and written 60+ articles with OTN, Dell, Toad World, UKOUG, Quest Community, etc. He holds the prestigious “Select Journal Editor’s Choice Award” in 2018 and 2020 based on the Quest Community’s best Oracle technical article. He is a board member for the KSAOUG, MAA SIG, and IDGOUG Oracle User Groups. He maintains an Oracle technology-related blog, www.oracle-ckpt.com, and can be reached at https://www.linkedin.com/in/nassyambasha/.

Monowar Mukul is currently working as a Principal Oracle Database Specialist. I am an Oracle Certified Master (Oracle 12c Certified Master Administration, Oracle 12c Certified Master MAA, and Oracle 11g Certified Master Administration). He has been working as an Oracle DBA consultant for over 17 years with Oracle MAA space for both Exadata and non-Exadata Systems, Oracle Cloud Space, and SOA Middleware. He worked across various business sectors including tertiary education, energy, government, mining, and transport in Australia. He demonstrated highly developed critical thinking and analytical skills working as a Principal Oracle Database Specialist. You can find more details about him and his work achievements at https://www.linkedin.com/in/monowarmukul/.

Interested in Writing for SELECT?

Are you interested in getting your writing published and sharing your knowledge with other Oracle users? Quest Oracle Community is looking for writers to contribute to the SELECT Journal for our Quest Database & Technology Community audience. We are looking for writers with expertise in a variety of topics, including the following:

• Database sharding
• Database security
• Database performance
• Database development
• Database auditing
• And more!

For more information about how to get involved, please email [email protected].