Oracle Advanced Replication

Article Summary:
Oracle is a database platform, and some projects use Oracle's database replication technology, which also encounters some problems, so here I have a personal understanding of Oracle's replication technology, hoping to help the project team with Oracle databases. The SQL scripts that are used in the text are validated to run.

--------------------------------------------------------------------------------

Body:

Oracle Advanced Replication

Before we start, it's a simple explanation that there are several projects in the company that use Oracle as a database platform, and some projects are using Oracle's database replication technology, and there are some problems, so here I have a personal understanding of Oracle's replication technology, Want to be able to help with the project team that uses Oracle databases. The SQL scripts that are used in the text are validated to run.
First, briefly introduce the basic concepts related to database replication, and then explore several important models of Oracle data replication technology, and finally introduce Oracle's data Replication Manager and how it can be used to configure and maintain the replication environment.
At the beginning of the article, I would like to start with a brief discussion of the basic concept of replication, that is, what is replication? Replication is simply the process of copying data in a distributed database environment consisting of two or more database systems. Oracle advanced replication, also known as symmetric replication, is the first in Oracle version 7.1.6, where Oracle data replication methods have only basic replication in the form of read-only snapshots. With each upgrade of the Oracle version, the functionality, management, and speed of data replication have been greatly improved and refined. At present, due to the growing demand for distributed data environment, more and more applications have put forward the demand of data replication.
1. Basic Concepts
1. Distributed Database Technology
Distributed database technology is an important part of distributed computing that we often refer to, which allows data to be shared across multiple server-side. Using distributed database technology, a local server can access data on remote servers in different physical locations, or it can enable all servers to hold copies/copies of data so that all servers in a distributed system can be accessed locally.
The first thing to consider in designing a distributed computing solution is the integrity, complexity, performance and availability, and response time of the application, as well as the need to consider whether to use real-time access to remote data for different application requirements or to use deferred access to remote data. This is for data replication is to take a real-time update replication scheme or delay data replication scheme.
2. The concept of synchronous and asynchronous
Synchronous distribution database technology is a real-time remote access and real-time updating of data technology. This technique ensures that the integrity of the application reduces the complexity of the application, but the response time is slow if the system has problems with slow network access.
Asynchronous distribution database technology is a technology that delays remote access and delays propagation to data updates. This technique has high availability and very short response time. It is more complicated than synchronous distribution database technology, in order to ensure the integrity of the application needs careful consideration and design.
For real business problems, it is necessary to weigh the pros and cons of these two technologies to finally choose the best solution, some problems to choose the synchronization technology is more appropriate, there are some problems using asynchronous technology is a better solution, there are some problems must be integrated in these two technologies.
3. The concept of reproduction
replication, by definition, is the copying of data from a database to a database in a different physical location to support distributed applications, which is an important part of the entire distributed computing solution. The concepts of synchronization and asynchrony are described above, and there are also synchronous replication and asynchronous replication issues for replication.
Replication, replicated data is consistent at any time at any replicated node. If the replicated data for any node in the replication environment has been updated, the change is immediately reflected to all other replicated nodes. This technique is suitable for commercial applications that require high real-time performance.
Asynchronous replication, the data for all replicated nodes is not synchronized for a certain amount of time. If the replicated data for one of the nodes in the replication environment has been updated, the change is propagated and applied to all other replication nodes in different transactions. These different transactions can be separated by a few seconds, a few minutes, a few hours, or a few days later. The data between replication nodes is temporarily unsynchronized, but the propagation will ultimately guarantee consistent data between all replicated nodes.
4. Update conflict
In an asynchronous replication environment, the key to all applications is to ensure data consistency. What happens when we look at the following situation? The same row of data on the same table is updated at two different locations at the same time. This situation can occur as an error called an update conflict. To ensure data consistency, update conflicts must be detected and processed to ensure that data elements in different locations maintain the same value. Update conflicts can be avoided by restricting "ownership" to a single node or by restricting the right to update a particular data element to a specific node.
2.Oracle Data Replication Application Model
In order to ensure the consistency of the actual application data, the method of conflict avoidance or conflict detection and elimination must be considered in the asynchronous replication application model. For example, for a real business application, it is important to have a logical understanding of the conflict avoidance methods of the business application and the data that is available at one node and those that are updatable and which are not updatable. In this section below, we will begin with a detailed discussion of two common conflict avoidance methods: The master Site ownership model and the dynamic ownership model. Then, the shared ownership model and the conflict detection and processing problems caused by this model are discussed, and finally, the replication configuration for Fail-over is discussed, which takes into account both conflict avoidance and conflict detection and elimination.
1. Master Site Ownership
Master site ownership, asynchronous replication data is "All" of a single node, the data to be replicated can only be updated by that node, and the other nodes are subscribing to the primary node that owns the data (subscribe) data, which means that they can only access the read-only copies of the replicated data on the local system. Here are some examples of this.
(1) Data unloading of online transaction processing for decision Support System (DSS). Data from one or more OLTP systems can be unloaded into a separate local DSS for read-only analysis
(2) Distribution of central information. Product information, such as price lists, is maintained at the Headquarters node and then replicated to the remote sales office's read-only product Catalog System
(3) The concentration of remote information. Product catalog data on many remote nodes is replicated to the headquarters node, where the headquarters node only needs to have data read-only permission
A master node has full ownership of a table, while the other nodes can only subscribe to a read-only copy of the table. It can also be that multiple master nodes have distinct subsets or partitions of the same table, while others subscribe to these subsets or read-only copies of the partition. such as, a distributed product Catalog system allows different sales offices to have a table of their own "level" section, such as in the Customer (CUSTOMERS) table, Order (Orders) table and Table of contents (ITEMS) that contain the customer and product information that each sales office serves. The Hub Headquarters node can maintain a complete, read-only copy of all product and customer information by subscribing to data owned by each office.
2. Dynamic ownership
Dynamic ownership, asynchronous updates move replicated data from one node to another, and in the process, it must be ensured that only one specific node at a time can update the data. A typical practical application is "Customs order Processing system", the typical procedure of order processing is, the declaration → agree → shipment → open a single → receive a bill → Enter account etc. The application model can perform any of these steps, but it must be ensured that the same data for each of the different modules should be in a consolidated database. In this system, the next application module can perform an operation to update order data only if the order's status indicates that the previous step is complete. For example, a "shipping" application module can only be executed if it is "declared" and "agreed". (A simplified version demo program is available in the demo program in Delphi)
By using the dynamic ownership replication model, a system can be distributed across multiple nodes and databases, which means that the application model can be built on different systems. Let's take a look at the above example, "customs" and "approval" of the two parts can be run on a system, "shipment" on another system, "Billing" and "receipt", etc. can be on a system. In this distributed system, data can be replicated to different nodes, which have read-only access to replicated data. For example, using replication, the "Customs" node can monitor the order processing process that has been declared.
The primary node ownership and dynamic ownership replication model discussed earlier has a common feature: at any given point in time, only one node is allowed to have the right to update data, while the remaining nodes have only read-only access to replicated copies of the data. However, there is a situation where multiple nodes can update the same data, in extreme cases at the same time, which is said to share ownership.
3. Share Ownership
Shared ownership uses asynchronous replication, which has some limitations for both the master node and the dynamic ownership model. In the case of shared ownership, there can be temporary inconsistencies across the system, and conflict detection and elimination must be used. The shared ownership model has some new features relative to the previous two models.
For example, for the one we discussed earlier, a distributed "Order Processing system" with a horizontally divided master-node replication scheme. With a shared ownership model, each sales office has a different level of division in the library table that contains some of the order information and customer-related information that each office serves. Each sales office enters only customer orders that are relevant to itself, regardless of the rest of the customer's information.
However, in practical business applications, this model is not the best choice. In this case, for example, a retail group can have several malls in a big city. Customers may often go to the nearest store where they live, but occasionally they may go shopping at other malls. At this time, if more than one store to update the same customer and their order data information, update conflict will occur. So in this case the conflict detection and elimination scheme must be adopted.
Oracle advanced replication Support replication models that consider both conflict avoidance and conflict detection and elimination. One of the reasons why Oracle Advanced Replication is also known as symmetric replication is to support the symmetric replication model, which means that all replication nodes can be updated at any point in the process. One way to avoid conflicts is to design the application to limit updates to specific nodes. For shared ownership, Oracle provides automatic conflict detection and elimination scenarios.
4. Standby Database (Fail-over)
Asynchronous replication, by replicating one Master system data to another, can provide protection against the crash of the primary system, which is called the Fail-over system, and the business can still be replicated through the database if there is a problem with the main system.
Oracle advanced replication supports this fail-over configuration. Oracle also provides two alternative solutions for Fail-over: Oracle parallel Server and standby database configuration. Users can weigh the choice of appropriate fail-over scenarios based on throughput, availability, the likelihood of transaction loss, and other reference metrics such as data consistency and methodological limitations.
3.Oracle Advanced Replication Configuration
Oracle advanced replication can support replication based on an entire table or two replication scenarios that are based on partial-table replication. These two replication scenarios are primarily done through Oracle's two replication mechanisms, namely, multiple master replication and updatable snapshot replication, while also combining these two replication mechanisms to meet changing business requirements.
1. Multi-master replication
The Multiple master replication scheme supports symmetric replication of the entire table across the main nodes, allowing all master nodes to have the right to update operations on the primary table. Updates to replicated tables on any one of the primary nodes are propagated and applied directly to all other primary tables. A problem with a master node does not affect the propagation of changes between other master nodes.
Multi-master replication uses a mechanism known as the "deferred remote procedure call (deferred remote procedure calls RPCs)" as the main mechanism for communication and application changes. The propagation of changes between nodes can be propagated either in an event-based fashion or at a particular point in time, such as when the network is idle (such as night). If one of the remote systems is not ready to propagate the change, the delayed remote procedure call (RPCs) that propagates the changes is saved in its local queue until the system is ready for execution.
2. Updatable Snapshots
Oracle extends the read-only snapshot mechanism on the earliest Oracle 7 to a symmetric replication mechanism that allows snapshots to be updatable. How the snapshot update is propagated and how it is applied to the snapshot master uses the same delayed remote procedure invocation mechanism as the multiple master replication.
The definition of a snapshot can be either a full copy of a primary table or a subset of the rows in the primary table that meet the criteria for selection based on the value. The snapshot is refreshed at the primary node at a certain time interval or on a separate request by the user. Any changes to the primary table that were last refreshed are also propagated and applied to the snapshot. The refresh of multiple snapshots is done in a consistent transaction, which ensures the integrity of the data and references.
3. Mixed configuration
Multiple master replication and updatable snapshot replication can be combined to form a new hybrid configuration that completes replication of a full table or child table. For example, the following application is a typical hybrid configuration scenario, a system with two central nodes located in different geographical regions, and there are branches below the two different geographical regions, and two central nodes can be considered as their own backup nodes. A multiple-master replication method is used to replicate data between two central sites, using a read-only or updatable snapshot replication method to replicate a full table or a child table between the master nodes in each zone range. One of the notable benefits of this configuration is that when one of the central nodes has a problem, the primary node of the snapshots can be redefined to another well functioning central node, which improves the reliability of the system.
4. Other replication mechanisms
In addition to the three replication mechanisms discussed earlier, Oracle provides two other replication mechanisms: process-level replication and synchronous replication.
(1) Process-level replication: This replication scheme is mainly used when there is a large number of data updates and the use of batch processing to manipulate data needs to replicate data. For example, the following application, a company that manages customer orders in years, does not need to be stored online for old order data and needs to be unloaded to a storage medium. At this point, it would be very inefficient to replicate each independent row-level change across multiple nodes. In this case, a procedure can be executed directly at each replication node to perform this update directly. The distributed mode management capabilities provided by Oracle advanced replication, which will be discussed in detail below, make it easy to set up and maintain remote procedures for process-level replication on multiple nodes.
(2) Synchronous replication: When a table with a synchronous replication scheme changes, Oracle ensures that the change succeeds in the replication table of the local table and other nodes, and that the entire transaction is successfully rolled back if the failure occurs. Synchronous replication is feasible in the case of high stability of the network, which can ensure that the replicated data between the replicated nodes is kept synchronized.
4.Oracle Management Tools
It is clear that effective management tools are critical to successful distributed applications, and that a professional copy function does not work well without the ability to operate it. Oracle Advanced replication offers several powerful management tools, such as replication catalogs, distributed mode management, and other Oracle replication management tools.
1. Copy Directory
A replicated directory is a single consolidated library that contains the original data for a replication environment. The replication directory itself is also replicated to multiple nodes to ensure that high availability and authorized users can simply implement local access management.
The replication directory defines the replicated database objects, the replicated nodes, and the mechanisms used for replication. database objects have library tables and other related objects that need to be replicated, such as indexes, views, procedures, triggers, synonyms, and so on. The original data is open and can be retrieved and queried by standard SQL statements.
2. Distributed mode Management
The distributed mode management capabilities of Oracle Advanced replication allow replication environments to be defined and changed at one control point. Any changes to the replication environment will automatically replicate and apply these data definition language (DDL) commands to other replication environment nodes, while also leveraging database triggers and replication-related procedures to automatically generate some of the support objects needed for replication.
Distributed mode management is controlled in a place called the Master definition node. It automatically pushes the DDL to all other master nodes and also allows the snapshot nodes to "pull" back and execute the DDL commands. Because the primary definition node uses and maintains the entire replication directory, the replicated directory itself is replicated to other nodes, so the primary definition node must be replaced with a different node once the problem occurs. Distributed mode management can be configured through a graphical user interface management tool (GUI).
3. Oracle Replication Manager
Oracle Replication Manager is a graphical interface replication management tool provided by Oracle that allows you to configure, schedule, and manage the entire replication environment through a single node. The administration tool can be run separately or be activated through an applet in Oracle Enterprise Manager.
With Oracle Replication Manager, the group of objects used for replication can be defined very simply with some mouse actions. A replication group can contain tables and other related objects, such as triggers, stored procedures, indexes, views, synonyms, and sequences. Oracle can also select a subset of replicated data in addition to replicating all copies of each replication group. The configuration of a replication group can be modified at any time, and this change is automatically applied to all other replication nodes.
Once you have defined groups of objects, you can automate the deployment of replication groups to a new node by simply dragging through the Oracle Replication Manager. These snapshot nodes can be either read-only or updatable. After the replication node configuration is complete, users can configure timing to propagate changes in the replication environment to other nodes, and of course, you can choose to use synchronous replication.
4. Choose a conflict resolution
If you choose to adopt a replication scheme that can be updated anywhere, Oracle Replication Manager provides several built-in conflict solutions, such as "recent timestamp" and "site first", to resolve potential conflicts. You can choose different methods for different tables, and you can even choose different conflict resolution schemes for different columns in one table. Users can also create their own conflict solutions for specific business needs.
5. Real-time management
With Oracle's replication Manager, errors occurring anywhere in the replication environment can be quickly and easily separated and corrected. With a single node, the user can see the deferred transaction queue for each node, or it can be timed or forced to execute the transaction immediately. Additionally, users can take advantage of the event management capabilities of Oracle Enterprise Manager to monitor the replication environment, which provides proactive monitoring of replication status between multiple sites, as well as options to define automatic corrections.
5. The main configuration parameter says the example
Oracle advanced replication, also known as symmetric replication. Unlike Oracle's other features, Oracle's advanced replication is not Plug and play, and users must have a deep understanding of how they work and the advantages and disadvantages of various replication scenarios. A deep understanding of the basic concepts of replication can help you design a reliable replication environment.
The next step is to introduce some of the system parameters that need to be set up to build an Oracle advanced replication environment. It should be noted that the specific configuration values of these system parameters can only guarantee a relatively good advanced replication environment, but they are not further discussed in detail in the optimization configuration of these parameters.
Now we're officially starting. Suppose there are two nodes, and the section names are RLDBA and Xjtu respectively.
Database name Rldba XJTU
Database domain Hr_group World
Database Sid Rep.world Xjtu.world
Listener Port number 1521 1521
Server IP Address 192.168.110.221 192.168.110.130

1. Prerequisites for Implementing database replication
The Q database supports advanced replication features: You can log on to the database with system identity, view the V$option view, and support advanced replication if the advanced replication is true, otherwise it is not supported.
Q Database initialization parameter requirements:
(1) Db_domain = test.com.cn: Indicates the domain name of the database (the default is world), where you can use your company's domain name.
(2) Global_names = true: It requires that the database link and the connected database name be the same. Global database name now: db_name+ "." +db_domain
Q Database Connection unobstructed:
Run $tnsping hr_svr and $tnsping Xjtu, the following prompt appears:
Attempting to contacts
(Address= (PROTOCOL=TCP) (host=10.1.1.200) (port=1521)) OK (n milliseconds) indicates that the database connection is smooth and ready to replicate.
2. System environment parameters (Init.ora file) settings (for Non-default values)
If you want to build an Oracle Advanced replication environment, you must set the following parameters in the initialization file, as shown in table 1.
Parameter name recommended value Note
Processes 100 Process number, default=59 if the number of processes is greater than 59 the database will likely be deadlocked
Shared_pool_size 30000000 At least 30M, if you have a lot of replication objects, you need more space
Large_pool_size 500K
Db_block_buffers 550K
Compatible 8.0.5.0 the version above
Db_file_multiblock_read_count 16
Dml_locks 100
Sequence_cache_entries 30
Sequence_cache_hash_buckets 23
Global_names TRUE
Distributed_lock_timeout 300
Distributed_transactions 10
Open_links 6 Replicate nodes more, need to increase
Sort_area_size 1000000
Db_name RLDBA
Job_queue_processes 6 defines the number of starts of the SNP process as N. The system defaults to 0, the normal definition range is 0~36, depending on the number of tasks, you can configure different values.
Job_queue_interval 60 defines that the system wakes up the process every n seconds. The system defaults to 60 seconds, and the normal range is 1-3,600 seconds. In fact, after the process finishes its current task, it goes to sleep and sleeps for a period of time, and the system's master is responsible for waking it up.
Parallel_max_servers 10 is only suitable for parallel propagation
Parallel_min_servers 2 is only suitable for parallel propagation
Peplication_dependency_tracking true if parallel propagation is used, it must be set to True
(Table 1:oracle Advanced replication Environment initialization parameter settings)
The recommended values for each parameter in table 1 suggest that the current replication environment is only two nodes, a simple advanced replication environment, if there are more nodes, and the replication relationship is complex, need to replicate a lot of tables, you need to increase the value of some parameters, this can be modified by the different parameters of the experiment. If you modify these parameters, you will need to restart the database for the parameters to take effect.

All snapshot or job_queue_keep_connections parameters are removed because they are not supported by Oracle at a later time.

2.net8 parameter file
(1) Sqlnet.ora File settings
# sqlnet. ORA network Configuration file:d:\oracle\ora81\network\admin\sqlnet.ora# generated by Oracle Configuration Toolssqlnet.authentication_services= (NTS) NAMES. Directory_path= (TNSNAMES, Onames, HOSTNAME) Automatic_ipc=offnames.default_domain = Worldname.default_zone = Worldsqlnet.expire_time = 0disable_oob = On

(2) Tnsnames.ora file settings
RLDBA = (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP) (HOST = hr_svr) (PORT = 1521)) (Connect_data = (SERVICE _name = Rep.world))) Xjtu = (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP) (HOST = xjtu) (PORT = 1521)) (CONN Ect_data = (service_name = xjtu.world)))
The host is the IP address for each replication node, and if there are many replicated nodes, all of them are added to the Tnsnames.ora file, which is used as two nodes in this use case.
3. Table Space
If you build an Oracle Advanced replication environment, additional table space is required, and table 2 is the recommended initial tablespace.
Table Space recommended initial value recommendation
SYSTEM at least 20M
ROLLBACK segments at least 20M recommends that the next area of the rollback segment be set at least 100K.
Temporary at least 10M
TOOLS at least 20M
(Table 2:oracle system table space Size setting)
The recommended settings above are a space setting in a smaller replication environment. If you want to copy a lot of tables, a large number of changes per day, replication nodes complex, you need to increase the size of the table space, the specific data according to the specific circumstances.
6. Copy Application Instance
The above is a description of the basic concepts and replication parameters, followed by a practical example of Oracle snapshot replication and its technical implementation details.
(1) Description of business requirements
In a practical database application, such as telecommunications, human resources management applications are usually used in such a solution, in an administrative area, such as a province or a city, in different geographical location to set up several database servers, these different geographical servers have the same background database. In order to maintain the consistency of the database system, the basic data tables for the whole administrative area should be consistent, and it is difficult to maintain the same conflict-less code table without considering data replication. Below we do not consider the actual business application, only from the database replication point of view of the application of Oracle snapshot replication.
In order to maintain the consistency of the basic data table of the whole system, such as the business requirements, some tables on the RLDBA are maintained on a single server. In other locations, such as database xjtu, user snap can use these tables directly, meaning that location 1 has the ability to insert, delete, and update code tables, and to have synchronous query applications elsewhere.
(2) Application design
In response to these requirements, a solution is now proposed, which is to take snapshot replication of Oracle. The specific business implementation plan is designed as follows:
Maintain all tables in the database Rldba at location 1, and create snapshots of all tables in the remaining databases relative to location 1. To maintain the convenience of snapshots, create a separate snapshot table space and a schema (schema) in Location 2, also known as a user, in which other users in the system access these snapshots through a private synonym. Private synonyms are better than public synonyms here because there is a same system in position 1, and its tables are accessed by public synonyms. For snapshot refreshes, the Oracle System Package Dbms_refresh is performed and the refresh process is scheduled to run at 2:00 every day, which reduces network traffic. For the refresh form of the snapshot, a full refresh is selected here because the table is not maintained much and the table has a relatively small amount of data, which avoids the hassle of managing the snapshot log. The following is an example of a Node 2 (XJTU) to illustrate specific technical implementation details.
(3) Technical implementation Details
Unless specifically stated, the following SQL commands are run under the Sysetm user of the database xjtu.

1. By adding the services name of database 1 (RLDBA) to Database 2 (XJTU), you can add the services name of database 1 directly in Tnsnames.ora files, including IP addresses, SIDS, and port numbers. The services name is named Rep.world.

2. Create a database connection (Dblink) to connect to database 1 (RLDBA)
DROP public Database link rep.hr_group@rldba;//Delete db linkcreate Public DATABASE LINK rep.hr_group@rldba CONNECT to rldba identified by admin USING ' RLDBA '; Create Database Linkselect * from CAT@REP.HR_GROUP@RLDBA; Test the Database link
Note: You can use a private data connection for security reasons.
3. Create a tablespace named Snapshot_rldba to hold the RLDBA snapshot and create a user named Snap that is related to the tablespace.
CREATE tablespace "snapshot_rldba" LOGGING datafile ' D:\ORACLE\ORADATA\ORA8I\SNAPSHOT_RLDBA.ora ' SIZE 30MDEFAULT STORAGE (INITIAL 30K NEXT 15K minextents maxextents Unlimited pctincrease 0); Create Table spacecreate USER snap identified by snap DEFAULT tablespace "SNAPSHOT_RLDBA"; Create User in the table Spacegrant CONNECT and RESOURCE to snap; Grant role to the user

4. Run the following script file Snapsql.sql to generate a snapshot script that creates the RLDBA user table on the RLDBA database:
Note if the table in which you want to generate the snapshot contains a column of type long, "SELECT *" will not work here, This SQL script above does not automatically create the script that generates the snapshot you want, you must make a snapshot of the table by explicitly adding a long column name in the select list. Here's an example, if we want to create a snapshot-dependent table t_ygjbxx has a column ZP type long, we need to write out the following script to create the snapshot:
Create SNAPSHOT t_ygjbxx PCTFREE 40TABLESPACE "Snapcost_rldba" STORAGE (INITIAL 40960 NEXT 57344 pctincrease 0) USING INDEX REFRESH with Primery keyfor Ateas Select Ygid, GZBH, FZJRLB, ZP, Sxzy, xcszy from T_YGJBXX@REP.HR_GROUP@RLDBA; Create Snapshot
EXECUTE dbms_snapshot. REFRESH (' snap. T_ygjbxx ')
After running the script file Create_snapshot.sql, the required snapshot is created in the pattern snap. The next step is to consider how to refresh the snapshot. For snapshot refreshes, you can refresh the snapshot with some desktop DBA tools or you can refresh a snapshot through System Package Dbms_snapshot.refresh:
Create or select users and objects that implement database replication, assign rights to users, and database objects must have primary keywords, If the database object does not have a primary key, you can run the following SQL command add:
ALTER TABLE dept Add (constraint DEPT_DEPTNO_PK primary KEY (Deptno));

5. Create a timed refresh process to refresh the snapshot periodically:
--sp_snapshot_refresh.sql create OR REPLACE PROCEDURE Sp_snapshot_refresh isbegindbms_ REFRESH. Make (name=> ' t_ygjbxx ', list=> ' snap.) T_ygjbxx ', Next_date=>trunc (sysdate+1) +2/24, interval=> ' (sysdate+1) ', Implicit_destroy=>false, LAX=> TRUE); End Sp_snapshot_refresh; --A scheduled task is created to refresh the snapshot
EXECUTE sp_snapshot_refresh//execute The procedure
SELECT job at 2:00 every day, WHAT from Dba_jobs; /query just joined this task
CREATE synonym system. T_ygjbxx for SNAP. t_ygjbxx;//creates a private synonym for a snapshot under User system
Grant Select on DM_GY_RYDM to system;//the snap user to grant the system user the right to SELECT the snapshot.

6. The same procedure establishes a table snapshot and a timed refresh task for position 1 (RLDBA) in other locations. This allows for a unified maintenance of the Code table at position 1 and uses the table for location 2 and other locations. As the following SQL statement, at position 2 (xjtu) User snap browses to the code table in position 1 (RLDBA).
SELECT * from T_YGJBXX;
(4) Daily maintenance and precautions
The refresh will fail whenever there is a network connection problem. These error messages can be found in the Alert.log file. Here's a quick way to deal with this problem:
1. First find the task number to refresh the snapshot in the task queue
SELECT JOB, what from Dba_jobs;
2. Delete this task
EXECUTE Dbms_job. REMOVE (Jobno);
3. Delete a snapshot group
EXECUTE Dbms_refresh. DESTROY (' tax_dmb_grp ');
4. Recreate the snapshot group and refresh the snapshot periodically by restarting the task
EXECUTE Sp_snapshot_refresh (' snap. T_ygjbxx ')
5. Snapshot monitoring
SELECT NAME, To_char (Last_refresh, ' Dd-mon-yy HH:MM:SS ') from Dba_snapshots;