Managed Exceptions

Using a migration Group in the event of an exception

A common requirement might be to recover the target to the original state that existed BEFORE the migration had begun. You can achieve this by defining an external context ( i_external_context_controller param ), and combining that with the i_exception_group param.

BEGIN
   migration_run_framework.launch(i_batch                        => 1
                                 ,i_migration_group              => 'DEMO'
                                 ,i_exception_group              => 'DO_THIS_ON_FAILURE'
                                 ,i_context                      => 'DEMO'
                                 ,i_start_at_step                => 1
                                 ,i_concurrency                  => 8
                                 ,i_max_rows_per_thread          => 500
                                 ,i_suppress_emails_bool         => TRUE
                                 ,i_halt_on_error_bool           => TRUE
                                 ,i_master_monitors_monitor_bool => TRUE
                                 ,i_external_context_controller  => 'TRUNC_TARGET=TRUE;'||
                                                                    'JOB=SALESMAN;'||
                                                                    'BACKUP_EXISTING_TARGET_DATA=TRUE');
END;

Then you should define an early +Process step to do something like the following (I have used an external controller called BACKUP_EXISTING_TARGET_DATA) - I then go on to set up another context called MIG_BACKUP_COMPLETED.

The MIG_BACKUP_COMPLETED context will be useful to know if the backup had originally succeeded.

This pattern is very useful if your migration can take a long time (for example overnight) - your organisation may have a need to have at least some functional data available (even if its not the most recently migrated data) - so you might want to automatically recover the last known "good data" so you do not hold up other work that needs to occur on that database.

Step 1: Set up an early +Process to capture the original schema tables (IMPORTANT NOTE : ONLY THE TABLES DEFINED AS (Table Document) in the exodus tool will be backed up. NOT ALL TABLES in the target schema - if you want ALL tables then they will ALL need to be included into the (Table Document) type tables in the Exodus Tool) NOTE : The example below assumes you have an i_external_context_controller (parameter) set to something like BACKUP_EXISTING_TARGET_DATA=TRUE (i.e. i_external_context_controller => 'BACKUP_EXISTING_TARGET_DATA=TRUE')

BEGIN
   IF pkg_pre_etl_tools.fn_get_context_value(i_attr => 'BACKUP_EXISTING_TARGET_DATA') = 'TRUE' THEN
     user_backup_restore_steps.pr_backup_schema(i_schema_name    => '${TARGET_SCHEMA}'
                                               ,i_context        => '${RUN_CONTEXT}'
                                               ,i_overwrite_bool => FALSE);

     -- We need to know if the backup completed in case we need to run restore.
     -- We DON'T want to run the restore if the backup didn't complete.
     pkg_pre_etl_tools.pr_set_context(i_attr  => 'MIG_BACKUP_COMPLETED'
                                     ,i_value => 'Y');
  END IF;
END;

Step 2: Create a Migration +Process step late in the migration (As the very last step or after the main body of the migration). This will clean up any backups because they can take up quite a bit of space,

DECLARE
   l_backup_status VARCHAR2(1) := nvl(pkg_pre_etl_tools.fn_get_context_value
                                        (i_attr => 'MIG_BACKUP_COMPLETED'
                                        ,i_fail_if_no_run_context_yn => 'Y')
                                     ,'N');
BEGIN
   IF l_backup_status = 'Y'
   THEN
      -- Set to cleanup mode (we don't want to fail here and recover after all our hard work.
      pkg_pre_etl_tools.pr_set_context(i_attr  => 'MIG_BACKUP_COMPLETED'
                                      ,i_value => 'C');
      user_backup_restore_steps.pr_remove_backups(i_schema_name => '${TARGET_SCHEMA}');
      --
      migration_run_framework.pr_log(i_log_type  => migration_run_framework.fn_info
                                    ,i_log_entry => 'BACKUPS OF ORIGINAL TARGET DATA CLEANED UP'
                                    ,i_log_batch => :batch);
   END IF;
END;

Step 3: Create a Migration Group (this needs to be done manually (once) - you may need to use the R button (refresh button in the main window) to see this if the Exodus interface is already running).

INSERT INTO pre_etl_migration_groups
   (group_name
   ,group_title
   ,group_description
   ,display_order)
VALUES
   ('EXCEPTION GROUP'
   ,'Exception Group'
   ,'Exception Group To Run In The Event Of Unexpected Failure'
   ,900)
/


COMMIT
/

Step 4:

In the NEW EXCEPTION GROUP (that was created in Step 3) - in this case its a Migration Group of 'DO_THIS_ON_FAILURE' (see the launch of the migration at the top of this page)

Create a RESTORE TARGETS (+Process Step) similar to the following:

DECLARE 
   l_backup_status VARCHAR2(1)
                   := nvl(pkg_pre_etl_tools.fn_get_context_value(i_attr => 'MIG_BACKUP_COMPLETED'
                                                                ,i_fail_if_no_run_context_yn => 'Y')
                          ,'N');
BEGIN
   IF l_backup_status = 'Y'
   THEN
      user_backup_restore_steps.pr_restore_schema(i_schema_name => '${TARGET_SCHEMA}'
                                                 ,i_context     => '${RUN_CONTEXT}');

      COMMIT;
      -- If we have successfully restored we can get rid of the backups.
      user_backup_restore_steps.pr_remove_backups(i_schema_name => '${TARGET_SCHEMA}');

      --
      migration_run_framework.pr_log(i_log_type  => migration_run_framework.fn_info
                                    ,i_log_entry => 'ORIGINAL TARGET DATA RESTORED'
                                    ,i_log_batch => :batch);
   ELSIF l_backup_status = 'C' THEN
      migration_run_framework.pr_log(i_log_type  => migration_run_framework.fn_warning
                                    ,i_log_entry => 'DID NOT RESTORE BECAUSE BACKUP WAS CLEANED UP'
                                    ,i_log_batch => :batch);
   ELSE
      migration_run_framework.pr_log(i_log_type  => migration_run_framework.fn_warning
                                    ,i_log_entry => 'DID NOT RESTORE BECAUSE BACKUP DID NOT HAPPEN.'
                                    ,i_log_batch => :batch);
   END IF;
END;