Slide 1
Slide 1 text
Versioning your Oracle Database
Jeff Smith, Master Product Manager
With SQLcl and Liquibase
1
Slide 2
Slide 2 text
• Your application schema(s)
• Tables
• Views
• Stored Procedures
• Everything in a schema!
Versioning what, exactly?
Slide 3
Slide 3 text
v3..vN
Upgrade v2
Deploy v1
Back to v2 ??? Rollback!
Database Deployments & Upgrades
Slide 4
Slide 4 text
• Home grown solution?
• Bespoke scripts?
• Liquibase | Flyway?
• Something else?
How are you handling upgrades today?
Slide 5
Slide 5 text
• Less coding
• Familiar, friendly interface
• Database object dependency ordering is taken care of
• Updates automatically generate rollback scenarios
Our solution: leverage Liquibase + EASY
For FREE
Slide 6
Slide 6 text
• Open source project
• Deploys versions of your database schema/user
• Supports popular DBMS solutions, including Oracle
• Installs, upgrades, or rollbacks
Liquibase – Source Control for your DB
Slide 7
Slide 7 text
• XML
• JSON
• YAML
• SQL
Liquibase - changesets
Slide 8
Slide 8 text
Controller/master => many changelogs
Slide 9
Slide 9 text
“Liquibase uses the DATABASECHANGELOG table to track which changeSets have been ran.”
DATABASECHANGELOG TABLE
Slide 10
Slide 10 text
• Executable via command line,
• Apache Ant,
• Apache Maven,
• servlet container,
• Spring Framework.
Liquibase – How it runs
Slide 11
Slide 11 text
• …YOU code the upgrade AND rollback changelogs
• YOU worry about the order of changelogs to be applied
A lot of power, but…
Slide 12
Slide 12 text
SQL Developer Family of Solutions
1
2
Time Tested
Launched in 2005
An Industry Standard
5M+ Oracle DBAs
& Developers
Always Improving
Quarterly
Releases
4
Slide 13
Slide 13 text
• Replaced the Oracle library for Liquibase
• Mounted it as ‘lb’ command in SQLcl
What we did
SQLcl: a modern CLI for your Oracle Database
Slide 14
Slide 14 text
• 25MB download, only requires a JRE
• Supports SQL*Plus commands, adds many more
• 11gR2 & higher
• Avail in OCI YUM, OTN,
DB $ORCLE_HOMEs
SQLcl: manage & query your Oracle DB
Slide 15
Slide 15 text
• Leverages DBMS_METADATA to gen XML changelogs
• Operates as connected USER, writes to $SQL_PATH
• Parses XML, determines object creation order
How it works
Slide 16
Slide 16 text
• GEN schema/object as LB changelogs
• Automatically sorted for creation
• Easily rollback to previous state
• Preview UPDATE SQL
SQLcl and the LB command
Slide 17
Slide 17 text
Help & Docs Examples
Slide 18
Slide 18 text
1. Create a directory to hold v1 changelogs
2. Login as app schema in DEV
3. >lb genschema
4. Login as app schema in PROD/TEST
5. >lb update false
Generate and Update
Slide 19
Slide 19 text
From DDL to Changelog…
Bespoke Upgrade Script
Slide 20
Slide 20 text
Generating changelog for our object
Preview the DB work to be applied via ‘lb updatesql’ command
Slide 21
Slide 21 text
Deploy a changelog
Slide 22
Slide 22 text
• We handle the code
for rollback
• DESC > INFO
for terse screenshots ☺
Oops…ROLLBACK!
Slide 23
Slide 23 text
• GEN APEX
• NUKE IT
• LB UPDATE
• Try running an APP
In-house scenarios (ORDS + APEX)
Spoiler: It worked.
Slide 24
Slide 24 text
Already use Liquibase? Just add our Jar
Add to your LB classpath
Slide 25
Slide 25 text
Bonus: GEN APEX Apps and REST Services
Slide 26
Slide 26 text
• Shuffle in changelogs at the page level vs application level
• Granular updates vs completely rebuilding an App
DEEP APEX Support Available
Slide 27
Slide 27 text
• LB command added
for version 19.2.1 of SQLcl
• Follow @oraclesqlcl for
product news and updates
Availability - NOW
Slide 28
Slide 28 text
Live Demo: The Movie!
YouTube 5 Minute Demo
Slide 29
Slide 29 text
Thank you!
Master Product Manager
@thatjeffsmith
[email protected]
https://www.thatjeffsmith
Jeff Smith