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>