Ronny Egners Blog

When doing performance analysis the current network throughput is often interesting. The following nice script display that – even without any root permissions.

#!/bin/sh
 # usage: netvolmon DEV [INTERVAL]
 DEV=$1
 IVAL=${2:-5}
getrxtx() {
 kstat -p "*:*:$1:*bytes64" |
 awk '{print $2}'
 }
rxtx=`getrxtx $DEV`
 while sleep $IVAL; do
 nrxtx=`getrxtx $DEV`
 (echo $IVAL $rxtx $nrxtx) |
 awk 'BEGIN {
 msg = "%6.2f MB/s RX %6.2f MB/s TX\n"}
 {rxd = ($4 - $2) / (1024*1024*$1);
 txd = ($5 - $3) / (1024*1024*$1);
 printf msg, rxd, txd}'
 rxtx="$nrxtx"
 done

The script is taken from here: http://utcc.utoronto.ca/~cks/space/blog/solaris/SolarisNetworkBandwidth

It produces the following output which is sufficient to get a quick overview about the current network traffic:

bash-3.2$ /export/home/oracle/net.sh igb0 5
  0.59 MB/s RX  20.54 MB/s TX
  1.17 MB/s RX  40.81 MB/s TX
  1.71 MB/s RX  59.72 MB/s TX

Today i had a smaller problem registering a newly created database in a rman catalog. The only unusual thing was that the database was using a unicode character set.

RMAN failed with:

 
[oracle@rac02a ~]$ rman target / catalog rman/rman@rman
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Feb 12 05:31:49 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA11 (DBID=781875003)
connected to recovery catalog database
RMAN> show all;
starting full resync of recovery catalog
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of show command at 02/12/2014 05:31:57
 RMAN-03014: implicit resync of recovery catalog failed
 RMAN-03009: failure of full resync command on default channel at 02/12/2014 05:31:57
 ORA-29275: partial multibyte character

As you can see the database was using Unicode:

 [oracle@rac02a ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 12 05:32:23 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options
SQL> select * from nls_database_parameters;
NLS_CHARACTERSET       AL32UTF8

So i changed the environment to match the character set of the database:

[oracle@rac02a ~]$ export NLS_LANG=GERMAN_GERMANY.AL32UTF8

And… Problem solved!

[oracle@rac02a ~]$ rman target / catalog rman/rman@rman
Recovery Manager: Release 11.2.0.3.0 - Production on Mi Feb 12 05:32:53 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Mit Ziel-Datenbank verbunden: ORA11 (DBID=781875003)
Verbindung mit Datenbank des Recovery-Katalogs
 RMAN> show all;
Vollständige Neusynchronisation des Recovery-Katalogs wird begonnen
 Vollständige Neusynchronisation abgeschlossen
 RMAN-Konfigurationsparameter für Datenbank mit db_unique_name ORA11 sind:
 CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
 CONFIGURE BACKUP OPTIMIZATION OFF; # default
 CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/nfs/backup/rman/ORA11/%F';
 CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COPY;
 CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
 CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
 CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/nfs/backup/rman/ORA11/%U';
 CONFIGURE MAXSETSIZE TO UNLIMITED; # default
 CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
 CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
 CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
 CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/nfs/backup/rman/ORA11/backup/snapcf_ORA11.f';

When dealing with large databases it might be neccessary to have more than 512 LUNs attached to the server. Most Linux distributions default to 512 luns max. This gets even worse with multipathing where each path is represented as a single lun.

Error messages like this indicate the limit was hit:

Dec 02 22:24:46 server kernel: scsi: host 2 channel 0 id 2 lun258 has a LUN larger than allowed by the host adapter
 Dec 02 22:24:46 server kernel: scsi: host 2 channel 0 id 2 lun259 has a LUN larger than allowed by the host adapter

So what is needed to change that limit?

First you need to modify the maximum numbers of LUNs allowed by the SCSI stack by adding the following line to /etc/modprobe.conf:

options scsi_mod max_luns=2048

Depending on the SCSI HBA used the driver module itself might need some adjustments. For QLOgic HBAs the following line ist required in /etc/modprobe.conf:

options lpfc lpfc_max_luns=2048

After rebooting or removing and re-inserting the module you should be able to attach 2048 LUNs.

By the way the linux device interface (including the multipath driver) supprts more than 254 LUNs sind Kernel 2.6:

[root@ora1 ~]# vgs 
 VG     #PV #LV #SN Attr   VSize   VFree 
 vgroot   1 388   0 wz--n- 179,88G 94,50G

As you can see the VG has 388 LVs. More than 254 … so we actually should run out of minor numbers in /dev, right? Actually major- and minor numbers were increaed to 10 bits in kernel 2.6:

[root@ora1 mapper]# ls -la /dev/mapper/vgroot-disk00*
brw-rw---- 1 root disk 253,  6 24. Dez 05:27 /dev/mapper/vgroot-disk001
brw-rw---- 1 root disk 253,  7 24. Dez 05:27 /dev/mapper/vgroot-disk002
brw-rw---- 1 root disk 253,  8 24. Dez 05:27 /dev/mapper/vgroot-disk003

brw-rw---- 1 root disk 253, 308 24. Dez 05:27 /dev/mapper/vgroot-disk306
brw-rw---- 1 root disk 253, 309 24. Dez 05:27 /dev/mapper/vgroot-disk307
brw-rw---- 1 root disk 253, 310 24. Dez 05:27 /dev/mapper/vgroot-disk308
brw-rw---- 1 root disk 253, 311 24. Dez 05:27 /dev/mapper/vgroot-disk309

Somethimes – especially on RACs – it is required to synchronize permissions on an ORACLE_HOME  running on one node with the permissions running on the other node.

Today i had the problem that after a patch all permissions were garbled. In order to restore service i had to fix them. The first idea here would be to copy the permissions from a working node and here is how you do it easy:

find /data/oragrid/product/11.2.0.3 -printf 'chown %U.%G %p\n' > /tmp/chown-home.sh
find /data/oragrid/product/11.2.0.3 -type d -printf "chmod %m %p \n" > /tmp/chmod-home.sh
find /data/oragrid/product/11.2.0.3 -type f -printf "chmod %m %p \n" > /tmp/chmod-home.sh

The scripts output for the file permissions looks like this:

chmod 775 /data/oragrid/product/11.2.0.3
chmod 755 /data/oragrid/product/11.2.0.3/patches
chmod 755 /data/oragrid/product/11.2.0.3/patches/p12989056
chmod 755 /data/oragrid/product/11.2.0.3/patches/p12989056/12989056
chmod 755 /data/oragrid/product/11.2.0.3/patches/p12989056/12989056/custom

and for the ownership:

chown 0.30275 /data/oragrid/product/11.2.0.3
chown 20341.30275 /data/oragrid/product/11.2.0.3/JRE
chown 20341.30275 /data/oragrid/product/11.2.0.3/patches
chown 20341.30275 /data/oragrid/product/11.2.0.3/patches/p12989056
chown 20341.30275 /data/oragrid/product/11.2.0.3/patches/p12989056/12989056

Just execute the scipts in any order and you are done.

Hi just discovered another flaw when having the workaround for CVE-2012-3132 in place. If you try to compile invalid database objects it will fail with:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp
SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual
 *
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_REGISTRY_SYS
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist

The solution for this is:

Either disable the trigger (alter system disable trigger sys.NAMECHECK_BEFORE_DDL_DB_TRG) OR disable the execution of all triggers on system level (ALTER SYSTEM SET "_system_trig_enabled" = FALSE;)

The query:

select
  rtime as time,
  sn.snap_id,
  ts.name,
  round(tablespace_size*tsb.block_size/1024/1024,0) SIZE_MB,
  round(tablespace_maxsize*tsb.block_size/1024/1024,0) MAXSIZE_MB,
  round(tablespace_usedsize *tsb.block_size/1024/1024,0) USEDSIZE_MB
from
  dba_hist_tbspc_space_usage tsu,
  v$tablespace ts,
  dba_hist_snapshot sn,
  dba_tablespaces tsb
where
  tsu.tablespace_id=ts.ts#
  and sn.snap_id= tsu.snap_id
  --and ts.name='USERS'
  and tsb.tablespace_name=ts.name
order by
sn.snap_id desc, tablespace_name;

Sample output;

TIME                 SNAP_ID  NAME      SIZE_MB   MAXSIZE_MB   USEDSIZE_MB
07/01/2013 11:00:30    644    SYSAUX    64000     65536        2154
07/01/2013 11:00:30    644    SYSTEM    6144      8192         2884
07/01/2013 11:00:30    644    TOOLS     100       65536        1
07/01/2013 11:00:30    644    UNDOTBS1  65535     65535        54
07/01/2013 11:00:30    644    USERS     8         8            7
07/01/2013 10:00:28    643    SYSAUX    64000     65536        2154
07/01/2013 10:00:28    643    SYSTEM    6144      8192         2884
07/01/2013 10:00:28    643    TOOLS     100       65536        1
07/01/2013 10:00:28    643    UNDOTBS1  65535     65535        58
07/01/2013 10:00:28    643    USERS     8         8            7

Oracle 12c Release 1 has been released!

Yes, the new generation of Oracles new database platform has been released. You can download it from Oracle

Now as 12c Release 1 is release i am freed from the NDA and allowed to share my information on the most recent Oracle Database Version.

The following slides are from a presentation i held at the 2013 ‘Frankfurter Datenbanktage’ in Germany. I put together a summary of all new Features using all the available sources from Oracle.

You can download the slides here.

A few months back i accidently set my global database name to “null”. As a result the database crashed with “ORA-00600: internal error code, arguments: [18062]”:

SQL> update global_name set global_name=null;
SQL> commit;

Starting background process QMNC
Fri Oct 21 21:38:12 2011
QMNC started with pid=68, OS id=26764
Errors in file /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/trace/ORADB2_ora_22638.trc  (incident=26069):
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/incident/incdir_26069/ORADB2_ora_22638_i26069.trc
Fri Oct 21 21:38:14 2011
db_recovery_file_dest_size of 20000 MB is 71.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/trace/ORADB2_ora_22638.trc:
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Errors in file /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/trace/ORADB2_ora_22638.trc:
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 22638): terminating the instance due to error 600
Instance terminated by USER, pid = 22638
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (22638) as a result of ORA-1092
Fri Oct 21 21:38:17 2011
ORA-1092 : opitsk aborting process

According to the Metalink articles your database is toast – you have to restore it from a backup. But what if there is no backup and you are not that familiar with BBED (the oracle block editor)?

Here is how to solve the issue:

Continue reading →

During an Exadata upgrade at a customer the NEM module suddenly faulted after completing the compute node server upgrade.

Checking the Hardware and Firmware compatibility yielded a message similar to:

Firmware not supported. Required Version: NEM 5.7.0.0

To chec the current firmware of the NEM module do the following:

1. Connect to the ILOM via SSH.
2. cd /sys/nem0   (or nem1)
3. ls

The ‘ls” has the following output:

/SYS/NEM0
 Targets:
 MB
 PRSNT
 STATE
 ERR
 OK
 SERVICE
 OK2RM
 LOCATE

Properties:
 type = Network Express Module
 ipmi_name = NEM0
 fru_manufacturer = FOXCONN
 fru_version = FW 5.7.0.0    <=========== check the firmware version
 fru_part_number = 511-1056-05
 fru_serial_number = 0226LHF-1031AC00N7
 fault_state = OK
 load_uri = (none)
 clear_fault_action = (none)

If LOWER than 5.7.0.0 then load the updated firmware rom from 11.2.2.4.2 cell image file:

1. Download the 11.2.2.4.2 cell image file
2. Make db_patch_11.2.2.4.2.111221/x4800-sas2exp-5.7.0.0-rom.pkg file available from a URL (for example http://myhost.mycompany.internal.com/firmware/x4800-sas2exp-5.7.0.0-rom.pkg)
3. Ensure that the grid infrastructure is shut down.
4. Use SSH to connect to the ILOM as the root user.
5. For each NEMx where x is 0 and 1, do the following:
6. cd /SYS/NEMx
   load -source http://myhost.mycompany.internal.com/firmware/x4800-sas2exp-5.7.0.0-rom.pkg
7. Power cycle the database server.

I just came accross a situation where multipathed devices were not discovered correctly. The device itself was discovered but the disks partitions were missing.

It turned out there are two bugs in OEL 5.8. The first one is related to multipath itself and can be fixed like this:

ls -l /sbin/mu*
 -rwxr-xr-x 1 root root     0 Jun 20 11:01 /sbin/multipath
 -rwxr-xr-x 1 root root 75464 Jul  9  2011 /sbin/multipathd

ln -s /sbin/multipath /sbin/multipath.static

ls -l /sbin/mu*
 -rwxr-xr-x 1 root root     0 Jun 20 11:01 /sbin/multipath
 -rwxr-xr-x 1 root root 75464 Jul  9  2011 /sbin/multipathd
 lrwxrwxrwx 1 root root    15 Jun 20 11:02 /sbin/multipath.static -> /sbin/multipath

The second issue arises with a huge number of LUNs (usually > 100). Here the HAL daemon sometimes has not enough time to detect all the disks. As a workaround create the following file with the following contents:

 cat /etc/sysconfig/haldaemon
 --child-timeout=720

Once done reboot the server and you are done.