Scribd
Upload
114 views3 pages

Table Replication Using Materialized View in Oracle 11g

This document describes how to set up table replication between an Oracle source and target database using a materialized view. It involves: 1. Creating a table on the source database and adding sample records. 2. Configuring the tnsnames.ora file on the target database to connect to the source. 3. Creating a database link on the target to connect to the source as a user. 4. Creating a materialized view on the target database that refreshes from the source table using the database link, keeping them synchronized.

Uploaded by

Anonymous 8RhRm6Eo7h
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
Download as docx, pdf, or txt
114 views3 pages

Table Replication Using Materialized View in Oracle 11g

This document describes how to set up table replication between an Oracle source and target database using a materialized view. It involves: 1. Creating a table on the source database and adding sample records. 2. Configuring the tnsnames.ora file on the target database to connect to the source. 3. Creating a database link on the target to connect to the source as a user. 4. Creating a materialized view on the target database that refreshes from the source table using the database link, keeping them synchronized.

Uploaded by

Anonymous 8RhRm6Eo7h
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1/ 3

Table replication using Materialized View in oracle 11g

Source Database:prim
Target Database:-stand

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

At source database:-
[oracle@server1 ~]$ sqlplus sys/system as sysdba@prim
SQL> create user appuser identified by appuser;
SQL> GRANT CONNECT,RESOURCE TO appuser;
SQL> conn appuser/appuser
Connected.

SQL> insert into users values (1 ,'RAM','DAS','REDHAT');


SQL> insert into users values (2, 'SHYAM','SINGH','SUSE');
SQL> insert into users values (3, 'ROHAN','SHARMA','UBUNTU');
SQL> COMMIT;
Commit complete.

At target database:-
Step 2.Now before creating database link we need to add entry inside tnsnames.ora file
so that the target database can connect to the source database.

[oracle@server1 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora


STAND =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stand)
)
)

prim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)
:wq
so we added the tnsentry for source database i.e. prim inside tnsnames.ora file

Step 3.Now connect to the local database i.e. target and create a user appuser2

[oracle@server2 ~]$ sqlplus sys/system as sysdba@stand


SQL> create user appuser2 identified by appuser2;
SQL> grant connect , resource , create database link , create materialized view to appuser2;
SQL> conn appuser2/appuser2
Connected.

At target database:-
Step 3.Now create a database link to connect source database from appuser2
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
SQL> CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser
USING 'prim';
CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING
'prim'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

While creating the database link i was facing the above error and i fixed it using the below steps
SQL>select * from global_name;
STAND
-- write down the current name;

SQL> Alter database rename global_name to tttt.xxx;

SQL> CREATE DATABASE LINK mylink CONNECT TO appuser IDENTIFIED BY appuser


USING 'prim';

Rename global name back to original from the query above.

SQL> Alter database rename global_name to STAND.soumya.com;

Now You must be able to Select any table from user APP1 On Source Database from target
database.
At target database:-
[oracle@server2 ~]$ sqlplus appuser/appuser@prim
SQL> select * from users;

USER_ID FIRST_NAME LAST_NAME PASSWORD


---------- ---------- ------------------------------ --------------------
1 RAM DAS REDHAT
2 SHYAM SINGH SUSE
3 ROHAN SHARMA UBUNTU

Step 4.Now 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@mylink;

Step 5. Now test the scenario:-


[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
At target database:-
SQL> select count(*) from V_USERS;
It Must Give you same Number Of Row In appuser@prim , If you want to test synchronization
All you have to do is

At source database:-
SQL> conn appuser/appuser
SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

And then check at target database after 10mins.It would be in sync.

You might also like