Thinking Out Loud

June 13, 2016

GoldenGate 12.2 cases and quotes

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

Avoid using quotes and case sensitivities if you can.

Process Abends : OGG-00919 Error in COLMAP clause referring to : @GETENV(“GGHEADER”…. (Doc ID 1635935.1)

1. Replace double quote with single quote.

Change reference is GETENV statement to use single quotes rather than double quotes, as example is :

@GETENV(‘GGHEADER’,’BEFOREAFTERINDICATOR’)

2. Specify NOUSEANSISQLQUOTES in GLOBALS parameter.

The default is now USEANSISQLQUOTES.

USEANSISQLQUOTES

When capturing and mapping object names, such as table names, Oracle GoldenGate always recognizes double-quoted strings as case-sensitive object names,
regardless of whether USEANSISQLQUOTES or NOUSEANSISQLQUOTES is specified.

With USEANSISQLQUOTES enabled, Oracle GoldenGate treats a string within double quotes as a case-sensitive column name, and it treats a string within single quotes as a literal.

Demo:

$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

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

GGSCI (arrow.localdomain) 1> INFO CREDENTIALSTORE

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggs_user
  Userid: ggs_admin

Other domains:

  admin

To view other domains, use INFO CREDENTIALSTORE DOMAIN 

GGSCI (arrow.localdomain) 2> DBLOGIN USERIDALIAS GGS_USER

ERROR: Alias 'GGS_USER' not found in credential store..

GGSCI (arrow.localdomain) 3> DBLOGIN USERIDALIAS ggs_user

Successfully logged into database.

June 12, 2016

GoldenGate 12.2 discard vs exception table

Filed under: 12c,GoldenGate — mdinh @ 4:44 pm

I have seen several blogs demonstrating using database exception table for error handling.

Truth be told, I have never used the exception table method and curious to know what’s the difference.

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

First, there’s manual work to create database exception table for error handling.

Let’ me know how long it takes you to configure database exception table for error handling.

Second, with new features, you may need to update database exception table for error handling.

Third, there is no values for bind variables.

Discard functionality is built-in and with OGG 12.2 and the missing delete can easily be converted into insert.

Which method will you be implementing and why?

$ cat dirrpt/r_hawk.dsc

Oracle GoldenGate Delivery for Oracle process started, group R_HAWK discard file opened: 2016-06-11 13:04:48.441744
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (JANE, 1995-11-11:13:52:00, 256, PLANE) does not exist in table DEMO.TCUSTORD
ORA-01403: no data found

Operation failed at seqno 0 rba 190519
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = JANE
000000: 4a 41 4e 45                                     |JANE            |

ORDER_DATE = 1995-11-11 13:52:00
000000: 31 39 39 35 2d 31 31 2d 31 31 20 31 33 3a 35 32 |1995-11-11 13:52|
000010: 3a 30 30                                        |:00             |

PRODUCT_CODE = PLANE
000000: 50 4c 41 4e 45                                  |PLANE           |

ORDER_ID = 256
000000: 32 35 36                                        |256             |

PRODUCT_PRICE = 133300.00
000000: 31 33 33 33 30 30 2e 30 30                      |133300.00       |

PRODUCT_AMOUNT = 1
000000: 31                                              |1               |

TRANSACTION_ID = 100
000000: 31 30 30                                        |100             |

*
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (WILL, 1994-09-30:15:33:00, 144, CAR) does not exist in table DEMO.TCUSTORD
ORA-01403: no data found

Operation failed at seqno 0 rba 190727
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = WILL
000000: 57 49 4c 4c                                     |WILL            |

ORDER_DATE = 1994-09-30 15:33:00
000000: 31 39 39 34 2d 30 39 2d 33 30 20 31 35 3a 33 33 |1994-09-30 15:33|
000010: 3a 30 30                                        |:00             |

PRODUCT_CODE = CAR
000000: 43 41 52                                        |CAR             |

ORDER_ID = 144
000000: 31 34 34                                        |144             |

PRODUCT_PRICE = 17520.00
000000: 31 37 35 32 30 2e 30 30                         |17520.00        |

PRODUCT_AMOUNT = 3
000000: 33                                              |3               |

TRANSACTION_ID = 100
000000: 31 30 30                                        |100             |

*
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE") = (JANE) does not exist in table DEMO.TCUSTMER
ORA-01403: no data found

Operation failed at seqno 0 rba 190905
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = JANE
000000: 4a 41 4e 45                                     |JANE            |

NAME = ROCKY FLYER INC.
000000: 52 4f 43 4b 59 20 46 4c 59 45 52 20 49 4e 43 2e |ROCKY FLYER INC.|

CITY = DENVER
000000: 44 45 4e 56 45 52                               |DENVER          |

STATE = CO
000000: 43 4f                                           |CO              |

*
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE") = (WILL) does not exist in table DEMO.TCUSTMER
ORA-01403: no data found

Operation failed at seqno 0 rba 191043
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = WILL
000000: 57 49 4c 4c                                     |WILL            |

NAME = BG SOFTWARE CO.
000000: 42 47 20 53 4f 46 54 57 41 52 45 20 43 4f 2e    |BG SOFTWARE CO. |

CITY = SEATTLE
000000: 53 45 41 54 54 4c 45                            |SEATTLE         |

STATE = WA
000000: 57 41                                           |WA              |

*
Process Abending : 2016-06-11 13:09:24

select * from exceptions

ARROW:(GGS_ADMIN@thor):PRIMARY> @pr "select * from exceptions";
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTORD
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25819152
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTORD
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25819772
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTMER
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25821712
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTMER
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25822128
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------

PL/SQL procedure successfully completed.

Convert DELETE into INSERT

Not all the columns’ value are available and because I did not configured properly.

ARROW:(DEMO@thor):PRIMARY> desc DEMO.TCUSTORD
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CUST_CODE                                             NOT NULL VARCHAR2(4)
 ORDER_DATE                                            NOT NULL DATE
 PRODUCT_CODE                                          NOT NULL VARCHAR2(8)
 ORDER_ID                                              NOT NULL NUMBER
 PRODUCT_PRICE                                                  NUMBER(8,2)
 PRODUCT_AMOUNT                                                 NUMBER(6)
 TRANSACTION_ID                                                 NUMBER

ARROW:(DEMO@thor):PRIMARY> select count(*) from  DEMO.TCUSTORD
  2

ARROW:(DEMO@thor):PRIMARY> select count(*) from  DEMO.TCUSTORD;

  COUNT(*)
----------
         0

ARROW:(DEMO@thor):PRIMARY> insert into DEMO.TCUSTORD ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE")
  2  values ('JANE', '1995-11-11:13:52:00', 256, 'PLANE');

1 row created.

ARROW:(DEMO@thor):PRIMARY> select count(*) from  DEMO.TCUSTORD;

  COUNT(*)
----------
         1

ARROW:(DEMO@thor):PRIMARY>

June 11, 2016

GoldenGate 12.2 checkprm and oggerr

Filed under: 12c,GoldenGate — mdinh @ 3:05 pm

At first, I was wondering what’s the true benefit of the new feature (checkprm) since there exists CHECKPARAMS which does this.

For someone lazy like me, it means not having to modify code to add and remove CHECKPARAMS from GoldenGate parameter files.

Once, there was oerr and now there is oggerr to look up error codes.

Reference: Oracle GoldenGate Release Notes for 12c (12.2.0.1)

$ checkprm dirprm/e_hawk.prm

2016-06-11 07:29:47  INFO    OGG-02095  Successfully set environment variable ORACLE_SID=hawk.

2016-06-11 07:29:47  INFO    OGG-02095  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/11.2.0/se_1.

(e_hawk.prm) line 15: Parameter [UPDATEMETADATA] is not valid for this configuration.

2016-06-11 07:29:47  INFO    OGG-10139  Parameter file dirprm/e_hawk.prm:  Validity check: FAIL.

$ cat dirprm/e_hawk.prm

EXTRACT e_hawk
-- CHECKPARAMS
USERIDALIAS ggs_user
EXTTRAIL ./dirdat/aa
INCLUDE ./dirprm/global_ggenv_se.inc
-- max_sga_size 1G per IE or IR process then add 25%, not to exeed 3.5G
-- max_sga_size > 3.5G not recommended
-- parallelism 1 is for standard edition database
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 128, parallelism 1)
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE ALL &
EXCLUDE OBJTYPE 'SEQUENCE' &
EXCLUDE UNMAPPED OBJNAME GGS_ADMIN.*
DDLOPTIONS UPDATEMETADATA, REPORT
WARNLONGTRANS 15m, CHECKINTERVAL 5m
TABLE DEMO.*;

$ checkprm dirprm/e_hawk.prm

2016-06-11 07:30:31  INFO    OGG-02095  Successfully set environment variable ORACLE_SID=hawk.

2016-06-11 07:30:31  INFO    OGG-02095  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/11.2.0/se_1.

2016-06-11 07:30:31  INFO    OGG-10139  Parameter file dirprm/e_hawk.prm:  Validity check: PASS.

Runtime parameter validation is not reflected in the above check.

$ oerr ora 01403

01403, 00000, "no data found"
// *Cause: No data was found from the objects.
// *Action: There was no data from the objects which may be due to end of fetch.

$ oggerr ogg 06439

06439, 00000, "No unique key is defined for table {0}. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key."
// *{0}: tableObjName (Object name)
// *Cause:  No unique key is defined for the specified table.
// *Action: Use a KEYCOLS clause in TABLE or MAP to define a key that contains
//          unique values. Using a defined key ensures uniqueness and improves
//          performance.

$ oggerr ogg 01296

01296, 00000, "Error mapping from {0} to {1}"
// *{0}: sourceTable (Qualified table name)
// *{1}: targetTable (Qualified table name)
// *Cause:  The mapping of the specified source and target tables failed.
// *Action: Examine the accompanying messages that provide details about the
//          mapping failure, and resolve the problem based on those messages.
//          If the problem persists, contact Oracle Support.

June 4, 2016

Oracle PSU Pain and Predicament

Filed under: oracle — mdinh @ 5:29 pm

Here is a list of download options for APR2016 (11.2.0.4.160419) PSU:
OJVM PSU (Linux/Unix) 22674697
Combo OJVM + DB PSU 22738777
Combo OJVM + DB SPU 22738732
Combo OJVM + GI PSU 22738793

That’s not enough  and there should be more (sarcasm).

FYI: GI PSU includes DB and DB PSU does not include GI.

There is a thread from MOS – OJVM PSU and RAC: What happened to high availability? and I was wondering the same.

One document says do A then B while another has do B then A.

Oracle Recommended Patches — “Oracle JavaVM Component Database PSU” (OJVM PSU) Patches (Doc ID 1929745.1)

1. Shutdown databases and services on all nodes
2. Apply DB PSU (or equivalent) but DO NOT RUN DB PSU POST INSTALL STEPS
3. Apply OJVM PSU patch [see note-1 below]
4. October 2014 only for DB versions below 12.1.0.2: Apply the JDBC Patch [see note-2 below]
5. Run post install steps on all DBs in the patched home:

For 11.2.0.3 and 11.2.0.4 run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.

6. Re-start any stopped databases / services running from this ORACLE_HOME

Patch 22738793 – Combo of OJVM Component 11.2.0.4.160419 DB PSU + GI PSU 11.2.0.4.160419 (Apr2016)

README has the opposite.
SQL> @catbundle.sql psu apply
SQL> @postinstall.sql

When opatch auto is used for Combo OJVM + GI PSU 22738793, database will be shutdown and may not start up.

PRCH-1061 Applying OCT2014 Grid PSU 19380115 With Opatch Auto (Doc ID 1946048.1)
srvctl stop/status home creates empty file after applying oct2014 psu 19380115 (Doc ID 1946050.1)


[root@arrow ~]# . oraenv <<< grid
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
[root@arrow ~]# which make ar ld nm
/usr/bin/make
/usr/bin/ar
/usr/bin/ld
/usr/bin/nm
[root@arrow ~]# export PATCH_TOP_DIR=/media/sf_linux_x64
[root@arrow ~]# $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocm.rsp
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (/tmp/ocm.rsp) was successfully created.
[root@arrow ~]# $ORACLE_HOME/OPatch/opatch auto $PATCH_TOP_DIR/22738793 -ocmrf /tmp/ocm.rsp
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch11203.pl -patchdir /media/sf_linux_x64 -patchn 22738793 -ocmrf /tmp/ocm.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.report.log

2016-06-04 09:11:57: Starting Oracle Restart Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Stopping RAC /u01/app/oracle/product/11.2.0/dbhome_1 ...
Stopped RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully

patch /media/sf_linux_x64/22738793/22502456  apply successful for home  /u01/app/oracle/product/11.2.0/dbhome_1
patch /media/sf_linux_x64/22738793/22502549/custom/server/22502549  apply successful for home  /u01/app/oracle/product/11.2.0/dbhome_1
patch /media/sf_linux_x64/22738793/22674697  apply successful for home  /u01/app/oracle/product/11.2.0/dbhome_1

Stopping CRS...
Stopped CRS successfully

patch /media/sf_linux_x64/22738793/22502456  apply successful for home  /u01/app/11.2.0/grid
patch /media/sf_linux_x64/22738793/22502549  apply successful for home  /u01/app/11.2.0/grid
patch /media/sf_linux_x64/22738793/22502505  apply successful for home  /u01/app/11.2.0/grid

Starting CRS...
CRS-4123: Oracle High Availability Services has been started.

Starting RAC /u01/app/oracle/product/11.2.0/dbhome_1 ...
Failed to start resources from  database home /u01/app/oracle/product/11.2.0/dbhome_1
ERROR: Refer log file for more details.

opatch auto failed.

[root@arrow ~]# tail /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.log
2016-06-04 09:30:23: Removing file /tmp/fileR3nOfE
2016-06-04 09:30:23: Successfully removed file: /tmp/fileR3nOfE
2016-06-04 09:30:23: /bin/su exited with rc=1

2016-06-04 09:30:23: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s 

/u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt 

output is PRCH-1001 : Failed to start resources in Oracle home /u01/app/oracle/product/11.2.0/dbhome_1

PRCH-1061 : The following resources specified in the state file do not exist: svc-hulk_svc, svc-thor_svc

2016-06-04 09:30:23: Failed to start resources from  database home /u01/app/oracle/product/11.2.0/dbhome_1
2016-06-04 09:30:23: ERROR: Refer log file for more details.

[root@arrow ~]# tail /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.report.log
33: /u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh : run as root

34: /u01/app/11.2.0/grid/crs/install/roothas.pl -patch : run as root

35: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl start dbconsole

36: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl start agent

37: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt

[root@arrow ~]# grep stop /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.report.log
13: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl stop dbconsole
14: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl stop agent
18: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl stop home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt -f
37: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt
[root@arrow ~]#

May 24, 2016

Using DBMS_STREAMS_ADM To Cleanup GoldenGate

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

This is really messed up. I chose GoldenGate because I did not want to mess around with streams.

When using Integrated Capture or Delivery, then knowing streams is a prerequisites.

Apologies as the format is not pretty.

The QUEUE table was indeed missing and this is what I get for monkeying around.

To resolve the issue –  exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

GGSCI (arrow.localdomain as ggs_admin@hawk) 3> unREGISTER EXTRACT e_hawk DATABASE

2016-05-23 19:16:32  ERROR   OGG-08221  Cannot register or unregister EXTRACT E_HAWK because of the following SQL error: 
OCI Error ORA-24010: QUEUE "GGS_ADMIN"."OGG$Q_E_HAWK" does not exist
ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 468
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 724
ORA-06512: at line 1 (status = 24010).

GGSCI (arrow.localdomain as ggs_admin@hawk) 4> exit


ARROW:(SYS@hawk):PRIMARY> select * from dba_capture;

CAPTURE_NAME                   QUEUE_NAME                     QUEUE_OWNER
------------------------------ ------------------------------ ------------------------------
RULE_SET_NAME                  RULE_SET_OWNER                 CAPTURE_USER
------------------------------ ------------------------------ ------------------------------
 START_SCN STATUS   CAPTURED_SCN APPLIED_SCN USE  FIRST_SCN
---------- -------- ------------ ----------- --- ----------
SOURCE_DATABASE
----------------------------------------------------------------------------------------------------
SOURCE_DBID SOURCE_RESETLOGS_SCN SOURCE_RESETLOGS_TIME LOGMINER_ID NEGATIVE_RULE_SET_NAME
----------- -------------------- --------------------- ----------- ------------------------------
NEGATIVE_RULE_SET_OWNER        MAX_CHECKPOINT_SCN REQUIRED_CHECKPOINT_SCN LOGFILE_ STATUS_CH
------------------------------ ------------------ ----------------------- -------- ---------
ERROR_NUMBER
------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
VERSION                                                          CAPTURE_TY LAST_ENQUEUED_SCN
---------------------------------------------------------------- ---------- -----------------
CHECKPOINT_RETENTION_TIME
-------------------------
START_TIME                                                                  PURPOSE
--------------------------------------------------------------------------- -------------------
CLIENT_NAME
----------------------------------------------------------------------------------------------------
CLIENT_S OLDEST_SCN FILTERED_SCN
-------- ---------- ------------
OGG$CAP_E_HAWK                 OGG$Q_E_HAWK                   GGS_ADMIN
                                                              GGS_ADMIN
    256229 DISABLED       346591      346586 NO      256229
HAWK
 3171223736                    1             912525304           3
                                           346420                  346586 IMPLICIT 23-MAY-16


11.2.0.4.0                                                       LOCAL
                        0
22-MAY-16 04.21.31.000000 PM                                                GoldenGate Capture
E_HAWK
DISABLED     346586       255600


ARROW:(SYS@hawk):PRIMARY> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_E_HAWK');

PL/SQL procedure successfully completed.

ARROW:(SYS@hawk):PRIMARY> select * from dba_capture;

CAPTURE_NAME                   QUEUE_NAME                     QUEUE_OWNER
------------------------------ ------------------------------ ------------------------------
RULE_SET_NAME                  RULE_SET_OWNER                 CAPTURE_USER
------------------------------ ------------------------------ ------------------------------
 START_SCN STATUS   CAPTURED_SCN APPLIED_SCN USE  FIRST_SCN
---------- -------- ------------ ----------- --- ----------
SOURCE_DATABASE
----------------------------------------------------------------------------------------------------
SOURCE_DBID SOURCE_RESETLOGS_SCN SOURCE_RESETLOGS_TIME LOGMINER_ID NEGATIVE_RULE_SET_NAME
----------- -------------------- --------------------- ----------- ------------------------------
NEGATIVE_RULE_SET_OWNER        MAX_CHECKPOINT_SCN REQUIRED_CHECKPOINT_SCN LOGFILE_ STATUS_CH
------------------------------ ------------------ ----------------------- -------- ---------
ERROR_NUMBER
------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
VERSION                                                          CAPTURE_TY LAST_ENQUEUED_SCN
---------------------------------------------------------------- ---------- -----------------
CHECKPOINT_RETENTION_TIME
-------------------------
START_TIME                                                                  PURPOSE
--------------------------------------------------------------------------- -------------------
CLIENT_NAME
----------------------------------------------------------------------------------------------------
CLIENT_S OLDEST_SCN FILTERED_SCN
-------- ---------- ------------
OGG$CAP_E_HAWK                 OGG$Q_E_HAWK                   GGS_ADMIN
                                                              GGS_ADMIN
    256229 DISABLED       346591      346586 NO      256229
HAWK
 3171223736                    1             912525304           3
                                           346420                  346586 IMPLICIT 23-MAY-16


11.2.0.4.0                                                       LOCAL
                        0
22-MAY-16 04.21.31.000000 PM                                                GoldenGate Capture
E_HAWK
DISABLED     346586       255600


ARROW:(SYS@hawk):PRIMARY>


ARROW:(SYS@hawk):PRIMARY> exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

PL/SQL procedure successfully completed.

ARROW:(SYS@hawk):PRIMARY> select * from dba_capture;

no rows selected

ARROW:(SYS@hawk):PRIMARY>

May 22, 2016

GoldenGate 12.2 Patch 17030189 required Integrated trail format RELEASE 12.2 or later

Filed under: 12c,GoldenGate — mdinh @ 8:26 pm

EXTRACT Abending With OGG-02912 (Doc ID 2091679.1)

Alternate script prvtlmpg.plb (included in the Oracle GoldenGate installation directory) to the mining database to work around this limitation.

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ ll prv*
-rw-r-----. 1 oracle oinstall 1272 Dec 28  2010 prvtclkm.plb
-rw-r-----. 1 oracle oinstall 9487 May 27  2015 prvtlmpg.plb
-rw-r-----. 1 oracle oinstall 3263 May 27  2015 prvtlmpg_uninstall.sql
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$

The other option in this case would be to request a backport since patch is not available for all database 11g releases.

Implementing work around.

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 22 15:23:27 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

ARROW:(SYS@hawk):PRIMARY> @prvtlmpg.plb

Oracle GoldenGate Workaround prvtlmpg

This script provides a temporary workaround for bug 17030189.
It is strongly recommended that you apply the official Oracle
Patch for bug 17030189 from My Oracle Support instead of using
this workaround.

This script must be executed in the mining database of Integrated
Capture. You will be prompted for the username of the mining user.
Use a double quoted identifier if the username is case sensitive
or contains special characters. In a CDB environment, this script
must be executed from the CDB$ROOT container and the mining user
must be a common user.

===========================  WARNING  ==========================
You MUST stop all Integrated Captures that belong to this mining
user before proceeding!
================================================================

Enter Integrated Capture mining user: ggs_admin

Installing workaround...
No errors.
No errors.
No errors.
Installation completed.
ARROW:(SYS@hawk):PRIMARY> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$

oracle@arrow:hawk:/u01/app/oracle/product/11.2.0/se_1/dbs
$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/se_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/se_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/se_1/cfgtoollogs/opatch/opatch2016-05-22_15-26-10PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/se_1/cfgtoollogs/opatch/lsinv/lsinventory2016-05-22_15-26-10PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
oracle@arrow:hawk:/u01/app/oracle/product/11.2.0/se_1/dbs
$

Create GoldenGate 12.2 Manager

Filed under: 12c,GoldenGate — mdinh @ 8:14 pm

I typically don’t like to see WARNING if I can help it.

Goldengate 12c has some security features to allow/prevent unauthorized access.

Be careful. Incorrect IPADDR or PROG is used will prevent Pump Extract delivery to target server.

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ tail -100 ggserr.log
2016-05-22 12:25:07  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2016-05-22 12:25:07  WARNING OGG-01877  Oracle GoldenGate Manager for Oracle, mgr.prm:  Missing explicit accessrule for server collector.
2016-05-22 12:25:07  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #7).
2016-05-22 12:25:07  INFO    OGG-00983  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7901).
2016-05-22 12:25:09  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2016-05-22 12:25:46  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2016-05-22 12:25:51  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): stop mgr.
2016-05-22 12:25:51  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host [127.0.0.1]:39551 (STOP).
2016-05-22 12:25:51  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #7).
2016-05-22 12:25:51  WARNING OGG-00938  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager is stopping at user request.
2016-05-22 12:26:00  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2016-05-22 12:26:00  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #2).
2016-05-22 12:26:00  INFO    OGG-00983  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7901).

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ cat dirprm/mgr.prm
PORT 7901
DYNAMICPORTLIST 15100-15120
ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW
USERIDALIAS ggs_admin
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
-- AUTOSTART ER *
-- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGCRITICALMINUTES 15
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$

Create GoldenGate 12.2 Wallet

Filed under: GoldenGate — mdinh @ 7:44 pm

So what’s different from this post versus other posts? I share my mistakes with you.

Did you know there was a DEFAULT domain? If you didn’t, neither did I and only found out by using

info credentialstore

alter credentialstore add user ggs_admin alias ggs_admin domain admin
USERIDALIAS ggs_admin DOMAIN admin

alter credentialstore add user ggs_admin alias ggs_admin
USERIDALIAS ggs_admin

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

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

GGSCI (arrow.localdomain) 1> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (arrow.localdomain) 2> add credentialstore

Credential store created in ./dircrd/.

GGSCI (arrow.localdomain) 3> alter credentialstore add user ggs_admin alias ggs_admin domain admin
Password:

Credential store in ./dircrd/ altered.

GGSCI (arrow.localdomain) 4> info credentialstore

Reading from ./dircrd/:

No information found in default domain OracleGoldenGate.

Other domains:

admin

To view other domains, use INFO CREDENTIALSTORE DOMAIN <domain>

GGSCI (arrow.localdomain) 5> info credentialstore domain admin

Reading from ./dircrd/:

Domain: admin

Alias: ggs_admin
Userid: ggs_admin

GGSCI (arrow.localdomain) 6> exit


oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

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



GGSCI (arrow.localdomain) 1> alter credentialstore add user ggs_admin alias ggs_admin
Password:

Credential store in ./dircrd/ altered.

GGSCI (arrow.localdomain) 2> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggs_admin
  Userid: ggs_admin

Other domains:

  admin

To view other domains, use INFO CREDENTIALSTORE DOMAIN 

GGSCI (arrow.localdomain) 3> exit
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$

 

May 15, 2016

Automating DG Broker

Filed under: 11g,awk,Dataguard,oracle,shell scripting — mdinh @ 2:11 am

I have been applying PSU lately and what’s so hard out it?

Four+ databases running on Primary with DG Broker for standby.

There are no conventions, as some standby databases have dr appended to primary name while others have 2 appended to primary name.

I wanted to view the DG configuration for currently active instances and show_dg_config.sh will show me this.

Next, I want a faster way to shutdown DG by having syntax generated and  gen_dg_cmd.sh does this.

Guess I could have taken it further by creating a shell script to create shell scripts to shutdown DG.

One day when I am really bore, I might OR may be you are so nice to complete my mission.

Tested on AIX 7.1

Note: the ps -ef syntax is for AIX and will not work with Linux.

See below for the Linux alternative.

$ ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

$ ps -eo args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
thor
hulk

show_dg_config.sh

#!/bin/sh -e
ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
export ORAENV_ASK=NO
for SID in ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'`
do
export ORACLE_SID=$SID
. /usr/local/bin/oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
echo "+++: " $ORACLE_SID $ORACLE_HOME
sysresv
dgmgrl -echo << END
connect /
show configuration
exit
END
done
exit

gen_dg_cmd.sh

#!/bin/sh -e
for XB in `egrep 'Primary|Physical' /tmp/dg.log |sort |awk -F" " '{print $3 $1}'`
do
#echo $XB
#echo $XB|awk '{print substr($1,1,7)}'
if [ "`echo $XB|awk '{print substr($1,1,7)}'`" == "Primary" ]
then
PRI=`echo $XB|awk '{print substr($1,8)}'`
echo "edit database $PRI set state='LOG-TRANSPORT-OFF';"
echo "show database $PRI"
echo "edit database $PRI set state='ONLINE';"
echo "show database $PRI"
fi
if [ "`echo $XB|awk '{print substr($1,1,8)}'`" == "Physical" ]
then
SBY=`echo $XB|awk '{print substr($1,9)}'`
echo "edit database $SBY set state='APPLY-OFF';"
echo "show database $SBY"
echo "edit database $SBY set state='APPLY-ON';"
echo "show database $SBY"
fi
done
exit

./show_dg_config.sh > /tmp/dg.log

egrep ‘Primary|Physical’ /tmp/dg.log |sort |awk -F” ” ‘{print $3 $1}’

Primarydb02
Physicaldb02dr
Primarydb01
Physicaldb01dr
Primarystageqa
Physicalstageqa2
Primarytest
Physicaltestdr

./gen_dg_cmd.sh

edit database db01 set state='LOG-TRANSPORT-OFF';
show database db01
edit database db01 set state='ONLINE';
show database db01
edit database db01dr set state='APPLY-OFF';
show database db01dr
edit database db01dr set state='APPLY-ON';
show database db01dr
edit database db02 set state='LOG-TRANSPORT-OFF';
show database db02
edit database db02 set state='ONLINE';
show database db02
edit database db02dr set state='APPLY-OFF';
show database db02dr
edit database db02dr set state='APPLY-ON';
show database db02dr
edit database stageqa set state='LOG-TRANSPORT-OFF';
show database stageqa
edit database stageqa set state='ONLINE';
show database stageqa
edit database stageqa2 set state='APPLY-OFF';
show database stageqa2
edit database stageqa2 set state='APPLY-ON';
show database stageqa2
edit database test set state='LOG-TRANSPORT-OFF';
show database test
edit database test set state='ONLINE';
show database test
edit database testdr set state='APPLY-OFF';
show database testdr
edit database testdr set state='APPLY-ON';
show database testdr
oracle:/home/oracle/working/dinh$

April 9, 2016

Create GoldenGate 12.2 Database User

Filed under: GoldenGate — mdinh @ 2:33 pm

Oracle GoldenGate for Windows and UNIX 12c (12.2.0.1)

First, I am disappointed that Oracle does not go above and beyond to provide SQL scripts to create GoldenGate users for the database.

There are different set of privileges depending on the version of the database:

4.1.4.2 Oracle 11.2.0.3 or Earlier Database Privileges
4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges

PDB is not being used and it’s different for PDB.

Depending on whether you want to practice the least principle privileges, ggadmin user can be create with privileges for both extract (capture) and replicat (apply).

Please don’t forget to change the password from the script since it is hard coded to be same as username :=)

cr_ggadmin_12c.sql
-- 4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges
set echo on lines 200 pages 1000 trimspool on tab off
define _username='GGADMIN'
-- grant privileges for capture
create user &_username identified by &_username default tablespace ggdata;
select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';
grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;
-- grant privileges for replicat
grant create table, lock any table to &_username;
-- grant both capture and apply
exec dbms_goldengate_auth.grant_admin_privilege('&_username')
-- grant capture
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
-- grant apply
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');

Demo:

oracle@arrow:tiger:/media/sf_working/ggs
$ sysdba @cr_ggadmin_12c.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 9 07:06:41 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@tiger):PRIMARY> define _username='GGADMIN'
ARROW:(SYS@tiger):PRIMARY> -- grant privileges for capture
ARROW:(SYS@tiger):PRIMARY> create user &_username identified by &_username default tablespace ggdata;

User created.

ARROW:(SYS@tiger):PRIMARY> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
GGDATA                         TEMP

ARROW:(SYS@tiger):PRIMARY> grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;

Grant succeeded.

ARROW:(SYS@tiger):PRIMARY> -- grant privileges for replicat
ARROW:(SYS@tiger):PRIMARY> grant create table, lock any table to &_username;

Grant succeeded.

ARROW:(SYS@tiger):PRIMARY> -- grant both capture and apply
ARROW:(SYS@tiger):PRIMARY> exec dbms_goldengate_auth.grant_admin_privilege('&_username')

PL/SQL procedure successfully completed.

ARROW:(SYS@tiger):PRIMARY> -- grant capture
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
ARROW:(SYS@tiger):PRIMARY> -- grant apply
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');
ARROW:(SYS@tiger):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:tiger:/media/sf_working/ggs
$
Next Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 511 other followers