Skip to content

02.-Build-The-Main-Branch.md

Latest commit

 

History

History
266 lines (138 loc) · 7.84 KB

02.-Build-The-Main-Branch.md

File metadata and controls

266 lines (138 loc) · 7.84 KB

The Main branch ( at this point, i.e., BEFORE we start deploying any changes generated by SQLcl Project) will represent the current state of the Production database .

It’s very important to capture this state, because all changes can only be generated if SQLcl Project (SP) detects a difference between a feature branch and main.

Here is a list of tasks you need to perform to accomplish this critical step:

1. Create a new repository

  • Checkout the main branch,
  • Connect to Prod,
  • And init Project

Note: Some steps here will mention scripts located in scripts folder of the repository, this folder is assumed to exist before this step. The folder content is available in this repository and shall be copied into the project repo.

sql /nolog
conn -name prod_apex
project init -name CLA -schemas cla_internal,cla_public,cla_utilities

2. Set up Schemas in .dbtools/project.config.json

{
  "project" : "CLA",
  "sqlcl" : {
    "connectionName" : "proj_test",
    "version" : "25.3.0.0"
  },
  "schemas" : [ "CLA_APEX", "CLA_PUBLIC", "CLA_UTILITIES" ],

3. Set up filters in .dbtools/filters/project.filters as follows.

-- Liquibase Tables
object_type != 'TABLE' or object_name not in ('DATABASECHANGELOG',
                                              'DATABASECHANGELOGLOCK',
                                              'DATABASECHANGELOG_ACTIONS'
                                              ),
not (object_type = 'VIEW'    and object_name ='DATABASECHANGELOG_DETAILS'),
not (object_type = 'TRIGGER' and object_name ='DATABASECHANGELOG_ACTIONS_TRG'),


-- DM generated tables
not (object_type = 'TABLE' and object_name like 'DM$%' ),
not (object_type = 'VIEW' and object_name like 'DM$V%' ),
object_name not like 'DM$%',      -- covers tables, views, indexes 
object_name not like 'I_MLOG$%',      -- covers Materialized Views Logs Indexes
object_name not like 'I_SNAP$%',
not (object_type = 'TABLE' and object_name like '%\_ERR$%' escape '\'),  -- Data Pump error tables,

--- grants to non-owner users
( grantee like 'CLA_%' or grantee = 'PUBLIC' ),

--- Exclude/Include certain export types:

-- Exclude certain database object types:
--object_type not in ('CONTEXT', 'SCHEDULE'),         

-- Export APEX application from Prod group:
APEX_APPLICATIONS.application_group ='Prod',

-- More APEX filters:
APEX_APPLICATIONS.is_working_copy = 'No', -- Yes/No
APEX_APPLICATIONS.main_application_id is null, -- if this has a value then it's WC
------------------------------^ The final trailing comma is optional

4. Commit and switch to a new branch called Prod that we will use to build and stage the baseline.

git add .
git commit -m 'filters'
git checkout -b prod

At this point, you have a main branch which has “project infrastructure” but no real project metadata and you started a new branch that will be used to build the metadata.

5. Export all schemas from Production

Note: Ideally cla_deployer connection should be used for this task, however, schema connections are also acceptable. With schema connections, the most attention should be in sys area, where they will overwrite each other: ORDS medatadata can only be exported today with schema connections.

sql /nolog
conn -name prod_apex
project export -schemas cla_apex

! git add .

conn -name prod_pub
project export -schemas cla_public

6. Export Roles, Grants, and Privileges

Currently Project does not do a good job with exporting roles , sys privs, and grants to SYS objects. You will note some conflicts in src/database/sys area that need to be resolved manually. These are basically bugs in SQLcl , and I’ll skip their descripotion for now, hopefully they will be fixed soon. For details, you may review a change history of src/database/sys/object_grants/user_role_privs.sql

7. Commit all changes in git and run the stage command. You will see a dist folder generated

--- In SQLcl: 
! git add .
! git commit -m 'gen dist'
conn -name proj_prod
proj stage -v

8. Deploy to the VM

The next step is the most important and the least intuitive. You will attempt to iteratively deploy your changes into VM database.

First, use "reset" script to fully wipe-out VM database from any previous installation efforts:

conn -name vm_sys
@scripts/sql/deployment/vm_reset.sql

This script drops all project schemas and workspaces and resets CLA_DFEPLOYER user.

Now connect as CLA_DEPLOYER user and start the deployment.

conn -name proj_vm
cd dist
@install

When you start running most likely you will get multiple errors. You will need to work through all these errors until you have a clean deployment.

SQL> lb update -log -changelog-file releases/main.changelog.xml -search-path "."
--Starting Liquibase at 2025-10-29T11:25:26.152057400 using Java 17.0.13 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)

<skipped>

Running Changeset: dev-01/sys/object_grants/user_role_privs.sql::1761243381929::SYS
Migration failed, error reported:
SQL> -- sqlcl_snapshot src/database/sys/object_grants/user_role_privs.sql:97c0a7fefbd62932b29326c377eb73518135830c:2e5a2c2f8d7ab4b8f6a177ccdb4a3a507c473023:alter
SQL>
SQL> grant apex_administrator_read_role to cla_utilities;

Error starting at line : 11 File @ C:\repo\cla-project\dist\install.sql
In command -
grant connect to cla_public
Error report -
ORA-01917: user or role 'CLA_PUBLIC' does not exist

You may:

  • Add/Remove/Edit files in dist folder

  • Add grants to CLA_DEPLOYER and update scripts/sql/deployment/create_cla_deployer.sql

  • Reorder changeset inside dist/releases/next/changes/prod/stage.changelog.xml

Note: Please note that it’s NOT practical to do deployment using project gen-artifact and project deploy every time to change a line in a SQL file . You should be rerunning sctipt dist\install.sql over and over again until you have a clean deployment

For example, on of the errors that pops up right away is tablespace quotas were not generated by the Project , and the following custom file was added:

proj stage add-custom -filename ts_quota.sql

with this content:

-- liquibase formatted sql
-- changeset  SqlCl:1761070751076 stripComments:false logicalFilePath:prod\_custom\ts_quota.sql
-- sqlcl_snapshot dist\releases\next\changes\prod\_custom\ts_quota.sql:null:null:custom

ALTER USER CLA_APEX QUOTA UNLIMITED ON USERS;
ALTER USER CLA_PUBLIC QUOTA UNLIMITED ON USERS;

9. Final Thoughts on VM Build

Unfortunately, even after all the changes run sucessfully, there may be compilation errors. Do everything possible to get rid of sll of them. If you cannot, makes sure you know all of them, so you can see that subsequent deployments don't add new ones.

  • Note : There are some bugs with compiling wrapped packages ( that looks like a bug with SQLcl ) they need to be recompiled manually