=====> Bidirectional DML replication in oracle Goldengate <=====
=====>Node 1 details
OS details:
[oracle@gg1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
hostname: gg1.example.com
database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Database name : Dev1
Goldengate details:
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.
====>Node2 details
OS details:
[oracle@gg2 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
hostname: gg2.example.com
database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Database name : Dev2
Goldengate details:
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.
====>TNS configuration both node..
edit tnsname.ora
node 1:
DEV2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dev2)
)
)
DEV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev1)
)
)
node 2:
DEV2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev2)
)
)
DEV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dev1)
)
)
====>On source database
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> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO YES
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> CREATE USER ggate IDENTIFIED BY oracle;
User created.
SQL> grant create session to ggate;
Grant succeeded.
SQL> grant dba to ggate;
Grant succeeded.
SQL> alter system set undo_retention=86400 scope=both;
System altered.
SQL> grant flashback any table to ggate;
Grant succeeded.
SQL>
==>On target server
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO YES
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> CREATE USER ggate IDENTIFIED BY ggate;
User created.
SQL> grant create session to ggate;
Grant succeeded.
SQL> grant dba to ggate;
Grant succeeded.
SQL> alter system set undo_retention=86400 scope=both;
System altered.
SQL> grant flashback any table to ggate;
Grant succeeded.
SQL>
====>Source
GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.
GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (gg1.example.com) 5> add trandata scott.dept
=====>target
GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.
GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (gg1.example.com) 5> add trandata scott.dept
Note:creating extract and replicate process on both server.
====>Source
GGSCI (gg1.example.com) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (gg1.example.com) 4> edit param RX1 -----extract process
EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.dept;
GGSCI (gg1.example.com) 5> edit param DP1 -------datapump process
EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.dept;
GGSCI (gg1.example.com) 6> edit param REP1 ------replication process
REPLICAT rep1
USERID rx025n, PASSWORD *****
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.dept, TARGET scott.dept;
GGSCI (gg1.example.com) 7> ADD EXTRACT rx1, TRANLOG, BEGIN NOW
GGSCI (gg1.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1
GGSCI (gg1.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx
GGSCI (gg1.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt, EXTRACT dp1
GGSCI (gg1.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/rt CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.
GGSCI (gg1.example.com) 20> Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/rt
Extract: DP1
Seqno: 3
RBA: 1513
File Size: 100M
Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
Extract: EX1
Seqno: 3
RBA: 1243
File Size: 100M
========>targate
GGSCI (gg2.example.com) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (gg2.example.com) 4> edit param RX1 -----extract process
EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.dept;
GGSCI (gg2.example.com) 5> edit param DP1 -------datapump process
EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx
TABLE scott.dept;
GGSCI (gg2.example.com) 6> edit param REP1 ------replication process
REPLICAT rep1
USERID rx025n, PASSWORD *****
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.dept, TARGET scott.dept;
GGSCI (gg2.example.com) 7> ADD EXTRACT ex1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (gg2.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1
EXTTRAIL added.
GGSCI (gg2.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx
EXTRACT added.
GGSCI (gg2.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx, EXTRACT dp1
RMTTRAIL added
GGSCI (gg2.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/tx CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.
GGSCI (gg2.example.com) 30> Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/tx
Extract: DP1
Seqno: 0
RBA: 0
File Size: 100M
Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
Extract: EX1
Seqno: 0
RBA: 0
File Size: 100M
======>start gg extract,datapump process on source
- start manager process
- start extract process
- start datapump process
GGSCI (gg1.example.com) 33> start manager
Manager started.
GGSCI (gg1.example.com) 34> start EXTRACT EX1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (gg1.example.com) 35> start EXTRACT DP1
Sending START request to MANAGER ...
EXTRACT DP1 starting
GGSCI (gg1.example.com) 36> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:10
EXTRACT RUNNING RX1 00:00:00 00:00:03
REPLICAT STOPPED REP1 00:00:00 00:20:17
note: replicate process we will start after target extract and replication process.
======>start gg extract,datapump process on targate
- start manager process
- start extract process
- start datapump process
GGSCI (gg2.example.com) 47> start extract EX1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (gg2.example.com) 48> start extract DP1
Sending START request to MANAGER ...
EXTRACT DP1 starting
GGSCI (gg2.example.com) 49> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:07
EXTRACT RUNNING RX1 00:00:00 00:00:04
REPLICAT STOPPED REP1 00:00:00 00:00:09
note: replicate process we will start after target extract and replication process.
=======>start gg replicate process on target
GGSCI (gg2.example.com) 50> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg1.example.com) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:03
EXTRACT RUNNING RX1 00:00:00 00:00:01
REPLICAT RUNNING REP1 00:00:00 00:00:04
=====>start gg replicate process on source
GGSCI (gg1.example.com) 66> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg1.example.com) 67> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:07
EXTRACT RUNNING RX1 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:09
======>inserting one row in source database
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev1
SQL> conn scott
Enter password:
Connected.
SQL> show user
USER is "SCOTT"
SQL> set lines 220
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values(66,'marketing','new york');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
SQL>
GGSCI (gg1.example.com) 2> stats EXTRACT RX1
Sending STATS request to EXTRACT RX1 ...
Start of Statistics at 2015-12-25 01:41:36.
Output to /u01/app/oracle/product/gg/dirdat/rx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-12-25 01:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
End of Statistics.
=======>checking on target side
GGSCI (gg2.example.com) 51> stats REPLICAT REP1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2015-12-25 01:42:22.
Replicating from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-12-25 01:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
End of Statistics.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev2
SQL> conn scott
Enter password:
Connected.
SQL> set lines 220
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
SQL>
===>Now inserting value in target database.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev2
SQL> conn scott
Enter password:
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
SQL> insert into dept values(68,'testing','perth');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
68 testing perth
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
6 rows selected.
====>checking gg process
GGSCI (gg2.example.com) 2> stats RX1
Sending STATS request to EXTRACT RX1 ...
Start of Statistics at 2015-12-25 03:34:11.
Output to /u01/app/oracle/product/gg/dirdat/rx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (gg2.example.com) 3> stats DP1
Sending STATS request to EXTRACT DP1 ...
Start of Statistics at 2015-12-25 03:34:19.
Output to /u01/app/oracle/product/gg/dirdat/tx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
====>checking gg process on source
GGSCI (gg1.example.com) 7> stats REP1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2015-12-25 03:42:02.
Replicating from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
==>database validation
SQL> conn /as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev1
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
66 marketing new york
68 testing perth
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
******END****
=====>Node 1 details
OS details:
[oracle@gg1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
hostname: gg1.example.com
database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Database name : Dev1
Goldengate details:
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.
====>Node2 details
OS details:
[oracle@gg2 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
hostname: gg2.example.com
database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Database name : Dev2
Goldengate details:
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.
====>TNS configuration both node..
edit tnsname.ora
node 1:
DEV2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dev2)
)
)
DEV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev1)
)
)
node 2:
DEV2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev2)
)
)
DEV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dev1)
)
)
====>On source database
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> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO YES
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> CREATE USER ggate IDENTIFIED BY oracle;
User created.
SQL> grant create session to ggate;
Grant succeeded.
SQL> grant dba to ggate;
Grant succeeded.
SQL> alter system set undo_retention=86400 scope=both;
System altered.
SQL> grant flashback any table to ggate;
Grant succeeded.
SQL>
==>On target server
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO YES
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> CREATE USER ggate IDENTIFIED BY ggate;
User created.
SQL> grant create session to ggate;
Grant succeeded.
SQL> grant dba to ggate;
Grant succeeded.
SQL> alter system set undo_retention=86400 scope=both;
System altered.
SQL> grant flashback any table to ggate;
Grant succeeded.
SQL>
====>Source
GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.
GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (gg1.example.com) 5> add trandata scott.dept
=====>target
GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.
GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (gg1.example.com) 5> add trandata scott.dept
Note:creating extract and replicate process on both server.
====>Source
GGSCI (gg1.example.com) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (gg1.example.com) 4> edit param RX1 -----extract process
EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.dept;
GGSCI (gg1.example.com) 5> edit param DP1 -------datapump process
EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.dept;
GGSCI (gg1.example.com) 6> edit param REP1 ------replication process
REPLICAT rep1
USERID rx025n, PASSWORD *****
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.dept, TARGET scott.dept;
GGSCI (gg1.example.com) 7> ADD EXTRACT rx1, TRANLOG, BEGIN NOW
GGSCI (gg1.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1
GGSCI (gg1.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx
GGSCI (gg1.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt, EXTRACT dp1
GGSCI (gg1.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/rt CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.
GGSCI (gg1.example.com) 20> Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/rt
Extract: DP1
Seqno: 3
RBA: 1513
File Size: 100M
Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
Extract: EX1
Seqno: 3
RBA: 1243
File Size: 100M
========>targate
GGSCI (gg2.example.com) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (gg2.example.com) 4> edit param RX1 -----extract process
EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.dept;
GGSCI (gg2.example.com) 5> edit param DP1 -------datapump process
EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx
TABLE scott.dept;
GGSCI (gg2.example.com) 6> edit param REP1 ------replication process
REPLICAT rep1
USERID rx025n, PASSWORD *****
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.dept, TARGET scott.dept;
GGSCI (gg2.example.com) 7> ADD EXTRACT ex1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (gg2.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1
EXTTRAIL added.
GGSCI (gg2.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx
EXTRACT added.
GGSCI (gg2.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx, EXTRACT dp1
RMTTRAIL added
GGSCI (gg2.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/tx CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.
GGSCI (gg2.example.com) 30> Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/tx
Extract: DP1
Seqno: 0
RBA: 0
File Size: 100M
Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
Extract: EX1
Seqno: 0
RBA: 0
File Size: 100M
======>start gg extract,datapump process on source
- start manager process
- start extract process
- start datapump process
GGSCI (gg1.example.com) 33> start manager
Manager started.
GGSCI (gg1.example.com) 34> start EXTRACT EX1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (gg1.example.com) 35> start EXTRACT DP1
Sending START request to MANAGER ...
EXTRACT DP1 starting
GGSCI (gg1.example.com) 36> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:10
EXTRACT RUNNING RX1 00:00:00 00:00:03
REPLICAT STOPPED REP1 00:00:00 00:20:17
note: replicate process we will start after target extract and replication process.
======>start gg extract,datapump process on targate
- start manager process
- start extract process
- start datapump process
GGSCI (gg2.example.com) 47> start extract EX1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (gg2.example.com) 48> start extract DP1
Sending START request to MANAGER ...
EXTRACT DP1 starting
GGSCI (gg2.example.com) 49> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:07
EXTRACT RUNNING RX1 00:00:00 00:00:04
REPLICAT STOPPED REP1 00:00:00 00:00:09
note: replicate process we will start after target extract and replication process.
=======>start gg replicate process on target
GGSCI (gg2.example.com) 50> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg1.example.com) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:03
EXTRACT RUNNING RX1 00:00:00 00:00:01
REPLICAT RUNNING REP1 00:00:00 00:00:04
=====>start gg replicate process on source
GGSCI (gg1.example.com) 66> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg1.example.com) 67> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:07
EXTRACT RUNNING RX1 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:09
======>inserting one row in source database
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev1
SQL> conn scott
Enter password:
Connected.
SQL> show user
USER is "SCOTT"
SQL> set lines 220
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values(66,'marketing','new york');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
SQL>
GGSCI (gg1.example.com) 2> stats EXTRACT RX1
Sending STATS request to EXTRACT RX1 ...
Start of Statistics at 2015-12-25 01:41:36.
Output to /u01/app/oracle/product/gg/dirdat/rx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-12-25 01:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
End of Statistics.
=======>checking on target side
GGSCI (gg2.example.com) 51> stats REPLICAT REP1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2015-12-25 01:42:22.
Replicating from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-12-25 01:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
End of Statistics.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev2
SQL> conn scott
Enter password:
Connected.
SQL> set lines 220
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
SQL>
===>Now inserting value in target database.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev2
SQL> conn scott
Enter password:
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
SQL> insert into dept values(68,'testing','perth');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
68 testing perth
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
6 rows selected.
====>checking gg process
GGSCI (gg2.example.com) 2> stats RX1
Sending STATS request to EXTRACT RX1 ...
Start of Statistics at 2015-12-25 03:34:11.
Output to /u01/app/oracle/product/gg/dirdat/rx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (gg2.example.com) 3> stats DP1
Sending STATS request to EXTRACT DP1 ...
Start of Statistics at 2015-12-25 03:34:19.
Output to /u01/app/oracle/product/gg/dirdat/tx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
====>checking gg process on source
GGSCI (gg1.example.com) 7> stats REP1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2015-12-25 03:42:02.
Replicating from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
==>database validation
SQL> conn /as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev1
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
66 marketing new york
68 testing perth
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
******END****
No comments:
Post a Comment