My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • February 2020
    M T W T F S S
    « Jun    
     12
    3456789
    10111213141516
    17181920212223
    242526272829  
  • Blogs I Follow

Archive for the ‘Uncategorized’ Category

Apache – No space left on device / Failed to create proxy Mutex

Posted by Sriram Sanka on August 10, 2018


A Big Thanks to “https://help.directadmin.com/item.php?id=110

Restarting  Apache in one of the Servers failed with “No Space left on the Device ” Error and we have enough space in all available partitions.

This is something to deal with Server Kernel semaphores.

“On Linux, A semaphore is a System V IPC object that is used to control utilization of a particular process. Refer https://www.tldp.org/LDP/tlk/ipc/ipc.html

Semaphores are a shareable resource that take on a non-negative integer value. They are manipulated by the P (wait) and V (signal) functions, which decrement and increment the semaphore, respectively. When a process needs a resource, a “wait” is issued and the semaphore is decremented. When the semaphore contains a value of zero, the resources are not available and the calling process spins or blocks (as appropriate) until resources are available. When a process releases a resource controlled by a semaphore, it increments the semaphore and the waiting processes are notified.”

The system does n`t have actual resource available to serve the request, so either we need to configure the Kernel Semaphores or clear the Old entries to get it back.

kernel.sem = SEMMSL SEMMNS SEMOPM SEMMNI

SEMMSL      maximum number of semaphores per array
SEMMNS     maximum semaphores system-wide
SEMOPM     maximum operations per semop call
SEMMNI      maximum arrays

[root@r12 ~]# cat /etc/sysctl.conf |grep kernel.sem
kernel.sem = 256 32000 100 142
[root@r12 ~]#

[root@r12 ~]# cat /proc/sys/kernel/sem
256 32000 100 142
[root@r12 ~]#

[root@r12 ~]# ipcs -l | awk ‘FNR>=7 && FNR<=15’

—— Semaphore Limits ——–
max number of arrays = 142
max semaphores per array = 256
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

—— Messages: Limits ——–
[root@r12 ~]#

[root@r12 ~]# ipcs -ls

—— Semaphore Limits ——–
max number of arrays = 142
max semaphores per array = 256
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

To Clear ,  we can execute the following

ipcs | grep apache | awk ‘{print $2}’ > sem.txt
for i in `cat sem.txt`; do { ipcrm -s $i; }; done;

For Automating the removal of apache semaphores with ipcs/ipcrm, Please refer https://help.directadmin.com/item.php?id=572

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Applying WLS patch using BSU -java.lang.OutOfMemoryError: GC overhead limit exceeded

Posted by Sriram Sanka on July 8, 2018


If you are trying to Apply any Big Patch, while Checking for conflicts…., “BSU”  might end-up with Java Heap  Errors.

 

 

To Avoid,  Try to Increase the size in $FMW_Home/utils/bsu . Edit bsu.sh using any editor as below. 

Change the Value MEM_ARGS=”-Xms256m -Xmx512m” to MEM_ARGS=”-Xms1024m -Xmx1024m” or even bigger  and save. 

This will resolve the above issue and re-try applying the patch .

 

For more Details Ref Oracle MOS Doc:  Weblogic 10.3.6 Patching Error Using BSU – “java.lang.OutOfMemoryError: GC overhead limit exceeded” (Doc ID 2007492.1) and 

Enhancement for WLS BSU (Smart Update): Resolves Very Long Time to Apply Patches – Especially When Checking for Patch Conflicts (Doc ID 2271366.1)

 

 

 

Posted in Web Logic Server | Leave a Comment »

Oracle Internals – Memory structures (arrays) – Part 1

Posted by Sriram Sanka on January 19, 2018


 

Dynamic Performance Views

Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.

Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.

Screen Shot 2018-01-19 at 10.50.09 AM

https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1001.htm#i1398692

There are two types of views,  

  • V$ Views
  • GV$ Views

Almost For Every V$ View there is a GV$View which will hold the instance specific Information in a Multi Instance Environment like RAC.

Screen Shot 2018-01-19 at 10.33.49 AM

These Dynamic Views are owned by Sys User and These views are constructed based on the Oracle Internal Memory Structures (X$ Tables/Views). We cannot alter or Update on these views. These X$ structure Get Created when the Instance started and Will get destroyed when you shutdown the DB.  

V$FIXED_VIEW

V$FIXED_TABLE displays all dynamic performance tables, views, and derived tables in the database.

V$FIXED_VIEW_DEFINITION

This  View contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice.

As described in the Above Example, Dynamic Performance views are based on Internal Memory Structures X$.

The X$ tables/views  are platform-specific & the No of tables are keep on increasing By Oracle Version.The Oracle kernel consists of layers. The X$ table names contain an abbreviation for the particular kernel layer.

The No#  Of Views are dependent on Oracle version and the OS Specific.

Screen Shot 2018-01-19 at 11.18.14 AM

Screen Shot 2018-01-19 at 10.24.17 AM

These Views Get populated only when the Instance Started. These Views Contains information about all the parameters,performance etc info. As We all know Oracle has documented some of Dynamic Views (V$_ objects) which are in handy to get the info from your instance these Dynamic Views are Based on GV$_ VIEWS which includes the data for Multiple Instances.  GV$ are Designed from these Fixed Views. View Columns might be similar from version to version where as Underlying Fixed X$ objects might different.

Screen Shot 2018-01-19 at 10.56.14 AM

Why do we call them “Fixed”?

As mentioned, All these Objects are owned by SYS and DDL or DML Operations are not permitted/supported on these. Even if we try to perform, Operation will end up with error ORA-02030: can only select from fixed tables/views.

Screen Shot 2018-01-19 at 10.50.09 AM

These objects contain information about the current configuration of the instance, information about the sessions connected to the instance, and a goldmine of performance information.  

Using Views like (g) v$fixed_view_definition  one can get the Actual Creation script of these Fixed views which are harmful in case of replacing the Existing or Create New views, which will end up with Errors or incorrect data as These Views are for Oracle not for Others. DBA/Developers should not create Objects using actual “V$_ “, they need to use v$ Objects instead of v$_ or X$, as The Definition of the base view may change in OS/DB software versions.   

The X$ views are completely undocumented, and Oracle does not provide any assistance for deriving the meaning of the contents of the X$ structures. since V$ views are dynamic and consistency is not guaranteed, Oracle does not support joins or sorts of dynamic views.  

The X$ views are an Oracle internal interface not exposed for customer use; customers should not be using it unless directed to do so by the support.Customer use without support guidance is unsupported.

Posted in Uncategorized | Leave a Comment »

oracle-e-business-suite-1227-now-available

Posted by Sriram Sanka on September 12, 2017


https://blogs.oracle.com/stevenchan/oracle-e-business-suite-1227-now-available

Posted in Uncategorized | Leave a Comment »

Error While invoking Discoverer Plus or Viewer 11g in a Single-node EBS Server

Posted by Sriram Sanka on August 10, 2017


Screen Shot 2017-08-10 at 6.15.32 PM
When you have Discoverer and EBS running on the same machine, this kind of conflicts may happen.

To resolve,
Start another session, clear All Environment Variables, restart the Web-logic Server Again. 

This will resolve the issue. For More Info, Refer
Unable to Access Discoverer Plus/Viewer 11g Installed On Server Having EBS 11i / R12 (Doc ID 1338244.1)

				

Posted in Uncategorized | Leave a Comment »

Error Opening 12.2.6 EBS URL

Posted by Sriram Sanka on May 22, 2017


 

There was an error while Re-Starting Our Newly Configured Oracle R12.2.6 Application Instance after a Hard reboot. I was unable to start some of the Server Components like oacore, oafm and forms.   When I tried to start them Manually, There was No error and everything seems to be started Successfully, But I was unable to access the URL with Below Error.

 

 

” Error 404–Not Found From RFC 2068 Hypertext Transfer Protocol — HTTP/1.1: 10.4.5 404 Not Found The server has not found anything matching the Request-URI. No indication is given of whether the condition is temporary or permanent. If the server does not wish to make this information available to the client, the status code 403 (Forbidden) can be used instead. The 410 (Gone) status code SHOULD be used if the server knows, through some internally configurable mechanism, that an old resource is permanently unavailable and has no forwarding address. ”

This  is due to the failure of  oafm_server,Forms_server and oacore_server using EBS Startup scripts .Starting Using Web Logic Server Deployments as  below endup with Java Null Pointer Issues with state as”ADMIN”

“<Server ‘forms_server1’ in cluster ‘forms_cluster1′ is being brought up in administration state due to failed deployments.>

“(self-tuning)’> <<WLS Kernel>> <> <> <1417622780708> <BEA-149205> <Failed to initialize the application ‘oafm [Version=1.0.0]’ due to error java.lang.NullPointerException.
java.lang.NullPointerException
at java.io.File.<init>(File.java:251)
at oracle.apps.ad.util.AppStartupPropertyListener.preStart(AppStartupPropertyListener.java:89)
at weblogic.application.internal.flow.BaseLifecycleFlow$PreStartAction.run(BaseLifecycleFlow.java:290) ”

 

 

 

 

This is Due to the recent Change in Hosts file. My Database Lister Started with an Alias Name defined in the Host file and these Server Components were unable to start/connect to the Actual Host Defined.   Changing the Host Entry and restart of these Components  resolved the issue.

For More info Refer Below Oracle Metalink Document

After Cloning Unable To Start The Managed Servers In 12.2.4 (Doc ID 1952853.1)

Posted in Uncategorized | 3 Comments »

ORA-600:[kpdbModAdminPasswdInRoot: Not CDB] Oracle v 12.1.0.1

Posted by Sriram Sanka on April 22, 2016


While Attempting to use the SQLPLUS PASSWORD command to change password of an Oracle-supplied user in a non-CDB can result ORA-600 as below.

ora-600

There is a patch available for Linux Platform. Oracle Meta link Doc ID 1583225.1 has more info on the Patch.

 

Posted in Uncategorized | Tagged: | 2 Comments »

Mega Oracle Walk-In Drive on 17th May

Posted by Sriram Sanka on May 9, 2014


http://www.careers.tcs.com/CareersDesign/Jsps/OracleDrive/AboutTCS.html

 

OracleDrive17May2014-WalkinMicrositeBanner

Posted in Uncategorized | Leave a Comment »

Oracle Database 12c Interactive Quick Reference Guide

Posted by Sriram Sanka on August 8, 2013


Downloadable Interactive Quick Reference for Oracle Database 12c available @

http://www.oracle.com/go/?&Src=7875565&Act=4&pcode=WWOU13045613MPP009

 

12c__arch_diag

Posted in Uncategorized | 5 Comments »

ORA-07445: exception encountered: core dump [kttuser0()+272] [SIGFPE] [Integer divide by zero] [0x8949718] [] []

Posted by Sriram Sanka on February 18, 2013


Today, we were encountered with this internal error on One of our test environment.

On Our Database ,we have 2 temporary table-spaces with a Temporary table space group.
While we are performing Data pump Import (IMPDP) into another machine , The job was failed multiple times with following error on screen ….
Screen Error :

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW01” prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.

When I verified for the cause, on alert log,  found an Internal error only after Temporary tablespace creation ..

Alert log Content :

CREATE TEMPORARY TABLESPACE “TEMP_TWO” TEMPFILE ‘/u01/app/oracle/oracle/product/10.2.0/oradata/*****/temp02.dbf’SIZE 844103680 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M TABLESPACE GROUP TBS_GROUP EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

ORA-1119 signalled during CREATE TEMPORARY TABLESPACE “TEMP_TWO” TEMPFILE ‘/u01/app/oracle/oracle/product/10.2.0/oradata/********/temp02.dbf’ SIZE 844103680 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M TABLESPACE GROUP TBS_GROUP EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 …

Sun Feb 17 13:18:26 PST 2013

Thread 1 advanced to log sequence 47 (LGWR switch)

Current log# 2 seq# 47 mem# 0: /u01/app/oracle/*****/*****/redo02.log

Sun Feb 17 13:18:32 PST 2013
Errors in file /u01/app/oracle/******/admin/******/bdump/*****_dw01_24538.trc:

ORA-07445: exception encountered: core dump [kttuser0()+272] [SIGFPE] [Integer divide by zero] [0x8949718] [] []

Creating the temp tablespaces manually before IMPDP process will avoid such errors.

For More info please read metalink document ID 1259393.1

Posted in Uncategorized | 2 Comments »

Article From Infoworld about Oracle Code flaw

Posted by Sriram Sanka on January 19, 2012


Yesterday, there was an interesting article was posted/published On InfoWorld  By .

http://www.infoworld.com/d/security/fundamental-oracle-flaw-revealed-184163
http://www.infoworld.com/t/data-management/calling-all-oracle-customers-184103?source=fssr

Oracle has published this On Its MOS Bug 12371955  Backup task can cause increased SCN growth rate leading to ORA-600 [2252] errors with Document ID [ID 12371955.8] and Old existing Document 253977.1 will also give some information on it.

for this to be fixed , One should apply the latest CPU patch released on 18-Jan-2012 by Oracle.

Oracle has published an article on MOS yesterday: note 1376995.1 with an associated script “scnhealthcheck.sql” you can find and download in note 1393363.1.which are very helpful.

Posted in Uncategorized | Tagged: , , | Leave a Comment »

New My Oracle Support interface….Soon

Posted by Sriram Sanka on January 19, 2012


On 17-Jan-2012,Oracle Announced,That its going to upgrade  (New)  My Oracle Support User interface, which Oracle ADF.

For More details Please refer The New My Oracle Support User Interface [ID 1385682.1]

http://www.dba-village.com/village/dvp_news.NewsDetails?NewsIdA=3874

Posted in Uncategorized | Leave a Comment »

“racgmain” Processes

Posted by Sriram Sanka on October 3, 2011


Some good Experience for Me today.Early in the Morning ……

One of Our RAC node  filled up with 100% CPU usage.As part of Investigation We found that this was because of  racgmain  Process.

It was filled-up with more than 250 racgmain process which are consuming  CPU a lot.Due this Our System got Very slow.

To avoid this, we should apply latest patch which is > 10.2.0.5 and CRS patch too.There was a temporary workaround for this ..you can see that in Metalink Doc ID 732086.1 and ID 6196746.8

 

Posted in Oracle Server Administration, Uncategorized | Leave a Comment »

Protected:

Posted by Sriram Sanka on July 29, 2011


This content is password protected. To view it please enter your password below:

Posted in Uncategorized | Enter your password to view comments.

How not to Play With Oracle Dual-Magical Dual

Posted by Sriram Sanka on July 18, 2011


Here is an Interesting topic about FAST DUAL and Full Table Scan on Dual table with some example about how not to play with Oracle Data Dictionary Objects.

http://www.orafaq.com/forum/mv/msg/173068/515910/136607/#msg_515910

Happy reading….

Sriram 🙂

Posted in Uncategorized | 2 Comments »

Read the file

Posted by Sriram Sanka on December 10, 2010


A Master piece from http://laurentschneider.com

An excellent piece of code handy for DBA`s


SQL> SELECT s.sid,
2 s.serial#,
3 pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
4 '_ora_' || p.spid || '.trc' AS trace_file
5 FROM v$session s,
6 v$process p,
7 v$parameter pa
8 WHERE pa.name = 'user_dump_dest'
9 AND s.paddr = p.addr
10 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
11 /

SID SERIAL#
---------- ----------
TRACE_FILE
--------------------------------------------------------------------------------
147 2101
D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP\orcl_ora_1000.trc

SQL>
SQL> sho parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCLE\UDUMP
SQL> CREATE DIRECTORY udump_dest AS
2 'D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP';

Directory created.

SQL> CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 dest_loc CLOB;
5 src_loc BFILE;
6 ret VARCHAR2 (4000);
7 BEGIN
8 src_loc := BFILENAME ('UDUMP_DEST', file_name);
9 DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
10 DBMS_LOB.createtemporary (dest_loc, TRUE);
11 DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
12 ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
13 DBMS_LOB.CLOSE (src_loc);
14 RETURN ret;
15 END;
16 /

Function created.

 

SQL>

SQL> SELECT get_tracefile (‘orcl_ora_1000.trc’) FROM DUAL;
GET_TRACEFILE(‘ORCL_ORA_1000.TRC’)——————————————————————————–Dump file d:\oracle\product\10.2.0\admin\orcle\udump\orcl_ora_1000.trcFri Dec 10 22:54:36 2010ORACLE V10.2.0.1.0 – Production vsnsta=0vsnsql=14 vsnxtr=3Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProductionWith the Partitioning, OLAP and Data Mining optionsWindows XP Version V5.1 Service Pack 3CPU                 : 1 – type 586, 1 Physical CoresProcess Affinity    : 0x00000000Memory (Avail/Total): Ph:269M/959M, Ph+PgF:1265M/2314M, VA:1606M/2047MInstance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 1000, image: ORACLE.EXE (SHAD)………………….

…………………………………..ETC

Posted in Oracle Server Administration, Uncategorized | Leave a Comment »

Not null constraint :–ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Posted by Sriram Sanka on December 9, 2009


SQL> sho user
USER is "SCOTT"
SQL> select constraint_name from user_constraints
2 where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL> create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2
2 authid current_user
3 is
4 l_search_condition user_constraints.search_condition%type;
5 begin
6 select search_condition into l_search_condition
7 from user_constraints
8 where constraint_name = p_cons_name;
9
10 return l_search_condition;
11 end;
12 /

Function created.

SQL> select constraint_name
2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%';

CONSTRAINT_NAME
------------------------------
SYS_C006082
SYS_C006081
SYS_C006080
SYS_C005558
SYS_C005696
SYS_C005695

6 rows selected.

SQL>

Source:- Asktom`s get_search_condition function.

Posted in Uncategorized | 1 Comment »

 
Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBA's become bulletproof!

%d bloggers like this: