How to re-open expired oracle database account without change password

Imagine that you have an expired Oracle Database account and you have an urgent need to recover it. Maybe you don’t have any chance to ask that user to change password, or maybe you expired such an account that used by a production application by mistake!

-yes, I confess; it was me who experienced that situation-

If so, do what I did; calm down and take a deep breath and imagine that you are the one who survived after a big disaster on the earth. Just kidding, it’s not a big trouble.

Anyway, I couldn’t find any documented solution for that by Oracle, but found a way to accomplish this task:

Alter user’s password with existing password. Yes, it works. Let’s make a short demo:

1. Create a user
2. Expire it
3. Alter user’s password with existing password to unexpire

1. CREATE AN ACCOUNT NAME TEST

SQL> CREATE USER TEST IDENTIFIED BY "manager";

User Created

SQL> SELECT username,account_status,expiry_date FROM dba_users WHERE username='TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- -----------
TEST OPEN

2. EXPIRE IT
 
SQL> ALTER USER TEST PASSWORD EXPIRE;

User altered.

SQL> SELECT username,account_status,expiry_date
FROM dba_users
WHERE username = 'TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- ------------
TEST EXPIRED 19/12/2012
 
3. GET THE EXISTING PASSWORD

SQL> SELECT DBMS_METADATA.get_ddl ('USER', 'TEST')
FROM DUAL;

CREATE USER "TEST" IDENTIFIED BY VALUES 'S:24BF9B8EBC5BC5B609D01915E089AF86D13E1F0627A729146708F5296E32;806B1E705874556A'

DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
 
4.EDIT ABOVE SCRIPT TO HAVE ALTER STATEMENT AND EXECUTED IT

SQL> ALTER USER "TEST"
IDENTIFIED BY VALUES 'S:4F5BD279183624CB94C3E67420A1E748A484183579F89E64B98ECB17A162;806B1E705874556A';

User altered.

SQL>SELECT username,account_status, expiry_date FROM dba_users WHERE username = 'TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE

-------- -------------- -----------
TEST OPEN


5.OPTIONALLY DELETE TUTORIAL OBJECT

SQL> Drop user TEST cascade;

I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!


 

How to Resolved the Account Locked (Timed) issue?

For application user account, you may come across issue where user account get lock very frequently.

To avoid frequent user account lock issue you can create new profile with unlimited resource limit.

Below are the detail step by step to resolve user account lock issue.

Details:
Oracle Database Version: 11g R2
 Application User: APPUSR
Error: ORA-28000: the account is locked

Login as SYSDBA

SQL> conn /as sysdba

Check the APPSUSR account status.

SQL> SELECT username, account_status,PROFILE FROM dba_users WHERE username= ‘APPUSR’;

USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR LOCKED(TIMED) DEFAULT

Here we can see the account status is LOCKED (TIMED) and the default user’s profile is DEFAULT.

Check the resource limits of DEFAULT profile.

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.

Unlock the user account:

SQL> ALTER USER appusr ACCOUNT UNLOCK;

User altered.

Now check again the status of APPUSR user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;

USERNAME ACCOUNT_STATUS    PROFILE
-------------------- --------------------
APPUSR     OPEN           APPUSR_DEFAULT

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Datapump compression parameter

Here I just like to show How compression datapump parameter working in Oracle 11g R2 ( see following demonstration how size vary from others.)

Default: METADATA_ONLY

Purpose

Specifies which data to compress before writing to the dump file set

Syntax and Description

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

• ALL enables compression for the entire export operation. The ALL option requires that the Oracle Advanced Compression option be enabled.
• DATA_ONLY results in all data being written to the dump file in compressed format. The DATA_ONLY option requires that the Oracle Advanced Compression option be enabled.
• METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
• NONE disables compression for the entire export operation.

Restrictions

• To make full use of all these compression options, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
• The METADATA_ONLY option can be used even if the COMPATIBLE initialization parameter is set to 10.2.

Compression of data (using values ALL or DATA_ONLY) is valid only in the Enterprise Edition of Oracle Database 11g

For Example, See Below Screenshot:

Compression =METADATA_ONLY

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressmeta.dmp directory=DATAPUMP_TEST compression=metadata_only

Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:32:31 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressmeta.dmp directory=DATAPUMP_TEST compression=metadata_only

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               5.007 KB       1 rows
. . exported "TEST"."EMP"                                5.007 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressmeta.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:32:36

Compression =ALL

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressall.dmp directory=DATAPUMP_TEST compression=all

Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:09 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressall.dmp directory=DATAPUMP_TEST compression=all

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               4.687 KB       1 rows
. . exported "TEST"."EMP"                                4.687 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressall.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:11

Compression =DATA_ONLY

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressdata.dmp directory=DATAPUMP_TEST compression=data_only

 Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:30 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Starting "TEST"."SYS_EXPOR_SCHEMA_01":  test/********@TESTDB dumpfile=compressdata.dmpdirectory=DATAPUMP_TEST compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               4.687 KB       1 rows
. . exported "TEST"."EMP"                                4.687 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressdata.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:34

Compression =NONE

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressnone.dmp directory=DATAPUMP_TEST compression=none

 Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:59 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressnone.dmp directory=DATAPUMP_TEST compression=none

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               5.007 KB       1 rows
. . exported "TEST"."EMP"                                5.007 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressnone.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:34:02

Detail File size comparison

Compare how size vary from every compression parameter.

[oracle@NVMBD1BZY150D00 datapump]$ ls -lrth

-rw-r-----. 1 oracle oinstall 192K Nov 26 12:32 compressmeta.dmp

-rw-r-----. 1 oracle oinstall  68K Nov 26 12:33 compressall.dmp
-rw-r-----. 1 oracle oinstall  72K Nov 26 12:33 compressdata.dmp
-rw-r-----. 1 oracle oinstall 196K Nov 26 12:34 compressnone.dmp

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!