Cleanup of the ADR

Sometimes you may want to clean up your adr. I had this problem after I had just recently created a new database on my server called "db01".

In my case, I had the following adr homes set:

ADR Homes:
diag/rdbms/db01/db01
diag/rdbms/db02/db02
diag/rdbms/db03/db03
diag/rdbms/db04/db04
diag/rdbms/db05/db05
diag/clients/user_oracle/RMAN_610688766_110
diag/clients/user_oracle/host_610688766_110
diag/clients/user_oracle/host_610688766_82
diag/tnslsnr/dbhost1/listener
diag/tnslsnr/dbhost1/mylsnr
diag/tnslsnr/dbhost1/testlistener

This caused operations against ADR to fail with

ADR HOME selected:  diag/rdbms/db01/db01 diag/rdbms/db02/db02 diag/rdbms/db03/db03 diag/rdbms/db04/db04 
DIA-48415: Syntax error found in string [diag/rdbms/db01/db01] at column [26]

DIA-48415: Syntax error found in string [diag/rdbms/db02/db02] at column [26]

DIA-48415: Syntax error found in string [diag/rdbms/db03/db03] at column [26]

DIA-48415: Syntax error found in string [diag/rdbms/db04/db04] at column [26]

Syntax
error

Solution: clean out the old folder physically:

cd /u01/oracle/diag/rdbms
rm -rf db02 db03 db04 db05

cd /u01/oracle/diag/tnslsnr/dbhost1
rm -rf mylsnr testlistener

cd /u01/oracle/diag/clients/user_oracle
rm -rf *

Afterwards, the adr should you can check your adrci settings. They should look better now:

ADR base = "/u01/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/db01/db01
diag/clients/user_oracle/RMAN_610688766_110
diag/tnslsnr/dbhost1/listener

List folders only - no files

For future quick reference:

I prefer:

ls -d */

dir1/      dir2/       di3/   dir4/ 

Also good:

ls -l | grep "^d"

drwxr-xr-x 4 root   root           4096 Oct 13  2022 dir1
drwxr-xr-x 2 root   root           4096 Mar 14 11:17 dir2
drwxr-xr-x 2 root   root           4096 Mar 26 15:51 dir2
drwxr-xr-x 2 root   root           4096 Apr 27  2021 dir4

A third option:

ls -F | grep "/$"
 
dir1/
dir2/
dir3/
dir4/

Arguments that can be passed to "dbca -createCloneTemplate" usage output

dbca -createCloneTemplate
   -sourceSID source_database_sid | -sourceDB source_database_name
   -templateName new_database_template_name
   [-promptForWalletPassword]
   [-rmanParallelism parallelism_integer_value]
   [-maxBackupSetSizeInMB maximum_backup_set_size_in_MB]
   [-dataFileBackup {true | false}]
   [-datafileJarLocation data_files_backup_directory]
   [-sysDBAUserName SYSDBA_user_name]
   [-sysDBAPassword SYSDBA_user_password]
   [-useWalletForDBCredentials {true | false}
       -dbCredentialsWalletPassword wallet_account_password
       -dbCredentialsWalletLocation wallet_files_directory]
   [-uploadToCloud 
       -opcLibPath OPC_library_path
       -opcConfigFile OPC_configuration_file_name
       [-rmanEncryptionPassword rman_encryption_password ]
   [-compressBackup { true | false } ]
   [-walletPassword database_wallet_password]

Documentation here

What is the reason behind the message: "xauth: file /root/.Xauthority does not exist "?

What does the message that you sometimes see when attempting to launch X applications on a Linux server

"xauth:  file /root/.Xauthority does not exist"

really mean?

It means that the .Xauthority file, which stores X11 authentication cookies, does not yet exist in your home directory (in this case, /root/).

This is common when:

X11 forwarding has not been set up or used, yet.

You are logging in as root and have not initialized X11 in that session.

The file was deleted or never created.

To work around the issue, follow these steps:

1. Make sure xauth is installed

# RHEL/Centos
su - 
yum install xauth

#Debian
apt install xauth

2. Make sure you use the -X flag (X forwarding) when connecting to the server

ssh -X root@yourserver

3. On the server, edit the file /etc/ssh/sshd_config, and make sure these lines exist and are set to yes

X11Forwarding yes
X11UseLocalhost yes

4. Restart ssh daemon

systemctl restart sshd

5. Try launching a graphical app, like xterm

If these work, your X11 forwarding is properly set up, and .Xauthority will be created automatically.

6. If you need to manually create the .Xauthority file

This is rarely necessary, but you can run:

touch ~/.Xauthority
xauth generate :0 . trusted

How to create a database with non-default blocksize using dbca

Prerequisites:

You have a valid Oracle 19c installation in a server

You have set your Oracle environment already, so that your PATH, ORACLE_SID etc already exists when you execute your script

1. create a response file called $ORACLE_SID.rsp, in this case, tstdwh1.rsp:

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=tstdwh1.oric.no
sid=tstdwh1
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=false
templateName=/u01/oracle/product/19c/assistants/dbca/templates/New_Database.dbt
sysPassword=mysecreatpassword
systemPassword=mysecreatpassword
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=/data02/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/data04/fra/{DB_UNIQUE_NAME}
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=tstdwh1,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=tstdwh1,ORACLE_HOME=/u01/oracle/product/19c,SID=tstdwh1
initParams=undo_tablespace=UNDOTBS1,sga_target=4710MB,db_block_size=32768BYTES,nls_language=NORWEGIAN,dispatchers=(PROTOCOL=TCP) (SERVICE=tstdwh1XDB),diagnostic_dest={ORACLE_BASE},control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/u04/fra/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=400,pga_aggregate_target=1570MB,nls_territory=NORWAY,local_listener=LISTENER_TSTDWH1,db_recovery_file_dest_size=7851MB,open_cursors=300,log_archive_format=%t_%s_%r.dbf,db_domain=oric.no,compatible=19.0.0,db_name=tstdwh1,db_recovery_file_dest=/u04/fra/{DB_UNIQUE_NAME},audit_trail=db
sampleSchema=false
memoryPercentage=40
automaticMemoryManagement=false

Notice the parameter db_block_size=32768BYTES in the "variables" directive, and the fact that I am using the New_Database.dbt template. This template is more flexible than the others delivered by Oracle, but makes the whole database creation process take more time, as they do not include any databaes files.

Also notice the absence of the directive "databaseType" - it is only meaningful if you let DBCA choose a built-in template based on that type (like OLTP or Data Warehouse).

2. execute the dbca directly in the shell:

dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent 

Or, create a wrapper around the command, make it executable and execute it in the background:

#!/bin/bash
start=$(date +%s.%N)
export GLOGFILE=run_dbca.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "Now running run_dbca.sh"
echo "Resetting oratab..."
echo '' > /etc/oratab
if [ $? -ne 0 ]; then
 echo "Could not erase oratab. Exit."
 exit 1
else
 echo "oratab erased. Continuing..."
fi
export instanceAlive=`ps -ef| grep pmon | grep -v grep |awk '{ print $8}' | cut -f 3 -d"_"`

if [ ! -z ${instanceAlive} ]; then
 echo "Instance for database $ORACLE_SID is already running. Shut it down first"
 exit 1
fi
dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent
echo "Finished running run_dbca.sh"
dur=$(echo "$(date +%s.%N) - $start" | bc)
printf "Total execution time for run_dbca.sh: %.6f seconds\n" $dur
exit 0

chmod 755 run_dbca.sh
nohup ./run_dbca.sh &

Solution to "ORA-12546: TNS:permission denied" when starting sqlplus

Problem: when trying to start sqlplus, the error below is thrown:

ORA-12546: TNS:permission denied

Cause: Incorrect permissions on the oracle binary.

It needs the setuid permissions. Without those permissions, it cannot open sockets or shared memory properly — and the ORA-12546 is thrown when trying to connect via sqlplus.

Solution: Relink the oracle binary

oracle@myserver:[cdb]# ls -l $ORACLE_HOME/bin/oracle
-rw-r--r-- 1 oracle dba 0 Jan 24 09:57 /u01/oracle/product/19c/bin/oracle
oracle@myserver:[cdb]# cd $ORACLE_HOME/rdbms/lib
oracle@myserver:[cdb]# make -f ins_rdbms.mk ioracle

Afterwards, permissions are correct:

oracle@myserver:[cdb]# ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 462603312 Apr 11 10:54 /u01/oracle/product/19c/bin/oracle

How to mimic sqlplus variable behaviour in PostgreSQL

Here is how you can mimic the usage of variables at the prompt using psql and PostgreSQL like you would with oracle and sqlplus/sqlc:

\connect mydb
\prompt 'Enter value for querid: ' v_quid
\echo You entered: :v_quid
SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id = :'v_quid';

Run it:

sql -h pgserver1.oric.no -U dbadmin -d postgres -f find_rest_of_statement.sql

Enter value for querid: -7644745457188850826
You entered: -7644745457188850826
                                                    query_sql_text                                                    
----------------------------------------------------------------------------------------------------------------------
      THE QUERY TEXT WILL SHOW UP HERE :-)