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.
|
1 2 3 4 5 6 7 |
CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE ] ON [COMMIT | DEMAND ] [[ENABLE | DISABLE] QUERY REWRITE] AS SELECT ...; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[oracle@dba11 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 4 05:11:32 2001 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ----- -------------- ---------------- PROD READ WRITE PRIMARY SYS>>exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Go to NETWORK ADMIN LOCATION: [oracle@dba11 ~]$ cd $ORACLE_HOME/network/admin [oracle@dba11 ~]$ vi listener.ora target= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11 )(PORT =1523)) ) ) ) SID_LIST_target= (SID_LIST = (SID_DESC = (SID_NAME =prod) (ORACLE_HOME = /oraeng/app/oracle/product/12.1.0) ) ) [oracle@dba11 ~]$ lsnrctl start target The command completed successfully. |
CLIENT SIDE CONFIGURATION:
Configure TNSNAMES.ora file at client side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DBA:12: CLIENT SIDE: [oracle@dba12 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 4 05:11:32 2001 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Go to NETWORK ADMIN LOCATION: [oracle@dba12 ~]$ cd $ORACLE_HOME/network/admin [oracle@dba12 ~]$ vi tnsnames.ora to_target= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT =1523)) ) (CONNECT_DATA = (SID =prod) ) ) [oracle@dba12 ~]$ tnsping to_target TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-JAN-2018 15:03:59 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT =1523))) (CONNECT_DATA = (SID =prod))) OK (0 msec) |
SERVER SIDE CONFIGURATION:
Connect to database user and select one table to replicate data into client side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SYS>>conn u1/u1 Connected. U1>>select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ -------- --------- ----- --------- ----- ----- ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. Try to INSERT SOME ROWS: U1>>insert into emp select * from emp; 14 rows created. U1>> commit; U1>> select count(*) from emp; count(*) -------- 28 |
CLIENT SIDE CONFIGURATION:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Try To Connect Server User:(sqlplus u1/u1@to_target) where U1 is a Server user and to_Target is a alias name. [oracle@dba12 ~]$ sqlplus u1/u1@to_target SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 20 15:23:03 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sat Jan 20 2018 15:13:31 +05:30 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options U1>>select count(*) from emp; COUNT(*) ---------- 28 |
Try to Create Normal User with required privileges in Client side:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SYS>>grant connect,resource,unlimited tablespace to dl identified by dl; Grant succeeded. Try to Create DATABASE LINK privilege: SYS>>grant create database link to dl; Grant succeeded. NOTE: Connect,Resource are ROLES. Unlimited tablespace is Privilege. create Database Link is Privilege. Try to Connect DL User: SYS>>conn dl/dl Connected. Check the privileges: DL>>select * from session_privs; PRIVILEGE SET CONTAINER CREATE INDEXTYPE CREATE OPERATOR CREATE TYPE CREATE TRIGGER CREATE PROCEDURE CREATE DATABASE LINK CREATE SEQUENCE CREATE CLUSTER CREATE TABLE UNLIMITED TABLESPACE CREATE SESSION 12 rows selected. DL>>conn / as sysdba Connected. SYS>>sho parameter global_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ global_names boolean FALSE NOTE: Up to 11g global_names parameter default was True. But in 12c global_names parameter default is False. |
DBA:12:CLIENT SIDE CONFIGURATION:
Create database link and try to select server side table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SYS>>conn dl/dl Connected. Try to Create Database Link: DL>>create database link db01 connect to u1 identified by u1 using 'to_target'; Database link created. WHERE DB01 -> DATABASE LINK NAME DL -> CLIENT SIDE USER U1 -> SERVER SIDE USER TO_TARGET -> NETWORK ALIAS NAME. DL>>desc user_db_links; Name Null? Type ----------------------------------------- -------- ---------------------------- DB_LINK NOT NULL VARCHAR2(128) USERNAME VARCHAR2(128) PASSWORD VARCHAR2(128) HOST VARCHAR2(2000) CREATED NOT NULL DATE DL>>select db_link,username,password,host,created from user_db_links; DB-Lin User-Name PASSWORD HOST CREATED ------ ---------- ---------- ---------- --------- DB01 U1 to_target 22-JAN-18 DL>>select count(*) from emp@db01; count(*) -------- 28 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
DL>>insert into emp@db01 select * from emp@db01; 28 rows created. DL>>/ 56 rows created. DL>>commit; Commit complete. DL>>select count(*) from emp@db01; COUNT(*) ---------- 112 |
NOTE:
Try to Check these updated records are transferred or not, by using REMOTE CONTROLLING.
SERVER SIDE :
|
1 2 3 4 |
U1>>select count(*) from emp; COUNT(*) ---------- 56 |
DBA:12 CLIENT SIDE CONFIGURATION:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Try to assign privilege and Create Materalized view under DL user. [oracle@dba11 ~]$ sqlplus / as sysdba Try to assign and Create Materalized view: SYS>>grant create materialized view to dl; Grant succeeded. SYS>>conn dl/dl Connected. MATERIALIZED VIEW CREATION WITH COMPLETE OPTION: DL>>create materialized view mv1 refresh complete start with sysdate next sysdate+1/(24*60*60) as select * from emp@db01; Materialized view created. |
Now select the data from Materialized view.
DL>>select * from mv1;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ -------- --------- ----- --------- ----- ----- ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 NOTE : IT WILL SHOW 112 records |
We can check materialized views under a user by using following data dictionary view.
|
1 2 3 4 5 |
DL>>desc user_mviews; DL>>select owner,mview_name from user_mviews; OWNER MV_NAME -------- ------------ DL MV1 |
DBA:11 SERVER SIDE CONFIGURATION :
Try to Update any column in emp table:
|
1 2 3 4 5 |
SYS>>conn u1/u1 U1>>update emp set sal=8000 where ename='KING'; 4 rows updated. U1>>commit; Commit complete. |
Check the records are updated or not at client side materialized view.
DBA:12: CLIENT SIDE CONFIGURATION :
|
1 2 3 4 5 6 7 8 |
Try to Check either Column value updated or not: DL>>select * from mv1 where ename='KING'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ -------- --------- ----- --------- ----- ----- ------- 7839 KING PRESIDENT 17-NOV-81 8000 10 7839 KING PRESIDENT 17-NOV-81 8000 10 7839 KING PRESIDENT 17-NOV-81 8000 10 7839 KING PRESIDENT 17-NOV-81 8000 10 |
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.
Loading...
