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

Editor to share with you how to install OGG, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

1. Install the main library OGG

Note: database version 10.2 and 11.2.0.3 can be used goldengate11.2 version, database version 11.2.0.4 and 12C, need to use goldengate12.1 version, otherwise ddl_setup.sql footer error, modify database goldengate related parameters, show parameter goldengate view, change to true (both master and standby databases need to be modified).

SQL > ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH

System altered.

SQL > show parameter goldengate

NAME TYPE VALUE

-

Enable_goldengate_replication boolean TRUE

[root@prod ~] # mkdir / goldengate

[root@prod ~] # chown oracle:dba / goldengate/

[root@prod ~] # chmod 775 / goldengate/

[oracle@node1 ~] $cat .bash _ profile

ORACLE_BASE=/home/oracle/app/oracle

ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1

ORACLE_SID=test

PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:$HOME/bin

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;export LD_LIBRARY_PATH

[oracle@prod goldengate] $tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@prod goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (prod) 1 > create subdirs

Creating subdirectories under current directory / goldengate

Parameter files / goldengate/dirprm: already exists

Report files / goldengate/dirrpt: created

Checkpoint files / goldengate/dirchk: created

Process status files / goldengate/dirpcs: created

SQL script files / goldengate/dirsql: created

Database definitions files / goldengate/dirdef: created

Extract data files / goldengate/dirdat: created

Temporary files / goldengate/dirtmp: created

Stdout files / goldengate/dirout: created

2. The main database configuration 2.1 sets the database archiving mode

SQL > Select log_mode from v$database

LOG_MODE

-

NOARCHIVELOG

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 488534016 bytes

Fixed Size 2229624 bytes

Variable Size 301992584 bytes

Database Buffers 180355072 bytes

Redo Buffers 3956736 bytes

Database mounted.

SQL > alter database archivelog

Database altered.

SQL > alter database open

Database altered.

2.2 Open the database attached log

Use the following sql statement to check the opening of the database attached log:

SQL > Select

2 SUPPLEMENTAL_LOG_DATA_MIN

3, SUPPLEMENTAL_LOG_DATA_PK

4, SUPPLEMENTAL_LOG_DATA_UI

5, SUPPLEMENTAL_LOG_DATA_FK

6, SUPPLEMENTAL_LOG_DATA_ALL from v$database

SUPPLEME SUP SUP SUP SUP

NO NO NO NO NO

Open additional logs and switch logs (make sure Online redo log and Archive log are consistent)

SQL > alter database add supplemental log data

Database altered.

SQL > alter database add supplemental log data (primary key, unique,foreign key) columns

Database altered.

SQL > alter system switch logfile

System altered.

Fallback operation: if there is a problem, you can fallback with the following statement:

Alter database drop supplemental log data

Alter database drop supplemental log data (primary key, unique,foreign key) columns

Use the following sql statement to check the opening of the database attached log:

SQL > Select

2 SUPPLEMENTAL_LOG_DATA_MIN

3, SUPPLEMENTAL_LOG_DATA_PK

4, SUPPLEMENTAL_LOG_DATA_UI

5, SUPPLEMENTAL_LOG_DATA_FK

6, SUPPLEMENTAL_LOG_DATA_ALL from v$database

SUPPLEME SUP SUP SUP SUP

YES YES YES YES NO

Note: make sure the minimum additional log is opened and the pk,uk,fk additional log is turned on. The additional log of all columns is closed.

If the additional log for all columns is turned on, you need to turn it off using the following statement:

Alter database drop supplemental log data (ALL) columns

2.3 enable database mandatory log mode

SQL > Alter database force logging

Database altered.

Note: the opening of this mode requires relevant confirmation and discussion with the business unit; if the database cannot be opened to force logging mode, the no logging table cannot be synchronized.

If you need to fallback and turn off forced logging mode, use the following sql:

Alter database no force logging

2.4 create a user running GoldenGate

SQL > create tablespace goldengate datafile'/ u01 size autoextend on maxsize autoextend on maxsize 10240m

Tablespace created.

SQL > create user goldengate identified by goldengate default tablespace goldengate

User created.

SQL > grant resource, connect, dba to goldengate

Grant succeeded.

2.5 turn off the recyclebin of the database (configured only when DDL is implemented)

Query the parameter values of the current recyclebin (10g needs to be disabled, 11g does not):

SQL > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string on

SQL > alter system set recyclebin=off scope=spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 488534016 bytes

Fixed Size 2229624 bytes

Variable Size 306186888 bytes

Database Buffers 176160768 bytes

Redo Buffers 3956736 bytes

Database mounted.

Database opened.

Add variables (take the linux operating system as an example)

Export GG_HOME=/goldengate

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Here are some different operating systems, different names for the LIBRARY path environment variable:

IBM AIX LIBPATH

IBM z/OS LIBPATH

HP-UX SHLIB_PATH

Sun Solaris LD_LIBRARY_PATH

HP Tru64 (OSF/1) LD_LIBRARY_PATH

LINUX LD_LIBRARY_PATH

2.7 Editing GLOBALS parameter files

[oracle@prod goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (prod) 1 > edit params. / GLOBALS

GGSCHEMA goldengate

CheckpointTable goldengate.ckpt

UnlockedTrailFiles

2.8 stop all Session of the database

The DDL object of OGG cannot be installed with a running sessoin. Ask DBA to perform a shutdown cleanup or kill Session in all databases.

It is recommended that you stop the business and shut down the Listener of Oracle if you have conditions.

Note: it takes about 30 minutes, during which all the steps in Section 2.9 need to be completed. All the steps that follow can be performed when the business is running normally.

2.9 create DDL objects for OGG, query does not support data types, and tns configuration for ASM

[oracle@prod goldengate] $sqlplus'/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 6 14:29:24 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > @ marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:goldengate

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GOLDENGATE

MARKER TABLE

-

OK

MARKER SEQUENCE

-

OK

Script complete.

SQL > @ ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle 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 Oracle GoldenGate schema name:goldengate

Working, please wait...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables...

Check complete.

Using GOLDENGATE as an Oracle GoldenGate schema name.

Working, please wait...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GOLDENGATE

CLEAR_TRACE STATUS:

Line/pos Error

--

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

--

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

--

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

--

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

DDL IGNORE TABLE

-

OK

DDL IGNORE LOG TABLE

-

OK

DDLAUX PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

--

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

DDL HISTORY TABLE

-

OK

DDL HISTORY TABLE (1)

-

OK

DDL DUMP TABLES

-

OK

DDL DUMP COLUMNS

-

OK

DDL DUMP LOG GROUPS

-

OK

DDL DUMP PARTITIONS

-

OK

DDL DUMP PRIMARY KEYS

-

OK

DDL SEQUENCE

-

OK

GGS_TEMP_COLS

-

OK

GGS_TEMP_UK

-

OK

DDL TRIGGER CODE STATUS:

Line/pos Error

--

No errors No errors

DDL TRIGGER INSTALL STATUS

-

OK

DDL TRIGGER RUNNING STATUS

ENABLED

STAYMETADATA IN TRIGGER

OFF

DDL TRIGGER SQL TRACING

0

DDL TRIGGER TRACE LEVEL

0

LOCATION OF DDL TRACE FILE

-

/ u01/oracle/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

-

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL > @ role_setup.sql

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

Where is the user assigned to the GoldenGate processes.

SQL > GRANT GGS_GGSUSER_ROLE TO goldengate

Grant succeeded.

SQL > @ ddl_enable.sql

Trigger altered.

If the ddl_pin.sql script in 10g may report an error, run it

SQL > @? / rdbms/admin/dbmspool.sql

SQL > @ ddl_pin.sql goldengate

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

GGSCI (prod) 2 > dblogin userid goldengate, password goldengate

Successfully logged into database.

GGSCI (prod) 3 > add checkpointtable ckpt

Successfully created checkpoint table ckpt.

Unsupported column data types for queries:

Select *

From dba_tab_columns

Where data_type in ('ANYDATA',' ANYDATASET', 'ANYTYPE',' BFILE'

'BINARY_INTEGER', 'MLSLABEL',' ORDDICOM', 'PLS_INTEGER'

'TIMEZONE_ABBR',' URITYPE', 'UROWID')

And owner in ('YS','JTZC1','IUFO57','JTIUFO','JTCWBB','ZJCS','CESHI','JTJT','IUFO')

Column data types that are not supported by the query list:

Select *

From dba_tab_columns

Where table_name in

(select table_name

From (select owner, table_name, count (column_name))

From dba_tab_columns

Group by owner, table_name

Having count (column_name) = 1 and owner in

('YS','JTZC1','IUFO57','JTIUFO','JTCWBB','ZJCS','CESHI','JTJT','IUFO')

And data_type in ('CLOB','BLOB','NCLOB',' LONG','BFILE', 'Nested table',' User defineddata type'

'VARRAY',' XML')

GGSCI (prod) 4 > ADD TRANDATA scott.*

Note: if, due to business problems, enabling DDL has a great impact on performance, and you need to disable DDL triggers temporarily, you can run the following statement:

SQL > @ ddl_disable.sql

Business can be started at this time.

Set up TNS

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1-vip) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = node2-vip) (PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = + ASM)

(UR=A)

)

)

2.10 Edit MGR

GGSCI (prod) 1 > edit params mgr

Port 7809

Userid goldengate, password goldengate

DYNAMICPORTLIST 9101-9356

CheckMinutes 10

PurgeOldExtracts. / dirdat/*, UseCheckpoints, MinKeepHours 96

PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120

AutoRestart extract *, WaitMinutes 5, Retries 3

LagInfoMinutes 0

LagReportMinutes 10

2.11 add extraction / transfer processes and queue files

GGSCI (prod) 5 > add extract ext01, tranlog, begin now (, threads 2)-rac environment requires parentheses

EXTRACT added.

GGSCI (prod) 6 > add exttrail. / dirdat/me, extract ext01, megabytes 200

EXTTRAIL added.

GGSCI (prod) 7 > edit params ext01

Extract ext01

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

SETENV (ORACLE_SID= "ogg")

SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

-- TRANLOGOPTIONS EXCLUDEUSER

-- CacheMgr CacheDirectory. / dirtmp 51980MB, CacheDirectory. / dirtmp/goldengate_tmp

Userid goldengate, password goldengate

-- TranLogOptions ExcludeUser goldengate (used for two-way replication)

-- TranLogOptions AltArchivedLogFormat Instance NETDB1% t_%s_%r.dbf

-- TranLogOptions AltArchivedLogFormat Instance NETDB2% t_%s_%r.dbf

-- TranLogOptions AltArchiveLogDest Primary Instance NETDB1 / arch2, AltArchiveLogDest Instance NETDB2 / arch3

-- # tranlogoptions asmuser sys@ASM, ASMPASSWORD oracle (used by rac)

-- TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE [x], BUFSIZE [y]

ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000

Exttrail. / dirdat/me

DDL Include ALL

DDL INCLUDE ALL, EXCLUDE OBJNAME "."

DDLOptions AddTranData, Report

DDLOptions NoCrossRename, Report

Table scott.*

-- TABLEEXCLUDE yszx_zz_jinshuiqu.TMP* (it is strongly recommended to add an exclusion temporary table)

-- Prevent data looping. This is generally used in bi-directional

-- configuration

-- TRANLOGOPTIONS EXCLUDEUSER

GGSCI (prod) 8 > add extract pump01, EXTTRAILSOURCE. / dirdat/me

EXTRACT added.

GGSCI (prod) 9 > add rmttrail. / dirdat/mr, extract pump01, megabytes 200

RMTTRAIL added.

GGSCI (prod) 10 > edit params pump01

Extract pump01

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

SETENV (ORACLE_SID= "ogg")

Passthru

Rmthost 10.65.9.132, mgrport 7809

Rmttrail. / dirdat/mr

-- DISCARDFILE info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT01 00:00:00 00:42:40

EXTRACT STOPPED PUMP01 00:00:00 00:38:21

GGSCI (prod) 15 > start extract ext01

Sending START request to MANAGER...

EXTRACT EXT01 starting

GGSCI (prod) 16 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT01 00:42:50 00:00:00

EXTRACT STOPPED PUMP01 00:00:00 00:38:31

3.2 create a directory to perform data pump operations

SQL > create directory expdir as'/ u01'

Directory created.

SQL > grant read, write on directory expdir to public

Grant succeeded.

3.3.The main database gets the current scn

SQL > select dbms_flashback.get_system_change_number from dual

GET_SYSTEM_CHANGE_NUMBER

-

1085365

3.4 main database data export

Perform data export in the source OS system, modify the export user name and dmp file name, and ftp to the destination datapump directory after the export is completed.

[oracle@prod goldengate] $expdp system/oracle directory=expdir dumpfile=scott.dmp schemas=scott flashback_scn=1085365

4. Prepare the library configuration 4.1 ogg installation

[root@proddg ~] # mkdir / goldengate

[root@proddg ~] # chown oracle:dba / goldengate/

[root@proddg ~] # chmod 775 / goldengate/

[oracle@proddg goldengate] $tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@proddg goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (proddg) 1 > create subdirs

Creating subdirectories under current directory / goldengate

Parameter files / goldengate/dirprm: already exists

Report files / goldengate/dirrpt: created

Checkpoint files / goldengate/dirchk: created

Process status files / goldengate/dirpcs: created

SQL script files / goldengate/dirsql: created

Database definitions files / goldengate/dirdef: created

Extract data files / goldengate/dirdat: created

Temporary files / goldengate/dirtmp: created

Stdout files / goldengate/dirout: created

4.2 create a user running GoldenGate

SQL > create tablespace goldengate datafile'/ u01 size autoextend on maxsize autoextend on maxsize 10240m

Tablespace created.

SQL > create user goldengate identified by goldengate default tablespace goldengate

User created.

SQL > grant resource, connect, dba to goldengate

Grant succeeded.

4.3 Editing GLOBALS parameter files and checkpointtable, ddl

[oracle@prod goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (prod) 1 > edit params. / GLOBALS

GGSCHEMA goldengate

CheckpointTable goldengate.ckpt

UnlockedTrailFiles

SQL > @ marker_setup.sql

SQL > @ ddl_setup

GGSCI (proddg) 4 > dblogin userid goldengate, password goldengate

Successfully logged into database.

GGSCI (proddg) 5 > add checkpointtable ckpt

Successfully created checkpoint table ckpt.

4.4 Edit MGR

GGSCI (prod) 1 > edit params mgr

Port 7809

Userid goldengate, password goldengate

DYNAMICPORTLIST 9101-9356

CheckMinutes 10

PurgeOldExtracts. / dirdat/*, UseCheckpoints, MinKeepHours 96

PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120

-- AutoRestart replicat *, WaitMinutes 5, Retries 3

LagInfoMinutes 0

LagReportMinutes 10

4.5 add a receiving process

GGSCI (proddg) 1 > add replicat rep01, exttrail. / dirdat/mr

REPLICAT added.

GGSCI (proddg) 2 > edit params rep01

Replicat rep01

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

SETENV (ORACLE_SID= "ogg")

SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

Userid goldengate, password goldengate

-- HandleCollisions

AssumeTargetDefs

DiscardFile. / dirrpt/rep1.dsc, APPEND Megabytes 800, Purge

DBOptions DeferrefConst

DBOptions SuppressTriggers (not supported by 11.2.0.4)

MaxTransOps 10000

GroupTransOps 1000

SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"

BatchSQL

DDL INCLUDE ALL, EXCLUDE OBJNAME "."

DDLOptions Report

DDLError 24344 Ignore

DDLError 4052 Ignore

DDLError 955 Ignore

DDLError 1408 Ignore

DDLError 911 Ignore

-- DDLError 907 Ignore

-- DDLError 942 Ignore

AllowNoOpUpdates

CheckSequenceValue

-- IGNORETRUNCATES

-- DEFERAPPLYINTERVAL 1 MINUTES

-- Sequence testgg.*, Target testgg.*

MapExclude scott.SYS_EXPORT_SCHEMA*

Map scott.*, target scott.*

4.6 create directories and import data

SQL > create directory expdir as'/ u01'

Directory created.

SQL > grant read, write on directory expdir to public

Grant succeeded.

[oracle@proddg U01] $impdp system/oracle directory=expdir dumpfile=scott.dmp

4.7 modify database 4.7.1 disable triggers

Declare

V_sql varchar2 (2000)

CURSOR c_trigger IS SELECT 'alter trigger' | | owner | |'. | | trigger_name | | 'disable' from dba_triggers where owner in (' EMP_HLJ','EPC_HLJ','EPSA_HLJ','FLOW_HLJ','EPM_DE_HLJ','XEPMA_HLJ','EPM_LOADC_HLJ','EPM_HIS_HL','EPM_RPT_HLJ','SYSODM','EPM_CP','EPM_BANK_HLJ','EPM_IC_HLJ','EPM_SEC') |

BEGIN

OPEN c_trigger

LOOP

FETCH c_trigger INTO v_sql

EXIT WHEN c_trigger%NOTFOUND

Execute immediate v_sql

End loop

Close c_trigger

End

/

4.7.2 disable foreign keys

Declare

V_sql varchar2 (2000)

CURSOR c_trigger IS SELECT 'alter table' | | owner | |'. | | table_name | | 'disable constraint' | | constraint_name from dba_constraints where constraint_type='R' and owner in ('EMP_HLJ','EPC_HLJ','EPSA_HLJ','FLOW_HLJ','EPM_DE_HLJ','XEPMA_HLJ','EPM_LOADC_HLJ','EPM_HIS_HL','EPM_RPT_HLJ','SYSODM','EPM_CP','EPM_BANK_HLJ','EPM_IC_HLJ' 'EPM_SEC')

BEGIN

OPEN c_trigger

LOOP

FETCH c_trigger INTO v_sql

EXIT WHEN c_trigger%NOTFOUND

Execute immediate v_sql

End loop

Close c_trigger

End

/

Other:

Disable JOB with DML operation

1) in dba_jobs, some job/schedule will generate DML operations in the replication object, which must be disabled, otherwise it will cause repeated execution of the DML statement on the target side.

2) some job in dba_schedule_jobs will generate DML operations in the replication object, which must be disabled, otherwise it will cause repeated execution of the DML statement on the target side.

Note: when restoring on the destination side, you can set job_queue_processes to 0 in the pfile file, then stop the job with dml, and then modify the job_queue_processes parameter to the normal value, such as job_queue_processes=10

5. Start the main library transfer process and standby library mgr, receiving process

Main library:

GGSCI (prod) 6 > start extract pump01

Sending START request to MANAGER...

EXTRACT PUMP01 starting

GGSCI (prod) 7 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT01 00:00:00 00:00:04

EXTRACT RUNNING PUMP01 00:00:00 01:14:59

Prepare the library:

GGSCI (proddg) 10 > start mgr

Manager started.

GGSCI (proddg) 3 > start replicat rep01,aftercsn 1085365

The SCN here is the SCN of the previous expdp export

Sending START request to MANAGER...

REPLICAT REP01 starting

GGSCI (proddg) 2 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP01 00:00:00 00:00:06

Catalogue

2. Configuration of the main library. PAGEREF _ Toc377733475\ h 2

2.1 set the database archive mode. PAGEREF _ Toc377733476\ h 2

2.2 Open the database attached log. PAGEREF _ Toc377733477\ h 3

2.3 turn on database mandatory log mode. PAGEREF _ Toc377733478\ h 4

2.4 create a user running GoldenGate... PAGEREF _ Toc377733479\ h 4

2.5 turn off the recyclebin of the database (configure only when DDL is implemented). PAGEREF _ Toc377733480\ h 5

Add variables (take the linux operating system as an example). PAGEREF _ Toc377733481\ h 5

Edit the GLOBALS parameter file. PAGEREF _ Toc377733482\ h 6

Stop all Session of the database. PAGEREF _ Toc377733483\ h 6

2.9.Create the DDL object of OGG. PAGEREF _ Toc377733484\ h 6

2.10 Edit MGR. PAGEREF _ Toc377733485\ h 14

2.11 add extraction / transfer processes and queue files. PAGEREF _ Toc377733486\ h 14

3. Oracle EXPDP/IMPDP initialization scheme. PAGEREF _ Toc377733487\ h 16

3.1 start the main library mgr and extract processes. PAGEREF _ Toc377733488\ h 16

Create a directory to perform data pump operations. PAGEREF _ Toc377733489\ h 17

3.3.The main library gets the current scn. PAGEREF _ Toc377733490\ h 17

3.4 main database data export. PAGEREF _ Toc377733491\ h 17

4. Prepare the library configuration. PAGEREF _ Toc377733492\ h 17

4.1 ogg installation. PAGEREF _ Toc377733493\ h 17

4.2 create a user running GoldenGate... PAGEREF _ Toc377733494\ h 18

Edit GLOBALS parameter files and checkpointtable, ddl. PAGEREF _ Toc377733495\ h 19

4.4.Editing MGR. PAGEREF _ Toc377733496\ h 19

4.5 add a receiving process. PAGEREF _ Toc377733497\ h 20

4.6 create a directory and import data. PAGEREF _ Toc377733498\ h 21

4.7 modify the database. PAGEREF _ Toc377733499\ h 21

4.7.1 disable triggers. PAGEREF _ Toc377733500\ h 21

4.7.2 disable foreign keys. PAGEREF _ Toc377733501\ h 21

5, start the main library transfer process and standby library mgr, receive process. PAGEREF _ Toc377733502\ h 22

1. Install the main library OGG

Note: database version 10.2 and 11.2.0.3 can be used goldengate11.2 version, database version 11.2.0.4 and 12C, need to use goldengate12.1 version, otherwise ddl_setup.sql footer error, modify database goldengate related parameters, show parameter goldengate view, change to true (both master and standby databases need to be modified).

SQL > ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH

System altered.

SQL > show parameter goldengate

NAME TYPE VALUE

-

Enable_goldengate_replication boolean TRUE

[root@prod ~] # mkdir / goldengate

[root@prod ~] # chown oracle:dba / goldengate/

[root@prod ~] # chmod 775 / goldengate/

[oracle@node1 ~] $cat .bash _ profile

ORACLE_BASE=/home/oracle/app/oracle

ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1

ORACLE_SID=test

PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:$HOME/bin

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;export LD_LIBRARY_PATH

[oracle@prod goldengate] $tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@prod goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (prod) 1 > create subdirs

Creating subdirectories under current directory / goldengate

Parameter files / goldengate/dirprm: already exists

Report files / goldengate/dirrpt: created

Checkpoint files / goldengate/dirchk: created

Process status files / goldengate/dirpcs: created

SQL script files / goldengate/dirsql: created

Database definitions files / goldengate/dirdef: created

Extract data files / goldengate/dirdat: created

Temporary files / goldengate/dirtmp: created

Stdout files / goldengate/dirout: created

2. The main database configuration 2.1 sets the database archiving mode

SQL > Select log_mode from v$database

LOG_MODE

-

NOARCHIVELOG

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 488534016 bytes

Fixed Size 2229624 bytes

Variable Size 301992584 bytes

Database Buffers 180355072 bytes

Redo Buffers 3956736 bytes

Database mounted.

SQL > alter database archivelog

Database altered.

SQL > alter database open

Database altered.

2.2 Open the database attached log

Use the following sql statement to check the opening of the database attached log:

SQL > Select

2 SUPPLEMENTAL_LOG_DATA_MIN

3, SUPPLEMENTAL_LOG_DATA_PK

4, SUPPLEMENTAL_LOG_DATA_UI

5, SUPPLEMENTAL_LOG_DATA_FK

6, SUPPLEMENTAL_LOG_DATA_ALL from v$database

SUPPLEME SUP SUP SUP SUP

NO NO NO NO NO

Open additional logs and switch logs (make sure Online redo log and Archive log are consistent)

SQL > alter database add supplemental log data

Database altered.

SQL > alter database add supplemental log data (primary key, unique,foreign key) columns

Database altered.

SQL > alter system switch logfile

System altered.

Fallback operation: if there is a problem, you can fallback with the following statement:

Alter database drop supplemental log data

Alter database drop supplemental log data (primary key, unique,foreign key) columns

Use the following sql statement to check the opening of the database attached log:

SQL > Select

2 SUPPLEMENTAL_LOG_DATA_MIN

3, SUPPLEMENTAL_LOG_DATA_PK

4, SUPPLEMENTAL_LOG_DATA_UI

5, SUPPLEMENTAL_LOG_DATA_FK

6, SUPPLEMENTAL_LOG_DATA_ALL from v$database

SUPPLEME SUP SUP SUP SUP

YES YES YES YES NO

Note: make sure the minimum additional log is opened and the pk,uk,fk additional log is turned on. The additional log of all columns is closed.

If the additional log for all columns is turned on, you need to turn it off using the following statement:

Alter database drop supplemental log data (ALL) columns

2.3 enable database mandatory log mode

SQL > Alter database force logging

Database altered.

Note: the opening of this mode requires relevant confirmation and discussion with the business unit; if the database cannot be opened to force logging mode, the no logging table cannot be synchronized.

If you need to fallback and turn off forced logging mode, use the following sql:

Alter database no force logging

2.4 create a user running GoldenGate

SQL > create tablespace goldengate datafile'/ u01 size autoextend on maxsize autoextend on maxsize 10240m

Tablespace created.

SQL > create user goldengate identified by goldengate default tablespace goldengate

User created.

SQL > grant resource, connect, dba to goldengate

Grant succeeded.

2.5 turn off the recyclebin of the database (configured only when DDL is implemented)

Query the parameter values of the current recyclebin (10g needs to be disabled, 11g does not):

SQL > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string on

SQL > alter system set recyclebin=off scope=spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 488534016 bytes

Fixed Size 2229624 bytes

Variable Size 306186888 bytes

Database Buffers 176160768 bytes

Redo Buffers 3956736 bytes

Database mounted.

Database opened.

Add variables (take the linux operating system as an example)

Export GG_HOME=/goldengate

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Here are some different operating systems, different names for the LIBRARY path environment variable:

IBM AIX LIBPATH

IBM z/OS LIBPATH

HP-UX SHLIB_PATH

Sun Solaris LD_LIBRARY_PATH

HP Tru64 (OSF/1) LD_LIBRARY_PATH

LINUX LD_LIBRARY_PATH

2.7 Editing GLOBALS parameter files

[oracle@prod goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (prod) 1 > edit params. / GLOBALS

GGSCHEMA goldengate

CheckpointTable goldengate.ckpt

UnlockedTrailFiles

2.8 stop all Session of the database

The DDL object of OGG cannot be installed with a running sessoin. Ask DBA to perform a shutdown cleanup or kill Session in all databases.

It is recommended that you stop the business and shut down the Listener of Oracle if you have conditions.

Note: it takes about 30 minutes, during which all the steps in Section 2.9 need to be completed. All the steps that follow can be performed when the business is running normally.

2.9 create DDL objects for OGG, query does not support data types, and tns configuration for ASM

[oracle@prod goldengate] $sqlplus'/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 6 14:29:24 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > @ marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:goldengate

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GOLDENGATE

MARKER TABLE

-

OK

MARKER SEQUENCE

-

OK

Script complete.

SQL > @ ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle 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 Oracle GoldenGate schema name:goldengate

Working, please wait...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables...

Check complete.

Using GOLDENGATE as an Oracle GoldenGate schema name.

Working, please wait...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GOLDENGATE

CLEAR_TRACE STATUS:

Line/pos Error

--

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

--

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

--

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

--

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

DDL IGNORE TABLE

-

OK

DDL IGNORE LOG TABLE

-

OK

DDLAUX PACKAGE STATUS:

Line/pos Error

--

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

--

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

--

No errors No errors

DDL HISTORY TABLE

-

OK

DDL HISTORY TABLE (1)

-

OK

DDL DUMP TABLES

-

OK

DDL DUMP COLUMNS

-

OK

DDL DUMP LOG GROUPS

-

OK

DDL DUMP PARTITIONS

-

OK

DDL DUMP PRIMARY KEYS

-

OK

DDL SEQUENCE

-

OK

GGS_TEMP_COLS

-

OK

GGS_TEMP_UK

-

OK

DDL TRIGGER CODE STATUS:

Line/pos Error

--

No errors No errors

DDL TRIGGER INSTALL STATUS

-

OK

DDL TRIGGER RUNNING STATUS

ENABLED

STAYMETADATA IN TRIGGER

OFF

DDL TRIGGER SQL TRACING

0

DDL TRIGGER TRACE LEVEL

0

LOCATION OF DDL TRACE FILE

-

/ u01/oracle/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

-

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL > @ role_setup.sql

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

Where is the user assigned to the GoldenGate processes.

SQL > GRANT GGS_GGSUSER_ROLE TO goldengate

Grant succeeded.

SQL > @ ddl_enable.sql

Trigger altered.

If the ddl_pin.sql script in 10g may report an error, run it

SQL > @? / rdbms/admin/dbmspool.sql

SQL > @ ddl_pin.sql goldengate

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

GGSCI (prod) 2 > dblogin userid goldengate, password goldengate

Successfully logged into database.

GGSCI (prod) 3 > add checkpointtable ckpt

Successfully created checkpoint table ckpt.

Unsupported column data types for queries:

Select *

From dba_tab_columns

Where data_type in ('ANYDATA',' ANYDATASET', 'ANYTYPE',' BFILE'

'BINARY_INTEGER', 'MLSLABEL',' ORDDICOM', 'PLS_INTEGER'

'TIMEZONE_ABBR',' URITYPE', 'UROWID')

And owner in ('YS','JTZC1','IUFO57','JTIUFO','JTCWBB','ZJCS','CESHI','JTJT','IUFO')

Column data types that are not supported by the query list:

Select *

From dba_tab_columns

Where table_name in

(select table_name

From (select owner, table_name, count (column_name))

From dba_tab_columns

Group by owner, table_name

Having count (column_name) = 1 and owner in

('YS','JTZC1','IUFO57','JTIUFO','JTCWBB','ZJCS','CESHI','JTJT','IUFO')

And data_type in ('CLOB','BLOB','NCLOB',' LONG','BFILE', 'Nested table',' User defineddata type'

'VARRAY',' XML')

GGSCI (prod) 4 > ADD TRANDATA scott.*

Note: if, due to business problems, enabling DDL has a great impact on performance, and you need to disable DDL triggers temporarily, you can run the following statement:

SQL > @ ddl_disable.sql

Business can be started at this time.

Set up TNS

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1-vip) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = node2-vip) (PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = + ASM)

(UR=A)

)

)

2.10 Edit MGR

GGSCI (prod) 1 > edit params mgr

Port 7809

Userid goldengate, password goldengate

DYNAMICPORTLIST 9101-9356

CheckMinutes 10

PurgeOldExtracts. / dirdat/*, UseCheckpoints, MinKeepHours 96

PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120

AutoRestart extract *, WaitMinutes 5, Retries 3

LagInfoMinutes 0

LagReportMinutes 10

2.11 add extraction / transfer processes and queue files

GGSCI (prod) 5 > add extract ext01, tranlog, begin now (, threads 2)-rac environment requires parentheses

EXTRACT added.

GGSCI (prod) 6 > add exttrail. / dirdat/me, extract ext01, megabytes 200

EXTTRAIL added.

GGSCI (prod) 7 > edit params ext01

Extract ext01

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

SETENV (ORACLE_SID= "ogg")

SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

-- TRANLOGOPTIONS EXCLUDEUSER

-- CacheMgr CacheDirectory. / dirtmp 51980MB, CacheDirectory. / dirtmp/goldengate_tmp

Userid goldengate, password goldengate

-- TranLogOptions ExcludeUser goldengate (used for two-way replication)

-- TranLogOptions AltArchivedLogFormat Instance NETDB1% t_%s_%r.dbf

-- TranLogOptions AltArchivedLogFormat Instance NETDB2% t_%s_%r.dbf

-- TranLogOptions AltArchiveLogDest Primary Instance NETDB1 / arch2, AltArchiveLogDest Instance NETDB2 / arch3

-- # tranlogoptions asmuser sys@ASM, ASMPASSWORD oracle (used by rac)

-- TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE [x], BUFSIZE [y]

ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000

Exttrail. / dirdat/me

DDL Include ALL

DDL INCLUDE ALL, EXCLUDE OBJNAME "."

DDLOptions AddTranData, Report

DDLOptions NoCrossRename, Report

Table scott.*

-- TABLEEXCLUDE yszx_zz_jinshuiqu.TMP* (it is strongly recommended to add an exclusion temporary table)

-- Prevent data looping. This is generally used in bi-directional

-- configuration

-- TRANLOGOPTIONS EXCLUDEUSER

GGSCI (prod) 8 > add extract pump01, EXTTRAILSOURCE. / dirdat/me

EXTRACT added.

GGSCI (prod) 9 > add rmttrail. / dirdat/mr, extract pump01, megabytes 200

RMTTRAIL added.

GGSCI (prod) 10 > edit params pump01

Extract pump01

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

SETENV (ORACLE_SID= "ogg")

Passthru

Rmthost 10.65.9.132, mgrport 7809

Rmttrail. / dirdat/mr

-- DISCARDFILE info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT01 00:00:00 00:42:40

EXTRACT STOPPED PUMP01 00:00:00 00:38:21

GGSCI (prod) 15 > start extract ext01

Sending START request to MANAGER...

EXTRACT EXT01 starting

GGSCI (prod) 16 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT01 00:42:50 00:00:00

EXTRACT STOPPED PUMP01 00:00:00 00:38:31

3.2 create a directory to perform data pump operations

SQL > create directory expdir as'/ u01'

Directory created.

SQL > grant read, write on directory expdir to public

Grant succeeded.

3.3.The main database gets the current scn

SQL > select dbms_flashback.get_system_change_number from dual

GET_SYSTEM_CHANGE_NUMBER

-

1085365

3.4 main database data export

Perform data export in the source OS system, modify the export user name and dmp file name, and ftp to the destination datapump directory after the export is completed.

[oracle@prod goldengate] $expdp system/oracle directory=expdir dumpfile=scott.dmp schemas=scott flashback_scn=1085365

4. Prepare the library configuration 4.1 ogg installation

[root@proddg ~] # mkdir / goldengate

[root@proddg ~] # chown oracle:dba / goldengate/

[root@proddg ~] # chmod 775 / goldengate/

[oracle@proddg goldengate] $tar-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@proddg goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (proddg) 1 > create subdirs

Creating subdirectories under current directory / goldengate

Parameter files / goldengate/dirprm: already exists

Report files / goldengate/dirrpt: created

Checkpoint files / goldengate/dirchk: created

Process status files / goldengate/dirpcs: created

SQL script files / goldengate/dirsql: created

Database definitions files / goldengate/dirdef: created

Extract data files / goldengate/dirdat: created

Temporary files / goldengate/dirtmp: created

Stdout files / goldengate/dirout: created

4.2 create a user running GoldenGate

SQL > create tablespace goldengate datafile'/ u01 size autoextend on maxsize autoextend on maxsize 10240m

Tablespace created.

SQL > create user goldengate identified by goldengate default tablespace goldengate

User created.

SQL > grant resource, connect, dba to goldengate

Grant succeeded.

4.3 Editing GLOBALS parameter files and checkpointtable, ddl

[oracle@prod goldengate] $. / ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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

GGSCI (prod) 1 > edit params. / GLOBALS

GGSCHEMA goldengate

CheckpointTable goldengate.ckpt

UnlockedTrailFiles

SQL > @ marker_setup.sql

SQL > @ ddl_setup

GGSCI (proddg) 4 > dblogin userid goldengate, password goldengate

Successfully logged into database.

GGSCI (proddg) 5 > add checkpointtable ckpt

Successfully created checkpoint table ckpt.

4.4 Edit MGR

GGSCI (prod) 1 > edit params mgr

Port 7809

Userid goldengate, password goldengate

DYNAMICPORTLIST 9101-9356

CheckMinutes 10

PurgeOldExtracts. / dirdat/*, UseCheckpoints, MinKeepHours 96

PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120

-- AutoRestart replicat *, WaitMinutes 5, Retries 3

LagInfoMinutes 0

LagReportMinutes 10

4.5 add a receiving process

GGSCI (proddg) 1 > add replicat rep01, exttrail. / dirdat/mr

REPLICAT added.

GGSCI (proddg) 2 > edit params rep01

Replicat rep01

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

SETENV (ORACLE_SID= "ogg")

SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")

Userid goldengate, password goldengate

-- HandleCollisions

AssumeTargetDefs

DiscardFile. / dirrpt/rep1.dsc, APPEND Megabytes 800, Purge

DBOptions DeferrefConst

DBOptions SuppressTriggers (not supported by 11.2.0.4)

MaxTransOps 10000

GroupTransOps 1000

SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"

BatchSQL

DDL INCLUDE ALL, EXCLUDE OBJNAME "."

DDLOptions Report

DDLError 24344 Ignore

DDLError 4052 Ignore

DDLError 955 Ignore

DDLError 1408 Ignore

DDLError 911 Ignore

-- DDLError 907 Ignore

-- DDLError 942 Ignore

AllowNoOpUpdates

CheckSequenceValue

-- IGNORETRUNCATES

-- DEFERAPPLYINTERVAL 1 MINUTES

-- Sequence testgg.*, Target testgg.*

MapExclude scott.SYS_EXPORT_SCHEMA*

Map scott.*, target scott.*

4.6 create directories and import data

SQL > create directory expdir as'/ u01'

Directory created.

SQL > grant read, write on directory expdir to public

Grant succeeded.

[oracle@proddg U01] $impdp system/oracle directory=expdir dumpfile=scott.dmp

4.7 modify database 4.7.1 disable triggers

Declare

V_sql varchar2 (2000)

CURSOR c_trigger IS SELECT 'alter trigger' | | owner | |'. | | trigger_name | | 'disable' from dba_triggers where owner in (' EMP_HLJ','EPC_HLJ','EPSA_HLJ','FLOW_HLJ','EPM_DE_HLJ','XEPMA_HLJ','EPM_LOADC_HLJ','EPM_HIS_HL','EPM_RPT_HLJ','SYSODM','EPM_CP','EPM_BANK_HLJ','EPM_IC_HLJ','EPM_SEC') |

BEGIN

OPEN c_trigger

LOOP

FETCH c_trigger INTO v_sql

EXIT WHEN c_trigger%NOTFOUND

Execute immediate v_sql

End loop

Close c_trigger

End

/

4.7.2 disable foreign keys

Declare

V_sql varchar2 (2000)

CURSOR c_trigger IS SELECT 'alter table' | | owner | |'. | | table_name | | 'disable constraint' | | constraint_name from dba_constraints where constraint_type='R' and owner in ('EMP_HLJ','EPC_HLJ','EPSA_HLJ','FLOW_HLJ','EPM_DE_HLJ','XEPMA_HLJ','EPM_LOADC_HLJ','EPM_HIS_HL','EPM_RPT_HLJ','SYSODM','EPM_CP','EPM_BANK_HLJ','EPM_IC_HLJ' 'EPM_SEC')

BEGIN

OPEN c_trigger

LOOP

FETCH c_trigger INTO v_sql

EXIT WHEN c_trigger%NOTFOUND

Execute immediate v_sql

End loop

Close c_trigger

End

/

Other:

Disable JOB with DML operation

1) in dba_jobs, some job/schedule will generate DML operations in the replication object, which must be disabled, otherwise it will cause repeated execution of the DML statement on the target side.

2) some job in dba_schedule_jobs will generate DML operations in the replication object, which must be disabled, otherwise it will cause repeated execution of the DML statement on the target side.

Note: when restoring on the destination side, you can set job_queue_processes to 0 in the pfile file, then stop the job with dml, and then modify the job_queue_processes parameter to the normal value, such as job_queue_processes=10

5. Start the main library transfer process and standby library mgr, receiving process

Main library:

GGSCI (prod) 6 > start extract pump01

Sending START request to MANAGER...

EXTRACT PUMP01 starting

GGSCI (prod) 7 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT01 00:00:00 00:00:04

EXTRACT RUNNING PUMP01 00:00:00 01:14:59

Prepare the library:

GGSCI (proddg) 10 > start mgr

Manager started.

GGSCI (proddg) 3 > start replicat rep01,aftercsn 1085365

The SCN here is the SCN of the previous expdp export

Sending START request to MANAGER...

REPLICAT REP01 starting

GGSCI (proddg) 2 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP01 00:00:00 00:00:06

These are all the contents of the article "how to install OGG". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.