Martins Blog

Trying to explain complex things in simple terms

Archive for June, 2016

Tales from the field: potential reasons for PDB plug-in violations part 1

Posted by Martin Bach on June 29, 2016

Container Databases have been an area that I have researched intensively over the past years. With this post (and hopefully some others that follow) I would like to demonstrate some of the new situations the DBA might be confronted with. Please don’t use this post to give the new 12c architecture a hard time: standardised deployments (which I love) help you a lot. Not only do your DBA scripts work reliably everywhere, but the error condition I am showing in this post should be a lot less likely.

At the end of the post I’ll show an alternative approach using a standardised way of creating PDBs.

Environment

Setting the scene, my environment is as follows:

  • Oracle Linux 7.2 with UEK4 (4.1.12-37.5.1.el7uek.x86_64 GNU/Linux)
  • Single Instance databases CDB1 and CDB3
  • Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
  • Database Patch Set Update : 12.1.0.2.160419 (22291127)
  • Oracle Managed Files (OMF) but no use of ASM

These aren’t in VMs for a change.

Scenario

The following steps lead me to the discovery of the plug-in violation. In CDB1 I created a common user, let’s call it c##martin for the sake of argument.

SQL> select sys_context('userenv','con_name') as con_name, name, cdb
  2  from v$database
  3  /

CON_NAME                       NAME      CDB
------------------------------ --------- ---
CDB$ROOT                       CDB1      YES

SQL> create user c##martin identified by xxxxxxxxxx 
  2  default tablespace tools  temporary tablespace temp
  3  quota unlimited on tools account unlock
  4  container = ALL;

User created.

So far nothing too exciting. Grant privileges as needed.

The Problem

Some time later I tried to create a PDB from the seed, and it failed:

SQL> create pluggable database pdb10 
  2  admin user pdbadmin identified by xxxxxxxxxx
  3  /

Pluggable database created.

SQL> alter pluggable database pdb10 open;

Warning: PDB altered with errors.

SQL> 

Altered with errors? The command I just typed is probably the simplest and most basic way to create a PDB, what could possibly go wrong with it? Fist stop is the alert.log, and this is what has been recorded:

2016-06-29 09:48:38.261000 +01:00
create pluggable database pdb10
admin user pdbadmin identified by *
 APEX_040200.WWV_FLOW_ADVISOR_CHECKS (CHECK_STATEMENT) - CLOB populated
2016-06-29 09:48:44.549000 +01:00
****************************************************************
Pluggable Database PDB10 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB10 is WE8MSWIN1252
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#19 to file$(old file#5)
Adding new file#20 to file$(old file#7)
Successfully created internal service pdb10 at open
2016-06-29 09:48:45.629000 +01:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB10 with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdb10
admin user pdbadmin identified by *
2016-06-29 09:53:48.816000 +01:00
alter pluggable database pdb10 open
Pluggable database PDB10 dictionary check beginning
Pluggable Database PDB10 Dictionary check complete
Database Characterset for PDB10 is WE8MSWIN1252
2016-06-29 09:53:51.317000 +01:00
Opening pdb PDB10 (3) with no Resource Manager plan active
Pluggable database PDB10 opened read write
Completed: alter pluggable database pdb10 open

OK so there’s nothing in there. But wait-am I not trying to create a new PDB and plug it into the CDB? And aren’t problems in there recorded in a view? So let’s give that a try:

SQL> select cause, message from pdb_plug_in_violations where name = 'PDB10';

CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
Sync Failure
Sync PDB failed with ORA-959 during 'create user c##martin identified by *defaul
t tablespace tools  temporary tablespace temp
quota unlimited on tools account unlock
container = ALL'

Got you! A sync failure: the common user I created earlier (container = ALL) can’t be created on the PDB because of a missing tablespace. Is the PDB in an unrecoverable state? No, but it is opened in restricted mode. Connections to it won’t be possible:

SQL> select name,open_mode,restricted from v$pdbs 
  2  where name = 'PDB10';

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB10                          READ WRITE YES

SQL> conn pdbadmin/xxxxxxxxxx@localhost/PDB10
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

NB: the same can happen when you create a common user in the CDB while a PDB that doesn’t meet the criteria for it to created is closed. For example, if all PDBs are open and you try to create c#martin and there is a PDB without the tools tablespace the create user command fails. It will not fail if a PDB doesn’t have a tools tablepsace but is closed at the time the user is created. The command to open the PDB will throw the same error as shown above when you try to open it.

The Fix

Dead easy-a common user cannot be created due to a missing tablespace. So let’s create it and see if we can open the PDB:

SQL> conn / as sysdba
Connected.
SQL> alter session set container = PDB10;

Session altered.

SQL> create tablespace tools datafile size 10m;

Tablespace created.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb10 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb10 open;

Pluggable database altered.

SQL> select name,open_mode,restricted from v$pdbs 
  2   where name = 'PDB10';

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB10                          READ WRITE NO

SQL> 

Fixed! But I would still argue that a different approach – a “gold image PDB” might be better suited for production work than a clone from the seed.

A potentially better Approach

I personally think that creating a PDB from the seed is not something that should be done in production, but that is – a personal opinion…

The problem you just read about could be avoided with a Golden Image CDB/PDB. I wrote about a way to create such a golden database image in my last post. Following the procedure I created CDB3 using dbca feeding it my template. It contains the goldenImage PDB which has been created as follows:

SQL> select sys_context('userenv','con_name') as con_name, name, cdb
  2  from v$database;

CON_NAME                       NAME      CDB
------------------------------ --------- ---
CDB$ROOT                       CDB3      YES

SQL> create pluggable database goldenImage 
  2  admin user pdbadmin identified by xxxxxxxx
  3  default tablespace users datafile size 10m
  4  storage (maxsize 20G);

Pluggable database created.

SQL> alter pluggable database goldenImage open;

Pluggable database altered.

SQL> alter session set container = goldenImage;

Session altered.

SQL> create tablespace tools datafile size 10m;

Tablespace created.

CDB3 also features a user C##MARTIN which has been created exactly as in CDB1:

SQL> select u.username, u.account_status, nvl(p.name, 'CDB$ROOT') con_name
  2  from cdb_users u, v$pdbs p
  3  where u.username = 'C##MARTIN'
  4 and u.con_id = p.con_id(+);

USERNAME             ACCOUNT_STATUS       CON_NAME
-------------------- -------------------- ------------------------------
C##MARTIN            OPEN                 CDB$ROOT
C##MARTIN            OPEN                 GOLDENIMAGE

With everything in place I can go about cloning goldenImage and avoid the problem I wrote about in the first part of this post altogether.

SQL> create pluggable database pdb10 from goldenImage;

Pluggable database created.

SQL> alter pluggable database pdb10 open;

Pluggable database altered.

SQL> select cause,message from pdb_plug_in_violations where name = 'PDB10';

no rows selected

SQL> conn c##martin/xxxxxxxxxx@localhost/pdb10
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB10

Standards win again.

Posted in 12c Release 1, Linux | Leave a Comment »

Automatic Deployment of CDBs with non-default database options in 12.1.0.2

Posted by Martin Bach on June 20, 2016

I have written about the importance of automation in previous posts, and this one is following the same line. This time I wanted to test and document my experience with “golden database templates”. Wy? Because most DBAs will appreciate that there are more interesting things to do than to run “create database” scripts all day, so why not automate the process? For quite some time DBCA, or the Database Creation Assistant offers you the option to create templates. One approach you could use would be to

  • Create a “golden master” database
  • Use DBCA to create a template based on this
  • And enjoy the fact that every database is exactly the same

All of this is done in hope to make life for the administrators a lot easier.

Motivation

In many environments a database is not an island, it’s rather part of a wider ecosystem where monitoring solutions, auditing, and what-have-you need to integrate with each database from the moment it is deployed. A template – at least to my mind – is better suited to this task than executing a number of scripts post database creation. SQL scripts can always run into errors, and their execution might require review of the output by a human (which is what I’d like to avoid). Oh, and it seems to take forever, even on reasonably modern hardware, to create a database from scripts.

Furthermore, it is possible to create a CDB with fewer options in 12.1.0.2, as explained by Mike Dietrich and on MOS. I wanted to get a reference to myself on how to deploy a CDB exactly the same way, each time, without all the options in it. Please have a look at these references for more background information.

All of them are very good references, and they contain the necessary “ifs” and “buts”. Please make sure you understand them all, especially those in MOS DOC ID 1616554.1, section 1. If the referenced documents resonate with you, and the pros/cons are understood, read on :)

Further to the introductory question on why to do this, there is more to mention: I don’t want any extra components in my database, for 2 main reasons:

  1. A component that is not installed can’t be used as an attack vector
  2. Components that aren’t installed in the RDBMS can’t be used accidentally

And there are many more … For the purpose of this post I am going to create a database with all the extra components I ever need: none.

In case you found this via an Internet search engine, here are the facts about my environment.

[oracle@server1 OPatch]$ ./opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.
[oracle@server1 OPatch]$ cat /etc/oracle-release
Oracle Linux Server release 6.8
[oracle@server1 OPatch]$ uname -r -o
4.1.12-37.4.1.el6uek.x86_64 GNU/Linux

This is not a RAC nor Oracle Restart database, just a single RDBMS home with the databases on traditional file system. This is a VM running on VirtualBox 5.0.20 in my lab.

Create the Golden Image database

Following MOS note 2001512.1 and understanding the comments in each section I consciously make the decision to remove everything that’s not needed for my purposes (these aren’t necessarily yours!) I decided to create the scripts for a CDB named CDBTEMPL. After exiting DBCA I can find these scripts in /u01/app/oracle/admin/CDBTEMPL/scripts/

[oracle@server1 scripts]$ ls -l
total 72
-rwxr-xr-x. 1 oracle oinstall  834 Jun 15 08:31 CDBTEMPL.sh
-rwxr-xr-x. 1 oracle oinstall 1260 Jun 15 08:31 CDBTEMPL.sql
-rw-r-----. 1 oracle oinstall  550 Jun 15 08:31 CreateClustDBViews.sql
-rw-r-----. 1 oracle oinstall 1552 Jun 15 08:31 CreateDB.sql
-rw-r-----. 1 oracle oinstall 1668 Jun 15 08:31 CreateDBCatalog.sql
-rw-r-----. 1 oracle oinstall  397 Jun 15 08:31 CreateDBFiles.sql
-rw-r-----. 1 oracle oinstall 1171 Jun 15 08:31 JServer.sql
-rw-r-----. 1 oracle oinstall  386 Jun 15 08:31 apex.sql
-rw-r-----. 1 oracle oinstall  998 Jun 15 08:31 context.sql
-rw-r-----. 1 oracle oinstall  353 Jun 15 08:31 cwmlite.sql
-rw-r-----. 1 oracle oinstall  365 Jun 15 08:31 datavault.sql
-rw-r-----. 1 oracle oinstall 2011 Jun 15 08:31 init.ora
-rw-r-----. 1 oracle oinstall  342 Jun 15 08:31 interMedia.sql
-rw-r-----. 1 oracle oinstall  344 Jun 15 08:31 labelSecurity.sql
-rw-r-----. 1 oracle oinstall 1155 Jun 15 08:31 lockAccount.sql
-rw-r-----. 1 oracle oinstall  360 Jun 15 08:31 ordinst.sql
-rw-r-----. 1 oracle oinstall  857 Jun 15 08:31 postDBCreation.sql
-rw-r-----. 1 oracle oinstall  335 Jun 15 08:31 spatial.sql

I need to update the $ORACLE_SID.sql file, which originally looks like this:

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter  user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter  user password: ' HIDE
host /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwCDBTEMPL force=y format=12
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDB.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/JServer.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/context.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/ordinst.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/interMedia.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/cwmlite.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/spatial.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/labelSecurity.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/apex.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/datavault.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/lockAccount.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/postDBCreation.sql

Using the very useful dependency matrix in MOS note 2001512.1 I can safely remove all components I don’t need for my particular case (again yours might be different). The resulting file looks like this for me:

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter  user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter  user password: ' HIDE
host /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwCDBTEMPL force=y format=12
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDB.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/lockAccount.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/postDBCreation.sql

Another VERY important aspect is the use of ORACLE’s perl. You need to make sure that your PATH uses oracle’s perl, and not the one installed with the host. In other words, “which perl” typed into the command prompt should return /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl, as shown in this example:

[oracle@server1 ~]$ export PATH=$ORACLE_HOME/perl/bin:$PATH
[oracle@server1 ~]$ which perl
/u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl

If you are on a recent chip and want try these steps in Virtualbox like me, you might run into issues with perl core-dumping on you. Have a look at http://dbaontap.com/2016/01/13/vbox5/ before you start to see if you are susceptible to the same problem.

When you are ready, start the database creation in a screen session.

[oracle@server1 dbs]$ cd /u01/app/oracle/admin/CDBTEMPL/scripts/
[oracle@server1 scripts]$ export PATH=$ORACLE_HOME/perl/bin:$PATH
[oracle@server1 scripts]$ which perl
/u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl
[oracle@server1 scripts]$ bash CDBTEMPL.sh

Don’t be startled by having to enter a password for USER twice, this is a slight irregularity with the script, looking at the source it appears it’s prompting you for Data Vault and Oracle Label Security information:

ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter  user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter  user password: ' HIDE

The last 2 don’t quite make sense, and going by the extra space character there should have been a username …

Once the script has been started, go and get some coffee. It will take a while to finish. After it completed, it’s time to see what we have. The alias “sq” is just a shortcut for “sqlplus / as sysdba”.

[oracle@server1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 10:20:10 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

SQL> select comp_name, version, status from dba_server_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ -----------
Oracle Workspace Manager                 12.1.0.2.0                     VALID
Oracle XML Database                      12.1.0.2.0                     VALID
Oracle Database Catalog Views            12.1.0.2.0                     VALID
Oracle Database Packages and Types       12.1.0.2.0                     VALID
Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF

SQL> select cdb, name from v$database;

CDB NAME
--- ---------
YES CDBTEMPL

SQL> select * From v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1

So this looks like it worked in my favour: the database is created, and I can merely see the bare minimum of options present. Well actually it seems that you could potentially also remove workspace manager and the RAC views, but they are ok for me to keep. Refer to MOS for more details about these two.

The database is in fact a CDB, and it’s name is CDBTEMPL. All the users I might want are in the password file as well.

Customising the Golden Image

First I want a new PDB that I use as en lieu of the PDB$SEED as the basis for new PDBs. Let’s create it:

SQL> !mkdir /u01/oradata/CDBTEMPL/MASTER

SQL> create pluggable database MASTER admin user PDBMASTER identified by PDBMASTERPWD
  2  default tablespace users
  3  datafile '/u01/oradata/CDBTEMPL/MASTER/users01.dbf'
  4  size 250m autoextend on next 250m maxsize 1G
  5  file_name_convert = ('/u01/oradata/CDBTEMPL/pdbseed','/u01/oradata/CDBTEMPL/MASTER')
  6  storage (maxsize 20G);

Pluggable database created.

While I’m still connected to the CDB$ROOT I create a few common user accounts that I need:

SQL> create user c##backupadmin identified by backupadminpwd;

User created.

SQL> grant sysbackup to c##backupadmin;

Grant succeeded.

SQL> create user c##monitor identified by monitorpassword;

User created.

-- grants as needed for the monitoring account

Switching to the MASTER-PDB in the next step I create components that I might need, for example STATSPACK or SQLT. Or anything else your standards require you to put in there. For example:

SQL> alter session set container = MASTER;
Session altered.
SQL> show con_name

CON_NAME
------------------------------
MASTER

SQL> create tablespace statpack_tbs datafile
  2  '/u01/oradata/CDBTEMPL/MASTER/statpack_tbs01.dbf'
  3  size 250m autoextend on next 250m maxsize 5g;

Tablespace created.

SQL> start ?/rdbms/admin/spcreate

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
STATPACK_TBS                   PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: STATPACK_TBS

Using tablespace STATPACK_TBS as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

Create the Template

With the Golden Image complete, it is time to create a template from this. You might think that all you need to do is to execute dbca and create a clone template but wait! I did this too but ran into an issue. Fast forward a bit (don’t worry I’ll detail the steps later), when I created a database off the template, dbca errored out with this message:

[oracle@server1 ~]$ dbca -silent -createDatabase -templateName CDB_MASTER_20160615.dbc \
> -gdbName CDB1 -sysPassword sysPassword -systemPassword systemPassword \
> -datafileDestination /u01/oradata -recoveryAreaDestination /u01/fra -totalMemory 4096
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
DBCA Operation failed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB1.log" for further details.

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB1.log

Unique database identifier check passed.

/u01/ has enough space. Required space is 8660 MB , available space is 14332 MB.
File Validations Successful.
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
Error while executing "/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/dbmssml.sql".
Refer to "/u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml0.log" for more details.
Error in Process: /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl
DBCA_PROGRESS : DBCA Operation failed.

Hmmm, not too good. Funny enough there was no content in the log file mentioned in the error message, but there was a similarly named file nearby:

[oracle@server1 ~]$ ls -l /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml*
-rw-r-----. 1 oracle oinstall   0 Jun 15 11:51 /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml0.log
-rw-r-----. 1 oracle oinstall 521 Jun 15 11:51 /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml_catcon_11711.lst
[oracle@server1 ~]$

Looking at dbmssml_catcon_11711.lst I saw this message:

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml_catcon_11711.lst
catcon: See /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml*.log files for output generated by scripts
catcon: See /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml_*.lst files for spool files, if any
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

        catconInit: start logging catcon output at 2016-06-15 11:51:11

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

validate_con_names: MASTER is not open
catconInit: Unexpected error returned by validate_con_names

So it seems I have to make sure that the clone database opens the MASTER PDB as part of the process. By default, a PDB is in mount mode when the database instance (re)starts. But I can force it to open. That’s not hard since 12.1.0.2, I can save a PDB’s state like this:

SQL> alter pluggable database master open;

Pluggable database altered.

SQL> alter pluggable database master save state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME                       INSTANCE_NAME        STATE
------------------------------ -------------------- --------------
MASTER                         CDBTEMPL             OPEN

That’s it-a quick test reveals that the MASTER PDB opens when I bounce the instance:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             377487464 bytes
Database Buffers          687865856 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MASTER                         READ WRITE NO
SQL>

Now I can create the clone!

[oracle@server1 ~]$ dbca -createCloneTemplate -help
Create a clone template from an existing database by specifying the following parameters:
        -createCloneTemplate
                -sourceSID    <Database unique name for RAC Database and SID for Single
                               Instance Database>
                -templateName      <new template name>
                [-sysDBAUserName     <user name  with SYSDBA privileges>
                 -sysDBAPassword     <password for sysDBAUserName user name>]
                [-maxBackupSetSizeInMB     <Maximum backup set size (MB)>]
                [-rmanParallelism  <Parallelism value>]
                [-datafileJarLocation       <directory to place the datafiles in a compressed
                                             format>]

[oracle@server1 ~]$ dbca -silent -createCloneTemplate -sourceSID CDBTEMPL -templateName \
> CDB_MASTER_20160615 -sysDBAPassword sysDBAPassword \
> -rmanParallelism 2 -datafileJarLocation /u01/templates

Gathering information from the source database
4% complete
8% complete
13% complete
17% complete
22% complete
Backup datafiles
28% complete
88% complete
Creating template file
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2016-06-15_11-31-28-AM"
for further details.

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/silent.log_2016-06-15_11-31-28-AM
Gathering information from the source database
DBCA_PROGRESS : 4%
DBCA_PROGRESS : 8%
DBCA_PROGRESS : 13%
DBCA_PROGRESS : 17%
DBCA_PROGRESS : 22%
Backup datafiles
DBCA_PROGRESS : 28%
DBCA_PROGRESS : 88%
Creating template file
DBCA_PROGRESS : 100%
The generation of the clone database template "CDB_MASTER_20160615" is successful.

That was a success!

Create a CDB from Template

This step is almost exactly the same as for the non-custom template. All you need to do is to invoke dbca in silent mode and point it to the new template. Here is an example:

[oracle@server1 ~]$ dbca -silent -createDatabase -templateName CDB_MASTER_20160615.dbc \
> -gdbName CDB1 -sysPassword sysPassword -systemPassword systemPassword \
> -datafileDestination /u01/oradata -recoveryAreaDestination /u01/fra -totalMemory 4096
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB10.log" for further details.

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB10.log

Unique database identifier check passed.

/u01/ has enough space. Required space is 10270 MB , available space is 13312 MB.
File Validations Successful.
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 62%
Completing Database Creation
DBCA_PROGRESS : 66%
DBCA_PROGRESS : 70%
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 85%
DBCA_PROGRESS : 96%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/CDB1.
Database Information:
Global Database Name:CDB1
System Identifier(SID):CDB1

Unlike in my first attempt this worked. But there are a couple of post-creation steps now to be taken. First, I would like to use MASTER as the golden master copy for each PDB. That requires the PDB to be opened read-only. But I would also like to check if everything worked

[oracle@server1 ~]$ echo $ORACLE_SID $ORACLE_HOME
CDB1 /u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@server1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 13:07:51 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MASTER                         READ WRITE NO
SQL> alter session set container = MASTER;

Session altered.

SQL> select username, account_status
  2    from dba_users
  3   where oracle_maintained <> 'Y';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SQLTXPLAIN                     OPEN
SQLTXADMIN                     EXPIRED & LOCKED
PERFSTAT                       OPEN
C##BACKUPADMIN                 EXPIRED & LOCKED
C##MONITOR                     EXPIRED & LOCKED
PDBMASTER                      OPEN

6 rows selected.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
STATPACK_TBS
SQLT_TBS

This seems to have worked. Notice how the common users are expired and locked. Now to enable cloning, I simply repeat the save state command but this time I want the PDB to be open read-only.

SQL> alter pluggable database master close immediate;

Pluggable database altered.

SQL> alter pluggable database master open read only;

Pluggable database altered.

SQL> alter pluggable database master save state;

Pluggable database altered.

-- create a new PDB

SQL> create pluggable database userpdb from master...;

Pluggable database created.

SQL> alter pluggable database userpdb open;

Pluggable database altered.

SQL> conn perfstat/perfstatPWD@localhost/userpdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
USERPDB
SQL>

That seems to have worked as well. Of course there are more variations to the theme, and you certainly need to perform intensive testing before you can roll out a strategy like this but I think you get the idea. The process cannot stop here of course, there are many more important tasks (backup, monitoring, auditing, logging, …) to be configured but those are left out for now.

Posted in 12c Release 1 | Tagged: , , | Leave a Comment »

Using OSWatcher for system diagnostics

Posted by Martin Bach on June 14, 2016

OSWatcher is a superb tool that gathers information about your system in the background and stores it in an (optionally compressed) archive directory. As an Oracle DBA I like the analogy with statspack: you make the tool available on the host in a location with – very important – enough available disk space and then start it. Most users add it to the startup mechanism their O/S uses- SysV init, upstart, or systemd for example on Linux to allow it to start in the background. OSWatcher will then gather a lot of the interesting O/S related statistics that you so desperately need in an “after the fact” situation. There are plenty of reasons where you might want that information.

Example use cases

Say for example the system experienced an outage-a RAC node rebooted. The big question is “why?”. There might be information in the usual Grid Infrastructure logs but they might be inconclusive and a look at the O/S can be necessary. The granularity SAR offers is often not enough, you are bound to loose detail information in the 10 minute average. What if you had 30 second granularity to see what happened more or less right before the crash?

Or maybe you want to see the system’s activity during testing for a new software release? And then compare to yesterday’s test cycle? The use cases are endless, and I’m sure you can think of one, too.

ExaWatcher as the role model

In Expert Oracle Exadata (both editions) the authors covered ExaWatcher quite extensively. ExaWatcher is (not really surprisingly) OSWatcher for Exadata, or at least sort of. And it’s there by default on cells and compute nodes. I have always taken the stance that if something is implemented in Exadata then someone must have spent a few brain cells working out why it’s a good idea to deploy that tool to every Exadata in the world. That’s a big responsibility to have and you’d hope that there was some serious thinking behind decisions to deploy such a tool.

OSWatcher deployment

I deploy OSWatcher by default on every VM I create in my lab. It’s part of the automated build process I use. That ensures that I’ll have a log of recent O/S information whenever I need it. Again the analogy to Statspack: just like OSWatcher Statspack isn’t installed by default and has to be created manually. In so many cases however it was deployed after the problem occurred. It could be that last night’s problems don’t re-appear the following execution cycle. It might be better to have OSWatcher deployed and recording information proactively.

Deploying OSWatcher is dead simple-get it from MOS and copy the tarball to the host to monitor. In this post I am using Oracle Linux 7.1, at first without the compatibility tools for the network stack. I customarily use the minimal installation that doesn’t come with net-tools. What are the net-tools again? They are the tools we have come to love over the years:

[oracle@rac12node2 ~]$ yum info net-tools
Available Packages
Name        : net-tools
Arch        : x86_64
Version     : 2.0
Release     : 0.17.20131004git.el7
Size        : 303 k
Repo        : local
Summary     : Basic networking tools
URL         : http://sourceforge.net/projects/net-tools/
Licence     : GPLv2+
Description : The net-tools package contains basic networking tools,
            : including ifconfig, netstat, route, and others.
            : Most of them are obsolete. For replacement check iproute package.

But they are obsolete. You could argue that it might be just the time to get used to iproute as it’s the future, but for OSWatcher on OL 7 net-tools are needed and I installed them. If you don’t OSWatcher won’t abort but can’t collect information netstat and ifconfig provide.

In case you are using RAC you need to make OSWatcher aware of the private interconnect-this is detailed in the OSWatcher user’s guide.

Starting OSWatcher

Once the tarball is unzipped in a location of your choice you need to start OSWatcher. The script startOSWbb.sh is responsible for starting the tool, and it can take a number of arguments that are explained in the shell script for convenience and the user guide available from MOS. The first parameter is used to set the snapshot interval in seconds. The second indicates the archive duration in hours (eg how long the information should be stored) and the optional third which compression tool to use for compressing the raw data. Parameters 2 and 3 have direct implications to space usage. The optional fourth parameter is used to set a custom location for the archive directory that will contain the data collected.

I want to have 30 second snapshot intervals and retain data for 2 days or 48 hours. Be aware that using a tool such as compress or gzip as the third argument will save space by compressing older files, but when you are trying to use the analyser (more on that later) then you’ll have to decompress the files first. Clever use of the find (1) command can help you uncompressing only the raw data files you need.

WARNING Depending on the system you monitor and the amount of activity you might end up generating quite a lot of data. And by a lot I mean it! The location you choose for storing the archive directory must be independent of anything important. In other words, should the location fill up despite all the efforts you put in to prevent that from happening, it must not have an impact on availability. It is imperative to keep a keen eye on space usage. You certainly don’t want to fill up important mount points with performance information.

 

Starting OSWatcher from the command line

With that said it’s time to start the tool:

[oracle@rac12node1 oswbb]$ ./startOSWbb.sh 30 48 gzip
[oracle@rac12node1 oswbb]$ Info...Zip option IS specified.
Info...OSW will use gzip to compress files.
Setting the archive log directory to/some/mount/point/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
IFCONFIG found on your system.
NETSTAT found on your system.
TOP found on your system.
Warning... /proc/slabinfo not found on your system.

Testing for discovery of OS CPU COUNT
oswbb is looking for the CPU COUNT on your system
CPU COUNT will be used by oswbba to automatically look for cpu problems

CPU COUNT found on your system.
CPU COUNT = 2

Discovery completed.

Starting OSWatcher v7.3.3  on Wed Dec 16 10:02:49 GMT 2015
With SnapshotInterval = 30
With ArchiveInterval = 48

OSWatcher - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)

...

Data is stored in directory: /some/mount/point/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Wed Dec 16 10:02:54 GMT 2015

Launching OSWatcher from the command line is probably the exception, most users will start OSW during the boot process as part of the multi-user runlevel.

Note that if you haven’t installed net-tools on Oracle Linux 7 you will see errors when starting OSWatcher as it can’t find netstat and ifconfig. If memory serves me right then you get net-tools with previous releases of Oracle Linux by default so this is OL 7 specific.

You will also notice an error that /proc/slabinfo does not exist. It does exist, but the permissions have changed to 0400 and the file is owned by root:root. Not having slab information is not a problem for me, I always struggle to make sense of it anyway.

What it does

The OSWatcher daemon now happily monitors my system and places information into the archive directory:

[oracle@rac12node1 oswbb]$ ls -l archive/
total 40
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswifconfig
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswiostat
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswmeminfo
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswmpstat
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswnetstat
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswprvtnet
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswps
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswslabinfo
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswtop
drwxr-xr-x. 2 oracle oinstall 4096 Dec 16 10:02 oswvmstat

There is a directory per tool used – ifconfig, iostat, meminfo, …. , vmstat. Inside the directory you find the output. The file format is plain text (optionally compressed if not the current file if you specified gzip or compress as the third parameter to the start script), and each snapshot is indicated by a line starting zzz. Here is an example for iostat taken while swingbench was running.

...

zzz ***Wed Dec 16 10:32:30 GMT 2015
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.23    0.00    7.45   76.06    0.00    4.26

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.00    0.00    2.00     0.00     4.00     4.00     0.24  128.00    0.00  128.00 121.00  24.20
vdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
vdc               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
vdd               0.00     0.00    2.00    1.00     1.00     0.50     1.00     0.11   36.67   33.00   44.00  36.67  11.00
vde               0.00     0.00    2.00    1.00     1.00     0.50     1.00     0.04   14.67    0.00   44.00  14.67   4.40
vdf               0.00     0.00    2.00    1.00     1.00     0.50     1.00     0.10   31.67   25.50   44.00  31.67   9.50
vdg               0.00     0.00   43.00   23.00   400.00    97.00    15.06     3.83   57.05   62.37   47.09  15.15 100.00
vdh               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.24    0.00    0.00    0.00   0.00  24.20
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
vdi               0.00     0.00   39.00   23.00   320.00    78.50    12.85     1.11   17.40    2.85   42.09  12.39  76.80

zzz ***Wed Dec 16 10:33:00 GMT 2015
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.58    0.00    7.94   81.48    0.00    0.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00    17.00    0.00    1.00     0.00    72.00   144.00     0.01    1.00    0.00    1.00  11.00   1.10
vdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
vdc               0.00     0.00   12.00    2.00   192.00    16.00    29.71     0.13    9.57    8.25   17.50   9.57  13.40
vdd               0.00     0.00    2.00    2.00     1.00     0.50     0.75     0.03   14.50    8.50   20.50   7.25   2.90
vde               0.00     0.00    2.00    2.00     1.00     0.50     0.75     0.02   13.25    2.00   24.50   6.00   2.40
vdf               0.00     0.00    2.00    2.00     1.00     0.50     0.75     0.11   34.50   23.00   46.00  27.25  10.90
vdg               0.00     0.00   32.00   31.00   272.00   123.00    12.54     4.96   80.46   90.50   70.10  15.68  98.80
vdh               0.00     0.00    0.00    1.00     0.00     0.00     0.00     0.01   37.00    0.00   37.00   8.00   0.80
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00   19.00     0.00    76.00     8.00     0.03    0.95    0.00    0.95   0.63   1.20
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
vdi               0.00     0.00   22.00   22.00   184.00    78.50    11.93     1.28   30.45    2.00   58.91  17.23  75.80

zzz ***Wed Dec 16 10:33:30 GMT 2015
...

As you can see there are snapshots every 30 seconds, just as requested. Let’s not focus on the horrendous I/O times-this is a virtualised RAC system on a host that struggles with a CPU bottleneck, and it does not have underlying SSD for the VMs…

You can navigate the archive directory and browse the files you are interested in. They all have timestamps in their names making it easy to identify each file’s contents.

The OSWatcher Analyser

Looking at text files is one way of digesting information. There is another option, the OSWatcher analyser. Here is an example of its use (it requires the DISPLAY variable to be set):

[oracle@rac12node1 oswbb]$ java -jar oswbba.jar -i archive -b "Dec 16 10:25:00 2015" \
> -e "Dec 16 10:35:00 2015" -P swingbench -s

Validating times in the archive...

Scanning file headers for version and platform info...


Parsing file rac12node1_iostat_15.12.16.1000.dat ...


Parsing file rac12node1_vmstat_15.12.16.1000.dat ...


Parsing file rac12node1_netstat_15.12.16.1000.dat ...


Parsing file rac12node1_top_15.12.16.1000.dat ...


Parsing file rac12node1_ps_15.12.16.1000.dat ...


A new analysis file analysis/rac12node1_1450262374129.txt has been created.
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Run_Queue.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Block_Queue.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Cpu_Idle.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Cpu_System.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Cpu_User.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Cpu_Wa.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Cpu_Interrupts.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Context_Switches.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Memory_Swap.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Memory_Free.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_Memory_Page_In_Rate.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_ST.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_RPS.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_WPS.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_PB.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_PBTP_1.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_PBTP_2.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_PBTP_3.gif
Generating file profile/rac12node1_swingbench/OSW_profile_files/OSWg_OS_IO_TPS.gif
[oracle@rac12node1 oswbb]$ 

In this example I asked the analyser tool to limit the search to specific time ranges and to create a “profile”. Refer to the MOS note about the OSWatcher Analyser for more information about the command line options.

The analyser will create a HTML “Profile” which is then stored in the profile directory as you can see in the last part of the output. If you transfer this to a system with a web-browser you can enjoy the graphical representation of the raw data. Very neat if you want to check on the O/S level if anything unusual might have happened.

Note also how there was a new analysis file created-have a look at it as it can provide very valuable information. In my example the following contents was recorded:

############################################################################
# Contents Of This Report:
#
# Section 1: System Status
# Section 2: System Slowdowns
#   Section 2.1: System Slowdown RCA Process Level Ordered By Impact
# Section 3: System General Findings
# Section 4: CPU Detailed Findings
#   Section 4.1: CPU Run Queue:
#   Section 4.2: CPU Utilization: Percent Busy
#   Section 4.3: CPU Utilization: Percent Sys
# Section 5: Memory Detailed Findings
#   Section 5.1: Memory: Process Swap Queue
#   Section 5.2: Memory: Scan Rate
#   Section 5.3  Memory: Page In:
#   Section 5.4  Memory: Page Tables (Linux only):
#   Section 5.5: Top 5 Memory Consuming Processes Beginning
#   Section 5.6: Top 5 Memory Consuming Processes Ending
# Section 6: Disk Detailed Findings
#   Section 6.1: Disk Percent Utilization Findings
#   Section 6.2: Disk Service Times Findings
#   Section 6.3: Disk Wait Queue Times Findings
#   Section 6.4: Disk Throughput Findings
#   Section 6.5: Disk Reads Per Second
#   Section 6.6: Disk Writes Per Second
#   Section 6.7: Disk Percent CPU waiting on I/O
# Section 7: Network Detailed Findings
#   Section 7.1  Network Data Link Findings
#   Section 7.2: Network IP Findings
#   Section 7.3: Network UDP Findings
#   Section 7.4: Network TCP Findings
# Section 8: Process Detailed Findings
#   Section 8.1: PS Process Summary Ordered By Time
#   Section 8.2: PS for Processes With Status = D or T Ordered By Time
#   Section 8.3: PS for (Processes with CPU > 0) When System Idle CPU < 30% Ordered By Time
#   Section 8.4: Top VSZ Processes Increasing Memory Per Snapshot
#   Section 8.5: Top RSS Processes Increasing Memory Per Snapshot
#
############################################################################

Summary

OSWatcher is a great tool that can be used on Oracle Linux 7 and other supported platforms. It provides a wealth of information both in textual as well as graphical format. It is invaluable in many situations where you need to retrospectively have a look at the state of the O/S in a given period and records lots of useful information.

As always, read the documentation found on MOS and make sure you understand the implications of using the tool. Also make sure you test it thoroughly first. Please ensure that you have sufficient disk space for your archive directory on a mount point that cannot affect the availability of your system.

Posted in Oracle | Tagged: | 3 Comments »

Little things worth knowing: auto-DOP or hint-which one takes precedence?

Posted by Martin Bach on June 2, 2016

This is just another short post about one of the little things worth knowing. Assume you are on 12.1.0.2 for example and you want to know if parallel_degree_limit reliably keeps the degree of parallelism at bay, for all sessions and queries across the board. After all, every machine has finite CPU (and other) resources that shouldn’t be exhausted. Setting parallel_degree_limit of course only has an effect if you set parallel_degree_policy to limited, auto or adaptive. The latter is the Big Knob to enable Automatic Degree Of Parallelism (DOP), and the post assumes that Auto DOP is enabled for the session. In this configuration, what happens to hinted statements?

Documentation

The documentation (12c Reference Guide, section on parallel_degree_limit) states that you can provide an integer in addition to “CPU” and “IO”:

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to ADAPTIVE, AUTO, or LIMITED.

Without further context this may sound a bit ambiguous-does enabling Auto DOP plus setting parallel_degree_limit to 8 ensure no query can exceed that DOP? And does that setting actually stop users from going over and above that value?

Testing

I am using a 12.1.0.2 RAC database for the test. I would like to limit the maximum DOP of a given statement to 8. Here are my settings for my lab system (emphasis on lab):

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      8
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     TRUE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     128
parallel_min_percent                 integer     0
parallel_min_servers                 integer     16
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     128
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

Now I need a table to query – after looking around in my demo-schema I found T1 which looks like a good candidate.

SQL> select table_name,num_rows,partitioned,compression,degree from tabs where table_name = 'T1';

TABLE_NAME                       NUM_ROWS PAR COMPRESS DEGREE
------------------------------ ---------- --- -------- ----------
T1                               32000000 NO  DISABLED          1

Unlike when setting parallel_degree_policy to limited I don’t need to worry about decorating the table with a default DOP. With parallel_degree_policy set to auto a parallel plan can be chosen even with the DOP on the table set to 1, provided the optimiser reckons that statement execution exceeds parallel_min_time_threshold. In my case I left its value at its default, which is 10 seconds. When querying the table I do not have to specify a hint to enable PX, or define PX on the session level as you can see in this example:

SQL> select count(*) from T1;

  COUNT(*)
----------
  32000000

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  byb4cbw4vy1cw, child number 1
-------------------------------------
select count(*) from T1

Plan hash value: 2755517917

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |       |   200K(100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |                |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |                |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000       |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |                |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |                |    32M|   200K  (1)| 00:00:04 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| T1             |    32M|   200K  (1)| 00:00:04 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

The important bit here is the information in the Note section: Oracle computed the DOP to be 8 because of the degree limit. Exactly what I wanted.

Now what if I use the hint?

No changes to the setup, this is the next statement I typed in:

SQL> select /*+ parallel(32) */ count(*) from T1;


  COUNT(*)
----------
  32000000

SQL> SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9d9aha2bdd5zc, child number 0
-------------------------------------
select /*+ parallel(32) */ count(*) from T1

Plan hash value: 2755517917

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |       | 50217 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |                |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |                |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000       |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |                |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |                |    32M| 50217   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| T1             |    32M| 50217   (1)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 32 because of hint

So based on this experiment it appears as if the hint took precedence over parallel_degree_limit in a session that had Auto DOP enabled. That doesn’t mean the DBA is out of luck, other tools come to mind to limit the DOP such as Database Resource Manager (DBRM). I have written about using DBRM to limit the DOP for all users of a consumer group some time ago. If you read that post make sure you look at Randolf’s comments.

Posted in 11g Release 1, 12c Release 1, Linux | Tagged: | Leave a Comment »