In this Blog we are going to learn about How To Find a Particular Database Session ID From the SQLID. One of my customer informed me I have query running since 10 hours and He already confirmed with Business user that this query session is safe to be terminated.
Here we will not discuss about invetigating if that is really applicable to be terminated
Instead this is a just to share the method on:
GOAL: How to find particular Database session ID from the SQLID we could see from Oracle 19c Enterprise Manager Database Express console
Note that we could not see the session id from same console.
SOLUTION:
select * from V$SESSION where SQL_ID = ‘7upkztdrajyay’
better to see the detailed row data returned from above select statement.
then check the sid in below sql which will generate kill statement to be run from database superuser account ( sysdba )
select ‘alter system kill session ‘||””|| vs.sid||’,’||vs.serial#||””|| ‘ immediate;’ from v$session vs, v$process vp where vs.paddr = vp.addr and vs.sid in ();
OR merge both queries for one shot result
select ‘alter system kill session ‘||”|| vs.sid ||’,’ || vs.serial# || ” || ‘ immediate;’ from v$session vs, v$process vp where vs.paddr=vp.addr and vs.sid in (select sid from v$session where sql_id =’7upkztdrajyay’);
sqlplus / as sysdba
SQL> ALTER SYSTEM KILL SESSION ‘275,44741’ immediate; SQL> ALTER SYSTEM KILL SESSION ‘279,44749’ immediate;
Issue: While accessing the EBS 12.2.13 Weblogic Console to check health status of EBS managed server the browser redirects to below error as a security restriction: The Server is not able to service this request: [Socket:000445]Connection rejected, filter blocked Socket, weblogic.security.net.FilterException: [Security:090220]rule 2
Solution: Connect to application from OS/Linux terminal/putty Source EBS 12.2 Run File system environment Last login: Mon Apr 15 09:35:16 2024 E-Business Suite Environment Information File System Type: SINGLE RUN File System : /d01/UAT/ebiz/fs1/EBSapps/appl PATCH File System : NOT APPLICABLE Non-Editioned File System : /d01/UAT/ebiz/fs_ne
Step 1: Create file get_health_status.py vi get_health_status.py
Insert below script content:
==========Start of script================== connect(‘weblogic’, ‘<<weblogicPwd>>’, ‘t3://ebsuat.erp.com:7081’) domainRuntime()
servers = domainRuntimeService.getServerRuntimes() for server in servers: print(‘Server: ‘ + server.getName()) print(‘Health State: ‘ + str(server.getHealthState())) disconnect() exit()
=========== End of Script =============== Step 2: Run below command with py script file we just created $FMW_HOME/oracle_common/common/bin/wlst.sh get_health_status.py
Recorded Session: [appluat@ebsuat ~]$ $FMW_HOME/oracle_common/common/bin/wlst.sh new.py
Initializing WebLogic Scripting Tool (WLST) … Welcome to WebLogic Server Administration Scripting Shell Type help() for help on available commands Connecting to t3://ebsuat.erp.com:7081 with userid weblogic … Successfully connected to Admin Server ‘AdminServer’ that belongs to domain ‘EBS_domain’. Warning: An insecure protocol was used to connect to the server. To ensure on-the-wire security, the SSL port or Admin port should be used instead. Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root. For more help, use help(domainRuntime)
Environment: Oracle EBS Release that is 12.2.13 with 19c that is 19.22 which is Running on Red Hat Enterprise Linux release 8.9 (Ootpa)
Issue:
After Applying :
R12.AD.C.delta.15 -> 34695811
R12.TXK.C.delta.15 -> 34785677
Oracle E-Business Suite 12.2.13 Release Update Pack -> 34776655
We started facing problem i.e Concurrent Request Output and Log output displays Blank Page in the Browser.
SOLUTION:
Make sure to shutdown EBS Services including Weblogic
Then Run the following commands to unzip the container and WLS patch respectively and then Apply below Weblogic Server Patch ( for OL8/RHEL8 customers only ):
cd /u01/8005_uat_app/ebiz/fs1/FMW_Home/utils/bsu/cache_dir/
2-a. unzip -o /u01/EBS_UPDATE_12_2_13/CONC_LOG_NO_OUTPUT/p32931976_R12_GENERIC.zip 2-b. unzip -o /u01/EBS_UPDATE_12_2_13/CONC_LOG_NO_OUTPUT/p31090393_1036_Linux-x86-64.zip —-> EG54.jar delivered by this patch is actually going to fix.
[appluat@ebsappuat CONC_LOG_NO_OUTPUT]$ ls -lrt total 213624 -rw-r–r–. 1 appluat dba 196680280 Dec 9 2020 patch-catalog_27387.xml -rw-r–r–. 1 appluat dba 449 Dec 9 2020 README.txt -rw-r–r–. 1 appluat dba 4088 Dec 9 2020 EG54.jar -rw-r–r–. 1 appluat dba 673 May 27 2021 Readme.txt -rw-r–r–. 1 appluat dba 11081624 May 27 2021 p31090393_1036_Linux-x86-64.zip -rw-r–r–. 1 appluat dba 10970736 Feb 21 16:09 p32931976_R12_GENERIC.zip [appluat@ebsappuat CONC_LOG_NO_OUTPUT]$
OS level error while starting WebLogic Admin adadminsrvctl.sh service in EBS 12.2 on Redhat and or Oracle Linux 8.9 :
You are running adadminsrvctl.sh version 120.10.12020000.11
Starting WLS Admin Server… Refer /u01/8005_uat_app/ebiz/fs1/inst/apps/PREUPG_ebsappuat/logs/appl/admin/log/adadminsrvctl.txt for details
AdminServer logs are located at /u01/8005_uat_app/ebiz/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs
adadminsrvctl.sh: exiting with status 0
adadminsrvctl.sh: check the logfile /u01/8005_uat_app/ebiz/fs1/inst/apps/PREUPG_ebsappuat/logs/appl/admin/log/adadminsrvctl.txt for more information …
.end std out.
sh: scl: line 1: syntax error: unexpected end of file sh: error importing function definition for scl’ sh: ml: line 1: syntax error: unexpected end of file sh: error importing function definition forml’ sh: switchml: line 1: syntax error: unexpected end of file sh: error importing function definition for switchml’ sh: which: line 1: syntax error: unexpected end of file sh: error importing function definition forwhich’ sh: ml: line 1: syntax error: unexpected end of file sh: error importing function definition for ml’ sh: _module_raw: line 1: syntax error: unexpected end of file sh: error importing function definition for_module_raw’ sh: scl: line 1: syntax error: unexpected end of file
.end err out.
To solve the error while starting weblogic admin server, follow the given below
Solution:
vi .bash_profile of your EBS Application OS user unset which unset scl
Extraction of EBS 12.1.x or EBS 12.2.x Workflow Mailer logs having Error, Exception or UNEXPECTED Keywords in your target logfiles.
By following the given below steps you will easily extract EBS 12.1.x or EBS 12.2.x Workflow Mailer logs having Error, Exception or UNEXPECTED Keywords in your target logfiles.
Steps:
Source EBS Run file system/
cd $APPLCSF/logs
Generate the subject logs with the following commands:
Steps to STOP Oracle EBS 12.2.x Application Services:
1. Connect Putty to Application Sever IP:
2. Login with Application OS User ( Username / password ) and source EBS Application Env
3. cd $ADMIN_SCRIPTS_HOME 4. [applprod@erp scripts]$ pwd
/u01/Ebiz/fs1/inst/apps/ERPPROD_erp/admin/scripts
[applprod@erp scripts]$
5. ./adstpall.sh apps/< password
Enter Weblogic Password when Prompted
Now EBS services will proceed to shut down…….
Ensure All processes are cleared on Linux terminal
Use the command : ps –ef | grep applprod
Except vnc process , Wait for each applprod process to be cleared out itself.
Incase of delay only we may proceed to kill the pending OS applprod processes in order to proceed next.
AFTER SHUTDOWN of All EBS Services including all OS Processes
Perform the TAR Backup of the EBS FileSystem:
Command to run TAR Backup ( For Full Backup of EBS File System ): cd <Backup Direcory> nohup tar -czf [Compressed file name].tgz /u01/Ebiz &
Command to run TAR Backup ( For Cloning Purpose Only – EBS Backup of EBS File System ): cd <Backup Direcory> nohup tar -czf [Compressed file name].tgz /u01/Ebiz/fs1/EBSapps &
NOTE: For cloning purpose, Ensure to run PRECLONE on Apps Tier before EBS Backup:
Steps to START Oracle EBS 12.2.x Application Services:
cd $ADMIN_SCRIPTS_HOME 4. ( a ) To start Weblogic and EBS Services in One Shot [applprod@erp scripts]$ pwd
/u01/Ebiz/fs1/inst/apps/ERPPROD_erp/admin/scripts
[applprod@erp scripts]$ ./adstrtal.sh apps/***** You are running adadminsrvctl.sh version 120.10.12020000.11
Enter the WebLogic Admin password: < Enter the Weblogic password >
Enter the APPS Schema password: < Enter the apps password >
NOTE: ( On the Safe Side ) It is a Good Practice to start the Weblogic Admin before starting EBS Services to ensure there is no Weblogic startup issue. 4. ( b-1 ) To start Weblogic First Alone [applprod@erp scripts]$ pwd
/u01/Ebiz/fs1/inst/apps/ERPPROD_erp/admin/scripts
[applprod@erp scripts]$ ./adadminsrvctl.sh start
You are running adadminsrvctl.sh version 120.10.12020000.11
Enter the WebLogic Admin password: < Enter the Weblogic password >
Enter the APPS Schema password: < Enter the apps password >
Now login to Weblogic Console :
AdminServer-(admin) should be up and running.
4. ( b-2 ) NOW start EBS Services to start the Managed Services ( Forms_server1, oacore_server1 and oaf_server1 ) under the Weblogic Admin Control[applprod@erp scripts]$ pwd
/u01/Ebiz/fs1/inst/apps/ERPPROD_erp/admin/scripts
[applprod@erp scripts]$ ./adstrtal.sh apps/***** You are running adadminsrvctl.sh version 120.10.12020000.11
Enter the WebLogic Admin password: < Enter the Weblogic password > Enter the APPS Schema password: < Enter the apps password > Once All the Services are ensure ( Forms_server1, oacore_server1 and oaf_server1 )that they are RUNNING
1. Connect Putty to Database Sever IP: 172.10.18.17 2. Login with Database OS User ( Username / password ) 3. Source Container Database Env
. env_filename.env
NOTE: Since there are 2 separate env files ( one for CDB and one for PDB ( EBS Database ) Therefore Environment is not configured to be auto sourced on Login
Issue command shu immediate
Since you are connected to Container Database , Shutdown immediate will first shutdown the Pluggable (PDB) EBS Database and then shutdown itself (CDB) at the end.
Go to the main directory where all datafiles of CDB and PDB are located.
In my case the dir is “/u01/erpprod/db “
cdb_data = Directory containing Container Data Files ebs_data = Directory containing Pluggable EBS Data Files
19.0.0 is the home directory having 19c binaries.
temp directory is a mandatory directory acting as utl_file directory path location: 19c CDB Database.
ONLY FOR INFORMATION ( REFERENCE SCREENSHOT BELOW)
AFTER SHUTDOWN of All Oracle Database Processes
For Cold Backup of Database ( both CDB and PDB ) This cold Backup may help DBA to recover during a Patching situation on the EBS Application or some major Updates in the Database. Perform the Cold TAR Backup using the below command:
Command to run TAR Backup ( For Full Backup of DB File System including both CDB and PDB ): cd <Backup Direcory> nohup tar -czf [To be name of compressed file].tgz /u01/erpprod/db&
For RMAN online Backup
Backup has been already scheduled in Linux crontab – Daily 02:00 AM
( Conditional ) After the patch is applied, You may Synch your patch filesystem by running adop phase=fs_clone Also relevancy of this article must be checked/validated for each / different respective environment like Single / Multiple EBS Nodes etc.
NOTE: One should make use of adop parameters to add with above patch command: Primary App Node: allnodes=no action=db Secondary App Node: allnodes=no action=nodb
Step – 1: Apply All EBS 12.2 relevant Patches to your EBS Application from below JWS MOS Doc: Using Java Web Start with Oracle E-Business Suite(Doc ID 2188898.1)
Step – 2: Enable Java Web Start :
Source EBS Run FS Environment ( 12.2 ) REGENERATE JAR FILES FORCE = YES
Step -3 : UPDATE EBS CONTEXT FILE VALUE OF s_forms_launch_method FROM browser To jws
SOLUTION: Possible areas and tips to review and fix the above issue : Make sure : – Select name from v$active_services and make sure below services are present and configured: ( ebs_PROD, PROD_ebs_patch, PROD) -PDB SID in small letters/Capital letters accordingly present in Ebs DB Ctx File.
Make sure below 19c Database Parameters are properly set: –.local_listener=’hostname.domain:dbport’ in parameter file –.service_names=ERPCDB –.db_domain parameter removed from DB parameter file –.sev_case_sensitive should be false
Visit Today : 31
Visit Yesterday : 24
This Month : 558
This Year : 3950
Total Visit : 4182
Hits Today : 46
Total Hits : 6168
Who's Online : 1