To be addressed : 1.How to enable monitoring 2.Regular maintenance tasks 3.DB refresh , How to handle it ? Prerequisites : Source : Establish network connectivity between source and target Enable firewall between source and target Enable exadata to send outboud traffic to the target host of port number 7801-7820 << Needs to be done only by exadata admins Install gg software in source create mgr.prm with required settings create GLOBALS file and allowoutputdirectory if required Create a test table in source server Configure test source to target replication. Check for undo retention and how to retain before image of a row for longer time to do scn based sourcetable. setup oem monitoring Target : Establish network connectivity between source and target Enable firewall between source and target Enable exadata to send outboud traffic to the target host of port number 7801-7820 << Needs to be done only by exadata admins Install gg software in source create mgr.prm with required settings create GLOBALS file and allowoutputdirectory if required Create a test table in source server Configure test source to target replication. setup oem monitoring Query to find the obejct existance in source: Check if the source dirdat folder has sufficient space to hold the files. Check if all the tables requested for the replication present in the database. set echo on set head off spool /tmp/a.log @/tmp/query1.sql select owner from dba_objects where object_name='xxx'; Spool off -----Source Configuration----- 1-Create the Instalation directory to receive the Oracle GoldenGate software cd /u01/app/oracle mkdir ogg Copy the software zip file to ogg location unzip software zip file 2-Start the GoldenGate Software Command Interface (GGSCI). Create the default empty subdirectories ./ggsci Create Subdirs exit If a directory already exists, the installation leaves the contents of that directory alone. You have successfully installed Oracle GoldenGate on Linux 3- Configure the Environment ----verify that LOG_MODE is set to ARCHIVELOG. SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ ARCHIVELOG -----if the DB is in NOARCHIVELOG follow the below steps after all approval with application and management-- SQL>shutdown immediate SQL>startup mount SQL>ALTER DATABASE ARCHIVELOG; SQL>ALTER DATABASE OPEN; SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ ARCHIVELOG ------Verify that supplemental logging and forced logging are set properly.-------- SQL> SELECT force_logging, supplemental_log_data_min FROM v$database; FOR SUPPLEME --- -------- YES YES ---if output is NO follow the below step----- SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> ALTER DATABASE FORCE LOGGING; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> show parameter enable_goldengate_replication SQL> alter system set enable_goldengate_replication=TRUE scope=both;<< Enable the parameter if not enabled. SQL> select force_logging from v$database; SQL> alter database force logging; << Enable it if not enabled especially if you use schematrandata Create common user and local user if not already present SQL> create user GG_USER identified by GG_USER ; SQL> GRANT CREATE SESSION TO GG_USER; SQL> GRANT DBA to GG_USER; Test connectivity: connect GG_USER/GG_USER@localhost:1521/ltru1 Create environment file ggsci>DBLOGIN USERID GG_USER PASSWORD GG_USER << check if db log is successful ggsci > ADD CREDENTIALSTORE <<< if credential store does not existis, oracle creates empty dircrd by using this command. ggsci > INFO CREDENTIALSTORE -------ggsci > ALTER CREDENTIALSTORE ADD USER GG_USER@DTRU1 PASSWORD oracle ALIAS GGUSERDTRU1 << container database -------ggsci > ALTER CREDENTIALSTORE ADD USER GG_USER PASSWORD password oracle ALIAS GG_USER << container database ggsci > ALTER CREDENTIALSTORE ADD USER GG_USER PASSWORD GG_USER ALIAS GG_USER << Non container database ggsci > DBLOGIN USERIDALIAS GG_USER << Non container database ggsci > DBLOGIN USERIDALIAS GG_USER <<< Test dblogin ggsci > DBLOGIN USERID test@pdb1, PASSWORD test_pass <<< Test dblogin *********************** Add trandata for all the tables or schematrandata ggsci > DBLOGIN USERIDALIAS FAEMBTR1 -------ggsci > ADD SCHEMATRANDATA pdb1.test ALLCOLS << To take care of current,future and altering tables reference doc below. -------ggsci > ADD TRANDATA pdb1.test.tcustmer ALLCOLS << Only if few tables are going to be replicated and need to be done every time a table is added/altered Check if the suplement logging has been enabled if you use add trandata command. ggsci > info trandata schemaname.tablename SQL >select owner, log_group_name, table_name, log_group_type, always, generated from dba_log_groups where owner = 'TP_DATA_SIT' and log_group_name like 'GGS%'; Check if supplement logging enabled at schema level ggsci > info schematrandata dtru1.TP_DATA_SIT ---------Need to check for DEFGEN---- Take list of tables to do initial load if not provided by appteam. set feedback off; set echo off set pagesize 0 set linesize 500 spool /tmp/atables.log select 'TABLE ' || owner || '.'|| table_name || ';'from dba_tables where owner in ('TP_AUDIT_TRG','TP_DATA_TRG','TP_EXTN_TRG','TP_METADATA_TRG','TP_STAGE_TRG','TP_USERS_TRG','TP_REPORTS_TRG'); ------------------------------------------------- ---: Create manager process Step 1 : Create extract process : Configure Classic Extract: ironment========================== vi eddec1.prm EXTRACT EDDEC1 --SOURECATALOG PDBNAME <<< Only for Multitenant database LOGALLSUPCOLS UPDATERECORDFORMAT FULL USERIDALIAS GG_USER --userid GG_USER, password GG_USER EXTTRAIL ./dirdat/et TRANLOGOPTIONS EXCLUDEUSER gg_user TRANLOGOPTIONS DBLOGREADER TRANLOGOPTIONS BUFSIZE 4096000 TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000 TABLE STAGING2.*; ggsci>DBLOGIN USERID GG_USER PASSWORD GG_USER ggsci>ADD EXTRACT EDDEC1, TRANLOG , begin now --ADD EXTRACT EXTDDEC1, TRANLOG , begin now ggsci>ADD EXTTRAIL ./dirdat/et, EXTRACT EDDEC1, megabytes 20 ---ADD EXTTRAIL ./dirdat/ex, EXTRACT EXTDDEC1, megabytes 10 ggsci >add trandata schmea.tablename ALLCOLS -------------------------------------NEED TO CHECK Configure Integrated extract: ============================ vi edtru1.prm EXTRACT EDTRU1 ExtTrail ./dirdat/dtru1/inc_load/ic SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1") SETENV (ORACLE_SID="C1DTRU1") SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8) USERIDALIAS GG_USER SOURCECATALOG dtru1 REPORTCOUNT EVERY 10 MINUTES, RATE BR, BRDIR ./dirdat/BR TABLE PT_DATA_SIT.*; TABLE PT_METADATA_SIT.*; --GETTRUNCATES --STATOPTIONS REPORTFETCH --FETCHOPTIONS USESNAPSHOT, USELATESTVERSION --FETCHOPTIONS MISSINGROW REPORT --DDL INCLUDE MAPPED --DDLOPTIONS REPORT --Table EDAA_RPT.*; --TABLE EDAA_CACHE.*; --TABLEEXCLUDE ODS.ODS_CLM_PPO_MED_KO_NEW; ggsci > DBLOGIN USERID GG_USER PASSWORD ggddec1 <<<< FOR Non userid setup ggsci > DBLOGIN USERIDALIAS GG_USER << Login to root container ggsci > ADD EXTRACT EDTRU1, INTEGRATED TRANLOG, BEGIN NOW ggsci > ADD EXTTRAIL ./dirdat/dtru1/inc_load/ic EXTRACT EDTRU1, megabytes 2000 ggsci > register extract edtru1 database <<<<< For NONCDB database ggsci > dblogin userid GG_USER password oracle << login to root container to execute the below command. ----------------ggsci > REGISTER EXTRACT EDTRU1 DATABASE CONTAINER (dtru1) <<< for CDB ggsci > start extract EDTRU1 Step 2 : create pump process: Configure Pump: ================= vi PDTRU1.prm EXTRACT PDTRU1 RMTHOST 192.30.111.177, MGRPORT 7801, TIMEOUT 30 RMTTRAIL /opt/ogg/dirdat/legacy/tp/inc_load/ic --CACHEMGR CACHEDIRECTORY ./dirdat/dirtmp PASSTHRU --GETTRUNCATES TABLE PT_DATA_SIT.*; TABLE PT_METADATA_SIT.*; ggsci > DBLOGIN USERID GG_USER PASSWORD GG_USER <<<< FOR Non userid setup ggsci > DBLOGIN USERIDALIAS GGUSER ggsci > add extract PDTRU2, EXTTRAILSOURCE ./dirdat/dtru1/inc_load/ic, begin NOW ggsci > ADD RMTTRAIL /opt/ogg/data/dirdat/legacy/tp/inc_load/ic extract PDTRU1 , seqno 1000 rba 0 <<< Check or assume number of trial files that inital load can produce and keep the seqno higher than that to avoid position col repeat" ggsci > start extract PDTRU1 Incase the pump is already running but you want to reposition it so the initial load and incremental load remote trail files does not overlap in number which will cause duplicate logposition values. ggsci > delete RMTTRAIL /opt/ogg/data/dirdat/facets/inc_load/ic , extract UT1_PMP ggsci > ADD RMTTRAIL /opt/ogg/data/dirdat/facets/inc_load/ic , SEQNO 200 RBA 0 , EXTRACT UT1_PMP ggsci > Alter extract UT1_PMP , extseqno 0 extrba 0 ggsci > info UT1_PMP detail Step 3 : Preparing and initiate Inital load : col current_scn format 9999999999999999 select current_scn from v$database; << Note the SCN CURRENT_SCN ----------- 2040227 GGSCI > EDIT PARAMS INLOAD1 Paste the following contents to INLOAD1.PRM: extract INLOAD1 USERIDALIAS GGUSER USERIDALIAS GG_USER << Multitentant db ENCRYPTTRAIL AES256 RMTHOST 192.30.111.173, MGRPORT 7801 RMTFILE /opt/ogg/data/dirdat/legacy/tp/ini_load/ii, MEGABYTES 500 TABLE dtru1.PT_DATA_SIT.* , SQLPREDICATE 'AS OF SCN 2040227'; TABLE dtru1.PT_METADATA_SIT.* , SQLPREDICATE 'AS OF SCN 2040227'; TABLE PDBNAME.STAGING2.TEST10 , SQLPREDICATE 'AS OF SCN 2040227'; <<<< for contaniner database only --TABLE STAGING3.QPNY_CLM_PRF_5010 , SQLPREDICATE "'WHERE BEG_SERV_DATE_YEAR>='20150101' and BEG_SERV_DATE_YEAR<'20170101'""; --TABLE STAGING3.QPNY_CLM_PRF_5010 , SQLPREDICATE "WHERE BEG_SERV_DATE_YEAR>='20170101'"; --TABLEEXCLUDE HR.EMP_DETAILS_VIEW; Initial load for big size partition tables -- TCPBUFSIZE 10000000 , tcpflushbytes 10000000 ( to set it 10 mb , How to calculate is in the commands_goldengate doc) EXTRACT INILOAD1 USERIDALIAS GGUSER ENCRYPTTRAIL AES256 RMTHOST 192.30.111.173, MGRPORT 7801, tcpbufsize 10000000, tcpflushbytes 10000000 RMTFILE /opt/ogg/data/dirdat/legacy/tp/ini_load/ii, MEGABYTES 2000 table east.milltblea, SQLPREDICATE “WHERE COLA < 499294558289218750”; add extract INLOAD1 SOURCEISTABLE SELECT 'TABLE DTRU1.'||OWNER||'.'||TABLE_NAME||", SQLPREDICATE 'AS OF SCN 9428942694650';"|| FROM DBA_TABLES WHERE OWNER LIKE 'TP_%_SIT%'; start ggsci >start extract inext1 << Either use this command or below script to start the extract. ggsci >nohup ./extract paramfile dirprm/inext1.prm reportfile dirrpt/inext1.rpt & Step 4 : starting replicate for the inital load files sent by iload. Create One time replicat Process: Replicat RLOAD1 USERIDALIAS GGUSER SPECIALRUN END RUNTIME EXTFILE /opt/ogg/data/dirdat/facets/ini_load/ii MAP FACT.*, TARGET FACT.*; start replicat RLOAD1 For Non co-ordinated replicat: ADD REPLICAT RLOAD1, EXTTRAIL /opt/GoldenGate/dirdat/fact/inc_load/tf ALTER REPLICAT EXTSEQNO 0 extrba 0 Oracle: dblogin userid GG_USER PASSWORD GG_USER ADD CHECKPOINTTABLE GG_USER.GGSCHKPT; add replicat rfac2, exttrail /ogg/oggdir/dirdat/ic, checkpointtable GG_USER.GGSCHKPT START REPLICAT RFAC2 , AFTERCSN 38270077089 While dropping and recreating we don't need to specify any checkpoint table, Oracle should be able to do it with below thread. Co-ordinated replicat : add replicat RLOAD1 exttrail /u01/app/ogg/product/12.3.0.1.2/dirdat/QSAW8/rf coordinated maxthreads 10 ALTER REPLICAT EXTSEQNO 0 extrba 0 Big data : Replicat file: Replicat icae1 targetdb libfile libggjava.so set property=dirprm/caehdfs_rba.props reportcount every 1 minutes , rate grouptransops 5000 MAP dbo.*, TARGET landing_cae.*, THREADRANGE (1-5); Step 5 : Starting Incremental using aftercsn so there is no data loss or collision of data. After completion of inital load , Start incremental load : start replicat replcdd, aftercsn 2040227 Reference: https://blog.dbi-services.com/performing-an-initial-load-with-goldengate-1-file-to-replicat/ http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_basics/pdb_basics.html http://www.vitalsofttech.com/oracle-12c-managing-common-and-local-users-roles-and-privileges-in-cdb-and-pdb/ https://dbasolved.com/2016/08/12/to-trandata-or-to-schematrandata-that-is-the-goldengate-questions-of-the-day/