GOLDENGATE installation and Data Replication flow setup configuration _ excluding DDL extraction version and goldengateddl

GOLDENGATE installation and Data Replication flow setup configuration _ excluding DDL extraction version and goldengateddl

GOLDENGATE installation and Data Replication flow configuration

(The source database is an instance and supports ASM, excluding the DDL extraction Version)

1. Configuration scenarios

2. Install OGG Software

2.1 create OGG installation directory and authorization on the source and target Terminals

# Mkdir/u01/ogg

# Chown-R oracle: oinstall/u01/ogg

# Chmod-R 777/u01/ogg

 

2.2 configure environment variables on the source and target ends

# Su-oracle

[Oracle @ server1 ~] $ Vi. bash_profile

Add the following content:

Export OGG =/u01/ogg Export LD_LIBRARY_PATH = $ ORACLE_HOME/lib:/u01/ogg

[Oracle @ server1 ~] $ Source. bash_profile

2.3 install OGG software on the source and target ends

(1) copy the ogg112101_fbo_ggs_linux_x64_ora11g_64bit.zip software to the/u01/ogg directory.

(2) decompression software

[Oracle @ server1 ~] $ Unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[Oracle @ server1 ~] $ Tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

2.4 create test tables and insert test data in the source database

(Skip this step if it is a production environment)

(1) create a Test table goldengate. OGG_UPG

Create table GOLDENGATE. OGG_UPG ( Pr_id NUMBER (10) notnull, T_name VARCHAR2 (20 ), Sal NUMBER (10 ), Insert_time DATE primary key ) Tablespace GOLDENGATE

(2) create sequence for testing

Create sequence GOLDENGATE. SEQ_OGG_T01 Minvalue1 Maxvalue999999999999999999999999999999 Startwith1 Incrementby1 Cache 20;

(3) Insert data

Declare I number: = 1; Begin Loop Inser tinto goldengate. OGG_UPGvalues (goldengate. seq_ogg_t01.nextval, 'ogg _ test1', I, sysdate ); Commit; I: = I + 1; Exit when I = 10001; End loop; End; /

3. modify source database and table configuration 3.1 enable minimum-level database append logs

SQL> alter database add supplemental log data;

SQL> altersystem switch logfile;

SQL> exit

3.2 enable table-level append logs for tables to be synchronized

[Oracle @ server1 ~] $./Ggsci

GGSCI> dbloginuserid goldengate, password goldengate

GGSCI> addtrandata goldengate.ogg _ upg

4. Configure OGG source client 4.1 to create subdirs

GGSCI> create subdirs

4.2 create MGR

GGSCI> editparams mgr

Port 7809 Dynamicportlist 7800-8000 -- Autorestart extract *, waitminutes 30, resetminutes 5 Lagreporthours 1 Laginfominutes 20 Lagcriticalminutes 60 Purgeoldextracts./dirdat/tr *, usecheckpoints, minkeepdays 10

GGSCI> start mgr

4.3 create an extract process

GGSCI> add ext exttr, tranlog, begin now

GGSCI> addexttrail./dirdat/tr, ext exttr, megabytes 200

GGSCI> editparams exttr

Extract exttr Setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) Userid goldengate, password goldengate Report at 0:59 Reportrolover TRANLOGOPTIONS CONVERTUCS2CLOBS Threadoptions maxcommitpropagationdelay 90000 IOLATENCY 100000 Tranlogoptions dblogreader Tranlogoptions altarchivelogdest primary instance orcl/u01/archive Discardfile./dirrpt/exttr. dsc, append, megabytes 1000 Gettruncates -- Warnlongtrans 2 h, checkintervals 3 m Exttrail./dirdat/tr Numfiles 2000 Dynamicresolution TABLEGOLDENGATE. OGG_UPG;

4.4 create a dataPump Process

GGSCI> add extdpetr, exttrailsource./dirdat/tr

GGSCI> add rmttrail./dirdat/tr, extdpetr, megabytes 200

GGSCI> edit param dpetr

Extract dpetr Setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) Userid goldengate, password goldengate Report at 0:59 Reportrolover Rmthost 192.168.1.220, mgrport 7809, compress Rmttrail./dirdat/tr Dynamicresolution Numfiles 2000 Gettruncates Table goldengate. OGG_UPG;

GGSCI> start param dpetr

5. Configure OGG on the target end. Configure 5.1 to create subdirs.

GGSCI> create subdirs

5.2 create MGR

GGSCI> edit params mgr

Port 7809 Dynamicportlist 7800-8000 Autorestart replicat *, waitminutes 5, resetminutes 5 Lagreporthours 1 Laginfominutes 20 Lagcriticalminutes 60 Purgeoldextracts./dirdat/tr *, usecheckpoints, minkeepdays 10

GGSCI> start mgr

5.3 create a replicat Process

GGSCI> dblogin userid goldengate, password goldengate

GGSCI> add checkpointtable goldengate. checkpoint_reptr_01

GGSCI> add replicat reptr, exttrail./dirdat/tr, checkpointtablegoldengate. checkpoint_reptr_01

GGSCI> edit params reptr

Replicat reptr Setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) Userid goldengate, password goldengate Sqlexec "Alter session set constraints = deferred" Report at 0:59 Reportrolover Discardrolover on Friday -- Handlecollisions Reperror default, abend Discardfile./dirrpt/reptr. dsc, append, megabytes 1000 Assumetargetdefs Checksequencevalue Allownoopupdates Dynamicresolution Numfiles 2000 GETTRUNCATES Batchsql BATCHESPERQUEUE 100, OPSPERBATCH 8000 Grouptransops 10000 Maxtransops 1, 10000 Map goldengate. OGG_UPG, target goldengate. OGG_UPG;

Do not start the replicat process after it is created.

6. Data initialization 6.1 Stop the JOB of the source database

SQL> alter system set job_queue_processes = 0 scope = both; -- Disable the job Queue to prevent subsequent jobs from being started. SQL> alter system set aq_tm_processes = 0 scope = both; -- Disable advanced queue SQL> select count (*) from dba_jobs_running; -- Check whether a job is being executed in the system. If a JOB is running, Kill the job.

 

6.2 stop front-end system applications

(After a transaction is started, it is not finished when the SCN is retrieved,Since the GOLDENGATE extraction process only extracts the transactions started after the extract process starts, this is very important..)

6.3 kill all transactions

(1) query ongoing transactions

Select * from gv $ transaction;

(2) kill the session of the transaction being executed

6.4 stop listener

$ Lsnrctl stop

(Prevent new connections from creating new transaction operation table data)

6.5 query the current SCN Number of the source database.

SQL> selectdbms_flashback.get_system_change_number from dual;

Record SCN No.: 2553440

6.6 export data

$ Expdpsystem/XXX directory = expdp_dir dumpfile = tableXXX. dmp filesize = 30G logfile = expdp_XXXX.log flashback_scn =2553440Tables = GOLDENGATE. OGG_UPG

6.7 restore the JOB parameters of the source database

SQL> alter system set job_queue_processes = 20 scope = both; -- Restore based on the actual value. SQL> alter system set aq_tm_processes = 1 scope = both;

6.8 import data to the target database

$ Imppdp system/XXX directory = expdp_dir dumpfile = tableXXX. dmp logfile = impdp_XXXX.log

7. Start the target replicat Process

GGSCI> startreptr,Aftercsn 2553440

8. Verify that OGG can synchronize data normally

8.1 query the data volume of the GOLDENGATE. OGG_UPG table in the target database

SQL> select count (*) from goldengate.ogg _ upg;   COUNT (*) ---------- 10000

8.2 Insert 10 thousand rows of data in the GOLDENGATE. OGG_UPG table of the source database

Declare I number: = 1; Begin Loop Insertinto goldengate. OGG_UPGvalues (goldengate. seq_ogg_t01.nextval, 'ogg _ test1', I, sysdate ); Commit; I: = I + 1; Exitwhen I = 10001; End loop; End; /

8.2 query the target database to check whether the data in the GOLDENGATE. OGG_UPG table is increasing.

SQL> select count (*) from goldengate.ogg _ upg;   COUNT (*) ---------- 20000

The configuration is successful and can be synchronized normally.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244