Overview
| |||||||||||
Types of Replication
Read-Only Materialized Views
 | |||||||||||
Check TNSNAMES.ORA
- On Master (Host=quorum)
DIA1.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = DIA1)
(INSTANCE_NAME = DIA1)
(SRVR = DEDICATED)
)
)
DIA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = DIA1)
(INSTANCE_NAME = DIA1)
(SRVR = DEDICATED)
)
)On Snapshot (Host=diamond)QUO3.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT = 1523))
(CONNECT_DATA =
(SERVICE_NAME = QUO3)
(INSTANCE_NAME = QUO3)
(SRVR = DEDICATED)
)
)
QUO3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT = 1523))
(CONNECT_DATA =
(SERVICE_NAME = QUO3)
(INSTANCE_NAME = QUO3)
(SRVR = DEDICATED)
)
)
Create DB Links
On Master (Host=quorum)sqlplus scott/tiger@QUO3
CREATE DATABASE LINK DIA1 CONNECT TO scott IDENTIFIED BY tiger using 'DIA1';Database link created.On Snapshot (Host=diamond)sqlplus scott/tiger@DIA1
CREATE DATABASE LINK QUO3 CONNECT TO scott IDENTIFIED BY tiger using 'QUO3';
Database link created.desc emp@QUO3;
Name Null? Type
----------------------------------------- -------- ----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Create Snapshot Log
For each table, which should be replicated, a SNAPSHOT LOG must be created (as user, who owns the table).A materialized view log (Snapshot Log) is a table at the materialized view's master site or master materialized view site that records all of the DML changes to the master table or master materialized view. A materialized view log is associated with a single master table or master materialized view, and each of those has only one materialized view log, regardless of how many materialized views refresh from the master. A fast refresh of a materialized view is possible only if the materialized view's master has a materialized view log. When a materialized view is fast refreshed, entries in the materialized view's associated materialized view log that have appeared since the materialized view was last refreshed are applied to the materialized view.On Master (Host=quorum)sqlplus scott/tiger@QUO3
DROP SNAPSHOT LOG ON emp;
CREATE SNAPSHOT LOG ON emp
TABLESPACE tab
STORAGE
(INITIAL 200K
NEXT 200K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0);
Materialized view log created.
Create Snapshot
A snapshot contains on the remote site the data of the master table. All data changes are reflected in the snapshot after a refresh of the snapshot (either triggered manually or automatically).On Snapshot (Host=diamond)sqlplus scott/tiger@DIA1
CREATE SNAPSHOT emp
PCTFREE 15
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE tab
USING INDEX
PCTFREE 0
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE idx
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE+(1/1440) /* 60 SECONDS */
AS SELECT * FROM emp@QUO3;
Materialized view created.
Create Synonym
On Snapshot (Host=diamond)sqlplus scott/tiger@DIA1
CREATE PUBLIC SYNONYM emp FOR scott.emp;Synonym created.Now, you can access the table emp locally which will be automatically refreshed every 60 sec.
Refresh the Snapshot
The snapshot on the remote site must be refreshed regularily. This can be done bullet either by hand after a substantial change on the master site or in regular intervalls.
Manual Refresh
On Snapshot (Host=diamond)sqlplus scott/tiger@DIA1
execute dbms_snapshot.refresh('scott.emp','F');PL/SQL procedure successfully completed.The first parameter is a list of snapshots to be refreshed. The second describes the method, F stands for FAST refresh (only changes in the master table are propagated, C stands for complete refresh.There is also a dbms_snapshot.refresh_all routine. It requires some more privileges.execute dbms_snapshot.refresh_all;
Automatic Refresh
Automatic refresh is realized by parameters for a refresh group or by the definition of the snapshot. In order to run periodoc jobs in the database (as automatic refresh jobs), the ability to run SNP background jobs must be given.
Especially, in the file init<instance>.ora , located the parameterjob_queue_processes = 1must be included (the default is 0) and the database must be restarted! This parameter allows background processes to be executed in one job queue.
Check Automatic Refresh
You may use the following query to check, if automatically refresh works.SELECT SUBSTR(job,1,4) "Job", SUBSTR(log_user,1,5) "User",
SUBSTR(schema_user,1,5) "Schema",
SUBSTR(TO_CHAR(last_date,'DD.MM.YYYY HH24:MI'),1,16) "Last Date",
SUBSTR(TO_CHAR(next_date,'DD.MM.YYYY HH24:MI'),1,16) "Next Date",
SUBSTR(broken,1,2) "B", SUBSTR(failures,1,6) "Failed",
SUBSTR(what,1,20) "Command"
FROM dba_jobs;
Job User Schem Last Date Next Date B Failed Command
---- ----- ----- ---------------- ---------------- - ------ --------------------
2 SCOTT SCOTT 06.09.2005 15:05 06.09.2005 15:06 N 0 dbms_refresh.refresh
Drop Snapshots and Snapshot Logs
On Master (Host=quorum)sqlplus scott/tiger@QUO3
spool drop_snapshot_logs.sql
select 'PROMPT Dropping Snapshot Log for '||MASTER||chr(10)
||'drop snapshot log on '||MASTER||';'
from USER_SNAPSHOT_LOGS;
spool off
@drop_snapshot_logs.sql
PROMPT Snapshot Logs DroppedOn Snapshot (Host=diamond)sqlplus scott/tiger@DIA1
spool drop_snapshots.sql
select 'PROMPT Dropping Snapshot for '||NAME||chr(10)
||'drop snapshot '||NAME||';'
from USER_SNAPSHOTS;
@drop_snapshots.sql
PROMPT Snapshots dropped
Refresh Groups
If the snapshot must obey certain integrity rules, like referential integrity, then the refresh of the snapshot tables must be synchronized. This is achieved by creating refresh groups.dbms_refresh.make(
name => 'MY_GRP',
list => 'emp,dept,bonus,salgrade',
next_date => SYSDATE,
interval => 'SYSDATE + (1/1440)', /* 60 seconds */
implicit_destroy => TRUE, /* delete the group if substracting
the last member */
lax => TRUE, /* delete from other group if already
existing in a group */
rollback_seg => 'RB06'
);
commit;
Multi-Master Replication
You have already seen how to create and use read-only materialized views. They offer the powerful ability to replicate data in tables across separate databases. With multi-master replication, you can replicate more than just database tables. You can replicate:
- Tables
- Indexes
- Procedures, functions, and triggers
- Packages
- User-defined types (Oracle9i)
As always, there are plusses and minuses to using multi-master replication.
The positive benefits of MMR include the following:
- Replicates more objects, including user-defined objects.
- Updates or modifies the objects being replicated. Adding a column to a table at the master definition site can be replicated to other master sites.
- Replicates with any number of other databases. Any master site can replicate with other master sites, updatable Mview sites, and read-only Mview sites.
However, there are some downsides such as:
- Potentially large network bandwidth requirements. Not only does multi-master push and pull changes between sites, it also sends acknowledgements and quite a bit of administrative data.
- Reduced Performance. Complexity and robustness comes at a price. MMR involves the use of triggers and procedures, and this can result in a database performance hit. Depending on how much data you are replicating, this performance hit can be substantial.
- Significant increases in administration requirements. When problems appear in the database, the DBA must insure that replication is not the cause or that the cause is not replicated to other databases. Database performance tuning and problem resolution becomes more complicated by an order of magnitude.
- Database changes require additional planning. Rolling out a new version of an application can be much more difficult. Each new version will require revisiting the design of the replication.
The considerations above should reinforce not to implement a higher level of replication than you need. Multi-master replication is powerful, and it is complicated to create and monitor the replication environment.
Deferred Transaction
A deferred transaction is a transaction that is queued for delivery to one or more remote databases. If you use multi-master replication with asynchronous propagation, Oracle creates deferred transactions for all local DML activity against the replicated tables.
Replication Group
A replication group is a collection of one or more replicated objects (typically tables) that are administrated together. Very generally speaking, the objects in a given replication group are logically related; for example, they are often the set of objects that a given application uses. A given replication group can contain objects from multiple schema, and a given schema can have objects in more than one replication group. However, any given object can be in only one replication group.The most significant property of replication groups is that all objects in a given group are quiesced together. That is, DML activity is enabled and disabled for all group members simultaneously.
Quiescence
Quiescence is the act of suspending DML activity for all tables in a given replication group. This is required in order to perform certain administrative tasks on objects in a replication group, such as altering a table. The Oracle built-in package procedure call that quiesces a replication group is DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY.
Master Definition Site
The master definition site of a replication group is the database instance from which the group is administered. This site is usually, but not necessarily, the site at which the replication group was originally created.
Master Site
A master site is a site that is participating in one or more replication groups but is not the master definition site.
Replication Support
Replication support refers to the packages and triggers that Oracle creates in order to propagate changes to replicated objects, to detect and resolve conflicts, and so on.
Propagation and Conflict
When Oracle propagates an update to destination tables, it expects the current data for the row at the destination to match the data at the originating site prior to the update. If the data is not the same, an update conflict results. Similarly, if an insert fails because of a primary key violation (i.e., a unique constraint violation) the result is a uniqueness conflict or violation. And, if the target row of a delete does not exist at the destination site, a delete conflict results.Unless you are propagating changes among master sites synchronously, there is a delay between the time a DML change is applied at the originating database and the time the transaction reaches the destination databases. This lag is known as propagation latency.
- The Administrator maintains the master group, adds or removes objects, etc.
- The Propagator is responsible for pushing items in the deferred transaction queue to all other master sites.
- The Receiver takes items that have arrived in the deferred transaction queue and applies them to the local objects.
Oracle recommends that you use the Replication Administrator to perform all three tasks when establishing your replication environment. For additional security, you can establish a separate user as the receiver and propagator.
- A master site can have only one propagator.
- A propagator has the "execute any procedure" grant.
- A master site can have multiple receivers.
- A master group can have only one receiver per master site.
- A receiver is not granted "execute any procedure".
Example for a Multimaster Site
Next, we illustrate how to set up both a master site and a materialized view replication site using the replication management API.
Before you build the replication environment, you need to set up the sites that will participate in the replication environment. As illustrated there are separate processes for setting up a master site versus setting up a materialized view site.We use the following databases:
ORA-04052: error occurred when looking up remote object
REPADMIN.SYS@CEL1.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified
export ORACLE_SID=CEL1
sqlplus repadmin/repadminNow create the Replication Group.
We use the simple EMP/DEPT schema from SCOTT/TIGER.sqlplus scott/tiger@CEL1.WORLD
start demobld.sql
ALTER TABLE emp ADD (CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE);
ALTER TABLE dept ADD (CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE);
ALTER TABLE emp ADD (
CONSTRAINT fk_emp_dept FOREIGN KEY (deptno)
REFERENCES dept (deptno) ENABLE);
sqlplus scott/tiger@CEL2.WORLD
start demobld.sql
ALTER TABLE emp ADD (CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE);
ALTER TABLE dept ADD (CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE);
ALTER TABLE emp ADD (
CONSTRAINT fk_emp_dept FOREIGN KEY (deptno)
REFERENCES dept (deptno) ENABLE);
The following sections contain step-by-step instructions for setting up the master sites in the sample replication environment: CEL1.WORLD and CEL2.WORLD. Before you set up the master sites, configure your network and Oracle Net so that all three databases can communicate with each other.
If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database if the instance has not been started up. Therefore, the authentication of the DBA must happen outside of the database.The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.
Default location and file name
The default location for the password file is:
Deleting a password file
If password file authentication is no longer needed, the password file can be deleted and the init parameterremote_login_passwordfile set to none.
Password file state
If a password file is shared or exclusive is also stored in the password file. After its creation, the state is shared. The state can be changed by setting remote_login_passwordfile and starting the database. That is, the database overwrites the state in the password file when it is started up. A password file whose state is shared can only contain SYS.
Creating a password file
Password files are created with the orapwd tool.orapwd file=orapwCEL1 password=manager entries=5 force=yCreate a Symbolic Link from $ORACLE_HOME/dbs to the Password.lrwxrwxrwx 1 oracle dba 39 2005-09-13 16:55 initCEL1.ora -> /home/oracle/config/10.1.0/initCEL1.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 11:22 initCEL2.ora -> /home/oracle/config/10.1.0/initCEL2.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 08:20 initREP1.ora -> /home/oracle/config/10.1.0/initREP1.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 11:22 initREP2.ora -> /home/oracle/config/10.1.0/initREP2.ora
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwCEL1 -> /home/oracle/config/10.1.0/orapwCEL1
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwCEL2 -> /home/oracle/config/10.1.0/orapwCEL2
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwREP1 -> /home/oracle/config/10.1.0/orapwREP1
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwREP2 -> /home/oracle/config/10.1.0/orapwREP2
Adding Users to the password file
Users are added to the password file when they're granted the SYSDBA or SYSOPER privilege.select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
grant SYSDBA to scott;
Grant succeeded.
select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUESCOTT TRUE FALSE
The Network must be setup for all Sites:# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach listener.ora
# --------------------------------------------------------------------------
# File: listener.ora
#
# Autor: Martin Zahn Akadia AG
#
# Purpose: Configuration file for Net Listener
#
# Location: $TNS_ADMIN
#
# Certified: Oracle 10.1.0.4 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
LSNR101 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
)
SID_LIST_LSNR101 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CEL1.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = CEL1)
)
(SID_DESC =
(GLOBAL_DBNAME = CEL2.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = CEL2)
)
(SID_DESC =
(GLOBAL_DBNAME = REP1.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = REP1)
)
(SID_DESC =
(GLOBAL_DBNAME = REP2.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = REP2)
)
)
USE_PLUG_AND_PLAY_LSNR101 = OFF
STARTUP_WAIT_TIME_LSNR101 = 0
LOG_DIRECTORY_LSNR101 = /home/oracle/config/10.1.0
TRACE_FILE_LSNR101 = listener_LSNR101.trc
CONNECT_TIMEOUT_LSNR101 = 10
TRACE_LEVEL_LSNR101 = OFF
SAVE_CONFIG_ON_STOP_LISTENER = OFF# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach tnsnames.ora
# --------------------------------------------------------------------------
# File: tnsnames.ora
#
# Autor: Martin Zahn Akadia AG
#
# Purpose: Configuration File for all Net Clients
#
# Location: $TNS_ADMIN
#
# Certified: Oracle 10.1.0.2 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
#
CEL1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL1.WORLD)
)
)
CEL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL1)
)
)
CEL2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL2.WORLD)
)
)
CEL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL2)
)
)
REP1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP1.WORLD)
)
)
REP1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP1)
)
)
REP2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP2.WORLD)
)
)
REP2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP2)
)
)# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach sqlnet.ora
# --------------------------------------------------------------------------
# File: sqlnet.ora
#
# Autor: Martin Zahn Akadia AG
#
# Purpose: Configuration File for all Net8 Clients
#
# Location: $TNS_ADMIN
#
# Certified: Oracle 10.1.0.2 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
#
TRACE_LEVEL_CLIENT = OFF
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf
AUTOMATIC_IPC = ON
NAMES.DEFAULT_DOMAIN = WORLD
BEQUEATH_DETACH = NO
SQLNET.EXPIRE_TIME = 10
NAME.DEFAULT_ZONE = WORLD
USE_DEDICATED_SERVER = ON
Setup INIT.ORA Parameters
global_names = TRUE db_name = CEL1 db_domain = WORLD instance_name = CEL1 service_names = CEL1.WORLD remote_login_passwordfile = exclusive job_queue_processes = 10 parallel_min_servers = 2 parallel_max_servers = 10
The following Figure shows the needed Steps to setup the users at the Master Sites.
No comments:
Post a Comment