配置部署Oracle Goldengate Version 11.2.1.0.1
站在用戶的角度思考問題，與客戶深入溝通，找到興城網(wǎng)站設(shè)計(jì)與興城網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名注冊、虛擬空間、企業(yè)郵箱。業(yè)務(wù)覆蓋興城地區(qū)。
配置用戶環(huán)境變量
vi .bash_profile
添加：
export OGG_HOME=/data0/temp/ogg
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
查看數(shù)據(jù)是否開啟歸檔
SQL> archive log list； --Enabled
Configuring logging properties
Oracle GoldenGate relies on the redo logs to capture the data and metadata that it
needs to replicate source transactions. The Oracle redo logs on the source system must
be configured properly before you start Oracle GoldenGate processing. Because redo
volume is increased as the result of this required logging, you might want to wait until
just before you start Oracle GoldenGate processing to enable the logging.
This section addresses the following logging levels:
■ Enabling database-level supplemental logging
■ Enabling schema-level supplemental logging
■ Enabling table-level supplemental logging
查看數(shù)據(jù)庫日志打開情況
SQL> Select SUPPLEMENTAL_LOG_DATA_MIN,
2 SUPPLEMENTAL_LOG_DATA_PK,
3 SUPPLEMENTAL_LOG_DATA_UI,
4 SUPPLEMENTAL_LOG_DATA_FK,
5 SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
NO NO NO NO NO
打開附加日志并切換日志（保證Online redo log和Archive log一致）
alter database add supplemental log data ;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
再次檢查日志打開情況：
SQL> Select SUPPLEMENTAL_LOG_DATA_MIN,
2 SUPPLEMENTAL_LOG_DATA_PK,
3 SUPPLEMENTAL_LOG_DATA_UI,
4 SUPPLEMENTAL_LOG_DATA_FK,
SUPPLEMENTAL_LOG_DATA_ALL from v$database;
5
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES YES YES NO
注：確保最小附加日志,pk,uk,fk附加日志打開。而all columns的附加日志關(guān)閉；
如果all columns的附加日志打開的話，則需要使用以下語句予以關(guān)閉：
alter database drop supplemental log data (ALL) columns;
如果出現(xiàn)問題，可以通過以下語句進(jìn)行回退：
alter database drop supplemental log data ;
alter database drop supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
數(shù)據(jù)庫開啟強(qiáng)日志模式：
SQL>alter database force logging;
--注：這里source 庫已是dataguard primary DB 所以強(qiáng)制日志模式已經(jīng)打開：
查看：
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
SQL>
關(guān)閉recyclebin
--注在system一級,不加DEFERRED參數(shù)是不允許修改的,
SQL> alter system set recyclebin=off DEFERRED;
System altered.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL>
退出，重新登錄查看
[oracle@dkdb_primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 13 18:50:42 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL>
安裝goldengate在source端和target端上安裝
[oracle@dkdb_primary ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (dkdb_primary) 1> CREATE SUBDIRS
Creating subdirectories under current directory /data0/temp/ogg
Parameter files /data0/temp/ogg/dirprm: already exists
Report files /data0/temp/ogg/dirrpt: created
Checkpoint files /data0/temp/ogg/dirchk: created
Process status files /data0/temp/ogg/dirpcs: created
SQL script files /data0/temp/ogg/dirsql: created
Database definitions files /data0/temp/ogg/dirdef: created
Extract data files /data0/temp/ogg/dirdat: created
Temporary files /data0/temp/ogg/dirtmp: created
Stdout files /data0/temp/ogg/dirout: created
GGSCI (dkdb_primary) 2>
GGSCI (dkdb_primary) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
安裝oracle sequence 支持
這里的用戶和創(chuàng)建DDL同步所需用戶一樣。（登陸源端和目標(biāo)端執(zhí)行創(chuàng)建用戶）：
SQL> create tablespace urogg datafile '/opt/oracle/database/oradata/dkhlstd/dkhlstd/urogg01.dbf' size 100M autoextend on NOLOGGING;
Tablespace created.
SQL> create user urogg identified by test_oracle1_P default tablespace urogg temporary tablespace temp;
User created.
賦權(quán)：
GRANT CONNECT, RESOURCE, DBA TO urogg;
GRANT CREATE TABLE,CREATE SEQUENCE TO urogg;--這里是用于用戶安裝DDL執(zhí)行SQL腳本時(shí)用；
編輯GLOBALS 參數(shù)
進(jìn)入./ggsci在所有節(jié)點(diǎn)執(zhí)行
EDIT PARAMS ./GLOBALS 并添加
GGSCHEMA urogg --指定的進(jìn)行DDL復(fù)制的數(shù)據(jù)庫用戶
保存退出，并進(jìn)入$OGG_HOME 登陸sqlplus / as sysdba 執(zhí)行腳本
SQL> @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
urogg
Setting schema name to UROGG
source 端：
SQL> GRANT EXECUTE on urogg.updateSequence TO urogg;
Grant succeeded.
SQL> ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Table altered.
SQL>
target端：
SQL> GRANT EXECUTE on urogg.replicateSequence TO urogg;
Grant succeeded.
編輯target 端 GLOBALS
GGSCI (dkdb_ogg) 10> edit params ./GLOBALS
--GGSCHEMA urogg
CHECKPOINTTABLE urogg.checktable --添加checkpointtable
添加checkpointtable
GGSCI (dkdb_ogg) 16> ADD CHECKPOINTTABLE DKOGG.CHECKTABLE
Successfully created checkpoint table urogg.checktable.
添加表級附加日志：
GGSCI (dkdb_primary) 1> dblogin userid urogg,password test_oracle1_P
Successfully logged into database.
GGSCI (dkdb_primary) 5> show user
Parameter settings:
SET SUBDIRS ON
SET DEBUG OFF
Current directory: /data0/temp/ogg
Using subdirectories for all process files
Editor: vi
Reports (.rpt) /data0/temp/ogg/dirrpt
Parameters (.prm) /data0/temp/ogg/dirprm
Stdout (.out) /data0/temp/ogg/dirout
Replicat Checkpoints (.cpr) /data0/temp/ogg/dirchk
Extract Checkpoints (.cpe) /data0/temp/ogg/dirchk
Process Status (.pcs) /data0/temp/ogg/dirpcs
SQL Scripts (.sql) /data0/temp/ogg/dirsql
Database Definitions (.def) /data0/temp/ogg/dirdef
GGSCI (dkdb_primary) 3> add trandata TEST_D.*
這里會(huì)出現(xiàn)警告：
WARNING OGG-00869 、WARNING OGG-01387
2015-05-11 20:09:36 WARNING OGG-00869 No unique key is defined for table 'T_APPDETAIL'.
All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
檢查確認(rèn)：
GGSCI (dkdb_primary) 4> info trandata TEST_D.*
Logging of supplemental redo log data is enabled for table TEST_D.API_SWITCH.
Columns supplementally logged for table TEST_D.API_SWITCH: API_NAME, ISVALID, API_COMMENT, API_ID.
注：add trandata TEST_D.* 后面沒有;號
否則報(bào)錯(cuò)如下：
GGSCI (dkdb_primary) 2> add trandata test_desk.*;
ERROR: No viable tables matched specification.
創(chuàng)建 manager 進(jìn)程：
GGSCI (dkdb_primary) 4> edit param mgr
PORT 7801
DYNAMICPORTLIST 7802-7820
PURGEOLDEXTRACTS /opt/ogg/dirdat/ *, USECHECKPOINTS, MINKEEPDAYS 7
--配置DDL參數(shù)區(qū)域
--trail file 保留7天
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10,FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
--配置一般日志信息區(qū)域
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
--##注 ./dirdat/*
-- AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7 自動(dòng)啟動(dòng)extract進(jìn)程
GGSCI (dkdb_primary) 5>
啟動(dòng)mgr進(jìn)程
GGSCI (dkdb_primary) 5> start mgr
Manager started.
GGSCI (dkdb_primary) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (dkdb_primary) 7>
配置source 端 extract進(jìn)程
1、獲取加密
GGSCI (dkdb_primary) 56> encrypt password test_oracle1_P, ENCRYPTKEY default
Using default key...
Encrypted password: AACAAAAAAAAAAANAOHOJFBNCCHOJHEGIIARCPDNETBLFDEWF
Algorithm used: BLOWFISH
測試：
GGSCI (dkdb_primary) 57> dblogin userid urogg,password AACAAAAAAAAAAANAOHOJFBNCCHOJHEGIIARCPDNETBLFDEWF, ENCRYPTKEY default
Successfully logged into database.
2、查詢是否有事務(wù)等待
select count(*) from gv$transaction;
3、抽取(extract)進(jìn)程參數(shù)配置
GGSCI (dkdb_primary) 5> edit param extdr
--基礎(chǔ)參數(shù)區(qū)域
EXTRACT extdr
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID urogg,PASSWORD AACAAAAAAAAAAANAOHOJFBNCCHOJHEGIIARCPDNETBLFDEWF, ENCRYPTKEY default
REPORTCOUNT EVERY 30 MINUTES,RATE
DISCARDFILE ./dirrpt/extdr.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 3:00
WARNLONGTRANS 2h, CHECKINTERVAL 3m
EXTTRAIL ./dirdat/sa
DYNAMICRESOLUTION
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TRANLOGOPTIONS EXCLUDEUSER urogg
--TRANLOGOPTIONS altarchivelogdest instance dkhlstd /opt/oracle/database/fast_recovery_area
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
--DDL參數(shù)區(qū)域，所有復(fù)制對象，屏蔽對象在源端體現(xiàn)。
DDL &
INCLUDE MAPPED OBJTYPE 'TABLE' &
INCLUDE MAPPED OBJTYPE 'INDEX' &
INCLUDE MAPPED OBJTYPE 'SEQUENCE' &
INCLUDE MAPPED OBJTYPE 'VIEW' &
INCLUDE MAPPED OBJTYPE 'PROCEDURE' &
INCLUDE MAPPED OBJTYPE 'FUNCTION' &
INCLUDE MAPPED OBJTYPE 'PACKAGE' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS addtrandata REPORT
--添加對象
TABLE TEST_D.*;
SEQUENCE TEST_D.*;
配置 source端 data pump 進(jìn)程：
These steps configure the data pump that reads the local trail and sends the data
across the network to a remote trail.
GGSCI (dkdb_primary) 8> edit param extpump
--基礎(chǔ)參數(shù)區(qū)域
EXTRACT extpump
RMTHOST 172.16.1.246, MGRPORT 7801, COMPRESS
PASSTHRU
RMTTRAIL ./dirdat/sa
DYNAMICRESOLUTION
--對象區(qū)域
TABLE TEST_D.*;
SEQUENCE TEST_D.*;
配置DDL objects
給urogg用戶賦權(quán)：
GRANT EXECUTE ON utl_file TO urogg;
進(jìn)入OGG_HOME，sqlplus / as sysdba
執(zhí)行腳本并輸入ogg用戶urogg：
@marker_setup.sql
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:urogg
@ddl_setup.sql
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:urogg
@role_setup.sql
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:urogg
GRANT GGS_GGSUSER_ROLE TO urogg;
--SQL> GRANT GGS_GGSUSER_ROLE TO urogg;
--GRANT GGS_GGSUSER_ROLE TO urogg
--*
--ERROR at line 1:
--ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
開啟DDL
ddl_enable.sql
SQL> @ddl_enable.sql
Trigger altered.
安裝性能提升工具
--為了提供OGG的DDL復(fù)制的性能，可以將ddl_pin腳本加入到數(shù)據(jù)庫啟動(dòng)的腳本后面，該腳本需要帶一個(gè)OGG的DDL用戶（即安裝DDL對象的用戶，本例中是goldengate）的參數(shù)，腳本如果不能正常執(zhí)行，需要事先創(chuàng)建DBMS_SHARED_POOL包（可以通過執(zhí)行$ORACLE_HOME/rdbms/admin/ dbmspoll.sql創(chuàng)建）。
--執(zhí)行以下操作創(chuàng)建性能提升工具
SQL> @ddl_pin urogg
--如果因?yàn)闃I(yè)務(wù)問題，開啟DDL以后，對性能的影響比較大的話，需要臨時(shí)禁用DDL觸發(fā)器的話，可以運(yùn)行以下語句：
SQL> @ddl_disable.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
--添加抽取/傳輸進(jìn)程與隊(duì)列文件
GGSCI (dkdb_primary) 1> stop mgr !
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (dkdb_primary) 2> ADD EXTRACT extdr, TRANLOG, BEGIN 2015-05-21 08:22:22
EXTRACT added.
GGSCI (dkdb_primary) 3> ADD EXTTRAIL ./dirdat/sa, EXTRACT extdr, MEGABYTES 200
EXTTRAIL added.
GGSCI (dkdb_primary) 5> ADD EXTRACT extpump, EXTTRAILSOURCE ./dirdat/sa
EXTRACT added.
GGSCI (dkdb_primary) 6> ADD RMTTRAIL ./dirdat/sa, EXTRACT extpump, MEGABYTES 200
RMTTRAIL added.
GGSCI (dkdb_primary) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXTDR 00:00:00 00:00:43
EXTRACT STOPPED EXTPUMP 00:00:00 00:00:13
GGSCI (dkdb_primary) 8> start mgr
Manager started.
GGSCI (dkdb_primary) 9> start ext*
GGSCI (dkdb_primary) 28> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTDR 00:00:00 00:00:03
EXTRACT RUNNING EXTPUMP 00:00:00 00:16:12
--注：修改extract進(jìn)程隊(duì)列文件
GGSCI (dkdb_primary) 61> ALTER EXTRACT extpump, EXTTRAILSOURCE ./dirdat/sa
EXTRACT altered.
使用數(shù)據(jù)泵同步數(shù)據(jù)
select current_scn from v$database ;
SQL> select current_scn from v$database ;
CURRENT_SCN
-----------
1442477
SQL>
數(shù)據(jù)泵導(dǎo)出：
expdp system/test_oracle1_P directory=DATA_PUMP_DIR cluster=n compression=all schemas=TEST_D parallel=2 logfile=expdp201505012.log dumpfile=DKPRODB_EX%U.DMP reuse_dumpfiles=y flashback_scn=1442477
數(shù)據(jù)泵導(dǎo)入：
impdp SYSTEM/test_oracle1_P DIRECTORY=DATA_PUMP_DIR DUMPFILE=DKPRODB_EX01.DMP, DKPRODB_EX02.DMP LOGFILE=import.log table_exists_action=truncate
查看mgr進(jìn)程：
GGSCI (dkdb_ogg) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (dkdb_ogg) 2> view report mgr
出現(xiàn)警告
WARNING OGG-00952
禁用觸發(fā)器：
select trigger_name,status from dba_triggers where owner='TEST_D'
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers where owner ='TEST_D';
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;
/
SQL> select trigger_name,status from dba_triggers where owner='TEST_D';
TRIGGER_NAME STATUS
------------------------------ --------
TRIG_IN_UP_T_APPDETAIL DISABLED
禁用外鍵：
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 ='TEST_D';
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;
/
SQL> select table_name,constraint_name from dba_constraints where owner='TEST_D' and constraint_type='R';
no rows selected
SQL>
關(guān)于歸檔可以選擇關(guān)閉，根據(jù)業(yè)務(wù)的實(shí)際情況來定。
配置target 端replicat進(jìn)程：
target 端 replicat 進(jìn)程
生產(chǎn)密鑰
encrypt password test_oracle1_P, ENCRYPTKEY default
GGSCI (dkdb_ogg) 17> encrypt password test_oracle1_P, ENCRYPTKEY default
Using default key...
Encrypted password: AACAAAAAAAAAAANAOHOJFBNCCHOJHEGIIARCPDNETBLFDEWF
Algorithm used: BLOWFISH
配置進(jìn)程：
edit params repdr
--基礎(chǔ)參數(shù)區(qū)域
REPLICAT repdr
USERID urogg, PASSWORD AACAAAAAAAAAAANAOHOJFBNCCHOJHEGIIARCPDNETBLFDEWF, ENCRYPTKEY default
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"
REPORT AT 06:00
REPORTCOUNT EVERY 25 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
ALLOWNOOPUPDATES
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repdr.dsc, APPEND, MEGABYTES 1024M
DISCARDROLLOVER AT 02:30
ALLOWNOOPUPDATES
--DDL參數(shù)區(qū)域 需要屏蔽的操作類型在此區(qū)域屏蔽
DDL include mapped
ddloptions report
--DDL 錯(cuò)誤過濾
--DDLERROR
--DDLERROR
--復(fù)制對象區(qū)域
MAP TEST_D.*, TARGET TEST_D.*;
/*
Report
Valid for Extract and Replicat
Use the REPORT parameter to specify when Extract or Replicat generates interim runtime
statistics in a process report. The statistics are added to the existing report. By default,
runtime statistics are displayed at the end of a run unless the process is intentionally
killed.
The statistics for REPORT are carried over from the previous report. For example, if the
process performed 10 million inserts one day and 20 million the next, and a report is
generated at 3:00 each day, then the first report would show the first 10 million inserts,
and the second report would show those plus the current day’s 20 million inserts, totalling
30 million. To reset the statistics when a new report is generated, use the STATOPTIONS
parameter with the RESETREPORTSTATS option. See page 335.
For more information about using process reports, see the Oracle GoldenGate Windows
and UNIX Administrator’s Guide
--Example
Example 1 REPORT AT 17:00
Example 2 REPORT ON SUNDAY AT 1:00
*/
/*
REPORTCOUNT
Valid for Extract and Replicat
Use the REPORTCOUNT parameter to report a count of transaction records that Extract or
Replicat processed since startup. Each transaction record represents a logical database
operation that was performed within a transaction that was captured by Oracle
GoldenGate. The record count is printed to the report file and to the screen.
--NOTE This count might differ from the number of records that are contained in the Oracle
GoldenGate trail. If an operation affects data that is larger than 4K, it must be
stored in more than one trail record. Hence, a report count might show 1,000
records (the database operations) but a trail count might show many more records
than that. To obtain a count of the records in a trail, use the Logdump utility.
You can schedule record counts at regular intervals or after a specific number of records.
Record counts are carried over from one report to the other.
REPORTCOUNT can be used only once in a parameter file. If there are multiple instances of
REPORTCOUNT , Oracle GoldenGate uses the last one.
Default None
Syntax REPORTCOUNT [EVERY]
{RECORDS | SECONDS | MINUTES | HOURS} [, RATE]
*/
--添加replicat進(jìn)程
ADD REPLICAT repdr1, EXTTRAIL ./dirdat/sa
ADD REPLICAT repdr2, EXTTRAIL ./dirdat/sa
--啟動(dòng)replicat進(jìn)程
GGSCI (dkdb_ogg) 65> start replicat repdr, aftercsn 1442477
Sending START request to MANAGER ...
REPLICAT REPDR starting
--啟動(dòng)replicat進(jìn)程方式
--指定SCN號啟動(dòng)
start replicat repdr, aftercsn 1442477
--指定rba號啟動(dòng)
start replicat repdr1, extseqno 5, RBA 2535303
start replicat repdr2, extseqno 5, RBA 2535303
--指定具體時(shí)間啟動(dòng)
alter REPDR1,begin yyyy-mm-dd
start repdr1
GGSCI (dkdb_ogg) 66> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPDR 00:00:00 00:00:01
create table WIDGET_BAK as select * from P_WIDGET where 1=2 空表
DML 測試：
update table P_WIDGET set state=6
commit;
在standby 端查看：
SQL> select state from test_desk.P_WIDGET;
STATE
----------
6
6
6
查看source（primary）端 extract 進(jìn)程
里面會(huì)出現(xiàn)一個(gè)警告：WARNING OGG-00869
查看target端 ：
[root@dkdb_ogg ~]# su - oracle
[oracle@dkdb_ogg ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 13 16:59:48 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select state from test_desk.P_WIDGET;
STATE
----------
6
6
......
6
34 rows selected.
到這里說明DML已經(jīng)測試成功！
DDL 測試：
--##Source 端（primary_db）：
創(chuàng)建表
SQL> CONN test_desk/pwd4test
Connected.
SQL> create table WIDGET_BAK as select * from P_WIDGET where 1=2;
--##Standby_db端查看
SQL> CONN test_desk/pwd4test
Connected.
SQL> select count(*) from WIDGET_BAK;
COUNT(*)
----------
0
SQL> select count(*) from P_WIDGET;
COUNT(*)
----------
34
SQL>
--##OGG_Target端
view report repdr 進(jìn)程日志
2015-05-13 14:04:18 INFO OGG-00482 DDL found, operation [create table WIDGET_BAK as select * from P_WIDGET where 1=2 (size 80)].
2015-05-13 14:04:18 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [create table TEST_D."WIDGET_BAK" as select * from QN_DESKTOP_
WIDGET where 1=2 (size 92)].
2015-05-13 14:04:18 INFO OGG-00487 DDL operation included [include mapped], optype [CREATE], objtype [TABLE], objowner [TEST_D], objname [WIDGET_BAK]
.
2015-05-13 14:04:18 INFO OGG-01407 Setting current schema for DDL operation to [TEST_D].
2015-05-13 14:04:18 INFO OGG-00484 Executing DDL operation.
2015-05-13 14:04:18 INFO OGG-00483 DDL operation successful.
2015-05-13 14:04:18 INFO OGG-01408 Restoring current schema for DDL operation to [urogg].
這里說明已經(jīng)同步成功，進(jìn)入庫查看
SQL> show user
USER is "TEST_D"
SQL> select count(*) from WIDGET_BAK;
COUNT(*)
----------
0
SQL> select count(*) from P_WIDGET;
COUNT(*)
----------
34
SQL>
查看replicat進(jìn)程：
GGSCI (dkdb_ogg) 16> info replicat repdr detail
REPLICAT REPDR Last Started 2015-05-13 12:00 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File ./dirdat/sa000000
2015-05-13 14:04:12.382273 RBA 43978
Extract Source Begin End
./dirdat/sa000000 2015-05-13 09:12 2015-05-13 14:04
./dirdat/sa000000 2015-05-13 09:12 First Record
./dirdat/sa000000 2015-05-13 09:12 2015-05-13 09:12
./dirdat/sa000000 2015-05-13 09:12 First Record
./dirdat/sa000000 * Initialized * 2015-05-13 09:12
./dirdat/sa000000 * Initialized * First Record
Current directory /data0/temp/ogg
Report file /data0/temp/ogg/dirrpt/REPDR.rpt
Parameter file /data0/temp/ogg/dirprm/repdr.prm
Checkpoint file /data0/temp/ogg/dirchk/REPDR.cpr
Checkpoint table urogg.checktable
Process file /data0/temp/ogg/dirpcs/REPDR.pcr
Stdout file /data0/temp/ogg/dirout/REPDR.out
Error log /data0/temp/ogg/ggserr.log
GGSCI (dkdb_ogg) 17>
重慶分公司
重慶分公司
