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