Setting up a simple GoldenGate replication configuration between two single node databases_Oracle Goldengate 11g Complete Cookbook-QQ阅读男生玄幻网

上QQ阅读APP看书，第一时间看更新

Setting up a simple GoldenGate replication configuration between two single node databases

In this recipe we will look at how to configure GoldenGate for continuous replication between two single node databases.

Getting ready

For this setup, we make the following assumptions:

The source and target database have already been set up.
Both the databases have a schema called SCOTT which is in a consistent state. No transformations will be performed in this recipe.
Oracle GoldenGate binaries are already installed in both sites.
The extract method used in this example will be classic capture.
Supplemental Logging is enabled at database and table level for all the objects to be replicated.

How to do it...

We will follow these steps to set up this replication configuration.

Perform the following steps in the source database:

Set up the GoldenGate user. Run the following command in the source database:

sqlplus sys/**** as sysdba
CREATE USER GGATE_ADMIN identified by GGATE_ADMIN;
GRANT CREATE SESSION, ALTER SESSION to GGATE_ADMIN;
GRANT ALTER SYSTEM TO GGATE_ADMIN;
GRANT CONNECT, RESOURCE to GGATE_ADMIN;
GRANT SELECT ANY DICTIONARY to GGATE_ADMIN;
GRANT FLASHBACK ANY TABLE to GGATE_ADMIN;
GRANT SELECT ON DBA_CLUSTERS TO GGATE_ADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO GGATE_ADMIN;
GRANT SELECT ANY TRANSACTION To GGATE_ADMIN;
GRANT SELECT ON SCOTT.DEPT to GGATE_ADMIN;
GRANT SELECT ON SCOTT.EMP to GGATE_ADMIN;
GRANT SELECT ON SCOTT.BONUS to GGATE_ADMIN;
GRANT SELECT ON SCOTT.SALGRADE to GGATE_ADMIN;

Set up the source GoldenGate manager process:

./ggsci
EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
PURGEOLDEXTRACTS /u01/app/ggate/dirdat/st*, USECHECKPOINTS, MINKEEPHOURS

Start the source manager process:
```
START MGR
```

Create a GoldenGate classic Extract process in the source environment:

./ggsci
EDIT PARAMS EGGTEST1
EXTRACT EGGTEST1
USERID GGATE_ADMIN@DBORATEST, PASSWORD GGATE_ADMIN
EXTTRAIL /u01/app/ggate/dirdat/st
TABLE scott.*;

Create a GoldenGate Datapump process in the source environment:

./ggsci
EDIT PARAMS PGGTEST1
EXTRACT PGGTEST1
USERID GGATE_ADMIN@DBORATEST, PASSWORD GGATE_ADMIN
RMTHOST stdby1-ol6-112 , MGRPORT 8809
RMTTRAIL /u01/app/ggate/dirdat/rt
TABLE scott.*;

Add the Extract process to the source manager configuration:
```
ADD EXTRACT EGGTEST1, TRANLOG, BEGIN NOW
```

Add the local trail to the Extract process:

ADD EXTTRAIL /u01/app/ggate/dirdat/st, EXTRACT EGGTEST1

Add the Datapump process to the source manager configuration:

ADD EXTRACT PGGTEST1, EXTTRAILSOURCE /u01/app/ggate/dirdat/st

Add the remote trail location to the Datapump process:

ADD RMTTRAIL /u01/app/ggate/dirdat/rt, EXTRACT PGGTEST1

Perform the following steps in the target database:

Set up the GoldenGate user:

sqlplus sys/**** as sysdba
CREATE USER GGATE_ADMIN identified by GGATE_ADMIN DEFAULT TABLESPACE GGATE_ADMIN_DAT;
ALTER USER GGATE_ADMIN QUOTA UNLIMITED ON GGATE_ADMIN_DAT;
GRANT CREATE SESSION, ALTER SESSION to GGATE_ADMIN;
GRANT CONNECT, RESOURCE to GGATE_ADMIN;
GRANT SELECT ANY DICTIONARY to GGATE_ADMIN;
GRANT SELECT ANY TABLE TO GGATE_ADMIN;
GRANT CREATE TABLE TO GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.DEPT to GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.EMP to GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.SALGRADE to GGATE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.BONUS to GGATE_ADMIN;

Set up target GoldenGate manager process:

./ggsci
EDIT PARAMS MGR
PORT 8809
DYNAMICPORTLIST 8810-8820, 8830
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /u01/app/ggate/dirdat/rt*, USECHECKPOINTS, MINKEEPHOURS 2

Start the target manager process:
```
START MGR
```

Create a checkpoint table in the target database:

./ggsci
DBLOGIN, USERID GGATE_ADMIN@TGORTEST PASSWORD *****
ADD CHECKPOINTTABLE

Create a GoldenGate Replicat process in the target environment:

./ggsci
EDIT PARAMS RGGTEST1
REPLICAT RGGTEST1
USERID GGATE_ADMIN@TGORTEST, PASSWORD GGATE_ADMIN
DISCARDFILE /u01/app/ggate/dirrpt/RGGTEST1.dsc,append,MEGABYTES 500
ASSUMETARGETDEFS
MAP SCOTT.*, TARGET SCOTT.*;

Add the Replicat process to the target manager configuration:

ADD REPLICAT RGGTEST1, EXTTRAIL /u01/app/ggate/dirdat/rt, BEGIN NOW, CHECKPOINTTABLE CHECKPOINT

Perform the following steps in the source database:

Start the Extract and Datapump process:
```
START EXTRACT EGGTEST1
START EXTRACT PGGTEST1
```
Perform the following steps in the target database:
Start the Replicat process:
```
START REPLICAT RGGTEST1
```

How it works...

The replication configuration requires set up in both the source and target environments. In each database, GoldenGate requires an Admin user which it connects to the database. In the preceding steps, we first set up a GoldenGate Admin user in the source database and grant it necessary privileges. After this we create a GoldenGate manager instance in the source environment and start it. Then we create an Extract and a Datapump process in the source environment. Then we add the Extract process, extract trail, and Datapump process to the manager configuration. We also need to associate the remote trail location to the Datapump process to enable it to transfer the files to the target server.

After this we set up the GoldenGate Admin user in the target database with necessary privileges. We then create the manager instance in the target environment and start it. After this we create a Replicat process in the target environment. The Replicat processes require a checkpoint table in the target database to maintain checkpoints. We then create a checkpoint table in the GGATE_ADMIN schema. Then we create a Replicat process and add it to the target manager configuration.

Once all the setup is done, we need to start the Extract, Datapump, and Replicat processes to enable the continuous replication.

There's more...

The Datapump process, as we know, is always an optional process in the GoldenGate configuration. This process is mainly used to offload the task of TCP/IP network transfer and data transformation from the main Extract process. Using Datapump also provides recoverability benefits in case of network failures.

Although a Datapump process is always used in the production environments, one of the practical scenarios where it offers further advantages is when replicating between systems which are on two different networks. In such an environment, you can configure an Extract and Datapump on the source server. The source server Datapump then ships the trail files to an intermediary server, which can see both networks and where there is an additional Datapump that reads the trail files, and transfers them on to the target server. The Datapump process can also do the transformations on the intermediary server as long as both the source and target databases use the same character set.