Search this Blog

Thursday, September 29, 2011

ORA-27123: Unable to attach a shared memory segment

This morning we had another brainteaser starting one of our databases.

The situiation was as following:

We have TEST/DEV server with a single Oracle 9.2.0.8 installation on it, installed as user "oracle"
Next to that there are several application users, called appt01, appt02, appt03 and appt04, all using this same ORACLE_HOME for their database.

All databases were running fine this morning. 
We shutdown the appt01 to install a patch. 

When trying to restart the database, suddenly this error appeared:

SQL> startup 
ORA-27123: Unable to attach a shared memory segment 
IBM AIX RISC System/6000 Error: 13: Permission denied


Using Google and MOS docs (115753.1 &167250.1), all information pointed to the incorrect file permissions of the "oracle" binary where the sticky bit has to be set. However this could be possible, because the patch installation rebuild this binary, it felt as not the correct reason. The file permssions were correct when looking at the file:

oracle@devrsv1% cd$ORACLE_HOME
oracle@devrsv1% ls -l ./bin/oracle
-rwsr-s--x    1 oracle   dba     70518002 Sep 29 12:09 ./bin/oracle

Investigating even more it was soon discovered that all other three database were able to stop and restart as expected. They showed no problems at all, even though they use the same ORACLE_HOME and settings.

After hours of searching and looking at the system, I finally turned to the OS.
Because the error message showed something about shared memory, I started investigating into that direction.
As user root, we executed:

# ipcs -mob

This statement usually shows all active shared memory segments, their sizes and the owners of the segments. In our case, only the output header appeared:


IPC status from /dev/mem as of Thu Sep 29 14:17:01 DFT 2011
T        ID     KEY        MODE       OWNER    GROUP NATTCH     SEGSZ


Now this showed that something was really wrong with the OS shared memory administration.
The other three databases were running, but also from these three no shared memory was shown, not even mentioning the standard OS shared memory pools.

We then decided to restart the complete server. And that solved the problem.
After the restart all databases started again as expected. Also the shared memory segments were correctly shown.
So even the error message displayed, usually points into the direction of the 'sticky bit' of the oracle binary, the problem can also be caused by a total shared memory problem of the system, caused by whatever reason.

Wednesday, September 28, 2011

Database Index administration

I would like to share some PL/SQL code that enables us DBA's to do index maintenance. In our data warehouse there were a couple of procedures that saved very basic index information.
Then the indexes where dropped and after the warehouse load they were recreated.
The pitfall of these simple procedures was that they were not able to cope with partitioned indexes and other complex structures. These more complex indexes were rebuild as normal non-partitioned indexes afterwards.

To be able to also rebuild partitioned and other index structures I rewrote the complete procedure, making effective use of the DBMS_METADATA package available in the SYS account.

This DBMS_METADATA package has a function called GET_DDL that is able to return the DDL of any object in the database, like tablespaces, tables, indexes and others.
If you e.g. would want to know the DDL of the EMP table, simple use the following query:

SQL > select dbms_metadata.get_ddl ('TABLE','EMP') from dual;

Ofcourse the above statement needs to be executed as the owner of the table. If you want to execute it as SYS then you must supply the function with the table owner, like:

SQL> select sys.dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

The result is a CLOB variable containing the DDL to (re)create the EMP table of user SCOTT:


 CREATE TABLE "SCOTT"."EMP" 
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
"ENAME" VARCHAR2(10 CHAR), 
"JOB" VARCHAR2(9 CHAR), 
"MGR" NUMBER(4,0), 
"HIREDATE" DATE, 
"SAL" NUMBER(7,2), 
"COMM" NUMBER(7,2), 
"DEPTNO" NUMBER(2,0), 
CONSTRAINT "EMP_VALID_JOB" CHECK (job in ('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')) ENABLE, 
PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1017
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1017
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"



The procedures I wrote uses a simple table for administration purposes. This table exists of four columns:
INDEX_NAME of type VARCHAR(30)
TABLE_NAME of type VARCHAR(30)
QUERY of type CLOB
STMT_ORDER of type NUMBER(3)

The first three column are quite self-explaining. They contain the table_name, and index_name that is being dropped, and of course the query that is needed to recreate the index afterwards.
The last column STMT_ORDER determines the order of queries. This is important e.g. when creating primary-key indexes with extra constraints. The primary key needs to exist, before a constraint can be added to it.

The DDL structure of this table looks like:

CREATE TABLE WBX_DDL_INDEXES
(
   INDEX_NAME     VARCHAR2 (30 CHAR)
,  TABLE_NAME     VARCHAR2 (30 CHAR)
,  QUERY          CLOB
,  STMT_ORDER     NUMBER (3)
)
LOB (
   QUERY)
   STORE AS
   (
      TABLESPACE USERS
      ENABLE STORAGE IN ROW
      CHUNK 16384
      RETENTION
      NOCACHE LOGGING)
TABLESPACE USERS
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX WBX_DDL_INDEXES_PK
   ON WBX_DDL_INDEXES (INDEX_NAME, TABLE_NAME, STMT_ORDER)
   NOLOGGING
   TABLESPACE TOOLS
   NOPARALLEL;


ALTER TABLE WBX_DDL_INDEXES ADD 
    ( CONSTRAINT WBX_DDL_INDEXES_PK PRIMARY KEY 
      (INDEX_NAME, TABLE_NAME, STMT_ORDER) 
      USING INDEX WBX_DDL_INDEXES_PK ENABLE VALIDATE);

There are two procedures written, the first responsible for dropping a given index, the second for recreating it. Note that the procedure take a table_name as argument, where one would maybe expect a table_name, index_name combination.
However, since we are using this is a large datawarehouse, all indexes of a given table are dropped. We therefore don't need to specify the index_name.
The "DROP_INDEX" procedure looks like:

  procedure drop_index
    (p_table      in     varchar2
    )
   is
    q clob;
    l_count number(3);


    -- Use a cursor with a complex statement to determine all indexes and their types
    cursor c1(p_table varchar2) 
    is
        select ui.index_name, ui.table_name, ui.index_type, ui.uniqueness, uc.constraint_type
        from user_indexes ui
        left outer join user_constraints uc
        on ui.index_name=uc.index_name 
        and ui.table_name=uc.table_name
        where ui.table_name=p_table
        and ui.index_type <> 'LOB'
        order by uc.constraint_type;
begin
    l_count := 1;
    for r1 in c1(p_table) 
    loop
        if r1.constraint_type = 'P'
        then
            q := sys.dbms_metadata.get_ddl('CONSTRAINT', r1.index_name);
            begin
                insert into wbx_ddl_indexes (index_name, table_name, query, stmt_order)
                values ( r1.index_name, p_table, q, l_count);
                exception
                    when dup_val_on_index then
                         null;
            end;
            -- if the index is a primary key, drop it using an "ALTER TABLE.."
            execute immediate 'alter table '||p_table||' drop primary key';   
        else 
            q := sys.dbms_metadata.get_ddl('INDEX', r1.index_name);
            begin
                insert into wbx_ddl_indexes (index_name, table_name, query, stmt_order)
                values ( r1.index_name, p_table, q, l_count);
                exception
                when dup_val_on_index then
                     null;
            end;
            -- if the index is a normal index, use the "DROP INDEX" statement
            execute immediate 'drop index '||r1.index_name;
        end if;


        l_count := l_count+1;        
        commit;               
     end loop;
end; 
     
The "CREATE_INDEX" procedure looks like:

  procedure create_index
    (p_table      in     varchar2
    )
   is
    q clob;
    exec_q varchar2(32767);
    l_index varchar2(30);
    l_length number(10);
    -- Use a cursor to go through all saved index statements
    cursor c1(l_table varchar2) 
    is 
        select index_name, query,stmt_order
        from wbx_ddl_indexes 
        where table_name=l_table
        order by stmt_order asc;
     
begin
    for r1 in c1(p_table) 
    loop
         begin
             select index_name
             into l_index
             from user_indexes where table_name=p_table and index_name=r1.index_name;
         
             exception 
                when no_data_found then
                -- Index does not exist, create it
                select query into exec_q 
                    from wbx_ddl_indexes
                    where index_name=r1.index_name
                    and table_name=p_table
                    and stmt_order=r1.stmt_order; 
                
                execute immediate exec_q;         
         end;
         
         -- As soon as the index is created, update the administration
         delete
           from   wbx_ddl_indexes  
           where  index_name = r1.index_name 
           and table_name=p_table
           and stmt_order=r1.stmt_order;


        commit;
   end loop;
end;


It is too much to exactly explain the procedure here themselves. Anyone who can read PL/SQL code should be able to understand the procedures.

Feel free however to post questions or send me an Email.