How to retrieve the hash value for a password in Oracle 11g 20 October 2010
Posted by David Alejo Marcos in Oracle 11.2, SQL - PL/SQL.Tags: Oracle 11.2
comments closed
As we all know, Oracle has decided to do not show the hash value for the password on the dba_users view.
The problem:
Saving your passwords to a file as below does not work anymore:
SELECT 'ALTER USER ' || username || ' identified by values '''|| password || ''';' FROM dba_users;
This is necessary, in our case, for releases, DEV/QA refreshes, etc…
The solution:
I first wrote the following query:
MYUSER@mydb> SELECT substr (trim(DBMS_METADATA.get_ddl ('USER','MYUSER')),
2 REGEXP_INSTR(trim(DBMS_METADATA.get_ddl ('USER','MYUSER')),'''',1,1,0,'m'), 20
3 ) PASSWD
4 FROM DUAL;
PASSWD
--------------------------------------------------------------------------------
'2YYYYY4DXXXXXX'
Note.- The hash value has been modified for security reasons.
I was working under the assumption Oracle will not change the algorithm any time soon (currently the length is 16 characters); but I also found “dangerous” to make this assumption.
So my next query is:
select REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER','MYUSER'), '''[^'']+''') PASSWD from dual;
which I find more elegant.
MYUSER@MYDB> select REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER','MYUSER'), '''[^'']+''') PASSWD from dual;
PASSWD
--------------------------------------------------------------------------------
'XXXXX4YYYYYZZZZZZ'
The code we need to use from now on is:
set long 150
set linesize 150
set longchunksize 150
select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' from dba_users
As always, comments are welcome.
Enterprise Manager Grid Control does not send emails 20 October 2010
Posted by David Alejo Marcos in Enterprise Manager, Grid Control, Oracle 11.2.Tags: Enterprise Manager, Grid Control, Oracle 11.2
comments closed
Have you ever wondered why EM Grid Control is not sending you email notifications for your alerts?
The problem:
I have been working with EM Grid Control for some time. We have some rules and templates created to monitor our databases and we normally receive emails when a Warning or Critical threshold have been crossed.
We did upgrade our OEM repository to 11g 1 month ago and I noticed we stopped receiving emails. I did review the alerts, tested emails and all was working fine, we just did not receive emails from our alerts.
The solution:
Quite simple, but it took me some time to find it.
As part of the upgrade, the parameter JOB_QUEUE_PROCESSES was set to 0.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
as soon as I changed to a different number (please, bear in mind 10 is the minimum recommended by Oracle)
SQL> alter system set job_queue_processes=15 scope=memory;
I started to receive emails.
Notes:
1.- I have not tested SNMP Traps to feed our Nagios as it is still in development, but I will assume the fix is the same.
2.- Remember to change the parameter on your spfile.ora or init.ora file.
3.- As soon as you change the parameter you will start receiving all alerts (including old), I did receive over 1300 emails in 20 minutes, so you might consider doing the change on Friday by EOD and clear your inbox on Monday.
As always, comments are welcome.