Active – Active (Bi-Directional) Replication using Oracle Golden Gate 12c

Since it was announced in Early 2010 that Oracle GoldenGate will be the RoadMap for Replication for Oracle Database, The adoption of GoldenGate over Streams has been quite Rapid. The complexity of Streams has intimidated many adopters, specially for Multi-Master or Peer-to-Peer Replication. GoldenGate not only makes MultiMaster Replication easy but is so full of options and topologies that anybody who has used Golden Gate would most likely never go back to Oracle Streams.

In the Example below we will create a Bi-Directional Model where both the Tables have same name in different databases and can be used for INSERT’ing transactions. The CR( Conflict Resolution) is handled by GG itself.

You can download the latest version of GoldenGate from OTN >> http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

We will use the Sample Table EMP in Sample Schema SCOTT . Before we begin, the assumption is that you have installed and created Oracle Database 12c in both Site A and Site B for creating the replication, (This setup was done on Windows platform, there isn’t much difference between the Linux and Windows setup)

Site A : GGPRIM

————————

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

Site A : GGSTANDBY

————————-

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

With 12c the GoldenGate installation is GUI based. GG12c will need to be installed on both Sites and ensure that both databases are in archive log mode.

1. Enable Supplemental Logging
** Do Below Steps in Both Databases **

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> create user ggate identified by ggate;

SQL> grant dba to ggate;
SQL> conn scott/scott

2. At SITE A, Create the extract (EXT1) and data pump (DPUMP1) —

Site A Home Directory of GG : “D:\app\sql_admin\product\12.1.2\oggcore_1”

add extract ext1 tranlog begin now

add exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa extract ext1

add extract dpump1 exttrailsource D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa

— The below Directory is remote directory on other Database Server —

ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

edit params ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;

3. On SITE B Add Replicat (REP2) —

Site A Home Directory of GG : “F:\app\sql_admin\product\12.1.2\oggcore_1”

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep2 exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab checkpointtable ggate.ggschkpt
REPLICAT added
.

edit params rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

Create the extract (EXT2) and data pump (DPUMP2) on Site B

add extract ext2 tranlog begin now
EXTRACT added.

add exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac extract ext2
EXTTRAIL added.

add extract dpump2 exttrailsource F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
EXTRACT added.

— Below Directory is Remote Directory of Other Database Server —

add rmttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, extract dpump2
RMTTRAIL added.

edit params ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;

4. On SITE A, Add replicat (REP1) —

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep1 exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad checkpointtable ggate.ggschkpt
REPLICAT added.

edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

5. On both SITE A AND SITE B, add trandata, this steps will be done on both sites —

dblogin userid ggate password ggate
Successfully logged into database.

add trandata scott.emp

info trandata scott.emp

exit

6. At SITE A, Start the Extract and Data Pump process —

start mgr

start extract ext1
start extract dpump1
info extract ext1
info all

7. SITE B, Start the Extract and Data Pump process on Site B —

start extract ext2
start extract dpump2
info all

— SITE A —

start replicat rep1

status replicat rep1

— SITE B —

start replicat rep2

status replicat rep2

8. — ERROR in ggserr when starting extract —

If you get below error in starting extract on both sites “Operation not supported because enable_goldengate_replication is not set to true.” then set the parameter enable_goldengate_replication=true

— On Both Sites A and B —

sqlplus "/as sysdba"

alter system set enable_goldengate_replication=true scope=spfile;

Restart both the databases since it is a static parameter(this parameter is only in 11.2.0.4 onwards and in 12c)
Now restart the extract process on both Sites

start extract ext1

start extract ext2

— If you get Directory Level Errors, check on Both sides the RMTTRAIL is pointing to Remote Directory of other server respectively and Remove any Firewall Service blocking the ports servers on both —

-- In Site A --
ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

-- In Site B --
ADD RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, EXTRACT DPUMP2

Do this and Start the extract dpump1 and dpump2, replicat rep1 and rep2 in Both sites A and B

9. — Testing The Active Active Replication —

on Site A

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1111, 'SHADAB', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site b

On Site B

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1112, 'Yusuf', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site A

10.
————————————–
|SUMMARY OF DEFINITIONS ON BOTH SITES |
————————————–

There is a slight modification in definitions below, I created another table called tb_table_1 (code below) on both sites to demonstrate you can add as many tables as you want or even the full schema using * wildcard.

create table Tb_table_1(
user_id number primary key,
field_1 varchar2(30)
);

—- SITE A DEFINITIONS —

edit param mgr

PORT 7809

edit param ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;
TABLE scott.tb_table_1;

—- SITE B DEFINITIONS —

edit param mgr

PORT 7809

edit param ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;

Advertisements

10 Largest Tables in Oracle with Size Greater than 1GB

Query to find out size of the 10 Largest Tables sitting inside an Oracle database which is greater than 1GB in size


COLUMN segment_nm FORMAT A45
COLUMN used_size FORMAT A16
Set Lines 999
SELECT
segment_nm,
segment_type,
LPAD( CASE
WHEN bytes < 1024
THEN ROUND( bytes, 2 ) || ' B'
WHEN bytes < POWER( 1024, 2 )
THEN ROUND( ( bytes / 1024 ), 2 ) || ' KB'
WHEN bytes < POWER( 1024, 3)
THEN ROUND( ( bytes / 1024 / 1024 ), 2 ) || ' MB'
WHEN bytes < POWER( 1024, 4 )
THEN ROUND( ( bytes / 1024 / 1024 / 1024 ), 2 ) || ' GB'
ELSE ROUND( ( bytes / 1024 / 1024 / 1024 / 1024 ), 2 ) || ' TB'
END, 15 ) AS used_size,
tablespace_name
FROM
(
SELECT
owner || '.' || LOWER( segment_name ) AS segment_nm,
segment_type,
bytes,
tablespace_name,
DENSE_RANK() OVER ( ORDER BY bytes DESC ) AS dr
FROM
dba_segments
) A
WHERE
dr <= 10 /* top-10 may have more then 10 */
ORDER BY /* lots of ordering in cases of ties */
bytes DESC,
dr ASC,
segment_nm ASC;

Add string to query Oracle 11g

Suppose you have a query result and want to concatenate a string to that output for better readibility, it can be done using the Concat operator in SQL*PLUS

 

Select Name, Size  from Table ;

 

EMV_DATA                                          23
LOG_RECORD_HIST                          6
EMVX                                                       5
CARDHOLDER_TRAN                       4
EJ_LOG                                                   3
ISO_MSG                                                3
CARD_TRAN_IDX2                           2
ACCT_IX                                                2
CARD_TRAN_IDX                              2

 

Now if you want to add a string “GB” at the end of the result set of  second column the SQL statement will be as below

Select Name, Size || ‘ GB’  from Table;

EMV_DATA                                          23 GB
LOG_RECORD_HIST                         6 GB
EMVX                                                      5 GB
CARDHOLDER_TRAN                      4 GB
EJ_LOG                                                  3 GB
ISO_MSG                                               3 GB
CARD_TRAN_IDX2                          2 GB
ACCT_IX                                                2 GB
CARD_TRAN_IDX                             2 GB

 

 

 

 

Practical Oracle Security

One of the best books I have read on Oracle database security.It skips all the basics like what is a database and how it works etc, which most Oracle security books tend to emphasize on. It goes right into the meaty part by citing practical examples on how to secure the database. It’s a must read !!

Free Monitoring tool for Oracle Database : Mumbai

Just found an amazing monitoring tool for Oracle Databases. All it needs is an Oracle client installed on your workstation. It is a completely free. You can find it here : https://marcusmonnig.wordpress.com/mumbai/