ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device

When starting up an Oracle database, the below errors occur:

ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 3773
Additional information: 3221225472

Subscribe to get access

Read more of this content when you subscribe today.

ORA-16857: standby disconnected from redo source for longer than specified threshold

One single 11.2.0.4 instance Oracle database alert log shows below information;

 RFS[1]: No standby redo logfiles available for thread 1

Data Guard shows ORA-16857 error:

DGMGRL> show database "TESTSTY";

Database - TESTSTY

Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 10 minutes 32 seconds (computed 48 seconds ago)
 Apply Lag: 10 minutes 32 seconds (computed 48 seconds ago)
 Apply Rate: 39.97 MByte/s
 Real Time Query: OFF
 Instance(s):
 TESTSTY

Database Warning(s):
 ORA-16857: standby disconnected from redo source for longer than 
specified threshold

Database Status:
WARNING

Checked both primary and standby database, the  standby online redo logs have been created. But the size of the standby online redo logs are different from the database redo logs for both primary and secondary database.

— on standby :

SQL> select GROUP#,THREAD# ,BYTES/1024/1024 from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024
---------- ---------- ---------------
 4         1           50
 5         1           50
 6         1           50
 7         1           50

SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$log;

GROUP#     THREAD#    BYTES/1024/1024
---------- ---------- ---------------
 1         1          100
 3         1          100
 2         1          100

— On Primary

SQL> select GROUP#,THREAD# ,BYTES/1024/1024 from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024
---------- ---------- ---------------
 4         1          50
 5         1          50
 6         1          50
 7         1          50

SQL> select GROUP#,THREAD#,BYTES/1024/1024 from v$log;

GROUP#     THREAD#    BYTES/1024/1024
---------- ---------- ---------------
 1         1          100
 2         1          100
 3         1          100

Drop all standby online redo logs on both primary and standby databases, and recreate them again with same size as redo logfiles.

-- for standby db which is under recovery, recovery needs to be stopped first
SQL>alter database recover managed standby database cancel;

SQL>alter database add standby logfile thread 1 group 4 size 100m;

It will create standby online redo logs for one under +FRA, and another one under from “db_create_file_dest” parameter, if “db_create_online_log_dest_x” are not defined.

Finally restart the recovery process, then everything is fine.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Database altered.

alert.log:

RFS[1]: Selected log 4 for thread 1 sequence 41436 dbid 1345227970 branch 816878594
Mon Feb 19 14:38:25 2018
..
.
Recovery of Online Redo Log: Thread 1 Group 4 Seq 41436 Reading mem 0
..
.

DGMGRL of DataGuard:

DGMGRL> show database 'TESTSTY';

Database - TESTSTY

Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Apply Rate: 208.00 KByte/s
 Real Time Query: OFF
 Instance(s):
 TESTSTY

Database Status:
SUCCESS

ORA-600 [kwqmnuji: bad option]

SYMPTOMS

There are a lot of ORA-600 [kwqmnuji: bad option] errors in alert.log of 11.2.0.4 RAC database.

ORA-00600: internal error code, arguments: [kwqmnuji: bad option], [2], [], [], [], [], [], [], [], [], [], []
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x119190D, kwqmncif()+31] [flags: 0x0, count: 1]
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x119190D, kwqmncif()+31] [flags: 0x0, count: 1]

CAUSES

This is “Bug 14746914 – ORA-600 [kwqmnuji: bad option] using AQ in RAC (Doc ID 14746914.8)”.

The fix for 14746914 is first included in

WORKAROUND

We used below workaround to make ORA-600 [kwqmnuji: bad option] error disappeared from alert.log.

1) Bounce the instance, and still see cored umps:

Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_ora_2954.trc:
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_ora_2954.trc:
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_ora_2954.trc:

2) Check OS process 2954 and its related session. then kill this process.

$kill -9 2954

3) Check alert.log, and find there is no more ORA-00600 errors and core dumps.

Drop Tablespace With Datafile Missing

  1. shutdown database.
  2. startup database in mount status.
  3. alter database datafile ‘PATH/filename’ offline drop.
  4. open database.
  5. drop tablespace tablespace_name including contents.

Note, the following errors could be seen in alert.log:

ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: ''

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: ''
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-02065: illegal option for ALTER SYSTEM

For 10.2.0.5, set up parameter value by :
alter system set sga_target= scope= sid=;

In 10g database, when trying to change SGA_TARGET parameter in spfile, “ORA-02065: illegal option for ALTER SYSTEM” occurred.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jun 13 15:56:37 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter sga_target

NAME_COL_PLUS_SHOW_PARAM TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------ ----------- --------------------------
sga_target               big integer 4G

SQL> alter system set sga_target=2g sid='RACTEST1' scope=spfile;
 alter system set sga_target=2g sid='RACTEST1' scope=spfile
 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

By swapping the order of “sid” and scope, it is working fine:

SQL> alter system set sga_target=2g scope=spfile sid='RACTEST1';

System altered.

SQL>