denis@WESTDB>> CREATE TABLE west.interval_tab
2 ( prod_id NUMBER(6)
3 , cust_id NUMBER
4 , time_id DATE
5 )
6 PARTITION BY RANGE (time_id)
7 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
8 ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2013', 'MM-DD-YYYY')),
9 PARTITION p1 VALUES LESS THAN (TO_DATE('2-1-2013', 'MM-DD-YYYY')),
10 PARTITION p2 VALUES LESS THAN (TO_DATE('3-1-2013', 'MM-DD-YYYY')),
11 PARTITION p3 VALUES LESS THAN (TO_DATE('4-1-2013', 'MM-DD-YYYY')) );
Table created.
Verify it is replicated in target:
denis@EASTDB>> desc east.interval_tab; Name Null? Type ----------------------------------------------------------------------------- -------- --------------------- PROD_ID NUMBER(6) CUST_ID NUMBER TIME_ID DATE2. create indexes and contraint At source:
denis@WESTDB>> create unique index west.interval_tab_pk on west.interval_tab(prod_id, time_id) local; Index created. denis@WESTDB>> create index west.interval_tab_ix1 on west.interval_tab(cust_id) local; Index created. denis@WESTDB>> alter table west.interval_tab add constraint interval_tab_pk primary key (prod_id, time_id) using index; Table altered.Verify index and constraints creation are replicated at target:
denis@EASTDB>> @tabix Enter value for tabowner: east Enter value for tabname: interval_tab TABLE_NAME INDEX_NAME COLUMN_NAME COL_POS UNIQUENES -------------------- -------------------- -------------------- ---------- --------- INTERVAL_TAB INTERVAL_TAB_IX1 CUST_ID 1 NONUNIQUE INTERVAL_TAB INTERVAL_TAB_PK PROD_ID 1 UNIQUE INTERVAL_TAB TIME_ID 2 UNIQUE denis@EASTDB>> select owner,table_name, constraint_name from dba_constraints where table_name='INTERVAL_TAB'; OWNER TABLE_NAME CONSTRAINT_NAME --------------- -------------------- -------------------- EAST INTERVAL_TAB INTERVAL_TAB_PK3. Insert data at source that will cause new interval partition created automatically
denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------- -------------------- -------------------- ---------------------------------------------
WEST INTERVAL_TAB P0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Perform the following insert at source and check the partitions: denis@WESTDB>> insert into west.interval_tab values (1004,1, sysdate);
1 row created.
denis@WESTDB>> commit;
Commit complete.
denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ -------------------- -------------------- ---------------------------------------------
WEST INTERVAL_TAB P0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
WEST INTERVAL_TAB SYS_P81 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
5 rows selected.
4. Check partitions at target database
denis@EASTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- -------------------- -------------------- ---------------------------------------------
EAST INTERVAL_TAB P0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB SYS_P123 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
5 rows selected.
denis@EASTDB>> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='INTERVAL_TAB_IX1';
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ -------------------- --------------------
EAST INTERVAL_TAB_IX1 P0
EAST P1
EAST P2
EAST P3
EAST SYS_P123
5 rows selected.
5. Drop a partition at source
alter table west.interval_table drop partition P0;
denis@WESTDB>> alter table west.interval_tab drop partition P0;
Table altered.
Verified, it is replicable
denis@EASTDB>> /
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ -------------------- -------------------- ---------------------------------------------
EAST INTERVAL_TAB P1 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB P3 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EAST INTERVAL_TAB SYS_P123 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
4 rows selected.
In conclusion, the tests confirmed that GG DDL replication support the interval partition automatic creation and drop partition operations.
It is worth noting that when we use DBMS_METADATA package to get the definitions of interval partitioned table and its associated
local indexes, we won't see the interval partitions that are created automatically. To verify the existance of the partitions, we shall use
dba_tab_partitions and dba_ind_partitions views. --- for port 2200 --- select owner, object_name, objid from splex.shareplex_objmap s, dba_objects o where s.objid = o.object_id order by owner, object_name; --- for port 2201 ---- select owner, object_name, objid from splex_cpf.shareplex_objmap s, dba_objects o where s.objid = o.object_id order by owner, object_name;
SQL> select sequence_name, last_number from dba_sequences
where sequence_owner=user;
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
TEST2_SEQ 107
TEST3_SEQ 107
TEST_SEQ 175
SQL> ;
1* SELECT DBMS_METADATA.GET_DDL(upper('&OBJTYPE'), upper('&OBJNAME') , upper('&OWNER')) ddl_string from dual
SQL> /
Enter value for objtype: sequence
Enter value for objname: TEST_SEQ
Enter value for owner: abc
CREATE SEQUENCE "ABC"."TEST_SEQ" MINVALUE 1 MAXVALUE 1.00000000000000E+27
INCREMENT BY 98 START WITH 175 CACHE 20 NO
ORDER NOCYCLE ;
---- seq_odd.sql -----
declare
seqown varchar2(30);
seqname varchar2(30);
sqlstmt varchar2(1000);
cval number;
incr number;
cursor seq_cur is
select sequence_owner, sequence_name
from dba_sequences
where sequence_owner is [some_condition];
begin
open seq_cur;
loop
fetch seq_cur into seqown, seqname;
exit when seq_cur%notfound;
sqlstmt := 'select ' seqown '.' seqname '.nextval from dual';
execute immediate sqlstmt into cval;
if ( mod(cval, 2) = 0 )
then
-- ensure the current val is odd number
-- first change increment by 1
sqlstmt := 'alter sequence ' seqown '.' seqname ' increment by 1';
execute immediate sqlstmt;
sqlstmt := 'select ' seqown '.' seqname '.nextval from dual';
execute immediate sqlstmt into cval;
sqlstmt := 'alter sequence ' seqown '.' seqname ' increment by 2';
execute immediate sqlstmt;
else
-- already an odd number
sqlstmt := 'alter sequence ' seqown '.' seqname ' increment by 2';
execute immediate sqlstmt;
end if;
end loop;
close seq_cur;
-- follwing code is to verify ----
---- end of seq_odd.sql --------
=========================================
== Set up read-only MVIEW replication ==
=========================================
Environment:
Master site (DBT920) : 9.2.0.4
Materialized view site (DBT10G) : 10.2.0.2
Reference:
1. Metalink Note 256235.1 Scripts to create Trusted / Untrusted ReadOnly MVIEW Replication Sites
2. http://www.hpfuchs.com/2008/02/06/materialized-view-replication/
Overview
~~~~~~~~~~
1. create users at master site
2. create users at mview site
3. create database link at mview site
4. create mview logs at master site
5. create mview at mview site
6. create mview groups
7. Some basic operations
Detailed Steps
~~~~~~~~~~~~~~~
1. create users at master site
-- run as system
create user mviewproxy identified by mviewproxy;
grant create session to mviewproxy;
grant create any table to mviewproxy;
grant comment any table to mviewproxy;
grant select any table to mviewproxy;
BEGIN
dbms_repcat_admin.register_user_repgroup(
username => 'mviewproxy',
privilege_type => 'proxy_mviewadmin',
list_of_gnames => NULL);
END;
/
Note: Not sure if this register_user_repgroup is necessary
2. create users at mview site
-- run as system
create user mvowner identified by oracle
default tablespace users temporary tablespace temp;
grant connect, resource to mvowner;
grant create materialized view to mvowner;
grant create database link to mvowner;
-- run as system
CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
ALTER USER mviewadmin DEFAULT TABLESPACE users;
ALTER USER mviewadmin TEMPORARY TABLESPACE temp;
EXECUTE dbms_repcat_admin.grant_admin_any_schema('mviewadmin');
GRANT comment any table TO mviewadmin;
GRANT lock any table TO mviewadmin;
GRANT create any materialized view TO mviewadmin;
GRANT alter any materialized view TO mviewadmin;
3. create db link at mview site
-- run as system
CREATE PUBLIC DATABASE LINK DBT92.US.ORACLE.COM USING 'DBT92';
Note: not sure why this is necessary.
-- run as mvowner
create database link DBT92.US.ORACLE.COM connect to mviewproxy identified by mviewproxy
using 'DBT92';
4. create mview logs at master site
-- run as mviewproxy
create materialized view log on mstowner.big_table tablespace mviewlog;
Note: 1. mview logs residing in their own tablespace, i.e.
create tablespace mviewlog datafile '/u03/oracle/oradata/DBT92/mviewlog01.dbf' size 50M;
2. mviewproxy does not have the privs to alter/drop materialized view log
5. create mview at mview site
login as mvowner
-- run as mvowner
CREATE MATERIALIZED VIEW mvowner.t_mv REFRESH FAST AS SELECT * FROM mstowner.t@DBT92.US.ORACLE.COM;
CREATE MATERIALIZED VIEW mvowner.big_table_mv REFRESH FAST AS SELECT * FROM mstowner.big_table@DBT92.US.ORACLE.COM;
Note:
Oracle will create three objects when executing the following command:
CREATE MATERIALIZED VIEW mvowner.big_table_mv REFRESH FAST AS SELECT * FROM mstowner.big_table@DBT92.US.ORACLE.COM;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
BIG_TABLE_MV TABLE
BIG_TABLE_PK INDEX
BIG_TABLE_MV MATERIALIZED VIEW
6. create mview groups at mview site
-- create the refresh group for the mview to ensure transactional
-- consistency when refreshing nore than one mview in the group.
-- run as mviewadmin on the mview side
begin
dbms_refresh.make(
name => 'RG_BIG_TABLE',
list => 'mvowner.big_table_mv',
next_date => sysdate,
interval => 'sysdate + 1/24',
implicit_destroy => true,
lax => true);
end;
/
7. Some basic operations
(1) Refresh the complete group
execute dbms_refresh.refresh('RG_BIG_TABLE');
(2) Refresh a single snapshot
execute dbms_snapshot.refresh('mvowner.big_table_mv');
(3) Check materialized veiw refresh status
set linesize 120
set pagesize 100
col owner format a20
col table_name format a20
col name format a20
col master_owner format a20
col master_link format a20
col next format a20
select owner
,name
-- ,table_name
-- ,master_owner
-- ,master
-- ,master_link
,to_char(last_refresh, 'YYYY-MON-DD HH24:MI:SS') lst_rfrsh
,next
,status
from dba_snapshots
/
(4) views:
dba_snapshots
dba_mviews
Name Null? Type ----------------- -------- --------------- ID NOT NULL NUMBER FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(30)
create or replace procedure update_strm_tab3(l_id in number, l_first_name in varchar2, l_last_name in varchar2 ) as begin update strm_tab3 set first_name = l_first_name, last_name=l_last_name where id= l_id; commit; end; /
ID FIRST_NAME LAST_NAME ---------- -------------------- ------------------------------ 6 fn6d_10g ln6d_10g
ID FIRST_NAME LAST_NAME ---------- -------------------- ------------------------------ 6 fn6d_02db ln6d_02db
++ APPLY PROCESS INFORMATION ++ APPLY_NAME : STRMADMIN_APPLY_2 MAX_APPLIED_MESSAGE_NUMBER : STATUS : ABORTED STATUS_CHANGE_TIME : 14-dec-2008 15:10:09 ERROR_NUMBER : 26714 ERROR_MESSAGE : ORA-26714: User error encountered while applying
ID FIRST_NAME LAST_NAME ---------- -------------------- ------------------------------ 6 fn6d_10g ln6d_10g
COLUMN OBJECT_OWNER HEADING 'TableOwner' FORMAT A5 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A12 COLUMN METHOD_NAME HEADING 'Method' FORMAT A12 COLUMN RESOLUTION_COLUMN HEADING 'ResolutionColumn' FORMAT A13 COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A30 SELECT OBJECT_OWNER, OBJECT_NAME, METHOD_NAME, RESOLUTION_COLUMN, COLUMN_NAME FROM DBA_APPLY_CONFLICT_COLUMNS ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;
Table Resolution Owner Table Name Method Column Column Name ----- ------------ ------------ ------------- ------------------------ DENIS STRM_TAB3 DISCARD FIRST_NAME LAST_NAME DENIS STRM_TAB3 DISCARD FIRST_NAME FIRST_NAME
Table Resolution Owner Table Name Method Column Column Name ----- ------------ ------------ ------------- ----------------- DENIS STRM_TAB3 OVERWRITE FIRST_NAME LAST_NAME DENIS STRM_TAB3 OVERWRITE FIRST_NAME FIRST_NAME
Before starting this game, my environment is as follows:
By the end of game, my new enviroment should be:
My experimenting steps are as follows:
(ref: Oracle document: Creating a New Streams Multiple-Source Environment )
Step 0 -- Stop current capture, propagation and apply processes
Step 1 -- Create table denis.strm_tab3 at TEST10G
++ CAPTURE PROCESSES IN DATABASE ++
CAPTURE_NAME : STRMADMIN_CAPTURE
QUEUE_OWNER : STRMADMIN
QUEUE : STREAMS_QUEUE
CAPTURE_TYPE : LOCAL
STATUS : ENABLED
RULE_SET_OWNER : SYS
RSN : RULESET$_17
NEGATIVE_RULE_SET_OWNER :
RSN2 :
CHECKPOINT_RETENTION_TIME : 60
VERSION : 10.2.0.1.0
LOGFILE_ASSIGNMENT : IMPLICIT
ERROR_NUMBER :
STATUS_CHANGE_TIME : 30-nov-2008 21:07:57
ERROR_MESSAGE :
-----------------
++ CAPTURE PROCESS SOURCE INFORMATION ++
CAPTURE_NAME : STRMADMIN_CAPTURE
CAPTURE_TYPE : LOCAL
SOURCE_DATABASE : TEST10G.WORLD
FIRST_SCN : 10138270589233
START_SCN : 10138270589233
CAPTURED_SCN : 10138270859114
APPLIED_SCN : 10138270859114
LAST_ENQUEUED_SCN : 10138270865525
REQUIRED_CHECKPOINT_SCN : 10138270830473
MAX_CHECKPOINT_SCN : 10138270859114
SOURCE_DBID : 917147433
SOURCE_RESETLOGS_SCN : 534907
SOURCE_RESETLOGS_TIME : 665622764
LOGMINER_ID : 1
-----------------
++ PROPAGATIONS IN DATABASE ++
PROPAGATION_NAME : STRMADMIN_PROPAGATE
SOURCE_QUEUE_OWNER : STRMADMIN
SOURCE_QUEUE_NAME : STREAMS_QUEUE
SRC GLOBAL NAME : TEST10G.WORLDInstantiating
DESTINATION_QUEUE_OWNER : STRMADMIN
DESTINATION_QUEUE_NAME : STREAMS_QUEUE
DESTINATION_DBLINK : TEST02DB.WORLD
QUEUE_TO_QUEUE : FALSE
STATUS : ENABLED
ERROR_DATE :
ERROR_MESSAGE :
++ APPLY INFORMATION ++
APPLY_NAME : STRMADMIN_APPLY
QUEUE_OWNER : STRMADMIN
QUEUE_NAME : STREAMS_QUEUE
APPLY_CAPTURED : YES
STATUS : ENABLED
APPLY_USER : SCOTT
APPLY_TAG : 00
RULE_SET_OWNER : STRMADMIN
RULE_SET_NAME : RULESET$_35
NEGATIVE_RULE_SET_OWNER :
NEGATIVE_RULE_SET_NAME :
APPLY_DATABASE_LINK :
-----------------
++ APPLY PROCESS INFORMATION ++
APPLY_NAME : STRMADMIN_APPLY
MAX_APPLIED_MESSAGE_NUMBER :
STATUS : ENABLED
STATUS_CHANGE_TIME : 03-dec-2008 15:26:13
ERROR_NUMBER :
ERROR_MESSAGE :
-----------------
# exp_stats.awk
{
print "begin"
print " dbms_stats.export_table_stats("
print " 'MYSCHEMA', "
print " '" $1 "',"
print " stattab => 'TAB_B_STATS' ,"
print " statown => 'DB_ADMIN' ,"
print " cascade => TRUE, "
print " );"
print "end;"
print "/"
print " "
}
# imp_stats.awk
{
print "begin"
print " dbms_stats.import_table_stats("
print " 'MYSCHEMA', "
print " '" $1 "',"
print " stattab => 'TAB_B_STATS' ,"
print " statown => 'DB_ADMIN' ,"
print " cascade => TRUE "
print " );"
print "end;"
print "/"
print " "
}
select count(*) from sf.smc
where
mc_date >= to_date('2008-05-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and
mc_date <= to_date('2008-05-21 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
/
Then, I created a temporary table from the SMC table in the reporting database as follows: create table temp_a nologging
as
select
id, oid, eid, mid, st
FROM
sf.smc
where
mc_date > to_date('2008-05-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and
mc_date > to_date('2008-05-21 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
/
execute dbms_stats.gather_table_stats(user, 'TEMP_A', null,estimate_percent=>10);
The duplicated rows as well as the baseline row ( i.e. the row to which any other rows compared in order to determine if they are duplicated )are displayed through the following sql: select id, oid, eid, st, mid from smc where (oid, eid, st, mid ) in ( select oid, eid, st, mid FROM temp_a A WHERE rowid > (SELECT min(rowid) FROM temp_a B WHERE B.oid = A.oid and B.eid = A.eid and B.mid = A.mid and B.st = A.st ) ) order by 2,3,4,5The following are the duplicate rows in RPT
ID OID EID ST MID ---------- -------------------- -------------------- ----------- ---------- 1037693787 CICN087494895 489740124 Change 8 1036151673 CICN087494895 489740124 Change 8 1036151856 CICN087494953 430282795 Change 2 1036152154 CICN087494953 430282795 Change 2 1037621342 ICOG087494888 503141443 Install 19 1036151603 ICOG087494888 503141443 Install 19 1036151617 IICN087494988 503141881 Install 8 1036183677 IICN087494988 503141881 Install 8 8 rows selected.Note the column ID is different, to decide which row should be removed, we need to know which ID is presented in the produciton database: The following are the corresponding row in the production
ID OID EID ST MID ---------- -------------------- -------------------- ---------- ---------- 1037693787 CICN087494895 489740124 Change 8 1036152154 CICN087494953 430282795 Change 2 1037621342 ICOG087494888 503141443 Install 19 1036183677 IICN087494988 503141881 Install 8
DELETE FROM tbl_test
WHERE ROWID IN (
SELECT rid
FROM (SELECT ROWID rid,
ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn
FROM tbl_test)
WHERE rn <>1);
RESEARCH
=========
1. issue following query against MYDBP and MYDBQA
col owner format a14
col segment_name format a27
col table_name format a18
col column_name format a20
select a.owner, a.segment_name, a.bytes/1024/1024 "size (M)",
b.table_name, b.column_name
from dba_segments a, dba_lobs b
where a.tablespace_name='SYSTEM'
and a.segment_type='LOBSEGMENT'
and a.segment_name=b.segment_name
order by 3
system@MYDBQA> /
OWNER SEGMENT_NAME size (M) TABLE_NAME
COLUMN_NAME
--------------- --------------------------- ----------
------------------ --------------------
SYS SYS_LOB0000000270C00002$$ .0234375 KOTTD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000274C00002$$ .0234375 KOTTB$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000278C00002$$ .0234375 KOTAD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000282C00002$$ .0234375 KOTMD$
SYS_NC_ROWINFO$
SYSTEM SYS_LOB0000002394C00012$$ .0625 AQ$_QUEUES
SUBSCRIBERS
SYSTEM SYS_LOB0000002590C00002$$ .0625 DEF$_TEMP$LOB
TEMP$CLOB
SYSTEM SYS_LOB0000002590C00003$$ .0625 DEF$_TEMP$LOB
TEMP$NCLOB
SYSTEM SYS_LOB0000002590C00001$$ .0625 DEF$_TEMP$LOB
TEMP$BLOB
SYSTEM SYS_LOB0000002581C00005$$ .0625 DEF$_LOB
NCLOB_COL
SYSTEM SYS_LOB0000002581C00004$$ 2.3125 DEF$_LOB
CLOB_COL
SYSTEM SYS_LOB0000002581C00003$$ 3241 DEF$_LOB
BLOB_COL
system@MYDBP> /
OWNER SEGMENT_NAME size (M) TABLE_NAME
COLUMN_NAME
---------- --------------------------- ---------- ------------------
--------------------
SYS SYS_LOB0000000270C00002$$ .0234375 KOTTD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000274C00002$$ .0234375 KOTTB$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000278C00002$$ .0234375 KOTAD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000282C00002$$ .0234375 KOTMD$
SYS_NC_ROWINFO$
SYSTEM SYS_LOB0000002394C00012$$ .0625 AQ$_QUEUES
SUBSCRIBERS
SYSTEM SYS_LOB0000002590C00002$$ .0625 DEF$_TEMP$LOB
TEMP$CLOB
SYSTEM SYS_LOB0000002590C00003$$ .0625 DEF$_TEMP$LOB
TEMP$NCLOB
SYSTEM SYS_LOB0000002590C00001$$ .0625 DEF$_TEMP$LOB
TEMP$BLOB
SYSTEM SYS_LOB0000002581C00005$$ .0625 DEF$_LOB
NCLOB_COL
SYSTEM SYS_LOB0000002581C00004$$ 2.3125 DEF$_LOB
CLOB_COL
SYSTEM SYS_LOB0000002581C00003$$ 640.625 DEF$_LOB
BLOB_COL
Note: the last LOGSEGMENT has size of 3.2G in MYDBQA versus 640 M in MYDBP
CONCLUSIONS
===========
It can be inferred that there are significant DMLs on LOB type columns
during conversion
From Oracle Doc:
=================
( http://www.csee.umbc.edu/help/oracle8/server.815/a67791/ch9.htm#1420 )
DEFLOB View of Storage for RPC
Oracle stores internal LOB parameters to deferred RPCs in a side table that is referenced only by way of a synonym. This gives the you flexibility for storage parameters and the containing schema. The following shows the default storage table for LOB parameters.
CREATE TABLE system.def$_lob(
id RAW(16) CONSTRAINT def$_lob_primary PRIMARY KEY,
deferred_tran_db VARCHAR2(128), -- origin db
deferred_tran_id VARCHAR2(22), -- transaction id
blob_col BLOB,
clob_col CLOB
nclob_col NCLOB)
/
-- make deletes fast
CREATE INDEX system.def$_lob_n1 ON system.def$_lob(
deferred_tran_db,
deferred_tran_id)
/
-- use a synonym in case underlying table is moved
CREATE SYNONYM sys.def$_lob FOR system.def$_lob
/
CREATE OR REPLACE VIEW DefLOB AS SELECT * FROM sys.def$_lob
/
CREATE PUBLIC SYNONYM DefLOB FOR DefLOB
/
1. Check deferred transactions.
system - chk_def_tbls.sql
----
spool chk_def_tbls.log
select * from defcall;
select * from deftran;
select * from deferror;
select * from dba_repcatlog;
spool off
----
2. Check base tables.
system - chk_base_def_tbls.sql
----
spool chk_base_def_tbls.log
select * from system.def$_calldest;
select * from system.def$_defaultdest;
select * from system.def$_error;
select * from system.def$_origin;
select * from system.repcat$_repschema;
select * from system.def$_destination;
spool off
-----
3. Remove replication if it exist.
sys - $ORACLE_HOME/rdbms/admin/catrepr.sql
4. Drop the base deferred transaction tables.
system - dr_base_def_tbls.sql
----------------
spool dr_base_tbls.log
drop table system.def$_calldest;
drop table system.def$_defaultdest;
drop table system.def$_error;
drop table system.def$_origin;
--drop table system.repcat$_repschema; This is dropped in catrepr.sql
drop table system.def$_destination;
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQCALL', TRUE);
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQERROR', TRUE);
commit;
spool off
-------------
5. Create the replication tablespace and
set system's default tablespace to the rep tblsp.
system - cr_rep_tblsp.sql
6. Create the base deferred transaction tables.
system - $ORACLE_HOME/rdbms/admin/catdefrt
7. Create replication
sys - $ORACLE_HOME/rdbms/admin/catrep
8. Change system's default tablespace back to what it was.
Issues:
-------
When use RAMN duplicate command to create a test database from a
production database, which is a replication master definition site
database, we want to drop all replication stuff in the test database.
Post-refresh task
--------------------
1. drop replicaiton packages
login as system
------ gen_drop_rep_packages.sql ---
set feedback off;
set echo off;
set pagesize 1000;
set linesize 200;
set heading off;
set echo off;
spool drop_replication_packages.sql;
select 'spool drop_replication_packages.lst;' from dual;
select 'DROP PACKAGE ' owner '.' object_name ';' from
dba_objects
where object_type ='PACKAGE' and (object_name like '%$RP' or
object_name like '%$RL') ;
select 'spool off;' from dual;
select 'exit;' from dual;
spool off;
-- exit;
---------------
select 'DROP PUBLIC SYNONYM ' object_name ';' from dba_objects
where object_type ='SYNONYM' and (object_name like '%$RP' or
object_name like '%$RL') ;
2. login as sys, run catrepr.sql ( remove replciation-replciated catalog
views)
@?/rdbms/admin/catrepr.sql
3. remove replciation related jobs
login as repadmin
select 'exec dbms_job.remove(' job ');' from user_jobs;
'EXECDBMS_JOB.REMOVE('JOB');'
---------------------------------------------------------------
exec dbms_job.remove(98);
exec dbms_job.remove(78);
exec dbms_job.remove(44);
exec dbms_job.remove(79);
exec dbms_job.remove(80);
exec dbms_job.remove(81);
exec dbms_job.remove(82);
exec dbms_job.remove(83);
exec dbms_job.remove(84);
exec dbms_job.remove(85);
exec dbms_job.remove(86);
exec dbms_job.remove(43);
exec dbms_job.remove(87);
exec dbms_job.remove(88);
exec dbms_job.remove(89);
exec dbms_job.remove(90);
exec dbms_job.remove(141);
exec dbms_job.remove(142);
exec dbms_job.remove(144);
exec dbms_job.remove(145);
4. drop database links
repadmin@GENQB> drop database link GENB.WORLD
2 /
Database link dropped.
repadmin@GENQB> @conn system/xxx
system@GENQB> drop public database link GENB.WORLD;
Database link dropped.
drop GENARCHP.WORLD public link
drop public database link GENARCHP.WORLD;
5. Remove the REPADMIN user
DROP USER repadmin CASCADE;
Note: Before re-create replication, maybe need to execulte following:
EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN');
EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN');
6. change password for all users
7. fix global_name
alter database rename global_name to genqb.world
8. remove statspack job too
conn perfstat/xxx
execute dbms_job.remove(119);
9. Add tempfile if not done so
10. Drop gen_rep_data tablespace:
rm the following file after drop the tablespace
system@GENQB> select file_name from dba_data_files where tablespace_name
='GEN_REP_DATA';
FILE_NAME
------------------------------------------------------------------------
----------------------------
/ora02/oradata/GENXQB/gen_rep_data_05.dbf
/ora01/oradata/GENQB/gen_rep_data_01.dbf
/ora02/oradata/GENQB/gen_rep_data_02.dbf
/ora01/oradata/GENQB/gen_rep_data_03.dbf
/ora02/oradata/GENQB/gen_rep_data_04.dbf
1. To remove replication perform the following on the Master Definition Site:
CONNECT repadmin/repadmin@tsh1
-- Stop replication
EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname=>'MYREPGRP');
-- Delete replication groups
EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE');
EXECUTE Dbms_Repcat.Remove_Master_Databases('MYREPGRP', 'TSH2.WORLD');
2. Next do the following on all Master Sites:
CONNECT repadmin/repadmin@tsh1
-- Remove private databse links to other master databases
EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYREPGRP');
DROP DATABASE LINK TSH2.WORLD;
-- Remove any leftover jobs (see DBA_JOBS for job numbers)
EXECUTE Dbms_Job.Remove(62);
EXECUTE Dbms_Job.Remove(63);
CONNECT sys@tsh1
-- Remove the REPADMIN user
EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN');
EXECUTE
Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN');
DROP USER repadmin CASCADE;
-- Drop public database links to other master databases
DROP PUBLIC DATABASE LINK TSH2.WORLD;
