grep for all database names in /etc/oratab

The file /etc/oratab contains:

#
mydb01:/orainst/product/19c:Y
cdb:/orainst/product/19c:N

To extract the database names:

grep "^[^#]" /etc/oratab | awk -F':' '{print $1}'

Result:

mydb01
cdb

Solution to script warning: here-document at line < num > delimited by end-of-file (wanted `!')

In a script, I had formatted my code as follows:

  for l in $(cat file.txt|grep 'Status'|  awk -F' ' '{print $2}'); do
    if [ $l != "SUCCESS" ]; then
       echo $l ": Errors found. Please check logfile status.log"
    else
      echo "Readjust memory for the remaining cdb instance"
        su - oracle <<!
        
        -- code here --
      !
      runAdjustMemory
    fi
  done

During execution, my script failed with

./myscript.sh: line 32: warning: here-document at line 20 delimited by end-of-file (wanted `!')
./myscript.sh: line 33: syntax error: unexpected end of file

Cause: The termination character, in this case the exclamation point ! was indented in the code.

Solution: Remove the formatting and pull the termination character all the way to the left margin of your editor:

  for l in $(cat file.txt|grep 'Status'|  awk -F' ' '{print $2}'); do
    if [ $l != "SUCCESS" ]; then
       echo $l ": Errors found. Please check logfile status.log"
    else
      echo "Readjust memory for the remaining cdb instance"
        su - oracle <<!
        
        -- code here --
!
      runAdjustMemory
    fi
  done

Solution to ORA-28547: connection to server failed, probable Oracle Net admin error

When trying to perform sqlplus actions against one of my databases, I received

oracle@oric-dbserver01:[mydb01]# sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 17 10:43:56 2024
Version 19.22.0.0.0

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

ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error

Cause:

This is a consequence of the fact that oracle nls files are not found in its default location for this particular server.

If there are no .nlb files in $ORACLE_HOME/nls/data, you need to find out where they are located and set the ORA_NLS10 parameter correctly

If you lookup the error you will get a hint about this fact:

oracle@oric-dbserver01:[mydb01]# oerr ora 28547
A failure occurred during initialization of a network connection from a client process to the Oracle server.  ... 

The most frequent specific causes are: [ oracle lists several potential causes here ]

The character set of the database is not recognized by the client process, which may be caused by an incorrect or unnecessary setting 
of the ORA_NLS10 client environment variable or by a new or user-defined character set installed in the Oracle server and used for the database.

Solution:

Set the ORA_NLS10 environment variable:

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

When this is done, sqlplus will work.

Don't forget to add the same to your .bash_profile for the operating system user owning and running the oracle server software. For most installations I have seen, this will be the os user called "oracle".

Solution to [FATAL] [DBT-05509] Failed to connect to the specified database (cdb) in dbca

I was attempting to create a container database using dbca like this:

dbca -createDatabase -responsefile /home/oracle/scripts/cdb.rsp -silent

The following error kept coming up:

[FATAL] [DBT-05509] Failed to connect to the specified database (cdb).
   CAUSE: OS Authentication might be disabled for this database (cdb).
   ACTION: Specify a valid sysdba user name and password to connect to the database.

Solution:

Same solution as in my previous post "My solution to ORA-12701: CREATE DATABASE character set is not known":

If there are no *.nlb files in the default location $ORACLE_HOME/nls/data, then set the ORA_NLS10 parameter to the place where these files actually resides. In my case, they were found in the sub directory 9idata instead:

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

Otherwise Oracle won't be able to find the language files it needs to create the database.

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>