Oracle Database Change Management (CI/CD)

With the rising popularity of DevOps, database developers must evolve to use proper change management when deploying database code across the enterprise. In this session, we will be going over how to incorporate database change management into your development lifecycle. We will take a deep dive into using Liquibase in SQLcl for change management, database migration, rollbacks, and the inner workings of the tooling.

This session will cover:
• Using SQLcl and Liquibase for Database Change Management
• A deep dive into the Liquibase commands available in SQLcl
• Data Movement with Liquibase
• Examination of the files that Liquibase exports
• A look at the change tracking tables created and the content within

VIDEO DEMO HERE:
https://www.youtube.com/watch?v=o-_mdtacxgg

Transcript

1. Database Change Management Jeff Smith Distinguished Product Manager Brian Spendolini Product Manager
2. Copyright © 2021, Oracle and/or its affiliates SQL Developer Data Modeler ORDS SQLcl Database Actions Oracle Database Developer Tools
3. 9/22/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 3 • Stateful vs Stateless • Versioning or lack there of • Roll back or roll forward? • Missing changes (manual scripting) • Testing Environments • DBAs/Database Developers are sometimes working in isolation • Possible code over-writes when multiple developers are working on the same task/object Why is Database Change Management Difficult?
4. 9/22/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 4 • Ability to capture all changes at once per developer/environment • Source Control/Code Repository • Adequate test and development databases • Tasks assigned so over-writing of code is avoided • Smaller, shorter-lived tasks • Using the Agile method or versions of it • Tracking of changes visible in the target system with ability to rollback Is Database Change Management Possible? Yes it is.
5. “If NOTHING ELSE, please start putting your database code – that’s DDL, PL/SQL, APEX Apps, REST APIs, etc into a Source Control System.” Jeff & Brian Several times a day, every day… 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 5
6. 9/22/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 6 • Standardization • Consistency • Repeatability • Accountability • Security • Find Issues Faster • Better Code • Quality Releases • More Frequent Releases Why should I even consider doing this?
7. Copyright © 2021, Oracle and/or its affiliates 7 What’s SQLcl? Oracle SQLcl (SQL Developer Command Line) is a Java-based command-line interface for Oracle Database. Using SQLcl, you can execute SQL and PL/SQL statements interactively or as as a batch file. SQLcl provides inline editing, statement completion, command recall, and also supports existing SQL*Plus scripts. SQLcl is also in the OCI Cloud Shell by default. What’s Liquibase? Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. How do they work together? We extended the Liquibase community distribution to better support Oracle Database AND baked it into SQLcl. This enables you to execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs). SQLcl handles dependency ordering AND Rollback Scenarios, FOR YOU. SQLcl and Liquibase
8. 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 8 LB GENOBJECT - Generates change logs for APEX, ORDS RESTful Service Modules, or database objects LB GENSCHEMA - Captures all the supported objects in the entire schema that the user is connected to in SQLcl. Important flags to remember: -split and –sql (new in 21.3) LB GENCONTROLFILE - Creates an empty changelog. Useful if you want to roll our own (more on this later). SQLcl and Liquibase – Important Commands SQL> lb genobject -type table -name zipcodes SQL> lb genobject -type ords –name myModule SQL> lb genobject -type apex -applicationid 101 SQL> lb genschema –split -sql SQL> lb gencontrolfile
9. 9/22/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 9 SQLcl and Liquibase – Objects Supported AQ_QUEUE AQ_QUEUE_TABLE AQ_TRANSFORM ASSOCIATION AUDIT AUDIT_OBJ CLUSTER CONSTRAINT CONTEXT DB_LINK DEFAULT_ROLE DIMENSION FGA_POLICY FUNCTION INDEX JOB LIBRARY MATERIALIZED_VIEW MATERIALIZED_VIEW_LOG OBJECT_GRANT OPERATOR PACKAGE_SPEC PACKAGE_BODY PROCEDURE PROFILE PROXY PUBLIC_SYNONYM REF_CONSTRAINT REFRESH_GROUP RESOURCE_COST RLS_CONTEXT RLS_GROUP RLS_POLICY RMGR_CONSUMER_GROUP RMGR_PLAN RMGR_PLAN_DIRECTIVE ROLE ROLLBACK_SEGMENT SEQUENCE SYNONYM TABLE TABLESPACE TRIGGER TRUSTED_DB_LINK TYPE TYPE_SPEC TYPE_BODY USER VIEW XMLSCHEMA XS_USER XS_ROLE XS_ROLESET XS_ROLE_GRANT XS_SECURITY_CLASS XS_DATA_SECURITY XS_ACL XS_ACL_PARAM XS_NAMESPACE RMGR_INTITIAL_CONSUMER_GROUP
10. Copyright © 2021, Oracle and/or its affiliates 10 SQL> lb gencontrolfile Action successfully completed please review created file controller.xml SQL> !more controller.xml <?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <include file="{filename.xml}"/> ... ... </databaseChangeLog> Liquibase and SQLcl – Roll your Own Control File
11. 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 11 LB UPDATE – Apply the updates that are in the change log file. (controller.xml, table.xml, etc) LB UPDATESQL - Generates the SQL statements to the SQLcl screen that would be applied for a specific change log file. LB ROLLBACK – Rolls back some or all of the changes in the provided changelog file SQLcl and Liquibase – Important Commands SQL> lb update –changelog controller.xml SQL> lb update –changelog my_table.xml SQL> lb updatesql -changelog my_table.xml SQL> lb updatesql -changelog data.xml SQL> lb rollback -changelog controller.xml -count
12. 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 12 LB ROLLBACKSQL – Similar to updatesql, this writes the roll back SQL to the SQLcl screen for some or all of the changes. LB DIFF - Displays differences between the current connected schema and the specified remote/local database schema. LB DBDOC - Generates Javadoc-like documentation based on the current database and change log. SQLcl and Liquibase – Important Commands SQL> lb rollbacksql -changelog controller.xml -count 100 SQL> lb diff -url localhost:1521/ORCLPDB1 -user hr -password hr SQL> lb dbdoc -changelog my_table.xml –dir ./docs
13. 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 13 ROLLBACKSQL – show me what you’re going to do, before you do it
14. 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 14 DIFF – Show me what’s different between two environments/changelogs
15. 9/22/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 15 LB CHANGELOGSYNC - Writes the SQL statements to mark all changes in the change log as executed in the database to a file. LB CLEARCHECKSUMS - Removes current checksums from the database. LB LISTLOCKS - Lists who currently has locks on the database change log. (So you can kill their sessions!) LB RELEASELOCKS - Releases all locks on the database change log. (If killing their session didn’t work) LB STATUS – Creates a list of change sets that have not been applied to the database in the supplied changelog. LB VALIDATE - Checks the supplied changelog for errors. SQLcl and Liquibase – Advanced Commands (session/locks)
16. Copyright © 2021, Oracle and/or its affiliates 16 Export the module (ORDS REST APIs are organized by Modules) And to install It’s just that simple….. SQLcl and Liquibase - ORDS SQL> lb genobject -type ords -name myModule SQL> lb update –changelog myModule.xml
17. Copyright © 2021, Oracle and/or its affiliates 17 Creates a single f101.xml file for your application Creates multiple folders/files per component and an install file (like with the database) NOTE: Liquibase will only update the new/altered files when exporting so that your repository only reflects the changes Important Flags for change management: -skipExportDate -expOriginalIds -split To deploy the single file or To deploy multiple files SQLcl and Liquibase - APEX SQL> lb genobject -type apex -applicationid 101 SQL> lb genobject -type apex -applicationid 101 –split SQL> lb update -changelog f101.xml SQL> lb update –changelog controller.xml
18. Copyright © 2021, Oracle and/or its affiliates 18 Exports the data from the indicated table(s) Imports the data into the table(s) Great for metadata tables, not great for millions of rows.. (use this thing called ORDS for that) SQLcl and Liquibase - Table Data SQL> lb data –object TABLE_NAME,TABLE_NAME... SQL> lb update -changelog data.xml
19. Copyright © 2021, Oracle and/or its affiliates 19 DATABASECHANGELOG_DETAILS is a view that consolidates information from the DATABASECHANGELOG and DATABASECHANGELOG_ACTIONS tables Liquibase and SQLcl
20. SQLcl & Liquibase in Action – SEE the YouTube Video! Also Hot-Linked in the Slide Description https://www.youtube.com/watch?v=o-_mdtacxgg Demo 9/23/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 20
21. 9/22/2021 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 21 Thank You