Previous | Table of Contents | Next

Page 719


CAUTION
If the primary key is not used with the create snapshot command, the record's rowid will be used. This can create potential problems if the master table is recovered due to a database recovery. The recovery will change the rowid values and can potentially invalidate the snapshot. To ensure that this is not a problem, refresh snapshots using the complete option after any database recovery.

Using defaultmaster|LOCAL specifies which type of rollback segment to use. DEFAULT will enable Oracle to choose, MASTER will use the rollback segment at the remote site, and LOCAL will use the rollback segments in the local database.

Using Snapshot Refresh Groups

Many snapshots, although associated with different master tables, may have the same refresh rate, making them candidates for grouping. Although Oracle8 enables a point-and-click method for creating these snapshots, it is important to know what happens behind the mouse.

Oracle refers to this capability as API calls. The manipulations of replicated objects are not performed via standard SQL commands. The calls are performed through the use of procedures. For more information concerning procedures, their syntax, and how they are used, refer to the Oracle8 Application Developer's Guide.

Identifying Potential Problems with a Snapshot

As with any database management process, proactive management is the most appropriate method. To proactively manage the snapshot process, the DBA must be able to identify potential problems and design the system in order to prevent them from occurring. This section identifies some of these problems.

Page 720

Sizing There is one pitfall to avoid during the creation of a snapshot. Although it is query only, the snapshot is not a static table. Sizing should be based on the update rate of the table copied, not the snapshot itself. For example, if the table GERMANY_SPECS is a highly dynamic table, it should have a larger percent used (PCTUSED) to accommodate the updates. The table should be sized to ensure that fragmentation does not occur.

Dropping Objects Inadvertently Some of the snapshots are in refresh groups. To find the job numbers associated with them, query the DBA_REFRESH_CHILDREN table. These groups can be dropped; however, care must be taken to ensure that the objects are not also dropped. For this reason, the method for dropping any type of replication group should be performed via sql code and not the Enterprise Manger. The Enterprise Manager enables the DBA to drop the objects by simply clicking on the objects. The way to drop Refresh Groups is to use the procedure:

execute DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP(gname=>'[name of
Âgroup]',drop_contents=>FALSE);
drop_contents defaults FALSE; specifically setting it to FALSE is just an added precaution. When using procedures, they must be entered as a single line or they will not be invoked.

CAUTION
If you set drop_contents to TRUE, the tables associated with the group are also dropped.

Losing Snapshot Refreshes When a master database cannot complete the refresh of a snapshot, it will try 16 more times before the refresh attempts will stop. The 16 times does not mean 16 refresh intervals. The first time a refresh fails, the snapshot process will wait one minute and try again. If that fails, the time doubles to two minutes, then four, and the time span grows exponentially until it reaches or exceeds the actual refresh interval. When that occurs, it will continue at the refresh rate. After 16 times, Oracle will update the column BROKEN in the tables USER_REFRESH and USER_REFRESH_CHILDREN. The broken column indicates that something has gone wrong with the snapshot refresh. This will not only be reflected in this table, but the Oracle SNP process will also indicate a refresh problem in trace files and alert.log.

One way to reduce the possibility of a broken snapshot is to create the snapshot with the FORCE option rather than FAST. FORCE will choose FAST unless something has occurred to prevent a successful FAST refresh; then it will perform a complete refresh instead. One drawback to using this option occurs in the event of a very large table. If the refresh takes a long time, problems may occur if the current refresh is still going when the next refresh is scheduled to occur.

After the DBA has determined that the refresh is broken and has rectified the problems that broke the snapshot, the snapshot can be manually refreshed. Oracle8 provides several methods of refreshing manually. One way is to execute the job defined as the snapshot. To determine this, log in at the child site and perform the following query:

SELECT RNAME,JOB FROM DBA_REFRESH;

Page 721

The results will be similar to this:

RNAME           JOB
GERMANY SPECS   142

Run the job that has the broken snapshot. The job name will be the same as the snapshot (locally/child). To perform this, use the following procedure:

Execute DBMS_JOB.RUN(142);

The response will be the following:

PL/SQL procedure successfully completed.

When this has been completed, verify that the BROKEN status of the tables USER_REFRESH and USER_REFRESH_CHILDREN is no longer Y. If the broken status remains at Y, repeat the process again. If this does not fix the problem, ensure that there are not other problems (such a loss of connectivity) that is preventing this refresh from occurring.

To proactively monitor the refresh process to prevent another broken snapshot, compare the snapshot time and the time in the snapshot logs. These should match; if they do not, it may indicate that the problem causing the initial loss of the snapshot has not been completely resolved.

At the master site, use the following query:

Select master,substr
(to_char
(current_snapshots,'MM-DD-YYYY HH:MI:SS'),
1,20)
time FROM DBA_SNAPSHOT_LOGS;

From the local/child site, use the following query:

SELECT NAME,SUBSTR
(TO_CHAR
(LAST_REFRESH,'MM-DD-YYYY HH:MISS'),
1,20)
TIME FROM DBA_SNAPSHOTS;

These times should match. If they do not, the snapshots will still require manual management until the problem has been completely resolved.

Controlling Snapshot Log Growth Snapshot logs contain the DML changes of the master snapshot. This determines what should be sent to the remote sites. The log is purged after all of the snapshots using this log have been completed. However, if there is a problem with the snapshot, or one of the snapshots is refreshed infrequently, then the snapshot log can grow uncontrollably. This can present a problem in two ways. The DBA should size the logs to enable a certain amount of growth. If the stability of the snapshot is mission-critical, the use of unlimited extents is recommended. This will remove the possibility of reaching the maxextents. This fills the tablespace where the log resides. The DBA should periodically check this log to ensure that this does not occur. If the size of the log is becoming

Previous | Table of Contents | Next