Step by Step Installing and Configuring
Oracle 11gR2 GoldenGate
In this article I'd like to illustrate the installation of Oracle
GoldenGate on an Oracle Enterprise Linux 6.5. What we know about Oracle DB
replication by using Golden Gate ?
This first post is about installing GG software and creating the
simplest replication using only the “extract” and “replicat” processes.
GG can support a number of different business requirements like:
·
Business Continuity and High Availability
·
Data migrations and upgrades
·
Decision Support Systems and Data Warehousing
· Data integration and
consolidation
Oracle GoldenGate provides very fast
replication of heterogeneous databases by reading transaction logs and writing
the changes to one or more target databases. There are five processes involved
in a typical environment:
Manager:
Starts and stops the other processes on both the source and
target hosts. Not required once Extract or Replicate is running.
Initial Load:
Optional. Used to populate the target tables one time. It
can read either from the source tables directly or from ASCII files. This OBE
will not use an Initial Load.
Extract:
Runs on the source to capture transaction data to trail
files. There are two versions of Extract: Classic (this OBE) and Integrated.
Data Pump:
Optional, but highly recommended. Sends trail files from the
source to the target over an IP network. Technically it is a secondary Extract.
Replicate:
Delivers data to the target database. Normally the Replicate
runs on the target.
Downloading
Oracle GoldenGate
· Navigate to http://edelivery.oracle.com. The Oracle Software Delivery Cloud page appears.
· Click Sign-in/Register.
The Media Pack Search page appears.
· On the Media Pack Search page, do the
following:
- Click the Select Product Pack drop-down control and, from the list, select Oracle Fusion Middleware.
- Click the Platform drop-down control and, from the list, select the platform on which you are installing Oracle GoldenGate.
- Click Go.
Now Let’s start installing Oracle GG.
1.
Set bash for path by using vi command (for both database)
# Oracle bash_profile Environment Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=excellentJob1; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
DB_HOME=$ORACLE_BASE/product/11.2.0.3/db; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME_LISTNER=$ORACLE_HOME export ORACLE_HOME_LISTNER
GG_HOME=/u01/ogg1; export GG_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$GG_HOME:$ORACLE_HOME/OPatch:$PATH:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/lib:$GG_HOME; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
2.
So, Run the following command to accept bash
profile(for both database)
[oracle@excellentJob1 ]$ export PATH=$PATH:/u01/ogg
[oracle@excellentJob1 ]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ogg
3.
We have to create directories for both servers.
[oracle@excellentJob1 ]$ mkdir
/u01/ogg1/
4. [oracle@excellentJob1
]$ cd /u01/ogg1
5. [oracle@excellentJob1 ogg1]$ unzip V34339-01.zip
Archive: V34339-01.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc
inflating: Oracle GoldenGate_11.2.1.0.3_README.txt
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf
6. [oracle@excellentJob1 ogg1]$ tar -xvpf fbo_ggs_Linux_x64_ora11g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
... many lines
ulg.sql
usrdecs.h
zlib.txt
7.
[oracle@excellentJob1
ogg1]$ ./ggsci
GGSCI (excellentJob1) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/ogg1
Parameter files /u01/ogg1/dirprm: created
Report files /u01/ogg1/dirrpt: created
Checkpoint files /u01/ogg1/dirchk: created
Process status files /u01/ogg1/dirpcs: created
SQL script files /u01/ogg1/dirsql: created
Database definitions files /u01/ogg1/dirdef: created
Extract data files /u01/ogg1/dirdat: created
Temporary files /u01/ogg1/dirtmp: created
Stdout files /u01/ogg1/dirout: created
GGSCI (excellentJob1) 1> exit
[oracle@excellentJob1 ogg1]$
Do the same steps for the other host. Unpack the software in its directory on host2.
Create the installation directory to receive the Oracle GoldenGate software.
[oracle@excellentJob2]$ cd /u01/app/oracle
[oracle@excellentJob2 oracle]$ mkdir ogg2
[oracle@excellentJob2 ogg2]$ unzip V34339-01.zip
Archive: V34339-01.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc
inflating: Oracle GoldenGate_11.2.1.0.3_README.txt
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf
[oracle@excellentJob2 ]$ tar -xvpf fbo_ggs_Linux_x64_ora11g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
... many lines
ulg.sql
usrdecs.h
zlib.txt
[oracle@excellentJob2 ]$ cd /u01/app/oracle/ogg2
1. [oracle@excellentJob2
ogg2]$ ./ggsci
GGSCI (excellentJob2) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/oracle/ogg2
Parameter files /u01/app/oracle/ogg2/dirprm: created
Report files /u01/app/oracle/ogg2/dirrpt: created
Checkpoint files /u01/app/oracle/ogg2/dirchk: created
Process status files /u01/app/oracle/ogg2/dirpcs: created
SQL script files /u01/app/oracle/ogg2/dirsql: created
Database definitions files /u01/app/oracle/ogg2/dirdef: created
Extract data files /u01/app/oracle/ogg2/dirdat: created
Temporary files /u01/app/oracle/ogg2/dirtmp: created
Stdout files /u01/app/oracle/ogg2/dirout: created
GGSCI (excellentJob2) 1> exit
.1 Configuring the Oracle 11gR2 Databases (for both database)
This section must be done in SQL*Plus as sysdba.
The database should be on archive log mode
[oracle@excellentJob1 ogg1]$ sqlplus / as sysdba (for both database)
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
Verify that supplemental logging and forced logging are set properly. (for both database)
SQL> alter system set recyclebin=off scope=spfile;(make sure that recycle is off)
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> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
Create Tablespace and the administrator, user/schema owners. (for first database)
SQL> create tablespace goldengate datafile size 500m autoextend on next 10m maxsize unlimited;
Tablespace created.
SQL> CREATE USER sender IDENTIFIED BY veysi1 default tablespace goldengate;
User created.
SQL> CREATE USER oggadm1 IDENTIFIED BY veysi1;
User created.
SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO sender;
Grant succeeded.
SQL> GRANT dba TO oggadm1;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'OGGADM1',privile
ge_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE);
PL/SQL procedure successfully completed.
SQL> exit
Create the administrator and user/schema owners. (for second database)
SQL> CREATE USER receiver IDENTIFIED BY veysi1 default tablespace goldengate;
User created.
SQL> CREATE USER oggadm2 IDENTIFIED BY veysi1;
User created.
SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO sender;
Grant succeeded.
SQL> GRANT dba TO oggadm2;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'OGGADM2',privile
ge_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE);
PL/SQL procedure successfully completed.
SQL> exit
Now Run scripts for creating all necessary objects for support ddl replication: (for both database)
[oracle@excellentJob1 ogg1]$ cd $GG_HOME
[oracle@excellentJob1 ogg1]$ sqlplus / as sysdba
SQL> @marker_setup.sql;
Marker setup script
You will be prompted for the name of a schema for the Oracle
GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:sender
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql;
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate
database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled.
For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:sender
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate
metadata tables ...
Check complete.
Using GOLDENGATE as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
CLEAR_TRACE STATUS:
Line/pos Error
---------- ------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
---------- ------------------------
No errors No errors
.
..
...
DDL TRIGGER TRACE LEVEL
-----------------------------------
0
LOCATION OF DDL TRACE FILE
-----------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
-----------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
--
SQL> @role_setup.sql;
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql
script to change the gg_role parameter to the preferred name.
(Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:sender
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI,
and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
--
SQL> grant GGS_GGSUSER_ROLE to sender;
Grant succeeded.
SQL> @ddl_enable.sql;
Trigger altered.
Creating Startup Files and Managers (for both database)
Create the GLOBALS
file on host1 in the Oracle GoldenGate installation directory using your text
editor of choice.
It is possible to create and/or edit the GLOBALS file from inside GGSCI by prepending
the name with "./".
[oracle@excellentJob1 ogg1]$ vi GLOBALS
CheckpointTable oggadm1.oggchkpt
[oracle@excellentJob1]$ cd $GG_HOME
[oracle@excellentJob1 ogg1]$ ggsci
GGSCI (excellentJob1) 1> edit param mgr
-- Write the port by “vi”
PORT 7809
GGSCI (excellentJob1) 2> start manager
Manager started.
GGSCI (excellentJob1) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
-- Starting "extract"
GGSCI (excellentJob1) 4> add extract Ext1, tranlog, begin now
EXTRACT added.
-- Adding TRIAL
GGSCI (excellentJob1)) 5> ADD EXTTRAIL /u01/ogg1/dirdat/ex, EXTRACT Ext1
EXTTRAIL added.
GGSCI (excellentJob1) 6> ADD RMTTRAIL /u01/ogg1/dirdat/lt, EXTRACT EXT1
RMTTRAIL added.
GGSCI (excellentJob1) 7> edit params ext1
-- Set Parameter
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.3/db")
SETENV (ORACLE_SID = "orcl")
EXTRACT ext1
USERID sender, PASSWORD veysi1
--EXTTRAIL /u01/ogg1/dirdat/ex
RMTHOST excellentJob1, MGRPORT 7809
ddl include mapped objname source.*;
RMTTRAIL /u01/ogg1/dirdat/lt
TABLE source.*;
-- Check the services
GGSCI (excellentJob1) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED Ext1 00:00:00 00:05:01
-- Now start on TARGET
[oracle@excellentJob2 ]$ cd $GG_HOME
[oracle@excellentJob2 ogg2]$ ggsci
GGSCI (excellentJob2) 1> dblogin userid receiver
Password:
Successfully logged into database.
GGSCI (excellentJob2) 2> add checkpointtable receiver.checkpoint
Successfully created checkpoint table receiver.checkpoint.
GGSCI (excellentJob2) 3> add replicat Repl1,
exttrail /u01/app/ogg2/dirdat/lt,checkpointtable receiver.checkpoint
REPLICAT added
GGSCI (excellentJob2) 4> edit param Repl1
--SET PARAMETER
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.3/db")
SETENV (ORACLE_SID = "orcl")
REPLICAT Repl1
ASSUMETARGETDEFS
userid receiver, password veysi1
discardfile /u01/app/ogg2/discard/Repl1.txt, append, megabytes 20
DDL
map source.*, target target.*;
-- Starting Extract source db
GGSCI (excellentJob1) 1> start extract Ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (excellentJob1) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING Ext1 00:00:00 00:00:07
On target start Replicat
GGSCI (excellentJob2) 1> start replicat Repl1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (excellentJob2) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING Repl1 00:00:00 00:00:09
Also We can create script and doing the same Replications (other option)
Create the optional startup.oby (Obey)
file on host1 in the Oracle GoldenGate installation directory. These are GGSCI commands that are done almost every time you start GGSCI, and they do not persist between sessions, so you will find yourself entering them many, many times, and therefore it is convenient to create a startup obey file.
[oracle@excellentJob1 ogg1]$ vi startup.oby
DBLogin UserID oggadm1@orcl, Password veysi1
Start Mgr
Info Mgr
Info CheckpointTable
Set Editor gedit
Create the Manager parameter (mgr.prm)
file on host11 in dirprm/. Start GGSCI. Edit the file with no extension. Add the two lines (plus comments).
[oracle@excellentJob1 ogg1]$./ggsci
GGSCI (excellentJob2) 1> Edit Param mgr
Port 7809
PurgeOldExtracts ./dirdat/*, UseCheckpoints
GGSCI (excellentJob2) 1> Info mgr
Manager is DOWN!Start the Manager on host1 using the Obey files. Alternatively, you could type each of the lines every time you start GGSCI. (for both database)
GGSCI (excellentJob1) 3> Obey startup.oby
GGSCI (excellentJob1) 5> DBLogin UserID oggadm1@orcl, Password veysi1
Successfully logged into database.
GGSCI (excellentJob1)) 6> Start Mgr
Manager started.
GGSCI (excellentJob1) 7> Info Mgr
Manager is running .
GGSCI (excellentJob1) 8> Info CheckpointTable
No checkpoint table specified, using GLOBALS specification (oggadm1.oggchkpt)...
Checkpoint table oggadm1.oggchkpt does not exist.
GGSCI (excellentJob1) 9> Set Editor gedit
GGSCI (excellentJob1) 10>
Create the checkpoint table on both database
GGSCI (excellentJob1)) 10> Add CheckpointTable
No checkpoint table specified, using GLOBALS specification (oggadm1.oggchkpt).
Successfully created checkpoint table oggadm1.oggchkpt.
GGSCI (excellentJob1) 11> Exit
[oracle@excellentJob1 ogg1]$
No comments:
Post a Comment