How To: Simulate Restore ORA Errors for Testing oracleRestoreErrorWhitelist

Below is an example of how to simulate errors in Oracle’s Datapump API. The example is to highlight how Liquibase can be configured to ignore certain ORA errors during the restore process. This demonstration is for QA purposes only and should be used on a REF database that has been setup specifically for testing.

Scenario

In this scenario we will be adding a database trigger for ‘drop on database’ that will perform a select on an invalid table. This trigger will force errors during the Packager’s restore operation. We will then whitelist the errors and Re-package to show a successful run.

To setup the scenario, add the following trigger in your REF database using the DATICAL_USER:

Initial Project Setup

1. In the ddb repo, the deployPackager.properties should be set with below:

   1.  

      databaseBackupRestoreMethod=BackupRestoreOracleDatapump databaseBackupMode=always

2. In the sql repo, the ddl folder’s metadata.properties should be set with below:

   1.  

      #Ensure that Backup & Restore will occur packageMethod=convert

Run Packager

1. Add a create table statement to the ddl folder and check-in to SCM:

   1. In this example we’re using a file called add_vehicle_reg_tbl_10.sql:

      CREATE TABLE CAPS.VEHICLE_REG_10 ( VEHICLE_ID NUMBER NOT NULL, VEHICLE_DESC VARCHAR2(100 BYTE) NULL, CREATED_BY VARCHAR2(20 BYTE) DEFAULT USER NOT NULL, CREATED_DATE DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT PK_VEHICLE_REG_10 PRIMARY KEY (VEHICLE_ID) USING INDEX TABLESPACE CAPS_TS );

2. Run the Packager process. Depending on the existing objects in your REF database, you should see the following errors during the Restore operation in the daticaldb.log:

   1.  

      10:18:55.007 [main] DEBUG TraceFile - ----------------stack trace---------------- ... Starting "DATICAL_USER"."datical_imp_20210115_102010_90": Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"CAPS" already exists ORA-31684: Object type USER:"CACCORE" already exists ORA-31684: Object type USER:"DATICALDBTRACKING" already exists ... Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-39151: Table "CACCORE"."MOVIECATEGORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "CAPS"."CATEGORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ... Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39111: Dependent object type ALTER_PROCEDURE:"CAPS"."NEW_PROC_002" skipped, base object type PROCEDURE:"CAPS"."NEW_PROC_002" already exists ORA-39111: Dependent object type ALTER_PROCEDURE:"CAPS"."PROCEDURE1" skipped, base object type PROCEDURE:"CAPS"."PROCEDURE1" already exists ... Job "DATICAL_USER"."datical_imp_20210115_102010_90" completed with 149 error(s) at Fri Jan 15 10:20:13 2021 elapsed 0 00:00:02 stdout: ----------------------------- end of datapump import output --------------------------------- 10:21:17.144 [main] DEBUG BackupRestoreOracleDatapump - Found Oracle error on line ORA-39151: Table "CACCORE"."MOVIECATEGORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Whitelist the ORA errors and Re-run Packager

1. To whitelist the errors, in the ddb repo, add the following to deployPackager.properties:

   1.  

      oracleRestoreErrorWhitelist=ORA-31684,ORA-39151,ORA-39111

2. Because of the previous Backup and Restore error, you may also need to remove any .lock files that have been added to the ddb repo.

3. Check-in the above changes to SCM.

4. Re-run Packager process.

5. We should now see Successful job completion.

   1. Please remember to also drop your trigger from this Scenario so it does not impact your future testing efforts.

Packager Logs from Demonstration

Video

Open

Documentation

• Liquibase Enterprise v7.3 - Release NotesPreview

• Use the Required deployPackager.properties FilePreview