database error after upgrading oracle database to 11.2.0.4

Issue:
Recently I had upgraded the oracle database from 11.1.0.7 to 11.2.0.4 and upgrade  went fine and one of the application job failed with below error.

ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.lang.RuntimePermission accessClassInPackage.sun.misc) has not been granted to O1MGR. The PL/SQL to grant this is dbms_java.grant_permission( 'O1MGR', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.misc', '' )

Root cause:

This is the bug on the 11204 .

solution:

Changes in the 11.2.0.4 DB required additional grants be enabled for the GLOGOWNER user.

1. Log into the Database as SYSDBA
2. Run the following:

begin
 dbms_java.grant_permission('GLOGOWNER'
  , 'SYS:java.lang.RuntimePermission'
  ,'accessClassInPackage.sun.misc', '' ) ;
end;
/

Refer DOC:(Doc ID 1945137.1)

./runInstaller failed with OUI-10022 ,GUI error when installing oracle database 11.2.0.4

issue:

I got the below error when Installing oracle database 11.2.0.4 software using GUI and binary installation aborted.

./runInstaller failed with OUI-10022: The target area /u01../orainventory cannot be used because it is in an invalid state.

Root cause:

1.oracle inventory may be corrupted

2.oracle inventory file system full or 100% used

3.oracle inventory location updated in correctly.

4.permission issue on the inventory file

Solution:

when I checked my oraInventory fine and does not corrupted and file system we have more space,but the issue is orainst.loc file has wrong path and I corrected the  orainst.loc path and installation went fine.

 $ vi oraInst.loc

cd /etc/

"oraInst.loc" 11 lines, 474 characters

#inventory_loc=/app/oracle/oraInventory

#inventory_loc=/app/oracle/oraInventory

inventory_loc=/app/oracle/oraInventory

inst_group=dba

~

OPatch failed with error code 160 on IBM AIX 64 bit power machine.

OPatch failed with error code 160 on IBM AIX 64 bit power machine.


$ opatch lsinventory
Invoking OPatch 11.2.0.1.3

Oracle Interim Patch Installer version 11.2.0.1.3
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : /appcdcp/oracle/product/11.1.0
Central Inventory : /appcdcp/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.3
OUI version       : 11.1.0.7.0
OUI location      : /appcdcp/oracle/product/11.1.0/oui
Log file location : /appcdcp/oracle/product/11.1.0/cfgtoollogs/opatch/opatch2018-01-05_14-15-02PM.log

Patch history file: /appcdcp/oracle/product/11.1.0/cfgtoollogs/opatch/opatch_history.txt

Unhandled exception
Type=Segmentation error vmState=0x00000000
J9Generic_Signal_Number=00000004 Signal_Number=0000000b Error_Value=00000000 Signal_Code=00000033
Handler1=09001000A103FC78 Handler2=09001000A0BC1538
R0=0900000007661AC0 R1=0FFFFFFFFFFFDF80 R2=07000000007CA460 R3=000000000000000B
R4=0000000000000000 R5=0900000007661AC0 R6=0000000111A03689 R7=0000000000000008
R8=00000001100A0200 R9=0000000014930000 R10=000000011191E580 R11=090000000766DC38
R12=090000000351D714 R13=00000001100138A0 R14=0000000111A03678 R15=000000011009FC00
R16=0000000110E8EBD0 R17=000000011191C408 R18=09001000A10433B0 R19=0000000000000031
R20=000000011191C4C8 R21=0000000111A036A8 R22=000000011009FC80 R23=0000000000000000
R24=09001000A1051A50 R25=09001000A10513C8 R26=070000000002C7B8 R27=0000000110016ED0
R28=0000000000000000 R29=09001000A0A39C98 R30=0FFFFFFFFFFFE510 R31=FFFFFFFFFFFFFFF8
IAR=090000000766DC38 LR=00000001106968BC MSR=A00000000000D032 CTR=090000000766DC38
CR=4254504420002000 FPSCR=8202000000000000 XER=2000200082020000
FPR0 0000000800000008 (f: 8.000000, d: 1.697597e-313)
FPR1 41e0000000000000 (f: 0.000000, d: 2.147484e+09)
FPR2 3fe8000000000000 (f: 0.000000, d: 7.500000e-01)
FPR3 3fee666660000000 (f: 1610612736.000000, d: 9.500000e-01)
FPR4 4530000000000000 (f: 0.000000, d: 1.934281e+25)
FPR5 4530000000000000 (f: 0.000000, d: 1.934281e+25)
FPR6 3ff0000000000000 (f: 0.000000, d: 1.000000e+00)
FPR7 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR8 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR9 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR10 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR11 4026000000000000 (f: 0.000000, d: 1.100000e+01)
FPR12 3fe8000000000000 (f: 0.000000, d: 7.500000e-01)
FPR13 4020800000000000 (f: 0.000000, d: 8.250000e+00)
FPR14 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR15 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR16 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR17 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR18 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR19 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR20 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR21 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR22 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR23 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR24 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR25 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR26 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR27 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR28 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR29 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR30 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR31 0000000000000000 (f: 0.000000, d: 0.000000e+00)
Module=/appcdcp/oracle/product/11.1.0/jdk/jre/bin/libj9jit23.so
Module_base_address=0900000007643000
Target=2_30_20070131_11312_BHdSMr (AIX 6.1)
CPU=ppc64 (24 logical CPUs) (0xe00000000 RAM)
JVMDUMP006I Processing Dump Event "gpf", detail "" - Please Wait.
JVMDUMP007I JVM Requesting System Dump using '/appcdcp/oracle/core.20180105.141508.64291038.dmp'
Unhandled exception
Type=Segmentation error vmState=0x0005ff05
J9Generic_Signal_Number=00000004 Signal_Number=0000000b Error_Value=00000000 Signal_Code=00000033
Handler1=09001000A103FC78 Handler2=09001000A0BC1538
R0=0000000000000000 R1=0000000111789810 R2=09001000A15A35E0 R3=0000000112673404
R4=0000000000000000 R5=0000000000000000 R6=0000000000000001 R7=09001000A158B3E8
R8=0000000000000000 R9=09001000A157CDD8 R10=0000000000000000 R11=0000000000000000
R12=090000000791CB68 R13=0000000111793800 R14=00000001129775C4 R15=00000000FBFFFFFF
R16=0000000000000000 R17=0000000000000000 R18=0000000000000000 R19=0000000000000000
R20=0000000000000000 R21=0000000000000000 R22=000000011297758C R23=0000000100000000
R24=0000000112673394 R25=0000000112673324 R26=00000000000000DA R27=00000001129774EC
R28=0000000000000000 R29=0000000000000001 R30=09001000A1556C28 R31=0000000000000000
IAR=090000000776986C LR=090000000791CB90 MSR=A00000000000D032 CTR=09000000076ECE2C
CR=2220444800000004 FPSCR=AA00810000000000 XER=00000004AA008100
FPR0 c33fffffffffffff (f: 4294967296.000000, d: -9.007199e+15)
FPR1 3fe0000000000000 (f: 0.000000, d: 5.000000e-01)
FPR2 433fffffffffffff (f: 4294967296.000000, d: 9.007199e+15)
FPR3 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR4 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR5 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR6 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR7 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR8 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR9 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR10 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR11 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR12 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR13 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR14 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR15 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR16 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR17 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR18 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR19 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR20 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR21 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR22 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR23 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR24 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR25 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR26 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR27 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR28 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR29 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR30 0000000000000000 (f: 0.000000, d: 0.000000e+00)
FPR31 0000000000000000 (f: 0.000000, d: 0.000000e+00)
Module=/appcdcp/oracle/product/11.1.0/jdk/jre/bin/libj9jit23.so
Module_base_address=0900000007643000

Method_being_compiled=sun/misc/FloatingDecimal.doubleValue()D
Target=2_30_20070131_11312_BHdSMr (AIX 6.1)
CPU=ppc64 (24 logical CPUs) (0xe00000000 RAM)
JVMDUMP006I Processing Dump Event "gpf", detail "" - Please Wait.
JVMDUMP010I System Dump written to /appcdcp/oracle/core.20180105.141508.64291038.dmp
JVMDUMP007I JVM Requesting Snap Dump using '/appcdcp/oracle/Snap0001.20180105.141508.64291038.trc'
JVMDUMP010I Snap Dump written to /appcdcp/oracle/Snap0001.20180105.141508.64291038.trc
JVMDUMP007I JVM Requesting Java Dump using '/appcdcp/oracle/javacore.20180105.141508.64291038.txt'
JVMDUMP007I JVM Requesting System Dump using '/appcdcp/oracle/core.20180105.141510.64291038.dmp'
JVMDUMP010I Java Dump written to /appcdcp/oracle/javacore.20180105.141508.64291038.txt
JVMDUMP013I Processed Dump Event "gpf", detail "".

OPatch failed with error code 160


solution:
Please set the below variable and run the opatch again,it will resolve the issue.

export JAVA_COMPILER=NONE


$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /app/oracle/product/11.1.0
Central Inventory : /app/oracle/oraInventory
   from           : /app/oracle/product/11.1.0/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.1.0.7.0
Log file location : /app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch2018-01-05_14-21-49PM_1.log

Lsinventory Output file location : /app/oracle/product/11.1.0/cfgtoollogs/opatch/lsinv/lsinventory2018-01-05_14-21-49PM.txt

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

Oracle Database 11g                                                  11.1.0.6.0
Oracle Database 11g Patch Set 1                                      11.1.0.7.0
There are 2 products installed in this Oracle Home.


Interim patches (2) :


Patch  12419384     : applied on Thu Oct 06 12:32:39 EDT 2011

Patch  6800649      : applied on Thu Mar 11 12:39:54 EST 2010
Unique Patch ID:  10939151.1
   Created on 3 Jun 2009, 20:27:45 hrs PST8PDT
   Bugs fixed:
     6800649



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

OPatch succeeded.

what will happen when you create partition table using CTAS?

I just did some testing on partition tables.

when you create a table from another tables using CTAS (create table as select * from  <table_name> where 1=3),it will create the table structure as same as source table,but when you create the same  for partition table,it wont create partition table,it will just create only NORMAL table.

sql>CREATE TABLE "user1".table1"

   (    "DAY_ID" NUMBER DEFAULT -1,

        "ZIPMASTER_ID" NUMBER DEFAULT -1,

        "CORP_ID" NUMBER DEFAULT -1,

        "ETHNIC_CODE_ID" NUMBER DEFAULT -1,

        "ECOHORT_CODE_ID" NUMBER DEFAULT -1,

        "FIOS_ID" NUMBER DEFAULT -1,

        "UVERSE_ID" NUMBER DEFAULT -1,

        "CC_INBOUND_CALLS" NUMBER DEFAULT -1,

        "DESTINATION_ID" NUMBER DEFAULT -1,

        "HOUSE_STATUS_CODE_ID" NUMBER DEFAULT -1,

        "LOAD_DATE" DATE DEFAULT sysdate,

        "SOURCE_SYSTEM_ID" NUMBER DEFAULT 1 NOT NULL ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "tablespace_data"

  partition by range (day_id)  (PARTITION "calls201401" VALUES LESS THAN (20140201)

  SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

  COMPRESS FOR OLTP LOGGING

  STORAGE( MAXSIZE UNLIMITED

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "tablespace_data");

table created;

check if this table belongs to partitions:

select table_name  from dba_tab_partitions where table_name='TABLE1';

TABLE1

create table  "user1".table_part" from  "user1".table1 where 1=3;

Table created

check if the newly created table belongs to partition

select table_name  from dba_tab_partitions where table_name='TABLE1';

NO ROWS selectd

select table_name from dba_tables where table_name='TABLE1';

TABLE1

from the above testing ,we can conclude this cloning the structure of the partition table wont  create partition table.

How to find lag in golden gate Process

In golden gate we have usually face the problem for lag  could be generated based  from source side and network side and target side.

1.source side lag it's from extract lag
2.source side lag because of network issue/slow
3.target  side lag or replicat lag

Here is the method to find out the lag in golden gate from replicat side.

GGSCI (server_name) 1> send replicat REP3,getlag

Sending GETLAG request to REPLICAT LHGRP3 ...

At EOF, no more records to process.



GGSCI (server_name) 3> lag REPLICAT RP3

Sending GETLAG request to REPLICAT LHGRP3 ...


At EOF, no more records to process

How to check which Trail file Golden gate reading for replicat and RBA?

we can check the where the GG reads from the trail file using the below method.

step-1

using the info command find the current RBA where replicat its running
GGSCI (server_name) 1> info rep5

REPLICAT   LHGRP5    Last Started 2017-12-18 17:07   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint  File/dat/rt000043
                     2017-12-19 18:56:35.389347  RBA 6796391


GGSCI (server_name) 2> exit
step-2
open the logdump utility to check the latest trail file where GG writting and move the position to end using :POS EOF and not down the RBA,if both RBA matches then we can confirm that GG completed  the reading of trail file and it reached the end of the trail file with NO more records to process.
[ggsmgr@server_name]:/home8>logdump

Oracle GoldenGate Log File Dump Utility
Version 11.1.1.0.11 Build 001

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



Logdump 1 >/dat/rt000043
Current LogTrail is /dat/rt000043
Logdump 2 >pos eof
Reading forward from RBA 6796391

Error when adding the partition on oracle database table ORA-14074:

issue:
when adding the partition got the below error.

SQL> ALTER TABLE user1.test_table  ADD PARTITION "HMS_20180101" VALUES LESS THAN (TIMESTAMP '2018-01-01 00:00:00') TABLESPACE "user1";
ALTER TABLE user1.test_table  ADD PARTITION "HMS_20180101" VALUES LESS THAN (TIMESTAMP '2018-01-01 00:00:00') TABLESPACE "user1"
                                                           *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

Root cause:
when we tried to add partition ,but the partition & partition range already exist on the same table,it will throw the error

Solution:
make sure to check the partition name & partition range and add the next partition appropriately

select partition_name from dba_tab_partitions where table_name='<> and table_owner="USR";

Golden gate error ERROR OGG-01172 Discard file (/app/hp/v11110078/dirout/xmrr.dis) exceeded max bytes (1000000).

we have recently got the golden gate error on 11g environment  and below is the solution.

error:
ERROR   OGG-01172  Discard file (/app/hp/v11110078/dirout/xmrr.dis) exceeded max bytes (1000000) and replicat abended

Solution:
looks like this particular replicat discard file does not have much space and just add the space on the parameter file and start the replicat.

discardfile /app/hp/v11110078/dirout/xmrr.dis, Append,megabytes 10

step by step to stop golden gate process

Here is the step by step process to stop the golden gate process

Source side:
1.stop the application services
2.check if any records to be send
GGSCI>send extract <ext_name>,getlag This should  return "no  rows at EOF"

3.stop all extracts
GGSCI> stop extract <extract_name> or stop extract  * (if you have many extract this command will be helpful to stop extract)
4.stop the mgr process
GGSCI>stop mgr
5.make sure all Process stopped at source server
GGSCI>info all
6.stop the database

Target side
1.check if any replicat writing to database
2.GGSCI>send replicat <replicat_name>,getlag this should return  "At EOF, no more records to process."
3.stop the replicat
GGSCI> stop replicat <replicat_name> or stop replicat  * (if you have many replicat this command will be helpful to stop replicat)

4.stop the manager process
GGSCI>stop mgr
5.make sure all Process stopped at  target  server
GGSCI>info all
6.stop the database

scrip to get grants and privilege of user in netezza database

Netezza provided script to get grants and privilege of the user.

Syntax:
 nz_my_grants <username> > nz_my_grants_<username>.log

 nz_my_grants Bala > nz_my_grants_Bala.log

This will extract all the  grants and privilege about the user and redirect to log

The  out put log would be like below

\echo
\echo ************************************************************
\echo ************************************************************
\echo ************************************************************
\echo
\echo
\echo '*****  Connecting to database:  "NZ_DB1".'
\echo
\echo
\echo ************************************************************
\echo ************************************************************
\echo ************************************************************
\echo
\connect "NZ_DB1"

/* GRANTs associated with the user Bala */
GRANT  delete, insert, list, update  ON NZ_DB1  TO Bala ;
GRANT  create table  TO Bala ;

/* GRANTs associated with GROUPs that the user Bala is a member of */