Oracle Database Change Management (CI/CD)

by thatjeffsmith

Slide 1

Slide 1 text

Database Change Management Jeff Smith Distinguished Product Manager Brian Spendolini Product Manager

Slide 2

Slide 2 text

Slide 3

Slide 3 text

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?

Slide 4

Slide 4 text

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.

Slide 5

Slide 5 text

“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

Slide 6

Slide 6 text

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?

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

Copyright © 2021, Oracle and/or its affiliates 10 SQL> lb gencontrolfile Action successfully completed please review created file controller.xml SQL> !more controller.xml ... ... Liquibase and SQLcl – Roll your Own Control File

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

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)

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

Slide 20

Slide 20 text

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