Month: October 2017

Query Alert Log Contents From Database

Get alert log contents directly from database by using SQL query

We can get alert log contents directly from database by using SQL query, though the x$ view name is hard to remember.

X$DBGALERTEXT

The view x$dbgalertext is the view to query to get alert log contents for current instance:

SQL> DESC X$DBGALERTEXT
 Name                        Null?    Type
 --------------------------- -------- ----------------------------
 ADDR                                 RAW(8)
 INDX                                 NUMBER
 INST_ID                              NUMBER
 CON_ID                               NUMBER
 ORIGINATING_TIMESTAMP                TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                 TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                      VARCHAR2(64)
 COMPONENT_ID                         VARCHAR2(64)
 HOST_ID                              VARCHAR2(64)
 HOST_ADDRESS                         VARCHAR2(46)
 MESSAGE_TYPE                         NUMBER
 MESSAGE_LEVEL                        NUMBER
 MESSAGE_ID                           VARCHAR2(64)
 MESSAGE_GROUP                        VARCHAR2(64)
 CLIENT_ID                            VARCHAR2(64)
 MODULE_ID                            VARCHAR2(64)
 PROCESS_ID                           VARCHAR2(32)
 THREAD_ID                            VARCHAR2(64)
 USER_ID                              VARCHAR2(64)
 INSTANCE_ID                          VARCHAR2(64)
 DETAILED_LOCATION                    VARCHAR2(160)
 PROBLEM_KEY                          VARCHAR2(550)
 UPSTREAM_COMP_ID                     VARCHAR2(100)
 DOWNSTREAM_COMP_ID                   VARCHAR2(100)
 EXECUTION_CONTEXT_ID                 VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE           NUMBER
 ERROR_INSTANCE_ID                    NUMBER
 ERROR_INSTANCE_SEQUENCE              NUMBER
 VERSION                              NUMBER
 MESSAGE_TEXT                         VARCHAR2(2048)
 MESSAGE_ARGUMENTS                    VARCHAR2(512)
 SUPPLEMENTAL_ATTRIBUTES              VARCHAR2(512)
 SUPPLEMENTAL_DETAILS                 VARCHAR2(4000)
 PARTITION                            NUMBER
 RECORD_ID                            NUMBER

To get alert log for the last five hours

SQL> select 
         to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), 
         MESSAGE_TEXT 
 from    X$DBGALERTEXT 
 where   ORIGINATING_TIMESTAMP>sysdate-5/24 
 order by RECORD_ID;

TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20171030-12:34:15 Thread 1 advanced to log sequence 1016 (LGWR switch)
20171030-12:34:15 Current log# 2 seq# 1016 mem# 0: +DATA2/RACTEST/ONLINELOG/group_2.1453.926116803
20171030-12:34:15 Current log# 2 seq# 1016 mem# 1: +FRA/RACTEST/ONLINELOG/group_2.20660.926116805
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_1_seq_844.2274.952747445
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_2_seq_875.15096.952747443
20171030-12:34:27 Archived Log entry 3854 added for thread 1 sequence 1015 ID 0x7553f3c0 dest 1:

6 rows selected.

To get alert log for the first 20 lines

SQL> select 
           to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), 
           MESSAGE_TEXT 
     from  X$DBGALERTEXT 
     order by RECORD_ID 
     fetch first 20 rows only;

TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20170825-16:13:10 Starting ORACLE instance (normal) (OS id: 10587)
20170825-16:13:10 CLI notifier numLatches:53 maxDescs:4998
20170825-16:13:10 **********************************************************************
20170825-16:13:10 Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
20170825-16:13:10 Per process system memlock (soft) limit = UNLIMITED
20170825-16:13:10 Expected per process system memlock (soft) limit to lock
20170825-16:13:10 SHARED GLOBAL AREA (SGA) into memory: 16G
20170825-16:13:10 Available system pagesizes:
20170825-16:13:10 4K, 2048K
20170825-16:13:10 Supported system pagesize(s):
20170825-16:13:10 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
20170825-16:13:10 4K Configured 10 10 NONE
20170825-16:13:10 2048K 68613 8193 8193 NONE
20170825-16:13:10 **********************************************************************
20170825-16:13:10 LICENSE_MAX_SESSION = 0
20170825-16:13:10 LICENSE_SESSIONS_WARNING = 0
20170825-16:13:10 Initial number of CPU is 32
20170825-16:13:10 Number of processor cores in the system is 16
20170825-16:13:10 Number of processor sockets in the system is 2
20170825-16:13:10 Private Interface 'bond1:1' configured from GPnP for use as a private interconnect.

20 rows selected.
Advertisement

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

Reconfigure OS Parameters After Linux Server Memory Upgrade

Adjust OS memory parameters after more or less memory available

One of the Oracle database server ‘s memory is to be upgraded from 48GB to 72GB, so some parameters should be adjusted to take advantage of the newly added memory.

Change memlock Parameter

Modify “memlock” parameter in /etc/security/limits.conf to 70G, which is for hugepage purpose. This parameter can be bigger than SGA, up to or even bigger than memory size.

oracle  soft      memlock  73400320
oracle  hard      memlock  73400320
  grid  soft      memlock  73400320
  grid  hard      memlock  73400320

Change nr_hugepages Parameter

Change parameter ‘nr_hugepages’  in /etc/sysctl.conf.
43GB memory( 60% of whole memory ) is allocated to hugepages.

vm.nr_hugepages = 22016

Change shmmni, shmall and shmmax  values in /etc/sysctl.conf

 a)Keep kernel.shmmni = 4096

b) kernel.shmall=13369344 (51GB)  = 70% of whole memory divided by page size 4096.

c ) kernel.shmmax=38654705664( 36GB) =  50% of the whole memory size.

kernel.shmmni = 4096
kernel.shmall = 13369344
kernel.shmmax = 38654705664

Reconfigure SGA_TARGET and SGA_MAX_SIZE

Make sure the total SGA usage is less than vm.nr_hugepages = 22016( 43GB).

“Database option mismatch: PDB installed version NULL.” in PDB_PLUG_IN_VIOLATIONS

Since 12.2.0.1, CDB can have same or more options installed than PDB

There are warning messages in PDB_PLUG_IN_VIOLATIONS :

SQL>select NAME,CAUSE,MESSAGE,TYPE,CON_ID 
      from PDB_PLUG_IN_VIOLATIONS;

NAME         CAUSE  MESSAGE                                                                                              
----------  ------- ---------------------------------------------
                                                 TYPE      CON_ID
------------ ---------- -------------------- ------------------
RACTESTPDB   OPTION     Database option DV mismatch: PDB installed version NULL. CDB installed version 12.2.0.1.0.           WARNING    3 
RACTESTPDB   OPTION     Database option OLS mismatch: PDB installed version NULL. CDB installed version 12.2.0.1.0.          WARNING    3
RACTESTPDB   OPTION     Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.2.0.1.0.          WARNING    3
RACTESTPDB   APEX       APEX mismatch: PDB installed version NULL CDB installed version 5.0.4.00.12                          WARNING    3

Check what options installed in CDB ( CDB$ROOT ):

SQL> show con_name

CON_NAME
--------------
CDB$ROOT

SQL> select COMP_NAME from dba_registry;

COMP_NAME
----------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Real Application Clusters
Oracle XML Database
Oracle Workspace Manager
Oracle Text
Oracle Multimedia
Spatial                    <--- Not in PDB
Oracle Label Security      <--- Not in PDB
Oracle Application Express <--- Not in PDB
Oracle Database Vault      <--- Not in PDB

14 rows selected.

Check what options installed in PDB:

SQL> show con_name

CON_NAME
-----------------
RACTESTPDB

SQL> select COMP_NAME from dba_registry;

COMP_NAME
-------------------------------------
JServer JAVA Virtual Machine
Oracle Database Catalog Views
Oracle Database Java Packages
Oracle Database Packages and Types
Oracle Multimedia
Oracle Real Application Clusters
Oracle Text
Oracle Workspace Manager
Oracle XDK
Oracle XML Database

10 rows selected.

We can see CDB has four more options installed than PDB.

CONCLUSION

It is accepted that CDB not necessarily has same options installed as PDB. But CDB should have same or more options installed than PDB.

So in this situation, the warning messages in PDB_PLUG_IN_VIOLATIONS can be ignored.