Martins Blog

Trying to explain complex things in simple terms

Archive for July, 2014

Upgrading clustered Grid Infrastructure to 12.1.0.2 from 12.1.0.1.3

Posted by Martin Bach on July 29, 2014

Oracle 12.1.0.2 is out, after lots of announcements the product has finally been released. I had just extended my 12.1.0.1.3 cluster to 3 nodes and was about to apply the July PSU when I saw the news. So why not try and upgrade to the brand new thing?

What struck me at first was the list of new features … Oracle’s patching strategy has really changed over time. I remember the days when Oracle didn’t usually add additional features into point releases. Have a look at the new 12.1.0.2 features and that would possibly qualify to be 12c Release 2…

In summary the upgrade process is actually remarkably simple, and hasn’t changed much since earlier versions of the software. Here are the steps in chronological order.

./runInstaller

I don’t know how often I have type ./ruinInstaller instead of runInstaller, but here you go. This is the first wizard screen after splash screen has disappeared.

GI 12.1.0.2-001

Naturally I went for the upgrade of my cluster. Before launching the installer though I made sure that everything was in working order by means of cluvfy. On to the next screen:

GI 002

always install English only. Troubleshooting Oracle in a different language (especially if I don’t speak or understand) is really hard so I avoid it in the first place.

Over to the screen that follows and oops-my SYSDG disk group (containing OCR and voting files) is too small. Bugger. In the end I added 3 new 10GB LUNs and dropped the old ones. But it took me a couple of hours to do so. Worse: it wasn’t even needed, but proved to be a good learning exercise. The requirement to have that much free space is most likely caused by the management repository and related infrastructure.

GI 003 error

Back to this screen everything is in best order, the print screen has been taken just prior to the change to the next. Note the button to skip the updates on unreachable nodes. Not sure if I wanted to do that though.

GI 003

I haven’t got OEM agents on the servers (yet) so I’m skipping the registration for now. You can always do that later.

GI 004

This screen is familiar; I am keeping my choices from the initial installation. Grid Infrastructure is owned by Oracle despite the ASMDBA and ASMADMIN groups by the way.

GI 005

On the screen below you define where on the file system you want to install Grid Infrastructure. Remember that for clustered deployments the ORACLE_HOME cannot be in the path of the ORACLE_BASE. For this to work you have to jump to the command line and create the directory on all servers and grant ownership to the GI owner account (oracle in this case, could be grid as well).

GI 006

Since I like to be in control I don’t allow Oracle to run the root scripts. I didn’t in 12.1.0.1 either:

GI 007

In that screen you notice the familiar checking of requirements.

GI 008

In my case there were only a few new ones shown here. This is a lab server so I don’t plan on using swap, but the kernel parameter “panic_on_oops” is new. I also didn’t set the reverse path filtering which I corrected before continuing. Interestingly the installer points out that there is a change in the asm_diskstring with its implications.

One thing I haven’t recorded here (because I am using Oracle Linux 6.5 with UEK3) is the requirement for using a 2.6.39 kernel – that sounds like UEK2 to me.

Update: my system is Oracle Linux 6.5, not Red Hat. See Sigrid’s comments below: for Red Hat Linux there doesn’t seem to be a similar requirement to use UEK 2, which matches the documentation (Installation guide for Grid Infrastructure/Linux).

Another interesting case was that the kernel_core pattern wasn’t equal on all nodes. Turned out that 2 nodes had the package abrt installed, and the other two didn’t. Once the packages were installed on all nodes, the warning went away.

GI 009

Unfortunately I didn’t take a print screen of the summary in case you wonder where that is. I went straight into the installation phase:

GI 010

At the end of which you are prompted to run the upgrade scripts. Remember to run them in screen and pay attention to the order you run them in.

GI 011

The output from the last node is shown here:

[root@rac12node3 ~]# /u01/app/12.1.0.2/grid/rootupgrade.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.1.0.2/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0.2/grid/crs/install/crsconfig_params
2014/07/26 16:15:51 CLSRSC-4015: Performing install or upgrade action for Oracle
Trace File Analyzer (TFA) Collector.

2014/07/26 16:19:58 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.

2014/07/26 16:20:02 CLSRSC-464: Starting retrieval of the cluster configuration data

2014/07/26 16:20:51 CLSRSC-465: Retrieval of the cluster configuration data has successfully completed.

2014/07/26 16:20:51 CLSRSC-363: User ignored prerequisites during installation

ASM configuration upgraded in local node successfully.

2014/07/26 16:21:16 CLSRSC-466: Starting shutdown of the current Oracle Grid Infrastructure stack

2014/07/26 16:22:51 CLSRSC-467: Shutdown of the current Oracle Grid Infrastructure stack has successfully
completed.

OLR initialization - successful
2014/07/26 16:26:53 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2014/07/26 16:34:34 CLSRSC-343: Successfully started Oracle Clusterware stack

clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully taken the backup of node specific configuration in OCR.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
2014/07/26 16:35:55 CLSRSC-478: Setting Oracle Clusterware active version on the last node to be upgraded

2014/07/26 16:35:55 CLSRSC-482: Running command: '/u01/app/12.1.0.2/grid/bin/crsctl set crs activeversion'

Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the CSS.
The CSS was successfully upgraded.
Started to upgrade Oracle ASM.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Successfully upgraded the Oracle Clusterware.
Oracle Clusterware operating version was successfully set to 12.1.0.2.0
2014/07/26 16:38:51 CLSRSC-479: Successfully set Oracle Clusterware active version

2014/07/26 16:39:13 CLSRSC-476: Finishing upgrade of resource types

2014/07/26 16:39:26 CLSRSC-482: Running command: 'upgrade model  -s 12.1.0.1.0 -d 12.1.0.2.0 -p last'

2014/07/26 16:39:26 CLSRSC-477: Successfully completed upgrade of resource types

2014/07/26 16:40:17 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Did you notice that TFA has been added? Trace File Analyzer is another of these cool things to play with, it was available with 11.2.0.4 and as an add-on to 12.1.0.1.

Result!

Back to OUI to complete the upgrade. After which cluvfy performs a final check and I’m done. Prove it worked:

[oracle@rac12node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 26 17:13:02 2014

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 Real Application Clusters and Automatic Storage Management options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL>

In another post I’ll detail the upgrade for my databases. I am particularly interested about the unplug/plug way of migrating…

Posted in 12c Release 1, KVM, Linux, RAC | Tagged: , | 6 Comments »

Implications of threaded_execution = true in 12c

Posted by Martin Bach on July 21, 2014

I had an interesting discussion as part of my latest presentation at the UKOUG RAC CIA & Database Combined SIG. Part of my talk was about the implications of the new threaded execution model in Oracle.

Since “we do not use Windows” (except for gaming) I can’t compare the Windows thread model to the new 12c implementation on UNIX. There are however interesting implications when switching to the new model, some of which I’d like to demonstrate here. First of all, threaded execution is not enabled by default. With 12.1.0.1.3 on top of Oracle Restart you get the either all or a subset of the following background and auxiliary processes for a CDB:

[oracle@london ~]$ ps -eLf | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    6041     1  6041  0    1 08:56 ?        00:00:00 ora_pmon_CDB1
oracle    6043     1  6043  0    1 08:56 ?        00:00:00 ora_psp0_CDB1
oracle    6045     1  6045  2    1 08:56 ?        00:00:08 ora_vktm_CDB1
oracle    6049     1  6049  0    1 08:56 ?        00:00:00 ora_gen0_CDB1
oracle    6051     1  6051  0    1 08:56 ?        00:00:00 ora_mman_CDB1
oracle    6055     1  6055  0    1 08:56 ?        00:00:00 ora_diag_CDB1
oracle    6057     1  6057  0    1 08:56 ?        00:00:00 ora_dbrm_CDB1
oracle    6059     1  6059  0    1 08:56 ?        00:00:00 ora_dia0_CDB1
oracle    6061     1  6061  0    1 08:56 ?        00:00:00 ora_dbw0_CDB1
oracle    6063     1  6063  0    1 08:56 ?        00:00:00 ora_lgwr_CDB1
oracle    6065     1  6065  0    1 08:56 ?        00:00:00 ora_ckpt_CDB1
oracle    6067     1  6067  0    1 08:56 ?        00:00:00 ora_lg00_CDB1
oracle    6069     1  6069  0    1 08:56 ?        00:00:00 ora_lg01_CDB1
oracle    6071     1  6071  0    1 08:56 ?        00:00:00 ora_smon_CDB1
oracle    6073     1  6073  0    1 08:56 ?        00:00:00 ora_reco_CDB1
oracle    6075     1  6075  0    1 08:56 ?        00:00:00 ora_lreg_CDB1
oracle    6077     1  6077  0    1 08:56 ?        00:00:00 ora_rbal_CDB1
oracle    6079     1  6079  0    1 08:56 ?        00:00:00 ora_asmb_CDB1
oracle    6081     1  6081  0    1 08:56 ?        00:00:00 ora_mmon_CDB1
oracle    6083     1  6083  0    1 08:56 ?        00:00:00 ora_mmnl_CDB1
grid      6086     1  6086  0    1 08:56 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6087     1  6087  0    1 08:56 ?        00:00:00 ora_d000_CDB1
oracle    6089     1  6089  0    1 08:56 ?        00:00:00 ora_s000_CDB1
oracle    6092     1  6092  0    1 08:56 ?        00:00:00 ora_mark_CDB1
oracle    6094     1  6094  0    1 08:56 ?        00:00:00 ora_ocf0_CDB1
oracle    6109     1  6109  0    1 08:56 ?        00:00:00 ora_o000_CDB1
grid      6111     1  6111  0    1 08:56 ?        00:00:00 oracle+ASM_ocf0_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      6113     1  6113  0    1 08:56 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6118     1  6118  0    1 08:56 ?        00:00:00 ora_nss2_CDB1
oracle    6148     1  6148  0    1 08:56 ?        00:00:01 ora_p000_CDB1
oracle    6150     1  6150  0    1 08:56 ?        00:00:02 ora_p001_CDB1
oracle    6154     1  6154  0    1 08:56 ?        00:00:00 ora_o001_CDB1
grid      6156     1  6156  0    1 08:56 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6158     1  6158  0    1 08:56 ?        00:00:00 ora_tmon_CDB1
oracle    6160     1  6160  0    1 08:56 ?        00:00:00 ora_arc0_CDB1
oracle    6162     1  6162  0    1 08:56 ?        00:00:00 ora_arc1_CDB1
oracle    6164     1  6164  0    1 08:56 ?        00:00:00 ora_arc2_CDB1
oracle    6166     1  6166  0    1 08:56 ?        00:00:00 ora_arc3_CDB1
oracle    6169     1  6169  0    1 08:57 ?        00:00:00 ora_tt00_CDB1
oracle    6171     1  6171  0    1 08:57 ?        00:00:00 ora_smco_CDB1
oracle    6176     1  6176  0    1 08:57 ?        00:00:00 ora_aqpc_CDB1
oracle    6180     1  6180  0    1 08:57 ?        00:00:00 ora_p002_CDB1
oracle    6182     1  6182  0    1 08:57 ?        00:00:00 ora_p003_CDB1
oracle    6184     1  6184  0    1 08:57 ?        00:00:00 ora_p004_CDB1
oracle    6186     1  6186  0    1 08:57 ?        00:00:00 ora_p005_CDB1
oracle    6188     1  6188  0    1 08:57 ?        00:00:00 ora_p006_CDB1
oracle    6190     1  6190  0    1 08:57 ?        00:00:00 ora_p007_CDB1
oracle    6192     1  6192  0    1 08:57 ?        00:00:00 ora_qm02_CDB1
oracle    6194     1  6194  0    1 08:57 ?        00:00:00 ora_qm00_CDB1
oracle    6196     1  6196  0    1 08:57 ?        00:00:00 ora_q002_CDB1
oracle    6200     1  6200  0    1 08:57 ?        00:00:00 ora_q004_CDB1
oracle    6202     1  6202  0    1 08:57 ?        00:00:00 ora_q005_CDB1
oracle    6204     1  6204  0    1 08:57 ?        00:00:00 ora_q006_CDB1
oracle    6206     1  6206  0    1 08:57 ?        00:00:00 ora_q007_CDB1
oracle    6208     1  6208  0    1 08:57 ?        00:00:00 ora_q008_CDB1
oracle    6210     1  6210  0    1 08:57 ?        00:00:00 ora_q009_CDB1
oracle    6212     1  6212  0    1 08:57 ?        00:00:00 ora_q00a_CDB1
oracle    6292     1  6292  0    1 08:57 ?        00:00:00 ora_w000_CDB1
oracle    6318     1  6318  0    1 08:57 ?        00:00:00 ora_cjq0_CDB1
oracle    6320     1  6320  0    1 08:57 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6322     1  6322  0    1 08:57 ?        00:00:01 oracleCDB1 (LOCAL=NO)
oracle    6324     1  6324  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6326     1  6326  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6407     1  6407  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6438     1  6438  0    1 08:57 ?        00:00:00 ora_p008_CDB1
oracle    6440     1  6440  0    1 08:57 ?        00:00:00 ora_p009_CDB1
oracle    6442     1  6442  0    1 08:57 ?        00:00:01 oracleCDB1 (LOCAL=NO)
oracle    6490     1  6490  0    1 08:58 ?        00:00:00 ora_p00a_CDB1
oracle    6492     1  6492  0    1 08:58 ?        00:00:00 ora_p00b_CDB1
oracle    6640  5653  6640  0    1 09:01 pts/0    00:00:00 egrep -i UID|CDB1

Quite a few more than in 9i it seems. Most of the background processes are documented in the database reference. As you can see, there are only processes (-> NLWP = 1), no threads. If you wondered about the strange output, bear with me, there is a reason I chose those flags to the ps command.

The change with the threaded model

Let’s enable threaded execution. It’s simple:

SYS@CDB$ROOT> alter system set threaded_execution=true scope=spfile;

System altered.

SYS@CDB$ROOT> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@CDB$ROOT> startup
ORA-01017: invalid username/password; logon denied

There’s the first surprise. Not to worry, it’s just that OS level authentication doesn’t work anymore, but you can continue by providing your username and password:

SYS@CDB$ROOT> conn sys as sysdba
Enter password:
Connected.
SYS@CDB$ROOT> alter database mount;

Database altered.

SYS@CDB$ROOT> alter database open;

Database altered.

SYS@CDB$ROOT>

What does that mean for our processes? First let’s use the old way of checking for Oracle processes using just ps -ef:

[oracle@london ~]$ ps -ef | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    7138     1  0 09:04 ?        00:00:00 ora_pmon_CDB1
oracle    7140     1  0 09:04 ?        00:00:00 ora_psp0_CDB1
oracle    7142     1  2 09:04 ?        00:00:05 ora_vktm_CDB1
oracle    7146     1  0 09:04 ?        00:00:01 ora_u004_CDB1
oracle    7152     1  4 09:04 ?        00:00:08 ora_u005_CDB1
oracle    7158     1  0 09:04 ?        00:00:00 ora_dbw0_CDB1
grid      7173     1  0 09:04 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7202     1  0 09:04 ?        00:00:00 oracle+ASM_ocf0_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7226     1  0 09:04 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7229     1  0 09:04 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7401     1  0 09:06 ?        00:00:00 oracle+ASM_o002_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7407     1  0 09:06 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7494     1  0 09:06 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7526     1  0 09:07 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7573     1  1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7575     1  3 09:07 ?        00:00:02 oracleCDB1 (LOCAL=NO)
oracle    7578     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7592     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7594     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7596     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7691  5653  0 09:08 pts/0    00:00:00 egrep -i UID|CDB1
[oracle@london ~]$

The list is considerably shorter, and you will notice some strange process names: u004 and u005. If you squint you see that PMON, PSP0, VKTM and DBW0 are still processes. I am checking for threads (or light weight processes as they are also called) in the next step:

[oracle@london ~]$ ps -eLf | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    7138     1  7138  0    1 09:04 ?        00:00:00 ora_pmon_CDB1
oracle    7140     1  7140  0    1 09:04 ?        00:00:00 ora_psp0_CDB1
oracle    7142     1  7142  2    1 09:04 ?        00:00:11 ora_vktm_CDB1
oracle    7146     1  7146  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7147  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7148  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7149  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7155  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7159  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7160  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7161  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7162  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7163  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7165  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7166  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7167  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7152     1  7152  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7153  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7154  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7156  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7164  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7168  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7169  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7170  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7172  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7174  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7175  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7224  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7227  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7249  0   46 09:05 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7361  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7362  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7365  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7366  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7367  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7396  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7397  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7404  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7405  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7416  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7418  0   46 09:06 ?        00:00:01 ora_u005_CDB1
oracle    7152     1  7419  0   46 09:06 ?        00:00:02 ora_u005_CDB1
oracle    7152     1  7420  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7421  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7422  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7423  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7424  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7425  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7426  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7427  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7428  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7430  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7431  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7432  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7433  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7434  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7435  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7576  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7618  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7619  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7696  0   46 09:08 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7697  0   46 09:08 ?        00:00:00 ora_u005_CDB1
oracle    7158     1  7158  0    1 09:04 ?        00:00:00 ora_dbw0_CDB1
grid      7173     1  7173  0    1 09:04 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7226     1  7226  0    1 09:04 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7229     1  7229  0    1 09:04 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7401     1  7401  0    1 09:06 ?        00:00:00 oracle+ASM_o002_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7494     1  7494  0    1 09:06 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7526     1  7526  0    1 09:07 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7573     1  7573  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7575     1  7575  1    1 09:07 ?        00:00:02 oracleCDB1 (LOCAL=NO)
oracle    7578     1  7578  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7592     1  7592  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7594     1  7594  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7596     1  7596  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7810     1  7810  0    1 09:10 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7840  5653  7840  0    1 09:11 pts/0    00:00:00 egrep -i UID|CDB1

This is where it’s interesting. First let’s match this to the database, focusing on the background processes.

SYS@CDB$ROOT> select pname, pid, sosid, spid, stid, execution_type
  2  from v$process where background = 1
  3  order by pname
  4  /

PNAME        PID SOSID                    SPID                     STID                     EXECUTION_
----- ---------- ------------------------ ------------------------ ------------------------ ----------
AQPC          43 7152_7416                7152                     7416                     THREAD
ARC0          35 7152_7362                7152                     7362                     THREAD
ARC1          36 7152_7365                7152                     7365                     THREAD
ARC2          37 7152_7366                7152                     7366                     THREAD
ARC3          38 7152_7367                7152                     7367                     THREAD
ASMB          22 7146_7167                7146                     7167                     THREAD
CJQ0          68 7152_7576                7152                     7576                     THREAD
CKPT          15 7146_7160                7146                     7160                     THREAD
DBRM          11 7146_7155                7146                     7155                     THREAD
DBW0          13 7158                     7158                     7158                     PROCESS
DIA0          12 7152_7156                7152                     7156                     THREAD
DIAG           9 7152_7154                7152                     7154                     THREAD
GEN0           5 7146_7148                7146                     7148                     THREAD
LG00          16 7146_7161                7146                     7161                     THREAD
LG01          18 7146_7163                7146                     7163                     THREAD
LGWR          14 7146_7159                7146                     7159                     THREAD
LREG          20 7146_7165                7146                     7165                     THREAD
MARK          28 7152_7175                7152                     7175                     THREAD
MMAN           7 7146_7149                7146                     7149                     THREAD
MMNL          24 7152_7169                7152                     7169                     THREAD
MMON          23 7152_7168                7152                     7168                     THREAD
NSS2          33 7152_7249                7152                     7249                     THREAD
O002          40 7152_7397                7152                     7397                     THREAD
PMON           2 7138                     7138                     7138                     PROCESS
PSP0           3 7140                     7140                     7140                     PROCESS
Q002          54 7152_7428                7152                     7428                     THREAD
Q004          56 7152_7430                7152                     7430                     THREAD
Q005          57 7152_7431                7152                     7431                     THREAD
Q006          58 7152_7432                7152                     7432                     THREAD
Q007          59 7152_7433                7152                     7433                     THREAD
Q008          60 7152_7434                7152                     7434                     THREAD
Q009          61 7152_7435                7152                     7435                     THREAD
QM00          53 7152_7427                7152                     7427                     THREAD
QM02          44 7152_7426                7152                     7426                     THREAD
RBAL          21 7146_7166                7146                     7166                     THREAD
RECO          19 7152_7164                7152                     7164                     THREAD
SCMN           6 7146_7146                7146                     7146                     THREAD
SCMN          10 7152_7152                7152                     7152                     THREAD
SMCO          41 7152_7404                7152                     7404                     THREAD
SMON          17 7146_7162                7146                     7162                     THREAD
TMON          34 7152_7361                7152                     7361                     THREAD
TT00          39 7152_7396                7152                     7396                     THREAD
VKTM           4 7142                     7142                     7142                     PROCESS
W000          42 7152_7405                7152                     7405                     THREAD

44 rows selected.

Here you clearly see which one of the background processes is a thread, and which isn’t. The threads will have multiple STIDs per SPID, or thread IDs to the process:

SYS@CDB$ROOT> select count(spid),spid,execution_type from v$process where background = 1 group by spid, execution_type;

COUNT(SPID) SPID                     EXECUTION_
----------- ------------------------ ----------
          1 7158                     PROCESS
         12 7146                     THREAD
          1 7138                     PROCESS
          1 7140                     PROCESS
          1 7142                     PROCESS
         29 7152                     THREAD

6 rows selected.

Circling back to the OS level for SPID 7146-can you see the threads there as well? You can:

[oracle@london ~]$ ps -eLf | egrep -i "UID|7146"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    7146     1  7146  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7147  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7148  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7149  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7155  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7159  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7160  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7161  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7162  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7163  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7165  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7166  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7167  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    8156  6876  8156  0    1 09:19 pts/2    00:00:00 egrep -i UID|7146
[oracle@london ~]$

But what about user sessions?

Let’s connect to the database (a PDB in this case) as a user.

[oracle@london ~]$ sqlplus martin@localhost/pdb4
...
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 09:22:39 2014

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

Last Successful login time: Fri Jul 18 2014 09:21:27 -04:00

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

MARTIN@PDB4> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
280
MARTIN@PDB4>

I can see the session from the CDB root:

SYS@CDB$ROOT> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 280 and s.paddr = p.addr;

USERNAME          SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
---------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN            280        407          4 8672                     8672                     8672                     PROCESS

SYS@CDB$ROOT>

Interestingly – as the output shows – user processes seem to remain UNIX processes (there is a twist to that too, bear with me). Remember that this process has been created using a net service and the EZConnect syntax. What about my session that bypasses the net*8 layer? I connected using sqlplus sys as sysdba:

SYS@CDB$ROOT> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
------------------------------------------------------------------------------------------------------------------------------------------------------
249

SYS@CDB$ROOT> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 249 and s.paddr = p.addr;

USERNAME          SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
---------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
SYS               249        685          1 7152                     7152_8696                8696                     THREAD

SYS@CDB$ROOT>

Now that’s a thread. Surprised? Let’s test on a non CDB. First I try to connect by bypassing the listener:

[oracle@london ~]$ sqlplus martin

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:34:58 2014

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

Enter password:
Last Successful login time: Fri Jul 18 2014 10:33:25 -04:00

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

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
249

MARTIN@NCDB>
-- in a different session
SYS@NCDB> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 249 and s.paddr = p.addr;

USERNAME                              SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
------------------------------ ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                                249         13          0 14634                    14634_15337              15337                    THREAD

This session is a thread. Now using the listener:

[oracle@london ~]$ tnsping ncdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 18-JUL-2014 10:36:33

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = london.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NCDB)))
OK (0 msec)
[oracle@london ~]$ sqlplus martin@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:36:36 2014

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

Enter password:
Last Successful login time: Fri Jul 18 2014 10:36:18 -04:00

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

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
249

And what’s the guess?

SYS@NCDB> r
  1  select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3* where s.sid = 249 and s.paddr = p.addr

USERNAME                              SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
------------------------------ ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                                249         17          0 15507                    15507                    15507                    PROCESS

You were right-a process. But so far I haven’t shown you my listener.ora file. So far in the testing it didn’t use DEDICATED_THROUGH_BROKER_listener = ON. Let’s set this, and reload the listener. Here is the complete example:

[oracle@london ~]$ sqlplus martin@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:45:18 2014

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

Enter password:
Last Successful login time: Fri Jul 18 2014 10:36:38 -04:00

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

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
14

-- and in another session

SYS@NCDB> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 14 and s.paddr = p.addr;

USERNAME               SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
--------------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                  14         25          0 14634                    14634_16287              16287                    THREAD

The listener parameter DEDICATED_THROUGH_BROKER_ allows you to create user sessions as threads. And now to wrap up, how do you kill that session? On my Oracle Linux 6.5 system I killed the LWP. THIS IS A VERY BAD IDEA as it turns out.

Here is why. I connected again as a regular user to the database, and was assigned SID 258. Since that user didn’t have select privileges on v$session and v$process I created a new session as SYS to get more information. This resulted in the following situation in v$process for the “regular” user session:

SQL> select pid,sosid,spid,stid,execution_type from v$process where addr = 
  2  (select paddr from v$session where sid = 258 );

       PID SOSID                    SPID                     STID                     EXECUTION_
---------- ------------------------ ------------------------ ------------------------ ----------
        51 21584_21662              21584                    21662                    THREAD

On the O/S side I can see the parent with all the threads:

[oracle@server3 ~]$ ps -eLf | egrep "21584|21662"
oracle   21584     1 21584  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21585  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21586  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21587  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21588  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21589  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21590  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21591  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21592  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21593  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21594  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21595  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21596  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21597  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21598  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21599  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21600  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21601  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21602  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21603  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21605  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21606  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21607  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21608  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21609  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21610  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21613  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21614  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21615  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21618  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21619  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21627  0   34 07:01 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21662  0   34 07:03 ?        00:00:00 ora_u005_NCDB
oracle   21584     1 21666  0   34 07:03 ?        00:00:00 ora_u005_NCDB

My session, a thread, is in the second to last line of the above output. Now I tried to be smart (you don’t try this please, because it isn’t!) and removed the STID from the OS using the familiar kill command.

[oracle@server3 ~]$ kill -9 21662
[oracle@server3 ~]$ ps -eLf | egrep "21584|21662"
oracle   21788 21675 21788  0    1 07:08 pts/16   00:00:00 grep -E --color=auto 21584|21662
[oracle@server3 ~]$

Oops. That removed the process. And all of the sessions. Not the way to make friends!

SQL> r
  1*  select pid,sosid,spid,stid,execution_type from v$process where addr = (select paddr from v$session where sid = &1 )
Enter value for 1: 258
old   1:  select pid,sosid,spid,stid,execution_type from v$process where addr = (select paddr from v$session where sid = &1 )
new   1:  select pid,sosid,spid,stid,execution_type from v$process where addr = (select paddr from v$session where sid = 258 )
 select pid,sosid,spid,stid,execution_type from v$process where addr = (select paddr from v$session where sid = 258 )
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 21584 Thread ID: 21666
Session ID: 16 Serial number: 31927

So it would appear that you cannot kill individual threads within a process with the kill commands without taking every other user with you.

Disclaimer

As with everything on this blog, don’t take it literally-test it! Just because it worked for me on my VM with the specific set of packages does not mean this is applicable for other Linux or even Solaris/AIX!

Posted in 12c Release 1, Linux | Tagged: , | 4 Comments »

Setting up Direct NFS on Oracle 12c

Posted by Martin Bach on July 9, 2014

Direct NFS is a great feature that I have finally had the time to investigate further. Since I always forget how to set it up and I didn’t find blog posts about this subject elsewhere I decided to put something together.

In this configuration I am using a virtual machine named server1 to export a directory to server2. Oracle is not as lenient as myself and may have certain support requirements when it comes to dNFS servers but I just wanted to get started. The VMs are running Oracle Linux 6.

The export of the NFS mount is shown here:

[root@server1 ~]# cat /etc/exports
/u01/oraback	*.example.com(rw,sync,all_squash,insecure,anonuid=54321,anongid=54321)

There is nothing too special about the export definition here. The all_squash directive normally uses a uid and gid of 65534. Since this is most likely not matching the oracle user I chose to override this behaviour. You may have already guessed that I am using the oracle preinstall RPMs which use 54321 for the oracle account and oinstall group respectively. The “all_squash” directive maps all uids and gids to the anonymous user by default.

Oracle also recommends tweaking some network related parameters in /etc/sysctl.conf. They appear to have to do with better performance, but I didn’t have time to verify that claim yet. On the other hand, it seems that from kernel 2.6 onwards Linux tunes the send and receive buffers automatically, and it’s probably a good idea not trying to outsmart it.

Using “service nfs start” I started the NFS server process on my Oracle Linux 6.4 system. I had appropriate firewall rules in place, if you have a firewall then you might need to do the same.

Mounting

Mounting the file system on the second node requires a setting in the fstab file to begin with. In this example I want to mount the exported backups in /media/backups on server2.

The corresponding entry in fstab was:

...
server1:/u01/oraback	/media/backup	nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600	1 2

I kept the habit of enforcing NFSv3 even though 4 and 4.1 are now supported with 12c. With the fstab entry I can now mount the directory:

[root@server2 ~]# mount /media/backup
[root@server2 ~]# mount | grep backup
server1:/u01/oraback on /media/backup type nfs (rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600,addr=192.168.56.44)

So far kernel NFS has been used, but not direct NFS. To do so, you need to create a mapping file named oranfstab. The file resides in $ORACLE_HOME/dbs. In this file you define how you get to the NFS filer. You can define up to 4 paths if you have multiple NICs in your database server. My VM does not so the most basic setup is used:

[root@server2 ~]# cat /u01/app/oracle/product/12.1.0.1/dbhome_1/dbs/oranfstab
server: server1
local: 192.168.56.45
path:  192.168.56.44
export: /u01/oraback mount: /media/backup
export: /m/CLONE mount: /u01/oradata/CLONE
[root@server2 ~]#

The “server” directive gives the NFS filer a name. Multiple sections are introduced using the “server” keyword.

Following the server keyword you define how you get the data: the “local” IP address shown here is the IP of server2. The “path” keyword indicates the path to the NFS filer, server1 or 192.168.56.44. And finally you tell Oracle the name of the export on the filer (/u01/oraback) and where it is mounted locally (/media/backup).

The last step necessary is to enable dNFS:

[oracle@server2 lib]$ make -f ins_rdbms.mk dnfs_on
rm -f /u01/app/oracle/product/12.1.0.1/dbhome_1/lib/libodm12.so;
cp /u01/app/oracle/product/12.1.0.1/dbhome_1/lib/libnfsodm12.so
 /u01/app/oracle/product/12.1.0.1/dbhome_1/lib/libodm12.so

All done! After the next start of your database you should see the a reference to the ODM library in the alert.log similar to this one:

...
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Sun Nov 17 14:30:41 2013
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
Starting background process PMON
Starting background process PSP0
...

Hopefully you will now be able to query the metadata views too.

SQL> select * from v$dnfs_servers

        ID SVRNAME    DIRNAME                 MNTPORT    NFSPORT NFSVERSI      WTMAX      RTMAX     CON_ID
---------- ---------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
         1 server1    /m/clone                  52690       2049 NFSv3.0     1048576    1048576          0

That’s all there is to say about dNFS for this post. Oh and if at first the query against v$dnfs_servers does not return anything, it doesn’t necessarily imply a problem. I just created a file on the mount point and – as if by magic – dNFS kicked in, opened the channel and opened the file descriptors.

Update

I tried my own steps for Oracle Linux 7 but those failed! Despite using dnfs_on etc I couldn’t add a new tablespace on dnfs. Whatever I tried I always ended up with this error:

ERROR at line 1:
ORA-01119: error in creating database file '/oranfs/test1.dbf'
ORA-17502: ksfdcre:7 Failed to create file /oranfs/test1.dbf
ORA-17500: ODM err:Operation not permitted

After a lot of digging around I found that the “insecure” option that was touted as the solution to this exact problem in MOS note “DNFS (Direct NFS) 12c Database Cannot Be Mounted Due To “ORA-17500: ODM err:Operation not permitted” (Doc ID 1966267.1)”

For the record, the documentation for NFS on RHEL 7 is here:

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Storage_Administration_Guide/ch-nfs.html

References

  • MOS Doc ID 1464567.1: Collecting The Required Information For Support To Troubleshot DNFS (Direct NFS) Issues (11.1, 11.2 & 12c)
  • Step by Step – Configure Direct NFS Client (DNFS) on Linux (11g) (Doc ID 762374.1)

Posted in 12c Release 1, Linux | 5 Comments »

Why is P1 the only parameter populated in cell smart table scan?

Posted by Martin Bach on July 7, 2014

Anyone who has looked at Exadata might ask the question, and I did so too. After all, cell smart table scan is in wait class User IO so there should be more, right? This is what you find for a smart scan:

NAME                           PARAMETER1           PARAMETER2           PARAMETER3                     WAIT_CLASS
------------------------------ -------------------- -------------------- ------------------------------ ---------------
cell smart table scan          cellhash#                                                                User I/O
cell smart index scan          cellhash#                                                                User I/O

Compare this to the traditional IO request:

NAME                           PARAMETER1           PARAMETER2           PARAMETER3                     WAIT_CLASS
------------------------------ -------------------- -------------------- ------------------------------ ---------------
db file sequential read        file#                block#               blocks                         User I/O
db file scattered read         file#                block#               blocks                         User I/O
direct path read               file number          first dba            block cnt                      User I/O

At first I didn’t understand as to why that was the. Until tracing a lot more. Using the method described by my colleague Frits Hoogland I traced Exadata calls as well as the IB interconnect in addition to a SQL trace (level 8). Please don’t do this outside the lab, the trace will get huge!

Now that produces an interesting file, and the SQL trace information is only 1 intermittent line drowning in a sea of other information. So I used a sed oneliner to make them stand out a bit:

$ sed  "/WAIT/{x;p;x;G;}" db12c1_ora_11916.trc > kxd_libcell_sqltace.trc

This introduces a newline before and after each line containing WAIT. The SQL Trace information is familiar:

WAIT #139970814085680: nam='cell smart table scan' ela= 251 cellhash#=822451848 p2=0 p3=0 obj#=61471 tim=3470911250866

By the way the obj# is the data_object_ID, not the object_id in DBA_OBJECTS.

Stepping through the trace

The trace information reflects what is happening during the various smart scan processing steps. This first example shows you some of the processing during the smart scan initialisation phase, when the cells are being initialised. I initially struggled mapping the “appliance” hexadecimal number to an actual cell. But it’s easy if you grep for kcfis_open_appliance_fd, and match the cellhash to v$cell.cell_hashval. Continuing the example:

kcfis_open_appliance:
throttle: initialized for appliance 0x7f4d7e9bc4c0
kcfis_find_appliance_fd: appliance fd not found. appliance 192.168.12.10/ Cached appliance fd count 0
kcfis_open_appliance_fd: 0x7f4d7e9bc4c0
oss_open called for o/192.168.12.10//box/predicate
Sending message RQ_Tag_2827385864_11: 0xf9b3f08 with frag # 0, to box my_box_refid: 2 (box inc: 12)
Command with band 1, payload length 116, payload 0xf9b4110
RQ_Tag_2827385864_11: Command name OPEN, refid 11
Open flags: readwrite - 1, sync - 0, char - 1.
 Device name - /box/predicate
Vrpc completion - RQ_Tag_2827385864_11: cmd 0xf9b3f48, req 0xf9b3a40, frag # 1
Vrpc reply 0xf9b4000 with refid 11 for existing message: 0xf9b3f08
Reply with band 0, payload length 0, payload (nil)
RQ_Tag_2827385864_11: Command name REPLY OPEN, refid 11
Returned open file descriptor  1
opening file number 11

WAIT #139970814085680: nam='cell smart table scan' ela= 259 cellhash#=822451848 p2=0 p3=0 obj#=61471 tim=3470911167788

So here the cell with IP 192.168.12.10 maps to 0x7f4d7e9bc4c0 in the trace. As you can see, immediately before the wait event is written to the trace file a file is opened on the cell. This causes the line to be emitted in the SQL trace file. So far there hasn’t been any significant scanning activity against the data. But the trace line makes sense: the IO request was issued against the cell with hash 822451848. We don’t know what it did though, and which file it accessed.

SYS:db12c1> select cell_path from v$cell where cell_hashval = '822451848';

CELL_PATH
----------------------------
192.168.12.10

I was initially quite pleased with myself initially when I found this out.

More trace information. A lot more.

Lesson learned trawling through the trace: just because there is a single line in the trace file doesn’t mean there aren’t other requests either in flight or under preparation.

The next few thousand (!) lines show you the mapping between RDBMS and cell addressing. This part is well explained in Frits’ post I referenced earlier. Further down in the trace it gets more interesting from a SQL trace perspective again. Here payload maps are generated and issued against the cells. Remember that these IO requests to cells are performed asynchronously. I found quite a few calls to the code generating payloads for the cells between individual SQL Trace WAIT lines. This makes reading the trace a bit confusing, it almost appears as if the SQL Trace couldn’t keep up with the speed the requests are created/sent.

Looking at the file I found the following pattern preceding a smart scan line in the first half of the process (before data comes back from the cells). Beginning immediately after the last WAIT line, in abbreviated format:

Ioctl completed.
appliance 0x7f4d7e9bc4c0 : cellsrv pid: 9563: predicate: /box/predicate37843
...
kcfis_create_maps_payload. appliance 0x7f4d7ea65570 num_disks 12
throttle: allowing map push: appliance 0x7f4d7ea65570, maps_to_push 7
...
Pushing request : disknumber = 0, offset = 936606973952, len = 1032192 rdba: 43778306 version 0 reqid=10 cver=1
disk=0x7f4d7e9c0aa8 state=1
trans_req_element = 0x7f4d7e9cba00

The appliance name, cellsrv (thread) ID, and the box predicate appear to remain constant for the lifetime of the smart scan. You can find them in other occurrences of the trace as well (but not used in the same context).

The preceding lines are then followed by many more lines beginning with “Pushing request”, iterating over all 12 disks (-> num_disks), for a range of Data Block Addresses. After that map payload has been created it is time to push it to the cell. Here it gets a bit murky: the KXD.* traces show requests being pushed, but not necessarily to the cell mentioned in the event line. Consider this:

kcfis_create_maps_payload. alloc_len 272 num maps 2
throttle: mappayload: maps_to_push 4 iosize_being_pushed 8388608
kcfis_push: pushing maps to appliance 0x7f4d7e9bc4c0
kcfis_issue_ioctl: payload_type 2
oss_ioctl open file: 11, opcode: 99
Sending message RQ_Tag_2827385864_54: 0xf9c5728 with frag # 0, to box my_box_refid: 2 (box inc: 12)
Command with band 1, payload length 272, payload 0x7f4d7e9b9ef8
RQ_Tag_2827385864_54: Command name IOCTL, refid 54
Ioctl arguments fd 1 opcode 99 size 272
oss_ioctl done
oss_wait called for request: 0xf9b3a40
Vrpc completion - RQ_Tag_2341095748_52: cmd 0xf9c6ec8, req 0xf9b3a40, frag # 1
Vrpc reply 0xf9c6f80 with refid 52 for existing message: 0xf9c6e88
Reply with band 1, payload length 40, payload 0x7f4d7ea65a90
RQ_Tag_2341095748_52: Command name REPLY IOCTL, refid 52
oss_wait done, request 0xf9b3a40, result 0x7f4d837e4c48

WAIT #139970814085680: nam='cell smart table scan' ela= 44 cellhash#=3249924569 p2=0 p3=0 obj#=61471 tim=3470911270172

The kcfis_push call is addressed to appliance 0x7f4d7e9bc4c0, which maps to 192.168.12.10. The cell hash from the event (3249924569) maps to a different cell though. You can find the corresponding kcfis_push earlier in the trace. In fact, it is the first occurrence after the last WAIT event line.

trans_req_element = 0x7f4d7e870780
Pushing request : disknumber = 5, offset = 937011707904, len = 4194304 rdba: 44570624 version 0 reqid=1021 cver=1
disk=0x7f4d7e9b5980 state=1
disk=0x7f4d7e9b4968 state=1
disk=0x7f4d7e9b3460 state=1
disk=0x7f4d7e9b2f70 state=1
disk=0x7f4d7e9df5a0 state=1
disk=0x7f4d7e9df0b0 state=1
disk=0x7f4d7e9dea78 state=1
kcfis_create_maps_payload. alloc_len 2648 num maps 35
throttle: mappayload: maps_to_push 10 iosize_being_pushed 102039552
kcfis_push: pushing maps to appliance 0x7f4d7ea65570
kcfis_issue_ioctl: payload_type 2
SKGXP-connecting to the box 192.168.12.8 OSS OS Pid 4541
oss_ioctl open file: 2, opcode: 99
Sending message RQ_Tag_2341095748_52: 0xf9c6e88 with frag # 0, to box my_box_refid: 0 (box inc: 16)
Command with band 3, payload length 2648, payload 0x7f4d7ea62e98
RQ_Tag_2341095748_52: Command name IOCTL, refid 52
Ioctl arguments fd 1 opcode 99 size 2648
oss_ioctl done

This is getting too complex, can you say it again?

Now let’s put this together again, simplified:

WAIT #139970814085680: nam='cell smart table scan' ela= 24 cellhash#=822451848 p2=0 p3=0 obj#=61471 tim=3470911267234

kcfis_push: num-appliances 3. payload_size 0x7f4d837e4cf0 ioctl_issued 0x7f4d837e4cc8 results 0x7f4d837e4c48 payload_type 0x7f4d837e4d20
kcfis_create_maps_payload. appliance 0x7f4d7ea65570 num_disks 12
...
kcfis_create_maps_payload. alloc_len 2648 num maps 35
throttle: mappayload: maps_to_push 10 iosize_being_pushed 102039552
kcfis_push: pushing maps to appliance 0x7f4d7ea65570
kcfis_issue_ioctl: payload_type 2
SKGXP-connecting to the box 192.168.12.8 OSS OS Pid 4541
oss_ioctl open file: 2, opcode: 99
Sending message RQ_Tag_2341095748_52: 0xf9c6e88 with frag # 0, to box my_box_refid: 0 (box inc: 16)
Command with band 3, payload length 2648, payload 0x7f4d7ea62e98
RQ_Tag_2341095748_52: Command name IOCTL, refid 52
Ioctl arguments fd 1 opcode 99 size 2648
oss_ioctl done
...
kcfis_create_maps_payload. appliance 0x7f4d7e9bf518 num_disks 12
...
kcfis_create_maps_payload. alloc_len 1208 num maps 15
throttle: mappayload: maps_to_push 10 iosize_being_pushed 34471936
kcfis_push: pushing maps to appliance 0x7f4d7e9bf518
kcfis_issue_ioctl: payload_type 2
SKGXP-connecting to the box 192.168.12.9 OSS OS Pid 9547
...
oss_ioctl done
...
kcfis_create_maps_payload. appliance 0x7f4d7e9bc4c0 num_disks 12
...
kcfis_create_maps_payload. alloc_len 272 num maps 2
throttle: mappayload: maps_to_push 4 iosize_being_pushed 8388608
kcfis_push: pushing maps to appliance 0x7f4d7e9bc4c0
...
oss_ioctl done
...
oss_wait done, request 0xf9b3a40, result 0x7f4d837e4c48

WAIT #139970814085680: nam='cell smart table scan' ela= 44 cellhash#=3249924569 p2=0 p3=0 obj#=61471 tim=3470911270172

Interestingly the results for the requests come back in that order as well. It seems coincidence, judging by the rest of the trace. Compare the appliance values with the summary you just read:

WAIT #139970814085680: nam='cell smart table scan' ela= 44 cellhash#=3249924569 p2=0 p3=0 obj#=61471 tim=3470911270172

Ioctl completed. Payload type 2
Ioctl quarantine response 1 for appliance 0x7f4d7ea65570
appliance 0x7f4d7ea65570 : cellsrv pid: 4541: predicate: /box/predicate114
oss_wait called for request: 0xf9c69c0
Vrpc completion - RQ_Tag_2825762783_53: cmd 0xf9c6768, req 0xf9c69c0, frag # 1
Vrpc reply 0xf9c6820 with refid 53 for existing message: 0xf9c6728
Reply with band 1, payload length 40, payload 0x7f4d7e9bfa38
RQ_Tag_2825762783_53: Command name REPLY IOCTL, refid 53
oss_wait done, request 0xf9c69c0, result 0x7f4d837e4c68

WAIT #139970814085680: nam='cell smart table scan' ela= 64 cellhash#=674246789 p2=0 p3=0 obj#=61471 tim=3470911270269

Ioctl completed. Payload type 2
Ioctl quarantine response 1 for appliance 0x7f4d7e9bf518
appliance 0x7f4d7e9bf518 : cellsrv pid: 9547: predicate: /box/predicate37211
oss_wait called for request: 0xf9c5260
Vrpc completion - RQ_Tag_2827385864_54: cmd 0xf9c5768, req 0xf9c5260, frag # 1
Vrpc reply 0xf9c5820 with refid 54 for existing message: 0xf9c5728
Reply with band 1, payload length 40, payload 0x7f4d7e9bc9e0
RQ_Tag_2827385864_54: Command name REPLY IOCTL, refid 54
oss_wait done, request 0xf9c5260, result 0x7f4d837e4c88

WAIT #139970814085680: nam='cell smart table scan' ela= 54 cellhash#=822451848 p2=0 p3=0 obj#=61471 tim=3470911270351

Ioctl completed. Payload type 2
Ioctl quarantine response 1 for appliance 0x7f4d7e9bc4c0

SQL trace wait events frequently seem to be written after an IOCTL (standard Linux call) or oss_wait (Oracle) has completed.  So it appears as if requests are created and treated on a cell-by-cell basis. This explains why you get so many cell table smart scan events in general. It’s not because they are necessarily small in size, the payloads always seem to include more than 1 disk to scan. Thinking about it now it makes sense, but as an analyst I would have appreciated more insights into what happens under the covers. But we can trace, right?

Posted in 11g Release 2, 12c Release 1, Exadata | Leave a Comment »