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.

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

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

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:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

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"

http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#CHDEFIHB

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
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#CHDEFIHB

Instructions for Clearing pre-11g Database Password Hashes [ID 463999.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=463999.1

See also

11g R1 New Feature : Case Sensitive Passwords and Strong User Authentication [ID 429465.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=429465.1

Case Sensitive Passwords and Strong User Authentication | OraDBA
http://www.oradba.ch/2011/02/case-sensitive-passwords-and-strong-user-authentication-2/

How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly [ID 1304142.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1304142.1


Authentication Protocols

Oracle native authentication version 9i and 10g
http://www.soonerorlater.hu/index.khtml?article_id=511

Downgrading the Oracle Native Authentication
SecuriTeam - Downgrading the Oracle Native Authentication
http://www.securiteam.com/securitynews/5KP0M00KKG.html

ORA-00600 [kkpo_rcinfo_defstg:objnotfound] - deferred segment creation strikes.

11gR2 has a new feature, deferred segment creation. It has through lack of wisdom, been set as default in 11.2.0.2. What it does is defer the creation of a segment until there is actually data to go into it. So if you create a table, you will not see it in dba/user_segments until you add the first row, then the segment gets created.

It is buggy - just check My Oracle Support for nasty bugs that require a database restore to fix.

Here is what I ran into today:


  'alter table XXXXX enable constraint PK_XXXXX exceptions into PK_VIOLATIONS': 
  Error:
  ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [85752], [], [], [], [], [], [], 

There were various references to running the wrong catupgrd.sql script causing it, but that was not my issue. After a bit of hunting matched it to Bug 12840114 which is still open.

I highly recommend disabling this new default behaviour - that is turn off deferred segment creation.

You can do this either:

• at the database level via your init/spfile setting parameter DEFERRED_SEGMENT_CREATION to FALSE
• at the session level via "alter session set deferred_segment_creation = false;"
• at the statement level via "create table t ( x int ) segment creation immediate;"

See

Deferred Segment Creation...
The Tom Kyte Blog: Deferred Segment Creation...
http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html

Oracle related stuff: ORA-14404 / 14405 and Deferred Segment Creation
http://oracle-randolf.blogspot.com/2011/04/ora-14404-14405-and-deferred-segment.html

Alter Table Enable Constraint Fails With ORA-600 [Kkpo_rcinfo_defstg:Objnotfound] [ID 1352678.1]
https://support.oracle.com/CSP/main/article?cmd=show&id=1352678.1&type=NOT

The bug that got me:
Bug 12840114: ORA-600 [KKPO_RCINFO_DEFSTG:OBJNOTFOUND] ENABLING CONSTRAINT EXCEPTIONS INTO..
https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=12840114

Oracle SQL Developer version 3 is released

It's an improvement over version 2 with support for data models and other features:

Area	Feature	Description
Data Mining	Oracle Data Miner 11g Release 2 GUI	Oracle Data Mining, an Option to the Oracle Database EE, provides powerful data mining functionality as a feature of the database
		Oracle Data Miner, a free download from the Oracle Technology Network with SQL Developer 3.0, simplifies and automates the data mining process
		Oracle Data Miner help users mine their data and define, save and share advanced analytical methodologies
		Visualize data, perform data transformations for data mining
		Build, evaluate and apply classification, clustering, anomaly detection, text mining, market basket, etc. predictive models
		Developers can use the SQL and Java APIs to build applications to automate knowledge discovery
Data Modeler		SQL Developer Data Modeler 3.0 is now fully integrated, providing read and write access for designing and developing data models.
		The readme.html for SQL Developer Data Modeler 3.0 can be found in the SQL Developer installation in the folder \sqldeveloper\sqldeveloper\extensions\oracle.datamodeler.
DBMS Scheduler		Create a full schedule definition using graphical and grid editors
	Programs	Basic tasks which could be external procedures, PL/SQL or stored procedures
	Schedules	Specify when and how many times a job is executed
	Jobs	Objects that are scheduled which could be Programs or Chains
	Job Classes	Group jobs for resource allocation and prioritization
	Chains	Create a flow of Chain Steps that are linked together by Chain Rules to create more complex Jobs
	Credentials	Assign a username/password pair that is used by jobs to authenticate with a host operating system or Oracle Database
	Database destinations	Specify Oracle database on which remote database job run on
DBA Functionality	Database Configuration	Ability to review and tune Database configuration and edit initialization parameters
	Resource Manager	Create and prioritize consumer groups and manage resource plans
	Security	Define audit settings and manage profiles, roles and users
	Storage	Define and manage archive logs, control files, datafiles, redo logs, rollback segments and tablespaces
Exporting and Importing	Import Data	Customizable user interface based on Preference settings
		Dynamic data preview reflects changes as they are made to the file definition
		Delimited file support allowing specification for delimiters, enclosures, line terminators, and file encoding
		New “Staging External Table” load method provides generation of an external table used as a staging table for the load. Data is inserted into the table by selecting from the staging external table
	Export Data	Customizable user interface based on Preference settings
		Delimited file support allowing specification for delimiters, enclosures, line terminators, and file encoding
		PDF file support based on Preference settings
		Extended object search capability to include schema and object type
		Ability to unload data from views and materialized views
		Options to write output to a single file, separate files for each object, separate files for each object type, and files into separate directories by object type
Files and Version Control	File Based Debugging	Set breakpoints in PL/SQL files (.pls, .pkb, etc) and run or debug these files
Menus	Connections	Recompile Schema on a connection
		Disconnect All connections
	Tables	Generate Table API on the Tables node
Migrations	Full command line support	All major migration functions can now be run on the command line
	Server Capture support	Ability to capture all the databases in a server for SQL Server and Sybase
	Project support for Migrations	New project navigator. All migration tasks are now wizard based to manage the process
	Application Scanning and Migration for Sybase DBLIB and CTLIB applications	The applications scanning is only supported for scanning dblib and ctlib and reporting on the areas which need to be changed to work with Oracle
	Migration Project Estimation	Ability to work out a time and resource duration for a database migration project. The project estimation generates a spreadsheet that can be used by Project Managers to customize resource planning to suit their organization
	Migration Reports	Reports for detailed analysis of source objects, converted objects and issues with migrations
	Improved identifier and name mapping	Define the names of target objects before the migration starts
PDF	Customizable Layouts	Customize table, column and cell layout which includes fonts, styles, row shading, borders, alignment, padding, spacing and wrapping text. Options to include column headers and column footers.
	Binary Large Object Support	Images, video, text, doc, spreadsheets, and other objects embedded in the PDF document, or linked as separate files
	Document Compression	Different levels of document compression
	Meta data	Meta data such as title, name, subject and keywords
	PDF Generation	Generate PDF for database objects or data from Connections Navigator, Reports Navigator, DBA Navigator, Database Unload and Data Grid via Unload
	Security	Password-protect a pdf document, permission password for security settings and different levels of encryption
	Version Support	Version support from SQL Developer 1.5 to latest release
PL/SQL Support	Parser	Code completion was amended with additional templates automatically derived from PL/SQL and SQL syntax
	Debugging	PL/SQL debugging of anonymous blocks
	Ref Cursor	Run and debug procedures with ref cursors
		Display ref cursor data
Query Builder	Query Builder Viewer	Visual SQL Query Builder with an object drag and drop feature from the connection navigator or schema browser to the Query Builder view. SQL Worksheet and Query Builder view are tightly integrated. Switch between Worksheet (text) and Query Builder (visual) views at any time
	Automatic Join Creation	Automatically determine and show joins between related tables added to the Query Builder canvas. Can also drag/drop columns to manually create them
	Multiple Join Types	Join properties in clear user terms (“Show all ”) remove confusion with LEFT OUTER, RIGHT FULL, etc. syntax
	Grouping, Predicates, Sorting	Column visibility, alias name, grouping, sorting and selection predicates are shown in an easy to use table layout
	Derived Tables, Sub-Queries, and Unions	Derived Tables, Sub-Queries, and Unions are shown as sub-tabs in the Query Builder and can be edited in the same way as the main query
Schema Browser		Easier navigation between object types and schemas for a specific connection
		Support fast client-side name based filtering for narrowing the list of objects displayed
SQL Plus Commands		View all supported SQL Plus Commands
Spatial	Spatial Data Management	Identify tables/views and materialized views that contain spatial datatype columns
		Define /Validate spatial metadata and indexes
		Show and manipulate spatial data instances on a new 'map' view
		Interact with objects on the map view
		Validate spatial data instances
TimesTen	TimesTen In-Memory Database Support	Management of database users. Create and drop users, grant and revoke system privileges, change user passwords
		Pre-defined reports on TimesTen database objects, such as PL/SQL, Table, User and Privileges.
		Support for Synonyms and Materialized View Logs
		View and update column data using custom cell editors
		Gather table and column statistics for all tables belonging to the connection user
		Add or change a PK index to hash index
		Schema browser support for filtering and narrowing the list of objects displayed
		Create cache groups from Oracle synonyms
		Compile invalid or all PL/SQL packages, procedures and functions
Tuning	SQL Tuning Advisor	Analyzes submitted high-volume SQL statement and offers tuning recommendations
	Diff Tool for Explain Plans	Compare selected Explain Plan or Autotrace results for any SQL statements.
	Management Pack Licensing Preferences	Preferences option to track which Oracle database features are claimed to be licensed on a connection-by-connection basis
Unit Testing		Advanced Data Types Support
		Multi-user support
		Multiple startups/teardowns
		Nested suites
		Purge run results
		Rename test implementation
		Test synchronization and retargeting tests
		Export tests

Download:

Oracle SQL Developer