Shulou(Shulou.com)06/01 Report--

Experimental environment

Data source side: host1 ip 192.168.199.163

Data destination: host2 ip 192.168.199.104

Both machines are installed with http://lqding.blog.51cto.com/9123978/1694971. This article describes that Goldengate is installed and configured.

To synchronize data, the Oracle source must meet the following settings

Oracle needs to run in archive mode

SQL > startup mount ORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 574621728 bytesDatabase Buffers 255852544 bytesRedo Buffers 2412544 bytesDatabase mounted.SQL > alter database archivelog;Database altered.SQL > alter database open;Database altered.

two。 Enable log additional properties

SQL > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered.SQL > ALTER SYSTEM SWITCH LOGFILE;System altered.

Some sql for demo is provided in the ogg installation directory

[oracle@localhost ogg] $ls demo_ora_*demo_ora_create.sql demo_ora_insert.sql demo_ora_lob_create.sql demo_ora_misc.sql demo_ora_pk_befores_create.sql demo_ora_pk_befores_insert.sql demo_ora_pk_befores_updates.sql

We use demo_ora_create.sql to create two tables on both the source side and the destination side, and use demo_ora_insert.sql to insert data on the source side.

Host1

SQL > alter user scott identified by tiger account unlock;User altered.SQL > grant resource to scott; # ggsci this permission is required when logging in to the database Grant succeeded.SQL > grant select any dictionary to scott; # add trandata Grant succeeded.SQL > conn scott/tigerConnected.SQL > @ demo_ora_create.sqlSQL > @ demo_ora_insert.sql

Host2

This permission is required for SQL > alter user scott identified by tiger account unlock;User altered.SQL > grant resource to scott; # ggsci to log in to the database Grant succeeded.SQL > grant select any dictionary to scott; # add trandata for SQL > conn scott/tigerConnected.SQL > @ demo_ora_create.sql

Add additional logs for tables that need to be synchronized

GGSCI (localhost.localdomain) 1 > dblogin userid scott, password tigerSuccessfully logged into database.GGSCI (localhost.localdomain) 2 > add trandata scott.tcustmerLogging of supplemental redo data enabled for table SCOTT.TCUSTMER.GGSCI (localhost.localdomain) 3 > add trandata scott.tcustordLogging of supplemental redo data enabled for table SCOTT.TCUSTORD.

Initialize the loading data

Configure an initialization Extract on the source side to synchronize existing data in the table

GGSCI (localhost.localdomain) 7 > ADD EXTRACT EINILOAD, SOURCEISTABLEEXTRACT added.

The ADD EXTRACT command is used to add an EXTRACT, and the EINILOAD is the group name of Extract. SOURCEISTABLE indicates that the data source is a table.

View the information of Extract

GGSCI (localhost.localdomain) 9 > INFO EXTRACT *, TASKSEXTRACT EINILOAD Initialized 2015-09-11 15:25 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLE

Configure capture parameters for initialization loading

GGSCI (localhost.localdomain) 10 > edit params EINILOAD---- GoldenGate Initial Data Capture-- for TCUSTMER and TCUSTORD--EXTRACT EINILOADUSERID system, PASSWORD "oracle" RMTHOST 192.168.199.104, MGRPORT 7809RMTTASK REPLICAT, GROUP RINILOADTABLE SCOTT.TCUSTMER;TABLE SCOTT.TCUSTORD

On the destination side, configure a REPLICAT

GGSCI (localhost.localdomain) 2 > ADD REPLICAT RINILOAD, SPECIALRUNREPLICAT added.

View REPLICAT information

GGSCI (localhost.localdomain) 4 > info replicat *, tasksREPLICAT RINILOAD Initialized 2015-08-22 14:18 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:02:50 ago) Log Read Checkpoint Not AvailableTask SPECIALRUN

Configure Replicat parameters

GGSCI (localhost.localdomain) 5 > edit params riniload---- GoldenGate Initial Load Delivery--REPLICAT RINILOADASSUMETARGETDEFSUSERID system, PASSWORD "oracle" DISCARDFILE. / dirrpt/RINILOAD.dsc, PURGEMAP scott.*, TARGET scott.*

Start Extract

GGSCI (localhost.localdomain) 11 > start extract einiloadSending START request to MANAGER... EXTRACT EINILOAD starting

View the log

GGSCI (localhost.localdomain) 21 > view report einiload

If there is an error, find out the cause and solve it.

Processing table SCOTT.TCUSTMERProcessing table SCOTT.TCUSTORD**** Run Time Statistics * * Report at 2015-09-11 16:23:40 (activity since 2015-09-11 16:23:33) Output to RINILOAD:From Table SCOTT.TCUSTMER: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0From Table SCOTT.TCUSTORD: # inserts: 2 # updates: 0 # deletes : 0 # discards: 0REDO Log Statistics Bytes parsed 0 Bytes output 574

The log shows that the data has been synchronized successfully.

Verify on the destination database

SQL > select count (*) from tcustmer; COUNT (*)-2SQL > select count (*) from tcustord; COUNT (*)-2

2. Real-time synchronization of configuration data

Source side, configure a real-time Extract

GGSCI (localhost.localdomain) 22 > ADD EXTRACT EORAKK, TRANLOG, BEGIN NOW, THREADS 1EXTRACT added.

Edit the parameter file for Extract

GGSCI (localhost.localdomain) 23 > EDIT PARAMS EORAKK---- Change Capture parameter file to capture-- TCUSTMER and TCUSTORD Changes--EXTRACT EORAKKUSERID system, PASSWORD oracleRMTHOST 192.168.199.104, MGRPORT 7809RMTTRAIL. / dirdat/KKTABLE SCOTT.TCUSTMER;TABLE SCOTT.TCUSTORD

Add a remote tail file to the Extract, which means that the tail file is generated on the destination side.

GGSCI (localhost.localdomain) 24 > ADD RMTTRAIL. / dirdat/KK, EXTRACT EORAKK, MEGABYTES 5RMTTRAIL added.

Verification result

GGSCI (localhost.localdomain) 28 > INFO RMTTRAIL * Extract Trail:. / dirdat/KK Extract: EORAKK Seqno: 0 RBA: 0 File Size: 5m

Start the Extract process

GGSCI (localhost.localdomain) 29 > start extract eorakkSending START request to MANAGER... EXTRACT EORAKK starting

Verification result

GGSCI (localhost.localdomain) 30 > INFO EXTRACT EORAKK, DETAILEXTRACT EORAKK Last Started 2015-09-11 17:07 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:01 ago) Log Read Checkpoint Oracle Redo Logs 2015-09-11 17:07:47 Thread 1, Seqno 25 RBA 36139008 SCN 0.1174781 (1174781) Target Extract Trails: Remote Trail Name Seqno RBA Max MB. / dirdat/KK 0 1050 5 Extract Source Begin End / u01 / App/oracle/oradata/orcl/redo01.log 2015-09-11 16:58 2015-09-11 17:07 Not Available * Initialized * 2015-09-11 16:58Current directory / u01/app/oggReport file / u01/app/ogg/dirrpt/EORAKK.rptParameter file / u01/app/ogg/dirprm/eorakk.prmCheckpoint file / u01/app/ogg/dirchk/EORAKK.cpeProcess file / U01 / app/ogg/dirpcs/EORAKK.pceStdout file / u01/app/ogg/dirout/EORAKK.outError log / u01/app/ogg/ggserr.logGGSCI (localhost.localdomain) 31 > VIEW REPORT EORAKKGGSCI (localhost.localdomain) 32 > INFO ALLProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EORAKK 00:00:00 00:00:06

Target side, configure REPLICAT

Install the checkpoint table

Configure the name of the checkpoint table

GGSCI (localhost.localdomain) 33 > EDIT PARAMS. / GLOBALSHECKPOINTTABLE system.ggschkpt

Generate checkpoint table

GGSCI (localhost.localdomain) 1 > DBLOGIN USERID system, PASSWORD oracleSuccessfully logged into database.GGSCI (localhost.localdomain) 2 > ADD CHECKPOINTTABLENo checkpoint table specified, using GLOBALS specification (system.ggschkpt)... Successfully created checkpoint table system.ggschkpt.GGSCI (localhost.localdomain) 3 >

Add Replicat

GGSCI (localhost.localdomain) 3 > ADD REPLICAT RORAKK, EXTTRAIL. / dirdat/KKREPLICAT added.

Create a parameter file for Replicat

GGSCI (localhost.localdomain) 4 > EDIT PARAMS RORAKK---- Change Delivery parameter file to apply-- TCUSTMER and TCUSTORD Changes--REPLICAT RORAKKUSERID system, PASSWORD oracleHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE. / dirrpt/RORAKK.DSC, PURGEMAP scott.tcustmer, TARGET scott.tcustmer;MAP scott.tcustord, TARGET scott.tcustord

Start Replicat

GGSCI (localhost.localdomain) 5 > start replicat rorakkSending START request to MANAGER... REPLICAT RORAKK starting

Verification result

GGSCI (localhost.localdomain) 6 > info replicat rorakkREPLICAT RORAKK Last Started 2015-08-22 15:49 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:08 ago) Log Read Checkpoint File. / dirdat/KK000000 First Record RBA 1050

Verify data synchronization

In the source database, execute the following script to perform insert, update, delete operations on the two tables

[oracle@localhost ogg] $sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 11 17:58:17 2015Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > @ / u01/app/ogg/demo_ora_misc.sql

View the data in the source table

SQL > select count (*) from tcustmer; COUNT (*)-5SQL > select count (*) from tcustord; COUNT (*)-3

View the data of the destination table

SQL > select count (*) from tcustmer; COUNT (*)-5SQL > select count (*) from tcustord; COUNT (*)-3

Data has been synchronized

Third, add data pump for synchronization

If there is no data pump,Extract, write the Trail file to the remote machine. If Data Pump is configured for Extract. So Extract writes the Trail file locally. The Trail file is then transferred to the remote end by Data Pump.

Modify the configuration of Extract first

GGSCI (localhost.localdomain) 1 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EORAKK 00:00:00 00:00:09 GGSCI (localhost.localdomain) 2 > stop extract eorakkSending STOP request to EXTRACT EORAKK... Request processed.GGSCI (localhost.localdomain) 3 > edit params eorakk---- Change Capture parameter file to capture-- TCUSTMER and TCUSTORD Changes--EXTRACT EORAKKUSERID system PASSWORD oracleRMTHOST 192.168.199.104, MGRPORT 7809EXTTRAIL. / dirdat/KKTABLE SCOTT.TCUSTMER TABLE SCOTT.TCUSTORD

Change the RMTTRAIL. / dirdat/kk in the parameter file to EXTTRAIL. / dirdat/KK

two。 Delete the remote Trail of Extract

GGSCI (localhost.localdomain) 7 > DELETE RMTTRAIL. / dirdat/KKDeleting extract trail. / dirdat/KK for extract EORAKK

3. Add a local Trail

GGSCI (localhost.localdomain) 10 > ADD EXTTRAIL. / dirdat/KK, EXTRACT eorakkEXTTRAIL added.

4. New Data pump

GGSCI (localhost.localdomain) 12 > ADD EXTRACT EPMPKK, EXTTRAILSOURCE. / dirdat/KKEXTRACT added.

Data pump is essentially an Extract, but the type is different.

Configure the parameter file for data pump

GGSCI (localhost.localdomain) 14 > edit params epmpkk---- Data Pump parameter file--EXTRACT EPMPKKPASSTHRURMTHOST 192.168.199.104, MGRPORT 7809RMTTRAIL. / dirdat/KKTABLE SCOTT.TCUSTMER;TABLE SCOTT.TCUSTORD

Add a remote Trail to the Data Pump

GGSCI (localhost.localdomain) 15 > add rmttrail. / dirdat/KK, EXTRACT EPMPKKRMTTRAIL added.

Start Extract, Data Pump

GGSCI (localhost.localdomain) 16 > start extract eorakkSending START request to MANAGER... EXTRACT EORAKK startingGGSCI (localhost.localdomain) 17 > start extract epmpkkSending START request to MANAGER... EXTRACT EPMPKK starting

Verify data synchronization

On the source side, delete the contents of the two tables

SQL > delete from tcustmer;5 rows deleted.SQL > delete from tcustord;3 rows deleted.SQL > commit;Commit complete.

View the contents of the destination table

SQL > select * from tcustmer;no rows selectedSQL > select * from tcustord;no rows selected

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.