Month: September 2017

ORA-27154 ORA-27300 ORA-27301 ORA-27302 from Instance Startup

Always make sure “kernel.sem = SEMMSL SEMMNS SEMOPM SEMMNI” set-up correctly to avoid issues.

The following 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.
Advertisement

PARALLEL_MIN_SERVERS Parameter Behaviors

Set PARALLEL_MIN_SERVERS = 0 to reduce unnecessary parallel processes when instance up

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.

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

Since 12.2.0.1, CDB and PDB can have different components. PDB can have a subset components of CDB.

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, then manually modify scripts, and 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 steps to do so :

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

Choose options for CDB and PDB respectively
DBCA: Choose options for CDB and PDB respectively

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.

PDB_PLUG_IN_VIOLATIONS

Always check PDB_PLUG_IN_VIOLATIONS for inconsistency between CDB and PDB, and run “datapatch” to resolve it

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 issues anymore.

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

no rows selected

RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows

It is a good practice to upgrade RMAN CATLOG to the latest version in your environment

SYMPTOMS

Target Database : 12.2.0.1
RMAN Catalog  :  12.1.0.2

Trying to “upgrade catalog” from 12.2.0.1 RMAN and 12.1.0.2  RMAN catalog, then got following errors:

connected to target database: RACTEST (DBID=2073981615)
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;
error creating init_nrsp_pdb_key
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows

CAUSES

Bug 25852885 UPGRADE CATALOG WITH 12.2 RMAN ON 12.1 DATABASE FAILS W/ ORA-1422

SOLUTION

Backup then edit the recover.bsq file under target 12.2.0.1 ORACLE_HOME/rdbms/admin to change the following lines:

define init_nrsp_pdb_key
<<<
declare
   cursor nrsp_c is
      select distinct nrsp.rowid rid, dbinc.db_key db_key
        from nrsp, dbinc
       where nrsp.pdb_key is null
         and nrsp.dbinc_key = dbinc.dbinc_key
       order by dbinc.db_key;
   prev_db_key   number := 0;
   local_pdb_key number;
begin
   for r in nrsp_c loop
     if (r.db_key <> prev_db_key) then
        prev_db_key := r.db_key;
        select pdb_key into local_pdb_key
          from pdb
         where pdb.con_id in (1, 0);
     end if;

 TO:

define init_nrsp_pdb_key
<<<
declare
   cursor nrsp_c is
      select distinct nrsp.rowid rid, dbinc.db_key db_key
        from nrsp, dbinc
       where nrsp.pdb_key is null
         and nrsp.dbinc_key = dbinc.dbinc_key
       order by dbinc.db_key;
   prev_db_key   number := 0;
   local_pdb_key number;
begin
   for r in nrsp_c loop
     if (r.db_key <> prev_db_key) then
        prev_db_key := r.db_key;
        select pdb_key into local_pdb_key
          from pdb
         where pdb.con_id in (1, 0)
           and pdb.db_key = r.db_key;
     end if;

AND CHANGE :

define init_grsp_pdb_key
<<<
declare
   cursor grsp_c is
      select distinct grsp.rowid rid, dbinc.db_key db_key
        from grsp, dbinc
       where grsp.pdb_key is null
         and grsp.dbinc_key = dbinc.dbinc_key
       order by dbinc.db_key;
   prev_db_key   number := 0;
   local_pdb_key number;
begin
   for r in grsp_c loop
     if (r.db_key <> prev_db_key) then
        prev_db_key := r.db_key;
        select pdb_key into local_pdb_key
          from pdb
         where pdb.con_id in (1, 0);
     end if;

 TO:

define init_grsp_pdb_key
<<<
declare
   cursor grsp_c is
      select distinct grsp.rowid rid, dbinc.db_key db_key
        from grsp, dbinc
       where grsp.pdb_key is null
         and grsp.dbinc_key = dbinc.dbinc_key
       order by dbinc.db_key;
   prev_db_key   number := 0;
   local_pdb_key number;
begin
   for r in grsp_c loop
     if (r.db_key <> prev_db_key) then
        prev_db_key := r.db_key;
        select pdb_key into local_pdb_key
          from pdb
         where pdb.con_id in (1, 0)
           and pdb.db_key = r.db_key;
     end if;

Then re-run “upgrade catalog” twice:

RMAN> upgrade catalog;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;
PL/SQL package RMAN.DBMS_BA version 12.02.00.01 in RCVCAT database is too old
recovery catalog upgraded to version 12.02.00.01
DBMS_RCVMAN package upgraded to version 12.02.00.01
DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Please note: As of today ( 25/09/2017), Oracle support released patch 25852885.

  1. It works for 12.2.0.1.0 only, because updated “recover.bsq” is included in patch ( p25852885_122010_Linux-x86-64.zip) .
  2. It does NOT work for 12.2.0.1.170814, because updated “recover.bsq” is not included in  patch ( p25852885_12201170718DBRU_Linux-x86-64.zip).