Hi,
I'm having a problem with the volume of archive redo logging generated on the target during my initial load extract. I am using integrated capture and apply in a bi-directional replication setup. The schemas are identical on both sides. I perform the initial load using BULKLOAD to sqlloader. My database is Oracle Database 19c Enterprise Edition and Golden Gate is v19.1.0.0.0. There's nobody else on either database while I'm performing the Initial Load. I'm seeing 4GB of archive redo log files generated per minute. My server runs out of storage before the Initial Load can complete. The .dbf files that constitute the tablespace associated with the schema I'm copying are about 2GB, total.
Appearing below is the process that I use to execute the Initial Load Extract. Most parameter files are include below that.
Any help is much appreciated. Kindly let me know if there's any information that I left out that's important for answering this question or understanding my setup.
Best,
Dan
ON THE PRIMARY
From SQL*Plus:
alter database add supplemental log data;
alter database force logging;
alter system switch logfile;
alter system set enable_goldengate_replication=TRUE;
alter system set streams_pool_size=2G scope=both;
From GGSCI:
dblogin userid stradmin@MYSID1, password mypassword
add schematrandata "ISX" ALLCOLS
ALLOWNESTED
dblogin userid stradmin@MYSID1, password mypassword
register extract ecapture with database
add extract ecapture, integrated tranlog, begin now
add exttrail ./dirdat/MYSID1/aa, extract ecapture, megabytes 500
dblogin userid stradmin@MYSID1, password mypassword
register replicat RMYSID2 database
add replicat RMYSID2 integrated, exttrail ./dirdat/MYSID2/STRADMIN/APPLY_Q_SRC_MYSID2/zz, nodbcheckpoint
add extract pcapture, exttrailsource ./dirdat/MYSID1/aa
add rmttrail ./dirdat/MYSID1/STRADMIN/APPLY_Q_SRC_MYSID1/zz, extract pcapture, megabytes 500
stop RMYSID2
ON THE SECONDARY
From SQL*Plus:
alter database add supplemental log data;
alter database force logging;
alter system switch logfile;
alter system set enable_goldengate_replication=TRUE;
alter system set streams_pool_size=2G scope=both;
alter user stradmin default tablespace isx quota unlimited on isx;
From GGSCI:
dblogin userid stradmin@MYSID2, password mypassword
add schematrandata "ISX" ALLCOLS
dblogin userid stradmin@MYSID2, password mypassword
register extract ecapture with database
add extract ecapture, integrated tranlog, begin now
add exttrail ./dirdat/MYSID2/aa, extract ecapture, megabytes 500
dblogin userid stradmin@MYSID2, password mypassword
register replicat RMYSID1 database
add replicat RMYSID1 integrated, exttrail ./dirdat/MYSID2/STRADMIN/APPLY_Q_SRC_MYSID1/zz, nodbcheckpoint
add extract pcapture, exttrailsource ./dirdat/MYSID2/aa
add rmttrail ./dirdat/MYSID1/STRADMIN/APPLY_Q_SRC_MYSID2/zz, extract pcapture, megabytes 500
stop ecapture
stop pcapture
stop RMYSID1
start manager
Create Initial Load Replicat:
ADD REPLICAT INITLR, SPECIALRUN
FROM THE PRIMARY
From GGSCI:
start manager
Create Initial Load Extract:
ADD EXTRACT INITLE, SOURCEISTABLE
start ecaptuire
start pcapture
FLUSH SEQUENCE ISX.A_SEQUENCE
start initle
[Wait for Initial Load Extract to complete. This is when all of the archive redo logs are generated. There's nobody else using the source or the target database during this time.]
ON THE SECONDARY
From GGSCI:
start RMYSID1
start ecapture
start pcapture
[now bi-directional replication is set up and running]
Initial Load Extract Parameter File:
EXTRACT INITLE
userid stradmin@MYSID1, password mypassword
RMTHOST 1.1.1.1, mgrport 7809, ENCRYPT AES256
RMTTASK REPLICAT, GROUP INITLR
TABLE ISX.*;
Initial Load Replica Parameter File:
REPLICAT INITLR
userid stradmin@MYSID2, password mypassword
BULKLOAD NOLOGGING PARALLEL
DISCARDFILE ./dirrpt/initlr.dsc, MEGABYTES 500, append
ASSUMETARGETDEFS
MAP ISX.* TARGET ISX.*;
ecapture.prm
extract ecapture
SETENV (ORACLE_HOME = "/home/root/home/xxx/oracle/oracle/product/19.0.0/dbHome_1")
SETENV (ORACLE_SID = "MYSID1")
userid stradmin@MYSID1, password mypassword
exttrail ./dirdat/MYSID1/aa
INCLUDE ./dirprm/report.prm
WARNLONGTRANS 1H, CHECKINTERVAL 30m
LOGALLSUPCOLS
IGNORETRUNCATES
discardfile ./dirrpt/ECAPTURE.dsc, PURGE, MEGABYTES 500
DISCARDROLLOVER AT 23:59
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS INTEGRATEDPARAMS (_CHECKPOINT_FORCE N, _CHECKPOINT_FREQUENCY 100, _CKPT_RETENTION_CHECK_FREQ 600, _LOGMINER_READ_BUFFERS 256)
DDL EXCLUDE ALL
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG +
TABLE "ISX".*;
pcapture.prm
extract pcapture
SETENV (ORACLE_HOME = "/home/root/home/xxx/oracle/oracle/product/19.0.0/dbHome_1")
SETENV (ORACLE_SID = "MYSID1")
INCLUDE ./dirprm/MYSID1_stradmin.prm
INCLUDE ./dirprm/report.prm
rmthost 1.1.1.2, mgrport 7809, ENCRYPT AES256, PARAMS -f -B1048576, TCPBUFSIZE 10000000, COMPRESS
rmttrail ./dirdat/MYSID2/STRADMIN/APPLY_Q_SRC_MYSID1/zz
discardfile ./dirrpt/pcapture.dsc, PURGE, MEGABYTES 500
DISCARDROLLOVER AT 23:59
PASSTHRU
TABLE "ISX"."*";
mgr.prm
PORT 7809
ACCESSRULE, PROG *, IPADDR 1.1.1.2, ALLOW
DYNAMICPORTLIST 7819-7899
LAGREPORTMINUTES 5
LAGINFOMINUTES 5
LAGCRITICALMINUTES 15
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1, FREQUENCYMINUTES 5
RMYSID2.prm
MACRO #exception_handler
BEGIN
, TARGET STRADMIN.GG_EXCEPTIONS
, COLMAP ( rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME')
, TABLE_NAME = @GETENV ('GGHEADER', 'TABLENAME')
, ERRNO = @GETENV ('LASTERR', 'DBERRNUM')
, DBERRMSG = @GETENV ('LASTERR', 'DBERRMSG')
, OPTYPE = @GETENV ('LASTERR', 'ERRTYPE')
, LOGRBA = @GETENV ('GGHEADER', 'LOGRBA')
, LOGPOSITION = @GETENV ('GGHEADER', 'LOGPOSITION')
, COMMITTIMESTAMP = @GETENV ('GGHEADER', 'COMMITTIMESTAMP')
, GGS_FILENAME = @GETENV ('GGFILEHEADER', 'FILENAME')
, CDRFAIL = @GETENV ('DELTASTATS', 'CDR_RESOLUTIONS_FAILED')
, CDRSUC = @GETENV ('DELTASTATS', 'CDR_RESOLUTIONS_SUCCEEDED')
, CDRDETECT = @GETENV ('DELTASTATS', 'CDR_CONFLICTS'))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
replicat RMYSID2
SETENV (ORACLE_HOME = "/home/root/home/xxx/oracle/oracle/product/19.0.0/dbHome_1")
SETENV (ORACLE_SID = "MYSID1")
GETENV (NLS_LANG)
userid stradmin@MYSID1, password mypassword
BATCHSQL
HANDLECOLLISIONS
ASSUMETARGETDEFS
discardfile ./dirrpt/RMYSID2.dsc, PURGE, MEGABYTES 500
DISCARDROLLOVER AT 23:59
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, EXCEPTION)
REPORTCOUNT EVERY 5 MINUTES, RATE
DBOPTIONS DEFERREFCONST
DBOPTIONS SETTAG 00
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS _NO_DISABLE_ON_RETRY_ERROR
DBOPTIONS INTEGRATEDPARAMS (PARALLELISM 6)
DBOPTIONS INTEGRATEDPARAMS (_DATA_LAYER N)
REPERROR DEFAULT DISCARD
IGNORETRUNCATES
SQLEXEC "alter session set commit_wait = 'NOWAIT'";
SQLEXEC "alter session set commit_logging = 'BATCH'";
DDL EXCLUDE ALL
MAP "ISX"...
[map statements excluded]
MAP "ISX".* ,TARGET "ISX".*,
COMPARECOLS (
ON UPDATE ALL,
ON DELETE ALL);