| Previous | Table of Contents | Next |
For snapshots that are small enough to be totally repopulated, the following steps are necessary. Note that it is possible to do a REFRESH COMPLETE or a REFRESH FORCE rather than a cron job, but using a cron is a simple way to guarantee that the replicated table will be fully repopulated. To avoid the REFRESH FAST option with a Unix cron, the following two steps are required:
For snapshots on large tables, you may want to use the REFRESH FAST option. For REFRESH FAST, the following steps are required:
Listing 7.1 shows an example of a snapshot that reads a table from an instance called london.
Listing 7.1 The london snapshot.
CONNECT sys/xxxx;
DROP PUBLIC DATABASE LINK london;
CREATE PUBLIC DATABASE LINK london
CONNECT TO db_link IDENTIFIED BY db_pass USING 'london';
--------------------------------------------------------
DROP SNAPSHOT my_replicated_table;
--------------------------------------------------------
CREATE SNAPSHOT my_replicated_table
PCTFREE 10 PCTUSED 40
TABLESPACE ts2
STORAGE (initial 60k next 10k pctincrease 1)
REFRESH FAST
START WITH SYSDATE
NEXT (sysdate+1) + 3/24
AS SELECT * FROM ORACLE. my_master_table@london;
GRANT ALL ON my_replicated_table TO PUBLIC;
******************************************************
Add the appropriate synonyms for the snapshots...
******************************************************
CONNECT /;
CREATE PUBLIC SYNONYM snap$_my_replicated_table FOR
ops$oracle.snap$_my_replicated_table;
Here we see that the my_replicated_table table is refreshed each morning at 3:00 AM, and the read-only name snap$_my_replicated_table has been replaced with synonym my_replicated_table. Here is an example of the snapshot log syntax that needs to be run on the master database:
CREATE SNAPSHOT LOG ON customer_table TABLESPACE ts2 STORAGE (initial 20k next 20k);
The dbms_snapshot.refresh_all procedure can be run at any time on the destination system to refresh the snapshot tables. To force a refresh of an individual table, execute the following:
EXECUTE dbms_snapshot.refresh('office','f');
Note: Any refresh errors are written to the alert.log file.
The snapshot log is a table that resides in the same database as the master table, which can be seen in the dba_tables view as a table with the name MLOG$_tablename. In our example, the snapshot log would be called MLOG$_customer.
Real-World Tips And Techniques For Oracle Snapshots
Even with Oracle’s distributed features, it is still far faster to process a table on a local host than it is to process a remote table across SQL*Net’s distributed communication lines. As such, table replication is a very desirable technique for improving processing speeds.
Several factors influence the decision about replicating tables. The foremost considerations are the size of the replicated table and the volatility of the tables. Large, highly active tables with many updates, deletes, and inserts will require a lot of system resources to replicate and keep synchronized with the master table. Smaller, less active tables would be ideal candidates for replication, since the creation and maintenance of the replicated table would not consume a high amount of system resources.
Oracle’s snapshot facility is relatively mature and generally works as noted in the Oracle documentation. However, the flexibility of the snapshot tool gives the developer many choices in how the snapshot will be created and refreshed. We can refresh the replicated table in full, we can re-create the snapshot at will, we can choose periodic refreshes of the snapshot, and we can use database triggers to propagate changes from a master table to the snapshot table. Although the choice of techniques depends upon the individual application, some general rules apply.
If a replicated table is small and relatively static, it is usually easier to drop and re-create the snapshot than to use Oracle’s REFRESH COMPLETE option. A crontab file can be set up to invoke the drop and re-creation at a predetermined time each day, completely refreshing the entire table.
Another popular alternative to the snapshot is using Oracle’s distributed SQL to create a replicated table directly on the slave database. In the following example, the New York database creates a local table called emp_ny, which contains New York employee information from the master employee table at corporate headquarters:
CREATE TABLE emp_ny
AS
SELECT
emp_nbr,
emp_name,
emp_phone,
emp_hire_date
FROM emp@hq WHERE department = 'NY';
Very large replicated tables consume too much time in dropping and re-creating the snapshot or using the REFRESH COMPLETE option. For static tables, a snapshot log would not contain very many changes—we could direct Oracle to propagate the changes to the replicated table at frequent intervals. Let’s take a look at the different refresh intervals that can be specified for a snapshot:
CREATE SNAPSHOT cust_snap1
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+7
AS SELECT cust_nbr, cust_name FROM customer@hq WHERE department = 'NY';
Here we are saying that we want Oracle to take the snapshot log and apply it to the replicated table every seven days.
The next example shows a table that is refreshed each Tuesday at 6:00 AM:
CREATE SNAPSHOT cust_snap1
REFRESH FAST
START WITH SYSDATE
NEXT NEXT_DAY(trunc(sysdate),'TUESDAY')+6/24
AS SELECT cust_nbr, cust_name FROM customer@hq WHERE department = 'NY';
For very static tables, we can also specify refreshes to run quarterly. The example below refreshes a table completely on the first Tuesday of each quarter:
CREATE SNAPSHOT cust_snap1
REFRESH COMPLETE
START WITH SYSDATE
NEXT NEXT_DAY(ADD_MONTHS(trunc(sysdate,'Q'),3),'TUESDAY')
AS SELECT cust_nbr, cust_name FROM customer@hq WHERE department = 'NY';
| Previous | Table of Contents | Next |