Index of all the posts of Gurpartap Singh's Blog
Now we will configure the online change synchronization :
I am just setting up the commection and testing it to see if I can connect to ASM with TNS as we need this to read from redologs/archivelogs
from this location later.
Here is the tnsping, i.e. my tns entry and also you can see the connection is working.
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ tnsping RAC1ASM
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2010 15:58:22
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u02/app/11.2.0.1/grid/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = +ASM)))
OK (0 msec)
oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ sqlplus sys/gurpartap@RAC1ASM as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 15:58:38 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL>
Our source is: (Server : rac1, Database: simar, Instance: simar1, Schema: Meditate, Tablename: Employee)
Our target is: (Server : goldengate, Database: emrep, Instance: emrep, Schema: CONSCIOUSNESS, Tablename: Employee)
These are the steps that we will do:
a. Create a GoldenGate Checkpoint table
b. Create an Extract group
c. Create a parameter file for the online Extract group
d. Create a Trail
e. Create a Replicat group
f. Create a parameter file for the online Replicat group
a. Create a GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat
process will start processing after any kind of error or shutdown. This ensures data integrity and a record
of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.
Source:
------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 12> EDIT PARAMS ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.check_pt_tab
-------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 13> DBLOGIN USERID goldengate,password goldengate
Successfully logged into database.
GGSCI (rac1.rac.meditate.com) 14> ADD CHECKPOINTTABLE goldengate.check_pt_tab
Successfully created checkpoint table GOLDENGATE.CHECK_PT_TAB.
GGSCI (rac1.rac.meditate.com) 15>
SQL> conn goldengate/goldengate
Connected.
SQL> desc CHECK_PT_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
SQL>
----------------------------------------------------------------------------
Target:
GGSCI (goldengate) 1> EDIT PARAMS ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.check_pt_tab
GGSCI (goldengate) 2> DBLOGIN USERID goldengate,password goldengate
Successfully logged into database.
GGSCI (goldengate) 3> ADD CHECKPOINTTABLE goldengate.check_pt_tab
Successfully created checkpoint table GOLDENGATE.CHECK_PT_TAB.
GGSCI (goldengate) 4>
SQL> conn goldengate/goldengate
Connected.
SQL> desc check_pt_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
SQL>
b. Now lets create the online extract group as:
ADD EXTRACT elord1, TRANLOG, threads 3, BEGIN NOW
Then create a trial where this extract will write. It would be in the Goldengate software location on the target system
and here the trail files will be created. I am using the prefix rm. I am using thread 3 as our source database is a 3 node RAC database.
If you don`t use the thread parameter you will see the following error.
2010-11-08 22:40:05 GGS ERROR 500 Oracle GoldenGate Capture for Oracle, elord1.prm: The number of Oracle redo threads (3) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT
Output of above command is:
GGSCI (rac1.rac.meditate.com) 21> ADD EXTRACT elord1, TRANLOG, threads 3, BEGIN NOW
EXTRACT added.
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm, EXTRACT elord1
Output is as:
GGSCI (rac1.rac.meditate.com) 17> ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm, EXTRACT elord1
RMTTRAIL added.
c. Now we will create hte parameter file for the online extract group as:
Please note I am using parameter TRANLOGOPTIONS and connecting to ASM as our redologs are in ASM and if you forget this option
you will see the following error:
2010-11-08 22:46:18 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract elord1.
2010-11-08 22:46:18 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ELORD1 ).
2010-11-08 22:46:18 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ELORD1 starting.
2010-11-08 22:46:19 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 starting.
2010-11-08 22:46:26 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, elord1.prm: Default thread stack size: 33554432.
2010-11-08 22:46:48 GGS ERROR 500 Oracle GoldenGate Capture for Oracle, elord1.prm: No valid log files for current redo sequence 15, thread 1, error retrieving redo file name for sequence 15, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2010-11-08 22:42:03.
2010-11-08 22:46:48 GGS ERROR 190 Oracle GoldenGate Capture for Oracle, elord1.prm: PROCESS ABENDING.
--------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 3> encrypt password gurpartap
No key specified, using default key...
Encrypted password: AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI
GGSCI (rac1.rac.meditate.com) 4>
------------------------
EDIT PARAMS elord1
EXTRACT elord1
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
TABLE meditate.employee;
Here is the output of logs as:
2010-11-08 22:33:45 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT elord1 TRANLOG, threads 3, BEGIN NOW.
2010-11-08 22:34:00 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm EXTRACT elord1.
2010-11-08 22:34:09 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): EDIT PARAMS elord1.
d. Now on target system we will create the replicat group as follows, just need to note that the RMTTRAIL on
source is same as EXTTRAIL on the target:
ADD REPLICAT rlord1, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
Output is as:
GGSCI (goldengate) 8> ADD REPLICAT rlord1, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/rm
REPLICAT added.
e. Now we will create the parameter file of the replicat group as:
EDIT PARAMS rlord1
REPLICAT rlord1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
MAP meditate.employee, TARGET CONSCIOUSNESS.employee;
Now is the time to start the extract and replicat and then test.
Lets start the extract as:
On Source execute the following on GGSCI prompt:
start extract elord1
info all
STATUS EXTRACT elord1
info extract elord1
info extract elord1, detail
Output is as:
GGSCI (rac1.rac.meditate.com) 5> start extract elord1
Sending START request to MANAGER ...
EXTRACT ELORD1 starting
GGSCI (rac1.rac.meditate.com) 7> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:24:31
GGSCI (rac1.rac.meditate.com) 8> STATUS EXTRACT elord1
EXTRACT ELORD1: RUNNING
GGSCI (rac1.rac.meditate.com) 9> info extract elord1
EXTRACT ELORD1 Last Started 2010-11-08 23:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:08 Thread 1, Seqno 15, RBA 26684416
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:09 Thread 2, Seqno 15, RBA 36956672
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:09 Thread 3, Seqno 11, RBA 29586448
GGSCI (rac1.rac.meditate.com) 10> info extract elord1, detail
EXTRACT ELORD1 Last Started 2010-11-08 23:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 1, Seqno 15, RBA 26690048
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 2, Seqno 15, RBA 36961792
Log Read Checkpoint Oracle Redo Logs
2010-11-08 23:08:19 Thread 3, Seqno 11, RBA 29592064
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/app/oracle/product/goldengate/dirdat/rm 0 1010 10
Extract Source Begin End
+RECV1/simar/onlinelog/group_5.261.734045029 2010-11-08 22:42 2010-11-08 23:08
Not Available * Initialized * 2010-11-08 22:42
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/ELORD1.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/elord1.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/ELORD1.cpe
Process file /u01/app/oracle/product/goldengate/dirpcs/ELORD1.pce
Stdout file /u01/app/oracle/product/goldengate/dirout/ELORD1.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (rac1.rac.meditate.com) 11>
In logs on source you will see:
2010-11-08 23:04:15 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT ELORD1 ).
2010-11-08 23:04:15 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT ELORD1 starting.
2010-11-08 23:04:15 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 starting.
2010-11-08 23:06:23 GGS INFO Z0-016 Oracle GoldenGate Capture for Oracle, elord1.prm: Default thread stack size: 33554432.
2010-11-08 23:06:38 GGS INFO 310 Oracle GoldenGate Capture for Oracle, elord1.prm: EXTRACT ELORD1 started.
2010-11-08 23:06:43 GGS INFO 406 Oracle GoldenGate Capture for Oracle, elord1.prm: Socket buffer size set to 27985 (flush size 27985).
In logs on target you will see:
2010-11-08 22:35:53 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START SERVER CPU -1 PRI -1 PARAMS ).
2010-11-08 22:35:53 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started collector process (Port 7840).
2010-11-08 22:35:53 GGS INFO 373 Oracle GoldenGate Collector, port 7840: Waiting for connection (started dynamically).
2010-11-08 22:36:10 GGS INFO 373 Oracle GoldenGate Collector: Connected to 192.168.1.176:37003.
2010-11-08 22:36:10 GGS INFO 370 Oracle GoldenGate Collector, port 7840: Opening /u01/app/oracle/product/goldengate/dirdat/rm000000 (byte -1, current EOF 0).
---------------------------------------------------
Now on Target execute the following as:
START REPLICAT rlord1
status REPLICAT rlord1
INFO REPLICAT rlord1
INFO REPLICAT rlord1, detail
Output is as:
GGSCI (goldengate) 10> START REPLICAT rlord1
Sending START request to MANAGER ...
REPLICAT RLORD1 starting
GGSCI (goldengate) 11> status REPLICAT rlord1
REPLICAT RLORD1: RUNNING
GGSCI (goldengate) 12> INFO REPLICAT rlord1
REPLICAT RLORD1 Last Started 2010-11-08 22:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/rm000000
First Record RBA 1010
GGSCI (goldengate) 13> INFO REPLICAT rlord1, detail
REPLICAT RLORD1 Last Started 2010-11-08 22:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File /u01/app/oracle/product/goldengate/dirdat/rm000000
First Record RBA 1010
Extract Source Begin End
/u01/app/oracle/product/goldengate/dirdat/rm000000 * Initialized * First Record
/u01/app/oracle/product/goldengate/dirdat/rm000000 * Initialized * First Record
Current directory /u01/app/oracle/product/goldengate
Report file /u01/app/oracle/product/goldengate/dirrpt/RLORD1.rpt
Parameter file /u01/app/oracle/product/goldengate/dirprm/rlord1.prm
Checkpoint file /u01/app/oracle/product/goldengate/dirchk/RLORD1.cpr
Checkpoint table GGS_OWNER.GGSCHKPT
Process file /u01/app/oracle/product/goldengate/dirpcs/RLORD1.pcr
Stdout file /u01/app/oracle/product/goldengate/dirout/RLORD1.out
Error log /u01/app/oracle/product/goldengate/ggserr.log
GGSCI (goldengate) 14>
In logs you will see the following:
2010-11-08 22:44:21 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): START REPLICAT rlord1.
2010-11-08 22:44:21 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.169 (START REPLICAT RLORD1 ).
2010-11-08 22:44:21 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RLORD1 starting.
2010-11-08 22:44:22 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rlord1.prm: REPLICAT RLORD1 starting.
2010-11-08 22:44:23 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, rlord1.prm: REPLICAT RLORD1 started.
--------------------------------
Now testing if our configuration is replicating or not:
On source I did:
SQL> update EMPLOYEE set COMM=100 where empno=7844;
1 row updated.
SQL> commit;
Commit complete.
SQL>
You will see the following in logs of Source as:
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery initialization completed for target file /u01/app/oracle/product/goldengate/dirdat/rm000000, at RBA 1500.
2010-11-08 23:34:06 GGS INFO Z0-05M Oracle GoldenGate Capture for Oracle, elord1.prm: Output file /u01/app/oracle/product/goldengate/dirdat/rm is using format RELEASE 10.4.
2010-11-08 23:34:06 GGS INFO 224 Oracle GoldenGate Capture for Oracle, elord1.prm: Rolling over remote file /u01/app/oracle/product/goldengate/dirdat/rm000001.
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery completed for target file /u01/app/oracle/product/goldengate/dirdat/rm000001, at RBA 1010.
2010-11-08 23:34:06 GGS INFO 112 Oracle GoldenGate Capture for Oracle, elord1.prm: Recovery completed for all targets.
and then on target it was:
SQL> show user
USER is "CONSCIOUSNESS"
SQL> select * from employee;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 100 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Why do you say, "Mine, mine"? Look to God, who has given it to you. It is certain that you must arise and depart, and leave behind your hundreds of thousands and millions.
No comments:
Post a Comment