Use Goldengate to do Initial Load (Part 18 of series, Using Oracle Goldengate to do Initial Load of Oracle Goldengate on Linux)

For His sake, you assumed this body; see God always with you. God is pervading the water, the land and the sky; He sees all with His Glance of Grace.


Index of all the posts of Gurpartap Singh's Blog

Lets do the initial load, though you can can use many methods like export/import and transportable tablespaces but here I will use Goldengate to do the initial load.

For transportable tablespaces use Note : 371156.1
It works pretty good with ASM to ASM too.

For cross platform transporable tablespaces asm to asm see post 24.

Post 24 of series, Goldengate initial load using Cross platform transportable tablespaces Solaris to Linux


Lets encrypt the password first using the following statement on goldengate as:

encrypt password gurpartap

Output as:

oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.



GGSCI (rac1.rac.meditate.com) 1> encrypt password gurpartap
No key specified, using default key...

Encrypted password: AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI


GGSCI (rac1.rac.meditate.com) 2> exit

oracle : rac1.rac.meditate.com : @crs : /u01/app/oracle/product/goldengate
$

------------------------------------------------------------------------------------------------------
GGSCI (rac1.rac.meditate.com) 1> encrypt password goldengate
No key specified, using default key...

Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC


GGSCI (rac1.rac.meditate.com) 2>

------------------------------------------------------------------------------------------------------





Now lets create the parameter file for extracting initial load.

On Source

1) Create the Initial data extract process ‘initial'

ADD EXTRACT initial, SOURCEISTABLE

SOURCEISTABLE : Designates Extract as an initial load process extracting records directly from the source tables.
So, it doesnot read the transaction log files of the database



2) Create the parameter file for the extract group load1

We need to define the following parameters in the parameter file:


EXTRACT : Name of the extract group
USERID/PASSWORD : The database user in the source database which has been configured for Extract i.e "goldengate"
RMTHOST : This will be the hostname/ip_address of the target system
MGRPORT : The port where the Manager process is running
TABLE : This is a very flexible parameter where we can define wildcards to exclude and include tables and columns for replication or
extraction. We will use this in complex replication later.



EDIT PARAMS initial

EXTRACT initial
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7809
RMTTASK replicat, GROUP onetime
TABLE meditate.EMPLOYEE;

----------------------------------------------------------------------------------------------------

Output is as:

$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.



GGSCI (rac1.rac.meditate.com) 1> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING


GGSCI (rac1.rac.meditate.com) 2> ADD EXTRACT initial, SOURCEISTABLE
EXTRACT added.


GGSCI (rac1.rac.meditate.com) 3> EDIT PARAMS initial


EXTRACT initial
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
RMTHOST goldengate, MGRPORT 7909
RMTTASK replicat, GROUP onetime
TABLE meditate.EMPLOYEE;


-----------------------------------------------------------------------------------------------------







On Target:
Please note I have done all the steps of installation and created the user goldengate as:

create tablespace goldengate
datafile '/u01/app/oracle/datafiles/emrep/goldengate.dbf' size 300m;

create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp;

and then grant the following permisssions to the goldengate user as:

grant connect,resource to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;


Output is as:


SQL> create tablespace goldengate
datafile '/u01/app/oracle/datafiles/emrep/goldengate.dbf' size 300m;
2

Tablespace created.

SQL> SQL> create user goldengate identified by goldengate
default tablespace goldengate
temporary tablespace temp; 2 3

User created.

SQL> grant connect,resource,dba to goldengate;
grant select any dictionary, select any table to goldengate;
grant create table to goldengate;
grant flashback any table to goldengate;
grant execute on dbms_flashback to goldengate;
grant execute on utl_file to goldengate;


Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL> grant dba to goldengate;
Grant succeeded.

---------------------------------------------------------------------------------

Now we wil create the user where we will replicate the changes as:

create user consciousness identified by consciousness
default tablespace users
temporary tablespace temp;
grant connect,resource,dba to consciousness;

and then create the table as source, under this schema with extracted DDL from source we did earlier as:

CREATE TABLE "CONSCIOUSNESS"."EMPLOYEE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
);
---------------------------------------------------------------------------------------------------

Output is as:

oracle : goldengate : @emrep : /u01/app/oracle/flash_area/EMREP/archivelog
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 20:07:55 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> altre system switch logfile;
SQL> alter system switch logfile;

System altered.

SQL> conn / as sysdba
Connected.
SQL> create user consciousness identified by consciousness
default tablespace users
temporary tablespace temp; 2 3

User created.

SQL> grant connect,resource,dba to consciousness;

Grant succeeded.

SQL>


Now create the table under this schema as:


oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate
$ sqlplus consciousness/consciousness

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 20:58:58 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE "CONSCIOUSNESS"."EMPLOYEE"
2 ( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
3 4 5 6 "HIREDATE" DATE,
"SAL" NUMBER(7,2),
7 8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0)
10 );

Table created.

SQL>



-------------------------------------------------------------------------------------


Password encryption:

oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:50:50

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.



GGSCI (goldengate) 1> encrypt password goldengate
No key specified, using default key...

Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC


GGSCI (goldengate) 2>


--------------------------------------------------------------------------------



Create the replicat as:



Create the initial data load task 'onetime'

SPECIALRUN identifies the initial-load Replicat as a one-time run, not a continuous
process.


GGSCI (devu007) 1> ADD REPLICAT onetime, SPECIALRUN
REPLICAT added.

4) Create the parameter file for the Replicat group, onetime

REPLICAT : name of the Replicat group that we just created for the initial data load
USERID/PASSWORD : database credentials for the Replicat user i.e. "goldengate"
ASSUMETARGETDEFS : this means that the source table structure exactly matches the target database table structure. Just the simple replication.
we will do the complex replication later.
MAP : with GoldenGate we can have the target database structure entirely different from that of the source in terms of tablenames
as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which
is same in this case. But this parameter can do amazing complex mappings for replications.

GGSCI (devu007) 2> EDIT PARAMS onetime

"/u01/oracle/software/goldengate/dirprm/rep4.prm" [New file]

REPLICAT onetime
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ASSUMETARGETDEFS
MAP meditate.employees, TARGET consciousness.customers;




--------------------------------------------------------------------------------------------------------------------------

Now lets start the extract on source and before that make sure that the manager process is already running
on both source and target:

GGSCI (rac1.rac.meditate.com) 1> start extract initial

Sending START request to MANAGER ...
EXTRACT INITIAL starting


GGSCI (rac1.rac.meditate.com) 2>


You can see the info of this extract by using command "info extract initial"

To see the logs of this commad look at the logfile at:
/u01/app/oracle/product/goldengate/ggserr.log
where /u01/app/oracle/product/goldengate is the location of the goldengate binaries.


and you will see the following:

2010-11-07 21:51:48 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract initial.
2010-11-07 21:51:48 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.176 (START EXTRACT INITIAL ).
2010-11-07 21:51:48 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT INITIAL starting.
2010-11-07 21:51:48 GGS INFO 414 Oracle GoldenGate Capture for Oracle, initial.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2010-11-07 21:51:48 GGS INFO 310 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL starting.
2010-11-07 21:51:48 GGS WARNING 109 Oracle GoldenGate Capture for Oracle, initial.prm: No unique key is defined for table EMPLOYEE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2010-11-07 21:51:48 GGS INFO 310 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL started.
2010-11-07 21:52:05 GGS INFO 312 Oracle GoldenGate Capture for Oracle, initial.prm: EXTRACT INITIAL stopped normally.




---------------------------------------------------------------------------------



On target side you will see the following:

2010-11-07 21:21:05 GGS INFO 301 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host 192.168.1.176 (START REPLICAT ONETIME CPU -1 PRI -1 PARAMS ).
2010-11-07 21:21:05 GGS INFO 302 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started replicat task process (Port 7841).
2010-11-07 21:21:05 GGS INFO 310 Oracle GoldenGate Delivery for Oracle: REPLICAT task started by manager (port 7841).
2010-11-07 21:21:22 GGS INFO 373 Oracle GoldenGate Delivery for Oracle: Connected to 192.168.1.176:18802.
2010-11-07 21:21:22 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME starting.
2010-11-07 21:21:23 GGS INFO 320 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME started.
2010-11-07 21:21:23 GGS WARNING 109 Oracle GoldenGate Delivery for Oracle, onetime.prm: No unique key is defined for table EMPLOYEE. All viable c olumns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2010-11-07 21:21:28 GGS INFO 322 Oracle GoldenGate Delivery for Oracle, onetime.prm: REPLICAT ONETIME stopped normally.



---------------------------------------------------------------------------------------------


And after this enquire on the target and you will the records have been replicated as:

oracle : goldengate : @emrep : /u01/app/oracle/product/goldengate/dirprm
$ sqlplus consciousness/consciousness

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 7 21:21:47 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from employee;

COUNT(*)
----------
14

SQL>


----------------------------------------------------------------------------------------------------

Now I will create the index on both source and the target as:


Target:

SQL> show user
USER is "MEDITATE"
SQL> CREATE UNIQUE INDEX PK_employee ON employee("EMPNO");

Index created.

SQL>

and source:

SQL> show user
USER is "CONSCIOUSNESS"
SQL> CREATE UNIQUE INDEX PK_employee ON employee("EMPNO");

Index created.

SQL>


The mind and body become spotlessly pure, enshrining love for the True Lord. One who dwells upon the Feet of the Supreme Lord God has truly performed all meditations and austerities.