Skip to content

Quick one’s to remember

January 7, 2014

Index

  1. Drop empty datafile From Tablespace (10gR2 NEW FEATURE).
  2. Few details like Last Password changed,Last password Expired and how many times the failed logins were attempted for the user.
  3. How to re-use the old password ?

1) Drop empty datafile From Tablespace (10gR2 NEW FEATURE).
============================================================

Note : ORA-600 [3689] (Doc ID 554762.1)  Corrupt redo from ALTER TABLESPACE DROP DATAFILE , I found this in on 10.2.0.2 version so becareful.

Version affected as below , so do not use the same for below version, its fixed in 10.2.0.5

It’s seen sometimes while adding the datafile to tablespace, by mistake files get added with wrong tablespace name and then realise immediately.
So need not to worry, if its empty then you can easily drop them as below.

SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME from dba_data_files where TABLESPACE_NAME=’MDATA’;

TABLESPACE_NAME                   FILE_ID FILE_NAME
 ------------------------------ ---------- ----------------------------------------
 MDATA                                   5 C:\ORACLE\ORADATA\Q041\MDATA01.DBF

SQL> alter tablespace mdata add datafile ‘C:\ORACLE\ORADATA\Q041\user11_wrongfile.dbf‘ size 2M;

Tablespace altered.

SQL> alter tablespace mdata add datafile ‘C:\ORACLE\ORADATA\Q041\user12_wrongfile.dbf‘ size 2M;

Tablespace altered.

SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME from dba_data_files where TABLESPACE_NAME=’MDATA’;

TABLESPACE_NAME                   FILE_ID FILE_NAME
 ------------------------------ ---------- ---------------------------------------------
 MDATA                                   5 C:\ORACLE\ORADATA\Q041\MDATA01.DBF
 MDATA                                   6 C:\ORACLE\ORADATA\Q041\USER11_WRONGFILE.DBF
 MDATA                                   7 C:\ORACLE\ORADATA\Q041\USER12_WRONGFILE.DBF

SQL> alter tablespace mdata drop datafile ‘C:\ORACLE\ORADATA\Q041\USER11_WRONGFILE.DBF’;

Tablespace altered.

SQL> alter tablespace mdata drop datafile 7;

Tablespace altered.

SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME from dba_data_files where TABLESPACE_NAME=’MDATA’;

TABLESPACE_NAME                   FILE_ID FILE_NAME
 ------------------------------ ---------- ----------------------------------------
 MDATA                                   5 C:\ORACLE\ORADATA\Q041\MDATA01.DBF

In 10.2.0.2 Version after dropping the datafile my standby stopped recovering, 
due to Corrupt redo (Doc ID 554762.1)
===============================================================================
SQL> recover standby database;
ORA-00279: change 550360 generated at 01/07/2014 12:28:57 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\ORADATA\Q041DR\ARCH\Q041DR_ARCH_T1S27R826055072.ARC
ORA-00280: change 550360 for thread 1 is in sequence #27

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3689], [6], [], [], [], [], [], []
ORA-01112: media recovery not started

SQL> select FILE#,NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#,ONLINE_CHANGE# from v$datafile;
FILE# NAME                                          STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE#
----- --------------------------------------------- ------- ------------------ ------------ --------------- --------------
    1 C:\ORACLE\ORADATA\Q041DR\SYSTEM01.DBF         SYSTEM              550360                            0              0
    2 C:\ORACLE\ORADATA\Q041DR\UNDOTBS01.DBF        ONLINE              550360                            0              0
    3 C:\ORACLE\ORADATA\Q041DR\SYSAUX01.DBF         ONLINE              550360                            0              0
    4 C:\ORACLE\ORADATA\Q041DR\USERS01.DBF          ONLINE              550360                            0              0
    5 C:\ORACLE\ORADATA\Q041DR\MDATA01.DBF          RECOVER             550632                            0              0
    6 C:\ORACLE\ORADATA\Q041DR\WRONGFILE.DBF        RECOVER             550677                            0              0
    7 C:\ORACLE\ORADATA\Q041DR\WRONGFILE1.DBF       RECOVER             550756                            0              0

I got the above fixed by recreating the standby control file 
and restart standby in managed recovery mode as below :

Primary 
=========
ALTER DATABASE CREATE STANDBY CONTROLFILE AS  'C:\ORACLE\ORADATA\Q041DR\CONTROL01.CTL';

Standby
========
startup mount;
alter database recover managed standby database disconnect from session;

SQL> select name,status from v$datafile;

NAME                                          STATUS
--------------------------------------------- -------
C:\ORACLE\ORADATA\Q041DR\SYSTEM01.DBF         SYSTEM
C:\ORACLE\ORADATA\Q041DR\UNDOTBS01.DBF        ONLINE
C:\ORACLE\ORADATA\Q041DR\SYSAUX01.DBF         ONLINE
C:\ORACLE\ORADATA\Q041DR\USERS01.DBF          ONLINE
C:\ORACLE\ORADATA\Q041DR\MDATA01.DBF          ONLINE

Primary
========
alter system set log_archive_dest_state_2=DEFER;
alter system set log_archive_dest_state_2=ENABLE;

2) Few details like Last Password changed,Last password Expired and how many times the failed logins were attempted for the user.

SQL> alter user scott account lock;

User altered.

col user_created for a15
col name for a10
col PWD_LAST_CHG for a15
col last_pwd_expired for a16
col last_locked for a15
set line 200
select user#,name,ctime user_created,ptime pwd_last_chg,exptime last_pwd_expired,ltime last_locked,lcount Failed_logins#,status curr_status
from user$ u, user_astatus_map s
where u.ASTATUS=s.status#
and name=’SCOTT’
/

USER# NAME       USER_CREATED    PWD_LAST_CHG    LAST_PWD_EXPIRED LAST_LOCKED     FAILED_LOGINS# CURR_STATUS
 ----- ---------- --------------- --------------- ---------------- --------------- -------------- ---------------
 84 SCOTT      02-APR-10       31-DEC-13       01-SEP-13        07-JAN-14                    0 LOCKED

SQL> alter user scott account unlock;

User altered.

Failed Logins test:
=========================
SQL> conn scott/tig
ERROR:
ORA-01017: invalid username/password; logon denied

USER# NAME       USER_CREATED    PWD_LAST_CHG    LAST_PWD_EXPIRED LAST_LOCKED     FAILED_LOGINS# CURR_STATUS
 ------ ---------- --------------- --------------- ---------------- --------------- -------------- --------------
 84 SCOTT      02-APR-10       31-DEC-13       01-SEP-13        07-JAN-14                    1 OPEN

Note : As soon as you unlock the account, falied logins counter reset to Zero (0) as below.
======
SQL> alter user scott account unlock;

User altered.

USER# NAME       USER_CREATED    PWD_LAST_CHG    LAST_PWD_EXPIRED LAST_LOCKED     FAILED_LOGINS# CURR_STATUS
 ------ ---------- --------------- --------------- ---------------- --------------- -------------- ------------
 84 SCOTT      02-APR-10       07-JAN-14       01-SEP-13        07-JAN-14                    0 OPEN

Password will be expired ON for the user:
==============================================
SQL> select USERNAME,PROFILE,EXPIRY_DATE from  dba_users where USERNAME=’SCOTT’;

USERNAME                       PROFILE                        EXPIRY_DA
 ------------------------------ ------------------------------ ---------
 SCOTT                          DEFAULT                        06-JUL-14

set pages 19
select * From dba_profiles order by PROFILE,RESOURCE_NAME;

SQL> select * From dba_profiles where PROFILE=’DEFAULT’ order by  RESOURCE_NAME;

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

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.

SQL> select * From dba_profiles where PROFILE=’DEFAULT’ and RESOURCE_NAME like ‘%REUSE%’;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
 ------------------------------ -------------------------------- -------- ----------------
 DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
 DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

SQL> select * from user_history$;

no rows selected

SQL> alter profile DEFAULT limit PASSWORD_REUSE_MAX 5;

Profile altered.

SQL> select * From dba_profiles where PROFILE=’DEFAULT’ and RESOURCE_NAME=’PASSWORD_REUSE_MAX’;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
 ------------------------------ -------------------------------- -------- ----------------------
 DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD 5

SQL> alter user mtest identified by mtest;

User altered.

SQL> select u.USERNAME,h.PASSWORD,to_char(h.PASSWORD_DATE,’DD-MON-YY HH24:MI:SS’) PASSWORD_DATE from dba_users u,user_history$ h
where u.USER_ID=h.USER# order by h.PASSWORD_DATE;

USERNAME                       PASSWORD                       PASSWORD_DATE
------------------------------ ------------------------------ ----------------------
MTEST                          95AC92FF8B3B8CD6               07-JAN-14 19:10:10

SQL> alter user mtest identified by lion;

User altered.

SQL> select u.USERNAME,h.PASSWORD,to_char(h.PASSWORD_DATE,’DD-MON-YY HH24:MI:SS’) PASSWORD_DATE from dba_users u,user_history$ h
where u.USER_ID=h.USER# order by h.PASSWORD_DATE;

USERNAME                       PASSWORD                       PASSWORD_DATE
------------------------------ ------------------------------ -------------------
MTEST                          95AC92FF8B3B8CD6               07-JAN-14 19:10:10
MTEST                          AB7FCD89DD6C5405               07-JAN-14 19:10:25

3) How to re-use the old password.

SQL> alter user mtest identified by mtest;
alter user mtest identified by mtest
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> select * From dba_profiles where PROFILE=’DEFAULT’ and RESOURCE_NAME=’PASSWORD_REUSE_MAX’;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
 ------------------------------ -------------------------------- -------- ----------------------
 DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD 5

SQL> alter profile default limit PASSWORD_REUSE_MAX unlimited;

Profile altered.

SQL>  alter user mtest identified by mtest;

User altered.

Reset profile limit to old

SQL> alter profile default limit PASSWORD_REUSE_MAX 5;

Profile altered.

4) yet to come

Advertisements

From → General, Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: