Wikipedia

Search results

/etc/security/limits.conf - Shell limits for the oracle User

Setting Shell Limits For Grid and Oracle Users

ulimit is to provide control over system resources. Setting limits to allow the user to use resources as much as really needed. so the system resources can be shared by all users fairly.

To view current ulimit settings by typing the command “ulimit -a”.

$ ulimit -a

The command “ulimit -n” shows the maximum number of open file descriptors.

$ ulimit -n
1024

The command “ulimit -u” shows the maximum number of processes.

$ ulimit -u
2047

Note:

When the limits.conf file is changed, these changes take effect immediately. However, if the grid or oracle users are logged in, then these changes will not take effect until you log these users out and log them back in. You must do this before you attempt to use these accounts to install.

To improve the performance of the software, you must increase the following shell limits for the oracle user:

Shell Limit
Item in limits.conf
Hard Limit
Maximum number of open file descriptorsnofile65536
Maximum number of processes available to a single usernproc16384

How does ulimit work?

Make sure file /etc/pam.d/login contains following line:

session required pam_limits.so

When a user logon, shell loads the pam_limits.so module. The pam_limits.so module is configured to read the file /etc/security/limits.conf. For example, the following lines in /etc/security/limits.conf file for oracle user. GI( grid ) user should have the similar if GI is to be installed and configured.

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384

A soft limit can be increased until it reaches the value of a hard limit. Once a hard limit is set for a value, it cannot be increased further. So the hard limit is the upper cap of restriction for a value. By default, the users specified in /etc/security/limits.conf file DO NOT get the maximum resources as specified by the type “hard”. The users have to request for this explicitly. For this purpose and to enforce the ulimit settings across logins, it is required to edit the file /etc/profile.
Editing this file is also important because shells like bash and ksh may interpret ulimit settings differently. For the Bourne, Bash, or Korn shell, add the following lines in the /etc/profile file (or the /etc/profile.local file on SUSE systems):

if [ $USER = “oracle” ]; then
if [ $SHELL = “/bin/ksh” ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

For the C shell, add the following lines in the /etc/csh.login file (or the /etc/csh.login.local file on SUSE systems):

if ( $USER == “oracle” ) then
limit maxproc 16384
limit descriptors 65536
endif

Another way to achieve this by setting soft and hard values same in /etc/security/limits.conf:

oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

We also see some users like to configure them in local user profile, for bash shell, edit $HOME/.bash_profile file to copy and paste the following commands for oracle’s bash shell:

su – oracle
cat >> $HOME/.bash_profile << EOF
ulimit -n 65536 -u 16384
EOF

It Is not recommend to set the “hard” limit for nofile for the oracle user equal to /proc/sys/fs/file-max.This may prevent users from logging in as the system cannot open any PAM modules that are required for the login process.

From the above examples, we can see resource limits like the maximum number of user processes can be set from three different places, which are /etc/security/limits.conf, /etc/profile and $HOME/.bash_profile. They have the priority from high to low in order. When setting the ulimit value, we can’t set a bigger value in $HOME/.bash_profile than the value set in /etc/profile as we can’t set a bigger one in /etc/profile than the value set in /etc/security/limits.conf. Otherwise, we could encounter those kind of messages as below:

-bash: ulimit: open files: cannot modify limit: Operation not permitted
-bash: ulimit: max user processes: cannot modify limit: Operation not permitted

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

RMAN backup run encountered “RMAN-06059: expected archived log not found, loss of archived log compromises recoverability”

Solution for the mentioned error is to Crosscheck your archive logs from RMAN prompt and delete those expired logs which are not needed anymore.

While crosscheck archivelogs, RMAN will mark archive log as expired in its repository (i.e. controlfile or catalog) those not physically present in server so we can go ahead delete expired archives from controlfile or catalog repository.

Error log:

MAN> RUN
 2> {
 3> ALLOCATE CHANNEL backup1 DEVICE TYPE DISK FORMAT 'Z:\RMAN\%U';
 4> BACKUP DATABASE PLUS ARCHIVELOG;
 5> }
using target database control file instead of recovery catalog
 allocated channel: backup1
 channel backup1: SID=70 device type=DISK
Starting backup at 10-SEP-14
 current log archived
 released channel: backup1
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of backup plus archivelog command at 09/10/2014 18:22:35
 RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
 ORA-19625: error identifying file E:\ORACLE\--ORACLE_SID--\ORAARCH\1_35338_778192583.DBF
 ORA-27041: unable to open file
 OSD-04002: unable to open file
 O/S-Error: (OS 2) The system cannot find the file specified.


## Physically not available archive logs are marked as expired which are not required for recovery.
RMAN> CROSSCHECK ARCHIVELOG ALL;


##delete all archive logs which are marked as expired while crosscheck.
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

SYSTEM user cannot change the SYS password in Oracle 12.2, later release

12.2 - Users Being granted The ALTER USER System Privilege Cannot Change SYS Password 

In Oracle 12.2.0.1 and later, the user who is granted the ALTER USER system privilege is not able to change the SYS password as of Oracle RDBMS 12.2.0.1. 


[oracle@akb ~]$ sqlplus system/Orapass_777

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 09:11:38 2017

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

Last Successful login time: Tue May 09 2017 08:53:44 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select privilege from session_privs where privilege like 'ALTER USER%';

PRIVILEGE
----------------------------------------
ALTER USER

SQL> alter user sys identified by NewPassword_123;
alter user sys identified by NewPassword_123
*
ERROR at line 1:

ORA-01031: insufficient privileges

This was possible in the earlier versions :

[oracle@akb11 ~]$ sqlplus system/Orapass_999

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 9 09:13:50 2017

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

Last Successful login time: Tue Apr 18 2017 17:13:10 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select privilege from session_privs where privilege like 'ALTER USER%';

PRIVILEGE
----------------------------------------
ALTER USER

SQL> alter user sys identified by Oracle_555;

User altered.


Cause : This is the expected behaviour.


SOLUTION : 


As of Oracle RDBMS 12.2 a user with ALTER USER privilege will no longer be able to alter any of the attributes(password, profile, quota, etc.)  of the  SYS user.

There is also, a workaround to address this issue. We can create a procedure under sys schema that include alter user command to change sys password and grant execute on this procedure to system user. Then run the procedure as system user and change the sys password as explained below.

 ## Test ##

show user;

sys

create procedure sys.changepwd(passwd in varchar2)
as
begin
execute immediate 'alter user sys identified by '||passwd;
end;
/

grant execute on sys.changepwd to system;

SQL> conn system/XXXX
Connected.
SQL>
SQL>
SQL>
SQL> exec sys.changepwd('NewPWD');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> conn sys/Newpwd@db12201nc as sysdba
Connected.

12c: ORA-28040 After Upgrade: No Matching Authentication Protocol

12c: ORA-28040 After Upgrade: No Matching Authentication Protocol


After upgrade to the version 12c database, the following error 'ORA-28040: No matching authentication protocol exception' are thrown when attempting to connect from remote 
clients:] 

Version 12.1:

The default setting for the new parameters is 11.  Any client that attempts to connect must 
be at version 11 or higher unless these parameters are explicitly set in the server side sqlnet.ora file.

Version 12.2 note:

The default for the SQLNET.ALLOWED_LOGON_VERSION_SERVER setting has changed in 12.2 from 11 to 12.
See:  https://docs.oracle.com/database/122/DBSEG/configuring-authentication.htm#DBSEG33223
Important note for 12.2:  If your client is not at least 11.2.0.3 or includes the CPUOCT2012 patch you will not be able 
to use the 12 setting.

Typically, the sqlnet.ora file that would be referenced by the database is located in RDBMS_HOME/network/admin.

Soultion :

Set these parameters at the lowest version level that is required in your environment.  
For example:  All clients at version 10 or higher would require this setting:

 SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

Note : SQLNET.ALLOWED_LOGON_VERSION_CLIENT would be necessary on the server when the database is 'acting' as a client.  Such as the case of a database link.

There is  no need to restart either the listener or the database after this change.  See additional notes below.