Ways to increase maxextent value.

Without directly updating the oracle database via sql command an admin can use the following:

1. Using tcode SE14 – ABAP Diction : Database Utility, enter the table name and  follow the path:
    – edit->storage parameter->goto->for new creation->ok

2. Use the old SAP tool named sapdba or the new one named brtools.

Setting ORACLE DB into noarchivelog mode

Steps when the database state is down:
1. Login to sqlplus
  -sqlplus /nolog
  -connect / as sysdba
2. The db must be mounted EXCLUSIVE and not open for operation
  –startup mount;
3. Check the log mode status of the database
  –select log_mode from v$database;
4. Setting it to noarchivelog mode
  –alter database noarchivelog;
5. Setting db open for user operation
  –alter database open;
6. Reverting back to archivelog mode
  –alter database archivelog;

Different methods in locking/unlocking a user.

Different methods to Lock or Unlock SAP users

I want to lock all the users in SAP during upgrade, support package installations etc.  I know using SU10 we can do it. Any other alternative ways to lock the users.

Is there a way in SAP to unlock a locked user for a limited time, then automatically after x time set the user back to lock status? 

You can fill in “valid from” and “valid until”, but you cannot say from Monday to Friday from 8 – 12:00 for part time workers. 

Can we schedule to lock all users?

If users get locked, from SU01 you can unlock them. 

Use SU10 to mass lock/unlock the users.

Use address data or authorisation data to get a list of users – select the ones you want and click transfer. Once this is done click on lock or unlock. 

You can also use transaction code EWZ5 to mass lock/unlock the users or execute the program EWULKUSR in SE38

or 

set a profile parameter (login/failed_user_auto_unlock) to unlock at midnight the locked users. 

or 

An ABAP code as below: 

REPORT zreusrlock. DATA: time_limit TYPE sy-datum.
DATA: days TYPE i VALUE 40.

time_limit = sy-datum - days.
UPDATE usr02 SET uflag = 64 WHERE trdat < time_limit.

If time is not specified in the program, you can use SM37 to schedule it as a daily background job with the date and time.

or

Write an sqlplus SQL script that sets all the UFLAG fields in table USR02 to 64 (lock)EXCEPT for the BNAMEs you don’t want locked. When you are done, you can do the same again but change the UFLAG field to 0 (unlock). 

The SQL statement would look like: 

update SAPR3.USR02 set UFLAG = 64 where MANDT = <client number> and BNAME != <don’t lock user 1> and BNAME != <don’t lock user 2>; 

You can replace != with <> if you want. To run this from an OS command line, you would type: 

Unix/Oracle 9:–> sqlplus /nolog @<SQLpath+SQLname> 
NT/Oracle 9: —> sqlplus /nolog @<SQLpath+SQLname> 

To automate, in UNIX the script can be added in the crontab entries. In NT, it can automated via schedule a task.

or

This is another method to UNLOCK ALL users. 

Start Oracle Server manager (If in Oracle Environment) 
– connect internal 
– update sapr3.usr02 set uflag=’0′ where mandt='<client no.>’; 

When users are locked, the uflag is set to 64. Zero (0) means user in not locked.

To verify, use a sql query statement: 

select bname, uflag from sapr3.usr02 where mandt='<client no.>’; 

Is there a way to set a list of users that cannot be locked, even if we try to lock them manually, and even if they fail connection ( wrong password )?

Increase this parameter in SAP Instance profile: 

login/fails_to_user_lock = 6 (max is 99 wrong attempts, i.e, value 99). Currently you have a value of 3. 

login/failed_user_auto_unlock (for your midnight unlocking). 

Changing password of sap3 using brconnect

Below is the syntax in changing  sapr3 password using the brconnect tool.

Syntax: brconnect -u system -f chpass sapr3 -p <new password>