Summary of Oracle snapshot principles and implementation

Due to work requirements, we need to synchronize all the data in the Business Database to another processing server today. While working on the solution, I thought a lot of ways. Of course, the fastest way is to use physical hot backup.

However, I personally think that the SnapShot method is the best choice for medium and large databases (about GB for our databases), that is, the SnapShot method.

An Oracle Database snapshot is a table that contains query results for one or more tables or views on a local or remote database. That is to say, the basic principle of a snapshot is to save a query result from a local or remote database in a table.

The following is my Snapshot, which is used to Copy data from the business database to the processing database. It is a copy of data between two different servers.

Step 1: Create a database link on the Oracle terminal on the processing server. The SID of the Business database Server is TEST.

Create database link TEST_DBLINK.US.ORACLE.COM

Connect to AMICOS identified by AMICOS

Using 'test ';

Step 2: Create a snapshot log for the table corresponding to the Business Database

Create snapshot log on A_Table;

Step 3: Create a Snapshot named Test_SnapShot

Create snapshot Test_SnapShot

Refresh complete start with sysdate next sysdate + 1/24

As select * from A_Table @ TEST_DBLINK

Note: REFRESH is a REFRESH method.

There are two refresh Methods: COMPLETE and FAST, and start with indicates the START time of execution.

Next is the Next execution time.

In the future, AS is the method for querying snapshots.

Related methods:

Change Snapshot

Alter snapshot Test_SnapShot

Refresh complete start with sysdate next sysdate + 1/2;

Manually refresh the snapshot and execute the following command on the command interface:

EXEC DBMS_SNAPSHOT.REFRESH ('test _ SnapShot ', 'C ');

The first parameter is the snapshot name to be refreshed.

The second parameter is the refresh method, F----FAST, C---COMPLETE

View the last refresh date of the snapshot

Select name, LAST_REFRESH

FROM ALL_SNAPSHOT_REFRESH_TIMES;

The final solution for the examination is as follows:

1: Create a Snapshot log for the table to be Snapshot

Create snapshot log on t1 with rowid; here we use ROWID to create the parameter of the log.

2. Create a snapshot using Fast and use rowid as the reference parameter.

Create snapshot fb_test_ B refresh fast with rowid start with sysdate next sysdate + 1/1440 as select * from fb_test_ B @ my_dblink;

It is best to create a snapshot Based on rowid. Otherwise, you must create a Primary Key for the table.