Password History – Reusing a password

Posted on May 25, 2011 by marcel

By setting either one or both the profile limits PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME are set to anything other than UNLIMITED, Oracle somewhere keeps a history of passwords used by a user. This password history is stored in a table with the name user_history$ which is part of the SYS schema.

Whenever the users password is changed, either by the user of by a DBA, the case-insensitive password hash (3DES – 10g) is stored in this table. Even in 11g, where by default SHA1 password hashes (case-sensitive) are used, oracle stores the 3DES hash in the user_history$ table.

The sys.user_history$ table contains 3 columns; user#, password and password_date. Because the password column is of datatype VARCHAR2 with a maximum length of 30 characters, its not able to store the SHA1 hash value. When however you use an “alter user … identified by values” specifying only the SHA1 hash (effectively removing the 3DES hash), oracle stores the last 30 characters of the SHA1 hash value in the password field of the sys.user_history$ table instead.

Oracle only adds passwords for a user to the user_history$ table when password reusing is bounded by the profile enabled for the user. As soon as the “reuse” limits in a profile are set the UNLIMITED password changes don’t get tracked anymore, but any passwords that already exist in the history table won’t get deleted.

Posted in Database | Tagged , , , , , | 3 Comments

adrci – No ADR base is set

Posted on May 23, 2011 by marcel

Some time ago we had a problem using the adrci (ADR Command Interpreter) to look into some diagnostic information for a listener. After staring up the tool it came back with the warning “No ADR base is set”, even while the ORACLE_BASE environment variable was set. While it is possible to use the SET BASE <ADR base directory> to set the ADR base manually in the adrci it should work without this manual setting so we an use it safely from within scripts. Looking on MOS you get “Very” little results (only bug 1007522) and the solution is rather rigorous just for getting the correct path to the ADR base: restart your database/asm instance!

The actual problem is a corrupted or missing file named adrci_dir.mif which is located in $ORACLE_HOME/log/diag. The adrci_dir.mif file contains the path to the ADR base. The adrci gets the ADR base path from this file and when it does not exist or the file is corrupted you get the “No ADR base is set” warning. The adrci_dir.mif file is created when a database or asm instance is started and can get corrupted when two instances start at the same time. Starting a listener does not create/change this file!

To get the problem fixed without the rather absurd method of restarting one of the instances in the Oracle home having this issue, you can use the method below to create/overwrite the adrci_dir.mif file with the correct ADR base path. Don’t use standard vi of a normal echo while this will add a NewLine character (0x0a) at the end of the path, also resulting in the “No ADR base is set” warning.

printf "%s" "<ADR base path>" > $ORACLE_HOME/log/diag/adrci_dir.mif

For example:

printf "%s" "/u01/app/oracle" > $ORACLE_HOME/log/diag/adrci_dir.mif

It looks like in Oracle 11g release 2 things have changed a little bit, because there well be two lines in the adrci_dir.mif file. The first lines still contains the location of the ADR base path, but the seconds line specifies another path. As I find out more about this one I will update this post.

Posted in Database | Tagged , , , , , , | 3 Comments

Expired passwords in 11g database using 10g client

Posted on May 22, 2011 by marcel

When I create a new user/schema in an Oracle database I specify a password and immediately use the PASSWORD EXPIRE clause to expire this one-time-use only password. This way I make sure the user/application who is going to use this new account will be forced to specify his own password. This works great unless you have a pre 10.2.0.5 Oracle client connecting to an 11g database.

When connecting with a pre 10.2.0.5 client (for example SQL*Plus) after you login, the user will correctly receive the message that the password is expired and will be prompted for the new password. After typing the new password twice, he/she will receive an ORA-01017 (invalid username/password. Login denied) and the password stays unchanged. This will happen every time the user tries to login, in practice making the user unusable.

The problem is due to the case-insenstive 10g client and the use of case-sensitive passwords in 11g databases (enabled by default, which is good!). There are a couple of ways to fix/get around this problem:

  • Install the 10.2.0.5 patchset on your 10g clients or install the 11g client software instead
  • Don’t expire passwords

Ask the user to change the password for the account as soon as possible and check if this is done by looking at the timestamp in the ptime column of the sys.user$ table, which will show you the date/time of the last password change for an account.
Example query:

SELECT name, TO_CHAR(ptime,'DD-MM-YYYY HH24:MI:SS') "Password Changed" FROM sys.user$ WHERE name='&orauser';
  • Disable case-sensitive passwords in the 11g database (not recommended!)

Set the initialization parameter “sec_case_sensitive_logon” to FALSE.

ALTER SYSTEM SET sec_case_sensitive_logon=FALSE SCOPE=BOTH;

By the way, use the PASSWORD statement instead of ALTER USER …. IDENTIFIED BY … making sure passwords are not sent clear-text over the network.

A short description of this problem can be found in MOS note 888432.1

Posted in Database | Tagged , , , , , , | Leave a comment

Dump interesting information about Oracle files

Posted on May 16, 2011 by marcel

Here are some useful statements for generating trace files in your udump directory about various important Oracle files:

Get a dump of your controlfiles:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 10';

Dump the headers for all your datafiles:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME FILE_HDRS LEVEL 10';

Dump the headers of your redologs:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME REDOHDR LEVEL 10';

Dump the system state:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

 

 

 

Posted in Database | Tagged , , , , , , , , | Leave a comment

Unregistering (removing) agent from OEM Grid Control

Posted on May 15, 2011 by marcel

When you want to permanently remove an agent from you OEM GC environment, you will have the GUI method and the manual method. The reason for writing this post is that I was able to remove all targets from within the GUI except the agent itself, at least at screen I was expecting to offer this functionality.

The GUI method

  • Shutdown the agent that you want to remove
  • In OEM GC: goto the link “setup”
  • In OEM GC – setup: goto the link “Agents”
  • Look for your agent and click it. The next screen will show the monitored targets for the agent.
  • One-by-one select the targets and click the “Remove” button, until there are no targets left.
  • Now goto the tab “Targets” and select “All Targets”
  • Search for the name of your agent, select it and click the “Remove” button
  • The agent should now be unregistered (removed) from your OMS. The last step is to deinstall the agent software from the server.

The agent should now be unregistered (removed) from your OMS. The last step is to deinstall the agent software from the server.

The manual method

  • Shutdown the agent that you want to remove
  • Use SQL*Plus to connect to your OMS repository database as SYSMAN
  • Enter the following statement:
    EXEC MGMT_ADMIN.CLEANUP_AGENT(‘<server_to_remove>:<agent port>’);

The agent should now be unregistered (removed) from your OMS. The last step is to deinstall the agent software from the server.

Posted in Enterprise Manager | Tagged , , , , | Leave a comment

Privileges for AWR reporting

Posted on May 12, 2011 by marcel

Sometimes during a project there are people that would like to generate AWR reports. Here are the privilges on certain database objecs that a user needs to be able to generate such reports using SQL*Plus (in this case granted to a role).

CREATE ROLE awr_user;

GRANT SELECT ON SYS.V_$DATABASE TO awr_user;
GRANT SELECT ON SYS.V_$INSTANCE TO awr_user;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awr_user;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awr_user;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awr_user;

In short for generating an AWR report from within SQL*Plus

Getting the snapshot id’s for available snapshots taken (starting yesterday) and other information needed for generating the AWR report:

SELECT
  snap_id, dbid, instance_number, end_interval_time
FROM
  dba_hist_snapshot
WHERE
  end_interval_time > trunc(sysdate-1)
ORDER BY
  snap_id;

To generate an AWR report (in HTML format) use the following statement:
Don’t forget to start spooling before using the command. There will be a lot of output generated!

SELECT * FROM
TABLE(sys.DBMS_WORKLOAD_REPOSITORY.awr_report_html(<dbid>,<instance_number>,<snapshot_id begin>,<snapshot_id eind>));
Posted in Database | Tagged , , , , , | Leave a comment

Simulating ORA-errors

Posted on May 12, 2011 by marcel

Sometimes it can be useful to simulate a problem in your database by simulating an ORA-error. After we had a nasty bug with Grid Control (GC suddenly wasn’t able to monitor the alertlog of 10.2.0.5 databases anymore, see my post about this problem) where we had to discover this bug by having a problem in a database which was not alerted by Grid Control.

It would be nice to have way of testing some basic functionality (as I think monitoring the alertlog for errors is) before updating/patching an agent, grid control or (in the example mentioned above) database. As of Oracle database version 11g Release 1, with the introduction of the ADR (Automatic Diagnostic Repository) the way Grid Control monitors for errors has changed. Until ADR, simply adding 2 lines to the alertlog did the trick but with the ADR it is rather hard to “generate” an ORA-error that will be picked up by Grid Control (the agent).

Fortunately there is a trick to have an 11g database generate an ORA-error which will (should) be picked up by Grid Control. As mentioned before, in 10g you could fake an ORA-error just by adding two lines to the alertlog (first a line with the date and a second line with the actual ORA-error – an ORA-7445 for example) or by using a procedure of the DBMS_SYSTEM package.

For version 10g (and most likely 9i):

exec sys.dbms_system.ksdwrt(2, 'ORA-07445: SIMULATED ERROR, JUST FOR TESTING, IGNORE!!!');

If you would like to add the error to the alertlog and generate an tracefile for it, use:

exec sys.dbms_system.ksdwrt(3, 'ORA-07445: SIMULATED ERROR, JUST FOR TESTING, IGNORE!!!');

For version 11g (and above):

alter session set events '942 incident(SIMULATED_ERROR)';
drop table tablethatdoesnotexist;
alter session set events '942 trace name context off';
Posted in Database | Tagged , , , , , , , , , , | Leave a comment

Calculating Compression Ratio

Posted on May 10, 2011 by marcel

Before implementing one or more of the Oracle compression options that are available within an Oracle database, there are a couple of things you would like to examine before implementing one or more of these compression options. One thing you would like to know in front, without actually compressing an table, table partition or index, how much storage you would save when compressing such an object.

Oracle is delevering the so called Oracle Compression Advisor for Oracle database versions ranging from Oracle 9.2 up to 11.1 (this package is built-in into version 11.2), but the functionality of this package is rather limited (only determine compression ratio for tables).

This is the main reason why I’ve created a package (MCL_COMPRESS_CALC) which has the following functionality:

  • Determine compression ratio (and some other compression statistics) when implementing DIRECT_LOAD or OLTP compression for a table or table partition
  • Determine the compression ratio and optimal prefix length for when implementing index key compression for an index
  • Use a worklist for batch-mode calculation of the compression ratio (and others) for a list of objects and storing the results in a statistics table
  • Store the results in a statistics table for reporting (in worklist mode and single table/index mode)

Continue reading →

Posted in Database | Tagged , , , , , , , , , , , | 2 Comments

A listener and its children

Posted on March 27, 2011 by marcel

Until last week I din’t know about a listener process having one or more childrens (a child process has exactly the same name as its parent but have their parent-pid set to the parent listener pid). Trying to find information about the existence of listener child processes (first of all I wanted to know if this is “normal”) always pointed to some bug that was there until 10.2.0.3 about a hanging listener process in combination with a listener having a child process.

It appears that these children processes only show up on servers that are heavy loaded. In normal situations you don’t see these child processes because they are only there for a split second (so they are “normal” but they are to quick to see!).

This is the explanation for listener child processes being there:

  • Listener (parent) listens on a configured port number (default 1521)
  • When it receives a request, accepts it
  • The listener (parent) process forks itself (creating a child process) both using the same socket.
  • The listener (parent) process detaches from the socket and will start listening on the configured listener port number again, waiting for new connection requests.
  • The “child” listener closes all unnecessary parent listener resources (various file descriptors) and will start the Oracle binary (overwriting itself) which will let the Oracle binary inherit existing resources (like stdin, stdout en stderr) and socket to let Oracle communicate with the client.

This means that when you see listener child processes this means, the child listener process wasn’t overwritten by the Oracle binary just yet. When you have a server under heavy load, this overlaying could take longer than normal thus you seeing child listeners.

Posted in Database | Tagged , , , , , , | 1 Comment

DBMS_METADATA – Quickstart

Posted on March 26, 2011 by marcel

The DBMS_METADATA is a powerful package that offers the retrieval of metadata from the data dictionary as XML or DDL and to submit XML to re-create an object. In this post I will show you how you can quickly generate DDL statements for lots of objects in the database using some examples.

There are quite some functions in this package, but there are a couple of functions that I will focus on (and use in some examples) :

  • GET_DDL
  • GET_DEPENDENT_DDL
  • GET_GRANTED_DDL
  • OPEN
  • SET_COUNT
  • SET_FILTER
  • ADD_TRANSFORM
  • SET_TRANSFORM_PARAM
  • FETCH_CLOB
  • CLOSE

Continue reading →

Posted in Uncategorized | Leave a comment