Oracle Database: Unlock user account via SQLPlus

As stated in "Oracle Database: Change Password Expiration" when installing DB I usually change password expiration for DEFAULT profile from 180days to UNLIMITED.

This is because if you create users that uses DEFAULT profile after 180days Oracle gives you:

ORA-28001: the password has expired

So...you can edit DEFAULT profile in two ways:

1) Via Enterprise Manager Console as stated in  Oracle Database: Change Password Expiration
2) Via SQLPlus

In this post I explain how to recover a locked user via SQLPlus and set DEFAULT profile with an unlimited password expiration time.

Connect to the instance:

[oracle@oracle ~]$ sqlplus

SQL> conn sys/password@sid as sysdba

SQL> select username,account_status from dba_users;  


The above command will return all dba users and their corresponding status presenting them in a view similar to this:

USER1 LOCKED&EXPIRED
USER2 OPEN 

Now let's unlock the user(s):

SQL> alter user [user_name] identified by [password] account unlock;

Then modify DEFAULT profile setting the password expiration time to UNLIMITED:

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

That's all!!

Oracle Database: some SQL useful commands

In this brief post I list some SQL/SQLPlus commands I find pretty useful.

-Quick connect as sysdba:

[oracle@oracle ~]$ sqlplus / as sysdba

-Connect as sysdba to a specific db instance:

If more than one instance is running on same machine you can specify to which instance you wish to connect to using:

[oracle@oracle ~]$ sqlplus

Once logged in specify instance with this syntax:

SQL> conn sys/password@sid as sysdba

Connected.

-Drop user and user owned objects:

To drop a user and any database objects owned by user itself just use this command:

SQL> DROP  USER  user  CASCADE;

Oracle Database: How to enable Archive Mode

Some days ago I went into this error while trying to perform a backup of a running 11gR2 database using rman:

ORA-196602: cannot backup or copy active file in NOARCHIVELOG mode

The solution was easy: ArchiveLog needs to be enabled to perform a hot backup of a running database.

Using SQLPlus connect to database.

[oracle@orcl ~]$ /u01/app/oracle/product/11.2.0/db_1/bin/sqlplus sys/password as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 09:32:39 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

Stop and dismount database and shutdown instance.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
Restart instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2228904 bytes
Variable Size            1325403480 bytes
Database Buffers          318767104 bytes
Redo Buffers                7118848 bytes
Database mounted.
Enable ArchiveLog

SQL> alter database archivelog;

Database altered.
Re-open database

SQL> alter database open;

Database altered.
If everything went fine the following command should return Archive Mode as output:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     294
Next log sequence to archive   296
Current log sequence           296