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?


Well...


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
http://docs.oracle.com/cd/E11882_01/server.112/e23090/dvdisabl.htm

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
do
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]"
done


Taken from Ronny Egner's blog
http://blog.ronnyegner-consulting.de/2009/10/07/useful-asm-scripts-and-queries/

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 

HOW TO FIX IT SO THAT THE EMCPOWER DEVICE IS THE DEVICE THAT ASMLIB USES

Edit ORACLEASM_SCANORDER and ORACLEASM_SCANEXCLUDE!

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

Change

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning 
ORACLEASM_SCANORDER="" 

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan 
ORACLEASM_SCANEXCLUDE="" 


TO

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning 
ORACLEASM_SCANORDER="emcpower" 

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan 
ORACLEASM_SCANEXCLUDE="sd dm" 



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] 

Done