In the following post we’ll understand to set up a replication using GoldenGate, for instruction on installing the software take a look on my small post on the same
Now we assume that the GG is installed and we have two servers on which Oracle databases are running, the GG Manager process is up and running on both these servers as GG is installed on both these servers, the TCP/IP network connectivity open from source server to target server’s manager port and basic understanding of GG replication flow which can be quickly revised from my post
Basic Replication Steps
Preparing the Source Database
STEP 1:- The Source database should be in archivelog mode, if not, follow the below steps:-
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOG
STEP 2:- Enable minimal supplemental logging:
SQL> alter database add supplemental log data;
Database altered.
STEP 3:- Turn off the database recyclebin feature and purge it.
SQL> PURGE DBA_RECYCLEBIN;
SQL> alter system set recyclebin=off scope=spfile;
DBA Recyclebin purged.
STEP 4:- Create GoldenGate Schema (tablespace/user) and grant necessary priveleges:-
SQL> create tablespace gg_tbs datafile ‘/u01/app/oracle/oradata/orcl/gg_tbs_data01.dbf’ size 100m;
Tablespace created.
SQL> create user ggate identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;
User created.
SQL> grant CREATE SESSION, CONNECT, RESOURCE, ALTER ANY TABLE, ALTER SYSTEM, CREATE TABLE, DBA, LOCK ANY TABLE, SELECT ANY TRANSACTION, FLASHBACK ANY TABLE to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.
STEP 5:- Now go to the GoldenGate home directory and run scripts for creating all necessary objects for replication.
$ cd /u01/app/oracle/product/gg/
$ sqlplus / as sysdba
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql
STEP 6:- Now edit the parameter file:-
$./ggsci
GGSCI 1> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate
STEP 7:- Now lets create the schema for replication on both source and target database.
SQL> create user source identified by source default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to source;
Grant succeeded.
SQL>
SQL> create user target identified by target default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to target;
Grant succeeded.
STEP 8:- Now Creating the directories for trail files on both servers and create directory for discard file on target server only.
Source server
mkdir /u01/app/oracle/product/gg/dirdat/tr
Target Server
mkdir /u01/app/oracle/product/gg/dirdat/tr
mkdir /u01/app/oracle/product/gg/discard
STEP 9:- Now, Configuring extract process on Source Database:-
$ ./ggsci
GGSCI 1> edit params mgr
PORT 7809
GGSCI 2> start manager
Manager started.
GGSCI (db.us.oracle.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
STEP 10:- Login into database and add additional information about primary keys into log files.
GGSCI 4> dblogin userid ggate
Password:
Successfully logged into database.
GGSCI 5> ADD SCHEMATRANDATA source
2015-08-03 10:30:20 INFO OGG-01788 SCHEMATRANDATA has been added on schema source.
GGSCI 6> add extract ext1, tranlog, begin now
EXTRACT added.
GGSCI 7> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1
EXTTRAIL added.
GGSCI 8> edit params ext1
extract ext1
userid ggate, password oracle
exttrail /u01/app/oracle/product/gg/dirdat/tr
ddl include mapped objname source.*;
table source.*;
GGSCI 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:01:29
GGSCI 10> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now
EXTRACT added.
GGSCI 11> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1
RMTTRAIL added.
GGSCI 12> edit params pump1
EXTRACT pump1
USERID ggate, PASSWORD oracle
RMTHOST db2, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr
PASSTHRU
table source.*;
GGSCI 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:02:33
EXTRACT STOPPED PUMP1 00:00:00 00:02:56
Preparing the Target Database
STEP 1:- Verify if the manager is running on the Target Server and Start if not already started.
$ ./ggsci
GGSCI 1> edit params mgr
PORT 7809
GGSCI 2> start manager
Manager started.
GGSCI 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
STEP 3:- Create a checkpoint table and change the GLOBAL file.
GGSCI 4> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE target.checkpoint
GGSCI 5> dblogin userid target
Password:
Successfully logged into database.
GGSCI 6> add checkpointtable target.checkpoint
Successfully created checkpoint table target.checkpoint.
STEP 4:- Add replicat process
GGSCI 8> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now
REPLICAT added.
GGSCI (db2) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID target, PASSWORD oracle
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 DDL
map source.*, target target.*;
GGSCI 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:01:32
GGSCI 11>
Starting Replication
At Source Server
STEP 1:- Start extract process
GGSCI 6> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI 7> start extract pump1
Sending START request to MANAGER …
EXTRACT PUMP1 starting
GGSCI 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:01
EXTRACT RUNNING PUMP1 00:00:00 00:01:01
At Destination Server
STEP 2:- Start Replicat Process
GGSCI 6> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (db2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
Finally our replication has been configured between source and destination DB, so now you can create objects under Source schema and it would get replicated to Target Schema. There are several features of GoldenGate which makes it far better than Oracle Streams and CDC.
Refrence
Now we assume that the GG is installed and we have two servers on which Oracle databases are running, the GG Manager process is up and running on both these servers as GG is installed on both these servers, the TCP/IP network connectivity open from source server to target server’s manager port and basic understanding of GG replication flow which can be quickly revised from my post
Basic Replication Steps
Preparing the Source Database
STEP 1:- The Source database should be in archivelog mode, if not, follow the below steps:-
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOG
STEP 2:- Enable minimal supplemental logging:
SQL> alter database add supplemental log data;
Database altered.
STEP 3:- Turn off the database recyclebin feature and purge it.
SQL> PURGE DBA_RECYCLEBIN;
SQL> alter system set recyclebin=off scope=spfile;
DBA Recyclebin purged.
STEP 4:- Create GoldenGate Schema (tablespace/user) and grant necessary priveleges:-
SQL> create tablespace gg_tbs datafile ‘/u01/app/oracle/oradata/orcl/gg_tbs_data01.dbf’ size 100m;
Tablespace created.
SQL> create user ggate identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;
User created.
SQL> grant CREATE SESSION, CONNECT, RESOURCE, ALTER ANY TABLE, ALTER SYSTEM, CREATE TABLE, DBA, LOCK ANY TABLE, SELECT ANY TRANSACTION, FLASHBACK ANY TABLE to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.
STEP 5:- Now go to the GoldenGate home directory and run scripts for creating all necessary objects for replication.
$ cd /u01/app/oracle/product/gg/
$ sqlplus / as sysdba
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql
STEP 6:- Now edit the parameter file:-
$./ggsci
GGSCI 1> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate
STEP 7:- Now lets create the schema for replication on both source and target database.
SQL> create user source identified by source default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to source;
Grant succeeded.
SQL>
SQL> create user target identified by target default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to target;
Grant succeeded.
STEP 8:- Now Creating the directories for trail files on both servers and create directory for discard file on target server only.
Source server
mkdir /u01/app/oracle/product/gg/dirdat/tr
Target Server
mkdir /u01/app/oracle/product/gg/dirdat/tr
mkdir /u01/app/oracle/product/gg/discard
STEP 9:- Now, Configuring extract process on Source Database:-
$ ./ggsci
GGSCI 1> edit params mgr
PORT 7809
GGSCI 2> start manager
Manager started.
GGSCI (db.us.oracle.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
STEP 10:- Login into database and add additional information about primary keys into log files.
GGSCI 4> dblogin userid ggate
Password:
Successfully logged into database.
GGSCI 5> ADD SCHEMATRANDATA source
2015-08-03 10:30:20 INFO OGG-01788 SCHEMATRANDATA has been added on schema source.
GGSCI 6> add extract ext1, tranlog, begin now
EXTRACT added.
GGSCI 7> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1
EXTTRAIL added.
GGSCI 8> edit params ext1
extract ext1
userid ggate, password oracle
exttrail /u01/app/oracle/product/gg/dirdat/tr
ddl include mapped objname source.*;
table source.*;
GGSCI 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:01:29
GGSCI 10> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now
EXTRACT added.
GGSCI 11> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1
RMTTRAIL added.
GGSCI 12> edit params pump1
EXTRACT pump1
USERID ggate, PASSWORD oracle
RMTHOST db2, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr
PASSTHRU
table source.*;
GGSCI 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:02:33
EXTRACT STOPPED PUMP1 00:00:00 00:02:56
Preparing the Target Database
STEP 1:- Verify if the manager is running on the Target Server and Start if not already started.
$ ./ggsci
GGSCI 1> edit params mgr
PORT 7809
GGSCI 2> start manager
Manager started.
GGSCI 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
STEP 3:- Create a checkpoint table and change the GLOBAL file.
GGSCI 4> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE target.checkpoint
GGSCI 5> dblogin userid target
Password:
Successfully logged into database.
GGSCI 6> add checkpointtable target.checkpoint
Successfully created checkpoint table target.checkpoint.
STEP 4:- Add replicat process
GGSCI 8> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now
REPLICAT added.
GGSCI (db2) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID target, PASSWORD oracle
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 DDL
map source.*, target target.*;
GGSCI 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:01:32
GGSCI 11>
Starting Replication
At Source Server
STEP 1:- Start extract process
GGSCI 6> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI 7> start extract pump1
Sending START request to MANAGER …
EXTRACT PUMP1 starting
GGSCI 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:01
EXTRACT RUNNING PUMP1 00:00:00 00:01:01
At Destination Server
STEP 2:- Start Replicat Process
GGSCI 6> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (db2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
Finally our replication has been configured between source and destination DB, so now you can create objects under Source schema and it would get replicated to Target Schema. There are several features of GoldenGate which makes it far better than Oracle Streams and CDC.
Refrence
No comments:
Post a Comment