IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS.

Share via:

Post Views: 0

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.

CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

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:

[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.

[oracle@dba11 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 4 05:11:32 2001

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.

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)

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

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

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.

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

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(*)

--------

CLIENT SIDE CONFIGURATION:

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 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

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(*)

----------

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

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.

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.

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

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(*)

--------

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:

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

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 :

U1>>select count(*) from emp;
COUNT(*)
----------
56

U1>>select count(*) from emp;

COUNT(*)

----------

DBA:12 CLIENT SIDE CONFIGURATION:

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.

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)

select * from emp@db01;

Materialized view created.

Now select the data from Materialized view.

DL>>select * from mv1;

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

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.

DL>>desc user_mviews;
DL>>select owner,mview_name from user_mviews;
OWNER      MV_NAME
-------- ------------
DL          MV1

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:

SYS>>conn u1/u1
U1>>update emp set sal=8000 where ename='KING';
4 rows  updated.
U1>>commit;
Commit complete.

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 :

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