alter session force parallel query parallel 10;
alter session force parallel ddl parallel 10;
alter session force parallel dml parallel 10;
create materialized view TABLE_O refresh fast  with primary key  on demand  as
select * FROM  USER.BIG_TABLE
where mod(decode(length(col1), 1,ASCII(substr(col1, -1, 1)),
2, ASCII(substr(col1, -1, 1)) + ASCII(substr(col1, -2, 1)),
3, ASCII(substr(col1, -1, 1)) + ASCII(substr(col1, -2, 1)) + ASCII(substr(col1, -3, 1)) ,
......
, 150) = 0 ;

CREATE USER GOLDENGATE IDENTIFIED BY goldengate DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO GOLDENGATE;
GRANT ALTER ANY TABLE TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT CREATE SESSION TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT SELECT ANY TABLE TO GOLDENGATE;
GRANT RESOURCE TO GOLDENGATE;
GRANT EXECUTE ANY TYPE TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;
GRANT CREATE any  TABLE,CREATE any  SEQUENCE TO goldengate ;
GRANT INSERT ANY TABLE TO GOLDENGATE;
GRANT UPDATE ANY TABLE TO GOLDENGATE;
GRANT DELETE ANY TABLE TO GOLDENGATE;
GRANT CREATE ANY INDEX TO GOLDENGATE;
grant unlimited  tablespace to goldengate ;
grant execute on DBMS_FLASHBACK to goldengate;
grant  COMMENT ANY TABLE  to goldengate;
alter system set enable_goldengate_replication=true sid=* scope=both;
exec dbms_goldengate_auth.grant_admin_privilege(goldengate)

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter database FORCE LOGGING;
set line 350 pages 999
col NAME for a15
col LOG_MODE for a15
col OPEN_MODE for a15
col PLATFORM_NAME for a25
col FORCE_LOGGING for a20
col SUPPLEMENTAL_LOG_DATA_MIN for a25
SELECT
NAME,
LOG_MODE,
OPEN_MODE,
PLATFORM_NAME,
FORCE_LOGGING,
SUPPLEMENTAL_LOG_DATA_MIN
FROM
V$DATABASE;
alter system archive log current;

export TNS_ADMIN=/data/ggsdata/tnsadmin
exora=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.11 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =test )
)
)

add credentialstore
alter credentialstore add user goldengate@exora alias exora
INFO CREDENTIALSTORE
dblogin useridalias exora

add  trandata user.table_0
info  trandata user.table_0
add  trandata user.table_1
info  trandata user.table_2
......

PORT 7809
DYNAMICPORTLIST 7810-7819
ACCESSRULE, PROG SERVER, ALLOW
AUTOSTART EXTRACT *
AUTORESTART EXTRACT * retries 5 waitminutes 3
PURGEOLDEXTRACTS /ogg/ggs_oracle/dirdat/*,usecheckpoints, minkeepdays 2
LAGREPORTMINUTES 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 60

Start  mgr

--base parameter
extract ex_ora
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
SETENV (TNS_ADMIN=/data/ggsdata/tnsadmin)
useridalias exora
numfiles 5000
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256, parallelism 1)
exttrail /data/ggsdata/dirdat/yx
DISCARDFILE /data/ggsdata/dirrpt/ex_ora_01.dsc,APPEND,MEGABYTES 100
DISCARDROLLOVER AT 3:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 9:00
WARNLONGTRANS 2h, CHECKINTERVAL 30m
TRANLOGOPTIONS INTEGRATEDPARAMS(_CKPT_RETENTION_CHECK_FREQ 300)
TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE
-- Performance optimization
cachemgr cachesize 512M, cachedirectory /data/ggsdata/dirtmp 10G
BR BRINTERVAL 40M, BRDIR BR
--map objects(150個分表)
table UPSYS.t_audition_0;
table UPSYS.t_audition_1;......

dblogin useridalias exora
REGISTER EXTRACT ex_ora , DATABASE
ADD EXTRACT ex_ora, INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL /data/ggsdata/dirdat/yx, EXTRACT ex_ora , MEGABYTES 50
start  exora

create user goldengate@% identified by Goldengate-123;
grant all on *.* to goldengate@%;

edit params mgr

PORT 7839
DYNAMICPORTLIST 7840-7849
ACCESSRULE, PROG SERVER, ALLOW
AUTOSTART REPLICAT *
AUTORESTART REPLICAT * retries 5 waitminutes 1
--PURGEOLDEXTRACTS /ogg/ggs_mysql/dirdat/*,usecheckpoints, minkeepdays 2
LAGREPORTMINUTES 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 60
--STARTUPVALIDATIONDELAY 5
DBLOGIN sourcedb user@10.10.10.12:3306, USERID goldengate, PASSWORD Goldengate-123


Start mgr

create database pt;

cd $OGG_HOME
ggsci
edit param ./GLOBALS
checkpointtable pt.ggs_checkpoint

DBLOGIN sourcedb pt@10.10.10.12:3306, USERID goldengate, PASSWORD Goldengate-123
add checkpointtable pt.ggs_checkpoint
add replicat rp_ora,exttrail /data/ggsdata/dirdat/yx,checkpointtable pt.ggs_checkpoint

edit params rp_ora

replicat rp_ora
targetdb user@10.10.10.12:3306,userid goldengate,password Goldengate-123
--sourcedefs   /data/ggsdata/dirdef/zly.def
discardfile /data/ggsdata/dirrpt/rep_exora.dsc,append,megabytes 50
REPERROR DEFAULT, ABEND
REPORTCOUNT EVERY 60 MINUTES, RATE
REPORTROLLOVER AT 9:00

MAP user.table_0, target user.table_0;
MAP user.table_1, target user.table_1;
......
MAP user.table_149, target user.table_149;

Start rp_ora

set linesize 350 pages 999
col OWNER for a15
col JOB_NAME for a25
col JOB_CREATOR for a25
col ENABLED for a10
col START_DATE for a25
col NEXT_RUN_DATE for a25
col LAST_START_DATE for a25
col REPEAT_INTERVAL for a40
col JOB_ACTION for a60
SELECT OWNER,
JOB_NAME,
ENABLED,
STATE,
JOB_TYPE,
JOB_ACTION,
to_char(START_DATE, yyyy/mm/dd hh:mm) START_DATE,
to_char(LAST_START_DATE, yyyy/mm/dd hh:mm) LAST_START_DATE,
to_char(NEXT_RUN_DATE, yyyy/mm/dd hh:mm) NEXT_RUN_DATE,
REPEAT_INTERVAL,
RUN_COUNT
FROM dba_scheduler_jobs
WHERE
JOB_ACTION LIKE %TABLE_%"%
ORDER BY 6 ;

exec dbms_scheduler.enable(name=>upper(UPSYS.MV_RF$J_0_S_55082) );