Previous | Table of Contents | Next

Page 716

    There may also be information in the ADVICE column, indicating to the DBA where the transaction was. For example:
    ALTER SESSION ADVISE COMMIT;
    
    The column GLOBAL_TRAN_ID in the DBA_2PC_PENDING table will provide the DBA with the transaction id. This transaction id is the same on all databases involved in the distributed transaction. If the database is the global coordinator, the last part of the GLOBAL_TRAIN_ID will match LOCAL_ID in the DBA_2PC_PENDING table.

  1. Look at DBA_2PC_NEIGHBORS. The table looks like this:
    LOCAL_TRAN_ID—The local transaction ID for this distributed transaction.
    IN_OUT—Indicates whether the transaction is IN or OUT.
    DATABASE—If the transaction is IN, this is the name of the database sending the transaction. If the transaction is OUT, this will have the name of the database link to the database to which the transaction is being sent.
    DBUSER_OWNER—If the transaction is IN, this is the name of the local user. If the transaction is OUT, this is the owner of the database link.
    INTERFACE—C for commit request, N if in prepare or read-only state.
    DBID—Oracle unique identifier for the connecting database.
    SESS#—Session ID of the connection.
    BRANCH—Transaction id for this branch.
  2. Using the information in this table (the database and LOCAL_TRAN_ID), follow this thread to the next database.
  3. Query the DBA_2PC_PENDING table. If LOCAL_TRAN_ID and GLOBAL_TRAN_ID match, this is the global coordinator. This is where the DBA should start to follow the transaction thread. If, during this tracing process, the DBA encounters a resolved transaction, use this as the template for the outstanding transactions.
  4. To force a transaction, use the TRANSACTION_ID in the DBA_2PC_PENDING table, as in the following:
    Commit force `transaction id';
    Rollback force `transaction_id';
    

NOTE
The single quotes are required.n
  1. If in doubt, always roll the transaction back. Then communicate this rollback to the user community. As an added precaution, check the status of the tables affected to ensure data consistency. Although cumbersome, this will happen infrequently and will always ensure the integrity of the database.

Page 717

Understanding Read-Only Snapshots

A snapshot is simply a copy of a master table (or a subset) replicated to other child sites at specified intervals. The read-only snapshot is a faster solution than the distributed query (a select statement referencing a remote table). In addition to removing the network overhead, the hits on the remote table are significantly reduced. The snapshot can be one of the best methods of providing information to other remote sites, if the information needed does not have to be in real-time. The snapshots are normally refreshed on a predetermined schedule. Each snapshot or set of snapshots can have its own update schedule. Snapshots can be replicated to different locations depending upon the requirements of each site. In addition to scheduled refreshes, the DBA can manually refresh a snapshot. This is done using the DBMS_REFRESH procedure. They syntax for such a refresh is

EXECUTE DBMS_REFRESH.REFRESH (`Snapshotname');
NOTE
In this format, the refresh will use the default of FAST. If a COMPLETE is desired, then this must be added to the procedure call.n

The purposes for providing snapshots are the same as those of a simple distributed system. The following are specific reasons to use snapshots rather than dynamic database links:

For example, Big Widget Company has determined that due to increased reliance on the United States widget specifications, the distributed databases no longer support the business. Snapshots of the widget specifications are established. For business reasons, BWC has decided that Italy and Germany do not need to know what the other country's specifications are, but the United States needs to know what the widget specifications are for the other two countries. All the widget designs are kept in separate tables, so you don't need to combine the tables. The owner of the tables is the same worldwide to reduce confusion.

Setting Up a Snapshot

Creating snapshots is as simple as creating a table. Actually, this is what the DBA is doing—creating a copy of a table. The difference is that the copy becomes an automated process rather than a manual one. Once the table is copied, it is simply updated on a regular schedule to reflect the changes. The layout of the snapshot should be based on the same principles used to create a table.

The syntax for creating a snapshot is as follows:

CREATE SNAPSHOT [schema.]SNAPSHOT [Physical attributes *]
[TABLESPACE tablespace]
[LOB_storage_clause]
[CACHE|NOCACHE]
[CLUSTER (cluster column{,})]

Page 718

[table partition clause]
[parallel clause]
[USING INDEX physical attributes clause [LOGGING|NOLOGGING]TABLESPACE tablespace]
[FAST|COMPLETE|FORCE]
[START WITH date]
[NEXT date]
[WITH PRIMARY KEY|ROWID]
[USING DEFAULT {MASTER|LOCAL}] ROLLBACK SEGMENT]
[LOCAL|MASTER ROLLBACK SEGMENT rollback_segment]
FOR UPDATE AS select_command;

For example, the following creates a simple snapshot:

CREATE SNAPSHOT milan_specification
REFRESH FORCE START WITH SYSDATE next SYSDATE + 7 ;

START WITH and next of the CREATE SNAPSHOT command is based on SYSDATE. A whole number represents a single date. In the example, the next refresh rate is exactly 7 days from the first refresh. If next is not used, the snapshot will never refresh. If the refresh is to occur on a segment of time, such as an hour or half-hour, it will be in fractions. For example, a refresh rate of an hour will be SYSDATE + 1/24. A refresh rate of a half hour would be 1/48, every 15 minutes would be 1/96, and so on. If the refresh should always occur on a Monday, the syntax is a bit more complicated, such as the following:

NEXT_DAY(TRUNC(SYSDATE), `MONDAY')+3/24

NEXT_DAY is specified instead of SYSDATE. The 3 of the 24 represents 3 a.m.

Using Index allows the creation of an index that can improve performance when selecting from the snapshot.

FORCE/FAST/COMPLETE of the CREATE SNAPSHOT command is the method of replication. Complete is the most time intensive. This is recreating the snapshot. This is done by basically performing an INSERT INTO …AS SELECT *. Complete is what is used to create the initial snapshot. If a table is a significant size, this option should be avoided after the initial creation. If the table is very large, please refer to offline instantiation later in this chapter.

Fast is the default. It will use the log to compare the two snapshots and only add what is in the log.

NOTE
In a version 7 database, if a fast refresh fails, the snapshot refresh becomes broken and cannot be repaired without manual intervention. In version 8, if the Fast refresh fails, then a complete refresh will be performed. Fast refresh will work only if the snapshot has a snapshot log.n

Force will perform a fast refresh unless unable to do so (for example, a broken refresh). This option is removed in version 8.

The management of snapshots does not change significantly between versions 7 and 8. The biggest change is in the addition of options within the create clause. Most of these changes are not directly related to the snapshot process. Most are due to the addition of new object types, new create table commands, and new create index commands. The differences are

Previous | Table of Contents | Next