When you apply your local change, these triggers are fired to build deferred calls to generated procedures at the remote master sites. Procedural replication, illustrated ![[*]](jump.gif)
, uses procedure wrappers to build deferred transactions. The deferred transactions, however, are propagated in the same manner, whether you use procedural replication, row-level replication, or some combination
of both.
Propagation of deferred transactions is controlled by job queue processes that you set up. You may choose to forward calls at frequent intervals, such as every few seconds, thus simulating event-based propagation.
Alternatively, you may initiate propagation at points in time when connectivity is available or communications costs are lowest, such as during evening hours. Queued transactions can be propagated to different destinations at different frequencies according to priority. If a remote system is unavailable, the deferred transactions targeted for that system remain in the local queue for later propagation.
The DBMS_DEFER_SYS package contains the procedures that you must use to forward changes from your current master to another master site in the same replicated environment. There are two different methods that you can use to propagate changes to a master site:
Figure 4 - 2. Applying Changes to an Updatable Snapshot
As shown in Figure 4 - 3, the arguments that you pass to the EXECUTE or SCHEDULE_EXECUTION procedures determine how the transactions are executed at the remote site. For scheduled executions, Oracle creates a job queue entry for you, and the settings of the job queue initialization parameters also affect the execution of the transactions.
Figure 4 - 3. Deferred Transactions
Attention: EXECUTE and SCHEDULE_EXECUTION do not expand database link names. If you do not specify a fully qualified database link name, your transactions cannot be successfully propagated to the remote site.
To ensure that the transactions are able to execute successfully at the remote site, you should create the necessary private database links for these users. These links should include a username and password, and the username specified should have the appropriate privileges on the generated procedures at the remote site.
Oracle first searches for the appropriate private database link. If none is found, it attempts to complete the connection using a public database link. If the database link includes a username and password, Oracle completes the connection using this information. If the link does not include a username and password, Oracle attempts to use the username and password from the current local session. This username and password combination must exist as a valid user at the remote site in order for the connection to succeed.
Note that if your database link does not include the username and password information, Oracle uses the username and password for the connected session, even if EXECUTE_AS_USER is FALSE. Because the user at the remote site determines the privilege domain in which the transaction is executed at the remote site, this may cause unexpected results.
If you are using a background process to push your deferred transaction queue (that is, if you called SCHEDULE_EXECUTION), your database link must include a username and password, or the connection will fail. This is because the current session, a background process, has no associated username and password.
Deferred transactions maintain transactional consistency and ordering. Multiple procedure calls submitted within a single local transaction are executed as a single transaction remotely. Deferred transactions are executed remotely in the same order as they are committed locally. Order of execution within a transaction is also preserved. The deferred transaction executes every remote procedure call at each system in the same order as it was executed within the local transaction. Each remote procedure call and each deferred transaction is executed exactly once on each remote system.
DBMS_DEFER_SYS.SCHEDULE_EXECUTION(
dblink => 'acct_ny.ny.com',
interval => 'SYSDATE + 1',
next_date => SYSDATE);In this example, once a day, any deferred remote procedure calls queued at your current master site will be forwarded and applied to the ACCT_NY master site.
The scheduling information that you supply to this procedure is recorded in the DefSchedule view described in Table 13 - 24. You can either schedule the changes to be propagated once at a specified time, or to be forwarded periodically using a given formula.
To remove a scheduled job from the DefSchedule view, call the procedure UNSCHEDULE_EXECUTION.
When you call SCHEDULE_EXECUTION, Oracle creates a job queue entry using the INTERVAL and NEXT_DATE information that you supply to schedule the call to EXECUTE. Every few seconds (based on the value of the JOB_QUEUE_INTERVAL parameter), a background process checks the job queue to determine if there are any pending jobs. If so, the job is executed. You can experience some delay if you do not have enough available job queue background processes (as determined by the value of the JOB_QUEUE_PROCESSES parameter) to execute the outstanding jobs.
To schedule asynchronous propagation among all of your master sites, you must call this procedure N - 1 times at each master site, where N is the total number of master sites in your replicated environment. For example, if you have three master sites, you would need to call this procedure twice at each master site. To change the interval at which changes are forwarded, simply call this procedure again with a new interval formula.
Additional Information: The parameters for the SCHEDULE_EXECUTION procedure are described in Table 12 - 25.
DBMS_DEFER_SYS.EXECUTE(destination => 'acct_ny.ny.com');
In this example, any deferred transactions queued at your current site are immediately pushed to the ACCT_NY master site. In this example, these transactions will be pushed as the user who originally performed the update at the local site.
When you call EXECUTE, Oracle forwards and applies any outstanding remote procedure calls queued at your current location, to the given master site and records any errors or unresolvable update conflicts in the DefError view at the destination site.
This procedure can also be called automatically by an Oracle background process that is scheduled using the SCHEDULE_EXECUTION procedure. Anytime EXECUTE is called automatically, Oracle records the date information in the LAST_DATE field of the DefSchedule view.
Additional Information: The parameters for the EXECUTE procedure are shown in Table 12 - 23.
DBMS_DEFER_SYS.UNSCHEDULE_EXECUTION( dblink => 'acct_ny.ny.com');
In this example, any deferred remote procedure calls queued at your current master or snapshot site will no longer be automatically forwarded to the master site referred to by the dblink ACCT_NY.NY.COM.
No changes will be pushed to this site from your current site until you either manually push them by calling EXECUTE or you reschedule automatic execution. You should call UNSCHEDULE_EXECUTION if you drop a master site from your replicated environment.
Additional Information: The parameters for the UNSCHEDULE_EXECUTION procedure are described in Table 12 - 28, and the exceptions are listed in Table 12 - 29.
DBMS_DEFER_SYS.DISABLED ( dblink => 'acct_ny.ny.com');
The procedure will return TRUE, if propagation to the specified site is enabled, if not, it will return FALSE.