Soumya's Database Blog : March 2024

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.

Oracle Database 19c installation on Linux 8

In this post I will describe the installation of Oracle Database 19c 64-bit on Oracle Linux 8 (OL8) 64-bit. The installation require a minimum of 2g swap memory and secure Linux set to permissive.

· Download Software

Oracle Database 19c (19.3) Software (64-bit)

Add hosts entry in hosts file

[oracle@ocisoumya-2 PDB1]$ cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.0.142 ocisoumya-2.subnet09232321.vcn09232321.oraclevcn.com ocisoumya-2

· Oracle Installation

The installation can be done either in Automatic or Manual way

1. Automatic installation

[root@ocisoumya oled]# dnf install -y oracle-database-preinstall-19c

2. Manual Installation

a. In this installation mode, we need to make following changes on /etc/sysctl.conf file

[root@ocisoumya oled]# vi /etc/sysctl.conf

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

b. Run following command to amend the changes.

[root@ocisoumya oled]# sysctl -p

c. Make changes in /etc/security/limits.d/oracle-database-preinstall-19c.conf

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft nproc 16384

oracle hard nproc 16384

oracle soft stack 10240

oracle hard stack 32768

oracle hard memlock 134217728

oracle soft memlock 134217728

d. Install following packages

dnf install -y bc

dnf install -y binutils

#dnf install -y compat-libcap1

dnf install -y compat-libstdc++-33

#dnf install -y dtrace-modules

#dnf install -y dtrace-modules-headers

#dnf install -y dtrace-modules-provider-headers

#dnf install -y dtrace-utils

dnf install -y elfutils-libelf

dnf install -y elfutils-libelf-devel

dnf install -y fontconfig-devel

dnf install -y glibc

dnf install -y glibc-devel

dnf install -y ksh

dnf install -y libaio

dnf install -y libaio-devel

#dnf install -y libdtrace-ctf-devel

dnf install -y libXrender

dnf install -y libXrender-devel

dnf install -y libX11

dnf install -y libXau

dnf install -y libXi

dnf install -y libXtst

dnf install -y libgcc

dnf install -y librdmacm-devel

dnf install -y libstdc++

dnf install -y libstdc++-devel

dnf install -y libxcb

dnf install -y make

dnf install -y net-tools # Clusterware

dnf install -y nfs-utils # ACFS

dnf install -y python # ACFS

dnf install -y python-configshell # ACFS

dnf install -y python-rtslib # ACFS

dnf install -y python-six # ACFS

dnf install -y targetcli # ACFS

dnf install -y smartmontools

dnf install -y sysstat

# New for OL8

dnf install -y libnsl

dnf install -y libnsl.i686

dnf install -y libnsl2

dnf install -y libnsl2.i686

e. Create group and oracle user

groupadd -g 54321 oinstall

groupadd -g 54322 dba

useradd -u 54321 -g oinstall -G dba oracle

Set password for oracle user

[root@ocisoumya ~]# passwd oracle

Changing password for user oracle.

New password:

Retype new password:

passwd: all authentication tokens updated successfully.

Set Disabled or PERMISSIVE to SELINUX

[root@ocisoumya ~]# vi /etc/selinux/config

SELINUX=disabled

· Disable firewall

[root@ocisoumya ~]# systemctl stop firewalld

[root@ocisoumya ~]# systemctl disable firewalld

Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

· Create directories for oracle installation.

[oracle@ocisoumya u01]$ mkdir -p /u01/app/oracle/19.3.0/dbhome_1

[oracle@ocisoumya u01]$ chown -R oracle:oinstall /u01

[oracle@ocisoumya u01]$ chmod -R 775 /u01

· Login to root user and perform

[root@ocisoumya opc]# xhost +

[root@ocisoumya opc]# su – oracle

· Prepare bash_profile to set oracle home and other paths

export ORACLE_BASE=/u01/app

export ORACLE_HOME=/u01/app/oracle/19.3.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=/usr/sbin:/usr/local/bin:$PATH

export PATH=$JAVA_HOME/bin:$ORACLE_HOME/bin:$PATH

export ORACLE_SID=ORCL

· Unzip the software

[oracle@ocisoumya ~]$ cd $ORACLE_HOME

[oracle@ocisoumya dbhome_1]$ unzip -oq /u01/LINUX.X64_193000_db_home.zip

Start installation by invoking runinstaller using command We would only install the binary in first phase.

[oracle@ocisoumya dbhome_1]$ export ORACLE_HOSTNAME=ocisoumya.example.com

[oracle@ocisoumya dbhome_1]$ export ORA_INVENTORY= /u01/oraInventory

[oracle@ocisoumya dbhome_1]$ ./runInstaller -ignorePrereq -waitforcompletion -silent \

-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \

oracle.install.option=INSTALL_DB_SWONLY \

ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \

UNIX_GROUP_NAME=oinstall \

INVENTORY_LOCATION=${ORA_INVENTORY} \

SELECTED_LANGUAGES=en,en_GB \

ORACLE_HOME=${ORACLE_HOME} \

ORACLE_BASE=${ORACLE_BASE} \

oracle.install.db.InstallEdition=EE \

oracle.install.db.OSDBA_GROUP=oinstall \

oracle.install.db.OSBACKUPDBA_GROUP=oinstall \

oracle.install.db.OSDGDBA_GROUP=oinstall \

oracle.install.db.OSKMDBA_GROUP=oinstall \

oracle.install.db.OSRACDBA_GROUP=oinstall \

SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \

DECLINE_SECURITY_UPDATES=true

Execute following scripts as root user post software installation

As a root user, execute the following script(s):

1. /u01/app/oraInventory/orainstRoot.sh

2. /u01/app/oracle/19.3.0/dbhome_1/root.sh

· Database Creation

The Database Configuration Assistant (DBCA) is typically used to create databases. To facilitate user interaction, GUI screens will be displayed in the interactive mode. In this post, however, I'll create the database in silent mode without showing any windows because the command line already has all the necessary options given.

dbca -silent -createDatabase \

-templateName General_Purpose.dbc \

-gdbname ${ORACLE_SID} -sid ${ORACLE_SID} -responseFile NO_VALUE \

-characterSet AL32UTF8 \

-sysPassword welcome123# \

-systemPassword welcome123# \

-createAsContainerDatabase true \

-numberOfPDBs 1 \

-pdbName PDB1 \

-pdbAdminPassword welcome123# \

-databaseType MULTIPURPOSE \

-memoryMgmtType auto_sga \

-totalMemory 2000 \

-storageType FS \

-datafileDestination "/u01/app/oradata/ORCL" \

-redoLogFileSize 512 \

-emConfiguration NONE \

-ignorePreReqs

This concludes the oracle database 19c installation on Linux 8

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