Soumya's Database Blog

Oracle REST Data Services (ORDS) Configuration on Linux 8 - Part II

Before we proceed, the first part that includes apex installation and configuration can be found here

Download ORDS 23.4

Ords can be downloaded from Here

Download oracle JDK 17 +

https://www.oracle.com/in/java/technologies/downloads/#java17

Create following directories.

mkdir /u01/apex/ords_23.4

cd /u01/apex/ords_23.4

mkdir /u01/apex/ords_23.4/config

mkdir /u01/apex/ords_23.4/config/ords/standalone/logs

Unzip the downloaded directory

[oracle@ocisoumya ords_23.4]$ unzip ords-23.4.0.346.1619.zip -d /u01/apex/ords_23.4/

Install ORDS on linux 8

[oracle@ocisoumya ~]$ cd /u01/apex/ords_23.4/bin

[oracle@ocisoumya bin]$ ./ords --config /u01/apex/ords_23.4/config install --interactive --log-folder /u01/apex/ords_23.4/co nfig/ords/standalone/logs

2024-04-01T14:40:08.532Z INFO Your configuration folder /u01/apex/ords_23.4/config is located in ORDS product folder. Oracle recommends to use a different configuration folder.

ORDS: Release 23.4 Production on Mon Apr 01 14:40:08 2024

Configuration:

/u01/apex/ords_23.4/config

The option --log-folder /u01/apex/ords_23.4/config/ords/standalone/logs is located in the ORDS product folder. Oracle recom mends to use a different --log-folder location.

Created folder /u01/apex/ords_23.4/config/ords/standalone/logs

The configuration folder /u01/apex/ords_23.4/config does not contain any configuration files.

Oracle REST Data Services - Interactive Install

Enter a number to select the type of installation

[1] Install or upgrade ORDS in the database only

[2] Create or update a database pool and install/upgrade ORDS in the database

[3] Create or update a database pool only

Choose [2]: 2

Enter a number to select the database connection type to use

[1] Basic (host name, port, service name)

[2] TNS (TNS alias, TNS directory)

[3] Custom database URL

Choose [1]:

Enter the database host name [localhost]: ocisoumya

Enter the database listen port [1521]:

Enter the database service name [orcl]:

Provide database user name with administrator privileges.

Enter the administrator username: sys as sysdba

Enter the database password for sys as sysdba:

Connecting to database user: sys as sysdba url: jdbc:oracle:thin:@//ocisoumya:1521/orcl

Retrieving information.

Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: APEX

Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]: TEMP

Enter a number to select additional feature(s) to enable:

[1] Database Actions (Enables all features)

[2] REST Enabled SQL and Database API

[3] REST Enabled SQL

[4] Database API

[5] None

Choose [1]:

Enter a number to configure and start ORDS in standalone mode

[1] Configure and start ORDS in standalone mode

[2] Skip

Choose [1]:

Enter a number to select the protocol

[1] HTTP

[2] HTTPS

Choose [1]:

Enter the HTTP port [8080]:

Enter the APEX static resources location: /u01/apex/ords_23.4/config/ords/standalone/doc_root/images

copy images folder from apex installation folder to following path /u01/apex/ords_23.4/config/ords/standalone/doc_root/images which resides in ords installation directory.

[oracle@ocisoumya apex]$ cd

[oracle@ocisoumya ~]$ cd /u01/apex/apex

[oracle@ocisoumya apex]$ cp -r images/ /u01/apex/ords_23.4/config/ords/standalone/doc_root/

Batch script to check tablespace usage and send email notification

Issue: We had a requirement for a customer, where oracle 19c database was running on Windows server 2019. Customer wanted to monitor tablespace’s datafile usage and get email alert incase the used space is less than 80%

To mitigate this , we developed a batch script leveraging powershell’s Send-Maillmessage command to achieve this.

Step 1.

First we create the sql which will be used to monitor the tablespace usage. Lets save it as E:\BackupScripts\Tbs_size_check.sql

Set lines 222

SELECT d.tablespace_name "TS Name", d.num_files "Num Files",

d.asize "Size MB", NVL (f.freebytes, 0) "Free MB",

(d.BYTES - NVL (f.freebytes, 0)) "Used MB",

((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize "Percent Used"

FROM (SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes

FROM dba_free_space

GROUP BY tablespace_name) f,

(SELECT tablespace_name, COUNT (DISTINCT (file_id)) num_files,

sum(greatest(maxbytes,bytes))/1024/1024 asize,

SUM (BYTES) / 1024 / 1024 BYTES

FROM dba_data_files

GROUP BY tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name(+)

AND d.tablespace_name not like '%UNDO%'

AND ROUND (((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize) > 80;

exit;

Step 2.

Now lets create the batch script that will be used to monitor the tablespace usage and send an alert if the usage hits a predefined threshold value. In our case we have set threshold value to 80%. This can be modified as per the requirement.

@echo on

REM Database credentials

set DB_USER=sys

set DB_PASS=passfordb

set DB_SID=orcl

REM Email configuration

set EMAIL_RECIPIENT=soumya.das@testmail.com,usertwo@testmail.com

set EMAIL_SUBJECT=Tablespace Usage Alert

set FROM_EMAIL=database.alerts@testmail.com

set HOSTNAME=%COMPUTERNAME%

set EMAIL_BODY=Tablespace usage is LOW at %HOSTNAME%. Please take appropriate action.

set ATTACHMENTS=E:\TABLESPACE_USAGE.txt

REM Execute SQL queries using SQL*Plus

REM Execute SQL query and send email if threshold is met

E:\app\oracle\product\19.3.0\dbhome_1\bin\sqlplus -S %DB_USER%/%DB_PASS%@%DB_SID% as sysdba @E:\BackupScripts\TS_SPACE_CHECK1.SQL > E:\TABLESPACE_USAGE.txt

cd E:\

REM Read usage percent and tablespace name from TABLESPACE_USAGE.txt

for /f "tokens=1,6" %%a in (E:\TABLESPACE_USAGE.txt) do (

set tablespace_name=%%a

set usage_percent=%%b

)

rem echo %usage_percent%

REM Check if usage_percent is greater than or equal to the threshold

if %usage_percent% GEQ 80 (

REM Send email alert with tablespace name and usage percentage

powershell -ExecutionPolicy Bypass -Command "Send-MailMessage -From 'database.alerts@testmail.com' -To %EMAIL_RECIPIENT% -Subject '%EMAIL_SUBJECT%' -Body '%EMAIL_BODY%' -SmtpServer '192.24.1.40' -Attachments '%ATTACHMENTS%'"

)

del E:\TABLESPACE_USAGE.txt

Open task scheduler and follow the following steps to schedule the batch script to run every 1 hour.

Done…

Oracle Apex 23.2 Installation and configuration on Linux - Part I

Oracle Application Express 23.2 Installation Steps

Before installing Oracle Application Express, you must verify your configuration meets the minimum installation requirements.

• Oracle Database Requirements

Oracle Application Express release 23.2 requires an Oracle Database release 19c or later, including Enterprise Edition and Standard Edition (SE) and Database 23c Free. Oracle Application Express can be installed in single-instance database and in Oracle Real Application Clusters (Oracle RAC) database.

· Browser Requirements
Oracle APEX requires a JavaScript-enabled browser and supports the current and prior major release of Google Chrome, Mozilla Firefox, Apple Safari, and Microsoft Edge.

· Web Server Requirements
Oracle APEX requires Oracle REST Data Services (ORDS) 20.x or later. The use of Quick SQL requires Oracle REST Data Services (ORDS) 23.3 or later.

• MEMORY_TARGET of the Target Database

Oracle Application Express requires the system global area (SGA) and program global area (PGA) to be at least 300 MB.

• Checking the WORKAREA_SIZE_POLICY of the Target Database
For the Oracle APEX installation or upgrade process, the WORKAREA_SIZE_POLICY session parameter must be set to AUTO.

SQL> alter system set WORKAREA_SIZE_POLICY=auto scope=both;

• Oracle XML DB Requirement

Oracle XML DB must be installed in the Oracle database that you want to use if you are installing a full development environment. Oracle XML DB is not required for runtime only installations. If you are using a preconfigured database created either during an installation or by Database Configuration Assistant (DBCA), Oracle XML DB is already installed and configured.

Download Link: -

Oracle Application Express can be downloaded from HERE

Environment details: -

OS	RHEL 7.9
IP	192.168.0.106
HOSTNAME	Server3.soumya.com
APEX VERSION	23.2
DB VERSION	19.3.0

Once downloaded, unzip the software to begin the installation.

Create a directory where the apex software will be installed.

[oracle@server3 u01]$ mkdir -p /u01/apex

[oracle@server3 apex]$ unzip apex_23.2.zip -d /u01/apex

Create a new tablespace for APEX.

[oracle@server3 apex]$ sqlplus / as sysdba

SQL> create tablespace APEX datafile '/u01/app/oradata/ORCL/apexd01.dbf' size 2G autoextend on;

Apex Installation: -

[oracle@server3 apex]$ cd /u01/apex/apex

[oracle@ocisoumya apex]$sqlplus / as sysdba

SQL>@apexins.sql APEX APEX TEMP /i/ -- i refers image location in application. APEX refers the tablespace name , TEMP refers the temporary tablespace name.

Once the installation is complete, confirm it by running following query

SYS> SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME VERSION STATUS

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

Oracle APEX 23.2.0 VALID

To change the admin password using “apxchpwd.sql” script as sys user.

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" does not yet exist and will be created.

Enter ADMIN's email [ADMIN]

Enter ADMIN's password []

Created instance administrator ADMIN.

For configuring static files, we must run apex_rest_config.sql after a new installation of Oracle APEX.

SQL> @apex_rest_config.sql

Enter a password for the APEX_LISTENER user []

Enter a password for the APEX_REST_PUBLIC_USER user []

...set_appun.sql

...setting session environment

...create APEX_LISTENER and APEX_REST_PUBLIC_USER users

...grants for APEX_LISTENER and ORDS_METADATA user

Configure APEX

Now there are two ways which we can use to access APEX.

1. Oracle REST Data Services(ORDS)

2. Embeded PL/SQL Gateway(EPG) Configuration

However Oracle recommendation is ORDS.

create a custom profile and assign the profile to standard users to avoid user lock issues.

CREATE PROFILE app_user1 LIMIT

FAILED_LOGIN_ATTEMPTS unlimited

PASSWORD_LIFE_TIME unlimited

PASSWORD_REUSE_TIME unlimited

PASSWORD_REUSE_MAX unlimited

PASSWORD_VERIFY_FUNCTION NULL

PASSWORD_LOCK_TIME unlimited

PASSWORD_GRACE_TIME unlimited;

Alter all apex related users to assign custom profile.

select username,account_status from dba_users where username like ('%APEX%');

ALTER USER APEX_230200 PROFILE app_user1;

ALTER USER APEX_LISTENER PROFILE app_user1;

ALTER USER APEX_REST_PUBLIC_USER PROFILE app_user1;

ALTER USER APEX_PUBLIC_USER PROFILE app_user1;

Unlock the users:-

ALTER USER APEX_LISTENER identified by Concept_ora1 account unlock;

ALTER USER APEX_REST_PUBLIC_USER identified by Concept_ora1 account unlock;

ALTER USER APEX_PUBLIC_USER identified by Concept_ora1 account unlock;

Execute following to grant connect privileges to any host for the APEX_230200 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.

BEGIN

DBMS_NETWORK_ACL_ADMIN.append_host_ace (

host => '*',

ace => xs$ace_type(privilege_list => xs$name_list('connect','resolve'),

principal_name => 'APEX_230200',

principal_type => xs_acl.ptype_db));

END;

Steps to Fix Output Post Processor Startup in EBS R12

Steps to Fix Output Post Processor Startup

In Oracle E-Business Suite, there are occasions when we encounter issues such as the Output Post Processor failing to start up, resulting in a mismatch between the actual number of processes and the target number of processes. Consequently, many concurrent programs end up completing with errors or warnings.

Solution: To resolve such issues, follow these steps:

Restart the manager called "Output Post Processor" from the frontend:
- Log in to Oracle Applications with sysadmin responsibility.
- Navigate to: Concurrent -> Managers -> Administer.
- Select "Output Post Processor" and click on the "Restart" button.
If the above step does not resolve the issue, perform a complete application stack restart:
- Ensure no processes are running from the application end after shutting down services.
- Execute the "ps ux" command from the OS application user to check for any running processes.
- If any processes are found, terminate them using the "kill -9 process_id" command.

Once completed, restart the application. This should resolve the issue with the Output Post Processor.

ORA-00742: Log read detects lost write

Issue:- We had a situation for a customer where his server was shutdown abruptly due to power cut at datacenter. After resuming the power, when he tried to start his database he encountered following error“ORA-00742: Log read detects lost write in thread 1 sequence 5789 block 805653”

Environment :- Windows 2019

DB Version:- 12.2.0.1

Solution: -

D:\oracle\product\12.2.0\dbhome\bin>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 11 10:26:27 2024

Connected to:

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

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Lets try to open the database

D:\oracle\product\12.2.0\dbhome\bin>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 11 10:28:29 2024

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1.2885E+10 bytes

Fixed Size 8761232 bytes

Variable Size 2885681264 bytes

Database Buffers 9965666304 bytes

Redo Buffers 24793088 bytes

Database mounted.

ORA-00742: Log read detects lost write in thread 1 sequence 5789 block 805653

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\ORCL\REDO01.LOG'

As we can see database is getting mounted, but it couldn’t open, and above error was thrown.

Th above error is generally observed due to a relog log corruption. In this case corruption seems to have happened on relog group 1.

SQL> select * from v$logfile;

SQL> col member for a35

SQL> /

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

4 ONLINE D:\ORACLE\ORADATA\ORCL\REDO04.LOG NO 0

3 ONLINE D:\ORACLE\ORADATA\ORCL\REDO03.LOG NO 0

2 ONLINE D:\ORACLE\ORADATA\ORCL\REDO02.LOG NO 0

1 ONLINE D:\ORACLE\ORADATA\ORCL\REDO01.LOG NO 0

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

1 1 5789 536870912 512 1 NO CURRENT 342324096 11-MAR-24 1.8447E+19 0

4 1 5788 536870912 512 1 YES INACTIVE 342320572 11-MAR-24 342324096 11-MAR-24 0

3 1 5787 536870912 512 1 YES INACTIVE 342313670 11-MAR-24 342320572 11-MAR-24 0

2 1 5786 536870912 512 1 YES INACTIVE 342313238 11-MAR-24 342313670 11-MAR-24 0

Lets try do an incomplete recovery of the database

SQL> recover database until cancel;

ORA-00279: change 342324096 generated at 03/11/2024 05:04:10 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2024_03_11\O1_MF_1_5789_%U_.ARC

ORA-00280: change 342324096 for thread 1 is in sequence #5789

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

D:\ORACLE\ORADATA\ORCL\REDO01.LOG

Log applied.

Media recovery complete

Here we need to provide the redolog group which was corrupted.

Now lets try to open the database using resetlogs

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-00600: internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []

As per Doc ID 2026541.1 , to fix the above error we need to rename the online corrupted redolog and initial resetlogs again

We renamed D:\ORACLE\ORADATA\ORCL\REDO01.LOG as D:\ORACLE\ORADATA\ORCL\REDO01_old.LOG and tried to do recovery again

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 342379894 generated at 03/11/2024 08:46:09 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2024_03_11\O1_MF_1_5789_%U_.ARC

ORA-00280: change 342379894 for thread 1 is in sequence #5789

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database alerted.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE