Azure DevOps provides comprehensive support for integrating Oracle Database DevOps practices into your CI/CD pipelines. By leveraging Azure DevOps services and tools, you can automate database deployment, version control, testing, and monitoring, ensuring a smooth and efficient DevOps workflow for Oracle databases.

Here are the key aspects of Azure DevOps integration with Oracle DB DevOps:

1. Version Control: Azure DevOps supports popular version control systems like Git and enables you to manage database schema changes and scripts in a version-controlled repository. You can track changes, collaborate with team members, and easily roll back to previous versions if needed.

2. Continuous Integration (CI): Azure DevOps allows you to set up CI pipelines that automatically build and validate your database changes whenever a new commit is made to the repository. This ensures that your database artifacts are always in a deployable state and reduces the risk of introducing errors.

3. Continuous Delivery (CD): Azure DevOps enables seamless deployment of your Oracle database changes to different environments, including development, testing, staging, and production. You can define release pipelines with different stages and approvals to control the promotion of database changes across environments.

Required Pre-requisites:

• Server IP
• Username (Schema Name)
• Service type
• DB instance name (SID)
• UAT password
• If RAC, primary IP address, and scan IP address
• TNS File

To Do :

In the Azure Repository create the folder structure like :

Here Consider “ORACLEDB” as the schema (Username) and create the folder structure as shown in the image under the schema. For e.g., If there are 5 schemas, Create the same folder structures for all 5 schemas likewise. And along with the folder structure maintain one Deploy.ctl file for each schema.

Once this configuration is done. Upload the required files inside the correct folder. For E.g. Suppose I want to create a table in the database. Write the file that will help you to create the table and upload it in the Tables folder. Once this activity is done modify the “Deploy.ctl” by specifying “Tables\#{Uploaded file name}#”.

Along with that Maintain one more Deploy.ctl file for all the schemas (At the repository level)  and mention all the schema names there.

Powershell Script Execution:

Need to Write the PowerShell script that performs a series of actions using SQLPlus to deploy an Oracle database. The script iterates over a list of lines obtained from a file, modifies a control file(deploy.ctl), and then executes SQLPlus commands to deploy the database.

Here’s a breakdown of the script’s main steps:

1. Initialization: The script sets up variables and retrieves the content of a file called “Deploy.ctl” into the Variable. If the content is empty, the script exits with a status code 0.

2. Iteration: The script iterates over each line in the Variable.

3. Modifications: Within each iteration, the script modifies the content of the control file by appending specific lines using the Add-Content cmdlet. These lines configure settings, define variables, and specify SQL queries and commands.

4. SQLPlus Execution: After modifying the control file, the script executes SQLPlus commands using the sqlplus command-line tool. The connection details for the database are provided, including the username, password, and connection string.

5. Error Handling: The script checks the exit code of the SQL*Plus command using the $LASTEXITCODE variable. If the exit code is non-zero, indicating an error occurred during the deployment, the script writes an error message to the console, logs the error, and exits with the same exit code. If the exit code is zero, the script indicates success.

6. Cleanup: After each iteration, the script returns to the parent directory.

It’s important to note that this code is specific to your database deployment scenario and may require modifications to work in different environments. Additionally, the specific actions performed by the script, such as modifying the control file and executing SQL commands, are specific to your database deployment process.

Please ensure that you have the necessary permissions and credentials to execute the database deployment commands and that the required tools, such as SQL*Plus, are properly installed and configured on the machine where the script is executed.

Conclusion: By creation of the folder structure along with the script and prerequisites required that are explained in this blog, We can easily integrate Oracle DB with Azure DevOps.