Tuesday, November 20, 2012

Networking in-depth for Oracle VM Server for x86

Good article on OTN looks "under the hood" of Oracle VM Server for x86, exploring the underlying logical network components that are created by Oracle VM Manager. By best practices engineer in a blog post on OTN garage.

#virtualization   #oraclevm   #ovm  

Saturday, August 11, 2012

Oracle RAC Internals Presentation including Advanced Listener Configuration

Just came across this excellent presentation on RAC internals.

It has quite a few nuggets of information for RAC 11.2, well worth a read.

Understanding Oracle RAC Internals

Markus Michalewicz (Markus.Michalewicz@oracle.com)
Senior Principal Product Manager Oracle RAC and Oracle RAC One Node

Couple of things I picked up (which is what I was searching on)

• Listeners used for a client connection to Oracle RAC should be managed by Oracle Clusterware and should be listening on an Oracle managed VIP.

• Using server-side TNSNAMES aliases to resolve the listener configuration is not recommended. 

• Listeners specified by the LISTENER_NETWORKS parameter should not be used in the LOCAL_LISTENER and REMOTE_LISTENER parameters. Otherwise, cross registration will happen and connections will be redirected cross networks.

Thursday, July 12, 2012

oraenv returns XPointer evaluation failed: no locset

Using the oraanv script and getting "XPointer evaluation failed: no locset"?

Well I have a potential solution. There are a couple of causes of this error that I know of.

One is a bug - using oranev from an agent home.
It's not supported - it has to be from a database home. See 
"Running oraenv from the Grid Control Agent Oracle Home Fails [ID 1323684.1]"
"The oraenv is not meant to be run from the Grid Control Agent ORACLE_HOME.This was diagnosed in BUG 10227734. The BUG will remove the oraenv from future agent installs....
Do not run oraenv to set the environment for the Grid Control Agent.Set the environment manually or using .sh script."
See the note for full details.

What if you are not using an agent home? 

Then the other cause I am aware of is this-

The oraenv script sets ORACLE_BASE. If it is not already set in the environment it calls a binary $ORACLE_HOME/bin/orabase.

orabase returns the ORACLE_BASE by looking in the Oracle Home's Inventory. It looks for the value in the file 

This file it seems can sometimes lose the value of the Oracle Base, and if it does then orabase returns the XPointer evaluation failed: no locset error.

The file contents should look much like this:

      <PROPERTY NAME="ORACLE_BASE" VAL="/ora/oracle"/> 

If the PROPERTY_LIST bit is missing, then you need to add it back in.
Set VAL= to be your Oracle Base - the example above is specific to MY environment.

Saturday, February 11, 2012

SYSDBA user gets ORA-01031: insufficient privileges updating user$

I was merrily following note "Instructions for Clearing pre-11g Database Password Hashes [ID 463999.1]" to remove the pre-11g password hashes from sys.user$. I connected as SYSDBA, and when trying to run the update statement I got ORA-01031:

SQL> update sys.user$ set password=NULL;
update sys.user$ set password=NULL
ERROR at line 1:
ORA-01031: insufficient privileges

I tried another user with SYSDBA privilege and got the same thing. These users can do everything else as SYSDBA but not it seems update user$. What's going on?


The software install was a default install with the "Enterprise Edition" option selected. As it turns out this includes installing the binaries for Database Vault. Even though Database Vault is not configured, the binaries still have an effect. So Database Vault is enabled even though you have not configured it, there are no Database Vault schemas etc.

To remedy the issue you have to relink Oracle without Database Vault on. This is documented in The Database Vault Administrators guide.

Disabling and Enabling Oracle Database Vault

From the doc: Disable Oracle Database Vault as follows:
  • UNIX: Run the following commands:
    $ cd $ORACLE_HOME/rdbms/lib
    $ make -f ins_rdbms.mk dv_off ioracle
  • Windows: In the ORACLE_HOME\bin directory, rename the oradvll.dll file to another name, such as oradvll.dll.dbl.
After disabling database vault I was successfully able to update the password file and remove the pre-11g password hashes.

Word to the wise: Use the custom install option when installing Oracle software and only pick the components you really need and are licensed for. This has the added benefit of upgrades going faster due to not having a bunch of unnecessary packages and tables.

Thursday, February 9, 2012

SQL Developer 3.1 released with new DBA features

SQL Developer 3.1 has been released with a number of new features for DBAs. I have been using the Early Adopter/EA versions and I find the Data Pump wizards useful for quick cheap exports and imports. Tim Hall plays and displays the Data Pump wizards here:

ORACLE-BASE - SQL Developer 3.1 Data Pump Wizards (expdp, impdp)

You can see all the new features here.

Most useful I think is the RMAN and Data Pump integration. There is an improved database diff and a few other things. Definitely worth downloading and updating your current version of SQL Developer

Thursday, February 2, 2012

Script to show device mappings for ASMLib disks

I really like this wee script for showing mapping:

/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` |
cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' |
while read v_asmdisk v_minor v_major
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor, $v_major]"

Taken from Ronny Egner's blog

He found it somewhere on the web and updated for modern ASM versions.

Output looks like:

ASM disk ORA_FPF_12GB_EMCP004 based on /dev/emcpowera1 [120, 1]
ASM disk ORA_FPF_12GB_EMCP005 based on /dev/emcpowerb1 [120, 17]
ASM disk ORA_FPF_12GB_EMCP006 based on /dev/emcpoweraf1 [120, 497]
ASM disk ORA_FPF_12GB_EMCP007 based on /dev/emcpoweras1 [120, 705]

Another great post with useful scripts by Bane Radulović - Senior Principle Support Engineer at Oracle Support in Melbourne:
ASM Support Guy ™: How to map an ASMLIB disk to a device name

ASMLib and EMC PowerPath Multipathing

Another quick note - an email I sent to other DBAs at work.

Problem - ASMLib picks the first disk device it finds for a particular ASM disk and uses that device. This usually means that ASM is NOT using the multipath emcpower device.

ASMLib picks the first disk device it finds for a particular ASM disk and uses that device. This usually means that ASM is NOT using the multipath emcpower device. 

Let's pick an ASMLib disk on my server - in this case I choose a disk that has been named  ORA_FPF_90GB_EMCP022 .

root@fpfkxtdb02 ~# /etc/init.d/oracleasm  querydisk ORA_FPF_90GB_EMCP022 
Disk "ORA_FPF_90GB_EMCP022" is a valid ASM disk 

There are lots of possible paths to the disk. ASM picks the first one it finds and ignores the rest. What paths are there for our disk?? 

root@fpfkxtdb02 ~# /etc/init.d/oracleasm querydisk -p ORA_FPF_90GB_EMCP022 
Disk "ORA_FPF_90GB_EMCP022" is a valid ASM disk 
/dev/sdn1: LABEL="ORA_FPF_90GB_EMCP022" TYPE="oracleasm" 
/dev/sdav1: LABEL="ORA_FPF_90GB_EMCP022" TYPE="oracleasm" 
/dev/sdcd1: LABEL="ORA_FPF_90GB_EMCP022" TYPE="oracleasm" 
/dev/sddk1: LABEL="ORA_FPF_90GB_EMCP022" TYPE="oracleasm" 
/dev/emcpowerm1: LABEL="ORA_FPF_90GB_EMCP022" TYPE="oracleasm" 

Lots of paths.

Lets see what device path is actually being used for our ASMLib disk: 

root@fpfkxtdb02 ~# /etc/init.d/oracleasm querydisk -d ORA_FPF_90GB_EMCP022 
Disk "ORA_FPF_90GB_EMCP022" is a valid ASM disk on device [8, 209] 

Device [8, 209]  is the first disk in the path list - /dev/sdn1 - NOT the emcpower device. 

root@fpfkxtdb02 ~# ls -l /dev/sdn1 
brw-r----- 1 root disk 8, 209 Oct 24 12:15 /dev/sdn1 

We want ASMLib to be using THIS device: 

root@fpfkxtdb02 ~# ls -l /dev/emcpowerm1 
brw-r----- 1 root disk 120, 193 Oct 24 12:15 /dev/emcpowerm1 



root@fpfkxtdb02 ~# vim /etc/sysconfig/oracleasm 


# ORACLEASM_SCANORDER: Matching patterns to order disk scanning 

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan 


# ORACLEASM_SCANORDER: Matching patterns to order disk scanning 

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan 

Unload, reload ASM and scandisks

root@fpfkxtdb02 ~# oracleasm exit 
Unmounting ASMlib driver filesystem: /dev/oracleasm 
Unloading module "oracleasm": oracleasm 
root@fpfkxtdb02 ~# oracleasm init 
Loading module "oracleasm": oracleasm 
Mounting ASMlib driver filesystem: /dev/oracleasm 
root@fpfkxtdb02 ~# /etc/init.d/oracleasm scandisks 
Scanning the system for Oracle ASMLib disks:               [  OK  ] 

Check that ASM is now using the emcpower device:

root@fpfkxtdb02 ~# /etc/init.d/oracleasm querydisk -d ORA_FPF_90GB_EMCP022 
Disk "ORA_FPF_90GB_EMCP022" is a valid ASM disk on device [120, 193] 


Thursday, January 5, 2012

Password Case Sensitivity and enabling SHA-1 authentication protocol

The following is a mish mash of stuff gleaned from postings and documentation on case sensitive passwords and enforcing SHA-1 password authentication protocol. It's not flash, posting for my own use. If you find it useful too good on ya!

Password Case Sensitivity

Database parameter to turn it on and off: sec_case_sensitive_logon

To view password hashes:
select name, password,spare4 from user$

To view what type of hashes are in use, but not the hashes themselves:

The users having password_version=10g 11g, means they are imported from 10g and they have modified their passwords after being imported to 11g.

If we enable case sensitivity (sec_case_sensitive_logon TRUE), authentication is done against the mixed case 11g password.

If we disable case sensitivity (sec_case_sensitive_logon FALSE), authentication is done against the 10g  case insensitive password

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is  false, the login fails as there is no 10g string. This would be most secure.

When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.

When using both hashes, switching back and forth to 11g mechanism is possible.

When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved.

When issuing a create user identified by values, you can choose if you want to have both passwords, only the case insensitive or only the case sensitive.

Exclusive mode

Security can be increased when case sensitive password are used AND logon’s are limited to the 11g authentication protocols.

Oracle calls this "EXCLUSIVE MODE".

"You optionally can configure Oracle Database to run in exclusive mode for Release 11 or later. When you enable exclusive mode, then Oracle Database uses the new SHA-1 hashing algorithm exclusively. Oracle Database 11g exclusive mode is compatible with Oracle Database 10g and later products that use OCI-based drivers, including SQL*Plus, ODBC, Oracle .NET, Oracle Forms, and various third-party Oracle Database adapters. However, be aware that exclusive mode for Release 11g is not compatible with JDBC type-4 (thin) versions earlier than Oracle Database 11g or Oracle Database Client interface (OCI)-based drivers earlier than Oracle Database 10g. After you configure exclusive mode, Oracle recommends that you remove the old password hash values from the data dictionary"

This can be achievement by setting the sqlnet parameter SQLNET.ALLOWED_LOGON_VERSION to 11 and removing the 10g password hashes from USER$.

The parameter actually specifies the AUTHENTICATION PROTOCOL (for example SHA-1) that a client is allowed to use, NOT the actual VERSION of that client (for example 10): even though the parameter value might be '10', the internal check is against the authentication protocol 'SHA-1'. (How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly [ID 1304142.1])

Oracle 10g and 11g both use the SHA-1 protocol. Oracle 12g will use the SHA-2 protocol.

The process is documented here:

Ensuring Against Password Security Threats by Using the SHA-1 Hashing Algorithm

Instructions for Clearing pre-11g Database Password Hashes [ID 463999.1]

See also

11g R1 New Feature : Case Sensitive Passwords and Strong User Authentication [ID 429465.1]

Case Sensitive Passwords and Strong User Authentication | OraDBA

How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly [ID 1304142.1]

Authentication Protocols

Oracle native authentication version 9i and 10g

Downgrading the Oracle Native Authentication
SecuriTeam - Downgrading the Oracle Native Authentication