Skip to content

06.-Mitigating-Production-Drift.md

Latest commit

 

History

History
406 lines (298 loc) · 13.9 KB

06.-Mitigating-Production-Drift.md

File metadata and controls

406 lines (298 loc) · 13.9 KB

Production Drift is very similar to Hot Fix deployment, but unlike Hot Fix where production changes are well known, The drift includes procedures outlined to identify the changes.

In loosely controlled environments, drift analysis may be recommended before any push to production

1. Create Drift Branch

Note: As drifts are long standing branches, it's highly recommended to handle them using git worktree feature. Please note that project stage does not work in worktrees, which is OK for our purpose. After drift changes are detected use normal branches to handle them.

  • If this is a first drift, start with creating a drift branch and the worktree. This will be a long-living branch that would be never merged into main.
  • If this is not your first drift for this repo, go to section 2.
git checkout main 
git pull
git worktree add ../drift-test -b drift-test

Open new VS Code window on ../drift-test folder.

  • Next, configure filters that support drift detection. The filters should exclude all objects you don't expect (for example, ORDS schemas, user definitions, system privileges, etc.)

For example, filters in .dbtools/filters/project.filters may look like the following:

export_type not in ('USER','USER_SYS_PRIVS','USER_ROLE_PRIVS' ),
export_type not in ('ORDS_SCHEMA'),
APEX_APPLICATIONS.application_group ='Prod',

Skip the next section and go to section 3

2. Refresh Drift Branch

If you already had a drift switch into the drift branch and rebase it from main. Open the worktree and run

git merge main

If you have merge conflicts while merging main into drift, you will see the following output:

$ git merge main
Auto-merging .dbtools/filters/project.filters
Auto-merging src/database/cla_apex/apex_apps/f104/f104.sql
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f104/f104.sql
Auto-merging src/database/cla_apex/apex_apps/f110/f110.sql
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f110/f110.sql
Auto-merging src/database/cla_apex/apex_apps/f110/readable/application/f110.yaml
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f110/readable/application/f110.yaml
Auto-merging src/database/cla_apex/apex_apps/f110/readable/workspace/app_groups.yaml
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f110/readable/workspace/app_groups.yaml
Auto-merging src/database/cla_apex/apex_apps/f116/f116.sql
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f116/f116.sql
Auto-merging src/database/cla_apex/apex_apps/f116/readable/application/f116.yaml
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f116/readable/application/f116.yaml
Auto-merging src/database/cla_apex/apex_apps/f202/f202.sql
CONFLICT (content): Merge conflict in src/database/cla_apex/apex_apps/f202/f202.sql
  • In this case you need to accept all changes from main for src and keep drift filter
git checkout --theirs -- src/
git checkout --ours -- .dbtools/filters/project.filters
git add .
git commit -m 'ready for drift detection Nov 27, 2025'

3. Delete All

  • Now delete all files under src/ in the working tree that you expect to be regenerated by export:
rm -fR src/database/cla_apex src/database/cla_public/ src/database/cla_utilities/

4. Export

  • Connect to prod in SQLcl and export everything according to the filters: ( Use 10 threads)
conn -name proj_prod
proj export -t 10 -v
  • To avoid any issues due to EOL-conversion stage the changes.

Also you should stage the changes to avoid differences between tracked and untracked files.

git add .

5. Revert Noise

Note: The steps below are encapsulated in a script scripts/bash/tools/drift_cleanup.sh. In most cases just run the script

scripts/bash/tools/drift_cleanup.sh

Revert changes that you cannot analyse , for example apex apps sql files or changes you expect to have for techncial reasons

5.1. ORDS, USERS, and APEX SQL Files

git restore --staged --worktree -- src/database/*/ords/ords.sql
git restore --staged --worktree -- src/database/*/users/*.sql"
git restore --staged --worktree -- src/database/*/apex_apps/f*/f*.sql
git restore  .

5.2. Whitespace

Revert white-space-only changes

for f in $(git diff --cached --name-only); do
  if ! git diff --cached -w -- "$f" | grep -q '^diff'; then
    echo "Restoring $f"
    git restore --staged --worktree -- "$f"
  fi
done

5.3. Whitespace and Empty Lines

Revert whitespace changes that include empty lines

for f in $(git diff --cached --name-only); do
  # --cached  = compare index vs HEAD
  # -w        = ignore all whitespace in line comparison
  # -I'^[[:space:]]*$' = ignore hunks where all changed lines are whitespace-only (incl. empty)
  # --quiet   = no output, just exit code (0 = no non-whitespace diff)
  if git diff --cached -w -I'^[[:space:]]*$' --quiet -- "$f"; then
    echo "Restoring $f"
    git restore --staged --worktree -- "$f"
  fi
done

5.4. Snapshots

  • Revert files only differernt in snapshot
for f in $(git diff --cached --name-only); do
  # Ignore hunks where ALL changed lines match ^-- sqlcl_snapshot
  if git diff --cached -I'^-- sqlcl_snapshot' --quiet -- "$f"; then
    echo "Restoring $f"
    git restore --staged --worktree -- "$f"
  fi
done

5.5. Columns Order

  • Revert files that only different in snapshot, empty lines, and order of columns
normalize() {   sed '/^-- sqlcl_snapshot/d' "$1" |        # remove snapshot line
  sed 's/[[:space:]]\+$//g'         |        # strip trailing whitespace
  sed '/^$/d'                        |        # drop empty lines
  sed 's/\([^,]\)$/\1,\n/'            |        # add comma if missing
  #tr -d '[:space:]'                 |        # remove remaining spaces/tabs
  sort                              |        # canonicalize order
  uniq                               # dedupe if SQLcl duplicated entries
}

for f in $(git diff --cached --name-only); do
  # write staged version to temp
  git show :$f > /tmp/staged.txt 2>/dev/null || continue
  # write HEAD version to temp
  git show HEAD:$f > /tmp/head.txt 2>/dev/null || continue

  if diff -q <(normalize /tmp/head.txt) <(normalize /tmp/staged.txt) >/dev/null; then
    echo "RESTORE (only ordering/snapshot changed): $f"
    git restore --staged --worktree -- "$f"
  fi
done

5.6. APEX Yaml

  • Version-Number
for f in $(git diff --cached --name-only -- 'src/database/*/apex_apps/*/readable/*'); do
  if git diff --cached -I'version-number:\s*[0-9]+' --quiet -- "$f"; then
    echo "Restoring version-number-only change: $f"
    git restore --staged --worktree -- "$f"
  fi
done
  • And id
for f in $(git diff --cached --name-only -- 'src/database/*/apex_apps/*/readable/*'); do
  if git diff --cached -I'id:\s*[0-9]+' --quiet -- "$f"; then
    echo "Restoring version-number-only change: $f"
    git restore --staged --worktree -- "$f"
  fi
done

6. Review Real Drift

Go over remaining diffs in VS code ; identify and address real differences. Document required changes. There can be these outcomes:

6.1. Create a Hotfix

Legitimate production changes that have never been reported should be identified, organized in a hotfix and dealt with as described in 5. Hotfix

6.2. Update Main

If changes correspond to changes in production database that are already done, but not requiring hotfix, for example, deleting old or temporary objects from main, create a branch, make these changes and request merging into main.

6.3. When Main is AHead of Production

Ignore changes resulting from features merged into main after testing and before deployment to prod. Presense of these changes indicates what would be changed by the next deployment.

7. Revert all changes and leave the branch

Note - don't do it for worktree branches!

At the end of drift detection always revert all changes in drift branch done by the latest project export

git restore --staged --worktree -- .

8. Example: Remove Garbage from Production

Problem Statement:

  • As a result of drift detection between production and test, several objects were identified as "garbage in production".
  • DBA after that removed the the objects from Production
  • However, the objects remain in both src and dist folders of the repo, which means they may exist in other environments, and they will be created in all new environments.

Follow the steps below to rectify this issue.

8.1. Run Drift in Production

Run as described above and verify you see the objects deleted. You confirm in VS code that expected objects were deleted from CLA_PUBLIC schema

8.2. List Objects for Scripting

Run git diff:

git diff --cached --name-only --diff-filter=D -- 'src/database/cla_public/'

You may get a list like this:

src/database/cla_public/ref_constraints/emp.cla_public.dept.sql
src/database/cla_public/ref_constraints/emp.cla_public.emp.sql
src/database/cla_public/sequences/dept_seq.sql
src/database/cla_public/sequences/emp_seq.sql
src/database/cla_public/tables/dept.sql
src/database/cla_public/tables/emp.sql
src/database/cla_public/tables/tmp_clob.sql
src/database/cla_public/triggers/dept_trg1.sql
src/database/cla_public/triggers/emp_trg1.sq

Put it into a tmpfile

git diff --cached --name-only --diff-filter=D -- 'src/database/cla_public/' > /tmp/deleted.txt

8.3. Create a hotfix branch

Go to main repo, NOT worktree and create a hotfix branch

git checkout main
git checkout -b 'hotfix-remove-prod-garbage'

8.4. Remove files in the hotfix

cat /tmp/deleted.txt |xargs -i rm {}
git add .
git commit -m 'remove garbage'

8.5. Stage Changes in SQLcl

conn -name proj_prod
project stage
--- remove fake ords changes 
prj_rm_ords

8.5. Manually change generated drop changesets in dist

Please note:

  • SQLcl project generates commented drop statements, you need to uncomment one that you need to drop
  • As always a case with garbage, it may not be present in all environmnets, so you DROP IF EXISTS synatax if available detect object existance yoursellf
  • Not all generated statements need to be run. For example, if you drop a table , there is no need to drop a comment or constraint.

For example, a changeset that drops a table EMP may look like the following:

-- liquibase formatted sql
-- changeset CLA_PUBLIC:1764786725672 stripComments:false  logicalFilePath:hotfix-remove-prod-garbage\cla_public\tables\emp.sql
-- sqlcl_snapshot src/database/cla_public/tables/emp.sql:38aac4b05748d0f13ceb64c7e69437402054472e:null:drop

DROP TABLE if exists "CLA_PUBLIC"."EMP" CASCADE CONSTRAINTS;

8.6. Test and commit changes, crete PR, push for review

  • Test changes againts VM
conn -name proj_vm
prj_install
  • Commit , push, create PM and apply for approval
  • Get PR merged into main
  • After approval run in prod or, better, sync with prod
conn -name proj_prod
prj_status
prj_sync

At this point the changes are tested, merged into main, and implemeted in VM and Prod

8.7. Propagate the changes to the worktrees

Navigate to all DRIFT-worktress and in each run

 git merge main

If the changes are comming from remote, run instead:

git pull origin main

If you have conflict with uncommited files, you will get a message like one below, and should be able to resolve the issue:

 git merge main
Updating 37f79fd..379df0e
error: Your local changes to the following files would be overwritten by merge:
        .dbtools/filters/project.filters
        scripts/bash/tools/drift_cleanup.sh
Please commit your changes or stash them before you merge.
Aborting

As a last step, run prj_install in the worktreee to register the changes with LB.

Now, your garbage removal is completed.

  • We detected garbage during drift
  • DBA removed it for us in Prod
  • We scripted the removal in the changesets, which guarantees removal of unneeded objects in all current and new environments
  • We applied these changesets to all current environments