Shadow Database (or Shadow Schema)
Published 31 October 2019
Deployment Suite for Oracle has been replaced by Redgate Deploy - Oracle. This page is therefore no longer being updated. For the latest documentation, please click here.
The shadow database is a separate database that is needed in development only. When you go to the Generate migrations tab in Redgate Change Control, we run the scripts in the project against the shadow database. Then, we use the Redgate Schema Compare for Oracle technology to compare the development database to the shadow database. This allows us to show you what changes have been made to your development database that have not been imported into the project yet.
Because we run all the migration scripts in your project on the shadow database/schema, this also helps you shift-left and identify any problems in your migration scripts as early as possible during development.
You should never have to make changes to your shadow database/schema. You also don't need to baseline your shadow database/schema. It will be cleaned (meaning all objects will be dropped) as required when you visit the Generate migrations tab, so make sure that you are not using a database/schema that contains important data.
When setting the shadow database/schema connection, ensure it isn't an important database/schema as this may be cleaned and rebuilt when generating migration scripts in Redgate Change Control.
The Shadow Database/Schema only gets updated with the migration scripts in your project, so the size should be small since there shouldn't be any transactional data in this database. It will just contain the schema objects and reference data from your migration scripts and any data updates ran on these. If you have a transactional data update in a migration script in your project, it won't do anything on the shadow since the transactional tables are empty.
You need to create the Shadow Database/Schema prior to creating a Redgate Change Control project so you can provide us the connection details for this database. There are a few options to use for the Shadow Database:
- Pluggable Database
If you are using Oracle 12c and above, the easiest way might be to use another pluggable database on your development instance.
- Oracle Express Edition (XE)
Oracle XE is a free version of Oracle that can be used for development purposes. There are some size restrictions, but because the shadow database doesn't contain any transactional data, this should be ok.
If you're familiar with docker/containers, you could spin up an Oracle database in a container to use for the shadow. Since there is no transactional data, no persistence is required and therefore a transient container should just work.
- Create another Oracle instance for this.
- A separate schema within the same Oracle database
Using a Shadow Schema instead of a shadow database is currently an experimental feature
We have added an experimental feature that enables the use of a Shadow Schema rather than a separate shadow database. This feature can only be used for projects that are tracking changes to a single schema. If your project contains multiple schemas then a separate shadow database is required so we maintain the schema name in object identifiers. In single schema mode, all migration scripts are created without the schema name in object identifiers so that we can deploy the same script to both the development and shadow schemas.
To enable this mode, after creating a project, add the following to your project .conf file and re-open it
There are a couple of caveats when writing migration scripts in this single-schema mode:
- Any references to objects in the managed schema must not be schema-qualified. Since the migration scripts will run against different schemas with different names, managed objects must always be created in the current default schema.
- Any references to objects in other schemas must be schema-qualified. You'll be responsible for making sure any dependencies of the managed schema are present in the database as appropriate.
CREATE TABLE log ( message VARCHAR2(50), timestamp INTEGER ); -- this object references log in the same schema, so log must not be schema-qualified CREATE OR REPLACE TRIGGER SetTimestampForLog BEFORE INSERT ON log FOR EACH ROW DECLARE v_today DATE; BEGIN SELECT CURRENT_DATE INTO v_today FROM DUAL; -- date2epoch is in a different schema, so must be schema-qualified :new.timestamp := util.date2epoch(v_today); END;
- We're also wondering if we should provide a way to spin up database clones on the fly for this purpose.
If you'd like to talk about these options more or have any questions about the Shadow Database/Schema, please reach out to the development team.
Why is a shadow database/schema required?
When is the shadow cleaned?
The shadow database/schema gets automatically updated from the migration scripts in your project when you visit/refresh the Generate migrations tab. This update involves potentially cleaning the database (i.e. dropping all objects) prior to updating it with the migration scripts depending upon the current state of the shadow.
If the project uses a database as the development source, and
- the schema history table in development contains no scripts, then the shadow will be cleaned.
- the schema history table in development contains only repeatable migration scripts, then the shadow will be cleaned before being migrated with the repeatable migration scripts.
- the shadow version is ahead of the development version, or the schema history table in development contains Missing or Future migrations (see Migration scripts), the shadow will be cleaned and then migrated up to the current development version.
Otherwise, the shadow will not be cleaned. Only the pending migration scripts will be applied to have it reflect the end state of the migration scripts in the project.
If the project uses a Source Control for Oracle project as the development source (i.e. Hybrid mode), then the shadow will be cleaned only if the schema history table in the shadow contains Missing or Future migrations. The shadow will be migrated using all the scripts in the current project.