417 lines
9.8 KiB
Plaintext
417 lines
9.8 KiB
Plaintext
# setup source schema
|
|
#####################
|
|
|
|
create user WOMBAT identified by "NDbGvewNHVj8@#2FFGfz!De";
|
|
grant connect, resource to WOMBAT;
|
|
alter user WOMBAT quota unlimited on USERS;
|
|
|
|
connect WOMBAT/"NDbGvewNHVj8@#2FFGfz!De";
|
|
|
|
drop table T0 purge;
|
|
drop table T1 purge;
|
|
drop table T2 purge;
|
|
drop table T3 purge;
|
|
|
|
create table JOB (
|
|
id NUMBER GENERATED ALWAYS AS IDENTITY,
|
|
d DATE not null
|
|
);
|
|
alter table JOB add constraint JOB_PK_ID primary key (ID);
|
|
|
|
|
|
create table T0 (
|
|
id NUMBER GENERATED ALWAYS AS IDENTITY,
|
|
d DATE not null,
|
|
c VARCHAR2(20),
|
|
n NUMBER
|
|
)
|
|
partition by range (d)
|
|
interval (interval '1' MONTH) (
|
|
partition p0 values less than (DATE'2000-01-01')
|
|
)
|
|
;
|
|
|
|
alter table T0 add constraint T0_PK_ID primary key (ID);
|
|
|
|
create table T1 (
|
|
d DATE not null,
|
|
c VARCHAR2(10),
|
|
n1 NUMBER,
|
|
n2 NUMBER
|
|
)
|
|
partition by range (d)
|
|
interval (interval '1' MONTH) (
|
|
partition p0 values less than (DATE'2000-01-01')
|
|
)
|
|
;
|
|
|
|
create table T2 (
|
|
d DATE not null,
|
|
n1 NUMBER,
|
|
n2 NUMBER,
|
|
n3 NUMBER
|
|
)
|
|
partition by range (d)
|
|
interval (interval '1' MONTH) (
|
|
partition p0 values less than (DATE'2000-01-01')
|
|
)
|
|
;
|
|
|
|
create table T3 (
|
|
d DATE not null,
|
|
n NUMBER,
|
|
c1 VARCHAR2(10),
|
|
c2 VARCHAR2(10),
|
|
c3 VARCHAR2(10)
|
|
)
|
|
partition by range (d)
|
|
interval (interval '1' MONTH) (
|
|
partition p0 values less than (DATE'2000-01-01')
|
|
)
|
|
;
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION random_date(
|
|
p_from IN DATE,
|
|
p_to IN DATE
|
|
) RETURN DATE
|
|
IS
|
|
BEGIN
|
|
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
|
|
END random_date;
|
|
/
|
|
|
|
CREATE OR REPLACE FUNCTION random_string(
|
|
maxsize IN NUMBER
|
|
) RETURN VARCHAR2
|
|
IS
|
|
BEGIN
|
|
RETURN dbms_random.string('x',maxsize);
|
|
END random_string;
|
|
/
|
|
|
|
CREATE OR REPLACE FUNCTION random_integer(
|
|
maxvalue IN NUMBER
|
|
) RETURN NUMBER
|
|
IS
|
|
BEGIN
|
|
RETURN trunc(dbms_random.value(1,maxvalue));
|
|
END random_integer;
|
|
/
|
|
|
|
# add some data into tables
|
|
###########################
|
|
|
|
set timing ON
|
|
|
|
DECLARE
|
|
imax NUMBER default 100000;
|
|
i number;
|
|
begin
|
|
dbms_random.seed (val => 0);
|
|
for i in 1 .. imax loop
|
|
insert /*+ APPEND */ into T0 (d,c,n) values (random_date(DATE'2000-01-01',SYSDATE),random_string(20),random_integer(999999999));
|
|
end loop;
|
|
commit;
|
|
end;
|
|
/
|
|
|
|
DECLARE
|
|
imax NUMBER default 100000;
|
|
i number;
|
|
begin
|
|
dbms_random.seed (val => 0);
|
|
for i in 1 .. imax loop
|
|
insert /*+ APPEND */ into T1 (d,c,n1,n2) values (random_date(DATE'2000-01-01',SYSDATE),random_string(10),random_integer(999999999),random_integer(999999999));
|
|
end loop;
|
|
commit;
|
|
end;
|
|
/
|
|
|
|
DECLARE
|
|
imax NUMBER default 100000;
|
|
i number;
|
|
begin
|
|
dbms_random.seed (val => 0);
|
|
for i in 1 .. imax loop
|
|
insert /*+ APPEND */ into T2 (d,n1,n2,n3) values (random_date(DATE'2000-01-01',SYSDATE),random_integer(999999999),random_integer(999999999),random_integer(999999999));
|
|
end loop;
|
|
commit;
|
|
end;
|
|
/
|
|
|
|
DECLARE
|
|
imax NUMBER default 100000;
|
|
i number;
|
|
begin
|
|
dbms_random.seed (val => 0);
|
|
for i in 1 .. imax loop
|
|
insert /*+ APPEND */ into T3 (d,n,c1,c2,c3) values (random_date(DATE'2000-01-01',SYSDATE),random_integer(999999999),random_string(10),random_string(10),random_string(10));
|
|
end loop;
|
|
commit;
|
|
end;
|
|
/
|
|
|
|
|
|
# run this PL/SQL block to generate living data
|
|
###############################################
|
|
connect WOMBAT/"NDbGvewNHVj8@#2FFGfz!De";
|
|
|
|
DECLARE
|
|
i number;
|
|
begin
|
|
loop
|
|
sys.dbms_session.sleep(5);
|
|
dbms_random.seed (val => 0);
|
|
i:=random_integer(999999999);
|
|
insert into JOB (d) values (sysdate);
|
|
|
|
update T0 set c=random_string(20) where n=i;
|
|
update T1 set c=random_string(20) where n2 between i-1000 and i+1000;
|
|
update T2 set d=random_date(DATE'2000-01-01',SYSDATE) where n1 between i-1000 and i+1000;
|
|
update T3 set c1=random_string(20),d=random_date(DATE'2000-01-01',SYSDATE) where n between i-1000 and i+1000;
|
|
|
|
insert into T0 (d,c,n) values (random_date(DATE'2000-01-01',SYSDATE),random_string(20),random_integer(999999999));
|
|
insert into T1 (d,c,n1,n2) values (random_date(DATE'2000-01-01',SYSDATE),random_string(10),random_integer(999999999),random_integer(999999999));
|
|
insert into T2 (d,n1,n2,n3) values (random_date(DATE'2000-01-01',SYSDATE),random_integer(999999999),random_integer(999999999),random_integer(999999999));
|
|
insert into T3 (d,c1,c2,c3) values (random_date(DATE'2000-01-01',SYSDATE),random_string(10),random_string(10),random_string(10));
|
|
|
|
commit;
|
|
exit when 1=0;
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
|
|
## Golden Gate setup
|
|
####################
|
|
|
|
# on source & destination
|
|
alias gg='rlwrap /app/oracle/product/ogg21/ggsci'
|
|
|
|
create user OGGADMIN identified by "eXtpam!ZarghOzVe81p@1";
|
|
# maybe too much
|
|
grant DBA to OGGADMIN;
|
|
|
|
Edit params ./GLOBALS
|
|
#-->
|
|
GGSCHEMA OGGADMIN
|
|
#<--
|
|
|
|
# on source
|
|
add credentialstore
|
|
info credentialstore domain admin
|
|
alter credentialstore add user OGGADMIN@//togoria:1521/ANDOPRD password "eXtpam!ZarghOzVe81p@1" alias ANDOPRD domain admin
|
|
dblogin useridalias ANDOPRD domain admin
|
|
|
|
# on destination
|
|
add credentialstore
|
|
info credentialstore domain admin
|
|
alter credentialstore add user OGGADMIN@//bakura:1521/EWOKPRD password "Chan8em11fUwant!" alias EWOKPRD domain admin
|
|
info credentialstore domain admin
|
|
dblogin useridalias EWOKPRD domain admin
|
|
|
|
|
|
# setup replication only for tables T0, T1 and T2
|
|
#################################################
|
|
|
|
# on source machine
|
|
dblogin useridalias ANDOPRD domain admin
|
|
list tables WOMBAT.*
|
|
add trandata WOMBAT.T0
|
|
add trandata WOMBAT.T1
|
|
add trandata WOMBAT.T2
|
|
|
|
edit params extr_w1
|
|
-------------------------------->
|
|
EXTRACT extr_w1
|
|
useridalias ANDOPRD domain admin
|
|
EXTTRAIL ./dirdat/w1
|
|
LOGALLSUPCOLS
|
|
UPDATERECORDFORMAT compact
|
|
table WOMBAT.T0;
|
|
table WOMBAT.T1;
|
|
table WOMBAT.T2;
|
|
<--------------------------------
|
|
|
|
dblogin useridalias ANDOPRD domain admin
|
|
register extract extr_w1 database
|
|
|
|
add extract extr_w1, integrated tranlog, begin now
|
|
add exttrail ./dirdat/w1, extract extr_w1, megabytes 5
|
|
|
|
start extr_w1
|
|
info extr_w1
|
|
|
|
# on source, configure the datapump
|
|
edit params dpump_w1
|
|
-------------------------------->
|
|
EXTRACT dpump_w1
|
|
PASSTHRU
|
|
RMTHOST bakura, MGRPORT 7809
|
|
RMTTRAIL ./dirdat/w1
|
|
table WOMBAT.T0;
|
|
table WOMBAT.T1;
|
|
table WOMBAT.T2;
|
|
<--------------------------------
|
|
|
|
add extract dpump_w1, exttrailsource ./dirdat/w1
|
|
add rmttrail ./dirdat/w1, extract dpump_w1, megabytes 5
|
|
|
|
start dpump_w1
|
|
info dpump_w1
|
|
|
|
# on target, setup replcat but not start it
|
|
edit params repl_w1
|
|
-------------------------------->
|
|
REPLICAT repl_w1
|
|
ASSUMETARGETDEFS
|
|
DISCARDFILE ./dirrpt/repl_w1.dsc, purge
|
|
useridalias EWOKPRD domain admin
|
|
MAP WOMBAT.T0, TARGET OTTER.T0;
|
|
MAP WOMBAT.T1, TARGET OTTER.T1;
|
|
MAP WOMBAT.T2, TARGET OTTER.T2;
|
|
<--------------------------------
|
|
|
|
dblogin useridalias EWOKPRD domain admin
|
|
add replicat repl_w1, integrated, exttrail ./dirdat/w1
|
|
|
|
# perform the intial LOAD
|
|
#########################
|
|
|
|
# Note down the current scn of the source database
|
|
SQL> select current_scn from v$database;
|
|
|
|
CURRENT_SCN
|
|
-----------
|
|
4531616
|
|
|
|
# on destination, import tables
|
|
create public database link ANDOPRD connect to OGGADMIN identified by "Chan8em11fUwant!" using '//togoria:1521/ANDOPRD';
|
|
select * from DUAL@ANDOPRD;
|
|
|
|
# create target schema using same DDL defionotion as on source database
|
|
create user OTTER identified by "50DbGvewN00K@@)2FFGfzKg";
|
|
grant connect, resource to OTTER;
|
|
alter user OTTER quota unlimited on USERS;
|
|
|
|
impdp userid=OGGADMIN/"Chan8em11fUwant!"@//bakura:1521/EWOKPRD logfile=MY:WOMBAT_01.log network_link=ANDOPRD tables=WOMBAT.T0,WOMBAT.T1,WOMBAT.T2 flashback_scn=4531616 remap_schema=WOMBAT:OTTER
|
|
|
|
start repl_w1, aftercsn 4531616
|
|
|
|
# when LAG is catched, retart replcat
|
|
stop repl_w1
|
|
start repl_w1
|
|
info repl_w1
|
|
|
|
# add 2 tables to SYNC
|
|
######################
|
|
|
|
# on source, add 2 tables to extract & datapump
|
|
stop dpump_w1
|
|
stop extr_w1
|
|
|
|
# add new tables in extract & datapump parameter files
|
|
edit params extr_w1
|
|
-------------------------------->
|
|
EXTRACT extr_w1
|
|
useridalias ANDOPRD domain admin
|
|
EXTTRAIL ./dirdat/w1
|
|
LOGALLSUPCOLS
|
|
UPDATERECORDFORMAT compact
|
|
table WOMBAT.T0;
|
|
table WOMBAT.T1;
|
|
table WOMBAT.T2;
|
|
table WOMBAT.JOB;
|
|
table WOMBAT.T3;
|
|
<--------------------------------
|
|
|
|
# add trandata for new tables
|
|
dblogin useridalias ANDOPRD domain admin
|
|
list tables WOMBAT.*
|
|
add trandata WOMBAT.JOB
|
|
add trandata WOMBAT.T3
|
|
|
|
start extr_w1
|
|
info extr_w1
|
|
|
|
edit params dpump_w1
|
|
-------------------------------->
|
|
EXTRACT dpump_w1
|
|
PASSTHRU
|
|
RMTHOST bakura, MGRPORT 7809
|
|
RMTTRAIL ./dirdat/w1
|
|
table WOMBAT.T0;
|
|
table WOMBAT.T1;
|
|
table WOMBAT.T2;
|
|
table WOMBAT.JOB;
|
|
table WOMBAT.T3;
|
|
<--------------------------------
|
|
|
|
start dpump_w1
|
|
info dpump_w1
|
|
|
|
# once extract & datapump are up and running, we will proceed with the initial load of the nexw tables using expdp/impdp
|
|
# Note down the current scn of the source database
|
|
SQL> select current_scn from v$database;
|
|
|
|
CURRENT_SCN
|
|
-----------
|
|
4675686
|
|
|
|
impdp userid=OGGADMIN/"Chan8em11fUwant!"@//bakura:1521/EWOKPRD logfile=MY:WOMBAT_02.log network_link=ANDOPRD tables=WOMBAT.JOB,WOMBAT.T3 flashback_scn=4675686 remap_schema=WOMBAT:OTTER
|
|
|
|
# on target, stop replicat, add new tables and start FROM THE GOOD SCN ON NEW TABLES
|
|
stop repl_w1
|
|
|
|
edit params repl_w1
|
|
-------------------------------->
|
|
REPLICAT repl_w1
|
|
ASSUMETARGETDEFS
|
|
DISCARDFILE ./dirrpt/repl_w1.dsc, purge
|
|
useridalias EWOKPRD domain admin
|
|
MAP WOMBAT.T0, TARGET OTTER.T0;
|
|
MAP WOMBAT.T1, TARGET OTTER.T1;
|
|
MAP WOMBAT.T2, TARGET OTTER.T2;
|
|
MAP WOMBAT.JOB, TARGET OTTER.JOB, filter(@GETENV ('TRANSACTION','CSN') > 4633243);
|
|
MAP WOMBAT.T3, TARGET OTTER.T3, filter(@GETENV ('TRANSACTION','CSN') > 4633243);
|
|
<--------------------------------
|
|
|
|
start repl_w1
|
|
info repl_w1
|
|
|
|
# wen lag is catched, remove SCN clauses from replicat and restart
|
|
|
|
stop repl_w1
|
|
|
|
edit params repl_w1
|
|
-------------------------------->
|
|
REPLICAT repl_w1
|
|
ASSUMETARGETDEFS
|
|
DISCARDFILE ./dirrpt/repl_w1.dsc, purge
|
|
useridalias EWOKPRD domain admin
|
|
MAP WOMBAT.T0, TARGET OTTER.T0;
|
|
MAP WOMBAT.T1, TARGET OTTER.T1;
|
|
MAP WOMBAT.T2, TARGET OTTER.T2;
|
|
MAP WOMBAT.JOB, TARGET OTTER.JOB;
|
|
MAP WOMBAT.T3, TARGET OTTER.T3;
|
|
<--------------------------------
|
|
|
|
start repl_w1
|
|
info repl_w1
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|