The Realm of Ken Masters: Oracle

Friday, January 20, 2012

DBA: DC City Stories: Oracle 11g Password Expiry

Don't be caught out with 11g's new DEFAULT profile password policy. The 11G DEFAULT profile has the following:

6 month password expiry
10 failed login attempts before lockout

Both will cause havoc when they're service accounts.

To unlock an account:

ALTER USER SCOTT ACCOUNT UNLOCK;

To find the profile settings:

col profile for a20
col resource_name for a25
col limit for a15
select profile,resource_name,limit
from dba_profiles
where resource_type='PASSWORD'
order by profile;

PROFILE RESOURCE_NAME LIMIT

---------- ------------------------- ---------------

DEFAULT PASSWORD_REUSE_TIME UNLIMITED

DEFAULT PASSWORD_LOCK_TIME 1

DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION NULL

DEFAULT PASSWORD_LIFE_TIME UNLIMITED

DEFAULT PASSWORD_REUSE_MAX UNLIMITED

DEFAULT PASSWORD_GRACE_TIME 7

To find out the status of the accounts:

set pagesize 100
set wrap off
col USERNAME for a25
col PROFILE for a15
col ACCOUNT_STATUS for a20
col EXPIRY_DATE for a15

select USERNAME, PROFILE, ACCOUNT_STATUS, EXPIRY_DATE
from DBA_USERS;

USERNAME PROFILE ACCOUNT_STATUS EXPIRY_DATE

------------------------- ---------- -------------------- ---------------

CORPDIR_USER DEFAULT OPEN

MMS_USER DEFAULT OPEN

NIZAR_RO DEFAULT OPEN

SS_USER DEFAULT OPEN

SCOTT DEFAULT EXPIRED & LOCKED 17-MAR-11

ORACLE_OCM DEFAULT EXPIRED & LOCKED 15-AUG-09

XS$NULL DEFAULT EXPIRED & LOCKED 15-AUG-09

MDDATA DEFAULT EXPIRED & LOCKED 15-AUG-09

DIP DEFAULT EXPIRED & LOCKED 15-AUG-09

APEX_PUBLIC_USER DEFAULT EXPIRED & LOCKED 15-AUG-09

SPATIAL_CSW_ADMIN_USR DEFAULT EXPIRED & LOCKED 15-AUG-09

SPATIAL_WFS_ADMIN_USR DEFAULT EXPIRED & LOCKED 15-AUG-09

DBSNMP MONITORING EXPIRED 20-SEP-11

To change the DEFAULT profile password policy:

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;

Wednesday, August 3, 2011

DBA: DC City Stories: Datapump

Oracle always has a way with error message. Why can't they make it clear and precise?

I was doing a database export using datapump for a client, and I passed in PARFILE. The following error was spat out:

ksunsodb01:oracle$KTECH$@ expdp parfile=expdp.par

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 07 July, 2011 11:09:21

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 488

ORA-29283: invalid file operation

Not very clear huh? Well, what Oracle was complaining about was that it was missing the Datapump directory on a OS level. Therefore, check which directory your PARFILE is using:

ksunsodb01:oracle$KTECH$@ cat parfile_expdp_WMDATA.par

USERID="/ as sysdba"

dumpfile=DATA_PUMP_DIR:expdp_KTECH_WMDATA_20110707.dmp

logfile=DATA_PUMP_DIR:logexpdp_KTECH_WMDATA_20110707.log

schemas=WMDATA

flashback_time="to_timestamp(to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss'), 'dd/mon/yyyy hh24:mi:ss')"

In our case, the directory is "DATA_PUMP_DIR". Find out where this directory is mapped to:

ksunsodb01:oracle$KTECH$@ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 7 11:14:07 2011

SQL> conn / as sysdba

Connected.

SQL> select directory_path, directory_name from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH

------------------------------------------------------------
/u06/flash2/exports/OTECH/ DATA_PUMP_DIR

And check that the directory actually exists. In our case it doesn't, so modify it to a directory that exists:

SQL> create or replace directory DATA_PUMP_DIR as '/u06/flash1/export/KTECH/';

Directory created.

SQL> exit

Now, we're ready to run again. :)