Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 1 hour 42 min ago

Losing /u01

Wed, 2016-07-27 10:54

There was an incident where /u01 was lost.

This means having to reinstall and patch software again.

When installing grid and I encountered:

CRS is already configured on this node for crshome=/u01/app/oracle/product/11.2.0/grid

Ah Crap! This is me being melodramatic.

The system was using Oracle Restart, it needs to be removed.

# /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose  

In hindsight, it would have been much easier to delete from /etc/oracle.

Guess what? ASM will need to be recreated and there’s a post for that.

ReCreate ASM Disks

++++++++++

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
/u01/app/oracle/product/11.2.0/grid/bin/crsctl query crs activeversion ... failed rc=-1 with message:
 Failure in execution (rc=-1, 0, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl query crs activeversion 

CRS is already configured on this node for crshome=/u01/app/oracle/product/11.2.0/grid
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home. 

# ps -ef|grep pmon
root     143547 143405  0 15:22 pts/2    00:00:00 grep pmon

++++++++++

# /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose  
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl stop resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl delete resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl stop has -f
You must kill ohasd processes or reboot the system to properly 
cleanup the processes started by Oracle clusterware
Successfully deconfigured Oracle Restart stack
# 

++++++++++

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
LOCAL ADD MODE 
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE 
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node arrow successfully pinned.
Adding Clusterware entries to inittab

arrow     2016/07/26 15:30:21     /u01/app/oracle/product/11.2.0/grid/cdata/arrow/backup_20160726_153021.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
# 

++++++++++

$ ll /etc/oracle
total 2256
drwxrwx--- 2 oracle oinstall    4096 Jul 26 15:29 lastgasp
-rw-r----- 1 oracle oinstall      92 Jul 26 15:29 ocr.loc
-rw-r--r-- 1 root   root          16 Jul 26 15:29 ocr.loc.orig
-rw-r----- 1 root   oinstall     125 Jul 26 15:29 olr.loc
-rw-r--r-- 1 root   root           0 Jul 26 15:29 olr.loc.orig
drwxrwx--- 5 root   oinstall    4096 Jul 26 15:29 oprocd
drwxr-x--- 3 root   oinstall    4096 Jul 26 15:29 scls_scr
-rws--x--- 1 root   oinstall 2280039 Jul 26 15:59 setasmgid

+++++++++

ASM disk creation failed because it's already a MEMBER.

SQL> alter system set asm_diskstring='ORCL:*' scope=both;

System altered.

SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*';
CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk 'ORCL:REDO01_002' belongs to diskgroup "REDO01"
ORA-15033: disk 'ORCL:REDO01_001' belongs to diskgroup "REDO01"


SQL> CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*';
CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk 'ORCL:DATA01_002' belongs to diskgroup "DATA01"
ORA-15033: disk 'ORCL:DATA01_001' belongs to diskgroup "DATA01"

SQL> select HEADER_STATUS||','||path from v$asm_disk;

HEADER_STATUS||','||PATH
--------------------------------------------------------------------------------
MEMBER,ORCL:DATA01_001
MEMBER,ORCL:DATA01_002
MEMBER,ORCL:DATA01_003
MEMBER,ORCL:DATA01_004
MEMBER,ORCL:REDO01_005
MEMBER,ORCL:REDO01_001
MEMBER,ORCL:REDO01_002
MEMBER,ORCL:REDO01_003
MEMBER,ORCL:REDO01_004
MEMBER,ORCL:DATA01_005

10 rows selected.

SQL> 

ReCreate ASM Disks

Wed, 2016-07-27 08:33

The following ASM disks were created which does not follow convention:
ASM_REDO01_006, ASM_REDO01_007, ASM_REDO01_008, ASM_REDO01_009, and ASM_REDO01_010

It should be:
ASM_REDO01_001, ASM_REDO01_002, ASM_REDO01_003, ASM_REDO01_004, and ASM_REDO01_005

Hence, we will need to recreate them. Here’s the process for me to remember.

conn / as sysasm
drop diskgroup DATA01;
drop diskgroup REDO01;
SQL> show parameter disk

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string
asm_diskstring			     string	 ORCL:*

++++++++++

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_001
Disk "ASM_DATA01_001" is a valid ASM disk on device [120,33]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_002
Disk "ASM_DATA01_002" is a valid ASM disk on device [120,49]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_003
Disk "ASM_DATA01_003" is a valid ASM disk on device [120,65]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_004
Disk "ASM_DATA01_004" is a valid ASM disk on device [120,81]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_005
Disk "ASM_DATA01_005" is a valid ASM disk on device [120,97]

++++++++++

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_006
Disk "ASM_REDO01_006" is a valid ASM disk on device [120,113]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_007
Disk "ASM_REDO01_007" is a valid ASM disk on device [120,129]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_008
Disk "ASM_REDO01_008" is a valid ASM disk on device [120,145]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_009
Disk "ASM_REDO01_009" is a valid ASM disk on device [120,161]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_010
Disk "ASM_REDO01_010" is a valid ASM disk on device [120,177]

++++++++++

$ ls -l /dev/|grep 120
drwxr-xr-x  6 root root       120 May  5 13:19 disk
brw-r-----  1 root disk  120,   0 May 18 08:35 emcpowera
brw-r-----  1 root disk  120,   1 May 18 08:42 emcpowera1
brw-r-----  1 root disk  120,  16 May 18 08:35 emcpowerb
brw-r-----  1 root disk  120,  17 May 18 08:43 emcpowerb1
brw-r-----  1 root disk  120,  32 May 18 00:19 emcpowerc
brw-r-----  1 root disk  120,  33 May 18 08:36 emcpowerc1
brw-r-----  1 root disk  120,  48 May 18 00:19 emcpowerd
brw-r-----  1 root disk  120,  49 May 18 08:36 emcpowerd1
brw-r-----  1 root disk  120,  64 May 18 00:19 emcpowere
brw-r-----  1 root disk  120,  65 May 18 08:36 emcpowere1
brw-r-----  1 root disk  120,  80 May 18 00:19 emcpowerf
brw-r-----  1 root disk  120,  81 May 18 08:36 emcpowerf1
brw-r-----  1 root disk  120,  96 May 18 00:29 emcpowerg
brw-r-----  1 root disk  120,  97 May 18 08:36 emcpowerg1
brw-r-----  1 root disk  120, 112 May 18 00:19 emcpowerh
brw-r-----  1 root disk  120, 113 May 18 08:36 emcpowerh1
brw-r-----  1 root disk  120, 128 May 18 00:19 emcpoweri
brw-r-----  1 root disk  120, 129 May 18 08:36 emcpoweri1
brw-r-----  1 root disk  120, 144 May 18 00:19 emcpowerj
brw-r-----  1 root disk  120, 145 May 18 08:36 emcpowerj1
brw-r-----  1 root disk  120, 160 May 18 00:19 emcpowerk
brw-r-----  1 root disk  120, 161 May 18 08:36 emcpowerk1
brw-r-----  1 root disk  120, 176 May 18 00:19 emcpowerl
brw-r-----  1 root disk  120, 177 May 18 08:36 emcpowerl1
brw-r-----  1 root disk  120, 192 May 18 08:35 emcpowerm
brw-r-----  1 root disk  120, 193 May 18 08:40 emcpowerm1

+++++++++

# /etc/init.d/oracleasm deletedisk ASM_REDO01_006
Removing ASM disk "ASM_REDO01_006":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_007
Removing ASM disk "ASM_REDO01_007":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_008
Removing ASM disk "ASM_REDO01_008":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_009
Removing ASM disk "ASM_REDO01_009":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_010
Removing ASM disk "ASM_REDO01_010": 

++++++++++

# /etc/init.d/oracleasm createdisk DATA01_001 /dev/emcpowerc1
Marking disk "DATA01_001" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_002 /dev/emcpowerd1
Marking disk "DATA01_002" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_003 /dev/emcpowere1
Marking disk "DATA01_003" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_004 /dev/emcpowerf1
Marking disk "DATA01_004" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_005 /dev/emcpowerg1
Marking disk "DATA01_005" as an ASM disk:                  [  OK  ]

+++++++++

# /etc/init.d/oracleasm createdisk REDO01_001 /dev/emcpowerh1
Marking disk "REDO01_001" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_002 /dev/emcpoweri1
Marking disk "REDO01_002" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_003 /dev/emcpowerj1
Marking disk "REDO01_003" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_004 /dev/emcpowerk1
Marking disk "REDO01_004" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_005 /dev/emcpowerl1
Marking disk "REDO01_005" as an ASM disk:                  [  OK  ]

+++++++++

# /etc/init.d/oracleasm listdisks
DATA01_001
DATA01_002
DATA01_003
DATA01_004
DATA01_005
REDO01_001
REDO01_002
REDO01_003
REDO01_004
REDO01_005

SQL> select HEADER_STATUS||','||path from v$asm_disk;

HEADER_STATUS||','||PATH
--------------------------------------------------------------------------------
PROVISIONED,ORCL:DATA01_001
PROVISIONED,ORCL:DATA01_002
PROVISIONED,ORCL:DATA01_003
PROVISIONED,ORCL:DATA01_004
PROVISIONED,ORCL:REDO01_005
PROVISIONED,ORCL:REDO01_001
PROVISIONED,ORCL:REDO01_002
PROVISIONED,ORCL:REDO01_003
PROVISIONED,ORCL:REDO01_004
PROVISIONED,ORCL:DATA01_005

10 rows selected.


SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*';

Diskgroup created.

SQL> CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*';

Diskgroup created.

SQL> show parameter disk

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string	 REDO01, DATA01
asm_diskstring			     string	 ORCL:*

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

+++++++++

$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    511994   511936                0          511936              0             N  DATA01/
MOUNTED  EXTERN  N         512   4096  1048576     51195    51137                0           51137              0             N  REDO01/
ASMCMD> 

++++++++++

$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA01.dg
               ONLINE  ONLINE       arrow                                    
ora.LISTENER.lsnr
               ONLINE  ONLINE       arrow                                    
ora.REDO01.dg
               ONLINE  ONLINE       arrow                                    
ora.asm
               ONLINE  ONLINE       arrow                Started             
ora.ons
               OFFLINE OFFLINE      arrow                                    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       arrow                                    
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.evmd
      1        ONLINE  ONLINE       arrow                                    
$

GoldenGate 12.2 Object Exclude

Sun, 2016-07-24 13:06

1.8 Details of Support for Objects and Operations in Oracle DDL

The following tables are excluded by default.

  "*.AQ$*", // advanced queues
  "*.DR$*$*", // oracle text
  "*.M*_*$$", // Spatial index
  "*.MLOG$*", // materialized views
  "*.OGGQT$*",
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and Table
  "*.SYS_IMPORT_TABLE*",
  "*.CMP*$*", // space management, rdbms >= 12.1
  "*.DBMS_TABCOMP_TEMP_*", // space management, rdbms < 12.1
  "*.MDXT_*$*" // Spatial extended statistics tables

If you specify JOB_NAME for datapump, then you may have to manually add the exclusion.

Next, during migration from 10g to 12c with Goldengate, extract was failing with

2016-06-20 01:00:16  ERROR   OGG-00665  Oracle GoldenGate Capture for Oracle, e_db10g.prm:  OCI Error getting OCI_ATTR_NAME for UDT SYS.ANYDATA (status = 24328-ORA-24328: illegal attribute value), SQL.
2016-06-20 01:00:17  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_db10g.prm:  PROCESS ABENDING.

The following exclusion was added to the extract.

SCHEDULER$_JOB_ARG

It might be better to add “*.SCHEDULER$*”?


Troubleshooting ORA-02049: timeout: distributed transaction waiting for lock

Sat, 2016-07-23 17:45

Controlling Connections Established by Database Links

Tracing can be done using – ALTER SYSTEM SET EVENTS ‘2049 trace name ERRORSTACK level 3’;

Nice note from MOS.

How to Obtain a System State Trace BEFORE the Error ORA-2049 Occurs, While Still Experiencing the Contention (Doc ID 789517.1)

Commit after select from dblink – blocking lock.
Session 1:
++++++++++
04:44:51 ARROW:(DEMO@leo):PRIMARY>
04:45:30 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.01
04:45:36 ARROW:(DEMO@leo):PRIMARY> commit;

Commit complete.

Elapsed: 00:00:00.00
04:45:41 ARROW:(DEMO@leo):PRIMARY>
04:45:49 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;

++++++++++
Session 2:
04:45:18 ARROW:(DEMO@leo):PRIMARY> update t set id=100;

1 row updated.

Elapsed: 00:00:00.00
04:45:21 ARROW:(DEMO@leo):PRIMARY> @/media/sf_working/sql/b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   DEMO            32,17        74nhxnyztg454    402 SQL*Net message to c ACTIVE      0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           41,271       1qfpvr7brd2pq    113 enq: TX - row lock c ACTIVE      8 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   DEMO            7741d4und71ph with s as ( SELECT decode(level,1,'BLOCKING','WAITING') state, LPAD('*',(level-1
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
04:54:19 ARROW:(DEMO@leo):PRIMARY> select count(*) from t;

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

Elapsed: 00:00:00.01
05:00:49 ARROW:(DEMO@leo):PRIMARY>
No commit after select from dblink – ORA-02049: timeout: distributed transaction waiting for lock.

Session is automatically killed based on database paraneter tributed_lock_timeout (default is 60s)

Session 1:
++++++++++
21:58:06 ARROW:(DEMO@leo):PRIMARY> update t set id=100;

1 row updated.

Elapsed: 00:00:00.01
21:58:14 ARROW:(DEMO@leo):PRIMARY>

Session 2:
+++++++++
21:58:45 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.00
21:58:51 ARROW:(DEMO@leo):PRIMARY> update t set id=1;
update t set id=1
       *
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:01:00.01
21:59:59 ARROW:(DEMO@leo):PRIMARY>

Don’t forget to commit or rollback after using dblinks.

UPDATED: Tried to determine if there was a way to monitor distributed transaction and in this test case was not able to.

Session 1: 
++++++++++
15:59:32 ARROW:(MDINH@leo):PRIMARY> update demo.t set id=100;

1 row updated.

Elapsed: 00:00:00.01
15:59:43 ARROW:(MDINH@leo):PRIMARY>

Session 2: Distributed Transaction
++++++++++
16:00:43 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.00
16:00:47 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;
update t set id=-9999
       *
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:10:00.00
16:10:57 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;
update t set id=-9999
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:10:00.01
16:23:03 ARROW:(DEMO@leo):PRIMARY>

Session 3: Monitoring
++++++++++
16:21:23 ARROW:(SYS@leo):PRIMARY> show parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     600
16:21:35 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   22 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE      9 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:21:38 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   22 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE      9 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:22:10 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   23 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE     10 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:22:56 ARROW:(SYS@leo):PRIMARY> @b.sql

no rows selected

Elapsed: 00:00:00.01

no rows selected

Elapsed: 00:00:00.00
16:23:12 ARROW:(SYS@leo):PRIMARY>

GoldenGate 12.2 TROUBLESHOOTING USING GETENV

Thu, 2016-07-21 21:14

I was faced with a very strange situation.

When using SETENV (ORACLE_SID = db01), replicat ABEND with  OGG-00664  OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available

When using SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1), replicat starts without any issues.

I know what you are thinking, why not set both ORACLE_SID and ORACLE_HOME and you are correct since this is best practices.

The client already knows this and wanted to troubleshoot why it was failing?

Any ideas? I scoured the internet and MOS and finally found that using get_env was the key to troubleshooting.

Tried tracing the replicat and did not work since replicat abended.

NOTE:  Environment variables are all set correctly from the OS.

BAD:
REPLICAT R_2D2
SETENV (ORACLE_SID = db01)

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1

getenv (ORACLE_SID)
ORACLE_SID = b01
USERIDALIAS ggadmin

2016-07-21 18:33:46 ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory).
GOOD:
REPLICAT R_2D2

SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1

getenv (ORACLE_SID)
ORACLE_SID = db01
USERIDALIAS ggadmin

Notice, there are 2 different ORACLE_HOMEs being used.

Bad one is ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1 since the HOME does not exists.

Wait a minute, how is it possible the HOME does not exists and where did Goldengate get this information ???

Let’s find the installation log.
/u01/app/oraInventory/logs> grep "MANAGER_PORT" installActions2016*.log|uniq
installActions2016-05-09_04-57-53PM.log:INFO: Setting value for the property:MANAGER_PORT in the bean:OGGInstallSettings
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                       
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                                                    
installActions2016-05-09_04-57-53PM.log:INFO: adding the variable MANAGER_PORT to command line args table
installActions2016-05-09_04-57-53PM.log:INFO: Setting variable 'MANAGER_PORT' to '7809'. Received the value from the command line.
installActions2016-05-09_04-57-53PM.log:INFO: This variable MANAGER_PORT is not added to the global context map

/u01/app/oraInventory/logs> grep DATABASE_HOME installActions2016-05-09_04-57-53PM.log
INFO: Setting value for the property:DATABASE_HOME in the bean:OGGInstallSettings
 DATABASE_HOME             /u01/app/oracle/product/11.2.0.3/dbhome_1  
 DATABASE_HOME             /u01/app/oracle/product/11.2.0.3/dbhome_1                               
 
INFO: adding the variable DATABASE_HOME to command line args table
INFO: Setting variable 'DATABASE_HOME' to '/u01/app/oracle/product/11.2.0.3/dbhome_1'. Received the value from the command line.
INFO: This variable DATABASE_HOME is not added to the global context map

There it is, Goldengate is using ORACLE_HOME defined from the installation.

DATABASE_LOCATION is not required, so why set it?

Admittedly, I have have been setting DATABASE_LOCATION but will not be doing so any more.

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=

Lastly, I have not attempted to reproduce the error. Too lazy. May be you can help?


Different names for 12c CDB

Thu, 2016-07-21 09:15

Truthfully, I am feeling a little lazy to write a nice blog.

Configuring listener.ora and tnsnames.ora, varying names for database, connecting to database.

Summary:
db_name                              string      tmnt
db_unique_name                       string      tmnt_a
instance_name                        string      tmnt01

ADR Home /u01/app/oracle/diag/rdbms/tmnt_a/tmnt

Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Details:
[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat listener.ora
LISTENER_TMNT =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
      (ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767))
    )
  )

SID_LIST_LISTENER_TMNT =
  (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = tmnt)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = tmnt)
    )
  )

USE_SID_AS_SERVICE_LISTENER_TMNT = ON
INBOUND_CONNECT_TIMEOUT_LISTENER_TMNT = 120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_TMNT = OFF
DIAG_ADR_ENABLED_LISTENER_TMNT = OFF
LOGGING_LISTENER_TMNT = OFF
TRACE_LEVEL_LISTENER_TMNT = OFF
SAVE_CONFIG_ON_STOP_LISTENER_TMNT = TRUE

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat tnsnames.ora
APRIL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = APRIL)
    )
  )

TMNT_A =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TMNT_A)
    )
  )

TMNT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SID = tmnt)
    )
  )

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ lsnrctl status listener_tmnt

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JUL-2016 06:58:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener_tmnt
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JUL-2016 06:23:03
Uptime                    0 days 0 hr. 35 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)))
Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
The command completed successfully

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ ps -ef|grep pmon
oracle    2572     1  0 Jul20 ?        00:00:02 ora_pmon_tmnt
oracle    8998  3716  0 06:58 pts/0    00:00:00 grep pmon

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:58:58 2016

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

Last Successful login time: Thu Jul 21 2016 06:57:32 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt_a

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:06 2016

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

Last Successful login time: Thu Jul 21 2016 06:58:58 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt_a):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@april

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:13 2016

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

Last Successful login time: Thu Jul 21 2016 06:59:06 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

APRIL:(SYSTEM@april):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus sys/oracle@tmnt_a as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:22 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@tmnt_a):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      tmnt
db_unique_name                       string      tmnt_a
global_names                         boolean     FALSE
instance_name                        string      tmnt01
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      tmnt_a
CDB$ROOT:(SYS@tmnt_a):PRIMARY> @/home/oracle/diag_info.sql

   INST_ID NAME                           VALUE                                                                                CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
         1 Diag Enabled                   TRUE                                                                                      0
         1 ADR Base                       /u01/app/oracle                                                                           0
         1 ADR Home                       /u01/app/oracle/diag/rdbms/tmnt_a/tmnt                                                    0
         1 Diag Trace                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace                                              0
         1 Diag Alert                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/alert                                              0
         1 Diag Incident                  /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/incident                                           0
         1 Diag Cdump                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/cdump                                              0
         1 Health Monitor                 /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/hm                                                 0
         1 Default Trace File             /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace/tmnt_ora_9016.trc                            0
         1 Active Problem Count           0                                                                                         0
         1 Active Incident Count          0                                                                                         0

11 rows selected.

tmnt

CDB$ROOT:(SYS@tmnt_a):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[07:00]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$

12.1.0.2 New Features

Tue, 2016-07-19 21:51

Just a quick note as I play with CDB/PDB.

No more triggers!

PDB State Management Across CDB

select con_name, instance_name, state from DBA_PDB_SAVED_STATES;
alter pluggable database PDB1 SAVE STATE;
alter pluggable database ALL SAVE STATE;
alter pluggable database ALL DISCARD STATE;
alter pluggable database ALL OPEN;

startup force mount exclusive restrict;
drop database;

Drop database still does not clean up ADR.

[19:32]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 19:32:38 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@cdb12c):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all open;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all SAVE state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

CON_NAME INSTANCE_NAME STATE
-------- ------------- --------------
PDB2     cdb12c        OPEN
PDB1     cdb12c        OPEN

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all DISCARD state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
CDB$ROOT:(SYS@cdb12c):PRIMARY> !ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfilecdb12c.ora  spfiletmnt.ora

CDB$ROOT:(SYS@cdb12c):PRIMARY> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
CDB$ROOT:(SYS@cdb12c):PRIMARY>
CDB$ROOT:(SYS@cdb12c):PRIMARY> exit
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ps -ef|grep pmon
oracle    7151     1  0 13:04 ?        00:00:01 ora_pmon_tmnt
oracle   16898  5756  0 19:39 pts/1    00:00:00 grep pmon
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfiletmnt.ora
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/diag/rdbms/
cdb12c  tmnt_a
[19:40]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$

Create 12c CDB using dbca or sqlplus ???

Tue, 2016-07-19 17:27

How do you typically create database, using dbca or sqlplus?

I am still surprised with all the bloatware added for creating database using dbca.

Also, is there a reason to prefix PDB with pdb?

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 28
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ dbca -silent \
> -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true \
> -gdbName cdb12c -sid cdb12c \
> -SysPassword oracle -SystemPassword oracle \
> -numberOfPdbs 2 -pdbName pdb \
> -pdbadminUsername pdba -pdbadminPassword oracle \
> -emConfiguration NONE \
> -redoLogFileSize 100 \
> -storageType FS -datafileDestination /oradata \
> -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 \
> -totalMemory 1024 -databaseType MULTIPURPOSE \
> -initparams audit_trail=NONE

Registering database with Oracle Restart
4% complete
Copying database files
5% complete
6% complete
12% complete
17% complete
22% complete
30% complete
Creating and starting Oracle instance
32% complete
35% complete
36% complete
37% complete
41% complete
44% complete
45% complete
48% complete
Completing Database Creation
50% complete
53% complete
55% complete
63% complete
71% complete
74% complete
Creating Pluggable Databases
79% complete
84% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb12c/cdb12c.log" for further details.

[15:11]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 48
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 15:10 hc_cdb12c.dat
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 14:56 lkCDB12C
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 7680 Jul 19 15:07 orapwcdb12c
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 3584 Jul 19 15:11 spfilecdb12c.ora
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
#Backup file is  /u01/app/12.1.0.2/grid/srvm/admin/oratab.bak.arrow line added by Agent
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1
cdb12c:/u01/app/oracle/product/12.1.0.2/db_1:N          # line added by Agent

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:12:33 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
CDB12C    DV           Oracle Database Vault                    12.1.0.2.0                     VALID
CDB12C    APEX         Oracle Application Express               4.2.5.00.08                    VALID
CDB12C    OLS          Oracle Label Security                    12.1.0.2.0                     VALID
CDB12C    SDO          Spatial                                  12.1.0.2.0                     VALID
CDB12C    ORDIM        Oracle Multimedia                        12.1.0.2.0                     VALID
CDB12C    CONTEXT      Oracle Text                              12.1.0.2.0                     VALID
CDB12C    OWM          Oracle Workspace Manager                 12.1.0.2.0                     VALID
CDB12C    XDB          Oracle XML Database                      12.1.0.2.0                     VALID
CDB12C    CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
CDB12C    CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID
CDB12C    JAVAVM       JServer JAVA Virtual Machine             12.1.0.2.0                     VALID
CDB12C    XML          Oracle XDK                               12.1.0.2.0                     VALID
CDB12C    CATJAVA      Oracle Database Java Packages            12.1.0.2.0                     VALID
CDB12C    APS          OLAP Analytic Workspace                  12.1.0.2.0                     VALID
CDB12C    XOQ          Oracle OLAP API                          12.1.0.2.0                     VALID
CDB12C    RAC          Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF

16 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Manually created CDB.
[15:14]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ . oraenv <<< tmnt ORACLE_SID = [cdb12c] ? The Oracle base remains unchanged with value /u01/app/oracle 

[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs 
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:14:10 2016 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          MOUNTED
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
TMNT      XDB          Oracle XML Database                      12.1.0.2.0                     VALID
TMNT      CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
TMNT      CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$

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
$

 


Pages