ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

There are many ways to overcome this issue

Method 1 - Increase parameter

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Check current configuration of profile

SQL> col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' 
and a.username='APP01';

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- -------------------
APP01     DEFAULT             SESSIONS_PER_USER                500

3. Increase parameter value

SQL> ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 600;

Profile altered.

4. Makesure parameter has been changed successfully

SQL> col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username='APP01';

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- --------------------
APP01     DEFAULT             SESSIONS_PER_USER                600

Method 2 - Kill Idle sessions based on duration

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Select Idle connection which are inactive more than 12 hours

set heading off;
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where username='APP01' and status = 'INACTIVE' and last_call_et/60/60 >= 12;

3. Kill Idle sessions from above output

alter system kill session '100,35175' immediate;
alter system kill session '1768,53954' immediate;
alter system kill session '2129,15490' immediate;
alter system kill session '2251,6956' immediate;
alter system kill session '3331,17248' immediate;
...............

4. Makesure Connection count after killed Idle sessions

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
120          APP01

Method 3 - Kill sessions which create connection from particular machine/program

1. Check Current connection count

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
500          APP01

2. Check hosts which make multiple sessions

select machine,program,username,count(*)
from v$session 
group by machine,program,username
order by 4 desc;

machine  program  username  count(*)
-------  -------  --------  --------
e5a07b31a6e5	JDBC Thin Client	APP01	470
demo3021132.isaaviation.net	JDBC Thin Client	APP02	185

3. Kill from above output

begin
for s in(
    select *
    from v$session
    where machine = 'e5a07b31a6e5' and username = 'APP01'
)
loop
    begin
    execute immediate 'alter system kill session '''||s.sid||','||s.serial#||''' immediate';
    exception when others then
        null;
    end;
end loop;
end;

4. Makesure Connection count after killed Idle sessions

SQL> SELECT count(*) as connections,username FROM v$session where username='APP01' GROUP BY username;

CONNECTIONS  USERNAME
------------ ---------
120          APP01

Data Guard Broker Configuration in 19c

Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration

From Primary

1. Enable Broker configuration parameters

SQL> alter system set dg_broker_start=true;

SQL> alter system set local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.210.135)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = smsprod_bos03)))';

2. Add entry for data guard broker on listener.ora

[oracle@bo3upsmsxodb01 ~]$ vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bo3upsmsxodb01.pearsoncmg.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = smsprod_bos03_DG)
      (ORACLE_HOME = /data01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = smsprod)
    )
  )

3. Reload listener to effective the changes

[oracle@bo3upsmsxodb01 ~]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:28:17

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521)))
The command completed successfully

4. Makesure service for dataguard has been initiated

[oracle@bo3upsmsxodb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:28:22

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-JAN-2023 06:55:52
Uptime                    0 days 21 hr. 32 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /data01/app/oracle/diag/tnslsnr/bo3upsmsxodb01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "smsprodXDB" has 1 instance(s).
  Instance "smsprod", status READY, has 1 handler(s) for this service...
Service "smsprod_bos03" has 1 instance(s).
  Instance "smsprod", status READY, has 1 handler(s) for this service...
Service "smsprod_bos03_DG" has 1 instance(s).
  Instance "smsprod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

From Standby

1. Enable Broker configuration parameters

SQL> alter system set dg_broker_start=true;

SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.210.38)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = smsprod_stby)))';

2. Add entry for data guard broker on listener.ora

[oracle@bo3upsmsxdrs01 ahussmo]$ vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bo3upsmsxdrs01.pearsoncmg.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = smsprod_stby_DG)
      (ORACLE_HOME = /data01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = smsprod)
    )
  )

3. Reload listener to effective the changes

[oracle@bo3upsmsxdrs01 ahussmo]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:33:14

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521)))
The command completed successfully

4. Makesure service for dataguard has been initiated

[oracle@bo3upsmsxdrs01 ahussmo]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:33:18

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                20-JAN-2023 22:33:24
Uptime                    1 days 5 hr. 59 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /data01/app/oracle/diag/tnslsnr/bo3upsmsxdrs01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "smsprod_stby" has 1 instance(s).
  Instance "smsprod", status READY, has 1 handler(s) for this service...
Service "smsprod_stby_DG" has 1 instance(s).
  Instance "smsprod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enable Data guard configuration

1. Login to data guard command line interface via sys user

[oracle@bo3upsmsxodb01 ~]$ dgmgrl sys/f1Nal#cak3

2. Create new configuration

DGMGRL> create configuration 'smsprod_bos03_DG_config' as primary database is 'smsprod_bos03' connect identifier is smsprod;
Configuration "smsprod_bos03_DG_config" created with primary database "smsprod_bos03"

3. Make sure primary configuration is available

DGMGRL> show configuration;

Configuration - smsprod_bos03_DG_config

  Protection Mode: MaxPerformance
  Members:
  smsprod_bos03 - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

4. Reset 'LOG_ARCHIVE_DEST_n' to avoid 'ORA-16698' Errors

[oracle@bo3upsmsxodb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 22 05:25:38 2023
Version 19.15.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> sho parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby

SQL> alter system set LOG_ARCHIVE_DEST_2='';

5. Add standby database to dataguard broker

DGMGRL> Add database 'smsprod_stby' as connect identifier is smsprod_stby;
Database "smsprod_stby" added

6. Make sure both primary and standby configurations are available

DGMGRL> show configuration;

Configuration - smsprod_bos03_DG_config

  Protection Mode: MaxPerformance
  Members:
  smsprod_bos03 - Primary database
    smsprod_stby  - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

7. Reset 'LOG_ARCHIVE_DEST_n' to valid value

[oracle@bo3upsmsxodb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 22 05:25:38 2023
Version 19.15.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby';

SQL> sho parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby

8. Enable configuration

DGMGRL> enable configuration;

Enabled.

9. Verify configuration status is 'SUCCESS'

DGMGRL> show configuration

Configuration - smsprod_bos03_DG_config

  Protection Mode: MaxPerformance
  Members:
  smsprod_bos03 - Primary database
    smsprod_stby  - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 38 seconds ago)

Configure SSL on Oracle E-Business Suite 12.2 using Self-Signed Certificate

1. Creating wallet

1. Surce run edition environemnt variable

$ source EBSapps.env run

2. Navigate to s_web_ssl_directory/Apache, If not create the the directory

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs

mkdir Apache

3. Create an Auto-Login Wallet on Apache directory

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache

$ orapki wallet create -wallet ./ -auto_login_only

4. Create a Certificate Request

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache

$ orapki wallet add -wallet ./ -dn 'CN=devoracleebs@nsb.local' -asym_alg RSA -keysize 2048 -sign_alg sha256 -self_signed -validity 3650 -auto_login_only

2. Modify the Oracle HTTP Server Wallet

1. Copy wallet into $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OHS/<s_ohs_component>/keystores/default

cd /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OHS/EBS_web_DEVEGL/keystores/default

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso  .

2. Copy wallet into $EBS_DOMAIN_HOME/opmn/<s_ohs_instance_loc>/<s_ohs_component>/wallet

cd /finsys/DEVEGL/fs1/FMW_Home/user_projects/domains/EBS_domain_DEVEGL/opmn/EBS_web_DEVEGL_OHS1/EBS_web_DEVEGL/wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

3. Copy wallet into $EBS_DOMAIN_HOME/opmn/<s_ohs_instance_loc>/wallet

cd /finsys/DEVEGL/fs1/FMW_Home/user_projects/domains/EBS_domain_DEVEGL/opmn/EBS_web_DEVEGL_OHS1/wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

4. Copy wallet into $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OHS/<s_ohs_component>/proxy-wallet

cd /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OHS/EBS_web_DEVEGL/proxy-wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

5. Copy wallet into $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OPMN/opmn/wallet

cd /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OPMN/opmn/wallet

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso .

3. Configure Protocol and Cipher Suite for FMW Internal Communication

1. shutdown all services

./adstpall apps/apps

2. Edit opmn.xml from $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OPMN/opmn

vi /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OPMN/opmn/opmn.xml

change
<ssl enabled="true"
     wallet-file="<path to the wallet file>"/>

to
<ssl enabled="true"
    wallet-file="/finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OPMN/opmn/wallet" ssl-versions="TLSv1.0,TLSv1.1,TLSv1.2"
ssl-ciphers="SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_128_CBC_SHA"/>

3. Edit admin.conf from $FMW_HOME/webtier/instances/<s_ohs_instance_loc>/config/OHS/<s_ohs_component>

vi /finsys/DEVEGL/fs1/FMW_Home/webtier/instances/EBS_web_DEVEGL_OHS1/config/OHS/EBS_web_DEVEGL/admin.conf

change
SSLCipherSuite SSL_RSA_WITH_RC4_128_SHA
SSLProtocol nzos_Version_1_0 nzos_Version_3_0

to
SSLCipherSuite HIGH:MEDIUM
SSLProtocol nzos_Version_1_0 nzos_Version_1_1 nzos_Version_1_2

4. Update the Context File and Config Files

1. Start Application services

cd $ADMIN_SCRIPT_HOME
./adstrtall apps/apps

2. Login to Fusion Middlewere EM console via http://<host_name>:<port>/em

http://192.168.133.10:7021/em

3. Select web tier target under the EBS domain

4. Navigate to Administration, then Advanced Configuration

   

5. Select ssl.conf file for edit

6. Update the Listen and the VirtualHost default port as follows

change
Listen 4463 to Listen 4443
VirtualHost _default_:4463

to
Listen 4443
VirtualHost _default_:4443

change
SSLProtocol    -all +TLSv1 +SSLv3
SSLCipherSuite HIGH:MEDIUM:!aNULL:+SHA1:+MD5:+HIGH:+MEDIUM

to
SSLProtocol    nzos_Version_1_0 nzos_Version_1_1 nzos_Version_1_2
SSLCipherSuite HIGH:MEDIUM

7. Click on Apply

8. Run following command to propagate the changes made through the FMW console to the context file

$ perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE
Enter the APPS user password:
Enter the WebLogic AdminServer password:
./adstrtall apps/apps

Review the adSyncContext.log for the changes that have been picked up and made to the context file

9. Edit Application contex file as per below

$vi $CONTEXT_FILE

change
<url_protocol oa_var="s_url_protocol">http</url_protocol>

to
<url_protocol oa_var="s_url_protocol">https</url_protocol>

change
<local_url_protocol oa_var="s_local_url_protocol">http</local_url_protocol>

to
<local_url_protocol oa_var="s_local_url_protocol">https</local_url_protocol>

change
<webentryurlprotocol oa_var="s_webentryurlprotocol">http</webentryurlprotocol>

to
<webentryurlprotocol oa_var="s_webentryurlprotocol">https</webentryurlprotocol>

change
<activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">8020</activewebport>

to
<activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">4443</activewebport>

change
<web_ssl_port oa_var="s_webssl_port" oa_type="PORT" base="4443" step="1" range="-1" label="Web SSL Port">4443</web_ssl_port>

to
<web_ssl_port oa_var="s_webssl_port" oa_type="PORT" base="4443" step="1" range="-1" label="Web SSL Port">4443</web_ssl_port>

change
<httpslistenparameter oa_var="s_https_listen_parameter">4443</httpslistenparameter>

to
<httpslistenparameter oa_var="s_https_listen_parameter">4443</httpslistenparameter>

change
<login_page oa_var="s_login_page">http://devoracleebs.nsb.local:8020/OA_HTML/AppsLogin</login_page>

to
<login_page oa_var="s_login_page">https://devoracleebs.nsb.local:4443/OA_HTML/AppsLogin</login_page>

change
<externURL oa_var="s_external_url">http://devoracleebs.nsb.local:8020</externURL>

to
<externURL oa_var="s_external_url">https://devoracleebs.nsb.local:4443</externURL>

5. Run Autoconfig on application tier

cd $ADMIN_SCRIPT_HOME
adautocfg.sh

6. Re-start Application tier services

cd $ADMIN_SCRIPT_HOME
./adstpall.sh apps/apps
./adstrtal.sh apps/apps

7. Access the system via following URL

https://devoracleebs.nsb.local:4443/OA_HTML/AppsLocalLogin.jsp

Enable TLS for WLS AdminServer 1. Setup a WebLogic Server Identity Keystore

1. Surce run edition environemnt variable

$ source EBSapps.env run

2. set an alias for orapki in order to pickup the executable from the $FMW_HOME and not the one under the 10.1.2 home

$ alias orapki=$FMW_HOME/oracle_common/bin/orapki

3. Create following directories

mkdir /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts -p

4. Copy wallet file into newly created directory

cp /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/certs/Apache/cwallet.sso /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts

5. Copy following to newly created directory

cp /finsys/DEVEGL/fs1/EBSapps/comn/util/jdk64/jre/lib/security/cacerts /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts

2. Convert the Oracle Wallet to a JKS Keystore

1. Change directory to wlsSSLArtifacts

cd /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts

2. Run the orapki command

orapki wallet pkcs12_to_jks -wallet ./ -jksKeyStoreLoc ./ewallet.jks -jksKeyStorepwd Ebs#1234

3. The ewallet.jks file will be generate. Extract alias using following command and note down the alias

keytool -list -keystore ewallet.jks -v

3. Configure SSL on WLS

1. Take a backup of $EBS_DOMAIN_HOME/config/config.xml file

2. Use the adstpall.sh script to stop everything on the run file system and start only admin server

cd $ADMIN_SCRIPT_HOME
adstpall.sh
./adadminsrvctl.sh start

3. login to the admin console via http://192.168.133.10:7021/console

4. In the WebLogic Server Administration console, under the Domain Configuration, click on Environment and Servers

5. Click Lock & Edit

6. Click on the AdminServer to configure

7. Under the Configuration tab, click on the Keystores sub-tab

8. Click Change next to the Keystores setting

9. Select the Custom Identity and Custom Trust option and click Save

10. Enter the identity details

For example:
Custom Identity Keystore: /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts/ewallet.jks
Custom Identity Keystore Type: JKS (This must be in uppercase.)
Custom Identity Keystore Passphrase: This must match the password used from the orapki command previously in Step 2. Ebs#1234
Confirm Custom Identity Keystore Passphrase

11. Enter the trust information

For example:
Custom Trust Keystore: /finsys/DEVEGL/fs_ne/inst/DEVEGL_devoracleebs/wlsSSLArtifacts/cacerts
Custom Trust Keystore Type: JKS
Custom Trust Keystore Passphrase: Enter the cacerts keystore password. See The cacerts Certificates File, keytool. initial password is changeit
Confirm Custom Trust Keystore Passphrase: Confirm the cacerts keystore password

12. Click Save

13. Click the SSL tab

14. Enter the identity details

For example:
Private Key Alias: orakey. This would correspond to the alias extracted from the keystore previously in Step 2.
Private Key Passphrase: This must match the password used from the orapki command previously in Step 2. Ebs#1234
Confirm Private Key Passphrase

15. Click Save

16. Click the General tab

17. Select SSL Listen Port Enabled check box

18. Enter the SSL Listen Port

Note: The SSL Listen Port base values are available through the context variable s_wls_admin_sslport. Based on the server type, you need to choose the corresponding port value for the SSL Listen Port. You need to manually calculate SSL Listen Port value. For simplicity, the default SSL Listen port value is 1 prefixed with the server default Non SSL Listen port value.
For example, for port pool 0, the AdminServer Non SSL Listen port is 7001, so the AdminServer SSL Listen port will be 17001. 17021

19. Click Save

20. Select the SSL tab

21. Select the Advanced option and then perform the following

Set the Hostname Verification to Custom Hostname Verifier and the Custom Hostname Verifier field to weblogic.security.utils.SSLWLSWildcardHostnameVerifier

22. Click Save

23. Click Activate Changes

Post-Configuration Tasks

1. Restart everything including the Admin and Managed Servers using the adstpall.sh and adstrtal.sh

./adstpall.sh
./adstrtal.sh

2. Sync Changes to the Context File

perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

following changes will be reflect in contex file.
s_custom_trustKeyStoreFile - complete path of trust keystore
s_wls_admin_sslEnabled - true
s_wls_admin_sslport - AdminServer SSL port.
In the case where these listed context variables are not already populated, restart the middle tier services and re-run the following command:

$ perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

Change Backup Retention Policy on Standby Database

Problem

Following error occured when try to change backup policies on standby db

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 days;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 09/08/2022 08:08:11
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file;

Solution

From Primary

1. Change backup polices as per the requirement

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 days;

2. Create standby controlfile and copy to standby database

SQL> alter database create standby controlfile as '/tmp/stby.ctl';
Database altered.

From Standby

1. Cancel redo apply

SQL> alter database recover managed standby database cancel;

2. Backup exisitng control files and replace newly copied controlfiles from primary

cd /u02/oradata/PROD_STBY/controlfile
mv control01.ctl control01.ctl_bkp
mv control02.ctl control02.ctl_bkp
cp /tmp/stby.ctl control01.ctl
cp /tmp/stby.ctl control02.ctl

3. Mount standby database

SQL> alter database mount;

4. Change standby file managemnet to manual inorder change datafiles

alter system set standby_file_management='MANUAL';

5. Rename the datafile names to same as standby datafiles

SQL> select name from v$datafile;
NAME
--------------------------------------
/u02/oradata/PROD/datafile/system01.dbf
/u02/oradata/PROD/datafile/syaux01.dbf
/u02/oradata/PROD/datafile/undo01.dbf
/u02/oradata/PROD/datafile/users01.dbf
/u02/oradata/PROD/datafile/data01.dbf
/u02/oradata/PROD/datafile/index01.dbf

alter database rename file '/u02/oradata/PROD/datafile/system01.dbf' to '/u02/oradata/PROD_STBY/datafile/system01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/syaux01.dbf' to '/u02/oradata/PROD_STBY/datafile/syaux01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/undo01.dbf' to '/u02/oradata/PROD_STBY/datafile/undo01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/users01.dbf' to '/u02/oradata/PROD_STBY/datafile/users01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/data01.dbf' to '/u02/oradata/PROD_STBY/datafile/data01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/index01.dbf' to '/u02/oradata/PROD_STBY/datafile/index01.dbf';

6. Change back standby file managemnet to auto

alter system set standby_file_management='AUTO';

7. Start redo apply

SQL> alter database recover managed standby database disconnect from session;

ORA-30554: function-based index is disabled

Problem

Following error occured from DB when access the table

Rejected - Error on table GPS.MAIN_PROD.
ORA-30554: function-based index GPS.FN_IDX_MAIN_PROD_1 is disabled

Solution

Check the table and index status

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'MAIN_PROD';

OBJECT_NAME  OBJECT_TYPE  OWNER   STATUS
-----------  -----------  ------- ---------
MAIN_PROD      TABLE       GPS     VALID

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'FN_IDX_MAIN_PROD_1';

OBJECT_NAME         OBJECT_TYPE  OWNER  STATUS
------------       ------------ ------ -------
FN_IDX_MAIN_PROD_1    INDEX      GPS     VALID

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     DISABLED

Re-build index which was disabled

SQL> alter index gps.FN_IDX_MAIN_PROD_1 rebuild;

Index altered.

Check the index status again confirm whether issue is sorted

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     ENABLED

Resize Redolog files in Data guard environment

Primary

1. Check the current details of redolog and standby redolog files

SQL> select group#,bytes/1024/1024 size_,status from v$log;

    GROUP#      SIZE_   STATUS
---------- ------------  ----------
         5       512    INACTIVE
         6       512    INACTIVE
         7       512    CURRENT

SQL> select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1            512
         2            512
         3            512

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /data01/oradata/smsprod/stby_redo04a.log
         2 /data01/oradata/smsprod/stby_redo05a.log
         3 /data01/oradata/smsprod/stby_redo06a.log
         5 /data01/oradata/smsprod/redo05a.log
         6 /data01/oradata/smsprod/redo06a.log
         7 /data01/oradata/smsprod/redo07a.log

2. Drop existing redolog files which is not in 'CURRENT' state

SQL> alter database drop logfile group 1;              
Database altered.

SQL> alter database drop logfile group 2;
Database altered.  

3. Create redologs

SQL> alter database add logfile group 1 ('/data01/oradata/smsprod/onlinelog/redo01.log','/data02/oradata/smsprod/onlinelog/redo01.log') size 1024M;
Database altered.

SQL> alter database add logfile group 2 ('/data01/oradata/smsprod/onlinelog/redo02.log','/data02/oradata/smsprod/onlinelog/redo02.log') size 1024M;
Database altered.

4. Switch logfiles manually until Group 3 becomes INACTIVE state

SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint global;
System altered.

5. Makesure group 3 is INACTIVE

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

6. Drop group 3 and re-create

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3 ('/data01/oradata/smsprod/onlinelog/redo03.log','/data02/oradata/smsprod/onlinelog/redo03.log') size 1024M;
Database altered.

7. Create standby logfile. Standby logfile should be same size of redolog files and count should be redolog +1 (Here need to create 4 standby logfiles as we have 3 redolog files

SQL> alter database add standby logfile group 5 ('/data01/oradata/smsprod/onlinelog/stby_redo05.log','/data02/oradata/smsprod/onlinelog/stby_redo05.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 6 ('/data01/oradata/smsprod/onlinelog/stby_redo06.log','/data02/oradata/smsprod/onlinelog/stby_redo06.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 7 ('/data01/oradata/smsprod/onlinelog/stby_redo07.log','/data02/oradata/smsprod/onlinelog/stby_redo07.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 8 ('/data01/oradata/smsprod/onlinelog/stby_redo08.log','/data02/oradata/smsprod/onlinelog/stby_redo08.log') size 1024M;
Database altered.

Standby

1. Cancel Redo Apply

SQL> alter database recover managed standby database cancel;
Database altered.

2. Change standby management to manual

SQL> show parameter standby_file_management

NAME                           TYPE        VALUE
--------------------------- ----------- ------------
standby_file_management                  string            AUTO

SQL> alter system set standby_file_management=manual;
Database altered.

3. Check existing files

SQL> show parameter standby_file_management

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         7 ACTIVE
         1 UNUSED

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         5 UNASSIGNED
         6 UNASSIGNED

4. Clear logfiles which is 'ACTIVE' state

SQL> alter database clear logfile group 7;
Database altered.

5. Drop All redolog and standby log files

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 7;
Database altered.

SQL> alter database drop logfile group 5;
Database altered.

SQL> alter database drop logfile group 6;
Database altered.

6. Re-create same as primary

SQL> alter database add logfile group 1 ('/data01/oradata/smsprod/onlinelog/redo01.log','/data02/oradata/smsprod/onlinelog/redo01.log') size 1024M;
Database altered.

SQL> alter database add logfile group 2 ('/data01/oradata/smsprod/onlinelog/redo02.log','/data02/oradata/smsprod/onlinelog/redo02.log') size 1024M;
Database altered.

SQL> alter database add logfile group 3 ('/data01/oradata/smsprod/onlinelog/redo03.log','/data02/oradata/smsprod/onlinelog/redo03.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 5 ('/data01/oradata/smsprod/onlinelog/stby_redo05.log','/data02/oradata/smsprod/onlinelog/stby_redo05.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 6 ('/data01/oradata/smsprod/onlinelog/stby_redo06.log','/data02/oradata/smsprod/onlinelog/stby_redo06.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 7 ('/data01/oradata/smsprod/onlinelog/stby_redo07.log','/data02/oradata/smsprod/onlinelog/stby_redo07.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 8 ('/data01/oradata/smsprod/onlinelog/stby_redo08.log','/data02/oradata/smsprod/onlinelog/stby_redo08.log') size 1024M;
Database altered.

7. Change standby file management parameter

SQL> alter system set standby_file_management=auto;
System altered.

8. Start redo apply

SQL> alter database recover managed standby database disconnect from session;
Database altered.

Install Oracle Client In Silent Mode on Linux 8

1. Install preinstall package for Oracle 19c

yum install -y oracle-database-preinstall-19c

2. If you using RHEL8, then retreive RPM's from repository as follows

curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

yum -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

3. Download oracle 19c database software

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

4. Copy downloaded file to the server and unzip under oracle_home

mkdir /u01/app/oracle/product/19.3.0 -p
cd /u01/app/oracle/product/19.3.0 
unzip ~/LINUX.X64_193000_client.zip

5. Modify Response file as below

/usr1/app/oracle/product/client/response/client_install.rsp

UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/usr1/app/oracle/product/oraInventory
ORACLE_HOME=/usr1/app/oracle/product/19.3.0/
ORACLE_BASE=/usr1/app/oracle/product
oracle.install.client.installType=Administrator

6. To avoid bug '[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'' in Oracle 19c on RHEL8

export CV_ASSUME_DISTID=OEL7.8

7. Install DB software as silent mode

./runInstaller -showProgress -silent  -responseFile /usr1/app/oracle/product/19.3.0/client/response/client_install.rsp
Prepare in progress.
..................................................   7% Done.

Prepare successful.

Copy files in progress.
..................................................   12% Done.
..................................................   17% Done.
..................................................   22% Done.
..................................................   27% Done.
..................................................   32% Done.
..................................................   37% Done.
..................................................   42% Done.
..................................................   47% Done.
..................................................   52% Done.
........................................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   57% Done.
....................
Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup in progress.
..........
Finish Setup successful.
The installation of Oracle Client 19c was successful.
Please check '/usr1/app/oracle/oraInventory/logs/silentInstall2023-01-20_11-07-59AM.log' for                                                                                                 more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   67% Done.

Prepare for configuration steps in progress.

Prepare for configuration steps successful.
..................................................   82% Done.

Oracle Client Configuration in progress.

Oracle Client Configuration successful.
..................................................   96% Done.

As a root user, execute the following script(s):
        1. /usr1/app/oracle/oraInventory/orainstRoot.sh



Successfully Setup Software with warning(s).
..................................................   100% Done.

8. Execute root.sh from root user

/usr1/app/oracle/oraInventory/orainstRoot.sh

HugePages Configuration for Oracle on Linux (x86-64)

For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle systems running on x86-64 architectures. However their is a limitation by Oracle, because Automatic Memory Management (AMM) does not support HugePages. If you already use AMM and MEMORY_TARGET is set you have to disable it and switch back to Automatic Shared Memory Management (ASMM). That means set SGA_TARGET and PGA_AGGREGATE_TARGET.

1. To determine how much memory currently using to support the page table

[root@bo3udsmsxodb01 ~]# grep PageTables /proc/meminfo
PageTables:        9764 kB

2. Check current HugePage usages

[root@bo3udsmsxodb01 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

3. This script is intended to compute values for the recommended HugePages configuration

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating the overall size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.14') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.18') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '5.4') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
esac

# End

4. Create a text file named hugepages_settings.sh and Copy above contents

5. Make hugepages_settings.sh as executable file

[root@bo3udsmsxodb01 ~]# chmod +x hugepages_settings.sh

6. Verify all the below requiremnet has been met to execute the hugepages_settings.sh

• Oracle Database instance(s) are up and running 
• Automatic Memory Management (AMM) is not setup 
• The shared memory segments can be listed by command "ipcs -m" 
• Oracle Linux Package 'bc' installed

7. Run hugepages_settings.sh

[root@bo3udsmsxodb01 ~]# ./hugepages_settings.sh

8. Script return the computed value of HugePages.

[root@bo3udsmsxodb01 ~]# Recommended setting: vm.nr_hugepages = 16386

9. Modify /etc/sysctl.conf, Add 'vm.nr_hugepages = 16386' to end of the file and save

10. Run 'sysctl -p' effect the changes. Make sure the parameter and values are same as recommended

[root@bo3udsmsxodb01 ~]# sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 16386

11. You can verify whether HugePages are created now

[root@bo3udsmsxodb01 ~]# grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:   16386
HugePages_Free:    16386
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        33558528 kB

Reference

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

ORA-65140: invalid common profile name

Problem

Following Error occur while create new profile

SQL> Create profile ords_profile limit PASSWORD_LIFE_TIME unlimited;
Create profile ords_profile limit PASSWORD_LIFE_TIME unlimited
               *
ERROR at line 1:
ORA-65140: invalid common profile name;

Cause

Try to create profile by loging to the CDB when you should be logged into a PDB.

Solution

You can create profile when login to Pluggable database (PDB) instead of login to Container Database (CDB). If you want to continue from CDB, Please follow the step below

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> Create profile ords_profile limit PASSWORD_LIFE_TIME unlimited;

Profile created.

SQL> alter user ORDS_PUBLIC_USER profile ords_profile;

User altered.