Month: October 2008

Easy Streams Cookbook Example

We have Call Centres all over the country. The Call Centre’s IVR (interactive voice response, is a phone technology that allows a computer to detect voice and touch tones using a normal phone call) is used for routing calls to the correct call centre. The subscriber data is stored in 1 location and I was asked to replicate that data to 2 databases in other parts of the country. The main reasons are to have the data closer to the IVR system and also to relieve the main production system from all these queries.

Decisions: Use one-directional replication. Capture the changes at the source. Do the instantiation (first copy of data) with DataPump.

Preperation Steps

Enable Supplemental Logging (at source)
Use columns in PK constraints for Supplemental log group

ALTER TABLE "SCHEMA_ABC"."TABLE_ABC" ADD SUPPLEMENTAL LOG GROUP ivr_rep (XXMSISDN) ALWAYS;

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm Supplemental Log Groups:


COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20
SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS,'ALWAYS', 'Unconditional','CONDITIONAL', 'Conditional') ALWAYS, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS;

Create default tablespace for the Streams Administrator Account to store the queue table. Do these steps on the source and destinations.

CREATE TABLESPACE streams_tbs DATAFILE '/data/oradata/lfivrdb/streams_tbs.dbf' SIZE 7168M;

CREATE USER strmadmin IDENTIFIED BY STRMXXX
PROFILE VODA_COMPLIANT
DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
GRANT CONNECT, RESOURCE, DBA to STRMADMIN;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT SELECT_CATALOG_ROLE TO STRMADMIN;
EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

Set specific initialization parameters at the databases participating in the Streams setup

ALTER SYSTEM SET STREAMS_POOL_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET AQ_TM_PROCESSES = 1 scope=both;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
ALTER SYSTEM SET “_job_queue_interval”=1 scope=spfile;

(This is not a dynamic parameter and the db will need to be bounced)

Create 2 database links on the source to the 2 destinations.

CONNECT STRMADMIN/STRMXXX
CREATE DATABASE LINK LFIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘LFIVRDB.VODACOM.CO.ZA’;
CREATE DATABASE LINK BEIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘BEIVRDB.VODACOM.CO.ZA’;

STEP 1
Create Streams Queues
One Capture on the source db and one Apply on each destination:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_CAPTURE_Q',
queue_user => 'STRMADMIN');
END;
/

And on both dest sites:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_APPLY_Q',
queue_user => 'STRMADMIN');
END;
/

STEP2
Create Capture Process on Source.
This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'CAPTURE',
streams_name => '"STREAMS_CAPTURE"',
queue_name => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
include_dml => true,
include_ddl => true,
source_database => 'DESTPRD.VODACOM.CO.ZA');
END;
/

STEP3
Create 2 propagation process on the Source (one per destination)

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => '"SCHEMA_ABC"."TABLE_ABC"',
streams_name => '"STREAMS_PROP_LF"',
source_queue_name => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
destination_queue_name => '"STRMADMIN"."STREAMS_APPLY_Q"@LFIVRDB.VODACOM.CO.ZA',
include_dml => true,
include_ddl => true,
source_database => 'DESTPRD.VODACOM.CO.ZA',
inclusion_rule => true);
END;
/

And to the 2nd dest site:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => '"SCHEMA_ABC"."TABLE_ABC"',
streams_name => '"STREAMS_PROP_BE"',
source_queue_name => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
destination_queue_name => '"STRMADMIN"."STREAMS_APPLY_Q"@BEIVRDB.VODACOM.CO.ZA',
include_dml => true,
include_ddl => true,
source_database => 'DESTPRD.VODACOM.CO.ZA',
inclusion_rule => true);
END;
/

STEP 4
Create 2 Apply processes. One on each destination.

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY',
streams_name => 'STREAMS_APPLY',
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true,
include_ddl => true,
source_database => 'DESTPRD.VODACOM.CO.ZA');
END;
/

And 2nd site

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY',
streams_name => 'STREAMS_APPLY',
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true,
include_ddl => true,
source_database => 'DESTPRD.VODACOM.CO.ZA');
END;
/

Instantiation. This is the initial copy of the table.

Set & Get SCN:

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name =>  'SCHEMA_ABC.TABLE_ABC'); 

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm the SCN number that you will need in a future step:

col scn for 9999999999999999
select * from dba_capture_prepared_tables

Export table at source

expdp strmadmin/STRMXXX TABLES= SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp parallel=8

compress and ftp dump files to the 2 destination servers

Import table at each destination
First create the schema owner of the table

create user SCHEMA_ABC identified by STRMXXX default tablespace streams_tbs;
grant connect, resource to SCHEMA_ABC
/

impdp strmadmin/STRMXXX TABLES=SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp remap_tablespace=TBS_ABC:streams_tbs

Manually instantiating the objects at each destination

connect STRMADMIN/STRMXXX
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCHEMA_ABC.TABLE_ABC',
source_database_name => 'DESTPRD.VODACOM.CO.ZA',
instantiation_scn => &iscn);
END;
/
Enter value for iscn:

STEP5
Finally start the Process:
On source

CONNECT strmadmin/STRMXXX
exec dbms_capture_adm.start_capture(capture_name => 'STREAMS_CAPTURE');

At 1st destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

At 2nd destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

Post Installation steps
On Source force Stream checkpoint (see Metalink Note:342342.1)

EXEC DBMS_CAPTURE_ADM.SET_PARAMETER('STREAMS_CAPTURE','_CHECKPOINT_FORCE','Y');

On each destination

EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY', parameter => 'WRITE_ALERT_LOG', value => 'Y' );
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY',parameter => 'DISABLE_ON_ERROR',value => 'N' );

End of summer time

Last Sunday was the end of the daylight saving scheme that goes in these parts by the name of British Summer Time, today 2 days past the end of summertime it snows! I don’t recall snow so far south in October. Oh well it gives me something to write about!Posted in Random

Undocumented Oracle Functions

Undocumented functions in Oracle are always fun, and you just may find something useful.

The caveat of course is that they are undocumented. They can change without notice between releases or patch levels, so building apps that depend on them may be unwise.

They are often quite useful from a DBA perspective when used in SQL scripts.

Here are a few that I’ve played with. These are all found in Oracle 10.2.0.3

These functions have one thing in common – they have a prefix of SYS_OP_

Some of these appear to be identical to documented functions.

I don’t know of any official explanation regarding the purpose of undocumented functions that seem to mimic documented functions. It could be that the source for the documented functions are separate from those that are documented, ensuring that functionality will not change for an undocumented function that is used in the Oracle kernel, even though its documented doppelganger may change in future releases.

In any case, undocumented functions are always interesting, and here are a few to play with.

Just keep in mind that these are undocumented, and as such may change or disappear entirely in future releases

sys_op_vacand – Return the binary AND of two raw values. Results are in hex

SELECT sys_op_vecand(hextoraw(‘FF’),hextoraw(‘FE’)) from dual;
FE

16:13:12 SQL>SELECT sys_op_vecand(hextoraw(‘C3’),hextoraw(‘7E’)) from dual;
42

sys_op_vecor – Return the binary OR of two raw values. Results are in hex

16:14:39 SQL>SELECT sys_op_vecor(hextoraw(‘FF’),hextoraw(‘FE’)) from dual;
FF

sys_op_vecxor – Return the binary XOR of two raw values. Results are in hex

16:14:39 SQL>SELECT sys_op_vecor(hextoraw(‘FF’),hextoraw(‘FE’)) from dual;
FF

sys_op_vecbit – Return the value of the bit at position N in a raw value

The return value is 0 or 1

This is an interesting function as it can be used to determine the value of bits in a number. If for instance some flags are stored in a bit vector and you need to know the value of the 3 bit, this is an easy way to do it.

I believe the upper limit on the number of bits is 127.

prompt
define decnum=10
prompt &&decnum dec = 1010 bin

16:16:27 SQL>select ‘Bit 0 is ‘ || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),0) from dual;
Bit 0 is 0

16:16:27 SQL>select ‘Bit 1 is ‘ || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),1) from dual;
Bit 1 is 1

16:16:27 SQL>select ‘Bit 2 is ‘ || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),2) from dual;
Bit 2 is 0

16:16:27 SQL>select ‘Bit 3 is ‘ || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),3) from dual;
Bit 3 is 1

sys_op_bitvec – This appears to be for used to build a bit vector, but I haven’t figured out
how to use it. Please let me know if you do.

sys_op_map_nonnull – This has been thouroughly discussed on Eddie Awad’s blog:
sys_op_map_nonnull discussion

sys_op_descend – Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.

16:32:41 SQL>select sys_op_descend(‘ABC’) from dual;
BEBDBCFF

sys_op_undescend – The inverse of sys_op_descend. Well, almost

17:12:59 SQL>select sys_op_undescend(sys_op_descend(‘ABC’)) from dual
17:12:59 2 /

414243

Notice the output is in the original order, but in decimal rather than hex.

sys_op_dump – dump the data from an ADT/UDT (Abtract/User Data Type)

16:54:13 SQL>CREATE OR REPLACE TYPE my_adt AS OBJECT (
16:54:13 2 last_name varchar2(30),
16:54:13 3 first_name varchar2(30),
16:54:13 4 id number(6)
16:54:13 5 )
16:54:13 6 /
16:54:13 SQL>
16:54:13 SQL>
16:54:13 SQL>select sys_op_dump(my_adt(‘still’,’jared’,234987)) from dual;

(‘still’,’jared’,234987)

I don’t use objects in the database, but this would likely be useful for someone that does.

sys_op_guid – this appears to be identical to sys_guid

17:00:50 SQL>select sys_guid(), sys_op_guid() from dual;

52BA7CF06BB488ECE040010A7C646200 52BA7CF06BB588ECE040010A7C646200

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569