How to unlock and reset SAP user in Oracle

REQUIREMENT:

SAP Basis Administrator locked the user SAP* to avoid misuse and also for the auditing purpose.

In some scenarios like client copy, it would be required to use the user SAP* to access the system. 
To achieve this, it is required to unlock and reset the SAP* user in oracle.

STEPS TO BE PERFORMED TO UNLOCK & RESET SAP* USER

[1] Login with the OS user "ora<SID>" (Ex: oratst) and connect to sqlplus

• sqlplus / as sysdba

[2] Search for the SAP schema used

• select OWNER from DBA_TABLES where TABLE_NAME='T000';

[3] Display the uflag and client details from the usr02 table for the user sap*

Note:    (a) UFLAG = other than ‘0’ means account is locked

  (b) UFLAG = 0 means account is not locked

• select UFLAG, BNAME, MANDT from SAPSR3.USR02 where BNAME='SAP*';

[4] Unlock the SAP* user in the client where it’s locked (from above example, sap* user is locked in the client 800)

• update SAPSR3.USR02 set UFLAG=0 where BNAME='SAP*' and MANDT=800;

[5] Reset the SAP* password by deleting the relevant row

• delete from SAPSR3.USR02 where BNAME='SAP*' and MANDT=800;

[6] Set the parameter "login/no_automatic_user_sapstar=0" in default profile      (default.pfl)

Note: This can be done at OS level or from SAP level (RZ10)

• Change the value from 1 to 0

Note: Information of the parameter “login/no_automatic_user_sapstar”

[7] Finally perform the SAP system restart so that above parameter change will be effective

[8] Now we can access the SAP system with user SAP* and password PASS

Difference between System Copy and System Refresh

SYSTEM COPY:

To build a new SAP system (demo/test/training purpose) similar to Production system data. System copy can be of 2 types.

There won’t be any changes made in source system.

[1] Homogeneous system copy:

If the source and target systems are running on the same operating system (OS) and database (DB)

[2] Heterogeneous system copy:

If the source and target systems are running on the different operating system (OS) and database (DB)

SYSTEM REFRESH:

Update the existing system data with production data (The target system should be up and running)

Create new test/demo systems                Update existing system with latest data