ORA-27123: unable to attach to shared memory segment

Hello,

Of late, in one of our test unix (Sun Solaris) boxes, a database user is trying to connect to the database, but getting an error “ORA-27123: unable to attach to shared memory segment” with permission denied issue.

Initially we thought that there might be an issue with SGA memory area or may be an issue with the shared memory segments and semaphores for the instance allocated. But later we found that the permission on ORACLE_HOME directory got changed accidentally with the full permission.

Here are our findings:

$ sqlplus testusr/password@testdb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jan 27 20:53:44 2009

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
SVR4 Error: 13: Permission denied 

As per Oracle Error Messages:

Error: ORA-27123: unable to attach to shared memory segment
Cause: shmat() call failed
Action: check permissions on segment, contact Oracle support

Check the oracle executable file permission.

$ cd $ORACLE_HOME/bin
$ ls -l oracle
-rwxrwxr-x 1 oracle dba 119582976 Feb 3 2008 oracle

Here the oracle file permission has -rwxrwxr-x i.e. 775, but this file must have the permission -rwsr-s- -x i.e. 6751

Change the permissions for oracle file.

$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle
-rwsr-s--x 1 oracle dba 119582976 Feb 3 2008 oracle

After changing the permissions on oracle executable file, all the users are now able to connect to the database without any errors.

Note: For further information refer the Oracle Metalink Note ID: 1011995.6 Subject: COMMON ORACLE PERMISSION PROBLEMS ON UNIX.

Regards,
Sabdar Syed

How We Resolved the Account Locked (Timed) issue?

An application user account, in one of our Oracle 10g Databases, is being locked every time. Below are our findings and solution to resolve the issue.

Details:
Oracle Database Version: 10g R2 (10.2.0.1)
Application User: APPUSR
Error: ORA-28000: the account is locked

Login as SYSDBA

SQL> conn /as sysdba

Check the APPSUSR account status.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR LOCKED(TIMED) DEFAULT

Here we can see the account status is LOCKED (TIMED) and the default user’s profile is DEFAULT.

Check the resource limits of DEFAULT profile.

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT
2 COMPOSITE_LIMIT UNLIMITED
3 SESSIONS_PER_USER UNLIMITED
4 CPU_PER_SESSION UNLIMITED
5 CPU_PER_CALL UNLIMITED
6 LOGICAL_READS_PER_SESSION UNLIMITED
7 LOGICAL_READS_PER_CALL UNLIMITED
8 IDLE_TIME UNLIMITED
9 CONNECT_TIME UNLIMITED
10 PRIVATE_SGA UNLIMITED
11 FAILED_LOGIN_ATTEMPTS UNLIMITED
12 PASSWORD_LIFE_TIME UNLIMITED
13 PASSWORD_REUSE_TIME UNLIMITED
14 PASSWORD_REUSE_MAX UNLIMITED
15 PASSWORD_VERIFY_FUNCTION NULL
16 PASSWORD_LOCK_TIME UNLIMITED
17 PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.

Unlock the user account:

SQL> ALTER USER appusr ACCOUNT UNLOCK;

User altered.

Now check again the status of APPUSR user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR OPEN APPUSR_DEFAULT

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

ORA-12170: TNS: Connect timeout occurred !!

We have installed Oracle 10g on Windows-XP machine. But when a client (another pc – windows 2003) is trying to connect to the database, then the following error is being generated “ORA-12170: TNS: Connect timeout occurred”

We ensure that the tnsnames.ora file has the correct information i.e. hostname, port, and sid. When we tnsping the sid, it gives “TNS-12535: TNS: operation timed out” error.

So, we had to resolve this problem by turning OFF the Firewall on Server Side (Windows –XP). After that we could successfully connect to the database, and able to tnsping sid as well.

Conclusion: If you are facing these problems due to other reasons, I would request you to take a look at oracle metalink notes for the same.

Best Regards,
Sabdar Syed.

ORA-16005: database requires recovery

Due to some reason, I had to bring one of our test databases in read only mode, but the follwoing error generated while doing the same. "ORA-16005: database requires recovery".

I'm sure that we have't done any changes with the database recently. Well, the problem was resolved by shutting down the database in normal mode. Please take a look at the below steps for the same.

SQL> startup force open read only;

ORA-16005: database requires recovery

SQL> shutdown immediate;

SQL> startup restrict;

SQL> shutdown;

SQL> startup mount;

SQL> alter database open read only;

SQL> select open_mode from v$database;
OPEN_MODE

----------

READ ONLY

Conclusion: If you have got the error "ORA-16005: database requires recovery" due to other reasons, I request you to take a look at the metalink note ID: 316154.1

Regards,

Sabdar Syed.

WARNING: "Memory Notification: Library Cache Object Loaded Into SGA"

Dear Friends,

In one of our Databases, we have found that the WARNING message "Memory Notification: Library Cache Object Loaded Into SGA" is being written continuously in alert log file. As per the Oracle Support Documentation, These are warning messages generated in Oracle 10g (Release 2).

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter "_kgl_large_heap_warning_threshold" to a high value or zero. Note: In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.

To find out a hidden parameter value in the database.

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
------------------------------------ -------------------- --------------------_kgl_large_heap_warning_threshold 2097152 2097152

To change the hidden parameter value.

If you are using spfile, login as sysdba

$sqlplus /nolog
SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Or if you are using "old-style" init prameter file

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------_kgl_large_heap_warning_threshold integer 8388608

Regards,
Sabdar Syed.

Database Cloning Mistake !!!!!

Dear Friends,

The following errors were generated continuously in the test database alert log file.

1). ORA-25153: Temporary Tablespace is Empty

2). GATHER_STATS_JOB encountered errors. Check the trace file.

3). ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist.

But when we query to see the information about temporary tablespace and tempfiles in the database, the following findings were observed.

## To check TEMP tablespace and tempfile details.

SQL> SELECT file_name, bytes FROM dba_temp_files;

no rows selected

SQL> SELECT * FROM v$tempfile;

no rows selected

SQL> SELECT * FROM v$tablespace WHERE name='TEMP';

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
3 TEMP NO NO YES

SQL> SELECT tablespace_name, contents FROM dba_tablespaces WHERE tablespace_name='TEMP';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY

SQL> SELECT DISTINCT temporary_tablespace FROM dba_users;

TEMPORARY_TABLESPACE
------------------------------
TEMP

## Gather Schema Statistics.

In Oralce 10g, when the data of a table is grown to certain threshold value i.e. 10% of data, Automatically Gather Schema Statistics is performed on those tables.And also it needs enough free space in the temporary tablespace for sorting. Due to this reason above messages generated in the alert log file.

Cause: The TEST Database has recently been cloned from PRODuction Database. So, we realized that the step, adding a tempfile to the temp tablespace, was missed mistakenly. In fact the tempfile was copied and it is physically existing in the server, but not added to the TEMP tablespace as part of the cloning.

Solution:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/oradata/test/temp01.dbf' REUSE;

After adding a tempfile to the TEMP tablespace, no errors generated regarding Gather Schema Statistics and temp tablespace afterward !!!.

Note: if you are receiving an error "ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist." due to other reasons. Please take a look at the Oracle Metalink Note ID: 333966.1

This article, written by me, is also available in Oracle Metalink. Note ID: 427974.1

Best Regards,
Sabdar Syed.