{"id":4269,"date":"2022-02-11T07:00:00","date_gmt":"2022-02-11T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4269"},"modified":"2024-10-07T13:41:21","modified_gmt":"2024-10-07T10:41:21","slug":"pos-kanoyme-replicate-pinakes-apo-tin-oracle-database-pros-t","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-replicate-pinakes-apo-tin-oracle-database-pros-t\/","title":{"rendered":"How to replicate tables from Oracle Database to SQL Server using GoldenGate"},"content":{"rendered":"

In this article we will see how we can copy table entries from<\/strong> Oracle Database<\/strong> which is on Unix <\/strong>to SQL Server that is<\/strong> on Windows <\/strong>with its use Oracle GoldenGate<\/strong>.<\/p>\n\n\n\n

What is GoldenGate<\/h2>\n\n\n\n

Oracle GoldenGate is a tool that allows us to copy and format data between databases, without affecting the operating system or RDBMS being used. The updating of the tables can be either in one direction with a target (unidirectional), or to several targets (broadcast), or bidirectional.<\/p>\n\n\n\n

For example, we can transfer table data using GoldenGate from Oracle to Oracle, from Oracle to SQL Server, from SQL Server to SQL Server, or from Oracle simultaneously to SQL Server, MySQL, DB2 and PostgreSQL.<\/p>\n\n\n\n

How GoldenGate works<\/h2>\n\n\n\n

GoldenGate uses three processes: Extract<\/strong>, Data Pump<\/strong> (in case we use a remote server) and Replicat<\/strong>. <\/p>\n\n\n\n

The Extract <\/strong>is responsible for recording the committed transactions from the tables we have defined and storing them in a file called local trail<\/strong>. Then the Data Pump<\/strong> sends the data from the local trail file to the remote server via TCP\/IP and writes the data to the file named remote trail<\/strong>. In the end the Replicat <\/strong>reads the remote trail and applies the changes to the target databases.<\/p>\n\n\n\n

\"\"
docs.oracle.com<\/figcaption><\/figure>\n\n\n\n

The prerequisites<\/h2>\n\n\n\n

For starters we should have set it up Oracle Database Software<\/strong> on the primary and on the standby server and Oracle Database<\/strong> in primary to archivelog mode<\/strong><\/em>. How we do these installations can be seen step by step in the article here<\/a>.<\/p>\n\n\n\n

We should download the version Oracle GoldenGate<\/strong> separately for each system from here<\/a>. The Oracle on Linux x86-64 edition for Oracle Server and the SQL Server on Windows x86-64 edition for SQL Server.<\/p>\n\n\n\n

On the Windows Server that has SQL Server we will need to download and install it Oracle Database Client<\/strong> from here<\/a>.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The installation \/ configuration in the source<\/h2>\n\n\n\n

On Unix Oracle Database server (source)<\/h4>\n\n\n\n

For example, I have created an Oracle Database that will be the source with the name oracledb:<\/p>\n\n\n\n

su oracle\ndbca<\/code><\/pre>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
After the installation is complete, we connect to the database with sqlplus and make the necessary changes for GoldenGate to work:<\/p>\n\n\n\n
export ORACLE_SID=oracledb\n\nsqlplus \/ as sysdba<\/code><\/pre>\n\n\n\n
shutdown immediate;\nstartup mount;\n\nalter database archivelog;\nalter database add supplemental log data;\nalter database force logging;\nalter database open;\nalter system switch logfile;\nalter system set enable_goldengate_replication=true scope=both;\n<\/pre>\n\n\n\n
Then we stay in sqlplus and create the user that GoldenGate will connect to the database:<\/p>\n\n\n\n
create user ggadmin\n  identified by passw0rd\n  default tablespace USERS\n  temporary tablespace TEMP\n  quota unlimited on users;\n-- Grant\/Revoke role privileges \ngrant connect to ggadmin;\ngrant resource to ggadmin;\n-- Grant\/Revoke system privileges \ngrant select any table to ggadmin;\ngrant select any dictionary to ggadmin;\ngrant flashback any table to ggadmin;\ngrant create session to ggadmin;\ngrant alter session to ggadmin;\nexec dbms_goldengate_auth.grant_admin_privilege('ggadmin');\n<\/pre>\n\n\n\n
For the needs of the example I have created the user in the database STRATOS <\/em>with the table CUSTOMERS<\/em> and it's the one I want replicated:<\/p>\n\n\n\n
create user STRATOS IDENTIFIED BY PASSW0RD\n  default tablespace USERS\n  temporary tablespace TEMP\n  profile DEFAULT\n  password expire\n  quota unlimited on users;\n-- Grant\/Revoke role privileges \ngrant connect to STRATOS;\ngrant resource to STRATOS;\n-- Table Creation\ncreate table stratos.customers (id int, name varchar(50));\ninsert into stratos.customers values (1,'Stratos');\ninsert into stratos.customers values (2,'Nikos');\ninsert into stratos.customers values (3,'George');\ncommit;<\/pre>\n\n\n\n
Then we add to tnsnames.ora<\/strong> the following record with the connection information to the instance by putting as host <\/em>the machine name and as service_name<\/em> the name of the database:<\/p>\n\n\n\n
vi $ORACLE_HOME\/network\/admin\/tnsnames.ora\n\n<\/code><\/pre>\n\n\n\n
oracledb=\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledev2)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVICE_NAME = oracledb)\n    )\n  )<\/pre>\n\n\n\n
If we don't have DNS servers<\/em> we should go to hosts file<\/em> and set a record with the IP of the remote SQL Server:<\/p>\n\n\n\n
su root\nvi \/etc\/hosts<\/code><\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
We create the folder where the GoldenGate installation will be located, go to the folder where we downloaded the GoldenGate setup and unzip it:<\/p>\n\n\n\n
su oracle\n\nmkdir \/oracle\/app\/oracle\/goldengate\n\ncd $HOME\/Downloads\n\nunzip *.zip\n\n<\/code><\/pre>\n\n\n\n
After the unzip is complete, go into the files of the folder and run the installer:<\/p>\n\n\n\n
cd fbo_ggs_Linux_x64_Oracle_shiphome\/Disk1\n\n.\/runInstaller<\/code><\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
In the Software Location<\/strong> put the folder we created before to contain the installation and set the door, or default is 7809:<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
When the installation is complete, the path of GoldenGate and the tnsnames<\/em>.ora go to bash_profile <\/strong>and add the following:<\/p>\n\n\n\n
vi $HOME\/.bash_profile\n\n<\/code><\/pre>\n\n\n\n
export GGHOME=\/oracle\/app\/oracle\/goldengate\nexport TNS_ADMIN=$ORACLE_HOME\/network\/admin\nexport PATH=$PATH:$ORACLE_HOME\/bin:$GGHOME<\/pre>\n\n\n\n
To load the changes we either logout login or run the following:<\/p>\n\n\n\n
\nsource ~\/.bash_profile<\/code><\/pre>\n\n\n\n
Now we can connect to the GoldenGate interface by running the following:<\/p>\n\n\n\n
ggsci<\/code><\/pre>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
We write to create the folders in case the installer has not already done so:<\/p>\n\n\n\n
create subdirs<\/pre>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
We modify the GoldenGate Manager parameter file and add the following:<\/p>\n\n\n\n
edit params mgr<\/pre>\n\n\n\n
\nPORT 7809\nPURGEOLDEXTRACTS .\/dirdat\/*, MINKEEPHOURS 2\nAUTOSTART EXTRACT *\nAUTORESTART EXTRACT *, RETRIES 10, WAITMINUTES 5, RESETMINUTES 60<\/code><\/pre>\n\n\n\n