Ogg configuration review (dml & gt; ddl & gt; sequnce), dmlsequnce

Ogg configuration review (dml> ddl> sequnce), dmlsequnce

<Strong> <span style = "color: # ff6666; "> dml configuration process and test overview </span> </strong> Create a dedicated tablespace, schema, and authorization on the source. Create tablespace goldengate datafile 'golden000001. dbf 'size 100 m autoextend on; create user goldengate identified by goldengate default tablespace goldengate 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; g Rant select any table to goldengate; grant resource to goldengate; grant select any transaction to goldengate; check whether the source database is in archive mode. It is strongly recommended that the archive mode be used. SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination/archOldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3: add additional logs to uniquely identify a row of records, you need to turn on the minimum switch at the database level. To reduce the number of additional logs added to the entire database and reduce the number of archives, goldengate recommends copying the objects and adding additional logs for the tables (we will then experiment with the tables under the hr user ). Check: SQL> select supplemental_log_data_min from v $ database; SUPPLEME--------NO opens: SQL> alter database add supplemental log data; Database altered. check again: SQL> <span style = "color: # ff6666;"> select supplemental_log_data_min from v $ database; </span> the SUPPLEME--------YES switches the log group for the additional log switch to take effect. SQL> alter system switch logfile; System altered. Explanation: under normal circumstances, oracle uses rowid to uniquely identify a row of records, but goldengate is not enough. Additional logs must be enabled. Install and configure GGSCI (gg2) 2> <span style = "color: # ff6666;"> create subdirs </span> GGSCI (oratest) 80> view params mgrport 7839 DYNAMICPORTLIST 7840-7850AUTOSTART EXTRACT * autorestart extract * PURGEOLDEXTRACTS. /dirdat/*, usecheckpoints, minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 start the configuration management process GGSCI (gg2) 4> start mgrManager started. select owner | '. '| table_name, logging from dba_tab Leswhere owner = 'sender'; SQL> alter table SENDER. A1 logging; source end: configure the table-level additional logs in the hr table. Configure the login user. Check whether logs are successfully added. Log on to the database using the schema goldengate. GGSCI (gg1) 10> <span style = "color: # ff6666;"> dblogin userid goldengate, password goldengate </span> Successfully logged into database. view: GGSCI (gg1) 11> info trandata hr. * add extract ext_demo, tranlog, begin now, threads 1 parameter analysis: tranlog: indicates that the source of data capture is the redo data of the database. Begin now: indicates that data is captured when the capture process is started. Threads 1: indicates the number of redo threads in our database. A single instance is basically 1 or not. rac is set on its own. After configuring RAC, you can see the queue Files Added to the source end. GGSCI (gg1) 15> <span style = "color: # 3333ff;"> add extract ext_demo, <span style = "background-color: rgb (102,102,204 ); "> tranlog </span>, begin now, threads 1 </span> GGSCI (gg1) 15> <span style =" color: # ff0000; "> add EXTTRAIL. /dirdat/r1, extract ext_demo, megabytes 100 </span> EXTTRAIL added. /dirdat: indicates the directory r1 of the trail file: prefix extract ext_demo of the trail file: The value is specified for the process (ext_demo ). Megabytes 100: The file size is MB. Edit the parameters of the capture process We Just configured on the source end: GGSCI (gg1) 16> <span style = "color: # ff6666; "> edit param ext_demo </span> EXTRACT EXT_DEMOuserid goldengate, password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000 DISCARDFILE. /dirrpt/ext_demo.dsc, APPEND, MEGABYTES 1000 discardrolover AT 3: 00 exttrail. /dirdat/r1, megabytes 100 dynamicresolutionTRANLOGOPTIONS EXCLUDEUSER goldengateTRANLOGOPTIONS convert Ucs2clobs tranlogoptions dblogreadertable sender. *; setenv (ORACLE_SID = ogg): Specifies the sid of oracle. You can set this parameter in a single-host environment or set the default value in the bashprofile file. You can set this parameter in the cluster environment, we strongly recommend that you specify. Setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK): Character Set of the source database, which must match with the database. Userid goldengate, password goldengate: tells the crawling process to log on to the database through this user. Exttrail./dirdat/r1, megabytes 100: The Path and size must be consistent with the settings of the above configuration Grabbing Process. Table hr. *: the most important thing is to tell the capturing process which users are capturing and which tables are configuring the datapump process to upload the captured data to the target host. Responsible for TCPIP communication. GGSCI (gg1) 31> <span style = "color: # ff0000;"> add extract dpe_demo, exttrailsource. /dirdat/r1 </span> EXTRACT added. output: how to write data to the target host is also the output that defines the datapumo process. GGSCI (gg1) 33> <span style = "color: # ff6666;"> add rmttrail. /dirdat/t1, EXTRACT dpe_demo, MEGABYTES 100 </span> RMTTRAIL added. analysis: exttrailsource: capture the source of the process. Because the process is not crawled in the database, the source of the capture is the content of the trail file generated by the process. 17. Configure the datapump process parameters: GGSCI (oratest) 83> <span style = "color: # ff0000;"> view params dpe_demo </span> extract 192.168.175.139, mgrport 7840, compressrmttrail. /dirdat/t1numfiles 5000 table sender. *; GGSCI (oratest) 84> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER running extract running DPE_DEMO 00:00:00 extract running EXT_DEMO 00:00:00 00:00:06 GGSCI (oratest) 85> passthru: As mentioned above, you do not need to connect to the database, so use this command. Rmthost: the IP address of the target host, that is, the host sent by the rmttrail process. Mgrport: the port number of the target host management process, which has been configured above. Compress: Enable compression during transmission. Rmttrail: the path and file are consistent with those configured above. Table sender. *: As mentioned above, which tables transmit redo information. Target end: Create the schema used by golengate and grant some necessary special permissions. Note that the schema is different from the source end and DML can be ensured. Create tablespace goldengate datafile 'golden000001. dbf 'size 100 m autoextend on; create user goldengate identified by goldengate default tablespace goldengate 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; g Rant select any table to goldengate; grant resource 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 select any transaction to goldengate; target end: Create a checkpoint table for the replicat process: GGSCI (gg2) 1> <span style = "color: # ff0000;"> dblogin userid goldengate, password goldengate </span> Successfully logged Into database. GGSCI (gg2) 2> <span style = "color: # ff0000;"> add checkpointtable goldengate. rep_demo_ckpt </span> Successfully created checkpoint table goldengate. rep_demo_ckpt.21. Configure the target replicate process: GGSCI (gg2) 3> <span style = "color: # ff6666;"> add replicat rep_demo, exttrail. /dirdat/t1, checkpointtable goldengate. rep_demo_ckpt </span> REPLICAT added. analysis: exttrail: indicates the source of the data obtained by this process. It is set by the source rmttrail. 22. Configure the target replicate parameter: GGSCI (oratest) 13> <span style = "color: # ff6666;"> view params rep_demo </span> REPLICAT rep_demoUSERID goldengate, PASSWORD goldengateREPORTCOUNT EVERY 30 MINUTES, ratereperror default, ABENDnumfiles 5000 -- HANDLECOLLISIONSassumetargetdefsDISCARDFILE. /dirrpt/rep_demo.dsc, APPEND, MEGABYTES 1000 allownoopupdatesmap sender. *, target sender. *; analysis: the important points are basically the same as above. If you are interested, you can check the information of other parameters. MAP: corresponds to the source and target. When adding a table, add the table to the target first, and then the metadata end. The database can be automatically synchronized! <Strong> <span style = "color: # ff6666; "> 1.4 enable DDL </span> </strong> SQL> @ marker_setup. SQL -- prompt to enter the target schemaSQL> @ ddl_setup. SQL -- prompt to enter the target schema, enter initialsetup and input yesSQL> @ role_setup.sqlSQL> grant GGS_GGSUSER_ROLE to ddw; -- if you do not perform this step, the following process will report an error: SQL> @ ddl_enable. SQL -- make the trigger take effect. 1.5 clear the DDL synchronization settings. If many problems occur due to DDL synchronization, the simplest method is to Unmount and recreate it. Only scripts for clearing objects are provided in the installation directory. You can perform the following operations: First, stop all GG processes, including mgr process SQL> @ ddl_disable. SQL -- first invalidate DDL trigger SQL> @ ddl_remove.sqlSQL> @ marker_remove.sqlalter table OGG1.A1 logging; info trandata OGG1. * add extract ext2, tranlog, begin now, threads 1add EXTTRAIL. /dirdat/r3, extract ext2, megabytes 100 EXTRACT ext2userid goldengate, password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000 DISCARDFILE. /dirrpt/ext_demo1.dsc, APPEND, MEGABYTES 1000 discardrolover AT 3: 00 exttrail. /dirdat/r2, megabytes 100ddl include allTABLE OGG1. *; add extract dpe_ext2, exttrailsource. /dirdat/r3add rmttrail. /dirdat/t3, EXTRACT dpe_ext2, MEGABYTES 100 extract dpe_ext2dynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail. /dirdat/t3numfiles 5000 TABLE OGG1. *; add replicat rep_ext2, exttrail. /dirdat/t3, checkpointtable goldengate. rep_demo_ckptREPLICAT rep_ext2USERID goldengate, PASSWORD goldengateREPORTCOUNT EVERY 30 MINUTES, ratereperror default, ABENDnumfiles 5000 -- HANDLECOLLISIONSassumetargetdefsDISCARDFILE. /dirrpt/rep_rep2.dsc, APPEND, MEGABYTES 1000 ALLOWNOOPUPDATESMAP OGG1. *, TARGET OGG1 .*;

<Strong> <span style = "color: # ff6666;"> enable sequence: </span> <span style = "color: # ff6666;"> target end: </span> </strong>

SQL> @ sequence. sqlPlease enter the name of a schema for the GoldenGate database objects: goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS: line/pos Error ---------- warn No errors No errorsGETSEQFLUSHLine/pos Error ---------- warn No errors No errorsSEQTRACELine/pos E Rror ---------- ------------------------------------------------------------------- No errors No errorsREPLICATE_SEQUENCE STATUS: line/pos Error ---------- resume No errors No errorsSTATUS of sequence support --------------------------- ---SUCCESSFUL installation of Oracle Sequence replication supportSQL> GRANT EXECU TE on goldengate. replicateSequence to ogg1; Grant succeeded. SQL> target end: SQL> @ sequence. sqlPlease enter the name of a schema for the GoldenGate database objects: goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS: Line/pos Error ---------- No errors No errorsGETSEQFLUSHLine/pos Error -------- failed ------------------------------------------- ------------------------------ No errors No errorsSEQTRACELine/pos Error ---------- No errors No errorsREPLICATE_SEQUENCE STATUS: Line/pos Error ---------- SEQUENCE No errors No errorsSTATUS of sequence SUPPORT--------------------------------------------------------------SUCCESSFU L installation of Oracle Sequence Replication supportSQL> grant execute on goldengate. replicateSequence to ogg1; Grant succeeded. SQL> replication process: transmission process: GGSCI (oratest) 41> edit params DPE_EXT2extract dpe_ext2dynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail. /dirdat/t3numfiles 5000 TABLE OGG1. *; SEQUENCE OGG1 .*;~ "Dirprm/dpe_ext2.prm" 8L, 158C writtenGGSCI (oratest) 42> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER running extract running seconds 00:00:00 00:00:00 extract running seconds 00:00:00 00:00:04 extract running 00:00:00 extract running 00:00:09 EXT2 00:00:00 extract running EXT_DEMO 00:00:06 GGSCI (oratest) 43> stop DPE_EXT2Sending STOP request to EXTRA CT DPE_EXT2... request processed. GGSCI (oratest) 44> start DPE_EXT2Sending START request to MANAGER... EXTRACT DPE_EXT2 startingGGSCI (oratest) 45> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER running extract running DPE_DEMO 00:00:00 00:00:00 extract running DPE_EXT2 00:00:00 extract running EXT1 00:00:05 extract running EXT2 00:00:00 EXTRACT RUNNIN G EXT_DEMO 00:00:00 GGSCI (oratest) 46> extraction process: GGSCI (oratest) 48> edit params EXT2EXTRACT ext2userid goldengate, password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000 DISCARDFILE. /dirrpt/ext2.dsc, APPEND, MEGABYTES 1000 discardrolover AT 3: 00 exttrail. /dirdat/r3, megabytes 100ddl include allTRANLOGOPTIONS DBLOGREADERTABLE OGG1. *; SEQUENCE OGG1 .*;~ "Dirprm/ext2.prm" 11L, 284C writtenGGSCI (oratest) 49> stop ext2Sending STOP request to EXTRACT EXT2... request processed. GGSCI (oratest) 50> start ext2Sending START request to MANAGER... EXTRACT EXT2 startingGGSCI (oratest) 51> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER running extract running DPE_DEMO 00:00:00 extract running DPE_EXT2 00:00:07 EXTRACT RUNN ING EXT1 00:00:00 extract running EXT2 00:00:06 extract running EXT_DEMO 00:00:03 GGSCI (oratest) 52> modify this file in advance: Modify the file: GGSCI (oratest) 79> edit params. /GLOBALSGGSCHEMA goldengate ~ Flush sequence ogg1. * <strong> <span style = "color: # ff0000;"> restarting is more effective! </Span> </strong>