Target Side Setup
Install OGG on Target Side
Creates required directories for OGG
[oracle@vzwc1 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140523.1046_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May 23 2014 15:41:58
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (vzwc1) 1> create subdirs
Creating subdirectories under current directory /ggs
Parameter files /ggs/dirprm: already exists
Report files /ggs/dirrpt: created
Checkpoint files /ggs/dirchk: created
Process status files /ggs/dirpcs: created
SQL script files /ggs/dirsql: created
Database definitions files /ggs/dirdef: created
Extract data files /ggs/dirdat: created
Temporary files /ggs/dirtmp: created
Stdout files /ggs/dirout: created
Create "diroby","dirdsc","scripts" and "trails" directories for OGG obey scripts,discard files,scripts and trails respectively.
[oracle@vzwc1 ~]$ mkdir -p /ggs/diroby
[oracle@vzwc1 ~]$ mkdir -p /ggs/dirdsc
[oracle@vzwc1 ~]$ mkdir -p /ggs/trails/target
[oracle@vzwc1 ~]$ mkdir -p /ggs/trails/source
[oracle@vzwc1 ~]$ mkdir -p /ggs/scripts
Create Parameter Files and Start Manager
[oracle@vzwc1 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140523.1046_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May 23 2014 15:41:58
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (vzwc1) 1> view param mgr
PORT 7809
DYNAMICPORTLIST 7850-7860
LAGINFOMINUTES 0
LAGREPORTMINUTES 10
LAGCRITICALMINUTES 60
AUTORESTART EXTRACT E*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440
AUTORESTART EXTRACT P*, RETRIES 10, WAITMINUTES 5, RESETMINUTES 1440
PURGEOLDEXTRACTS /ggs/trails/target/*, USECHECKPOINTS, MINKEEPDAYS 10, FREQUENCYMINUTES 15
GGSCI (vzwc1) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (vzwc1) 3> start mgr
Manager started.
GGSCI (vzwc1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (vzwc1) 5> info mgr
Manager is running (IP port vzwc1.7809).
Create GGS and GGS_MON Users
[oracle@vzwc1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 16 12:28:37 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create tablespace ggs_tbs datafile '+DATADG' size 100M;
Tablespace created.
SQL> create user ggs identified by ggs default tablespace ggs_tbs temporary tablespace temp;
User created.
SQL> create user ggs_mon identified by ggs_mon default tablespace ggs_tbs temporary tablespace temp;
User created.
SQL> grant dba to ggs;
Grant succeeded.
SQL> grant connect,resource to ggs_mon;
Grant succeeded.
SQL>
SQL> select name from v$datafile;
NAME
——————————————————————————–
+DATADG/zwc/datafile/system.256.849043917
+DATADG/zwc/datafile/sysaux.257.849043921
+DATADG/zwc/datafile/undotbs1.258.849043923
+DATADG/zwc/datafile/users.259.849043925
+DATADG/zwc/datafile/example.264.849044181
+DATADG/zwc/datafile/undotbs2.265.849044603
+DATADG/zwc/datafile/ggs_tbs.269.850393741
7 rows selected.
Create GGS_MON Tables
SQL> create table ggs_mon.ggs_heartbeat(id number,ts date);
Table created.
SQL> insert into ggs_mon.ggs_heartbeat values(1,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> create table ggs_mon.ggs_lagtime
2 (id number,
3 ts date,
4 committime date,
5 groupname varchar2(8),
6 host varchar2(60),
7 local_insert_time date);
Table created.
Enable Database Level Supplemental Logging
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
——–
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch all logfile;
System altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
——–
YES
Start Pump On 10g Server
[oracle@zwc ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (zwc) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E10GDB 00:00:00 00:00:06
EXTRACT STOPPED P10GDB 00:00:00 232:22:33
GGSCI (zwc) 2> start p10gdb
Sending START request to MANAGER …
EXTRACT P10GDB starting
GGSCI (zwc) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E10GDB 00:00:00 00:00:06
EXTRACT RUNNING P10GDB 00:00:00 00:00:00
GGSCI (zwc) 4> info p10gdb
EXTRACT P10GDB Last Started 2014-06-16 12:48 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint File /ggs/trails/source/aa000000
First Record RBA 0
We should be able to see trails starting with "aa" in the "/ggs/trails/target" on 11g database server.
[oracle@vzwc1 target]$ ls -l /ggs/trails/target/
total 104
-rw-r—– 1 oracle oinstall 100381 Jun 16 12:52 aa000000
Perform Initial Data Load using Expdb/impdb
For expdb we need to create directoty on 10g database server
[oracle@zwc ~]$ mkdir -p /ggs/zwc/exp
[oracle@zwc ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jun 16 12:55:21 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory exp_ggs_dir as '/ggs/zwc/exp';
Directory created.
SQL> grant read,write on directory exp_ggs_dir to ggs;
Grant succeeded.
Create parameter file for export.But before we create parameter file,one thing we need to carefully understand here is that we must get a consistent export by using FLASHBACK_SCN
parameter of expdb utility.
SQL> select nvl((select max(username)
2 from gv$session s, gv$transaction t
3 where t.ses_addr = s.saddr
4 and t.inst_id = s.inst_id),
5 'Get this SCN for Export') transaction_dbuser,
6 (select to_char(current_scn) from v$database) scn
7 from dual
8 /
TRANSACTION_DBUSER SCN
—————————— —————————————-
Get this SCN for Export 834116
[oracle@zwc exp]$ cat expdp_10g.par
userid=ggs/ggs
flashback_scn=834116
job_name=GG_10G_EXPORT
directory=EXP_GGS_DIR
dumpfile=expdp_gg_10g_%U.dmp
logfile=expdp_gg_10g.log
parallel=2
SCHEMAS=HR,OE,PM
Start export by issuing the following command
[oracle@zwc exp]$ nohup expdp parfile=expdp_10g.par &
[1] 7949
[oracle@zwc exp]$ nohup: appending output to `nohup.out'
[oracle@zwc exp]$ tail -f nohup.out
Export: Release 10.2.0.4.0 – 64bit Production on Monday, 16 June, 2014 13:09:15
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "GGS"."GG_10G_EXPORT": parfile=expdp_10g.par
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"OE"."PURCHASEORDER" will be skipped.
Total estimation using BLOCKS method: 6.625 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
. . exported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows
. . exported "PM"."ONLINE_MEDIA" 39.24 KB 9 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY
. . exported "PM"."PRINT_MEDIA" 188.8 KB 4 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
. . exported "OE"."LINEITEM_TABLE" 283.5 KB 2232 rows
. . exported "OE"."CUSTOMERS" 75.22 KB 319 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
. . exported "OE"."WAREHOUSES" 12.61 KB 9 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "PM"."TEXTDOCS_NESTEDTAB" 87.35 KB 12 rows
. . exported "OE"."PRODUCT_INFORMATION" 71.73 KB 288 rows
. . exported "OE"."ACTION_TABLE" 14.87 KB 132 rows
. . exported "HR"."COUNTRIES" 6.085 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . exported "HR"."JOBS" 6.609 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . exported "HR"."LOCATIONS" 7.710 KB 23 rows
. . exported "HR"."REGIONS" 5.289 KB 4 rows
. . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.32 KB 288 rows
. . exported "OE"."CATEGORIES_TAB" 13.12 KB 22 rows
. . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.398 KB 21 rows
. . exported "OE"."INVENTORIES" 21.39 KB 1112 rows
. . exported "OE"."ORDERS" 11.64 KB 105 rows
. . exported "OE"."ORDER_ITEMS" 20.41 KB 665 rows
. . exported "OE"."PROMOTIONS" 5.312 KB 2 rows
Master table "GGS"."GG_10G_EXPORT" successfully loaded/unloaded
******************************************************************************
Dump file set for GGS.GG_10G_EXPORT is:
/ggs/zwc/exp/expdp_gg_10g_01.dmp
/ggs/zwc/exp/expdp_gg_10g_02.dmp
Job "GGS"."GG_10G_EXPORT" completed with 1 error(s) at 13:10:04
Once export is finished,then transfer dump files to the 11g database server.Then create exp_ggs_dir directoty,and grant read/write privlege to "GGS" user.For initiating import we
will be using "GGS" user.
[oracle@vzwc1 target]$ mkdir -p /ggs/zwc/imp
[oracle@vzwc1 target]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 16 13:15:59 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create directory exp_ggs_dir as '/ggs/zwc/imp';
Directory created.
SQL> grant read,write on directory exp_ggs_dir to ggs;
Grant succeeded.
Create parameter file for import but do not start inport right away.We need to check jobs,which we will do after creating import parameter file.
[oracle@vzwc1 imp]$ cat impdp_11g.par
userid=ggs/ggs
job_name=GG_11G_IMPORT
directory=EXP_GGS_DIR
dumpfile=expdp_gg_10g_%U.dmp
logfile=impdp_gg_11g.log
SCHEMAS=HR,OE,PM
Set job_queue_processes to Zero
[oracle@vzwc1 imp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 16 13:21:55 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter job_queue_process
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000
SQL> alter system set job_queue_processes=0 scope=both sid='*';
System altered.
SQL> show parameter job_queue_process
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 0
Start Import in 11g Database
[oracle@vzwc1 imp]$ impdp parfile=impdp_11g.par
Import: Release 11.2.0.2.0 – Production on Mon Jun 16 13:28:40 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "GGS"."GG_11G_IMPORT" successfully loaded/unloaded
Starting "GGS"."GG_11G_IMPORT": ggs/******** parfile=impdp_11g.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39034: Table TABLE_DATA:"OE"."ACTION_TABLE" does not exist.
ORA-39034: Table TABLE_DATA:"OE"."LINEITEM_TABLE" does not exist.
. . imported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows
. . imported "PM"."ONLINE_MEDIA" 39.24 KB 9 rows
. . imported "PM"."PRINT_MEDIA" 188.8 KB 4 rows
. . imported "OE"."CUSTOMERS" 75.22 KB 319 rows
. . imported "OE"."WAREHOUSES" 12.61 KB 9 rows
. . imported "PM"."TEXTDOCS_NESTEDTAB" 87.35 KB 12 rows
. . imported "OE"."PRODUCT_INFORMATION" 71.73 KB 288 rows
. . imported "HR"."COUNTRIES" 6.085 KB 25 rows
. . imported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . imported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . imported "HR"."JOBS" 6.609 KB 19 rows
. . imported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . imported "HR"."LOCATIONS" 7.710 KB 23 rows
. . imported "HR"."REGIONS" 5.289 KB 4 rows
. . imported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.32 KB 288 rows
. . imported "OE"."CATEGORIES_TAB" 13.12 KB 22 rows
. . imported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.398 KB 21 rows
. . imported "OE"."INVENTORIES" 21.39 KB 1112 rows
. . imported "OE"."ORDERS" 11.64 KB 105 rows
. . imported "OE"."ORDER_ITEMS" 20.41 KB 665 rows
. . imported "OE"."PROMOTIONS" 5.312 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Disable Jobs
select job,schema_user,broken,what from dba_jobs;
Then you can use following PL/SQL block to disable jobs one by one which you find from above query.
begin
dbms_job.broken(jobid,TRUE,'');
end;
/
For enabling a DBMS_JOB,following PL/SQL block can be used
begin
dbms_job.broken(jobid,FALSE,'');
end;
/
Run the following query to find out about DBMS_SCHEDULER jobs in a database.
select owner, job_name, state, enabled
from dba_scheduler_jobs
where owner in ('HR', 'OE', 'PM')
and enabled = 'TRUE'
order by state, 1
/
Script for generating script for Disabling Scheduler Jobs.
set head off
set feedback off
set linesize 100
spool disable_jobs_11g.sql
select 'exec dbms_scheduler.disable (name =>
' || '''' || owner || '.' || JOB_NAME || '''' || ');' stmt
from dba_scheduler_jobs
where owner in ('HR', 'OE', 'PM')
and state <> 'DISABLED';
spool off
Script for generating script for Enabling Scheduler Jobs.
set head off
set feedback off
set linesize 100
spool enable_jobs_11g.sql
select 'exec dbms_scheduler.enable (name =>
' || '''' || owner || '.' || JOB_NAME || '''' || ');' stmt
from dba_scheduler_jobs
where owner in ('HR', 'OE', 'PM')
and state <> 'DISABLED';
spool off
Chcek Invalid Objects
select owner, object_type, object_name, status
from dba_objects
where owner in ('HR', 'OE', 'PM')
and status <> 'VALID'
/
Disable Constraints
select owner,
table_name,
constraint_name,
constraint_type,
delete_rule,
status
from dba_constraints
where constraint_type = 'R'
and delete_rule <> 'NO ACTION'
and owner in ('HR', 'OE', 'PM')
and status = 'ENABLED';
Script for generating script for Disabling Constraints.
set head off
set pagesize 600
set feedback off
set linesize 100
spool disable_constraints_11g.sql
select 'alter table ' || owner || '.' || table_name ||
' disable constraint ' || constraint_name || ' ;'
from dba_constraints
where constraint_type = 'R'
and delete_rule <> 'NO ACTION'
and owner in ('HR', 'OE', 'PM')
and status = 'ENABLED'
order by status, owner, table_name, constraint_name;
spool off
Script for generating script for Enabling Constraints.
set head off
set pagesize 600
set feedback off
set linesize 100
spool enable_constraints_11g.sql
select 'alter table ' || owner || '.' || table_name ||
' enable constraint ' || constraint_name || ' ;'
from dba_constraints
where constraint_type = 'R'
and delete_rule <> 'NO ACTION'
and owner in ('HR', 'OE', 'PM')
and status = 'ENABLED'
order by status, owner, table_name, constraint_name;
spool off
Disable Triggers
select owner, trigger_name, status
from dba_triggers
where owner in ('HR', 'OE', 'PM')
and status = 'ENABLED';
Script for generating script for Disabling Triggers.
set head off
set feedback off
set linesize 100
spool disable_triggers_11g.sql
select 'alter trigger ' || owner || '.' || trigger_name || ' disable;'
from dba_triggers
where owner in ('HR', 'OE', 'PM')
and status = 'ENABLED'
order by owner, trigger_name;
spool off
Script for generating script for Enabling Triggers.
set head off
set feedback off
set linesize 100
spool enable_triggers_11g.sql
select 'alter trigger ' || owner || '.' || trigger_name || ' enable;'
from dba_triggers
where owner in ('HR', 'OE', 'PM')
and status = 'ENABLED'
order by owner, trigger_name;
spool off
Start Replicat
Add checkpoint table,and add checkpoint table to ./GLOBALS file.
[oracle@vzwc1 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140523.1046_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May 23 2014 15:41:58
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (vzwc1) 1> dblogin userid ggs, password ggs
Successfully logged into database.
GGSCI (vzwc1) 2> add checkpointtable ggs.chkpoint_table
Successfully created checkpoint table ggs.chkpoint_table.
GGSCI (vzwc1) 5> view param ./GLOBALS
CHECKPOINTTABLE ggs.chkpoint_table
For adding a replicat invoke ggsci interface,and create parameter file fot replicat.
GGSCI (vzwc1) 10> view param r10gdb
REPLICAT R10GDB
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ggs@ZWC, PASSWORD ggs
ASSUMETARGETDEFS
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /ggs/dirdsc/R10GDB.dsc, APPEND, MEGABYTES 100
DISCARDROLLOVER ON SUNDAY
REPERROR (default, abend)
DDLERROR default abend
DYNAMICRESOLUTION
GROUPTRANSOPS 2000
DBOPTIONS SUPPRESSTRIGGERS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP HR.*, TARGET HR.*;
MAP OE.*, TARGET OE.*;
MAP PM.*, TARGET PM.*;
–add for heartbeat
MAP ggs_mon.ggs_heartbeat, TARGET ggs_mon.ggs_lagtime,
KEYCOLS (ID),
INSERTALLRECORDS,
COLMAP (USEDEFAULTS,
id = 0,
committime = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
groupname = @GETENV ("GGENVIRONMENT", "GROUPNAME"),
host = @TOKEN("host"),
local_insert_time = @DATENOW()
);
GGSCI (vzwc1) 11> dblogin userid ggs, password ggs
Successfully logged into database.
GGSCI (vzwc1) 13> add replicat r10gdb, exttrail /ggs/trails/target/aa, checkpointtable ggs.chkpoint_table, extseqno 0, extrba 0
REPLICAT added.
GGSCI (vzwc1) 14> start replicat r10gdb atcsn 834116
Sending START request to MANAGER …
REPLICAT R10GDB starting
GGSCI (vzwc1) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R10GDB 01:24:49 00:00:01
Simple DML test
On 10g database server
[oracle@zwc ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jun 16 14:52:53 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> delete from hr.employees where rownum<=2;
2 row deleted.
SQL> commit;
Commit complete.
GSCI (zwc) 15> stats e10gdb
Sending STATS request to EXTRACT E10GDB …
Start of Statistics at 2014-06-16 14:53:26.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 3.00
Mapped operations 0.00
Unmapped operations 1.00
Other operations 2.00
Excluded operations 3.00
Output to /ggs/trails/source/aa:
*** Total statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Total statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Total statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
Extracting from GGS_MON.GGS_HEARTBEAT to GGS_MON.GGS_HEARTBEAT:
*** Total statistics since 2014-06-16 11:28:07 ***
Total inserts 0.00
Total updates 129.00
Total befores 129.00
Total deletes 0.00
Total discards 0.00
Total operations 129.00
*** Daily statistics since 2014-06-16 11:28:07 ***
Total inserts 0.00
Total updates 129.00
Total befores 129.00
Total deletes 0.00
Total discards 0.00
Total operations 129.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
Total inserts 0.00
Total updates 54.00
Total befores 54.00
Total deletes 0.00
Total discards 0.00
Total operations 54.00
*** Latest statistics since 2014-06-16 11:28:07 ***
Total inserts 0.00
Total updates 129.00
Total befores 129.00
Total deletes 0.00
Total discards 0.00
Total operations 129.00
Extracting from GGS.GGS_MARKER to GGS.GGS_MARKER:
*** Total statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 11:28:07 ***
No database operations have been performed.
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2014-06-16 11:28:07 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 11:28:07 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2014-06-16 11:28:07 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
End of Statistics.
GGSCI (zwc) 16>
GGSCI (zwc) 16> stats p10gdb
Sending STATS request to EXTRACT P10GDB …
Start of Statistics at 2014-06-16 14:53:38.
Output to /ggs/trails/target/aa:
*** Total statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Total statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Total statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Daily statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
No database operations have been performed.
Extracting from HR.LOCATIONS_SEQ to HR.LOCATIONS_SEQ:
*** Total statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
Extracting from HR.DEPARTMENTS_SEQ to HR.DEPARTMENTS_SEQ:
*** Total statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
Extracting from HR.EMPLOYEES_SEQ to HR.EMPLOYEES_SEQ:
*** Total statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
Extracting from OE.ORDERS_SEQ to OE.ORDERS_SEQ:
*** Total statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-06-16 12:48:55 ***
Total updates 2.00
Total discards 0.00
Total operations 2.00
Extracting from GGS_MON.GGS_HEARTBEAT to GGS_MON.GGS_HEARTBEAT:
*** Total statistics since 2014-06-16 12:48:55 ***
Total inserts 0.00
Total updates 401.00
Total befores 401.00
Total deletes 0.00
Total discards 0.00
Total operations 401.00
*** Daily statistics since 2014-06-16 12:48:55 ***
Total inserts 0.00
Total updates 401.00
Total befores 401.00
Total deletes 0.00
Total discards 0.00
Total operations 401.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
Total inserts 0.00
Total updates 54.00
Total befores 54.00
Total deletes 0.00
Total discards 0.00
Total operations 54.00
*** Latest statistics since 2014-06-16 12:48:55 ***
Total inserts 0.00
Total updates 401.00
Total befores 401.00
Total deletes 0.00
Total discards 0.00
Total operations 401.00
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2014-06-16 12:48:55 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 12:48:55 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:00:00 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2014-06-16 12:48:55 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
End of Statistics.
On 11g database server
GGSCI (vzwc1) 38> stats r10gdb
Sending STATS request to REPLICAT R10GDB …
Start of Statistics at 2014-06-16 14:59:05.
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 0.00
Mapped operations 0.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
Replicating from GGS_MON.GGS_HEARTBEAT to GGS_MON.GGS_LAGTIME:
*** Total statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 114.00
Total deletes 0.00
Total discards 0.00
Total operations 114.00
*** Daily statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 114.00
Total deletes 0.00
Total discards 0.00
Total operations 114.00
*** Hourly statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 114.00
Total deletes 0.00
Total discards 0.00
Total operations 114.00
*** Latest statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 114.00
Total deletes 0.00
Total discards 0.00
Total operations 114.00
Replicating from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2014-06-16 14:29:53 ***
Total inserts 0.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 2.00
End of Statistics.