Tag: Replicating data

Replicating data via materialized views

Posted on by Osama Mustafa in Database
Sometimes We Need The Same Data For specific Tables On Other Database , What Should I Do ?

As We Know There’s Oracle Solution For this issue :
1- Oracle Stream.
2- Golden Gate.
3-Standby Database.

The Above Solution Depends On what you need . What If I need One Table Or Two Or Three Table on another Database Some will say export/import , Sql Loader .

But why we don’t Use Materialized Views ,The below Steps Shows how to Replicating data via materialized views

The main difference between regular and materialized view is that the latter does not query the original tables for every user request. The materialized view holds a copy of their data instead.

Source Database : Source
Target Database: Target
 

1-create the USERS table and add some sample records inside.

  

SQL> CONNECT SYSTEM/*********@Source
Connected.
SQL> CREATE USER APP1 IDENTIFIED BY APP1;

User created.

SQL> GRANT CONNECT,RESOURCE TO APP1;

Grant succeeded.

SQL> CONNECT APP1/APP1@Source
Connected.
 
SQL> CREATE TABLE USERS (USER_ID INTEGER PRIMARY KEY,
  FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), PASSWORD VARCHAR2(20));

Table created.

SQL> INSERT INTO USERS VALUES (1, 'BRIAN', 'ALDISS', 'PASS1')

1 row created.

SQL> INSERT INTO USERS VALUES (2, 'POUL', 'ANDERSON', 'PASS2')

1 row created.

SQL> INSERT INTO USERS VALUES (3, 'NEAL', 'ASHER', 'PASS3')

1 row created.

SQL> COMMIT

Commit complete.

2-After That We need Move to The Target Database , We need to Create Database Link to make connection between the two databases , editing the TNSNAMES.ORA file and adding a record for Source there

Source =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.11.11.10)
      (PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

 We need to test it by “tnsping Source” .

3-connect to the local database (Target) and create the user account for the APP2 application.

SQL> CREATE USER APP2 IDENTIFIED BY APP2

User created.

SQL> GRANT CONNECT, RESOURCE, CREATE MATERIALIZED VIEW, CREATE DATABASE LINK TO APP2

Grant succeeded.

4-Connect To APP2 (User that we create it On target Database ).

SQL> CONNECT APP2@Target
Enter password:
Connected.

SQL> CREATE DATABASE LINK Source CONNECT TO APP1 IDENTIFIED BY APP1 USING 'Source';

Database link created.

 Now You must be able to Select any table from user APP1 On Source Database .
For Example :

Select * from User@source .

5-After we did the above steps we will create materialized view On target Database : 


SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT
  SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM USERS@Source;

“REFRESH NEXT SYSDATE + INTERVAL ’10’ MINUTE asks the database to refresh the materialized view every 10 minutes”

Notices : There’s Lot Of Attribute for  materialized view You don’t have to use the same Sentence its depend on what you need and how you will use it.

6- To Test It 

On APP2@Target  Users

SQL > SELECT COUNT(*) FROM V_USERS

It Must Give you same Number Of Row In APP1@Source , If you want to test synchronization All you have to do is

On Source Database :

APP1@source

INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

Wait For Sync .

Hope its Simple and Easy 
Osama Mustafa