1)Prerequisite:
Assumption that two database (Source and Destination) are available with archive log enabled. Both source & target hosts should be able to do ping/ssh/ each other.
Environment:
Particulars
Source DB:source
Target DB :target
DB Version
12.1.2.0.0
12.1.2.0.0
2)PATH=$ORACLE_HOME/bin:/u01/app/GGS:$PATH; export PATH
3) Login into Golden Gate
cd /u01/app/GGS
4)$./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
5) Create the mandatory files and directories if does not exist:
GGSCI (source_server) 1> create subdirs
GGSCI (source_server) 2> exit
$ mkdir /u01/app/GGS/discard
This will complete the Golden Gate software Installation on source_server
Repeat all the above steps on target_server to install the software on second host.
Preparing the database for replication:
Source Database:(source)
---------------------------
Switch the database to Archive log mode
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open
Enable Minimal Database Level Supplemental Logging
SQL> alter database add supplemental log data;
Prepare the database to support DDL Replication
a) Turn Off Recyclebin for The Database and then bounce it.
SQL> alter system set recyclebin=off scope=spfile;
6) Create New Schema for DDL Support Replication And Grant a Necessary Privileges To New User
SQL> create user gg_user identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect, resource, unlimited tablespace to gg_user;
SQL> grant execute on utl_file to gg_user;
7) Go to Golden Gate Installed location (in our scenario /u01/app/GGS) and then run the following Golden Gate inbuild scripts for creating all necessary objects to support DDL replication.
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:GG_USER
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GG_USER
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:GG_USER
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.
WARNING: Tablespace GG_TBS does not have AUTOEXTEND enabled.
/****************Need to create separate TBS for this***********************/
Using GG_USER 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 GG_USER
CLEAR_TRACE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED
STAYMETADATA IN TRIGGER
-----------------------------------
OFF
DDL TRIGGER SQL TRACING
-----------------------------------
0
DDL TRIGGER TRACE LEVEL
-----------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/target/target/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
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 pref erred 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:GG_USER
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 gg_user;
SQL>@ddl_enable.sql
Trigger altered.
Note: There are two ways/approach to connect the golden gate on target.
1. Use the same user (gg_user) which created on source database by using tns service name and database link.
2. Create an individual/same user on target.
Here we following the create an individual/same user approach, so the above points b) and c) to be executed on target database also.
Create Test Schemas for Replication.
I will create a replication from schema sender to schema receiver (on target_db database).
Source Database:
SQL> create user sender identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect,resource, unlimited tablespace to sender;
On Destination Database target_db (target_server):
SQL> create user receiver identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect, resource, unlimited tablespace to receiver;
Setup Replication:
The goal is to create DDL and transform DML from the sender schema on the SOURCE database to receiver schema on the destination target_db database.
1. Create and Start Manager on the Source and the Destination.
Source: SOURCE
[oracle@SOURCE ~]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (source_server) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (source_server) 2> edit params mgr
It will open the parameter file for manager. Enter the following in the file:
PORT 7809
Then Save And Quit The File.
edit params ./GLOBALS
GGSCHEMA gg_user
CHECKPOINTTABLE gg_user.ckptab
GGSCI (source_server) 1> start mgr
Manager started.
GGSCI (source_server) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
Note:
(a)The status shows it is running
(b)Repeat the same on TARGET to create manager process.
(c) 7809 is Default Port for Golden Gate
2. Create the extract group on the source side (SOURCE)
GGSCI (source_server) 1> add extract ex_grp, tranlog,begin now
EXTRACT added.
GGSCI (source_server) 2> add exttrail /u01/app/GGS/dirdat/lt, extract ex_grp
EXTTRAIL added.
GGSCI (source_server) 3> edit params ex_grp
Enter the following for the content of extract parameter ex_grp and then save and exit.
EXTRACT ex_grp
USERID gg_user, PASSWORD oracle
DBOPTIONS ALLOWNOLOGGING
SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.1.2/db_1")
SETENV (ORACLE_SID = "SOURCE")
RMTHOST <TARGET_HOST_IP>, MGRPORT 7809
RMTTRAIL /u01/app/GGS/dirdat/lt
DISCARDFILE discard.txt, APPEND
DDL include mapped objname sender.*;
DDLOPTIONS ADDTRANDATA
table sender.*;
3. Create replicat on the destination side (target_db)
$ cd /u01/app/GGS
$ ./ggsci
To create a checkpoint Table in the target database.
GGSCI (target_server) 1> edit params ./GLOBAL
CHECKPOINTTABLE gg_user.checkpoint
Save and quit the file.
GGSCI (target_server) 2> dblogin userid gg_user, Password oracle
Successfully logged into database.
GGSCI (target_server) 3>add checkpointtable gg_user.checkpoint
Successfully created checkpointtablegg_user.CHECKPOINT.
Create replicat group: target_db
GGSCI (target_server) 4>add replicat rep_grp2, exttrail /u01/app/GGS/dirdat/lt, checkpointtable gg_user.checkpoint
REPLICAT added.
Create Parameter File For Replicat:
GGSCI (target_server) 5> edit params rep_grp2
And put following lines in the parameter file:
replicat rep_grp2
ASSUMETARGETDEFS
userid gg_user, password oracle
SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.1.2/db_1")
SETENV (ORACLE_SID = "target_db")
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
discardfile /u01/app/GGS/discard/rep_grp2_discard.txt, append, megabytes 10
map sender.*, target receiver.*;
Save and quit
4. Start Extract and Replicat:
Make sure that manager is up and running before starting these.
Source: SOURCE
GGSCI (source_server) 14> start extract ex_grp
Destination: target_db
GSCI (target_server) 15> start replicat rep_grp2
5. Check the extract and replicat running in source and target system
Source: SOURCE
1 GGSCI (source_server) 8> info all
2 Program Status Group Lag Time Since Chkpt
3 MANAGER RUNNING
4vEXTRACT RUNNING ex_grp 00:00:00 00:12:25
Destination: target_db
1
2
3
4
GGSCI (target_server) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING rep_grp2 00:00:00 00:12:30
Once all the processes are running means that replication is created successfully.
RESULTS
Now we can check our replication.
We will create some table in the sender schema on the source, insert some rows, and check how it will replicate to destination side.
1. Source Database:
SQL>conn sender/oracle
SQL> select * from table_name;
Select * from table_name
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table table_name (id number primary key, name varchar2(50));
Table created.
SQL> insert into table_name values (1,'testable_name');
1 row created.
SQL> insert into table_name values (2,'test_apple');
1 row created.
SQL> commit;
Commit complete.
2. Destination Database:
SQL> conn receiver/oracle
SQL> select * from table_name;
ID NAME
---------- --------------------------------------------------
1 testable_name
2 test_apple
Our Golden Gate replication is now running fine. The table was created on the target_db side and data were replicated.
No comments:
Post a Comment