IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS.

IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS.

 

Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing. In data warehouses, materialized views are used to compute and store aggregated data such as sums and averages.

In distributed environments, materialized views are used to replicate data at distributed sites and synchronize updates done at several sites with conflict resolution methods.

Note:

The keyword SNAPSHOT is supported in place of  MATERIALIZED VIEW for backward compatibility.

Using materialized views against remote tables is the simplest way to achieve replication of data between sites.

MView log : A materialized view log is a schema object that records changes to a master table‘s data so that a materialized view defined on the master table can be refreshed incrementally.

The full syntax description for the CREATE MATERIALIZED VIEW command is available in the documentation. Here we will only concern ourselves with the basics.

The BUILD clause options are shown below.

  • IMMEDIATE : The materialized view is populated immediately.
  • DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.

  • FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
  • COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
  • FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

  • ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
  • ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
  • Create a materialized view log as:
  • SQL> CREATE MATERIALIZED VIEW LOG ON sales_master;
  • Start the CJQ Processes as:
  • SQL> ALTER SYSTEM SET job_queue_processes=5;
  • Or include the parameter in the init.ora as:
  • JOB_QUEUE_PROCESSES = 5
  • So, the CJQ processes fetch the data from the materialized view logs and refresh them in the materialized view at the specified refresh intervals.

 

PROCEDURE:

1. In order to achieve stream replication first we shall create a DB-Link so that we can connect to different databases.

2. Take two terminals DBA11  (server) and DBA12 (client)

3. Configure listener and alias name on both the server and client

4. Pick one user from DBA11 (server) and let us assume that a user U1 wants to access DL user data who is at dba12  (client) location.

5. Create DB-Link and on client (dba12) create a materialized view with refresh complete or Refresh fast option

6. Update the table values at server side and observe the change over the client side the updated rows are seen here.

IMPLEMENTATION:

SERVER SIDE CONFIGURATION:

DBA:11: LOCAL MACHINE:

CLIENT SIDE CONFIGURATION:

Configure TNSNAMES.ora file at client side.

SERVER SIDE CONFIGURATION:

Connect to database user and select one table to replicate data into client side.

CLIENT SIDE CONFIGURATION:

Try to Create Normal User with required privileges in Client side:

DBA:12:CLIENT SIDE CONFIGURATION:

Create database link and try to select server side table.

NOTE :

WHERE EMP@DB01 -> WE ARE GETTING THE EMP TABLE DATA FROM SERVER USER BASED ON DATABASE LINK.

Try to Insert some rows on EMP TABLE:

NOTE:

Try to Check these updated records are transferred or not, by using REMOTE CONTROLLING.

SERVER SIDE :

DBA:12 CLIENT SIDE CONFIGURATION:

Now select the data from Materialized view.

DL>>select * from mv1;

We can check materialized views under a user by using following data dictionary view.

DBA:11 SERVER SIDE CONFIGURATION :

Try to Update any column in emp table:

Check the records are updated or not  at client side materialized view.

DBA:12: CLIENT SIDE CONFIGURATION :

Whenever we performing any type DML operations on server side EMP table,The new committed data will be replicated to client side materialized view.

The user can select the data from Materialized view.

 

 

 

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 5.00 out of 5)
Loading...

Add Comment