Resetting the same old password in oracle or resolving password cannot be reused ORA-28007: or ORA-28003: password verification failed in oracle

Note:Password resetting is user own decision as per requirement.
Not to be copy pasted from this article and should not be used in production without CR.
This steps are described below keeping developement or perf testing environment in mind.

You might be doing this but still getting the above error:

SQL> select USER#,NAME,PASSWORD FROM USER$ where name='IPPADM' ;

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        26 IPPADM                         C42D1F57A9936520

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

Then you might be setting the password to the older value;

alter user IPPADM identified by values 'C42D1F57A9936520';

not working same message.


select * from dba_profiles where profile='DEFAULT' and  RESOURCE_NAME in ('PASSWORD_REUSE_TIME') ;

alter profile DEFAULT limit PASSWORD_REUSE_TIME UNLIMITED;


alter user IPPADM identified by values 'C42D1F57A9936520';

No help not working.

Finally execute the following:

How to set password expiry to unlimited for a schema account in the DB and reset with the same old password:

select profile from dba_users where username='IPPADM'

DEFAULT

Check the different resource and limit for this profile.

select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME Like 'PASS%'

PASSWORD_LIFE_TIME
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
PASSWORD_VERIFY_FUNCTION
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME

Change all the profile limit to unlimited including PASSWORD_VERIFY_FUNCTION which should be set to null.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;




How to set a samll amount for account lock time for a DB user:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 0.001;



How to change the password expiry to unlimited for a service account:

Check the current status of the user say oracle.


[root@pdevspdb ~]# chage -l oracle
Last password change                                    : May 04, 2016
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7


chage -I -1 -m 0 -M 99999 -E -1 username

How to do a proper cleanup of Oracle Stream configuration for a fresh install of Oracle Streams:

How to do a proper cleanup of Oracle stream configuration for a fresh install of Oracle Streams:


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

EXEC DBMS_APPLY_ADM.STOP_APPLY (APPLY_NAME => 'APPLY_STREAM');

EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');

begin
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(PROPAGATION_name  => 'pdsmar_TO_nixar');
end;
/

exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name => 'pdsmar_TO_nixar',drop_unused_rule_sets => TRUE);

EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE(CAPTURE_NAME=>'CAPTURE_STREAM',drop_unused_rule_sets => TRUE);

EXEC DBMS_APPLY_ADM.DROP_APPLY(APPLY_NAME=>'APPLY_STREAM',drop_unused_rule_sets => TRUE);

connect / as sysdba

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.APPLY_q',cascade=>true,drop_unused_queue_table=>true);

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.CAPTURE_q',cascade=>true,drop_unused_queue_table=>true);


drop user STRMADMIN cascade;


----
If above steps  required repeated for multiple environments we can write something line tis:

 BEGIN

    DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

  EXCEPTION
   WHEN OTHERS THEN



     -- Drop the APPLY queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_APPLY_Q1',cascade=>true,drop_unused_queue_table=>true);

     -- Drop the CAPTURE queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_CAPTURE_Q1',cascade=>true,drop_unused_queue_table=>true);
  END;
/

drop user STRMADMIN cascade;