remove spaces from SQL script in notepad++

 

1. Go to Search -> Replace
2. Select "Regular expression" under Search mode.
3. Use ^\s* for "Find what" and leave "Replace with" blank.
4. Click Replace all

Oracle APPS DBA - SQL query to get the print jobs from users

 Use the following query

select distinct fcr.printer, fu.user_name, 

fu.description, count(*) jobs

from apps.fnd_concurrent_requests fcr, apps.fnd_user fu

where fcr.requested_by = fu.user_id

and (fcr.printer is not null and fcr.printer not in  ('noprint','LOCAL_PRINT','LOCAL','LOCAL_PRINTTO', 'LOCAL_PREVIEW'))

group by fcr.printer, fu.user_name, fu.description;

Oracle ERP R12.2 cloned environment by EBS cloud manager - users facing "an invalid redirect has been blocked" while opening forms

 Run the following query and check if the source values are still there, if source values are there, then update with the cloned environment values or unset ICX forms launcher profile on site level.

SELECT
    p.profile_option_name        short_name,
    n.user_profile_option_name   name,
    decode(v.level_id, 10001, 'Site',
                       10002, 'Application',
                       10003, 'Responsibility',
                       10004, 'User',
                       10005, 'Server', v.level_id) level_set,
    decode(to_char(v.level_id), '10001', '',
                                '10002', app.application_short_name,
                                '10003', rsp.responsibility_key,
                                '10005', svr.node_name,
                                '10006', org.name,
                                '10004', usr.user_name, 'UnDef') "CONTEXT",
    v.profile_option_value       value
FROM
    fnd_profile_options         p,
    fnd_profile_option_values   v,
    fnd_profile_options_tl      n,
    fnd_user                    usr,
    fnd_application             app,
    fnd_responsibility          rsp,
    fnd_nodes                   svr,
    hr_operating_units          org
WHERE
    p.profile_option_id = v.profile_option_id (+)
    AND p.profile_option_name = n.profile_option_name
    AND n.language = 'US'
    AND upper(p.profile_option_name) = ('ICX_FORMS_LAUNCHER')
    AND usr.user_id (+) = v.level_value
    AND rsp.application_id (+) = v.level_value_application_id
    AND rsp.responsibility_id (+) = v.level_value
    AND app.application_id (+) = v.level_value
    AND svr.node_id (+) = v.level_value
    AND org.organization_id (+) = v.level_value
ORDER BY
    user_profile_option_name,
    level_set;

can not drop database link after changing the global_name parameter

 try the following step

1- take complete database backup

2- take the backup of table sys.link$

3- delete the row where name = db link

4- check the db link

extract DDL for database links with passwords -

 follow the link

https://forums.oracle.com/ords/apexds/post/how-to-extract-ddl-for-all-database-links-7664

set lines 200
set pages 0
set feed off
set heading off
spool get_ddl/get_dblink_ddl.run.sql
SELECT 'CREATE OR REPLACE PROCEDURE '||DECODE(U.NAME,'PUBLIC','SYSTEM',U.NAME)||'.dba$sp_create_dblink_tmp AS '||CHR(10)||'BEGIN'||CHR(10)||
'EXECUTE IMMEDIATE '||''''||
'create '||DECODE(U.NAME,'PUBLIC','public ',' ')||'database link '||L.name
||' connect to ' || L.USERID || ' identified by values '
||''''||''''||L.PASSWORDX||''''||''''||' using '||''''||''''|| L.host ||''''||''''||''''||';'|| CHR(10)||'END dba$sp_create_dblink_tmp; '
||CHR(10)||'/'||CHR(10)||
'EXECUTE '||DECODE(U.NAME,'PUBLIC','SYSTEM',U.NAME)||'.dba$sp_create_dblink_tmp;'||CHR(10)||
'DROP PROCEDURE '||DECODE(U.NAME,'PUBLIC','SYSTEM',U.NAME)||'.dba$sp_create_dblink_tmp;'||CHR(10)
||CHR(10)
TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#
;
spool off

command to kill all the processes running by a specific user

 

following command can be used to kill all the processes running by oracle user, replace oracle by any user of interest.

pkill -9 -u `id -u oracle`

SQL script to find the patches applied with patch name on ERP R12.2

 use the following SQL to get the patch name applied since certain date 

SQL query:

SELECT DISTINCT e.patch_name, to_char(c.end_date, 'DD-MON-RRRR HH24:MI:SS') End_date, e.patch_type, d.patch_abstract

FROM

ad_bugs a, ad_patch_run_bugs b, ad_patch_runs c, ad_patch_drivers d , ad_applied_patches e

WHERE

a.bug_id = b.bug_id AND

b.patch_run_id = c.patch_run_id AND

c.patch_driver_id = d.patch_driver_id AND

d.applied_patch_id = e.applied_patch_id 

AND e.creation_date > to_date ('01-JAN-23')

order by 1,3 ASC;

sample output:

PATCH_NAME END_DATE             PATCH_TYPE                     PATCH_ABSTRACT

---------- -------------------- ------------------------------ --------------------

13839836   23-APR-2024 09:26:50 ONE-OFF

34766982   22-JAN-2024 10:59:05 ONE-OFF                        ISG Consolidated Patch for 12.2 (23_1_1)

                                                                                                                                                                                                                                          

Concurrent Processing - Product Information Center (PIC) (Doc ID 1304305.1)

 huge library of Oracle support documents to deep dive into Oracle concurrent managers.

Concurrent Processing - Product Information Center (PIC) (Doc ID 1304305.1)

convert .p7b certs to .cer format

 use the following command 

first export the cert into .p7b with full chain

and after that use the following command to change the .p7b to .cer (PEM) format, openssl can be used on any linux box.

openssl pkcs7 -inform DER -outform PEM -in erpdummy-full-chain.p7b -print_certs > erpdummy.cer

Using Load Balancers with Oracle E-Business Suite Release 12.2 (Doc ID 1375686.1)

 Using Load Balancers with Oracle E-Business Suite Release 12.2 

Please check the Oracle support (Doc ID 1375686.1)

APEX ADMIN account locked for workspace INTERNAL - password change procedure

 If APEX ADMIN account has locked  for INTERNAL workspace or need to change the password, use the following procedure, please test it first and do the necessary backups first.

INTERNAL Admin password change

==============================

login with sys as sysdba to the APEX database

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 APEXUAT                          READ WRITE NO

SQL> alter session set container=APEXUAT;

Session altered.

SQL>

SQL>

SQL> @apxchpwd.sql

...set_appun.sql

================================================================================

This script can be used to change the password of an Oracle APEX

instance administrator. If the user does not yet exist, a user record will be

created.

================================================================================

Enter the administrator's username [ADMIN]

User "ADMIN" exists.

Enter ADMIN's email [apexadmin@testlab.com]

Enter ADMIN's password []

Changed password of instance administrator ADMIN.

- test the login with ADMIN account for INTERNAL workspace

Oracle EBS Cloud manager deployment on OCI

 Please follow the Oracle support document.

Deploying Oracle E-Business Suite Cloud Manager Version 20.1.1.X on Oracle Cloud Infrastructure (Doc ID 2434500.1)

OCI APPS R12.2.8 - adstrtal.sh: exiting with status 1 - adstrtal.sh: Database connection could not be established

Introduction:

After stopping the APPS tier services and database tier services, got the following errors while starting up the APPS Tier services post database services.

 [oracle@test_oracle_cloud_app1 scripts]$ adstrtal.sh

You are running adstrtal.sh version 20.24.12020

Enter the APPS username: apps

Enter the APPS password:

Enter the WebLogic Server password:

adstrtal.sh: Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong.

USAGE:  adstrtal.sh <appsusername/appspassword> [-nothreading]

        adstrtal.sh <applications_username/applications_password> -secureapps

        adstrtal.sh -nodbchk

        adstrtal.sh -mode=allnodes

        adstrtal.sh -msimode

adstrtal.sh: exiting with status 1

Resolution:

Issue is not related to do any thing with apps user password or any of the listener on APPS Tier or database tier.

The issue is related to one of the ERP Service which did not startup with the database start.

Check the services

select name,network_name from v$services;

select SERVICE_ID,NAME from dba_services;

login to the PDB of APPS 

show pdbs;

show con_name;

Alter session set container=APPS_DB_PDB_NAME;

start the required services, in our case it was ebs_ERPTEST

 

SQL> exec dbms_service.start_service(service_name=>'ebs_ERPTEST');

 

PL/SQL procedure successfully completed.

now start the APPS Tier services.

If you are not sure about the APPS Service, check the alert log and grep with ":Started" and start the service with the above procedure.

How to deploy Oracle EBS Cloud Manager on Oracle Cloud

 Please follow the Oracle support document

Deploying Oracle E-Business Suite Cloud Manager Version 20.1.1.X on Oracle Cloud Infrastructure (Doc ID 2434500.1)

Cloning an Oracle Base Database Service 19c DB System for Oracle E-Business Suite Release 12.2 on Oracle Cloud

 Introduction:

To clone the Oracle Base Database Service 19c DB system on Oracle cloud , check the following Oracle support doc.

Cloning an Oracle Base Database Service 19c DB System for Oracle E-Business Suite Release 12.2

(Doc ID 2800199.1)

Oracle Cloud - Cloning of Oracle EBS R12.2 Application Tier on OCI

Introduction:

How to clone Oracle EBS R12.2 APPS tier on Oracle Cloud.

Resolution:

Please follow the Oracle support document (Doc ID 2912000.1)

Cloning the Oracle E-Business Suite Release 12.2 Application Tier on 

Oracle Cloud Infrastructure Compute (Doc ID 2912000.1)

Lock the OCI Oracle cloud linux OS user

 Introduction:

Some times there is requirement not to delete the OS user but to lock it. Following can be followed to lock the user account on OCI Oracle cloud.

Resolution:

[root@oraclecloudlinuxserver ~]# passwd -l username

Locking password for user username.

passwd: Success

Server refused our key - No supported authentication methods available (server sent: publickey.gssapt-keyex,gssapi-with-mic)

 Introduction:

after setting up the ssh private and public key, error message coming "Server refused our key"

Resolution:

Check the following steps and check if it resolve the issue.

1- login to server and switch to root user

2- go to /home/user_not_connecting_with_ssh e.g. /home/testuser

3- go to .ssh

4- vi the authorized_keys

5- insert the following on the first line and make sure there is space at the end of the following

ssh-rsa 

6- test the issue

Reset EBS Cloud manager password when current password not working

 Steps to rest the EBS Cloud Manager password when current password not working

1. Make sure no jobs are running in EBS Cloud Manager UI.

2. Make sure NodeJS, Quartz server, Chef server and MySQL Database server are down.

$ ps aux | awk '/[s]erver.js/'
$ ps aux | awk '/[j]ob_scheduler.sh/'
$ ps aux | awk '/[m]ysqld/'
$ ps aux | awk '/[o]c_erchef/'

2.1 If they are running, bring them down:

A. for NodeJS: $ sudo pkill -f server.js

B. for Quartz server: $ sudo pkill -f job_scheduler.sh
: $ sudo pkill -f quartz-2.2.3.jar

C. for Chef server: $sudo chef-server-ctl stop

D. MySQL Database server: $ sudo pkill -f mysqld


3. Create file /tmp/resetpassword.ini with 777 permission

$ touch /tmp/resetpassword.ini

$ chmod 777 /tmp/resetpassword.ini


4. Using ViM Add below content to file : /tmp/resetpassword.ini

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Reset@123';
ALTER USER 'ebsoc'@'localhost' IDENTIFIED BY 'Reset@123';
ALTER USER 'quartz'@'localhost' IDENTIFIED BY 'Reset@123';
FLUSH PRIVILEGES;

5. Stat mysql server

$ sudo mysqld --init-file=/tmp/resetpassword.ini --user=mysql &

6. Kill the process

$ sudo pkill -f mysqld

7. Remove file /tmp/resetpassword.ini

$ rm -f /tmp/resetpassword.ini

8. Restart all services and when prompted, enter the new Cloud Manager admin password
$ cd /u01/install/APPS/apps-unlimited-ebs/bin
$ sh ebscloudmgrctl.sh startall

ERROR: Invalid Oracle E-Business Suite Cloud Manager Administration Password - Reset EBS Cloud manager password when current password not working

 Introduction:

-ERROR: Invalid Oracle E-Business Suite Cloud Manager Administration Password -

-Reset EBS Cloud manager password when current password not working

Resolution:

Following command can be used to reset password if the current password is working

./ebscmadmin change-admin-password

But if the current password is not working then follow the following steps to reset the EBC Cloud manager administrator password.

Steps:

1. Make sure no jobs are running in EBS Cloud Manager UI.

2. Make sure NodeJS, Quartz server, Chef server and MySQL Database server are down.

$ ps aux | awk '/[s]erver.js/'
$ ps aux | awk '/[j]ob_scheduler.sh/'
$ ps aux | awk '/[m]ysqld/'
$ ps aux | awk '/[o]c_erchef/'

2.1 If they are running, bring them down:

A. for NodeJS: $ sudo pkill -f server.js

B. for Quartz server: $ sudo pkill -f job_scheduler.sh
: $ sudo pkill -f quartz-2.2.3.jar

C. for Chef server: $sudo chef-server-ctl stop

D. MySQL Database server: $ sudo pkill -f mysqld


3. Create file /tmp/resetpassword.ini with 777 permission

$ touch /tmp/resetpassword.ini

$ chmod 777 /tmp/resetpassword.ini


4. Using ViM Add below content to file : /tmp/resetpassword.ini

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Reset@123';
ALTER USER 'ebsoc'@'localhost' IDENTIFIED BY 'Reset@123';
ALTER USER 'quartz'@'localhost' IDENTIFIED BY 'Reset@123';
FLUSH PRIVILEGES;

5. Stat mysql server

$ sudo mysqld --init-file=/tmp/resetpassword.ini --user=mysql &

6. Kill the process

$ sudo pkill -f mysqld

7. Remove file /tmp/resetpassword.ini

$ rm -f /tmp/resetpassword.ini

8. Restart all services and when prompted, enter the new Cloud Manager admin password
$ cd /u01/install/APPS/apps-unlimited-ebs/bin
$ sh ebscloudmgrctl.sh startall