Category: ORA- ERRORS

PMON (ospid: 123456): terminating the instance due to error 500

Oracle instance crashes with the following errors, sometimes the instance fails to start up with the same messages:

...
..
.
Errors in file /u02/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_lreg_104781.trc  (incident=287152) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [nscall()+911] [SIGSEGV] [ADDR:0x186] [PC:0x5C6F35F] [Address not mapped to object] []
Incident details in: /u02/app/oracle/diag/rdbms/ractest/RACTEST1/incident/incdir_287152/RACTEST1_lreg_104781_i287152.trc
...
..
.
Tue Aug 24 14:58:01 2021
Instance Critical Process (pid: 29, ospid: 104781, LREG) died unexpectedly
PMON (ospid: 123456): terminating the instance due to error 500
Tue Aug 24 14:58:01 2021
System state dump requested by (instance=1, osid=123456(PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u02/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_diag_104564_20210824145801.trc
Tue Aug 24 14:58:01 2021
Dumping diagnostic data in directory=[cdmp_20210824145801], requested by (instance=1, osid=123456(PMON)), summary=[abnormal instance termination].
Tue Aug 24 14:58:01 2021
Instance terminated by PMON, pid = 123456

The Call Stack Trace in the associated incident trace file ( RACTEST1_lreg_104781_i287152.trc ) shows :

nscall <- nsgrcOpen <- nsgrDo <- nsgrrg_Register <- kmmlrl <- ksucln <- ksbrdp <- opirip
  <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main

nscall()+911         signal   __sighandler()       2500000153 0F2408F7A
                                                   7F2275499F90 0F2408F7A ?
                                                   000000000 ? 000000000 ?
nsgrcOpen()+512      call     nscall()             2500000153 ? 7F2275499E30 ?
                                                   7F2275499F78 7F227549A0B0
                                                   7F227549A0C8 7F2275499F90
nsgrDo()+81          call     nsgrcOpen()          7F22753F2F30 7F2275499D78
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
nsgrrg_Register()+2  call     nsgrDo()             7F2275499D78 7F2275499D78 ?
70                                                 7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
kmmlrl()+12395       call     nsgrrg_Register()    7F22753F2F30 000000008
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
kmlmain()+43         call     kmmlrl()             7F22753F2F30 ? 000000008 ?
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
ksbrdp()+1072        call     kmlmain()            06003EDA0 000000008 ?
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
opirip()+1488        call     ksbrdp()             06003EDA0 ? 000000008 ?
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
opidrv()+616         call     opirip()             000000032 000000004
                                                   7FFDA41BB698 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
sou2o()+145          call     opidrv()             000000032 000000004
                                                   7FFDA41BB698 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
opimai_real()+270    call     sou2o()              7FFDA41BB670 000000032
                                                   000000004 7FFDA41BB698
                                                   7F227549A0C8 ? 7F2275499F90 ?
ssthrdmain()+412     call     opimai_real()        000000000 7FFDA41BB980
                                                   000000004 ? 7FFDA41BB698 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
main()+236           call     ssthrdmain()         000000000 000000003

CAUSE and SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Snapshot Too Old Error

The ORA-1555 or Snapshot Too Old errors are reported when the read consistent images are unavailable in the Undo tablespace. This happens when there is not enough space in the Undo tablespace to retain the undo records for the long running queries.

Snapshot Too Old Error detected: SQL ID 3jgzkm92q02i8, Snapshot SCN 0x074c.3280364e, Recent SCN 0x074c.33f38d0c, Undo Tablespace UNDOTBS1, Current Undo Retention 14542.

INVESTIGATION and SOLUTION

Tune the SQL

Find sql from v$sql or dba_hist_sqltext, and tune it accordingly

SQL> select SQL_TEXT, SQL_FULLTEXT from v$sql where sql_Id='3jgzkm92q02i8';

SQL>  select SQL_TEXT from DBA_HIST_SQLTEXT where sql_Id='3jgzkm92q02i8';

Check and Increase UNDO_RETENTION

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     1000


SQL> select max(maxquerylen) from v$UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
            1961

SQL> select max(maxquerylen) from DBA_HIST_UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
           22330

Set UNDO_RETENTION to the max of the above values, and make sure the UNDO tablespace has big enough space.

SQL> alter system set UNDO_RETENTION=23000;

UNDO Tablespace Size Advisor

To Get The Output using the historical information in memory :

SQL> SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE
-----------------------------------------------------------------------------------------------------------
The Required undo tablespace size using Statistics In Memory is 2048 MB

To Get The Output using Begin/End AWR snapshot id :

SQL> SELECT 'The Required Undo tablespace size During This AWR snaps Range is ' || dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE
------------------------------------------------------------------------------------------------------------
The Required Undo tablespace size During This AWR snaps Range is 2048 MB

Monitor UNDO Tablespace

The V$UNDOSTAT view holds undo statistics for 10-minute intervals, which represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance.

Column nameMeaning
BEGIN_TIMEThe beginning time for this interval check
END_TIMEThe ending time for this interval check
UNDOTSNThe undo tablespace number
UNDOBLKSThe total number undo blocks consumed during the time interval
TXNCOUNTThe total number of transactions during the interval
MAXQUERYLENThe maximum duration of a query within the interval
MAXCONCURRENCYThe highest number of transactions during the interval
UNXPSTEALCNTThe number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNTThe number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNTThe number of unexpired undo blocks reused by transactions
EXPSTEALCNTThe number of attempts when expired extents were stolen from other undo segments to satisfy a space request
EXPBLKRELCNTThe number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNTThe number of expired undo blocks reused within the same undo segments
SSOLDERRCNTThe number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNTThe number of Out-of-Space errors

ORA-01078 When running Dbca Delete Database

When trying to delete a database by running dbca, the following errors appear:

$  dbca  -silent -deleteDatabase -sourceDB TESTDB -sysDBAUserName sys  -sysDBAPassword "sys_password"
ORA-01078: failure in processing system parameters

SOLUTION

  1. Remove the entry in /etc/oratab for this database to be deleted.
  2. Make sure here sourceDB name is actually UNIQUE_DB_NAME, instead of DB_NAME. 
$  dbca  -silent -deleteDatabase -sourceDB TESTDB_UQ sysDBAUserName sys  -sysDBAPassword "sys_password"
Connecting to database
9% complete
14% complete
19% complete
23% complete
28% complete
33% complete
38% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instances and datafiles
66% complete
80% complete
95% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/TESTDB.log" for further details.

ORA-00392 from ALTER DATABASE OPEN RESETLOGS

After restoring the database, the following errors appear when opening resetlogs:

SQL>  alter database open resetlogs;
 alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '+DATAC1/TESTDB/ONLINELOG/redo02.log'

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 YES CLEARING               1682491 26-JUL-21      1682509 26-JUL-21          0
         3          1          0   52428800        512          1 YES CLEARING               1682488 26-JUL-21      1682491 26-JUL-21          0
         2          1          0   52428800        512          1 NO  CLEARING_CURRENT       1682509 26-JUL-21   2.8147E+14                    0

CAUSE

For some reason, “alter database open resetlogs” is abnormally abrupted leaving the redo log status as CLEARING/CLEARING_CURRENT in control file.

SOLUTION

SQL> alter database clear unarchived logfile group 1 ;
Database altered.

SQL>alter database clear unarchived logfile group 2 ;
Database altered.

SQL>alter database clear unarchived logfile group 3 ;
Database altered.

SQL>alter database open resetlogs;
Database altered.

If the above method is not working , then need recreate the control file with RESETLOGS option.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control_file.sql' resetlogs ;

Check the above control_file.sql to make sure all online redo logs directories exist.

SQL> STARTUP FORCE NOMOUNT
SQL> @/tmp/control_file.sql

controlfile created

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

Type <CANCEL> when prompted

Finally open database resetlogs successfully.

SQL> ALTER DATABASE OPEN RESETLOGS ;
Database altered.

ORA-12919: Can not drop the default permanent tablespace

SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace


SQL> alter database default tablespace user1;

Database altered.

SQL>  drop tablespace users including contents and datafiles;

Tablespace dropped.

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM                     USER1
OJVMSYS                        USER1
SYSKM                          USER1
XS$NULL                        USER1
GSMCATUSER                     USER1
MDDATA                         USER1
AUDSYS                         USER1
DIP                            USER1
SYSDG                          USER1
SPATIAL_CSW_ADMIN_USR          USER1
SPATIAL_WFS_ADMIN_USR          USER1
GSMUSER                        USER1
SYSBACKUP                      USER1
SYSTEM                         SYSTEM
SYS                            SYSTEM