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
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” .
SQL> CREATE USER APP2 IDENTIFIED BY APP2
User created.
SQL> GRANT CONNECT, RESOURCE, CREATE MATERIALIZED VIEW, CREATE DATABASE LINK TO APP2
Grant succeeded.
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 .
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.
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
Technology Geek 