Query Alert Log Contents From Database

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

The view x$dbgalertext is the table 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.

 

Advertisements
Posted in SQL Scripts For DBA | Tagged , , | Leave a comment

ORA-600 [kwqmnuji: bad option] In 11.2.0.4 RAC

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.

Posted in ORA- ERRORS | Tagged , , , | Leave a comment

Drop a Tablespace With a 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 below 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

Posted in ORA- ERRORS | Tagged , , , , , | Leave a comment

Reconfigure OS Parameters After Linux Server Memory Upgrade

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.

1)  Change ‘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

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

vm.nr_hugepages = 22016

3)  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

4) Reconfigure SGA_TARGET and SGA_MAX_SIZE of database instances. Make sure the total SGA usage is less than vm.nr_hugepages = 22016( 43GB).

Posted in Configuration, Linux | Tagged , , , , , , , , | Leave a comment

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

There are below 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.

Posted in 12c new features, CDB/PDB | Tagged , , , , , | Leave a comment

How to Configure SHMMAX SHMMNI SHMALL ?

Every DBA is so familiar with those three kernel parameters in Linux — SHMMAX, SHMMNI and SHMALL. Most of the time, the default values are used by majority clients. But sometime, those three parameters need to be reconfigured to reflect the system memory sizing and total databases SGA sizing.

What are SHMMAX SHMMNI SHMALL ?

SHMMAX :  The maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space.

 SHMMNI: The system wide maximum number of shared memory segments.

SHMALL: The total amount of shared memory pages that can be used system wide.

How to check current SHMMAX SHMMNI SHMALL values ?

a) run “ipcs -lm”.

$ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096   <-------------- SHMMNI
max seg size (kbytes) = 273678336  <----------- SHMMAX
max total shared memory (kbytes) = 371195904 <- SHMALL * PAGE_SIZE
min seg size (bytes) = 1

b) Check the values in proc file system.

$ getconf PAGE_SIZE
4096

$ cat /proc/sys/kernel/shmall
92798976


$ cat /proc/sys/kernel/shmmax
280246616064


$ cat /proc/sys/kernel/shmmni
4096

How to change SHMMAX SHMMNI SHMALL values ?

a) Change them in the proc file system without reboot.

# echo 92798976 > /proc/sys/kernel/shmall

# echo 4096 >  /proc/sys/kernel/shmmni

# echo 280246616064 > /proc/sys/kernel/shmmax

b) Use sysctl without reboot.

# sysctl -w kernel.shmall=92798976

# sysctl -w kernel.shmmni=4096

#  sysctl -w kernel.shmmax=280246616064

c) To make the change permanent.  This file is used during the boot process.

Edit /etc/sysctl.conf with below lines:

kernel.shmmni = 4096
kernel.shmall = 92798976
kernel.shmmax = 280246616064

What are the optimal values for  SHMMAX SHMMNI SHMALL ?

SHMMAX : Either SGA size of any individual database or half size of the system memory, whichever is the higher.

SHMMNI: Normally default value 4096 should be sufficient.

SHMALL: The sum of all the SGAs on the system, divided by the page size. The page size can be gotton by :

 $ getconf PAGE_SIZE
 4096
Posted in Configuration, Linux | Tagged , , , , , , , , , , , , | Leave a comment

ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpcreates

Below errors occurred while starting up a 12.2.0.1 Oracle database instance.

SQL> startup

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

The error “operation:semget failed with status: 28” points out that it could be semaphore resource issue.

Oracle 12.2.0.1 doc recommended minimum value for semaphore are :

kernel.sem = SEMMSL SEMMNS SEMOPM SEMMNI
SEMMSL - max semaphores per array
SEMMNS - max semaphores system wide
SEMOPM - max ops per semop call
SEMMNI - max number of arrays

semmsl  =   250
semmns  = 32000
semopm  =   100
semmni  =   128

Check current system configuration for semaphores:

$ cat /proc/sys/kernel/sem
250 32000 100 200

$ ipcs -ls

------ Semaphore Limits --------
max semaphores per array   = 250
max semaphores system wide = 32000
max ops per semop call     = 100
max number of arrays       = 200
semaphore max value        = 32767

We can see SEMMNS is not right. it is supposed to be equal to SEMMSL*SEMMNI =250*200=50000.

If  SEMMNS  value too small, then we have to increase SEMMNI first, then adjust SEMMNS to SEMMSL*SEMMNI.

Set semaphore kernel parameter dynamically without the need for server reboot:

# sysctl -w kernel.sem="250 50000 100 200"
kernel.sem = 250 50000 100 200

-- Put into file
cat /etc/sysctl.conf | grep kernel.sem
kernel.sem = 250 50000 100 200

Or put into /etc/sysctl.conf file first, then

# sysctl -p

Then verify the current semaphore configurations:

# ipcs -ls

------ Semaphore Limits --------
max number of arrays       = 200
max semaphores per array   = 250
max semaphores system wide = 50000
max ops per semop call     = 100
semaphore max value        = 32767

Finally start up instance successfully.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8801008 bytes
Variable Size 1073743120 bytes
Database Buffers 3187671040 bytes
Redo Buffers 24752128 bytes
Database mounted.
Database opened.

 

Posted in Configuration, ORA- ERRORS | Tagged , , , , , , , , , , , , , , | Leave a comment

PARALLEL_MIN_SERVERS Parameter Behaviours In 12.2.0.1

By Default, when 12.2.0.1 instance starts up, there are more than necessary parallel processes created.

In this test case, there are 133 parallel processes created when instance starts up.

SQL>select * from v$px_process_sysstat where statistic like '%Server%'


STATISTIC                      VALUE      CON_ID
------------------------------ ---------- ----------
Servers In Use                    0          0
Servers Available               133          0
Servers Started                 133          0
Servers Shutdown                  0          0
Servers Highwater                10          0
Servers Cleaned Up                0          0
Server Sessions               12577          0

7 rows selected.

Look at the default parameter values related to parallel process:

SQL> show parameter parallel_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
parallel_max_servers                 integer     352
parallel_min_servers                 integer     128
parallel_servers_target              integer     512
parallel_threads_per_cpu             integer       2
...
.

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
cpu_count                            integer     32

According to Oracle Doc, the default value for :

PARALLEL_MAX_SERVERS = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
PARALLEL_MIN_SERVERS = CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2
PARALLEL_SERVERS_TARGET = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2

If MEMORY_TARGET or SGA_TARGET parameter is set, then the number of concurrent_parallel_users = 4.
If neither MEMORY_TARGET or SGA_TARGET is set, then if a value is set for PGA_AGGREGATE_TARGET, then concurrent_parallel_users = 2. If a value is not set for PGA_AGGREGATE_TARGET, then concurrent_parallel_users = 1.


Let’s change PARALLEL_MIN_SERVERS to a lower than default value from 128 to 32. It does not work as specified. There are still 131 parallel processes started when instance bounced.

SQL> show parameter PARALLEL_MIN_SERVERS

NAME                                 TYPE         VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                  integer     32

SQL> select * from v$px_process_sysstat where statistic like '%Server%';

STATISTIC                      VALUE CON_   ID
------------------------------ ---------- ----------
Servers In Use                    0           0
Servers Available               131           0
Servers Started                 131           0
Servers Shutdown                  0           0
Servers Highwater                 6           0
Servers Cleaned Up                0           0
Server Sessions                 183           0

7 rows selected.

Let’s change  PARALLEL_MIN_SERVERS to 0. it works perfectly because there are only 5 parallel processes started when instance bounced.

SQL> show parameter PARALLEL_MIN_SERVERS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
parallel_min_servers                  integer     0

SQL> select * from v$px_process_sysstat where statistic like '%Server%';

STATISTIC                       VALUE      CON_ID
------------------------------ ---------- ----------
Servers In Use                    0          0
Servers Available                 5          0
Servers Started                   5          0
Servers Shutdown                  0          0
Servers Highwater                 5          0
Servers Cleaned Up                0          0
Server Sessions                  32          0

7 rows selected.

So in order to reduce the number of parallel processes when instance starts up, make PARALLEL_MIN_SERVERS = 0, then the number of parallel processes will be down to smaller. Bouncing instance is not required in 12.2.0.1.

Posted in 12c new features, Performance Tunning | Tagged , , , , , | Leave a comment

Create a Container Database (CDB) With A Subset Of Options Using DBCA In 12.2.0.1

For 12.1.0.2,  How to Create a Container Database (CDB) With Selected Options 

For 12.2.0.1, we don’t need create scripts from DBCA first, manually modify scripts, then run the scripts sequentially to create a CDB/PDB database with required options in both CDB and PDB..

In 12.2.0.1, CDB and PDB can have different options installed. here are the brief steps to do so :

1)Launch DBCA -> Create a Database -> Advanced Configuration -> Custom Database -> ……->……-> Database Options:

we can select all components for CDB$ROOT, and subset of options for PDB according to your licence bought from Oracle.

Please note below three components have been disabled as per Oracle Doc ID 948061.1.

Oracle Advanced Analytics
Oracle OLAP
Oracle Real Application Testing

2) Continue the rest steps, and create the database.

3) Check the options installed in both CDB and CDB.

a) in CDB$ROOT:

SQL> show con_name

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

SQL> select COMP_NAME,STATUS from dba_registry order by 1;

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

14 rows selected.

 

b) In PDB:

SQL> show con_name

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

SQL> select COMP_NAME,STATUS from dba_registry;

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

10 rows selected.
Posted in 12c new features, CDB/PDB | Tagged , , , , , , , | Leave a comment

PDB_PLUG_IN_VIOLATIONS

PDB_PLUG_IN_VIOLATIONS displays information about incompatibilities between a PDB and the CDB to which it belongs.

SQL> select name,cause,type,action from pdb_plug_in_violations where status <> 'RESOLVED';

NAME          CAUSE              TYPE
------------- ----------------   ---------
ACTION
-------------------------------------------
PDB$SEED       SQL Patch        ERROR
Call datapatch to install in the PDB or the CDB

PDB$SEED        SQL Patch       ERROR
Call datapatch to install in the PDB or the CDB

Run ‘datapatch’ again:

$./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue Sep 26 14:16:33 2017
Copyright (c) 2012, 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_103685_2017_09_26_14_16_33/sqlpatch_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)):
 Not installed in the binary or the SQL registry
Patch 26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)):
 Installed in the binary registry and CDB$ROOT RACTESTPDB PDB$SEED
Bundle series PSU:
 ID 170814 in the binary registry and ID 170814 in PDB CDB$ROOT, ID 170814 in PDB PDB$SEED, ID 170814 in PDB RACTESTPDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED RACTESTPDB
 Nothing to roll back
 Nothing to apply

SQL Patching tool complete on Tue Sep 26 14:18:39 2017

Check again without any issues.

SQL> select * from pdb_plug_in_violations where status <> 'RESOLVED';

no rows selected
Posted in 12c new features, CDB/PDB | Tagged , | Leave a comment