Use a cleanup script to reverse the effects of a deployed changeset. 

Sometimes database changes (changesets) need to be abandoned. This can occur if a change fails testing in a later stage environment. The work required to get the database back to the desired state may be simple or complex:

• Simple - only the changes introduced by the original SQL script need to be undone. In this case, a -cleanup script can be applied to undo the changes introduced by the original script.  When a -cleanup script is supplied, it will be deployed to eligible environments where the original script has already been deployed.  Neither the original SQL script nor the cleanup script will be deployed to environments where the original SQL script has yet to be deployed.
  
  

  Note

  If you are using artifacts for deployment, a new artifact must be created after packaging the -cleanup script to effect this behavior.

• Complex - other subsequent changes have been deployed that interact with the changes in the original script. In this case, it is recommended to introduce a brand new script (with a different filename) to correct the original SQL script.  Note that when doing so, the original script and the new script will both be deployed in all eligible environments.   

The cleanup capability allows you to deploy a changeset that is specially marked for undoing prior changes.  During processing, the original changeset gets the attribute ignore=true to prevent it from being deployed further. 

Comparison to Rollback Scripts

While a cleanup script and a rollback script may contain identical SQL, a cleanup script behaves differently than a rollback script.

• When you roll back changesets, they are still eligible for deployment in the pipeline. 
• When a cleanup script is provided for a script, the original script is marked as ignored so that it is no longer deployed. 

If you have written a custom rollback script for a change, it may be appropriate to use the contents of the custom rollback script in a cleanup script. 

See Rolling Back Changes. 

Pairing a Cleanup Script with a Replace Script

If you need to replace the original script as well as clean up its original effects, provide a -replace script as well. 

A cleanup script and a replace script can be packaged in the same packaging run. 

If you update a replace script but do not update the accompanying cleanup script, the previous version of the cleanup script is pulled forward automatically. 

See Replacing Deployed Changesets: replace. 

Types of Change Eligible for Cleanup Scripts

Cleanup scripts work for different types "non-rerunnable" changes:

• DDL folder or packageMethod=CONVERT
• DDL_DIRECT folder or packageMethod=DDL_DIRECT
• DATA_DML folder or packageMethod=DATA_DML
• SQL_DIRECT folder or packageMethod=DIRECT
• SQL folder or packageMethod=SQLFILE
• Liquibase changelogs

Cleanup and/or Replace are NOT recommended for stored logic (code: package, packagebody, procedure, function, trigger, view folders). Use the provided versioning capability with stored logic instead.

Cleaning Up After a Change

For a file named mychanges.sql, create a cleanup file that contains all of the needed changes. Use one of the following forms:

mychanges-cleanup.sql

mychanges_cleanup.sql 

The file may use any extension (e.g. .sql) or no extension. 

Re-Using a Cleanup Script

If you need to change a replace script but do not need to change its associated cleanup script, you can package just the updated replace script. The associated cleanup script is pulled forward.   

Processing

Deployment Packager processes the files during packaging, as follows:

1. Searches the changelog for matching changesets based on filename. Packager will error if there are no matching changesets in the changelog that were previously packaged with the same filename in the same path relative to the root folder. Changesets must match these attributes:
   • origFilePath
   • origFileName
2. Sets ignore=true attribute on all matching changesets (same orgFilePath and origFileName) that pertain to the most recent / highest version 
3. Produces new changeset(s) and positions them in the changelog.immediately following the most recent changeset(s) 
   1. Applies a datical:cleanup preCondition to the resulting changeset(s), which will prevent it from deploying to environments where the most recent version has not been deployed.
4. Archives the cleanup file as appropriate.