PostgreSQL function similar to Oracle validate_conversion function

 

This function accepts a text value, and a data type; returns 1 if the text value can be converted to the data type, otherwise 0.   This is tested on  server 15.4

CREATE OR REPLACE FUNCTION validate_conversion (p_input TEXT, p_type VARCHAR(128))
RETURNS integer
AS
$body$
    DECLARE
           valid_convert integer = 1;
           tmp_val TEXT;
               
     BEGIN 
         p_type = TRIM(LOWER(p_type));
               
         EXECUTE FORMAT('SELECT CAST (''%s'' AS %s)', p_input, p_type)
         INTO tmp_val;
           
               IF LENGTH(tmp_val) <> LENGTH(p_input)
               THEN
                       valid_convert = 0;
               END IF;
               RETURN valid_convert;
                          
               EXCEPTION
                       WHEN OTHERS THEN
                               valid_convert = 0;
                               RETURN valid_convert;
END;
$body$
LANGUAGE plpgsql;  

NFS not mounting on Linux

 NFS not mounting

================

Seen below messages in /var/log/messages

Sep  1 08:57:45 server-app1 systemd[1]: u01.mount: Unit is bound to inactive unit dev-oracleoci-oraclevdb1.device. Stopping, too.

Sep  1 08:57:45 server-app1 systemd[1]: Unmounting /u01...

Sep  1 08:57:45 server-app1 nfsrahead[8179]: setting /u01 readahead to 128

Sep  1 08:57:45 server-app1 systemd[1]: u01.mount: Succeeded.

Sep  1 08:57:45 server-app1 systemd[1]: Unmounted /u01.

 

Change:

Created a new compute instance from a custom image which already has the mountpoint reference in fstab which we are trying to use (u01)

 

Solution:

-----------

After altering fstab, systemd need to reparse /etc/fstab and pick up the changes or else the mount point will be unmounted automatically by systemd.

Correct the entry in the /etc/fstab . Make sure that required mount point entry is fstab and unwanted is reqmoved.

Also, make sure to unmount if same nfs export is mounted in other mount point on this server

 

Reload systemctl or reboot the server to avoid this issue.

 

# systemctl daemon-reload

 

We have executed systemctl daemon-reload command and ran mount -a

Issue solved.

How to Resolve Oracle TNS Error ORA -12514 and ORA-12521

 When you are trying to connect to your database using TNS alias you may get below errors like

ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor

OR

ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor

For example you are trying to connect to a DB using sqlplus as below

sqlplus user1@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 23 05:28:08 2023

Version 19.18.0.0.0

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

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When you try tnsping your TNS alias 

Used TNSNAMES adapter to resolve the alias

# tnsping ORCL

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=orclhost1)(PORT=1526)) (CONNECT_DATA= (SERVICE_NAME=ORCL) (INSTANCE_NAME=ORCLCDB1)))

OK (0 msec)

Solution for ORA-12514

It seems the SERVICE_NAME given in the TNS alias is not correct. Please check with your DBA and verify the listener has a service named ORCL. Find out the correct service_name and update your TNS alias in the local tnsnames.ora file.

Solution for ORA-12521

If you are getting ORA-12521: TNS:listener does not currently know of instance requested in

connect descriptor, similarly, try tnsping of the TNS alias. In this case INSTANCE_NAME in the TNS alias description is wrong. Find out the correct instance_name and update your TNS alias in the local tnsnames.ora file.