WL#4770: Online Backup: Test firing of triggers and events during backup/restore
Affects: Server-6.0
—
Status: Complete
Create tests that verify that triggers and events behave correctly during backup and restore. Backup is a read-only operation and shall not fire triggers itself. But modifying statements in other sessions shall fire the appropriate triggers as usual during an online backup operation, regardless if the trigger influences a table in backup or not. However, triggers that modify tables in backup shall be blocked during the validity point, and they shall not influence the backup contents when completing after the validity point. Events likewise. Restore shall not fire triggers itself. It tries to restore tables to a former state. This requires inserts into the tables in restore. These inserts shall not fire triggers. But modifying statements in other sessions shall fire the appropriate triggers as usual during a restore operation. However, triggers that modify tables in restore shall be blocked during restore. Events likewise. Also test that triggers on restored database objects work as expected after restore (already done).
In this worklog we will be testing the following test scenarios, PART 1: Triggers and Backup --------------------------- 1. Perform backup on a database. Fire trigger from another database which influences a table where both trigger and table are not included in backup. Fire the trigger at different phases of validity point (before, during, after). Note that since the database is not included in the backup, there will be no influence in the backup image. Trigger that will fired before and after validity point shall work and will be blocked during validity point. 2. Perform backup on a database. Fire trigger from another database (not included in backup) which influences a table that is included in backup image. Trigger will be fired at different phases of validity point. Result of trigger that is fired before validity point will be included in the backup image. Result of trigger fired during and after validity point will not be included in the backup image. 3. Perform backup on a database. Fire trigger from this database (included in backup) which influences a table that is not included in backup image. Trigger will be fired at different phases of validity point. Result of trigger that is fired before validity point will not be included in the backup image. Result of trigger fired during and after validity point will not be included in the backup image. 4. Perform backup on a database. Fire trigger from this database (included in backup) which influences a table that is included in backup image. Trigger will be fired at different phases of validity point. Result of trigger that is fired before validity point will be included in the backup image. Result of trigger fired during and after validity point will not be included in the backup image. PART 2: Triggers and Restore ---------------------------- 1. Perform restore of a database. Fire trigger from another database which influences a table where both trigger and table are not included in backup. Result of trigger shall be present in target table after restore. 2. Perform restore of a database. Fire trigger from another database (not included in backup) which influences a table that is included in backup image. Result of trigger shall be present in target table after restore. 3. Perform restore of a database. Fire trigger from this database (included in backup) which influences a table that is not included in backup image. Result of trigger shall be present in target table after restore. 4. Perform restore of a database. Fire trigger from this database (included in backup) which influences a table that is included in backup image. Result of trigger shall be present in target table after restore. PART 3: Events and Backup ------------------------- 1. Perform backup on a database. Execute event from another database which influences a table where both event and table are not included in backup. Execute the event at different phases of validity point (before, during, after). Note that since the database is not included in the backup, there will be no influence in the backup image. Events that will execute before and after validity point shall work and will be blocked during validity point. 2. Perform backup on a database. Execute event from this database (included in backup) which influences a table that is included in backup image. Execute the event at different phases of validity point. Result of event that is executed before validity point will be included in the backup image. Result of event executed during and after validity point will not be included in the backup image. PART 4: Events and Restore -------------------------- 1. Perform restore of a database. Execute event from another database which influences a table where both trigger and table are not included in backup. Result of event shall be present in target table after restore. 2. Perform restore of a database. Execute event from this database (included in backup) which influences a table that is included in backup image. Result of event shall be present in target table after restore.
The new tests will go into the existing test case backup_triggers_and_events. It contains just one test, which is slightly different but still similar enough to bundle it with the new tests. Triggers ======== Two databases shall be created: 1. bup_trigevt_backup - to be included in BACKUP/RESTORE, 2. bup_trigevt_indept - independent (not included in backup). Three tables shall be created per database: 1. t1_same - shall have INSERT, UPDATE, and DELETE triggers, which affect the third table of the same database. 2. t2_othr - shall have INSERT, UPDATE, and DELETE triggers, which affect the third table of the other database. 3. t3_vict - "victim" table to be affected by other tables's triggers. Triggers and BACKUP ------------------- To avoid a giant amount of tests, combine several tests into one. use the INSERT triggers before a validity point, UPDATE triggers at a validity point, and DELETE triggers after a validity point within one test. Furthermore, the four backup tests from the HLS can be combined by running the INSERT, UPDATE, and DELETE into each of the trigger-bearing tables at the respective states within one test. There shall be extra connections to run these statements: 1. UPDATE bup_trigevt_backup.t1_same 2. UPDATE bup_trigevt_backup.t2_othr 3. UPDATE bup_trigevt_indept.t2_othr All these statements affect tables that are included in BACKUP. All these statements run at the validity point and thus block. There shall be a RESTORE, unaffected by triggers, to show, what's in the tables. Triggers and RESTORE -------------------- There shall be another RESTORE, during which one statement per table shall be executed. The same three statements will block again, so the three extra connections are required again. Events ====== Two databases shall be created: 1. bup_trigevt_backup - to be included in BACKUP/RESTORE, 2. bup_trigevt_indept - independent (not included in backup). One table shall be created per database. One event shall be created per database, affecting the table in the same database. The events shall execute every second. Events and BACKUP ----------------- BACKUP shall wait before, at, and after the validity point until the event on its database has executed in each phase. There shall be a RESTORE, unaffected by events, to show, what's in the tables. Events and RESTORE ------------------ There shall be another RESTORE, during which the events shall be executed.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.