Thinking Out Loud

December 2, 2017

Goldengate 12.3 Automatic CDR

Filed under: 12.2,GoldenGate,oracle — mdinh @ 11:51 pm

Automatic Conflict Detection and Resolution

Requirements: GoldenGate 12c (12.3.0.1) and Oracle Database 12c Release 2 (12.2) and later.

Automatic conflict detection and resolution does not require application changes for the following reasons:

  • Oracle Database automatically creates and maintains invisible timestamp columns.
  • Inserts, updates, and deletes use the delete tombstone log table to determine if a row was deleted.
  • LOB column conflicts can be detected.
  • Oracle Database automatically configures supplemental logging on required columns.

I have not had the chance to play with this yet and just only notice the documentation has been updated with details.

 

 

Advertisements

November 23, 2017

CRS-2674: Start of dbfs_mount failed

Filed under: 12c,GoldenGate,oracle,RAC — mdinh @ 1:04 am

$ crsctl start resource dbfs_mount
CRS-2672: Attempting to start ‘dbfs_mount’ on ‘node2’
CRS-2672: Attempting to start ‘dbfs_mount’ on ‘node1’
CRS-2674: Start of ‘dbfs_mount’ on ‘node1’ failed
CRS-2679: Attempting to clean ‘dbfs_mount’ on ‘node1’
CRS-2674: Start of ‘dbfs_mount’ on ‘node2’ failed
CRS-2679: Attempting to clean ‘dbfs_mount’ on ‘node2’
CRS-2681: Clean of ‘dbfs_mount’ on ‘node1’ succeeded
CRS-2681: Clean of ‘dbfs_mount’ on ‘node2’ succeeded
CRS-4000: Command Start failed, or completed with errors.

Check to make sure DBFS_USER password is not expired.

November 5, 2017

GoldenGate Naming Convention P03

Filed under: GoldenGate — mdinh @ 4:44 pm

GoldenGate Naming Convention P01
GoldenGate Naming Convention P02

Here I provide an example for how I would implement 3-way replication.
I used capitalization for some clarity in trail – not tested yet.

N-way Replication calculations:
Primary Extract for each silo: 1
Pump Extract for each silo: N-1 
Replicats for each silo: N-1 
Total proceses for each silo: 2N-1
Sequence start value: (1001-100N), increment by 100

++++++++++

3-way Replication:
Primary Extract for each silo: 1
Pump Extract for each silo: 3-1=2
Replicats for each silo: 3-1=2
Total proceses for each silo: 2*3-1=5
Sequence start value: (1001,1002,1003) increment by 100

++++++++++

(Silo 1 - NYPRD) E_NY(aa) | pump (aB):(aC) | replicate (bA):(cA) | 1001+100 (sequence)
(Silo 2 - DCPRD) E_DC(bb) | pump (bA):(bC) | replicate (aB):(cB) | 1002+100 (sequence)
(Silo 3 - STDBY) E_SB(cc) | pump (cA):(cB) | replicAte (aC)|(bC) | 1003+100 (sequence)

++++++++++

E_NY1 (aa)

-- Pump include all other silos except current silo
P_NY2 (aB)
P_NY3 (aC)  

-- ReplicAt include all other silos except current silo
R_DC2 (bA)
R_SB3 (cA)

++++++++++

E_DC2 (bb)

P_DC1 (bA)
P_DC3 (bC)

R_NY1 (aB)
R_SB3 (cB)

++++++++++

E_SB3 (cc)

P_SB1 (cA)
P_SB2 (cB)

R_DC2 (bC)
R_NY1 (aC)

++++++++++

Stop replication from NYPRD:
(Silo 1) stop *NY* (stops E_NY1/P_NY2/P_NY3)

-- This may be optional depending on requirements.
-- If nothing is extracted, then nothing is replicated.
(Silo 2) stop *NY* (stops R_NY1) 
(Silo 3) stop *NY* (stops R_NY1)

November 4, 2017

GoldenGate Naming Convention P02

Filed under: GoldenGate — mdinh @ 1:05 pm

GoldenGate Naming Convention P01

Bidirectional replication:

E_10G (write to aa) | P_10G (read from aa, write to ab) | R_10G (read from ab, write to 12C DB)
E_12C (write to bb) | P_12C (read from bb, write to ba) | R_12C (read from ba, write to 10G DB)

Source (10G DB)     | Target (12C DB)
-------------------------------------
E_10G [>aa]         | E_12C [>bb]
P_10G [>ab]         | P_12C [>ba]
R_12C [<ba]         | R_10G [<ab]

Create same process name for extract, pump, replicat.

Using the example above:
stop 10G replication, stop *10G at source and target.
stop 12C replication, stop *12C at source and target.

Another method.

Source (10G DB)     | Target (12C DB)
-------------------------------------
E_10G [>aa]         | E_12C [>bb]
P_10G [>ab]         | P_12C [>ba]
R_10G [<ba]         | R_12C [<ab]

Process name was created based on DB versions.

Using the example above:
stop 10G replication, stop E_10G,P_10G at source and stop R_12C at target.
stop 12C replication, stop E_12C,P_12C at source and stop R_10G at target.

Of the 2 methods, which do you prefer?

Splitting extracts:

Source      | Target
-------------------------------------
E_USR [>aa] | R_JOE [<az]
P_JOE [>az] | R_SUE [<ay]
P_SUE [>ay] | R_AMY [<ax]
P_AMY [>ax] |

How does one know where source is from? You don’t unless you comment the parameters.

Example: RAC environment where VIP is used for PUMP

EXTRACT e_hawk
-- CHECKPARAMS
-- ADD EXTRACT e_hawk, INTEGRATED TRANLOG, BEGIN NOW
-- ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500

EXTRACT p_hawk
-- CHECKPARAMS
-- Target: host03/04
-- ADD EXTRACT p_hawk, EXTTRAILSOURCE ./dirdat/aa
-- ADD RMTTRAIL ./dirdat/ab, EXTRACT p_hawk, MEGABYTES 500
RMTHOST OGG_VIP MGRPORT 7801, TCPBUFSIZE 1048576, TCPFLUSHBYTES 1048576 

REPLICAT r_hawk
-- CHECKPARAMS
-- Source: host01/02
-- REGISTER REPLICAT r_hawk DATABASE
-- ADD REPLICAT r_hawk, INTEGRATED, EXTTRAIL ./dirdat/ab

In conclusion, there is really no best practice, but some thought and planning do help.

Goldengate XAG HAS

Filed under: GoldenGate — mdinh @ 12:53 am

If you install GI for SI DB, then you might as well install XAG for OGG.

Imagine if there is a Vagrant to put all of this together?

[oracle@db-asm-1 xag]$ mkdir -p /u01/app/oracle/xag

[oracle@db-asm-1 xag]$ ./xagsetup.sh --install --directory /u01/app/oracle/xag
Installing Oracle Grid Infrastructure Agents on: db-asm-1
Done.

[oracle@db-asm-1 ~]$ cd /u01/app/oracle/xag/bin/

[oracle@db-asm-1 bin]$ ./agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 8.1.0

[oracle@db-asm-1 bin]$ ./agctl query deployment
The Oracle Grid Infrastructure Agents deployment is standalone

[oracle@db-asm-1 bin]$ ./agctl add goldengate --help
Adds Goldengate instance to Oracle Clusterware.

[oracle@db-asm-1 bin]$ ./agctl add goldengate ogg_amer \
> --instance_type dual --databases ora.amer.db \
> --gg_home /u01/app/oracle/amer/ogg/12.3.0_ora12c \
>  --oracle_home /u01/app/oracle/12.2.0.1/db1

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is not running

[oracle@db-asm-1 bin]$ ./agctl start goldengate ogg_amer

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is running on db-asm-1

[oracle@db-asm-1 bin]$ ./agctl config goldengate
XAG-212: Instance '' is not yet registered.

[oracle@db-asm-1 bin]$ ./agctl config goldengate ogg_amer
GoldenGate location is: /u01/app/oracle/amer/ogg/12.3.0_ora12c
GoldenGate instance type is: dual
ORACLE_HOME location is: /u01/app/oracle/12.2.0.1/db1
Databases needed: ora.amer.db
EXTRACT groups to monitor:
REPLICAT groups to monitor:
Critical EXTRACT groups:
Critical REPLICAT groups:
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no
[oracle@db-asm-1 bin]$

oracle@db-asm-1:+ASM:/home/oracle
$ ./crs_stat.sh
The Oracle base remains unchanged with value /u01/app/oracle
NAME                                          TARGET     STATE           SERVER       STATE_DETAILS
-------------------------                     ---------- ----------      ------------ ------------------
                                              Name       Target          State        Server State
ora.CRS.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.DATA.dg                                   ONLINE     ONLINE          db-asm-1     STABLE
ora.FRA.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.LISTENER.lsnr                             ONLINE     ONLINE          db-asm-1     STABLE
ora.asm                                       ONLINE     ONLINE          db-asm-1     Started,STABLE
ora.ons                                       OFFLINE    OFFLINE         db-asm-1     STABLE
ora.amer.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.cssd                                      ONLINE     ONLINE          db-asm-1     STABLE
ora.diskmon                                   OFFLINE    OFFLINE         STABLE
ora.euro.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.evmd                                      ONLINE     ONLINE          db-asm-1     STABLE
xag.ogg_amer.goldengate                       ONLINE     ONLINE          db-asm-1     STABLE

oracle@db-asm-1:+ASM:/home/oracle
$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.euro.db' on 'db-asm-1'
CRS-2673: Attempting to stop 'xag.ogg_amer.goldengate' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'db-asm-1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.euro.db' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.CRS.dg' on 'db-asm-1' succeeded
CRS-2677: Stop of 'xag.ogg_amer.goldengate' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.amer.db' on 'db-asm-1'
CRS-2677: Stop of 'ora.amer.db' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.DATA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.FRA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'db-asm-1'
CRS-2677: Stop of 'ora.evmd' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.asm' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'db-asm-1'
CRS-2677: Stop of 'ora.cssd' on 'db-asm-1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'db-asm-1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

November 3, 2017

GoldenGate Naming Convention P01

Filed under: GoldenGate — mdinh @ 10:33 pm

I had a nice discussion with teammates about standards.

It’s wrong if there are no standards or naming conventions; otherwise, let your imagination run wild.

Hence, before you embark, think about it as it will make life much easier.

For prompt: I like to know what ORACLE_SID for environment.

oracle@db-asm-1:amer:/home/oracle
oracle@db-asm-1:euro:/home/oracle

For Goldengate: I did it this way because there are 2 DBs / 2 GGs for the same host.

Why ora12c? There are 2 options when installing Goldengate: ORA11g|ORA12c

/u01/app/oracle/amer/ogg/12.3.0_ora12c
/u01/app/oracle/euro/ogg/12.3.0_ora12c

There was discussion ogg/gg/ggs – doesn’t really matter.

ogg may seem redundant if gg is installed for Oracle DB / Client.

Where should it reside? I had planned for /u02 and /u03 but Vagrant was not being nice to me.

Why different mount? There are GG directories and trails which will fill up.

I like to KISS and avoid soft links.

One thing that does annoy is using $GGHOME.

This may raise the question, “Why GG_HOME and not GGHOME?”

Why? ORACLE_BASE, ORACLE_HOME, ORACLE_SID, GRID_HOME, etc…

There’s a pattern here.

Make life simple, use aliases.

oracle@db-asm-1:+ASM:/home/oracle
$ cat .bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
export PATH=$PATH:$HOME/.local/bin:$HOME/bin
. ~/.alias

oracle@db-asm-1:+ASM:/home/oracle
$ cat .alias

alias amer='source ~/.amer'
alias euro='source ~/.euro'
alias ggs='cd $GG_HOME'

oracle@db-asm-1:+ASM:/home/oracle
$ cat .amer

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=amer
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

oracle@db-asm-1:+ASM:/home/oracle
$ amer

The Oracle base remains unchanged with value /u01/app/oracle
oracle@db-asm-1:amer:/home/oracle
$ env|egrep 'ORACLE|HOME'
ORACLE_SID=amer
ORACLE_BASE=/u01/app/oracle
GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
PS1=\u@\h:${ORACLE_SID}:${PWD}\n$
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/12.2.0.1/db1

oracle@db-asm-1:amer:/home/oracle
$ cat .euro

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=euro
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

oracle@db-asm-1:amer:/home/oracle
$ euro

The Oracle base remains unchanged with value /u01/app/oracle
oracle@db-asm-1:euro:/home/oracle
$ env|egrep 'ORACLE|HOME'
ORACLE_SID=euro
ORACLE_BASE=/u01/app/oracle
GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
PS1=\u@\h:${ORACLE_SID}:${PWD}\n$
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/12.2.0.1/db1

oracle@db-asm-1:euro:/u01/app/oracle/euro/ogg/12.3.0_ora12c
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (db-asm-1) 1> exit

$ grep ORA oggcore.rsp

# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g

October 24, 2017

DB 12.2 NF for Goldengate

Filed under: 12.2,GoldenGate — mdinh @ 12:30 pm

Logical Replication

Error Handling Improvements for Oracle GoldenGate Integrated Replicat
Database views can manage and display details about Oracle GoldenGate conflict resolution methods, 
DBA_APPLY_REPERROR_HANDLERS 
DBA_APPLY_HANDLECOLLISIONS
DBA_APPLY_DML_CONF_HANDLERS
DBA_APPLY_DML_CONF_COLUMNS

A new capture parameter, ANNOTATE_DDL_SIZE, 
is available for Oracle GoldenGate integrated capture to specify the size of the buffer that describes the DDL more fully.

October 10, 2017

Cloning Goldengate Integrated Capture and DB

Filed under: 12c,GoldenGate — mdinh @ 10:10 pm

Using DBMS_STREAMS_ADM To Cleanup GoldenGate

Let’s say you want to clone DB and Goldengate implementation from PROD to DEV, then you need to drop the capture that was registered with PROD database.

This is what happens when dependencies are introduced / created.

select capture_name from dba_capture;
exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('&capture');

September 30, 2017

Mining Goldgate ggserr.log

Filed under: 12c,GoldenGate — mdinh @ 2:33 am

Can you imagine me running in circles shouting, “The sky is falling, the sky is falling?”

Replicat Lag at Chkpt: 03:21:45

Here are the trail files at target – look at how fast it is being created.

ls -alrt ./dirdat/aa*|tail -20
-rw-r----- 1 ggsuser ggsuser 499999845 Sep 29 11:09 ./dirdat/aa000020827
-rw-r----- 1 ggsuser ggsuser 499999575 Sep 29 11:09 ./dirdat/aa000020828
-rw-r----- 1 ggsuser ggsuser 499999929 Sep 29 11:10 ./dirdat/aa000020829
-rw-r----- 1 ggsuser ggsuser 499999771 Sep 29 11:11 ./dirdat/aa000020830
-rw-r----- 1 ggsuser ggsuser 499999941 Sep 29 11:11 ./dirdat/aa000020831
-rw-r----- 1 ggsuser ggsuser 499999858 Sep 29 11:12 ./dirdat/aa000020832
-rw-r----- 1 ggsuser ggsuser 499999571 Sep 29 11:12 ./dirdat/aa000020833
-rw-r----- 1 ggsuser ggsuser 499999874 Sep 29 11:13 ./dirdat/aa000020834
-rw-r----- 1 ggsuser ggsuser 499999782 Sep 29 11:14 ./dirdat/aa000020835
-rw-r----- 1 ggsuser ggsuser 499999975 Sep 29 11:14 ./dirdat/aa000020836

My hypothesis: lots of data being capture at source.

After all is said and done. The ggserr.log was mined.

Gather dates for the 10 highest number of trails created by day in 2017.

SOURCE:

grep "^2017" ggserr.log.dinh|grep "p_test.prm:  Rolling over remote file"|awk '{ print $1 }'|uniq -c|sort -nrk 1|head

    224 2017-09-29
    147 2017-06-02
    105 2017-02-28
    101 2017-05-31
    100 2017-03-01
     98 2017-06-01
     97 2017-05-18
     91 2017-05-26
     89 2017-07-25
     85 2017-01-26

TARGET:


grep "^2017" ggserr.log.dinh|grep "r_test.prm:  Switching to next trail file"|awk '{ print $1 }'|uniq -c|sort -nrk 1|head

    279 2017-09-29
    183 2017-02-28
    174 2017-03-01
    148 2017-06-02
    146 2017-02-24
    137 2017-08-29
    137 2017-03-24
    133 2017-08-11
    130 2017-08-16
    128 2017-03-02

Different count between source/target for 2017-09-29 is due to data being collected at different time.

June 11, 2017

GoldenGate 12.2 TROUBLESHOOTING REPLICAT LAG

Filed under: 12c,GoldenGate,oracle — mdinh @ 2:16 pm

Time Since Chkpt and Lag at Chkpt from replicat keep increasing

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
REPLICAT    RUNNING     R_NEW12C    03:49:45      06:37:47    

This occurs for due to the following reasons:

Delivering a long running transaction
Waiting on a full table scan
Blocked by another sessions
Primary extract lag or pump lag keeps increasing

Is There a Way to Make Long-running Transactions Checkpoint? (Doc ID 969684.1)

The tradeoff with GROUPTRANSOPS is with efficiency. 
The tradeoff with MAXTRANSOPS is efficiency and transaction integrity. 

There are no long running transaction from the extract and is monitored using WARNLONGTRANS 15m, CHECKINTERVAL 3m.

grep "Long Running Transaction" dirrpt/E_OLD10G.rpt

My suspicion is FTS, but how to find out?

SQL> r
  1  select
  2  -- SQL_ID,PLAN_HASH_VALUE,
  3  OBJECT_OWNER,OBJECT_NAME, min(TIMESTAMP) min_ts, max(TIMESTAMP) max_ts, count(*) ct
  4  from DBA_HIST_SQL_PLAN
  5  where operation='TABLE ACCESS'
  6  and options='FULL'
  7  and NOT REGEXP_LIKE(object_owner,'SYS|SYSTEM|DBSNMP')
  8  and TIMESTAMP > TO_DATE('01-JUN-2017','DD-MON-YYYY')
  9  group by
 10  -- SQL_ID,PLAN_HASH_VALUE,
 11  OBJECT_OWNER,OBJECT_NAME
 12  order by count(*), OBJECT_OWNER,OBJECT_NAME
 13*
 
OBJECT_OWNER         OBJECT_NAME                    MIN_TS               MAX_TS                       CT
-------------------- ------------------------------ -------------------- -------------------- ----------
XXX                  THISISAREALLYLONGTABLENAME     01-JUN-2017 00:01:00 11-JUN-2017 02:55:36        114

SQL> select index_name from dba_indexes where table_name='THISISAREALLYLONGTABLENAME';
no rows selected
SQL> 
Next Page »

Create a free website or blog at WordPress.com.