1 . Replication of one table using a set
of Smenu shortcuts.
Here are the
sequences of actions to setup an replication using Streams and AQ, with
switch ownership for the table from user USERA [db POLDEV] to user
USERB [db
RMANTST]. In this example we will replicate the table CUSTOMER.
Using this sequence
plus 5 shorcuts are all you need to know to setup this replication. The
Sames shortcuts are also used to monitor and provide infomartion on the
setup and status/count of the differenct queues. Type 'rul -s' to
have this sequence. You can perform all action with
shortcuts except the creation of the user Strmadmin which is done in
the smenu 3.8.7
The 5 shortcuts
are:
- aq
: Everything
related to queue, incuding the queues counters
- prop
: Check your
propagation statuts
- cap
: Everything
related to capture process. be aware that many objects in the capture
are in fact a rule
- app
: Same as
capture, watch the rule!
- rul
: Rules and
rulesets
Before setting we check with 'pars' the setting for streams replication
: [POLDEV]> pars
[RMANTST> pars
Steps
|
SOURCE
DB
|
TARGET
DB |
Shortcut &Source
|
Smenu
Command
|
Check
|
1
|
Create admin user
|
|
|
(smenu/3.8.7) |
/
|
2
|
|
Create admin user
|
|
(smenu/3.8.7) |
/
|
2b
|
Create a dblink for the admin
user and target the other admin user.
|
|
|
DB link must ave target DB name.
Mandatory for global_name=true
|
|
3
|
Create queue
|
|
aq
|
aq -create -u strmadmin -qn Q_SENDER -x
|
aq -l
|
4
|
|
Create queue
|
aq
|
aq -create -u strmadmin -qn Q_RECEIVER -x |
aq -l
|
5
|
Create ruleset
|
|
rul
|
rul -create -rs RS_CAP_USERA -u strmadmin -x
|
rul -ls
|
6
|
Create capture
|
|
cap
|
cap -create -cn cap_tbl_mytable -u
strmadmin
-rs rs_cap_usera -qn
q_sender -u strmadmin -x
|
cap -l
|
7
|
Create a rule for capture
|
|
rul |
rul -create -cap -rn RUL_CAP_CUSTOMER
-t mytable-so USERA -src_sid
POLDEV -x
|
rul -l
|
8
|
Add capture rule to rule set
|
|
rul |
rul -add -rn RUL_CAP_CUSTOMER -rs
RS_CAP_USERA
-u STRMADMIN -v
|
rul -l
|
9
|
Create propagation
|
|
prop
|
prop -create -u strmadmin -pn PROP_A_TO_B
-sourceq Q_SENDER -destq
Q_RECEIVER -dblk rmantst -x
|
prop
-l
|
10
|
|
Create generic function to
switch objetcs ownership
in queue from source
owner to target owner.
|
rul |
rul -switch -so USERA -to USERB -src_sid
POLDEV
|
output
|
11
|
|
Create a table apply rule
that uses this function
|
rul |
rul -apl_sw -t CUSTOMER -so USERA -to USERB
-src_sid POLDEV -sn
STRM_APL_TO_USERB
-qn Q_RECEIVER -u STRMADMIN
-x
|
app -l
rul -ls
rul -l
|
12
|
|
Grant execute to target user
on conversion fonction (cf.10)
|
rul |
rul -grant -sn STRM_APL_TO_USERB -to USERB
-src_sid POLDEV -x
|
output
|
13
|
Prepare source instantiation
|
|
cap |
cap -si -so USERA -x
|
cap -i
|
14
|
Instantiate the table
|
|
cap |
cap -ti -so USERA -t CUSTOMER -v -dblk
rmantst -x
|
cap -i
|
15
|
|
Don't disable the queue on error
|
app
|
app -dis_on_err N -sn STRM_APL_TO_USERB -x
|
|
16
|
|
Start apply process on target
|
app |
app -start -an STRM_APL_TO_USERB -x
|
app -l
|
17
|
start the capture process
|
|
cap |
cap -start -cn CAP_TBL_CUSTOMER -x
|
cap -l
|
2 .Parameter of functions
The parameters for each function called by one of the 5 shortcuts is
listed here. Click on the first column of the above table to see name a
number of these parameters. The last column will give you an idea of
the expected output of the command togother with a print of the real
command.
i) [S] -- create admin user at source
(smenu/3.8.7) [S]
ii) [T] -- create admin user at target
(smenu/3.8.7) [T]
a) Create queues
I) --
create queue at db source
(aq)
*
queue owner
*
queue name
II) --
create queue at db target
(aq)
*
queue owner
*
queue name
b) Create Capture:
III) -- create
ruleset (rul)
*rule set name
*rule set owner
IV) -- Create
capture
(cap)
*queue_name
*capture_name
*rule_set_name
V) -- Create
rule for capture (rul) + grant all on
<table> to strmamdin
* rule name
* object name
* object owner
* source DB
VI) -- Add
capture rule to ruleset. (rul)
* rule name
* rule owner
* rule set name
VII) --
Create propagation between source and target (prop)
* source queue
* target queue
* owener (of queues and prop)
* propagation name
c) Create Apply:
VIII) -- Create
generic switch data ownership function (rul)
* source owner
* target owner
* source db
IX) --
Create a stream apply rule table that take input from generic switch
function (rul)
* table
* source owner
* target owner
* source db
* stream name (invent one)
* target queue name
* target queue owner
X) -- Grant
execute of rule set to apllied owner (rul)
* stream name
* target owner
* source DB name
d) Load queue parameters:
XI) -- Prepare
source schema instantiation (cap)
* source owner
XII --
prepare source table instantiation (cap)
* source owner
* table table name
* db link to use
XIII -- set
option disable on TARGET queue (app)
* stream name
* N or Y
e)
Start queues:
XIV -- Start
apply (app)
* apply name
XV -- start
capture (cap)
* capture name
3. Commands output
-------------------------------------------------------------------------------------
0) Create
admin user at source and target DB (smenu/3.8) Admin user is STRMADMIN
After creation we use 'ros' to see objects
permissions of strmamdin.
[POLDEV]> ros strmadmin
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
Date - Friday 30th December 2005 12:23:04
Username - SYS
List System Privilege(s) for a User STRMADMIN Default Temporary
Username Tablespace Tablespace Profile Role (admin)
----------------------- ----------------------- ---------------- ------------ -----------------------------------
STRMADMIN TBS_ADMIN_STREAM TEMP DEFAULT CREATE RULE-
CREATE RULE SET-
UNLIMITED TABLESPACE-
OBJECT_TYPE Username Owner Table Column Privilege Gran
------------------ ---------------- ---------------- ----------------------------- ---------------- ----------------- ----
VIEW STRMADMIN SYS DBA_APPLY_ERROR SELECT NO
PACKAGE STRMADMIN SYS DBMS_APPLY_ADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_AQADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_CAPTURE_ADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_FLASHBACK EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_PROPAGATION_ADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_STREAMS_ADM EXECUTE NO
(don't forget to do same thing on target DB)
I) Create
SOURCE queue:
[RMANTST]> aq -create -qn Q_SENDER -u strmadmin
-x -v
Statement generated :
execute
DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'Q_SENDER',
queue_name => 'Q_SENDER');
To Show source queue we just
created [POLDEV]> aq -l
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
Queue Message Count
Qid Queue Owner and Name Queue Table Type Enq Deq in Queue
------ ------------------------------------ ---------------------- --------- --- --- -------------
8116 STRMADMIN.AQ$_Q_SENDER_E Q_SENDER EXCEPTION NO NO
8118 STRMADMIN.Q_SENDER Q_SENDER NORMAL YES YES
II) CREATE
TARGET QUEUE
[RMANTST]> aq -create -u strmadmin -qn Q_RECEIVER -x
Statement generated :
execute DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'Q_RECEIVER', queue_name => 'Q_RECEIVER');
Show target queue: [RMANTST]> aq -l
MACHINE s05096 - ORACLE_SID : RMANTST Page: 1
Queue Message Count
Qid Queue Owner and Name Queue Table Type Enq Deq in Queue
------ ------------------------------------ ---------------------- --------- --- --- -------------
8278 STRMADMIN.AQ$_Q_RECEIVER_E Q_RECEIVER EXCEPTION NO NO
8280 STRMADMIN.Q_RECEIVER Q_RECEIVER NORMAL YES YES
III) Create rules set on source:
[POLDEV]> rul -create -rs RS_CAP_USERA -u strmadmin -x
Statement generated :
execute
DBMS_RULE_ADM.CREATE_RULE_SET(rule_set_name =>
'STRMADMIN.RS_CAP_USERA',
evaluation_context => 'sys.streams$_evaluation_context');
Show rule sets:
[POLDEV]> rul -ls
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
RULE_SET_OWNER Rule set Name Rule Name RULE_SET_RULE_COMMENT
-------------- -------------------- ---------------------------------- -----------------------------------
STRMADMIN RS_CAP_USERA .
Q_SENDER_R .
SYS AQ$_PROP_NOTIFY_1_R .
IV) Create capture on source DB:
[POLDEV]> cap -create -cn CAP_TBL_CUSTOMER -u
strmadmin -rs RS_CAP_USERA -qn Q_SENDER -u strmadmin -x
Statement generated :
execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name =>
'STRMADMIN.Q_SENDER', capture_name => 'CAP_TBL_CUSTOMER',
rule_set_name => 'STRMADMIN.RS_CAP_USERA');
Show capture
: [POLDEV]> cap -l
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1 S
T
A
T Next Last Last
U captured captured applied
Capture name Queue Owner and Name Rule set name S Scn Scn Scn
------------------ ------------------------------ --------------------------- - ------------- ------------- -------------
CAP_TBL_CUSTOMER STRMADMIN.Q_SENDER STRMADMIN.RS_CAP_USERA D 149857785259
V) Create a rule for capture :
[POLDEV]> rul -create
-cap -rn RUL_CAP_CUSTOMER -t CUSTOMER -so USERA -src_sid POLDEV -x
Statement generated :
execute DBMS_RULE_ADM.CREATE_RULE ( rule_name =>
'RUL_CAP_CUSTOMER', condition => ':dml.get_object_owner() =
''USERA''
AND '|| ':dml.get_object_name() = ''CUSTOMER'' AND '||
':dml.get_source_database_name() = ''POLDEV''');
Show rule :
[POLDEV]> rul -l
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
RULE_OWNER RULE_NAME Target Object Object Owner Applied in
-------------- ------------------------------ -------------------------------- ---------------------- ----------
STRMADMIN RUL_CAP_CUSTOMER CUSTOMER USERA POLDEV
VI) Add capture rule to rule set :
[POLDEV]> rul -add -rn RUL_CAP_POD_INFOR -rs
RS_CAP_USERA -u STRMADMIN -v
Statement generated :
execute DBMS_RULE_ADM.ADD_RULE( rule_name =>
'STRMADMIN.RUL_CAP_CUSTOMER', rule_set_name =>
'STRMADMIN.RS_CAP_USERA',
evaluation_context => NULL);
Show new ruleset:
[POLDEV]> rul -ls
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
RULE_SET_OWNER Rule set Name Rule Name RULE_SET_RULE_COMMENT
-------------- -------------------- ---------------------------------- -----------------------------------
STRMADMIN RS_CAP_USERA STRMADMIN.RUL_CAP_CUSTOMER
VII) Create propagation
[POLDEV]> prop -create -u strmadmin -pn PROP_A_TO_B
-sourceq Q_SENDER -destq Q_RECEIVER -dblk rmantst -x
Statement generated :
execute
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION( propagation_name =>
'STRMADMIN.PROP_A_TO_B',
source_queue => 'STRMADMIN.Q_SENDER', destination_queue =>
'STRMADMIN.Q_RECEIVER',
destination_dblink => 'RMANTST');
Show propagation :
[POLDEV]> prop -l
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
Destination
Propagation name Source queue Remote queue Local or remot Rule set name
---------------------- ------------------------------- ------------------------------- -------------- --------------------
PROP_A_TO_B STRMADMIN.Q_SENDER STRMADMIN.Q_RECEIVER RMANTST
And since propagation is also a
job : [POLDEV]> jbsu
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
Date - Friday 30th December 2005 13:27:04
Username - SYS
List Submitted Jobs
Last Last Next Next
Ok Ok Run Run
Id Submitter Security Job Date Time Date Time Errs Ok
------ ---------- ---------- -------------------------------------------------- ---------- ----- ---------- ----- ---- --
361 SYS SYS next_date := sys.dbms_aqadm.aq$_propaq(job); 30/12/2005 13:26 Y
VIII) Create a generic function to
switch ownership of objects in queue from source owner to target owner.
[RMANTST] > rul -switch -so USERA -to USERB -src_sid
POLDEV
Statement generated :
CREATE OR REPLACE FUNCTION POLDEV_to_USERB ( p_in_data IN SYS.ANYDATA) RETURN SYS.ANYDATA IS
out_data SYS.LCR$_ROW_RECORD;
tc PLS_INTEGER;
typenm VARCHAR2(61);
BEGIN
typenm := p_in_data.GETTYPENAME();
IF typenm = 'SYS.LCR$_ROW_RECORD' THEN
-- Typecast AnyData to LCR$_ROW_RECORD
tc := p_in_data.GETOBJECT(out_data);
IF out_data.GET_OBJECT_OWNER() = 'USERA' THEN
-- Transform the in_data into out_data
out_data.SET_OBJECT_OWNER('USERB');
END IF;
-- Convert to AnyData
RETURN SYS.AnyData.ConvertObject(out_data);
ELSE
RETURN p_in_data;
END IF;
END;
/
IX) Create a table apply rule that
uses this function:
[RMANTST]> rul -apl_sw -t CUSTOMER -so USERA -to USERB -src_sid POLDEV -sn STRM_APL_TO_USERB
-qn Q_RECEIVER -u STRMADMIN -x
Statement generated :
DECLARE
action_ctx SYS.RE$NV_LIST;
ac_name varchar2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
v_dmlrule VARCHAR2( 128 );
v_ddlrule VARCHAR2( 128 );
rs_name VARCHAR2(64);
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'USERA.CUSTOMER', /* this is not an error : USERA for this rule on table CUSTOMER */
streams_type => 'APPLY', /* of USERB. The rule is on the objects in the queue and the */
streams_name => 'STRM_APL_TO_USERB', /* objects in the queue are from USERA.mytable */
queue_name => 'STRMADMIN.Q_RECEIVER',
include_dml => true,
include_ddl => false,
dml_rule_name => v_dmlrule,
ddl_rule_name => v_ddlrule,
source_database => 'POLDEV');
select rule_name into v_dmlrule from dba_rules where rule_owner='STRMADMIN' and RULE_CONDITION LIKE '%CUSTOMER%';
select rule_action_context into action_ctx from dba_rules where rule_owner='STRMADMIN' and rule_name = v_dmlrule;
action_ctx := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY());
action_ctx.ADD_PAIR(ac_name,SYS.ANYDATA.CONVERTVARCHAR2('STRMADMIN.POLDEV_to_USERB'));
DBMS_RULE_ADM.ALTER_RULE(rule_name => v_dmlrule,action_context => action_ctx );
END;
/
Show the apply rule :
[RMANTST]> app -l
Apply
Type of Process
Apply name Queue name Applied Events Rule Set name Status
------------------------------------------ ---------------------------- -------------- ---------------------------- --------
STRMADMIN.STRM_APL_TO_USERB STRMADMIN.Q_RECEIVER Captured RULESET$_8 DISABLED
[RMANTST]> rul -ls
RULE_SET_OWNER Rule set Name Rule Name RULE_SET_RULE_COMMENT
-------------- -------------------- ---------------------------------------- -----------------------------------
STRMADMIN RULESET$_8 STRMADMIN.CUSTOMER7 STRMADMIN.RULESET$_8
[RMANTST]> rul -l
RULE_OWNER RULE_NAME Target Object Object Owner Applied in
-------------- ------------------------------ -------------------------------- ---------------------- ----------
STRMADMIN CUSTOMER7 CUSTOMER USERA POLDEV
X) Grant execute to target user
on the conversion fonction (defined in 8) :
[RMANTST]> rul -grant -sn STRM_APL_TO_USERB -to USERB
-src_sid POLDEV -x
Statement generated :
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='STRM_APL_TO_USERB';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name, grantee => 'USERB');
END;
/
prompt doing 'grant execute on POLDEV_to_USERB to USERB ;'
prompt may fails if the current user has not enought privilege
grant execute on POLDEV_to_USERB to USERB
/
PL/SQL procedure successfully completed.
Grant succeeded.
XI ) Prepare source instantiation :
[POLDEV]> cap -si -so USERA -x
Statement generated :
execute DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'USERA');
PL/SQL procedure successfully completed.
XII)
Instantiate the table
[POLDEV]> cap -ti -so USERA -t CUSTOMER -v -dblk
rmantst -x
Statement generated :
execute
dbms_capture_adm.prepare_table_instantiation('USERA.CUSTOMER') ;
set serveroutput on size 2000
DECLARE
iscn NUMBER; -- Variable to
hold instantiation SCN value
BEGIN
iscn :=
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@RMANTST(source_object_name
=> 'USERA.CUSTOMER', source_database_name =>
'POLDEV', instantiation_scn =>
iscn);
dbms_output.put_line(iscn);
END;
/
PL/SQL procedure successfully completed.
149857796897
PL/SQL procedure successfully completed.
XIII) Don't disable the queue on dequeue
error:
[RMANTST]> app -dis_on_err N -sn STRM_APL_TO_USERB -x
Statement generated :
execute DBMS_APPLY_ADM.SET_PARAMETER(apply_name
=>'STRM_APL_TO_USERB', parameter => 'disable_on_error', value
=> 'n');
PL/SQL procedure successfully completed.
XIV) Start apply process on target:
[RMANTST]> app -start -an STRM_APL_TO_USERB -x
Statement generated :
execute DBMS_APPLY_ADM.START_APPLY(
apply_name => 'STRM_APL_TO_USERB');
PL/SQL procedure successfully completed.
Show the apply process status
[RMANTST]> app -l
Apply
Type of Process
Apply name Queue name Applied Events Rule Set name Status
------------------------------------------ ---------------------------- -------------- ---------------------------- --------
STRMADMIN.STRM_APL_TO_USERB STRMADMIN.Q_RECEIVER Captured RULESET$_8 ENABLED
XV ) Start the capture process :
[POLDEV]> cap -start -cn CAP_TBL_CUSTOMER -x
Statement generated :
execute DBMS_CAPTURE_ADM.START_CAPTURE( capture_name =>
'CAP_TBL_CUSTOMER');
PL/SQL procedure successfully completed.
Now, it is time to sacrify some
users to the god of DBA, but it should work.
Smenu also offer many features to track the status of the streams and
troubleshoot.
I will make soon another article about this.
|
Check parameters of POLDEV :
[s05096:POLDEV]:/export/home/oracle> pars
Date - Monday 02nd January 2006 12:58:06
Username - SYS
Report - Streams pararmeters
. parg = data guard pars = Streams
Is Sys
Parameter Value Modifiable
-------------------------------- ------------------------------------------------------------------------ ----------
aq_tm_processes 1 IMMEDIATE
archive_lag_target 0 IMMEDIATE
compatible 9.2.0 FALSE
db_name POLDEV FALSE
global_names TRUE IMMEDIATE
job_queue_processes 10 IMMEDIATE
log_archive_dest_1 LOCATION=/backup/archive/POLDEV IMMEDIATE
log_archive_format arch_%t_%s.arc FALSE
log_archive_start TRUE FALSE
log_parallelism 1 FALSE
logmnr_max_persistent_sessions 1 FALSE
open_links 4 FALSE
parallel_max_servers 5 FALSE
processes 150 FALSE
sessions 170 FALSE
sga_max_size 538007016 FALSE
shared_pool_size 251658240 IMMEDIATE
Check parameters of RMANTST:
[s05096:RMANTST]:/export/home/oracle> pars
Date - Monday 02nd January 2006 12:53:19
Username - SYS
Report - Streams pararmeters
. parg = data guard pars = Streams
Is Sys
Parameter Value Modifiable
-------------------------------- ------------------------------------------------------------------------ ----------
aq_tm_processes 1 IMMEDIATE
archive_lag_target 0 IMMEDIATE
compatible 9.2.0 FALSE
db_name RMANTST FALSE
global_names TRUE IMMEDIATE
job_queue_processes 10 IMMEDIATE
log_archive_dest_1 location=/backup/archive/RMANTST IMMEDIATE
log_archive_format arch_%t_%s.arc FALSE
log_archive_start TRUE FALSE
log_parallelism 1 FALSE
logmnr_max_persistent_sessions 1 FALSE
open_links 4 FALSE
parallel_max_servers 5 FALSE
processes 150 FALSE
sessions 170 FALSE
sga_max_size 92517584 FALSE
shared_pool_size 33554432 IMMEDIATE
Last updated : 13 Febuary 2006
| 