Simple Ogg Configuration

If you want to perform Ogg synchronization for some tables, you can follow the steps below to perform the operation. Normally, the execution is almost done in order.
[Database preparation]
First, enable archiving, global logging, and forcelogging.
Shutdown immediate;
Startup Mount;
Alter database archivelog;
Alter database open;
-- If the query result is yes, execute alter.
Select supplemental_log_data_min, force_logging from V $ database;
Alter database add Supplemental log data;
Alter database force logging;

The following information is required for both the source and target databases:
1. Create an ogguser user
Create user ogguser identified by ogguser;
Grant connect to ogguser;
Grant resource to ogguser;
Grant unlimited tablespaces to ogguser;
Grant execute on tul_file to ogguser;
Grant select any dictionary to ogguser;
Grant select any table to ogguser;
Grant alter any table to ogguser;
Grant flashback any table to ogguser;
Grant execute on dbms_flashback to ogguser;

2. decompress and install goldengate and create directories./ggsci and create subdirs.
[[Email protected] goldengate] $./ggsci
Ggsci (Rac1) 2> Create subdirs

3. Create a ckpt table
Edit Param./globals
Ggschema ogguser
Checkpointtable ogguser. Checkpoint
Ggsci (dbdata) 9> dblogin userid ogguser password ogguser
Successfully logged into database.

Ggsci (dbdata as [email protected]) 10> Add checkpointtable ogguser. Checkpoint
Successfully created checkpoint table ogguser. Checkpoint.

-- If an existing problem exists or a problem exists, use sqlplus to drop and re-execute the preceding
Sqlplus ogguser/ogguser
Drop table checkpoint;
Drop table checkpoint_lm;

4. Create and start the Mgr Process
Edit Param Mgr
Port 7839
Dynamicportlist 7840-7914
Autostart extract *
Autorestart extract *, retries 3, waitminutes 3
Purgeoldextracts./dirdat/*, usecheckpoints, minkeepdays 5
Lagreporthours 1
Laginfominutes 30
Lagcriticalminutes 45

Ggsci (Rac1 as [email protected]) 15> Start Mgr
Manager started.
[Source database]
0 \ identify the table to be synchronized and add additional logs:
Alter table test. t_test add Supplemental log data (all) columns;
Alter table test. AA add Supplemental log data (all) columns;
Alter table test. BB add Supplemental log data (all) columns;

1. Extraction Process
Add ext ext1, tranlog, threads 1, begin now
Add exttrail./dirdat/R1, extract ext1, megabytes 1000

Edit Param ext1
Extract ext1
Setenv (nls_lang = american_america.zhs16gbk)
Setenv (oracle_sid = Rac1)
Userid ogguser, password ogguser
-- Reportcount every 1 minutes, Rate
-- Numfiles 5000
Discardfile./dirrpt/EXT. DSC, append, megabytes 100
Discardrolover
Exttrail./dirdat/R1, megabytes 100
Dynamicresolution
Tranlogoptions convertucs2clobs
Tranlogoptions dblogreader
Fetchoptions nousesnapshot
Fetchoptions fetchpkupdatecols
Statoptions reportfetch
Warnlongtrans 5 h, checkinterval 30 m
---- Tables ------
Table Test. t_test;
Table Test. AA;
Table Test. BB;

Start ext1

-- The query of the character set of the database environment variable must correspond to the following
SQL> select * From nls_database_parameters;
Parameter Value
--------------------------------------------------------------------------------
-- Nls_language American
-- Nls_territory America
Nls_currency $
Nls_iso_currency America
Nls_numeric_characters .,
-- Nls_characterset zhs16gbk

2. transmission process
Add extract pxt1, exttrailsource./dirdat/r1
Add rmttrail./dirdat/P1, extract pxt1, megabytes 1000
-- 192.168.5.233 corresponds to the IP address of the target host.
Edit Param pxt1
Extract pxt1
Dynamicresolution
Passthru
Rmthost 192.168.5.233, mgrport 7839, compress
Rmttrail./dirdat/P1
Numfiles 500
--- Tables
Table Test. t_test;
Table Test. AA;
Table Test. BB;

Start pxt1

3. Notes:
1. Before starting the Mgr process on the target machine
2. Check whether data is captured and retrieved from the source and target databases. ll dirdat
Verify that the source is captured normally and the r000000 file has been generated
[[Email protected] goldengate] $ ll dirdat
16
-RW-r ----- 1 Oracle oinstall 1370 10-17 r000000

Verify that the data obtained by the target end is normal. A p000000 file has been generated.
[[Email protected] goldengate] $ ll dirdat
Total 12
-RW-r ----- 1 Oracle oinstall 0 Oct 17 p000000

[Target database data initialization]
1. Obtain the SCN from the source database
Source database SCN is required
Col current_scn format 999999999999999.
Select current_scn from V $ database;
2. Source + target database
Create or replace directory dump_dir as '/home/Oracle/dump_dir ';
Grant read, write on directory dump_dir to ogguser;
3. expdp/impdp
Source database
Expdp test/test directory = dump_dir tables = t_test, AA, BB dumpfile = expdp_test_2tabs.dmp logfile = expdp_test_2tabs.log flashback_scn = 8689213127
-- Transfer the file to the target database and restore it.
SCP/mnt/dump_dir/expdp_test_2tabs * 192.168.5.233:/home/Oracle/dump_dir
Target Database
Impdp ogguser/ogguser directory = dump_dir remap_schema = test: ogguser dumpfile = expdp_test_bb.dmp logfile = expdp_test_bb_imp.log

[Target database]
Replication Process
The file/directory used is the P1 of the source transmission process.
Add replicat rxt1, exttrail./dirdat/P1,

Edit Param rxt1
Replicat rxt1
Handlecollisions
Assumetargetdefs
-- Setenv (oracle_sid = test233)
Setenv (nls_lang = american_america.zhs16gbk)
Userid ogguser, password ogguser
Dboptions nosuppresstriggers
Reportcount every 1 minutes, Rate
-- Reperror default, abend
Numfiles 500
Discardfile./dirrpt/rxt. DSC, append, megabytes 100
Allownoopupdates
---- Tables -----
Map test. t_test, target ogguser. t_test;
Map test. AA, target ogguser. AA;
Map test. BB, target ogguser. BB;

Start rxt1

Notes
This parameter must be added when the nosuppresstriggers target uses a trigger. Otherwise, the trigger cannot be used.

[(Tables with triggers) synchronization test]
-- The source table and the target table (the structure is the same, initially from the source database impdp), must have a primary key, the primary key must be a condition in the trigger
Create Table bb
(
Bb varchar2 (30) not null,
Sdate date not null
);
Alter table bb add constraint pk_ B primary key (bb );
-- Intermediate table, which does not have a primary key
Create Table bb_changes
(
Bb varchar2 (30 ),
Timestamp timestamp (6 ),
Status Number default 0
);
-- Trigger. The primary key of the source table must be one of the conditions.
Create or replace trigger tr_bb_changes
After insert or update on bb
For each row
Declare
CNT number;
Begin
Select count (1)
Into CNT
From bb_changes s
Where: New. BB = S. bb
And S. Status = 0;
If CNT = 0 then
Insert into bb_changes
(BB, timestamp)
Values
(: New. BB, current_timestamp );
End if;
End;

Run in source database
Insert into BB (BB, sdate) values ('1', sysdate );
Commit;
Insert into BB (BB, sdate) values ('2', sysdate );
Commit;
Insert into BB (BB, sdate) values ('3', sysdate );
Commit;

Update BB set BB = '11' where BB = '1 ';
Commit;
-- In the target database, we can see that the above data already exists in the BB table, and the corresponding DML records will be added to bb_changes.
Select * From ogguser. BB order by sdate DESC;
Select * From ogguser. bb_changes;

Simple Ogg Configuration