OGG 11.2.1.0.3
Source:
IP: 172.17.0.2
Target:
IP: 172.17.0.3
Requirements:
DB size < 50GB, blob column
2.1 Creating the OGG software installation directory.
~]# mkdir /ggs ~]# chown -R oracle:dba /ggs
2.2 Unzip the OGG software.
~]$ unzip /install/OGG\ 11.2.1.0.3\ for \ Oracle\ 11g\ on\ linux\ x86_64.zip -d /ggs ~]$ tar -xf /ggs/fbo_ggs_Linux_x64_ora11g_64bit.tar -C /ggs
2.3 Creating OGG subdirs.
~]$ cd /ggs ~]$ ./ggsci GGSCI> create subdirs Creating subdirectories under current directory /ggs Parameter files /ggs/dirprm: already exists Report files /ggs/dirrpt: created Checkpoint files /ggs/dirchk: created Process status files /ggs/dirpcs: created SQL script files /ggs/dirsql: created Database definitions files /ggs/dirdef: created Extract data files /ggs/dirdat: created Temporary files /ggs/dirtmp: created Stdout files /ggs/dirout: created
2.4 (Option) Adding environment variables.
~]$ cat >> ~/.bash_profile <<'EOF' PATH=/ggs:$PATH LD_LIBRARY_PATH=/ggs:$LD_LIBRARY_PATH if command -v rlwrap >/dev/null 2>&1; then alias ggsci='rlwrap ggsci' fi EOF
2.5 Configuring TNS names.
~]$ cat >> $TNS_ADMIN /tnsnames.ora << EOF TO_SOURCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) TO_TARGET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) EOF
3.1 Enabling archive mode, supplemental log data min, force logging.
SQL > select log_mode,supplemental_log_data_min,force_logging from v$database;SQL > alter database add supplemental log data;SQL > alter database force logging;SQL > alter system archive log current ;
3.2 (Option, 11g no requires) Purge recycle.
SQL > show parameter recyclebinSQL > select * from dba_recyclebin;SQL > purge dba_recyclebin;
3.3 Creating the OGG tablespace and user.
create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/orcl/ogg_tbs.dbf' size 200 m autoextend on next 10 m maxsize 1024 m extent management local segment space management auto;create user goldengate identified by goldengate default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;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;grant select any table to goldengate;grant resource to goldengate;grant drop any table to goldengate;grant dba to goldengate;
3.4 Enabling goldengate replication parameter.
SQL > show parameter goldengateSQL > alter system set enable_goldengate_replication= true scope = both ;
3.5 Create the user jove for production simulator.
create tablespace jove_tbs datafile '/u01/app/oracle/oradata/orcl/jove_tbs.dbf' size 1 m autoextend on next 10 m maxsize unlimited;create user jove identified by jove default tablespace jove_tbs temporary tablespace temp;grant dba to jove;conn jove/ jove create t1(id int , name varchar2(10 ));
4.1 Configure the mgr process.
GGSCI> edit params mgr port 7809 autostart er * autorestart er *, waitminutes 3, retries 15 purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 7 -- userid goldengate@to_target, password goldengate GGSCI> start mgr GGSCI> info mgr
Login to the target database using the userid, and access the checkpoint table to clear the extract files.
4.2 Login to source database.
GGSCI> dblogin userid goldengate, password goldengate
4.3 Add schematrandata to enable supplemental logging.
GGSCI> add schematrandata jove GGSCI> add schematrandata user01 GGSCI> add schematrandata user02 GGSCI> add schematrandata user03 GGSCI> info schematrandata jove
4.4 Configure the classic capture process.
~]$ mkdir -p ./dirdat/capt00 ~]$ mkdir -p ./dirrpt/capt00 GGSCI> edit params capt00 extract capt00 userid goldengate,password goldengate exttrail ./dirdat/capt00/ct tranlogoptions excludeuser goldengate warnlongtrans 12h, checkinterval 30m discardfile ./dirrpt/capt00/capt00.dsc, append, megabytes 200 table jove.*; -- table user01.*; -- table user02.*; -- table user03.*; GGSCI> add extract capt00, tranlog, begin now GGSCI> add exttrail ./dirdat/capt00/ct, extract capt00, megabytes 200 GGSCI> info capt00
4.5 Configure the datapump process.
~]$ mkdir -p ./dirrpt/pump00 GGSCI> edit params pump00 extract pump00 passthru rmthost 172.17.0.3, mgrport 7809 rmttrail ./dirdat/rept00/rt discardfile ./dirrpt/pump00/pump00.dsc, append, megabytes 200 table jove.*; -- table user01.*; -- table user02.*; -- table user03.*; GGSCI> add extract pump00, exttrailsource ./dirdat/capt00/ct GGSCI> add rmttrail ./dirdat/rept00/rt, extract pump00, megabytes 200 GGSCI> info pump00
Do not start the capture and datapump processes here! Keep it stopped!
5.1 (Recommend) Disabling archive mode.
SQL > shutdown immediateSQL > alter database mount;SQL > alter database noarchivelog;SQL > alter database open ;
5.2 Enabling goldengate replication parameter.
SQL > show parameter goldengateSQL > alter system set enable_goldengate_replication= true scope = both ;
5.3 Creating the OGG tablespace and user.
create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/orcl/ogg_tbs.dbf' size 200 m autoextend on next 10 m maxsize 1024 m extent management local segment space management auto;create user goldengate identified by goldengate default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;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;grant select any table to goldengate;grant resource to goldengate;grant drop any table to goldengate;grant dba to goldengate;
5.3 Using datapump to expdp/import the table structure.
~]$ expdp system/oracle directory=data_pump_dir dumpfile=expdp_20260426.dmp logfile=expdp_20260426.log schemas=jove,user01,user02,user03 content=metadata_only ~]$ impdp system/oracle directory=data_pump_dir dumpfile=expdp_20260426.dmp logfile=impdp_20260426.log full=y
5.4 Disabling the constraints/triggers/jobs.
・ How to disable,enable the constraints,triggers,jobs for oracle database migration
6.1 Configure the mgr process.
GGSCI> edit params mgr port 7809 autostart er * autorestart er *, waitminutes 3, retries 15 purgeoldextracts ./dirdat, usecheckpoints, minkeepdays 7 GGSCI> start mgr GGSCI> info mgr
6.2 Login to target database.
GGSCI> dblogin userid goldengate, password goldengate
6.3 Add the checkpointtable .
GGSCI> edit params ./GLOBALS checkpointtable goldengate.checkpoint GGSCI> add checkpointtable goldengate.checkpoint
6.4 Configure the replicat process.
~]$ mkdir -p ./dirdat/rept00 ~]$ mkdir -p ./dirrpt/rept00 GGSCI> edit params rept00 replicat rept00 userid goldengate, password goldengate handlecollisions assumetargetdefs discardfile ./dirrpt/rept00/rept00.dsc, append, megabytes 200 map jove.*, target jove.*; -- map user01.*, target user01.*; -- map user02.*, target user02.*; -- map user03.*, target user03.*; GGSCI> add replicat rept00 exttrail ./dirdat/rept00/rt, checkpointtable goldengate.checkpoint GGSCI> info rept00
7.1 Start the replicate process on target.
GGSCI> start rept00 GGSCI> view report rept00 GGSCI> info all
7.2 Start the capture and datapump processes on source.
GGSCI> start capt00 GGSCI> view report capt00 GGSCI> start pump00 GGSCI> view report pump00 GGSCI> info all
7.3 Verify the test table DML operation is replicated.
SQL > insert into jove.t1(1 , 'a' );SQL > select * from jove.t1;
8.1 Stop all OGG processes.
GGSCI> stop capt00 GGSCI> stop pump00 GGSCI> stop rept00
8.2 Specify the ggschema for DDL replication.
GGSCI> edit params ./GLOBALS GGSCHEMA goldengate
8.3 Grant ddl privileges to OGG user on both side.
SQL > grant execute on utl_file to goldengate;SQL > grant restricted session to goldengate;SQL > grant create table , create sequence to goldengate;
8.4 Run the scripts to enable DDL replication.
~ ]$ cd / ggsSQL > @marker_setup .sql SQL > @ddl_setup .sql SQL > @role_setup .sql SQL > GRANT GGS_GGSUSER_ROLE TO goldengate;SQL > @ddl_enable .sql SQL > @$ORACLE_HOME/ rdbms/ admin/ dbmspool.sqlSQL > @ddl_pin .sql goldengate
8.5 Modify the capture and replicat processes.
GGSCI> edit params capt00 ddl include all ddloptions addtrandata, report GGSCI> edit params rept00 ddl include all ddlerror default ignore retryop
8.6 Start all OGG processes.
GGSCI> start capt00 GGSCI> start pump00 GGSCI> start rept00
8.7 Verify the DDL replication valid.
SQL > conn jove/ joveSQL > alter table jove.t1 add (city varchar (20 ) default 'beijing' not null );SQL > insert into jove.t1 (id, name) values (1 , 'b' )SQL > select * from jove.t1;SQL > drop table jove.t1;
9.1 Stop all OGG processes.
GGSCI> stop capt00 GGSCI> stop pump00 GGSCI> stop rept00
9.2 Configuring the initial extract process on source.
GGSCI> edit params ipump00 extract ipump00 userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, encryptkey default rmthost 172.17.0.3, mgrport 7809 rmtfile ./dirdat/irept00/ld.dat, purge table user01.*; table user02.*; table user03.*; GGSCI> add extract ipump00, sourceistable
9.3 Configuring the initial replicat process on target.
~]$ cd /ggs ~]$ mkdir -p ./dirdat/irept00 GGSCI> edit params irept00 replicat irept00 specialrun userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, encryptkey default assumetargetdefs extfile ./dirdat/irept00/ld.dat map user01.*, target user01.*; map user02.*, target user02.*; map user03.*, target user03.*; GGSCI> add replicat irept00, specialrun
9.4 Just start the initial datapump process on source only.
GGSCI> start ipump00 GGSCI> info ipump00 GGSCI> info irept00
9.5 Verify the production table has been initialized.
SQL > select count (* ) from user01.t1@to_source union all select count (* ) from user01.t1;SQL > select count (* ) from user02.t1@to_source union all select count (* ) from user02.t1;SQL > select count (* ) from user03.t1@to_source union all select count (* ) from user03.t1;
9.6 Delete the initial datapump and replicat processes.
GGSCI> delete extract ipump00 GGSCI> delete replicat irept00
11.1 Uncomment the production table on OGG processes.
GGSCI> edit params capt00 table user01.*; table user02.*; table user03.*; GGSCI> edit params pump00 table user01.*; table user02.*; table user03.*; GGSCI> edit params rept00 map user01.*, target user01.*; map user02.*, target user02.*; map user03.*, target user03.*;
11.2 Restart all OGG processes.
GGSCI> start capt00 GGSCI> start pump00 GGSCI> start rept00
11.3 Verify the production table has been replicated.
SQL > select count (* ) from user01.t1@to_source union all select count (* ) from user01.t1;SQL > select count (* ) from user02.t1@to_source union all select count (* ) from user02.t1;SQL > select count (* ) from user03.t1@to_source union all select count (* ) from user03.t1;
12.1 Verify the consistency of the data.
12.2 Stop OGG replication
GGSCI> stop capt00 GGSCI> stop pump00 GGSCI> stop rept00
12.3 Enable the constraints/triggers/jobs.
・ How to disable,enable the constraints,triggers,jobs for oracle database migration
