[DB] Application of Oracle Snapshot technology in data exchange

Application of Oracle Data Snapshot technology in data exchange

The author of this article (Pan chengbin), please read this article to respect the author's copyright.

Abstract: This article introduces the application of Oracle Data Snapshot technology in data exchange, and illustrates how to apply data snapshots to achieve cross-database data synchronization.

Keywords: Oracle; Snapshot the snapshot technology of Oracle in the data exchange application

Pan Cheng-bin

(Touris College of Zhejiang, Hangzhou 311231, China)

Abstract: This paper describe the snapshot technology of Oracle in the data exchange application, and how to apply snapshot to achieve inter-library data synchronization.

Key words: Oracle; Snapshot

 

A snapshot of an Oracle database is a database object that contains query results for one or more tables or views on a local or remote database. Because snapshots are a subset of a master table, using snapshots can speed up data query. While synchronizing two tables in different databases, using snapshots to refresh, the data update performance will also be greatly improved. Snapshots allow you to maintain copies of remote data locally, but these copies are read-only. To modify a local copy, you must use the advanced copy function. When you want to extract data from a table or view, you can extract data from the snapshot.

1. Specific snapshot Structure

Snapshots are widely used and can be used for performance optimization and data extraction. They are an important technology in Oracle Data Warehouse processing. Limited by the complexity and capabilities of snapshot technology, please refer to the relevant official help documentation for details.

Snapshot technology provides three refresh mechanisms:

1) Complete refresh mechanism, that is, refresh all the data in the table. If the data volume of the table is very large, this method will consume a considerable amount of time;

2) fast refresh: refreshes data increments only;

3) Force Refresh: first, determine whether a quick refresh mechanism can be used. If not, use a full refresh mechanism.

The basic syntax for creating a snapshot is as follows:

Create snapshot <specific snapshot Name>
The statement for setting up a refresh mechanism is as follows:

Alter snapshot <specific snapshot Name> refresh <refresh method> Start With <first refresh time> next <interval expression>

The following describes the two refresh mechanisms: complete and fast.

First, assume that a snapshot named testshot has been created. The specific statement is as follows:

Create snapshot testshot as <SELECT query statement>

1.1 completely refresh the snapshot

1) completely refresh snapshot Creation

Alter snapshot testshot refresh complete start with sysdate 1/720 next sysdate 1/2

The preceding statement indicates that the snapshot will be refreshed for the first time within 2nd minutes after the snapshot is created, and then automatically refreshed every 12 hours.

2) If you want to manually refresh, there is no limit on the interval. The specific statement is as follows:

Begin

Dbms_refresh.refresh ('testshot ', 'C ')

End

1.2 fast refresh snapshot is incremental refresh Snapshot

To create a quick snapshot, create a snapshot log on the source data table, as shown in process 1.

1) create a snapshot log on the source data table

Create snapshot log on Test

2) set the snapshot refresh time

Alter snapshot testshot refresh fast start with sysdate next sydate 1/2

From the above statement, we can actually use the same basic statement for completely refresh and quick refresh, but the refresh mechanism is different. Note that if you want to use Quick refresh, the source table cannot be a view because snapshot logs cannot be created on The View.

If the snapshot cannot work normally during the application process, we can query the user_jobs table to obtain the corresponding information. The table structure is shown in table 1.

2. Preparations before data snapshot Creation

The above section describes the basic write method and refresh time settings of the snapshot statement. Next we will introduce the preparations for applying the data snapshot, first, we need to create a database account on the local Oracle data and establish a remote database link under it.

The statement for creating an account is as follows:

Create user Binbin identified by Binbin default tablespace Pan temporarytablespace temp;

Grant connect, resource to Binbin;

Grant create synonym to Binbin;

Grant create snapshot to Binbin;

Grant create database link to Binbin;

The preceding statement creates an account Binbin with a password of Binbin and grants permissions to the account. For more information, see Oracle official introduction.

Create a remote database link. The specific statement is as follows:

Create database link <Link name> connectto <Remote Server user name> identified by "password" using "TNS configured Instance name"

Next we will use the above basic statement to create a database link named bintobin

Create Database Linke bintobin connect to sa identifiede by "sa" using "test"

After the database link is created, we can access the data in the remote database and a specific table. Below is a snapshot of the User table accessing the remote database.

Create snapshot testshot as select * from user @ Test

3. Specific Application of Data Snapshot technology

Because each department of the school has its own application system, the database creation time varies, and the data structure and data type are different, the database software used is also different. I have been studying how to quickly and accurately synchronize the interaction between these data. Using Snapshot technology can easily solve existing problems.

This article from www.14edu.com (paper) Source: http://www.14edu.com/ligong/jisuanji/05203H122010.html

 

The following is an example of Data Reading between subsystems using Snapshot technology in our hospital. Figure 2 shows the distribution of each database.

In terms of snapshot refresh interval, the Data Update Requirements of various application systems are different. For example, one-card data consumption requires that the data of the previous day be retrieved the next day, and some data only needs to be updated once every two days, therefore, the specific refresh method of Each snapshot needs to be set as required. Since the implementation of this scheme, the data reading has been basically normal on the premise that the network conditions are ensured.

4 Conclusion

The Snapshot technology provided by Oracle allows us to quickly read remote data. Using snapshots can easily implement data synchronization and replication between different databases, especially in incremental data replication. Generally, incremental replication is used to synchronize data. Especially when the original data volume is large, it is difficult to synchronize data due to errors.

References:

[1] Wang Xiong, Zuo Jie. Oracle Data Table Delta Extraction Algorithm Based on Snapshot logs [D]. Dalian University of Technology, 2008.

[2] Teng yongchang. Oracle9i Database Administrator [M]. Beijing: Machinery Industry Press, 2005.

This article from www.14edu.com (paper) Source: http://www.14edu.com/ligong/jisuanji/05203H122010_2.html