148 lines
3.9 KiB
Plaintext
148 lines
3.9 KiB
Plaintext
-- https://www.dbi-services.com/blog/performing-an-initial-load-with-goldengate-1-file-to-replicat/
|
|
-- https://www.dbi-services.com/blog/performing-an-initial-load-with-goldengate-2-expdpimpdp/
|
|
|
|
Source DB: ANDOPRD@togoria
|
|
Target DB: EWOKPRD@bakura
|
|
|
|
alias gg='rlwrap /app/oracle/product/ogg21/ggsci'
|
|
|
|
# install HR schema on source database
|
|
@install.sql
|
|
|
|
# install HR schema on target database, disable constraints and delete all data
|
|
@install.sql
|
|
|
|
connect / as sysdba
|
|
declare
|
|
lv_statement varchar2(2000);
|
|
begin
|
|
for r in ( select c.CONSTRAINT_NAME, c.TABLE_NAME
|
|
from dba_constraints c
|
|
, dba_tables t
|
|
where c.owner = 'HR'
|
|
and t.table_name = c.table_name
|
|
and t.owner = 'HR'
|
|
and c.constraint_type != 'P'
|
|
)
|
|
loop
|
|
lv_statement := 'alter table hr.'||r.TABLE_NAME||' disable constraint '||r.CONSTRAINT_NAME;
|
|
execute immediate lv_statement;
|
|
end loop;
|
|
for r in ( select table_name
|
|
from dba_tables
|
|
where owner = 'HR'
|
|
)
|
|
loop
|
|
execute immediate 'delete hr.'||r.table_name;
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
select count(*) from hr.employees;
|
|
select count(*) from hr.jobs;
|
|
|
|
# create OGGADMIN user on both databases
|
|
create user OGGADMIN identified by "Chan8em11fUwant!";
|
|
grant dba to OGGADMIN;
|
|
|
|
|
|
# on source machine
|
|
add credentialstore
|
|
info credentialstore domain admin
|
|
alter credentialstore add user OGGADMIN@//togoria:1521/ANDOPRD password "Chan8em11fUwant!" alias ANDOPRD domain admin
|
|
info credentialstore domain admin
|
|
dblogin useridalias ANDOPRD domain admin
|
|
|
|
# on target machine
|
|
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
|
|
|
|
|
|
# on source machine
|
|
dblogin useridalias ANDOPRD domain admin
|
|
list tables HR.*
|
|
add trandata HR.*
|
|
|
|
|
|
# on source, in order to catch transactions during the initial load, we will create an extract for Change Data Capture
|
|
|
|
edit params extrcdc1
|
|
-------------------------------->
|
|
EXTRACT extrcdc1
|
|
useridalias ANDOPRD domain admin
|
|
EXTTRAIL ./dirdat/gg
|
|
LOGALLSUPCOLS
|
|
UPDATERECORDFORMAT compact
|
|
TABLE HR.*;
|
|
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;
|
|
<--------------------------------
|
|
|
|
dblogin useridalias ANDOPRD domain admin
|
|
register extract extrcdc1 database
|
|
|
|
add extract extrcdc1, integrated tranlog, begin now
|
|
EXTRACT added.
|
|
|
|
add extract extrcdc1, integrated tranlog, begin now
|
|
add exttrail ./dirdat/gg, extract extrcdc1, megabytes 5
|
|
|
|
# on source, configure the datapump
|
|
edit params dppump1
|
|
-------------------------------->
|
|
EXTRACT dppump1
|
|
PASSTHRU
|
|
RMTHOST bakura, MGRPORT 7809
|
|
RMTTRAIL ./dirdat/jj
|
|
TABLE HR.*;
|
|
TABLEEXCLUDE HR.EMP_DETAILS_VIEW;
|
|
<--------------------------------
|
|
|
|
add extract dppump1, exttrailsource ./dirdat/gg
|
|
add rmttrail ./dirdat/jj, extract dppump1, megabytes 5
|
|
|
|
# on sourxe, start extracts CDC capture and datapump
|
|
start extract dppump1
|
|
start extract extrcdc1
|
|
info *
|
|
|
|
# on target, configure replicat for CDC
|
|
|
|
edit params replcdd
|
|
-------------------------------->
|
|
REPLICAT replcdd
|
|
ASSUMETARGETDEFS
|
|
DISCARDFILE ./dirrpt/replccd.dsc, purge
|
|
useridalias EWOKPRD domain admin
|
|
MAP HR.*, TARGET HR.*;
|
|
<--------------------------------
|
|
|
|
dblogin useridalias EWOKPRD domain admin
|
|
add replicat replcdd, integrated, exttrail ./dirdat/jj
|
|
|
|
# We will NOT START the replicat right now as we wan to do the initial load before
|
|
|
|
# Note down the current scn of the source database
|
|
SQL> select current_scn from v$database;
|
|
|
|
CURRENT_SCN
|
|
-----------
|
|
3968490
|
|
|
|
# on destination, import HS schema
|
|
create public database link ANDOPRD connect to OGGADMIN identified by "Chan8em11fUwant!" using '//togoria:1521/ANDOPRD';
|
|
select * from DUAL@ANDOPRD;
|
|
|
|
impdp userid=OGGADMIN/"Chan8em11fUwant!"@//bakura:1521/EWOKPRD logfile=MY:HR.log network_link=ANDOPRD schemas=HR flashback_scn=3968490
|
|
|
|
start replicat replcdd, aftercsn 3968490
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|