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