配置部署Oracle Goldengate Version 11.2.1.0.1

配置部署Oracle Goldengate Version 11.2.1.0.1 

配置用戶環境變量

vi .bash_profile

添加：

export OGG_HOME=/data0/temp/ogg

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

export LD_LIBRARY_PATH

查看數據是否開啟歸檔

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

查看數據庫日志打開情況

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的附加日志關閉；

如果all columns的附加日志打開的話，則需要使用以下語句予以關閉：

alter database drop supplemental log data (ALL) columns;

如果出現問題，可以通過以下語句進行回退：

alter database drop supplemental log data ;

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

alter system switch logfile;

數據庫開啟強日志模式：

SQL>alter database force logging;

--注：這里source 庫已是dataguard primary DB 所以強制日志模式已經打開：

查看：

SQL> select FORCE_LOGGING from v$database;

FOR

---

YES

SQL> 

關閉recyclebin 

--注在system一級,不加DEFERRED參數是不允許修改的,

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 支持

這里的用戶和創建DDL同步所需用戶一樣。（登陸源端和目標端執行創建用戶）：

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.

賦權：

GRANT CONNECT, RESOURCE, DBA TO urogg;

GRANT CREATE TABLE,CREATE SEQUENCE TO urogg;--這里是用于用戶安裝DDL執行SQL腳本時用；

編輯GLOBALS 參數 

進入./ggsci在所有節點執行 

EDIT PARAMS ./GLOBALS  并添加

GGSCHEMA urogg                    --指定的進行DDL復制的數據庫用戶 

保存退出，并進入$OGG_HOME 登陸sqlplus  / as sysdba 執行腳本

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.*

這里會出現警告：

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.

檢查確認：

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.* 后面沒有;號

否則報錯如下：

GGSCI (dkdb_primary) 2> add trandata test_desk.*;

ERROR: No viable tables matched specification.

創建 manager 進程：

GGSCI (dkdb_primary) 4> edit param mgr

PORT 7801

DYNAMICPORTLIST 7802-7820

PURGEOLDEXTRACTS /opt/ogg/dirdat/ *, USECHECKPOINTS, MINKEEPDAYS 7                          

--配置DDL參數區域

--trail file 保留7天

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10,FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

--配置一般日志信息區域

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

--##注 ./dirdat/*

-- AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7  自動啟動extract進程 

GGSCI (dkdb_primary) 5> 

啟動mgr進程

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進程

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、查詢是否有事務等待

select count(*) from gv$transaction;

3、抽取(extract)進程參數配置

GGSCI (dkdb_primary) 5> edit param extdr

--基礎參數區域

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參數區域，所有復制對象，屏蔽對象在源端體現。

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 進程：

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

--基礎參數區域

EXTRACT extpump

RMTHOST 172.16.1.246, MGRPORT 7801, COMPRESS

PASSTHRU

RMTTRAIL  ./dirdat/sa

DYNAMICRESOLUTION

--對象區域

TABLE    TEST_D.*;

SEQUENCE    TEST_D.*;

配置DDL objects 

給urogg用戶賦權：

GRANT EXECUTE ON utl_file TO urogg;

進入OGG_HOME，sqlplus / as sysdba 

執行腳本并輸入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復制的性能，可以將ddl_pin腳本加入到數據庫啟動的腳本后面，該腳本需要帶一個OGG的DDL用戶（即安裝DDL對象的用戶，本例中是goldengate）的參數，腳本如果不能正常執行，需要事先創建DBMS_SHARED_POOL包（可以通過執行$ORACLE_HOME/rdbms/admin/ dbmspoll.sql創建）。

--執行以下操作創建性能提升工具

SQL> @ddl_pin urogg 

--如果因為業務問題，開啟DDL以后，對性能的影響比較大的話，需要臨時禁用DDL觸發器的話，可以運行以下語句：

SQL> @ddl_disable.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

--添加抽取/傳輸進程與隊列文件

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進程隊列文件

GGSCI (dkdb_primary) 61> ALTER EXTRACT extpump, EXTTRAILSOURCE  ./dirdat/sa

EXTRACT altered.

使用數據泵同步數據

select current_scn from v$database ;

SQL>  select current_scn from v$database ;

CURRENT_SCN

-----------

    1442477

SQL> 

數據泵導出：

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

數據泵導入：

impdp SYSTEM/test_oracle1_P  DIRECTORY=DATA_PUMP_DIR DUMPFILE=DKPRODB_EX01.DMP, DKPRODB_EX02.DMP   LOGFILE=import.log table_exists_action=truncate

查看mgr進程：

GGSCI (dkdb_ogg) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

GGSCI (dkdb_ogg) 2> view report mgr

出現警告

WARNING OGG-00952

禁用觸發器：

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> 

關于歸檔可以選擇關閉，根據業務的實際情況來定。

配置target 端replicat進程：

target 端 replicat 進程

生產密鑰

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

配置進程：

edit params repdr

--基礎參數區域

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參數區域 需要屏蔽的操作類型在此區域屏蔽

DDL include mapped

ddloptions report

--DDL 錯誤過濾

--DDLERROR <error> IGNORE

--DDLERROR <error1> IGNORE

--復制對象區域

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] <count>

{RECORDS | SECONDS | MINUTES | HOURS} [, RATE]

*/

--添加replicat進程

ADD REPLICAT repdr1, EXTTRAIL ./dirdat/sa

ADD REPLICAT repdr2, EXTTRAIL ./dirdat/sa

--啟動replicat進程

GGSCI (dkdb_ogg) 65> start replicat  repdr, aftercsn 1442477

Sending START request to MANAGER ...

REPLICAT REPDR starting

--啟動replicat進程方式

--指定SCN號啟動

start replicat  repdr, aftercsn 1442477

--指定rba號啟動

start replicat  repdr1, extseqno 5, RBA 2535303

start replicat  repdr2, extseqno 5, RBA 2535303

--指定具體時間啟動

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 進程

里面會出現一個警告：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已經測試成功！

DDL 測試：

--##Source 端（primary_db）：

創建表

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 進程日志

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].

這里說明已經同步成功，進入庫查看

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進程：

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>