Document ID: 22589.1
Subject: SNAPSHOTS: INTRODUCTION AND EXAMPLES
Last Revision Date: 28 March 1995
Author: SMASUD
========
Abstract
========
This bulletin contains a discussion of snapshots in Oracle7. It looks
into their implementation, refresh processes and attempts to explain
through some working examples. The information provided in this bulletin
should be useful in getting started with snapshots and better understanding
different refresh processes. The following introductory section has been
summarized and condensed from the concepts manual for easy reference.
============
Introduction
============
Oracle7 has introduced the table snapshot feature that allows replication
of a master table by an unlimited number of snapshots existing on different
nodes in a distributed network. The snapshot cannot be updated. It is a
read-only kind of table. Updates are allowed only on the master table.
Each snapshot (replicate) is updated asynchronously (refreshed) to reflect
changes made in the master table. The refresh process can be either manully
invoked using SQL, or invoked automatically after specified periods of time.
=================
Simple vs Complex
=================
A simple snapshot is one in which each row is based on a single row in a
single remote table. A simple snapshot's defining query has no GROUP BY
or ORDER BY clauses, or subqueries, joins or set operations. If the defining
query of a snapshot contains any of these clauses or operations, it is
referred to as a complex snapshot.
==================
Why use snapshots?
==================
1) Having a snapshot for a remote table allows local queries to be executed
in much less time because the requested data does not have to be pulled
across the network.
2) If the master table is inaccessible due to some reason, the snapshots
(read only) are still available for use for master site users.
======
Syntax
======
CREATE SNAPSHOT <schema>.snapshot
PCTFREE integer
PCTUSED integer
INITRANS integer
MAXTRANS integer
TABLESPACE tablespace
STORAGE storage_clause
CLUSTER cluster (column_list)
REFRESH <fast> START WITH date NEXT date
<complete>
<force>
=========================
Snapshot related objects:
=========================
Master table
Snapshot base table
Snapshot log
================================================
Relevent Snapshot related Data dictionary Views:
================================================
DBA_SNAPSHOTS
DBA_SNAPSHOT_LOGS
USER_SNAPSHOTS
ALL_SNAPSHOTS
MVIEW$_<snapshot name>
=================================================
Relevent Snapshot related Data dictionary tables:
=================================================
SNAP$_<snapshot name>
MLOG$_<snapshot name>
=================
Relevent Packages
=================
DBMS_SNAPSHOT
Can be created by running the following two scripts in that order:
(1) dbmssnap.sql
(2) catproc.sql (which runs prvtsnap.plb, besides other scripts)
------------------------------------------------
-- SUMMARY OF SERVICES PROVIDED BY THIS PACKAGE
--
-- purge_log - purge log of unnecessary rows
-- refresh - refresh a given snapshot
-- refresh_all - refresh all snapshots that are due
-- to be refreshed
-- drop_snapshot - drop a given snapshot
-- set_up - prepare master site to refresh a snapshot
-- wrap_up - record a refresh at the master site
-- get_log_age - find oldest date entry in log
-- testing - test snapshots (currently null)
-- I_am_a_refresh - flag used to let triggers identify refreshes
This package must exist before attempting to create snapshots.
=============================
Relevent init.ora parameters:
=============================
The snapshot refresh process parameters are as follows:
o SNAPSHOT_REFRESH_PROCESSES (number)
o SNAPSHOT_REFRESH_INTERVAL (seconds)
o SNAPSHOT_REFRESH_KEEP_CONNECTIONS (true/false)
==================
Snapshot Mechanism
==================
When a snapshot is created, Oracle creates several other objects as well
corresponding to this snapshot. These objects should not be altered.
At the snapshot site, Oracle creates a base table that stores the rows
retrieved by the snapshot's defining query. It then creates a view
of this base table. It is this view that is available to users as
the snapshot. Note that this view is read-only.
Another view is created on the snapshot site. This view is used to
refresh the snapshot.
Refreshing a Snapshot:
Periodically, a snapshot is refreshed to reflect the most recent
changes made to the master table. The snapshot refresh processs uses the
snapshot's defining query to fetch the results and replace the
previous snapshot contents with this data. Each snapshot is refreshed
in a separate transaction.
Snapshot Log:
A snapshot log is a table associated with the master table and resides
on the master database. The snapshot log expedites the refresh process of
a simple snapshot. Oracle uses the snapshot log to track the rows that
have been updated/deleted/inserted in the master table. When a certain
simple snapshot based on a master table is refrshed, only the appropriate
rows in the snapshot log ned to be applied to the snapshot to refresh it.
This is called a "fast refresh". Notice that one snapshot log can be used to
update multiple snapshots based on the same master table. If no other snapshot
needs to have any row fro the snapshot log applied to it, then the log
is purged to keep its size small. However, as long as there is at least
one snapshot that needs a certain row from the snapshot log applied to it,
that row is kept in the log.
A snapshot that is not simple and is there is no snapshot log, then fast
refresh is not possible. In this case the snapshot has to be refreshed by
doing a "complete refresh".
=========
Examples:
=========
**************************************************
[1] Simple snapshot with Snapshot log (on Master):
**************************************************
MASTER SITE (REMOTE)
--------------------
Create a snapshot log on the master table:
sqlplus scott/tiger
create snapshot log on emp
tablespace users
storage (initial 10K pctincrease 50)
pctfree 5;
NOTE: init.ora parameters for snapshot NOT used (since not needed)
SNAPSHOT SITE (LOCAL)
---------------------
1. Following init.ora parameters set:
snapshot_refresh_interval = 20
snapshot_refresh_processes = 2
snapshot_refresh_keep_connections = true
open_cursors = 250
2. sqldba
connect internal
shutdown
startup (to make new init.ora parameters effective)
3. sqlplus system/manager
create public database link chicago
connect to scott identified by tiger
using 'chicago';
NOTE: SQL*Net V2 alias 'chicago' defined in tnsnames.ora
4. sqlplus scott/tiger
create snapshot emp_snap
pctfree 5
pctused 60
refresh fast
start with sysdate
next sysdate + (1/288) /* refresh every 5 mns. */
as select * from emp@chicago;
MASTER SITE (REMOTE)
--------------------
1. sqlplus scott/tiger
insert into emp (empno,ename,job,hiredate,deptno)
values (1234,'SCOTT','DBA',sysdate,10);
commit;
SNAPSHOT SITE (LOCAL)
---------------------
1. sqlplus scott/tiger (or simply switch to client window)
select count(*) from emp_snap;
Repeat until snapshot is automatically refreshed (about 5 mns.)
This confirms that fast refresh is working.
The snapshot can also be refreshed manually by using the
dbms_snapshot.refresh procedure as demonstrated below:
MASTER SITE (REMOTE)
--------------------
1. sqlplus scott/tiger
insert into emp (empno,ename,job,hiredate,deptno)
values (2345,'SCOTT','DBA',sysdate,10);
commit;
SNAPSHOT SITE (LOCAL)
---------------------
1. sqlplus system/manager
select count(*) from scott.emp_snap;
execute dbms_snapshot.refresh('SCOTT.EMP_SNAP','?');
PL/SQL procedure successfully completed.
select count(*) from scott.emp_snap;
NOTE: Snapshot was verified to have been manually refreshed !
The snapshot cannot be refreshed by executing the dbms_snapshot.refresh_all
procedure. That is, if a given snapshot is scheduled to be refreshed
at 1:00, then any calls to refresh_all before 1:00 will not cause that
snapshot to be refreshed. This is demonstrated below:
MASTER SITE (REMOTE)
--------------------
1. sqlplus scott/tiger
insert into emp (empno,ename,job,hiredate,deptno)
values (3456,'SCOTT','DBA',sysdate,10);
commit;
SNAPSHOT SITE (LOCAL)
---------------------
1. sqlplus system/manager
select count(*) from scott.emp_snap;
execute dbms_snapshot.refresh_all;
PL/SQL procedure successfully completed.
select count(*) from scott.emp_snap;
NOTE: Snapshot was NOT manually refreshed by 'refresh_all'
procedure.
*****************************************************
[2] Simple Snapshot with no Snapshot log MASTER SITE:
*****************************************************
(1) SNAPSHOT CREATED WITH REFRESH FAST OPTION.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This example will demonstrate that only those snapshots can be
refreshed fast for which the master table has a snspshot log.
If there is no snapshot log, even those snapshots for which
"refresh fast" was specified at create snapshot time will not
get automatically refreshed when the master table is updated.
This is due to the fact that the refresh fast process refreshes the
snapshots due to be refreshed (fast) only if a snapshot log
exists on the master site. Otherwise, it does not do anything and
the only way to refresh such a snapshot is by doing it manually,
using the dbms_snapshot.refresh packaged procedure.
=====================
SNAPSHOT SITE (LOCAL)
=====================
Create Snapshot
---------------
SQL> connect system/manager
Connected.
SQL> grant resource to saj;
Grant succeeded.
SQL> connect saj/saj
Connected.
SQL>
Create Database Link
--------------------
SQL> create database link aixlink
2 connect to system identified by manager
3 using 't:tcaix:V716';
Database link created.
Check the Database Link created
-------------------------------
SQL> desc dept@aixlink
Name Null? Type
------------------------------- -------- ----
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Create the Snapshot (to be refreshed every 5 minutes)
-----------------------------------------------------
SQL> create snapshot deptsnap
2 pctfree 5
3 pctused 60
4 refresh fast
5 start with sysdate
6 next sysdate + (1/288)
7 as select * from system.dept@aixlink;
Snapshot created.
Check the Snapshot created
--------------------------
SQL> select * from deptsnap;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
====================
MASTER SITE (REMOTE)
====================
SQL> connect system/manager
Connected.
Check the Master table
----------------------
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
SQL> delete from dept where deptno = 50;
1 row deleted.
SQL> commit;
Commit complete.
=====================
SNAPSHOT SITE (LOCAL)
=====================
SQL> select * from deptsnap;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
After 10 minutes:
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
RESULT: Snapshot is not refreshed, because it was created to be
refreshed fast, but a snapshot log does not exist.
Try refreshing manually using the dbms_snapshot.refresh packaged procedure.
Refresh is successful.
(2) SNAPSHOT CREATED WITH REFRESH COMPLETE OPTION.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
=====================
SNAPSHOT SITE (LOCAL)
=====================
Create Snapshot
---------------
SQL> connect system/manager
Connected.
SQL> grant resource to saj;
Grant succeeded.
Create Database Link
--------------------
SQL> create database link aixlink
2 connect to system identified by manager
3 using 't:tcaix:V716';
Database link created.
Check the Database Link created
-------------------------------
SQL> desc dept@aixlink
Name Null? Type
------------------------------- -------- ----
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Create the Snapshot
-------------------
SQL> connect saj/saj
Connected.
SQL> create snapshot deptsnap2
2 pctfree 5
3 pctused 60
4 refresh complete
5 start with sysdate
6 next sysdate + 1/(288*20) /* REFRESH EVERY 15 SECONDS */
7 as select * from system.dept@aixlink;
SQL>
Snapshot created.
Check the Snapshot created
--------------------------
SQL> select * from deptsnap2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
====================
MASTER SITE (REMOTE)
====================
SQL> connect system/manager
Connected.
Check the Master table
----------------------
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
SQL> delete from dept where deptno = 50;
1 row deleted.
SQL> commit;
Commit complete.
=====================
SNAPSHOT SITE (LOCAL)
=====================
SQL> select * from deptsnap2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 a,b
After 15 seconds:
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
RESULT: Snapshot is refreshed automatically, because it was created to be
refreshed complete.
====================
MASTER SITE (REMOTE)
====================
Check the Master table
----------------------
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values(50, 'FINANCE', 'SFO');
1 row inserted
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 FINANCE SFO
=====================
SNAPSHOT SITE (LOCAL)
=====================
SQL> select * from deptsnap2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
After 15 seconds:
SQL> select * from deptsnap2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 FINANCE SFO
RESULT: Snapshot is refreshed automatically, because it was created to be
refreshed complete.