Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 12 hours 22 min ago

GoldenGate and Networking

Fri, 2016-07-15 23:16

I have never delved into networking for GoldenGate since things just work. Due to security tightening, telnet is no longer available to verify opened port.

What’s to follow is a demo for ports being listened by GoldenGate when manager started and ports listened and when PUMP Extract is started as part using DYNAMICPORTLIST.

Also, how to test opened port using (nc – arbitrary TCP and UDP connections and listens)

Both source and target are running on the same host; however, the same principles still apply.

HAWK (source): PORT 7901 and DYNAMICPORTLIST 15100-15120

THOR (target):   PORT 7801 and DYNAMICPORTLIST 15200-15220

 

 

HAWK (source):

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

++++++++++

GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:06      00:00:47
EXTRACT     STOPPED     P_HAWK      00:00:00      00:00:35

GGSCI (arrow.localdomain) 2>
THOR (target):
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ cat dirprm/mgr.prm
PORT 7801
DYNAMICPORTLIST 15200-15220
ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
-- AUTOSTART ER *
-- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGCRITICALMINUTES 15

++++++++++

GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     R_HAWK      00:00:00      70:18:54

GGSCI (arrow.localdomain) 2>
Ports 7801 and 7901 are in LISTEN mode and none of the ports from DYNAMICPORTLIST are LISTEN.
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ netstat -lnp|grep "LISTEN "|egrep "mgr|server"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::7801                     :::*                        LISTEN      2808/./mgr
tcp        0      0 :::7901                     :::*                        LISTEN      3068/./mgr
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
HAWK (source):  Start PUMP
GGSCI (arrow.localdomain) 1> start p*

Sending START request to MANAGER ...
EXTRACT P_HAWK starting

GGSCI (arrow.localdomain) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:06      00:02:03
EXTRACT     RUNNING     P_HAWK      00:00:00      00:01:50

GGSCI (arrow.localdomain) 3>

Ports 7801 and 7901 are in LISTEN mode and none of the ports from DYNAMICPORTLIST 15200

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ netstat -lnp|grep "LISTEN "|egrep "mgr|server"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::7801                     :::*                        LISTEN      2808/./mgr
tcp        0      0 :::7901                     :::*                        LISTEN      3068/./mgr
tcp        0      0 :::15200                    :::*                        LISTEN      3297/./server
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$
How to use nc to test opened port. What RPM is required to install nc. Use yum install nc.
[root@arrow ~]# yum whatprovides nc
Loaded plugins: refresh-packagekit, security
epel/metalink                                                                                   |  12 kB     00:00
epel                                                                                            | 4.3 kB     00:00
epel/primary_db                                                                                 | 5.8 MB     00:03
public_ol6_UEKR3_latest                                                                         | 1.2 kB     00:00
public_ol6_latest                                                                               | 1.4 kB     00:00
public_ol6_latest/primary                                                                       |  59 MB     00:26
public_ol6_latest                                                                                          36199/36199
nc-1.84-22.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : public_ol6_latest
Matched from:

nc-1.84-24.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : public_ol6_latest
Matched from:

nc-1.84-24.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : installed
Matched from:
Other       : Provides-match: nc

[root@arrow ~]#
DEMO:
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 7901
Connection to arrow 7901 port [tcp/tnos-sp] succeeded!

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 15200
Connection to arrow 15200 port [tcp/*] succeeded!

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 15201
nc: connect to arrow port 15201 (tcp) failed: Connection refused
nc: connect to arrow port 15201 (tcp) failed: Connection refused
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$

GoldenGate 12.2 DDLOPTIONS MAPSESSIONSCHEMA

Tue, 2016-07-12 23:36

This is probably not a 12.2 specific features and since I only tested for 12.2, I am not going to vouch for other versions.

Be careful when qualifying schema with DDL replication especially when the source and target schemas are not the same.

Example: Source schema is DEMO and Target schema is DEMO_REP

What happens to DDL replication when the following is used?

alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix;

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ sqlplus demo/demo @t.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 12 20:36:55 2016

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


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

ARROW:(DEMO@hawk):PRIMARY> drop table demo.t purge;

Table dropped.

ARROW:(DEMO@hawk):PRIMARY> create table demo.t(id int);

Table created.

ARROW:(DEMO@hawk):PRIMARY> create unique index demo.t_pk_ix on demo.t(id);

Index created.

ARROW:(DEMO@hawk):PRIMARY> alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix;

Table altered.

ARROW:(DEMO@hawk):PRIMARY>
Target: ggserr.log
2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create table demo.t(id int) (size 27)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create table "DEMO_REP"."T"(id int) (size 35)].

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create unique index demo.t_pk_ix on demo.t(id) (size 46)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create unique index "DEMO_REP"."T_PK_IX" on "DEMO_REP"."T"(id) (size 62)].

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix (size 79)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix (size 87)].

2016-07-12 20:37:00  ERROR   OGG-00516  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Fatal error executing DDL replication: error 
[Error code [1418], ORA-01418: specified index does not exist SQL alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix 
/* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].
2016-07-12 20:37:04  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  PROCESS ABENDING.

Notice using index demo.t_pk_ix did not get mapped accordingly.

How do we fixed this? The simple solution is don’t prefix schema name.
2016-07-12 20:53:39  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create table y(id int) (size 22)].
2016-07-12 20:53:39  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create table "DEMO_REP"."Y"(id int) (size 35)].

2016-07-12 20:53:39  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create unique index y_pk_ix on y(id) (size 36)].
2016-07-12 20:53:39  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create unique index "DEMO_REP"."Y_PK_IX" on "DEMO_REP"."Y"(id) (size 62)].

2016-07-12 20:53:40  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table y add constraint y_pk primary key(id) using index y_pk_ix (size 69)].
2016-07-12 20:53:40  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."Y" add constraint y_pk primary key(id) using index y_pk_ix (size 82)].
I know what you are probably thinking. What if login to the schema is not allowed?

DDLOPTIONS MAPSESSIONSCHEMA DEMO, TARGET DEMO_REP, MAPSCHEMAS

2016-07-12 21:27:10  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix (size 79)].
2016-07-12 21:27:10  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix (size 87)].
2016-07-12 21:27:10  INFO    OGG-01369  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation mapped to target database [T], new DDL operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index DEMO_REP.t_pk_ix (size 91)].
2016-07-12 21:27:10  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [TABLE], objowner [DEMO_REP], objname [T].
2016-07-12 21:27:10  INFO    OGG-01562  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Source schema DEMO is mapped to target schema DEMO_REP to set the current schema for DDL execution.
2016-07-12 21:27:10  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Setting current schema for DDL operation to [DEMO_REP].
2016-07-12 21:27:10  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Executing DDL operation.
2016-07-12 21:27:10  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation successful.
2016-07-12 21:27:10  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Restoring current schema for DDL operation to [GGS_ADMIN].
Happy Replication!

Reference: https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters037.htm#GWURF451


RMAN 12c NF – SQL interface in RMAN


Sat, 2016-07-09 18:51

Admittedly, I did not realize the benefit at first until there came a need.

Example:


# Set ORACLE_SID
ORACLE_SID=$1
export ORACLE_SID
### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;
}

 


Oracle Education Dissatisfaction

Sat, 2016-07-09 18:28

Just like anything, there is good and bad.

Unfortunately for me, my experience with Oracle education has been predominately bad and former manager experienced the same where he was able to retake a course almost a year later since the original was unrealistic for real world scenarios.

With Oracle education, the most simplistic setup is used, e.g. no ASM, no Grid Infrastructure as it may add a little more complexity to set up?

A little advice, try to find out who will be the instructor before registering for the course. Call and ask.

Next, there is a large and great Oracle community where you may learn more about technology and new features.

Here are 2 features I came to know from twitter feed and blogs which were never covered in the 12c Data Guard Administration.

ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C (Doc ID 1987763.1) (Courtesy Nassyam Basha)
RMAN ACTIVE DUPLICATE USING BACKUPSET IN 12C(NEW FEATURE) (Doc ID 1987193.1) (Courtesy of Deiby Gomez)

 

 


GoldenGate 12.2 cases and quotes

Sun, 2016-06-12 19:10

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.

GoldenGate 12.2 discard vs exception table

Sun, 2016-06-12 11:44

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>

GoldenGate 12.2 checkprm and oggerr

Sat, 2016-06-11 10:05

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.

Oracle PSU Pain and Predicament

Sat, 2016-06-04 12:29

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 ~]#

Using DBMS_STREAMS_ADM To Cleanup GoldenGate

Tue, 2016-05-24 00:33

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>

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

Sun, 2016-05-22 15:26
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

Sun, 2016-05-22 15:14

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

Sun, 2016-05-22 14:44

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
$

 


Automating DG Broker

Sat, 2016-05-14 21:11

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$

Create GoldenGate 12.2 Database User

Sat, 2016-04-09 09:33

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
$

Clone GoldenGate 12.2

Mon, 2016-04-04 04:16

This is what insomnia does to you.

Since GoldenGate installation is now using runInstaller, I would expect the same functionality as the database; hence, cloning GoldenGate Home.

oracle@arrow:pooh:/home/oracle
$ cd /u01/app/12.2.0.1/ggs01/

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$ ls
bcpfmt.tpl            ddl_ora10.sql             defgen                           extract        libggnnzitp.so      marker_status.sql       role_setup.sql
bcrypt.txt            ddl_ora10upCommon.sql     deinstall                        freeBSD.txt    libggparam.so       mgr                     sequence.sql
cachefiledump         ddl_ora11.sql             demo_more_ora_create.sql         ggcmd          libggperf.so        notices.txt             server
cfgtoollogs           ddl_ora9.sql              demo_more_ora_insert.sql         ggMessage.dat  libggrepo.so        oggerr                  sqlldr.tpl
checkprm              ddl_pin.sql               demo_ora_create.sql              ggparam.dat    libicudata.so.48    OPatch                  srvm
chkpt_ora_create.sql  ddl_remove.sql            demo_ora_insert.sql              ggsci          libicudata.so.48.1  oraInst.loc             tcperrs
convchk               ddl_session1.sql          demo_ora_lob_create.sql          help.txt       libicui18n.so.48    oui                     ucharset.h
convprm               ddl_session.sql           demo_ora_misc.sql                install        libicui18n.so.48.1  params.sql              ulg.sql
db2cntl.tpl           ddl_setup.sql             demo_ora_pk_befores_create.sql   inventory      libicuuc.so.48      prvtclkm.plb            UserExitExamples
ddl_cleartrace.sql    ddl_status.sql            demo_ora_pk_befores_insert.sql   jdk            libicuuc.so.48.1    prvtlmpg.plb            usrdecs.h
ddl_create.sql        ddl_staymetadata_off.sql  demo_ora_pk_befores_updates.sql  keygen         libxerces-c.so.28   prvtlmpg_uninstall.sql  zlib.txt
ddl_ddl2file.sql      ddl_staymetadata_on.sql   diagnostics                      label.sql      libxml2.txt         remove_seq.sql
ddl_disable.sql       ddl_tracelevel.sql        dirout                           libantlr3c.so  logdump             replicat
ddl_enable.sql        ddl_trace_off.sql         dirwww                           libdb-6.1.so   marker_remove.sql   retrace
ddl_filter.sql        ddl_trace_on.sql          emsclnt                          libgglog.so    marker_setup.sql    reverse

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$ nohup tar -cvpf /tmp/ggs12c_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_ggs12c.log 2>&1 &
[1] 10859
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$
[1]+  Done                    nohup tar -cvpf /tmp/ggs12c_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_ggs12c.log 2>&1

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$ cd ..
oracle@arrow:pooh:/u01/app/12.2.0.1
$ mkdir ggs02
oracle@arrow:pooh:/u01/app/12.2.0.1
$ cd ggs02/
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ls

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ nohup tar -xvf /tmp/ggs12c_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_ggs12c.log 2>&1 &
[1] 10897
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$
[1]+  Done                    nohup tar -xvf /tmp/ggs12c_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_ggs12c.log 2>&1

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ll /tmp/*ggs*
-rw-r--r--. 1 oracle oinstall     84131 Apr  4 01:36 /tmp/clone_oracle_arrow_ggs12c.log
-rw-r--r--. 1 oracle oinstall 835788800 Apr  4 01:36 /tmp/ggs12c_oracle_arrow_clone.tar
-rw-r--r--. 1 oracle oinstall     84131 Apr  4 01:45 /tmp/untar_oracle_arrow_ggs12c.log

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ tail /tmp/clone_oracle_arrow_ggs12c.log
./jdk/bin/javadoc
./jdk/bin/jarsigner
./jdk/bin/pack200
./jdk/bin/rmid
./jdk/bin/jrunscript
./jdk/bin/extcheck
./jdk/bin/keytool
./jdk/LICENSE
./jdk/.manifest
./ddl_setup.sql

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ tail /tmp/untar_oracle_arrow_ggs12c.log
./jdk/bin/javadoc
./jdk/bin/jarsigner
./jdk/bin/pack200
./jdk/bin/rmid
./jdk/bin/jrunscript
./jdk/bin/extcheck
./jdk/bin/keytool
./jdk/LICENSE
./jdk/.manifest
./ddl_setup.sql

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ls
bcpfmt.tpl            ddl_ora10.sql             defgen                           extract        libggnnzitp.so      marker_status.sql       role_setup.sql
bcrypt.txt            ddl_ora10upCommon.sql     deinstall                        freeBSD.txt    libggparam.so       mgr                     sequence.sql
cachefiledump         ddl_ora11.sql             demo_more_ora_create.sql         ggcmd          libggperf.so        notices.txt             server
cfgtoollogs           ddl_ora9.sql              demo_more_ora_insert.sql         ggMessage.dat  libggrepo.so        oggerr                  sqlldr.tpl
checkprm              ddl_pin.sql               demo_ora_create.sql              ggparam.dat    libicudata.so.48    OPatch                  srvm
chkpt_ora_create.sql  ddl_remove.sql            demo_ora_insert.sql              ggsci          libicudata.so.48.1  oraInst.loc             tcperrs
convchk               ddl_session1.sql          demo_ora_lob_create.sql          help.txt       libicui18n.so.48    oui                     ucharset.h
convprm               ddl_session.sql           demo_ora_misc.sql                install        libicui18n.so.48.1  params.sql              ulg.sql
db2cntl.tpl           ddl_setup.sql             demo_ora_pk_befores_create.sql   inventory      libicuuc.so.48      prvtclkm.plb            UserExitExamples
ddl_cleartrace.sql    ddl_status.sql            demo_ora_pk_befores_insert.sql   jdk            libicuuc.so.48.1    prvtlmpg.plb            usrdecs.h
ddl_create.sql        ddl_staymetadata_off.sql  demo_ora_pk_befores_updates.sql  keygen         libxerces-c.so.28   prvtlmpg_uninstall.sql  zlib.txt
ddl_ddl2file.sql      ddl_staymetadata_on.sql   diagnostics                      label.sql      libxml2.txt         remove_seq.sql
ddl_disable.sql       ddl_tracelevel.sql        dirout                           libantlr3c.so  logdump             replicat
ddl_enable.sql        ddl_trace_off.sql         dirwww                           libdb-6.1.so   marker_remove.sql   retrace
ddl_filter.sql        ddl_trace_on.sql          emsclnt                          libgglog.so    marker_setup.sql    reverse

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ cd oui/bin/
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ ls run*
runConfig.sh  runInstaller  runInstaller.sh  runSSHSetup.sh

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ ./runInstaller -clone -silent -noconfig -defaultHomeName ORACLE_HOME="/u01/app/12.2.0.1/ggs02"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4072 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_01-50-47AM. Please wait ...oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log
.................................................................................................... 100% Done.



Installation in progress (Monday, April 4, 2016 1:50:56 AM PDT)
..........................................................................                                                      72% Done.
Install successful

Linking in progress (Monday, April 4, 2016 1:50:59 AM PDT)
Link successful

Setup in progress (Monday, April 4, 2016 1:50:59 AM PDT)
...........                                                     100% Done.
Setup successful

End of install phases.(Monday, April 4, 2016 1:51:20 AM PDT)
The cloning of OraHome3 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log' for more details.

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log|cut -d ":" -f1|sort -u
INFO

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ cd ../../

oracle@arrow:pooh:/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> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (arrow.localdomain) 2> show

Parameter settings:

SET SUBDIRS    ON
SET DEBUG      OFF

Current directory: /u01/app/12.2.0.1/ggs02

Using subdirectories for all process files

Editor:  vi

Reports (.rpt)                 /u01/app/12.2.0.1/ggs02/dirrpt -- does not yet exist
Parameters (.prm)              /u01/app/12.2.0.1/ggs02/dirprm -- does not yet exist
Replicat Checkpoints (.cpr)    /u01/app/12.2.0.1/ggs02/dirchk -- does not yet exist
Extract Checkpoints (.cpe)     /u01/app/12.2.0.1/ggs02/dirchk -- does not yet exist
Process Status (.pcs)          /u01/app/12.2.0.1/ggs02/dirpcs -- does not yet exist
SQL Scripts (.sql)             /u01/app/12.2.0.1/ggs02/dirsql -- does not yet exist
Database Definitions (.def)    /u01/app/12.2.0.1/ggs02/dirdef -- does not yet exist
Dump files (.dmp)              /u01/app/12.2.0.1/ggs02/dirdmp -- does not yet exist
Masterkey wallet files (.wlt)  /u01/app/12.2.0.1/ggs02/dirwlt -- does not yet exist
Credential store files (.crd)  /u01/app/12.2.0.1/ggs02/dircrd -- does not yet exist


GGSCI (arrow.localdomain) 3> create subdirs

Creating subdirectories under current directory /u01/app/12.2.0.1/ggs02

Parameter files                /u01/app/12.2.0.1/ggs02/dirprm: created
Report files                   /u01/app/12.2.0.1/ggs02/dirrpt: created
Checkpoint files               /u01/app/12.2.0.1/ggs02/dirchk: created
Process status files           /u01/app/12.2.0.1/ggs02/dirpcs: created
SQL script files               /u01/app/12.2.0.1/ggs02/dirsql: created
Database definitions files     /u01/app/12.2.0.1/ggs02/dirdef: created
Extract data files             /u01/app/12.2.0.1/ggs02/dirdat: created
Temporary files                /u01/app/12.2.0.1/ggs02/dirtmp: created
Credential store files         /u01/app/12.2.0.1/ggs02/dircrd: created
Masterkey wallet files         /u01/app/12.2.0.1/ggs02/dirwlt: created
Dump files                     /u01/app/12.2.0.1/ggs02/dirdmp: created


GGSCI (arrow.localdomain) 4> exit
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$

There is no clone.pl for GoldenGate.
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ locate clone.pl
/u01/app/oracle/product/11.2.0/dbhome_1/clone/bin/clone.pl
/u01/app/oracle/product/11.2.0/dbhome_1/clone/bin/prepare_clone.pl
/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/scripts/db/dbclone/db_clone.pl
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl.sbs
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl.sbs.ouibak
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/prepare_clone.pl
/u01/app/oracle/product/12.2.0/dbhome_2/inventory/Templates/clone/bin/clone.pl.sbs
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2011, Oracle. All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.3.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/dbhome_1″ TYPE=”O” IDX=”1″/>
<HOME NAME=”OraDB12Home1″ LOC=”/u01/app/oracle/product/12.2.0/dbhome_1″ TYPE=”O” IDX=”2″/>
<HOME NAME=”OraHome1″ LOC=”/u01/app/oracle/product/12.2.0/dbhome_2″ TYPE=”O” IDX=”3″/>
<HOME NAME=”OraHome2″ LOC=”/u01/app/12.2.0.1/ggs01″ TYPE=”O” IDX=”4″/>
<HOME NAME=”OraHome3″ LOC=”/u01/app/12.2.0.1/ggs02″ TYPE=”O” IDX=”5″/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>


Silent Install GoldenGate 12.2

Mon, 2016-04-04 03:31

There are probably many blogs about installation; however, I tend to do things a little different.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ cat /media/sf_working/oggcore_11gdb.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs01
START_MANAGER=
MANAGER_PORT=
DATABASE_LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall

I like to see installation progress.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /media/sf_working/oggcore_11gdb.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 28152 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4073 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_01-04-39AM. Please wait ...You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   61% Done.
..................................................   69% Done.
..................................................   77% Done.
..................................................   82% Done.
..................................................   87% Done.
..................................................   94% Done.
..................................................   95% Done.

Copy files successful.

Link binaries in progress.
..................................................   95% Done.

Link binaries successful.

Setup files in progress.
..................................................   95% Done.
..................................................   96% Done.
..................................................   97% Done.
..................................................   98% Done.
..................................................   99% Done.
..................................................   100% Done.

Setup files successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2016-04-04_01-04-39AM.log' for more details.
Successfully Setup Software.


oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ cat /u01/app/oraInventory/logs/silentInstall2016-04-04_01-04-39AM.log
silentInstall2016-04-04_01-04-39AM.log
The installation of Oracle GoldenGate Core was successful.

I like to see distinct log type: INFO|WARNING|ERROR.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log|cut -d ":" -f1|sort -u
INFO
WARNING

I like to see what WARNING are all about and to compare with subsequent install.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log|grep "^WARNING: "
WARNING: CVU is not enabled. No CVU based operation will be performed.
WARNING: Unable to find the namespace URI. Reason: Start of root element expected.
WARNING: Unable to find the namespace URI. Reason: Start of root element expected.
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Validation disabled for the state init
WARNING: Validation disabled for the state summary
WARNING: Validation disabled for the state finish

Is WARNING bogus?

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ grep INSTALL_OPTION /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
INFO: Setting value for the property:INSTALL_OPTION in the bean:OGGInstallSettings
 INSTALL_OPTION                                         ORA11g
 INSTALL_OPTION            ORA11g
INFO: adding the variable INSTALL_OPTION to command line args table
oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$

Deinstall GoldenGate 12.2

Mon, 2016-04-04 02:59

I always like to know how to remove software installation in the event I am not happy with how it was installed.

GoldenGate Home is at /u01/app/oracle/12.2/ggs01

Thinking of may be using the same directory structures as Grid Infrastructure, i.e. /u01/app/12.2.0.1/ggs01 with full release version.

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01
$ ls -ld deinstall/
drwxr-xr-x. 2 oracle oinstall 4096 Apr  4 00:24 deinstall/

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01
$ cd deinstall/

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$ ls
deinstall.sh

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$ ./deinstall.sh

ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script.

Proceed with removing Oracle GoldenGate home: /u01/app/oracle/12.2/ggs01 (yes/no)? [no]
yes
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4073 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_12-48-07AM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Monday, April 4, 2016 12:48:12 AM PDT)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Monday, April 4, 2016 12:48:33 AM PDT)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2016-04-04_12-48-07AM.log' for more details.

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$ cat /u01/app/oraInventory/logs/silentInstall2016-04-04_12-48-07AM.log
silentInstall2016-04-04_12-48-07AM.log
Starting deinstall
This deinstallation was successful
End of deinstallations
oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$

Easy Way to Create pfile from spfile

Sun, 2016-04-03 11:50

I learned something new today; so excited as this will make automation so much easier.

create pfile=’/tmp/init@.ora’ from spfile;

The @ automatically substitute SID.

oracle@arrow:tiger:/tmp
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 3 09:45:49 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> set time on
09:45:52 ARROW:(SYS@tiger):PRIMARY> !ls -l /tmp/init*
ls: cannot access /tmp/init*: No such file or directory

09:45:59 ARROW:(SYS@tiger):PRIMARY> create pfile='/tmp/init@.ora' from spfile;

File created.

09:46:04 ARROW:(SYS@tiger):PRIMARY> !ls -l /tmp/init*
-rw-r--r--. 1 oracle oinstall 1165 Apr  3 09:46 /tmp/inittiger.ora

09:46:10 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:/tmp
$


Tablespace Free w Fragmentation Info

Thu, 2016-03-24 14:43

I have been conversing with Liron Amitzi on twitter about his tablespace free SQL script.

He accommodate my request to add fragmentation details so I can verify against what I have.

Looks like I did it right.

You can find Liron script at this blog – Tablespaces Free Space and Stuff

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

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 24 12:35:23 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


TABLESPACE_NAME                   CURR_SIZE     MAX_SIZE    FREE_SIZE    USED_SIZE     PCT_FREE PCT_FREE_TOTAL  FREE_CHUNKS LARGEST_CHUNK
------------------------------ ------------ ------------ ------------ ------------ ------------ -------------- ------------ -------------
USERS                                  1024         1024      1021.38         2.62        99.74          99.74            2       1021.13
UNDOTBS1                                 80        32767        64.75        15.25        80.94          99.95           12            37
SYSAUX                                  400        32767       218.94       181.06        54.73          99.45            1        218.94
SYSTEM                                  500        32767       231.19       268.81        46.24          99.18            3           231

ARROW:(SYS@tiger):PRIMARY> @free.sql
Enter value for 1: *

                                                              Database Freespace Summary

TABLESPACE_NAME                      BLKSZ   DFCT      CT_FRAG MB_FREE_FRAG     MB_FREE     MB_TOTAL PCT_USED MAX_MB_FREE MAX_PCT_USED
----------------------------------- ------ ------ ------------ ------------ ----------- ------------ -------- ----------- ------------
*m s SYSTEM                           8192      1            3          231         231          499    53.71      32,767          .82
*m s UNDOTBS1                         8192      1           12           37          65           79    53.16      32,767          .13
*a s SYSAUX                           8192      1            1          219         219          399    45.13      32,767          .55
*m s USERS                            8192      1            2        1,021       1,021         1023      .18       1,023          .18
                                           ------                           -----------                       -----------
sum                                             4                                 1,536                            99,324

ARROW:(SYS@tiger):PRIMARY>

free.sql

set line 150 echo off verify off
ttitle -
   center  'Database Freespace Summary'  skip 2
break   on report
COMPUTE sum of mb_used on report
COMPUTE sum of mb_free on report
COMPUTE sum of max_mb_free on report
COMPUTE sum of dfct on report
COLUMN mb_used       format 99,999,999
COLUMN mb_free       format 99,999,999
COLUMN max_mb_free   format 99,999,999
COLUMN mb_free_frag  format 99,999,999
COLUMN dfct          format 99999
COLUMN blksz         format 99999
COLUMN pct_used      format 999.99
COLUMN max_pct_used  format 999.99
SELECT
  DECODE(extent_management,'LOCAL','*',' ') ||
  DECODE(segment_space_management,'AUTO','a ','m ') ||
  DECODE(allocation_type,'SYSTEM','s ','u ') ||
  fs.tablespace_name tablespace_name, block_size blksz, dfct,
  fs.nfrag                      ct_frag,
  fs.mxfrag           / 1048576 mb_free_frag,
  fs.free_bytes       / 1048576 mb_free,
  df.avail            / 1048576 mb_total,
  (df.avail-fs.mxfrag)/df.avail*100 pct_used,
  df.max_bytes        / 1048576 max_mb_free,
  (df.avail-fs.mxfrag)/df.max_bytes*100 max_pct_used
FROM dba_tablespaces ts,
  (SELECT tablespace_name, count(*) dfct,
   SUM(decode(maxbytes,0,user_bytes,greatest(maxbytes,user_bytes))) max_bytes,
   SUM(user_bytes) avail
   FROM dba_data_files
   GROUP BY tablespace_name
  ) df,
  (SELECT tablespace_name,  nvl(sum(bytes),0) free_bytes, count(bytes) nfrag, nvl(max(bytes),0) mxfrag
   FROM dba_free_space
   GROUP BY tablespace_name
  ) fs
WHERE fs.tablespace_name = ts.tablespace_name(+)
AND fs.tablespace_name   = df.tablespace_name
AND regexp_like(fs.tablespace_name,'&1','i')
ORDER BY pct_used desc

Don’t Know cron

Sat, 2016-03-19 20:54

Learn something new every day !!!

Did you know the Date and Day from cron is OR condition and not AND?

Wanted to schedule cron to run every 3rd Friday.

This ended up running on the 19th which is Saturday

$ crontab -l|head -1
### Schedule below will run Every Friday OR Date 15-21
41 18 15-21 * 5 /bin/date > /tmp/date.out

$ date
Sat Mar 19 18:40:05 PDT 2016

$ date
Sat Mar 19 18:41:17 PDT 2016

$ ll /tmp/date.out
-rw-r--r--. 1 oracle oinstall 29 Mar 19 18:41 /tmp/date.out

$ cat /tmp/date.out
Sat Mar 19 18:41:01 PDT 2016

OOPS!

++++++++++

Shell script will need to check for day and exit if not the correct day.

$ date
Sat Mar 19 18:43:13 PDT 2016

$ crontab -l|head -1
44 18 15-21 * * /home/oracle/t.sh > /tmp/date.out

++++++++++
$ cat t.sh
#!/bin/sh -ex
# Exit if not Friday
if [[ $(date +%u) -ne 5 ]] ; then
    exit
fi
date
++++++++++

$ date
Sat Mar 19 18:44:26 PDT 2016

$ ll /tmp/date.out
-rw-r--r--. 1 oracle oinstall 0 Mar 19 18:44 /tmp/date.out

$ ./t.sh
++ date +%u
+ [[ 6 -ne 5 ]]
+ exit

Option 2: check day from cron.

$ date
Sun Mar 20 04:44:39 PDT 2016

$ crontab -l|head -1
45 04 15-21 * * [ $(date +\%u) -eq 7 ] && /home/oracle/t2.sh > /tmp/date.out

++++++++++
$ cat t2.sh
date
++++++++++

$ date
Sun Mar 20 04:45:01 PDT 2016

$ ll /tmp/date.out
-rw-r--r--. 1 oracle oinstall 29 Mar 20 04:45 /tmp/date.out

$ cat /tmp/date.out
Sun Mar 20 04:45:01 PDT 2016

Tested on:
oracle@arrow:tiger:/home/oracle
$ uname -an
Linux arrow.localdomain 3.8.13-68.3.2.el6uek.x86_64 #2 SMP Tue Jun 9 17:07:32 PDT 2015 x86_64 x86_64 x86_64 GNU/Linux

oracle@arrow:tiger:/home/oracle
$ cat /etc/oracle-release
Oracle Linux Server release 6.6
oracle@arrow:tiger:/home/oracle
$

++++++++++

Updated: Mar 26, 2016

$ crontab -l|head -1
27 20 15-25 * * /usr/bin/test `date +\%a` = Fri && /home/oracle/t2.sh > /tmp/t2.sh.log 2>&1

Both && and || logic produce identical results for the correct day.

$ date
Fri Mar 25 21:21:26 PDT 2016

$ test `date +\%a` = Fri;echo $?
0

pwd if test = 0

$ test `date +\%a` = Fri && pwd; echo $?
/home/oracle
0

$ test `date +\%a` != Fri;echo $?
1

pwd if test != 0

$ test `date +\%a` != Fri || pwd; echo $?
/home/oracle
0

Noticed the difference in return code as mention in reference: 

http://docstore.mik.ua/orelly/unix3/upt/ch25_02.htm

Using && returns 1 while using || returns 0 for incorrect day.

It’s not Monday; hence, pwd did not return values.

$ test `date +\%a` = Mon && pwd; echo $?
1

$ test `date +\%a` != Mon || pwd; echo $?
0

 

The right side of && (pwd) will only be evaluated if the left side exit status = 0.

$ test `date +\%a` = Mon;echo $?
1

pwd if test = 0

$ test `date +\%a` = Mon && pwd; echo $?
1

The right side of || (pwd) will only be evaluated if the left side exit status is != 0.

$ test `date +\%a` != Mon;echo $?
0

pwd if test != 0

$ test `date +\%a` != Mon || pwd; echo $?
0

 


Pages