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

This article is to share with you about how to configure OGG under RAC. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

OGG configuration under RAC (only DML operations are transmitted)

1. [source and destination] create ACFS file system, create ogg software installation directory in shared storage, upload files and extract ogg software.

Cd / ogg

Tar-xvf / soft/fbo...

Add oracle environment variable LD_LIBRARY_PATH

Vi / .bash_profile

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Installation:

. / ggsci

Create subdirs

-- complete the ogg installation

2. [source side] check whether archiving is enabled

SQL > select name,log_mode,supplemental_log_data_min from v$database

NAME LOG_MODE SUPPLEME

MMDB NOARCHIVELOG NO

a. Open archiving

SHUTDOWN IMMEDIATE-close the database:

STARTUP MOUNT-- Boot to Mount state

ALTER DATABASE ARCHIVE LOG;-start archiving

ALTER DATABASE OPEN-Open the database

ARCHIVE LOG LIST-check the archiving status and confirm that the archiving location is appropriate

b. Open the minimum additional log

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;-Open the minimum attached log

ALTER SYSTEM SWITCH LOGFILE-- switch logs to take effect

c. Turn on the OGG feature

SHOW PARAMETER GOLDENGATE

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH

3. [source-side destination] determines that all tables to be synchronized must have a primary key or unique index

COL OWNER FOR A20

COL TABLENAME FOR A30

SELECT OWNER,TABLE_NAME,CONSTRAINT_TYPE,INVALID FROM DBA_CONSTRAINTS WHERE OWNER='SCOTT' AND CONSTRAINT_TYPE IN ('paired reproducing U');-- query which tables under the user have primary keys

4. OGG user creation:

_

[source]:

CREATE USER ggadmin IDENTIFIED BY ggadmin123, DEFAULT TABLESPACE USERS

GRANT CONNECT TO ggadmin

GRANT CREATE SESSION TO ggadmin

GRANT ALTER SESSION TO ggadmin

GRANT RESOURCE TO ggadmin

GRANT SELECT ANY DICTIONARY TO ggadmin

GRANT SELECT ANY TABLE TO ggadmin

GRANT FLASHBACK ANY TABLE TO ggadmin

GRANT ALTER ANY TABLE TO ggadmin

Or

GRANT DBA TO ggadmin

[destination]:

Set up OGG database users

? Write data to the database through sql

? OGG target database user rights

CREATE USER ggadmin IDENTIFIED BY ggadmin123, DEFAULT TABLESPACE USERS

GRANT ALTER SESSION TO ggadmin

GRANT CREATE SESSION TO ggadmin

GRANT CONNECT TO ggadmin

GRANT RESOURCE TO ggadmin

GRANT SELECT ANY DICTIONARY TO ggadmin

GRANT SELECT ANY TABLE TO ggadmin

GRANT FLASHBACK ANY TABLE TO ggadmin

GRANT INSERT ANY TABLE TO ggadmin

GRANT UPDATE ANY TABLE TO ggadmin

GRANT DELETE ANY TABLE TO ggadmin

Or

GRANT DBA TO ggadmin

5. [source configuration]:

5.1 mgr configuration

. / ggsci

GGSCI > edit param mgr

PORT 7809 / / listening port PORT

DYNAMICPORTLIST 7810-7900 / / dynamic listening port PORT, used when listening port fails

-- AUTOSTART ER * / / automatically start all EXTRACT and REPLICAT processes after starting the management process

AUTORESTART ER *, RETRIES 5WAITMINUTES 3 resume 60 / / automatically restart the abnormally aborted process, retry 5 times, wait 2 minutes each time, and try again in an hour

STARTUPVALIDATIONDELAY 5 / / delay check for 5 seconds at startup

PURGEOLDEXTRACTS. / dirdat/*,USECHECKPOINTS, MINKEEPDAYS 3 / / automatically clean up used trail queues and keep them for at least 3 days

LAGREPORTHOURS 1 / / hourly check delay

LAGINFOMINUTES 30 / / delay recording information to error log for 30 minutes

LAGCRITICALMINUTES 45 / / delay 50 minutes to record a warning to error log

GGSCI > START MGR / / Open the management process

GGSCI > INFO ALL / / verify that it is open

5.2 add extraction process and queue files

GGSCI > ADD EXTRACT ex_jtwx1, tranlog, begin now,thread 2

GGSCI > ADD EXTTRAIL. / dirdat/la,EXT ex_jtwx1, MEGABYTES 100

GGSCI > ADD EXTRACT dp_jtwx1, EXTTRAILSOURCE. / dirdat/la

GGSCI > ADD RMTTRAIL / ogg/dirdat/ra, EXT dp_jtwx1, MEGABYTES 100

5.3 configure extraction process parameters

Extract extraction process parameter configuration:

. / ggsci

GGSCI > edit param ex_jtwx1

EXTRACT ex_jtwx1

-- [environment variable, optional]

-- SETENV (ORACLE_SID=xxx)

-- SETENV (ORACLE_HOME=xxx)

-- SETENV (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

USERID ggadmin, PASSWORD ggadmin123

TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE 4194304, BUFSIZE 4194304

FETCHOPTIONS FETCHPKUPDATECOLS

NOCOMPRESSDELETES

EXTTRAIL. / dirdat/la

DYNAMICRESOLUTION / / dynamic parsing table structure

-- TRANLOGOPTIONS CONVERTUCS2CLOBS / / extraction CLOB must be added

-- GETTRUNCATES / / captures truncate operations. It is not captured by default.

-- [tables to be transmitted]

Table myschema.maytable1

Table myschema.maytable2

Table myschema.*

-- TABLE hq.acct, COLS (col1, col3);-- copy only 1pm 3 columns

-- TABLE hq.acct, where (id=1 and name=2);-- filter, only copy records that meet the criteria

-- TABLE hq.acct, COLSEXCEPT (col4);-- copy except for column 4

Datapump transport process parameter configuration:

GGSCI > edit param dp_jtwx1

Extract dp_jtwx1

Setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

Passthru / / passthru indicates that the process is a transfer process data pump, which does not need to interact with the database, but only needs to move the data.

-- REPORT AT 01:59 / / configure to report the transfer status at a specific time

-- reportrollover at 02:00 / / specifies that a new report is generated at a specific time every day

Rmthost 10.128.59.52 Magi Mgrport 7809. The compress / / transport process needs to know the host port information of the destination side.

Rmttrail / ogg/dirdat/ra

Dynamicresolution

Numfiles 8000

Table myschema.mytable1

Table myschema.mytable2

TABLE hq.acct; / / notice that you no longer need to filter cols at this time.

5.4 add table-level additional logs

GGSCI > DBLOGIN USERID ggadmin, PASSWORD ggadmin123

GGSCI > LIST TABLES SCOTT.*

GGSCI > ADD TRANDATA scott.emp

GGSCI > ADD TRANDATA scott.dept

....

Or

SQL > alter table user.table add supplemental log group ggs_table_1 (competitive or UK) always

SQL >

GGSCI > START *-start the extraction process and transfer process

GGSCI > INFO ALL-- check to see if it starts properly

-

[note 1]

GGSCI > VIEW REPORT ex_jtwx1-- Common method for troubleshooting 1

GGSCI > sh tail-10 ggserr.log-Common methods for troubleshooting 2

6. [destination configuration]

6.1 configure GLOBALS parameters

GGSCI > EDIT PARAM. / GLOBALS

GGSCHEMA ggadmin

CHECKPOINTTABLE ggadmin.checkpointtable

6.2 create a checkpoint record table

GGSCI > DBLOGIN USERID ggadmin, PASSWORD ggadmin123

GGSCI > ADD CHECKPOINTTABLE ggadmin.checkpointtable

6.3 configure the delivery process

GGSCI > ADD REP rp_jtwx1, EXTTRAIL. / dirdat/ra, CHECKPOINTTABLE ggadmin.checkpointtable

GGSCI > EDIT PARAM rp_jtwx1

REPLICAT rp_jtwx1

-- Environmental variables, not required

-- SETENV (ORACLE_SID=ogg2)

-- SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

-- SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID ggadmin, PASSWORD ggadmin

-- REPORT AT 01:59 / / configure to report replication status at a specific time

-- REPORTROLLOVER AT 02:00 / / specifies that a new report is generated at a specific time every day

REPERROR default,discard / / configure the processing mode after an error occurs. Here, abend means that the process is terminated when an error occurs and is resumed after correction by the customer. It can also be set to discard, where the error data is written to the file defined by the following parameters, but the processing continues and the process does not exit.

INSERTALLRECORDS

DISCARDFILE. / dirrpt/rp_jtwx1.dsc,append, megabytes 200 / / defines the target file for writing error messages after an error, and provides help for error checking. Append means to add at the end, purge means to generate a new disard file each time; megabyte represents the maximum allowable size, and if this size is exceeded, the process will be terminated.

ASSUMETARGETDEFS / / indicates that the source and target tables have the same structure, that is, the field definitions of the same database type and table are the same.

DYNAMICRESOLUTION / / dynamically parse the structure of the table to speed up startup

-- HANDLECOLLISIONS / / conflict data processing, mainly used during initialization

-- GETTRUNCATES / / synchronous truncate operation

MAP scott.emp, TARGET scott.emp

MAP scott.dept, TARGET scott.dept1, COLMAP (usedefaults, committime = @ GETENV ("GGHEADER", "COMMITTIMESTAMP"))

-- / / define the mapping between the source table and the target table. The column mapping is defined in colmap. If the columns of the two tables are all the same, then colmap is not used.

-- userdefaults indicates that the columns that are not listed below are all mapped according to the default rule (corresponding to the same column name), while the column following, such as committime, is assigned to a GoldenGate's own variable, in this case, the commit time.

GGSCI > START REP rp_jtwx1 / / start the delivery process

GGSCI > INFO ALL / / check status

-

[note 2]

GGSCI > help-- View available commands

GGSCI > help add exttrail-- View how to use the add queue file command

GGSCI > send rp_jtwx1,status-- View the progress of data synchronization

GGSCI > stats rp_jtwx1-- View the statistics of the amount of data processed by the delivery process

[scenario 1] the primary key is inconsistent

Destination: MAP src.s_tab, TARGET tar.t_tab, KEYCOLS (s_tab_pk_col)

Or source side: Add trandata src.s_tab, cols (t_tab_pk_col), nokey

[scenario 2] data filtering

Source: TABLE demo_src.people, FILTER (age > 50)

Age needs to be a primary key to filter, or Add trandata demo_src.people, cols (age) forces additional logs to be added to the column

[scenario 3] data initialization

After the initial data is loaded through import

Source side: ALTER ex_jtwx1,begin 2010-03-21 08:00:00 (ensure the existence of archives)

Or destination: ALTER REPLICAT rp_jtwx1, EXTSEQNO 53, EXTRBA 0

[scenario 4] data conflict processing

Use handlecollisions in replicat (must have a primary key or unique index)

The above is how to configure OGG under RAC. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.