oracle database memory upgrade error

when I tried to increase the sga_max_size and sga_target and got  the below error.


ISSUE:

alter system set SGA_MAX_SIZE=12288M scope=spfile;

 alter system set SGA_TARGET=12G scope=spfile;


SQL> startup pfile='initOra1.ora' mount;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 12884901888 cannot be set to more than MEMORY_TARGET 9663676416.
ORA-01078: failure in processing system parameters

Cause:
when I checked the parameter file and it contains AMM parameter enabled.

Solution:
remove  or comment the memory_target from the parameter file and startup with pfile  with sga_max_size & sga_target.

How to find locks on Netezza database?

Here is the step by step to find  the blocking or locking session in Netezza database
Please get the SQL statement  that's running on the server and get the table that you have issue and check locking session with the  DB  name and table name

nz_show_locks  NZ_DB1 table_name1



 Database: NZ_DB1

   Object: table_name1

 ObjectId: 13231729

Timestamp: 2018-03-01 17:12:49



=======================================================================================================



The following session(s) are HOLD'ing a lock on the object



 Requested | Granted @ | Wait Time | SESSIONID | PROCESSID | USERNAME  |    LOCKMODE     |           Current SQL Command

-----------+-----------+-----------+-----------+-----------+-----------+-----------------+------------------------------------------

 15:21:05  | 15:21:05  | 00:00:00  |    448002 |     14511 | user1 | AccessShareLock | SELECT count(a11.CHC_ID),a11.day_id, DEC

 16:01:12  | 16:01:12  | 00:00:00  |    448260 |      3201 | user1 | AccessShareLock | SELECT count(a11.CHC_ID),a11.day_id, DEC

 16:11:40  | 16:11:40  | 00:00:00  |    448329 |      1450 | user1 | AccessShareLock | SELECT count(a11.CHC_ID),a11.day_id, DEC

 16:51:37  | 16:51:37  | 00:00:00  |    448632 |     20697 | USR2  | AccessShareLock | SELECT MAX (a13.MONTH_DESC) MONTH_AT_DIS

(4 rows)



=======================================================================================================



The following sessions are WAIT'ing to access the object



 Requested | Granted @ | Wait Time | SESSIONID | PROCESSID |  USERNAME  |      LOCKMODE       |           Current SQL Command

-----------+-----------+-----------+-----------+-----------+------------+---------------------+------------------------------------------

 16:54:35  |           | 00:18:15  |    448653 |     30742 | USER4     | AccessExclusiveLock | ALTER TABLE NZ_DB1.NZUSER.TABLE_NAME

 16:55:27  |           | 00:17:23  |    448679 |      2638 |   USR5    | AccessShareLock     | SELECT To_Date (a.WFINDATE_DAY_ID, 'yyyy

 16:56:55  |           | 00:15:55  |    448680 |      4580 | USER3     | AccessShareLock     | select a13.MONTH_ID MONTH_ID1, max(a13.m

 16:57:48  |           | 00:15:02  |    448623 |     18626 | USER3     | AccessShareLock     | CREATE TEMP TABLE T6GXA4WPZMD003 as sele

 16:58:53  |           | 00:13:57  |    448624 |     18625 | USER3     | AccessShareLock     | CREATE TEMP TABLE TJCDUKW1BMD004 as sele

(5 rows)



=======================================================================================================

How to take backup of users  only in Netezza database?

Please use the below command to take user only backup.it will create a folder .
/SYSTEM/20180312164028/1/USERS/md


/nz/kit/bin/nzbackup -users -dir /export/home/nz/ > user_backup.log


Backup of global objects completed successfully.

How to check the user last login detail on the netezza database?

Here is the script to check the user last login detail.

connect to history DB and execute this query.

select username, max(time) from "$v_hist_log_events" where op_type ='session create' group by username order by 1;  

oracle database manual upgrade failed with below error

Issue:
oracle database manual upgrade failed with below error 

when I tried to upgrade oracle database to 11.2.0.4  had issues during the upgrade.

catupgrade ran almost  70% and come out from the SQL prompt and trowed the below error.



catupgrad failed with below error.


RROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_STATS_INTERNAL"
ORA-06512: at "SYS.DBMS_STATS", line 385
ORA-06512: at line 2



Solution:

when I check with oracle and they suggested us to validate these packages and re-run the catupgrade again.



drop table sys.WRI$_OPTSTAT_SYNOPSIS$;

create table wri$_optstat_synopsis$
( bo# number not null,
group# number not null,
intcol# number not null,
hashvalue number not null
) partition by range(bo#)
subpartition by hash(group#)
subpartitions 32
(
partition p0 values less than (0)
)
tablespace sysaux
pctfree 1
enable row movement;
descr sys.WRI$_OPTSTAT_SYNOPSIS$

--- check if the desc command shows:
Name Null? Type
----------------------------------------- -------- ----------------------------
BO# NOT NULL NUMBER
GROUP# NOT NULL NUMBER
INTCOL# NOT NULL NUMBER
HASHVALUE NOT NULL NUMBER

catalog
catproc
@?/rdbms/admin/prvtstas.plb
@?/rdbms/admin/prvtstat.plb

Can we add space to table space when we the oracle DB in upgrade mode?

Can we add space to table space when we the oracle DB in upgrade mode?

I came to the situation where I need to add some space to during the oracle database upgrade .
checked with oracle and we can add the space during the DB upgrade with just altering the data-file size.But oracle recommended to resize the data-file instead of adding new data file.

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.