Pre-install Steps:
1- Install Oracle Software and Create two databases on the two servers
2- Install Oracle 10.2.0.3 Patchset on the two databases
3- Create the Schema you want to replicate on both databases (Scott)
4- Import data to the two schemas
5- The Source database called ORCL1 and the Destination database is ORCL2
6- Global_names parameter is set to FALSE
7- Put the two databases in Archivelog Mode
8- Modify these parameters at both databases
Conn / as sysdba
ALTER SYSTEM SET GLOBAL_NAMES=TRUE SCOPE=BOTH;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=20 SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=30 SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_MIN_SERVERS=4 SCOPE=BOTH;
ALTER SYSTEM SET STREAMS_POOL_SIZE=300 SCOPE=BOTH;
ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=BOTH;
ALTER SYSTEM SET aq_tm_processes=1 SCOPE=BOTH;
ALTER SYSTEM SET OPEN_LINKS=6 SCOPE=SPFILE;
ALTER SYSTEM SET PROCESSES=200 SCOPE=SPFILE;
ALTER SYSTEM SET SESSIONS=200 SCOPE=SPFILE;
Shutdown immediate
Startup
Installation Steps
1. Create Streams Administrator at Source and Destination Databases
1.1 Create STRMADMIN user at source database
conn / as sysdba
alter database rename global_name to ORCL1.ORACLE.COM;
create tablespace streamadm datafile
'D:\oracle\product\10.2.0\db_2\oradata\ORCL1\streamadm01.dbf' size 100m;
create user strmadmin
identified by strmadmin
default tablespace streamadm
temporary tablespace temp;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'strmadmin',
grant_privileges => true);
END;
/
1.2 Create STRMADMIN user at Destination database
conn / as sysdba
alter database rename global_name to ORCL2.ORACLE.COM;
create tablespace streamadm datafile
'D:\oracle\product\10.2.0\db_2\oradata\ORCL2\streamadm01.dbf' size 100m;
create user strmadmin
identified by strmadmin
default tablespace streamadm
temporary tablespace temp;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'strmadmin',
grant_privileges => true);
END;
/
2. Create Database Links at Source and Destination Databases
2.1. Create Database Links at Source Database
connect strmadmin/strmadmin
CREATE DATABASE LINK "ORCL2.ORACLE.COM" CONNECT TO strmdmin IDENTIFIED BY strmadmin;
2.2. Create Database Links at Destination Database
connect strmadmin/strmadmin
CREATE DATABASE LINK "ORCL1.ORACLE.COM" CONNECT TO strmdmin IDENTIFIED BY strmadmin;
3. Create The Streams Queue :
connect STRMADMIN/STRMADMIN
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
queue_name => 'STREAMS_QUEUE',
queue_user => 'STRMADMIN');
END;
/
4. Steps to be carried out at the Destination Database ORCL2.ORACLE.COM
4.1 Add apply rules for the Schema at the destination database :
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'SCOTT',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ORCL1.ORACLE.COM');
END;
/
4.2 Specify an 'APPLY USER' at the destination database:
This is the user who would apply all DML statements and DDL statements. The user specified in the APPLY_USER parameter must have the necessary privileges to perform DML and DDL changes on the apply objects.
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'STRMADMIN_APPLY',
apply_user => 'SCOTT');
END;
/
4.3 Start the Apply process :
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_APPLY';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_APPLY');
end if;
END;
/
5. Steps to be carried out at the Source Database ORCL1.ORACLE.COM
5.1 Move LogMiner tables from SYSTEM tablespace:
By default, all LogMiner tables are created in the SYSTEM tablespace. It is a good practice to create an alternate tablespace for the LogMiner tables.
CREATE TABLESPACE LOGMNRTS DATAFILE ' D:\oracle\product\10.2.0\db_1\oradata\ORCL\logmnrts.dbf' SIZE 25M AUTOEXTEND ON
MAXSIZE UNLIMITED;
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/
5.2 Turn on supplemental logging for The Source Database :
If the number of tables are more the supplemental logging can be set at database level .
connect / AS SYSDBA
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
5.3 Add capture rules for the schema CM at the source database:
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'SCOTT',
streams_type => 'CAPTURE',
streams_name => 'STREAM_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'ORCL1.ORACLE.COM');
END;
/
6. Add propagation rules for the schema CM at the source database.
This step will also create a propagation job to the destination database.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'SCOTT',
streams_name => 'STREAM_PROPAGATE',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@ORCL2.ORACLE.COM',
include_dml => true,
include_ddl => true,
source_database => 'ORCL1.ORACLE.COM');
END;
/
7. Manaually instantiating the objects from Source to Destination Database
7.1. Get the Instantiation SCN at the source database:
connect STRMADMIN/STRMADMIN
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
/
7.2. Instantiate the objects at the destination database with this SCN value.
The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR. Else, the apply process applies the LCR.
connect STRMADMIN/STRMADMIN
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
SOURCE_SCHEMA_NAME => 'SCOTT',
source_database_name => 'ORCL1.ORACLE.COM',
instantiation_scn => &iscn );
END;
/
Enter value for iscn:
8. Start the Capture process
begin
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STREAM_CAPTURE');
end;
/
The setup is now ready to replicate data between the two databases using Oracle Streams.
Any DDL and DML objects in SCOTT schema will now be proagated to destination database.
No comments:
Post a Comment