Goldengate configuration instance: one-way synchronization from Oracle 10g RAC in RHEL 4.7 to a single instance (2)

Install DDL objects

Log on to sqlplus as an oracle user at the source end and execute the following script:

Run marker_setup

Make sure that the related process of goldengate is closed. Any application using Oracle is closed and no new session is generated. Run the following command:

[Goldengate @ gg1 ~] $ CD/opt/Gg/goldengate/
[Goldengate @ gg1 goldengate] $ sqlplus/As sysdba

SQL> @ marker_setup

Marker setup script

You will be prompted for the name of a schema for the goldengate database objects.
Note: The Schema must be created prior to running this script.
Note: Stop all DDL replication before starting this installation.

Enter goldengate schema name: goldengate

......

Script complete.

SQL>

Run ddl_setup

Make sure all sessions are closed

SQL> @ ddl_setup

Goldengate DDL replication setup script

Verifying that current user has privileges to install DDL replication...
Checking user sessions...

Check complete.

You will be prompted for the name of a schema for the goldengate database objects.
Note: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
Note: The Schema must be created prior to running this script.
Note: Stop all DDL replication before starting this installation.

Enter goldengate schema name: goldengate

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter initialsetup
To upgrade DDL replication, enter normal
Enter mode of installation: initialsetup

Working, please wait...
Spooling to file ddl_setup_spool.txt

Using goldengate as a goldengate schema name, initialsetup as a mode of installation.

Working, please wait...

Recyclebin must be empty.
This installation will purge recyclebin for all users.
To proceed, enter yes. To stop installation, enter NO.

Enter YES or NO: Yes

......

Script complete.
SQL>

Run role_setup

Create a role named ggs_ggsuser_role that contains the permissions required by DDL objects.
SQL> @ role_setup

GGS role setup script

This script will drop and recreate the role ggs_ggsuser_role
To use a different role name, quit this script and then edit the Params. SQL script to change the gg_role parameter to the preferred name. (do not run the script .)

You will be prompted for the name of a schema for the goldengate database objects.
Note: The Schema must be created prior to running this script.
Note: Stop all DDL replication before starting this installation.

Enter goldengate schema name: goldengate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the extract, ggsci, and Manager processes, by using the following SQL command:

Grant ggs_ggsuser_role to <loggeduser>

Where <loggeduser> is the user assigned to the goldengate processes.
SQL>

Grant the created role to the goldengate user

SQL> grant ggs_ggsuser_role to goldengate;

Grant succeeded.

SQL>

Enable DDL triggers

SQL> @ ddl_enable

Trigger altered.

SQL>

Install an optional performance tool to install the dbms_shared_pool package

If the dbms_shared_pool package does not exist in the system, manually execute the script for installation. As follows:

SQL> select object_name, object_type from dba_objects where object_name = 'dbms _ shared_pool ';

No rows selected

SQL> @? /Rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

SQL> select object_name, object_type from all_objects where object_name = 'dbms _ shared_pool ';

Object_name object_type
-------------------------------------------------
Dbms_shared_pool package
Dbms_shared_pool package body

2 rows selected.

Ddl_pin

Ddl_pin puts the PLSQL package used by the trigger into the memory

SQL> @ ddl_pin goldengate

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

Configure goldengate to create the goldengate working directory

Source and target:

[Goldengate @ Rac1 goldengate] $ CD $ gg_home
[Goldengate @ Rac1 goldengate] $./ggsci

Ggsci (Rac1) 1> Create subdirs

Creating subdirectories under current directory/opt/Gg/goldengate

Parameter files/opt/Gg/goldengate/dirprm: created
Report files/opt/Gg/goldengate/dirrpt: created
Checkpoint files/opt/Gg/goldengate/dirchk: created
......

Ggsci (gg1) 2> exit

Create a directory for storing trail files

Source and target:

[Goldengate @ Rac1 ~] $ Mkdir/opt/Gg/Trails
[Goldengate @ Rac1 ~] $ LS-L/opt/GG | grep Trails
Drwxr-XR-x 2 goldengate oinstall 4096 Nov 27 Trails
[Goldengate @ Rac1 ~] $

Configuration Manager

Source and target:

The port range used by the goldengate (extract and replicat) process is configured in dynamicportlist.
The port parameter specifies the port used by the manager.
The autorestart parameter automatically restarts when the extraction/replication process fails.
Configure the manager parameter. The purgeoldextracts parameter specifies that the trail files that have been extracted and copied are automatically deleted based on the checkpoint, but the last 10 files are retained.
Purgeddlhistory and purgemarkerhistory Delete the expired data in the DDL History Table and the marker table respectively to prevent them from becoming too large.

Ggsci (gg1) 1> edit Params Mgr

Dynamicportlist 7840-7914
Port 5898
Purgeoldextracts/opt/Gg/trails/W1 *, usecheckpoints, minkeepfiles 10
Purgeoldextracts/opt/Gg/trails/W2 *, usecheckpoints, minkeepfiles 10
Autorestart er *, retries 3, waitminutes 5
Purgeddlhistory minkeepdays 3, maxkeepdays 5, frequencyminutes 30
Purgemarkerhistory minkeepdays 3, maxkeepdays 5, frequencyminutes 30
~
~

Global parameter settings

Source end:

Ggsci (Rac1) 2> edit Params./globals
Ggschema goldengate
~
~
~

Target end:

Create a checkpoint table
Replicat maintains the read position in the trail file through this table. This is not a required operation. If this table is not available, a disk file is used for maintenance.

Ggsci (ggdb) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.

Ggsci (ggdb) 3> Add checkpointtable goldengate. chkpoint

Successfully created checkpoint table goldengate. chkpoint.

Ggsci (ggdb) 4> edit Params./globals

Ggschema goldengate
Checkpointtable goldengate. chkpoint
~
~
~
~
"./Globals" 2L, 44c written

Configure the source Extract

To prevent Primary extract from being affected by the network, we add a Data Pump between the source end and the target end. In this way, Primary extract extracts data from the source data, the local trail file exists, and the Data Pump process is responsible for transferring data from the local trail file to the target trail file. This improves flexibility and availability (when the network between the source and target fails, Primary extract will continue to extract data to the local Trail). The diagram is as follows:

Here we want to synchronize all tables under four users: dycommondatabase20, dyulcentermanage, dyacdb34, dyulcenterm_bak
Here we divide them into two parts for testing: two primary extract, one for synchronizing data under dycommondatabase20 and dyulcentermanage, and the other for synchronizing data between dyacdb34 and dyulcenterm_bak.

First, add two primary extract groups. Because this is a two-node RAC, We need to specify the threads 2 option.

Ggsci (Rac1) 4> Add extract w1ext, tranlog, threads 2, begin now

Extract added.

Ggsci (Rac1) 5> info all

Program Status group lag time since chkpt

Manager stopped
Extract stopped dyext 00:00:00

Edit the w1ext parameter file:

1. The exttrail parameter specifies the exttrail corresponding to the extraction process
2. The discardfile parameter specifies a file to record records that cannot be processed normally. The append method is used here. The maximum value is 5 MB.
3. tranlogoptions altarchivelogdest specifies the path of the source database archive. If you are not sure, run the SQL * Plus command: Show parameter log_archive_dest_1.
4. tranlogoptions altarchivedlogformat specifies the archive file naming format. You can use the SQL * Plus command show parameter log_archive_format to determine the format.
5. Because the source data uses ASM, tranlogoptions asmuser is used to provide the username and password for logging on to the ASM instance (the username must be sys)
6. DDL include mapped indicates that only DDL operations within the mapped range are captured.
7. ddloptions addtrandata: when a new table is created, append logs are automatically enabled for it.
8. fetchoptions: missingrow report indicates that when the row to be obtained by extract cannot be located in the source database, the extract process continues to run and related error information is saved in the file specified by the discardfile parameter; usesnapshot indicates that extract uses flashback queries to obtain some data from undo, such as UDT, nested table, xmltype, and lob in 9i that cannot be directly obtained from redo; nouselatestversion makes extract unable to obtain data from undo, ignore this condition instead of getting the current value from the source table.
9. statoptions reportfetch: When the ggsci command stats is used, the obtained ROW statistics are displayed.
10. warnlongtrans 1 h, checkinterval 5 m

Ggsci (Rac1) 6> edit Params w1ext

Extract w1ext
Userid goldengate, password goldengate
Exttrail/opt/Gg/trails/W1
Discardfile w1extdsc, append, megabytes 5
Tranlogoptions altarchivelogdest + recovery_dg
Tranlogoptions altarchivedlogformat % T _ % S _ % R. DBF
Tranlogoptions asmuser sys @ oradb_asm, asmpassword bbb
DDL include mapped
Ddloptions addtrandata
Fetchoptions, usesnapshot, nouselatestversion, missingrow report
Statoptions reportfetch
Warnlongtrans 1 h, checkinterval 5 m
Table dycommondatabase20 .*;
Table dyulcentermanage .*;

Add the trail file corresponding to the extract to store the extracted data. The size of a single file is set to 100 MB.
Ggsci (Rac1) 7> Add exttrail/opt/Gg/trails/W1, extract w1ext, megabytes 100
Exttrail added.

Add secondary extract group, that is, Data Pump:

Ggsci (Rac1) 8> Add extract w1extdp, exttrailsource/opt/Gg/trails/W1, begin now
Extract added.

Edit parameter file
The IP address or host name of the target end after rmthost (which must be resolved by the corresponding IP address in the hosts file) and the port number of the Manager process. rmttrail specifies the location of the trail file on the target end.

Ggsci (Rac1) 9> edit Params w1extdp

Extract w1extdp
Userid goldengate, password goldengate
Rmthost 192.168.47.211, mgrport 5898
Rmttrail/opt/Gg/trails/W1
Discardfile w1extdpdsc, append, megabytes 5
Table dycommondatabase20 .*;
Table dyulcentermanage .*;
~
~
Add this rmttrail

Ggsci (Rac1) 10> Add rmttrail/opt/Gg/trails/W1, extract w1extdp, megabytes 100
Rmttrail added.

Similarly, we add the second set of primary & secondary extract and exttrail & rmttrail
Ggsci (Rac1) 11> Add extract w2ext, tranlog, threads 2, begin now
Extract added.

Ggsci (Rac1) 12> edit Params w2ext

Extract w2ext
Userid goldengate, password goldengate
Exttrail/opt/Gg/trails/W2
Discardfile w2extdsc, append, megabytes 5
Tranlogoptions altarchivelogdest + recovery_dg
Tranlogoptions altarchivedlogformat % T _ % S _ % R. DBF
Tranlogoptions asmuser sys @ oradb_asm, asmpassword bbb
DDL include mapped
Ddloptions addtrandata
Fetchoptions, usesnapshot, nouselatestversion, missingrow report
Statoptions reportfetch
Warnlongtrans 1 h, checkinterval 5 m
Table dyacdb34 .*;
Table dyulcenterm_bak .*;

Ggsci (Rac1) 13> Add exttrail/opt/Gg/trails/W2, extract w2ext, megabytes 100
Exttrail added.

Ggsci (Rac1) 14> Add extract w2extdp, exttrailsource/opt/Gg/trails/W2, begin now
Extract added.

Ggsci (Rac1) 15> edit Params w2extdp

Extract w2extdp
Userid goldengate, password goldengate
Rmthost 192.168.47.211, mgrport 5898
Rmttrail/opt/Gg/trails/W2
Discardfile w1extdpdsc, append, megabytes 5
Table dyacdb34 .*;
Table dyulcenterm_bak .*;
~
~

Ggsci (Rac1) 16> Add rmttrail/opt/Gg/trails/W2, extract w2extdp, megabytes 100
Rmttrail added.

Configure the target replicat

Return to the target end and use the goldengate user to log on to the ggsci command line. Two replicat entries are added for the previous two groups of extract and Data Pump.

Ggsci (ggdb) 6> Add replicat w1rep, exttrail/opt/Gg/trails/W1, checkpointtable goldengate. chkpoint
Replicat added.

Here, the handlecollisions parameter is removed after the target data is initialized and synchronized.

Edit the parameter file:
1. assumetargetdefs
2. ddloptions Report: Write DDL details to the report file.
3. batchsql: put similar SQL statements into an array to speed up execution. In normal mode, repliat only applies one SQL statement at a time.
4. dboptions deferrefconst: delays integrity constraints until the replicat transaction is committed before detection.
5. If the database version is later than 10.2.0.5 or 11.2.0.2, you can use dboptions suppresstriggers to disable the trigger in the replicat session. If not, disable the trigger in the target database (the DML operations generated by the trigger will be synchronized from the source to the target database)
6. dboptions lobwritesize: the lob data to be written to the target database is cached in the memory. When the size specified in the parameter is reached, data is written to reduce I/O. The value range is 2 kb to 1 MB. The default value is 32 KB.
7. ddlerror default discard retryop maxretries 5 retrydelay 20: When a DDL error occurs, retry five times. The interval is 20 seconds. If it fails, replicat will continue to run, but the relevant information will be recorded in discardfile.
8. handlecollisions: When replicat inserts a record into the table and the record already exists, it overwrites it. When replicat tries to update or delete a record in the table, the record does not exist, the operation is discarded. This parameter is generally used in initial-data load. This parameter should be deleted after data synchronization at the source and target ends.
9. Map target: the ing between the source table and the target table. Wildcards can be used.

Ggsci (ggdb) 7> edit Params w1rep

Replicat w1rep
Assumetargetdefs
Userid goldengate, password goldengate
Discardfile w1repdsc, append, megabytes 5
DDL include mapped
Ddloptions report
Batchsql
Dboptions deferrefconst
Dboptions lobwritesize 102400
Handlecollisions
Ddlerror default discard retryop maxretries 5 retrydelay 20
Map dycommondatabase20. *, target dycommondatabase20 .*;
Map dyulcentermanage. *, target dyulcentermanage .*;
~
~
~

Configure another replicat process w2rep.
Ggsci (ggdb) 8> Add replicat w2rep, exttrail/opt/Gg/trails/W2, checkpointtable goldengate. chkpoint
Replicat added.

Ggsci (ggdb) 9> edit Params w2rep

Replicat w2rep
Assumetargetdefs
Userid goldengate, password goldengate
Discardfile w2repdsc, append, megabytes 5
DDL include mapped
Ddloptions report
Batchsql
Dboptions deferrefconst
Dboptions lobwritesize 102400
Handlecollisions
Ddlerror default retryop maxretries 5 retrydelay 20
Map dyacdb34. *, target dyacdb34 .*;
Map dyulcenterm_bak. *, target dyulcenterm_bak .*;
~

Add trandata

Before starting goldengate-related processes, enable object-level append logs for tables to be synchronized in the source database using trandata on the target end.

Ggsci (Rac1) 17> dblogin userid goldengate, password goldengate
Successfully logged into database.

Ggsci (Rac1) 18> Add trandata dycommondatabase20 .*
Logging of supplemental redo data enabled for table dycommondatabaseappsauditconfig.

Logging of supplemental redo data enabled for table dycommondatabase‑audithistory.
......

If the table does not have a primary key or a unique key, a warning is generated, for example:
22:53:11 warning OGG-00869 no unique key is defined for table udppackportset. All viable columns will be used to represent the key, but may not guarantee uniqueness. keycols may be used to define the key.

Ggsci (Rac1) 19> Add trandata dyulcentermanage .*
......
Ggsci (Rac1) 20> Add trandata dyacdb34 .*
......
Ggsci (Rac1) 21> Add trandata dyulcenterm_bak .*
......

There is no primary key or unique key in the business table. This is a bad habit and we will not criticize it here...

Goldengate configuration instance: one-way synchronization from Oracle 10g RAC in RHEL 4.7 to a single instance (1)

Http://blog.csdn.net/wildwave/article/details/7053768

Goldengate configuration instance: one-way synchronization from Oracle 10g RAC in RHEL 4.7 to a single instance (2)

Http://blog.csdn.net/wildwave/article/details/7056362

Goldengate configuration instance: one-way synchronization from Oracle 10g RAC in RHEL 4.7 to a single instance (3)

Http://blog.csdn.net/wildwave/article/details/7056451

Goldengate configuration instance: one-way synchronization from Oracle 10g RAC in RHEL 4.7 to a single instance (4)
Http://blog.csdn.net/wildwave/article/details/7056500