How to trim away all extra output from a result using sqlplus

The following code can be put in a shell script and executed by root.

Note the sqlplus directives which will remove all unwanted output from the resulting file /tmp/count_options.out

su - oracle <<!
echo "
set lines 10
set pages 0
set trimspool on
set verify off
set heading off
set feedback off
set echo off
spool /tmp/count_options.out
SELECT TRIM(COUNT(*)) FROM DBA_REGISTRY;
" > /tmp/count_options.sql
sqlplus -s / as sysdba @/tmp/count_options.sql
!

Output is:

 /tmp]# cat count_options.out
4

Note that without the TRIM function, the output will be

/tmp]# cat count_options.out
         4

The -s switch will execute sqlplus silently, without it, the output would be

cat count_options.out
         4
SYS@mydb01>SQL>

How to solve TNS-01194: The listener command did not arrive in a secure transport

On one of my servers running Oracle 19c with a container database and a PDB, I had trouble getting the services to automatically register themselves in the listener.

The database it self seemed healthy; the v$active_services view showed that my services were indeed alive.

But still, the services wouldn't be registered by LREG and thus wouldn't be available for the the listener to service incoming requests.

I turned on logging for the listener by setting the following parameter in listener.ora:

LOGGING_LISTENER=on

Then restart the listener. Logging starts:

lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2024 11:22:33

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-MAR-2024 11:11:35
Uptime                    0 days 0 hr. 10 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /orainst/product/19c/network/admin/listener.ora
Listener Log File         /orainst/diag/tnslsnr/myserver/listener/alert/log.xml

I then put a tail on the listener log and register the services with the listener manually:

sqlplus / as sysdba
alter system register;

I then noticed the output on the listener.log file:

 07-MAR-2024 10:37:56 * service_register_NSGR * 1194
 
 TNS-01194: The listener command did not arrive in a secure transport

Look up the error:

oracle@myserver.oric.no:[cdb]# oerr tns 01194
01194, 00000, "The listener command did not arrive in a secure transport"
// *Cause: Most of the listener administrative commands are only intended to
// be issued in a secure transport, which are configured in
// secure_control_ parameter. If the parameter is set, then the listener
// accepts administrative requests only on those secure transports.
// *Action: Make sure the command is issued using a transport specified
// in secure transport list.

I then noticed that my listener.ora parameter SECURE_REGISTER_LISTENER was set to TCP:

SECURE_REGISTER_LISTENER = (TCP)

To allow for dynamic instance registration, I needed to allow for the other protocol, IPC, too:

SECURE_REGISTER_LISTENER = (TCP,IPC)

My tests showed that they both need to be present, in that particular order.

In fact, the listener.ora file could be as simple as this:

ADR_BASE_LISTENER = /orainst/oracle
LOGGING_LISTENER=on
TRACE_LEVEL_LISTENER=off
SECURE_REGISTER_LISTENER = (TCP,IPC)

  SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
        (ORACLE_HOME = /orainst/oracle/product/19c)
        (SID_NAME = cdb)
      )
    )

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
    )

and still allow for remote client connections over TCP.

Sources: Oracle Net LISTENER Parameters for 19c

How to remove all postgres packages on a Linux RH server

Logged in as root on the server you would like to remove all postgres packages from:

Stop and disable the current postgres services (in this case, postgres version 15):

systemctl stop postgresql-15
systemctl disable postgresql-15

Remove the packages using "yum remove":

rpm -qa | grep postgres | xargs yum remove -y

What does it mean to run autoconfig on an EBS DB tier?

In a nutshell, it means running adautocfg.sh on the database server.

This is what I did when the listener.ora was missing on one of my EBS servers:

1. Login as the oracle software owner on your server
2. Remove softlink in $TNS_ADMIN called "listener.ora" which points to $TNS_ADMIN/mydb_myserver/listener.ora

cd $TNS_ADMIN
rm listener.ora

3. Create new listener.ora directly under $TNS_ADMIN

cd $TNS_ADMIN
vi listener.ora
-- add the following --
cdb =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = minserver.skead.no)(PORT = 1521))
    )
  )

SID_LIST_cdb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /orasw/oracle/product/19_21)
      (SID_NAME = cdb)
    )
  )


USE_SID_AS_SERVICE_cdb = ON
STARTUP_WAIT_TIME_cdb = 0
CONNECT_TIMEOUT_cdb = 10
TRACE_LEVEL_cdb = ADMIN

LOG_DIRECTORY_cdb = /orasw/oracle/product/19_21/network/admin
LOG_FILE_cdb = cdb
TRACE_DIRECTORY_cdb = /orasw/oracle/product/19_21/network/admin
TRACE_FILE_cdb = cdb
ADMIN_RESTRICTIONS_cdb = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_cdb = OFF

# added parameters for bug# 9286476
LOG_STATUS_cdb  =  ON
INBOUND_CONNECT_TIMEOUT_cdb = 60

# ADR is only applicable for 11gDB
DIAG_ADR_ENABLED_cdb  = ON
ADR_BASE_cdb = /orasw/oracle/product/19_21/admin/mydb_myserver

#IFILE=/orasw/oracle/product/19_21/network/admin/mydb_myserver/listener_ifile.ora

4. Run autoconfig

cd $ORACLE_HOME/appsutil/scripts/mydb_myserver/
./adautocfg.sh
Enter the APPS user password:

Result: autoconfig succeeds.

5. Create a new softlink under $TNS_ADMIN

cd $TNS_ADMIN
ln -s mindb_minserver/listener.ora listener.ora

Script snipplet to test for subdirectories

echo "Test: are there subdirectories in /orainst/oracle/cfgtoollogs/dbca/cdb ?"
ls $(pwd)/*/ > /dev/null 2>&1
if [ $? == 0 ]; 
then 
  echo "Yes"
  export PDBDIR=`ls -d */ | awk -F '/' '{ print $1 }'`
  grep ERROR --exclude-dir=${PDBDIR} * | egrep -v $ignorelist
else 
  echo "No"
  grep ERROR * | egrep -v $ignorelist
fi

The code above is based on a finding in this post on Stack Overflow.

Script snipplet to scan the dbca logs for errors

Here is a way to scan the logs in the directory where dbca creates its logfiles, by default under your $ORACLE_BASE/cfgtoollogs/dbca/$ORACLE_SID.

If it happens to be a multitenant database, dbca will create subdirectories for the PDB(s).

If you want to skip scanning these, exclude them from your grep command, like below:

cd /orasw/oracle/cfgtoollogs/dbca/cdb
export PDBDIR=`ls -d */ | awk -F '/' '{ print $1 }'`
export ignorelist='ORA_IGNORE_CVU_ERRORS|exectask.sh|utlrp0.log|RMANUtil\$RMANUtilErrorListener.handleError:1386|RMANUtil\$RMANUtilErrorListener.handleError:1383|RMANEngine.readSqlOutput:1046'
grep ERROR --exclude-dir=${PDBDIR} * | egrep -v $ignorelist

The errors I have excluded are based on my own observations on what's important or not during dbca usage.

Script to check for the number of control files in a database and create minimum two if only one is found

A script that checks for the number of controlfiles and creates two new ones if only one is found.

# 080224: Created 
# Check if there is only one controlfile present. If yes, create two new ones in the desired locations
#!/bin/bash
#cleanup old files
chkcntrfiles() {
rm -f /tmp/chk_cntrfile_location.sql
rm -f /tmp/chk_cntrfile_location.log
sqlfile=/tmp/chk_cntrfile_location.sql
logfile=/tmp/chk_cntrfile_location.log
echo "Check controlfile locations in database " $ORACLE_SID
cat << EoF > ${sqlfile}
   set lines 200 heading off feedback off verify off echo off termout off
   set pages 0
   set trimspool on
   col name format a70
   spool ${logfile}
select name
from v\$controlfile;
exit
EoF

cd /home/oracle/&&sqlplus -s -L / as sysdba @${sqlfile}
}
chkcntrfiles

if [ `cat ${logfile} | wc -l` -ge 2 ]; then
  echo "There are at least 2 controlfiles present"
else
  echo "There is only one controfile present."
  echo "Will now shutdown db and make another copy"
  echo "shutdown immediate" | sqlplus / as sysdba
  export org_contr_file=`cat ${logfile}`
  cp -v $org_contr_file /disk1/oradata/$ORACLE_SID/control01.ctl
  cp -v $org_contr_file /disk2/fra/$ORACLE_SID/control02.ctl
  echo "startup nomount" | sqlplus / as sysdba
  echo "alter system set control_files='/disk1/oradata/$ORACLE_SID/control01.ctl','/disk2/fra/$ORACLE_SID/control02.ctl' scope=spfile;" | sqlplus / as sysdba
  echo "startup mount force;" | sqlplus / as sysdba
  echo "Check new controlfile locations in database " $ORACLE_SID
  chkcntrfiles
  if [ ! `cat ${logfile} | wc -l` -ge 2 ]; then
   echo "We tried creating two new controlfiles but failed for some reason. Manual checkup required."
   exit 1
  fi
  echo "Alles OK!"
  echo "alter database open;" | sqlplus / as sysdba
fi

exit