My solution to ORA-12701: CREATE DATABASE character set is not known

Some of our Oracle servers are set up to accommodate EBS.

During the provisioning process of a new oracle server, the Oracle database creation failed with the following errors:

SYS@mydb01>SQL>alter database "mydb01" open resetlogs;
alter database "mydb01" open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-12701: CREATE DATABASE character set is not known

As part of the EBS 12.1.3 installation guide, the parameter ORA_NLS10 is pointing to $ORACLE_HOME/nls/data/9idata, where all the NLS files are unpacked during installation.

However, by default, the ORA_NLS10 variable is pointing to $ORACLE_HOME/nls/data. In my case, this directory does not contain any nls files. They have all been moved to the sub directory 9idata.

As explained in Oracle Suppport Doc ID 1058400.6 "ORA-12701 When Creating a Database": ORA_NLS parameters determine where to find the NLS characterset libraries. If they cannot be found, Oracle cannot create the database with the characterset you have requested. The error was resolved by setting ORA_NLS10 explisitly during installation:

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

and rerun the installation scripts.

A scriptlet that will look for a file and when found, start displaying it using tail

A short script that will look for the existence of a file, and when found, start tailing it to std out:

while true; do 
 if [ -f /u01/createdb.log ]; then 
   tail -f /u01/createdb.log
 fi 
done

How to open the PDB$SEED database for patching in read/write mode

The following should only be done if you need to patch the PDB$SEED using datapatch, or under instructions from Oracle Support.

sqlplus / as sysdba
Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SYS@cdb>SQL> alter session set container=PDB$SEED;

Session altered.

SYS@cdb>SQL>alter session set "_oracle_script"=TRUE;

Session altered.

SYS@cdb>SQL>alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SYS@cdb>SQL>alter pluggable database pdb$seed OPEN READ WRITE;

Pluggable database altered.

SYS@cdb>SQL>select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ WRITE NO
SYS@cdb>SQL>exit

To put the PDB$SEED back into READ ONLY and RESTRICTED mode:

SYS@cdb>SQL>alter pluggable database PDB$SEED close;

Pluggable database altered.

SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
SYS@cdb>SQL>alter pluggable database PDB$SEED open read only;

Pluggable database altered.

SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

SYS@cdb>SQL>alter session set "_oracle_script"=FALSE;

Session altered.

SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

SYS@cdb>SQL>alter system enable restricted session;

System altered.

SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  YES

How to process a variable consisting of values separated by blank spaces using a for-loop construct in shell scripting

If you have a variable with to or more values, separated by space, it's very simple to loop through them and process them individually in a script.

Notice the space between the two strings in the variable $pmon:

pmon=`ps -fu oracle | grep pmon_$1 | grep -v grep | awk '{ print $8}'`

echo $pmon
ora_pmon_cdb ora_pmon_mydb01

for pname in $pmon; do
 echo $pname
done

Output:

ora_pmon_cdb
ora_pmon_mydb01

The for loop construct seem to take space as a delimter by default, so in this case it works out well.

How to extract the the first of two identical sentences in a log file using awk

I have a log file which repeats the same message twice.

I would like to save this message in a variable to use later in the script.

The following awk expression will pull the first of the two sentences out for you to save in a variable:

analyze_status=`cat /u01/oracle/cfgtoollogs/upgrade/auto/autoupgrade.log | grep 'ANALYZE and FIXUPS' | awk 'NR==1'`

Output of the command is:

echo $analyze_status
ANALYZE and FIXUPS modes are not run when converting a Non-CDB database to a PDB

Without the NR==1 I get the same sentence repeated twice:

ANALYZE and FIXUPS modes are not run when converting a Non-CDB database to a PDB ANALYZE and FIXUPS modes are not run when converting a Non-CDB database to a PDB

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