Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand the one-way replication of Oracle database". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand the one-way replication of Oracle database".

1. Environmental requirements:

Two virtual machine servers equipped with Oracle software, and configured to listen and install the database

Linux

Oracle

OGG

IP

SID

OGG1

CentOS 6.5

11.2.0.4

12.2.0.2

192.168.1.211

Ogg

OGG2

CentOS 6.5

11.2.0.4

12.2.0.2

192.168.1.212

Ogg

two。 Configuration preparation

2.1 create an operating system user

Useradd ogg-g oinstall

Create a new installation directory

[root@ogg1] # mkdir-p / u01/ogg

[root@ogg1] # chown-R ogg.oinstall / u01/ogg

[root@ogg1 ~] # chmod 775 / u01/ogg/

2.2 set the user's environment variable, in particular, specify the location of the lib library and the ggsci location:

Export PATH

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Export GG_HOME=/u01/ogg

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Export PATH=$GG_HOME:$PATH

2.3 formal installation

[root@ogg1 soft] # unzip 122022_fbo_ggs_Linux_x64_shiphome.zip

[ogg@ogg1 ~] $cd / u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/

[ogg@ogg1 Disk1] $. / runInstaller

A brief installation step: set the installation directory to / u01/ogg

3. Replication preparation: achieving single table replication

Check whether the relevant parameters are enabled

Select LOG_MODE, FORCE_LOGGING, SUPPLEMENTAL_LOG_DATA_min from v$database

3.1 enable parameters (need to enable archiving mode and forced archiving)

SQL > show parameter golde

NAME TYPE VALUE

-

Enable_goldengate_replication boolean FALSE

SQL > alter system set enable_goldengate_replication=true

SQL > select SUPPLEMENTAL_LOG_DATA_min from v$database

SUPPLEME

-

NO

SQL > alter database add supplemental log data

Database altered.

SQL > select SUPPLEMENTAL_LOG_DATA_min from v$database

SUPPLEME

-

YES

SQL > alter system archive log current

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 536874104 bytes

Database Buffers 289406976 bytes

Redo Buffers 2392064 bytes

Database mounted.

SQL > alter database force logging

Database altered.

SQL > alter database archivelog

Database altered.

SQL > alter database open

Database altered.

System altered.

The source side and the destination side are set respectively

Source: create user ogg_source identified by oracle default tablespace users

Grant alter session to ogg_source

Grant create session to ogg_source

Grant connect to ogg_source

Grant resource to ogg_source

Grant select any dictionary to ogg_source

Grant select any table to ogg_source

Grant flashback any table to ogg_source

Grant alter any table to ogg_source

Target: create user ogg_target identified by oracle default tablespace users

Grant alter session to ogg_target

Grant create session to ogg_target

Grant connect to ogg_target

Grant resource to ogg_target

Grant select any dictionary to ogg_target

Grant select any table to ogg_target

Grant flashback any table to ogg_target

Grant alter any table to ogg_target

Grant insert any table to ogg_target

Grant update any table to ogg_target

Grant delete any table to ogg_target

3.2 Ogg configuration

Problem solved: unable to log in, it's the oracle_sid setting problem

GGSCI (ogg1) 5 > dblogin userid ogg_source, password oracle

ERROR: Unable to connect to database using user ogg_source. Please check privileges.

Unable to initialize database connection because of error ORA-12162: TNS:net service name is incorrectly specified.

GGSCI (ogg1) 6 > exit

[ogg@ogg1 ~] $export ORACLE_SID=ogg

[ogg@ogg1 ~] $/ u01/ogg/ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Jun 30 2017 14:42:26

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg1) 1 > dblogin userid ogg_source, password oracle

Successfully logged into database.

1. Configure tables or users that need to be extracted

Add trandata luc.*

two。 Configure global parameters

. / GLOBALS must be capitalized

Transaction configuration and checkpoint table

3. Configure the MGR process

Edit params mgr

> port 7809

4. Configure Extract script

Configuration parameters, in a local way.

Edit params EXT_1

EXTRACT EXT_1

USERID ogg_source, PASSWORD oracle

EXTTRAIL / home/oracle/ogg/ogg_work/dirdat/ss

TABLE N1.*

Add an extraction process to grab data from the database log.

> ADD EXTRACT EXT_1, TRANLOG, BEGIN NOW

EXTRACT added.

Configure the local queue, and then start

> ADD EXTTRAIL / home/oracle/ogg/ogg_work/dirdat/ss, EXTRACT EXT_1

> start EXT_1

Sending START request to MANAGER...

EXTRACT EXT_1 starting

After the configuration is complete, you can check whether it can be extracted normally, and you can see that it is not started properly. The status is still STOPPED.

> info EXT_1

EXTRACT EXT_1 Initialized 2016-11-11 16:16 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:01:22 ago)

Log Read Checkpoint Oracle Redo Logs

2016-11-11 16:16:04 Seqno 0, RBA 0

5. Configure Pump script

> edit params dpump_1

EXTRACT dpump_1

PASSTHRU

RMTHOST 10.127.2.32, MGRPORT 1530

RMTTRAIL / home/oracle/ogg/ogg_work/dirdat/ss

TABLE n1.*

> ADD EXTRACT dpump_1,EXTTRAILSOURCE / home/oracle/ogg/ogg_work/dirdat/ss

EXTRACT added.

> ADD RMTTRAIL / home/oracle/ogg/ogg_work/dirdat/ss, EXTRACT dpump_1

RMTTRAIL added.

After the configuration is complete, start the PUMP process.

> start dpump_1

Sending START request to MANAGER...

EXTRACT DPUMP_1 starting views the information of the DUMP process as follows:

> info dpump_1

EXTRACT DPUMP_1 Last Started 2016-11-11 16:24 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Process ID 53479

Log Read Checkpoint File / home/oracle/ogg/ogg_work/dirdat/ss000000000

First Record RBA 0

6. Configure Application script

Configure the delivery queue parameters and apply the data to the target database. There is a mapping relationship here, that is, the n1.* of the source database corresponds to the n1.* of the target database.

> edit params rep_1

REPLICAT REP_1

USERID ogg_target, PASSWORD oracle

ASSUMETARGETDEFS

HANDLECOLLISIONS

MAP n1. Delivery and target n1. Add delivery queues

> ADD REPLICAT REP_1, EXTTRAIL / home/oracle/ogg/ogg_work/dirdat/ss,CHECKPOINTTABLE ogg_target.CHKPTAB

REPLICAT added.

> start REP_1

Sending START request to MANAGER...

REPLICAT REP_1 starting

The state after a successful startup is like this.

> INFO REP_1

REPLICAT REP_1 Last Started 2016-11-11 17:02 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:02 ago)

Process ID 69571

Log Read Checkpoint File / home/oracle/ogg/ogg_work/dirdat/ss000000000

First Record RBA 0

Error execution:

BEGIN

DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (

Grantee = > 'ogg_target'

Grant_privileges = > true)

END

/

Loop insert 1-100

Begin

For i in 1.. 100 loop

Insert into luc.test values (iGrainghuuu`)

End loop

End

/

Insert a large amount of data to test for synchronization:

Insert into luc.test select level,level | | 'obj' from dual connect by level

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.