DB Notes

 Sometimes dummy and hard-coded vales might be needed for testing or some other purposes. We can return single rows easily with "select ...... from dual". But if we want several rows, special functions might be useful for it since "unioning" rows are not really useful and simple. Simple example is below: select column_value "mail" from table(sys.odcivarchar2list('alex@mymail.com','sally@mymail.com','campbell@mymail.com'); This will return a column with three rows as intended.

 Sometimes we may need to find some similar data (not exact one) regarding to our needs. For example, one digit of the phone number may have been registered incorrectly.So how can we find it?  From the classical point of view, we need to combine all possibilities. But we don't have to. But RDBMS' have some solutions for it. - For Oracle, you can use util_match.EDIT_DISTANCE function. Here is an example: select name,surname,utl_match.EDIT_DISTANCE('5822182911',phone) from HR.PERSONEL where utl_match.EDIT_DISTANCE('5822182911',phone)<=2 This looks for phone records that has only 2 different numbers (in the right order of course) from the original value (5822182911). Some other kind of functions are available at Oracle, according to your needs. - PostgreSQL has also a useful extension, named fuzzystrmatch. It also has some useful functions for detecting similar records. An example is below: SELECT levenshtein('FRANCE', 'FRANCOL'); --This returns ...

            It could be tricky to detect when your database slows down for no reason. At first, you should check system resources (CPU - RAM usage, network etc.) and if find some bottlenecks, then go deeper. ADDM report should be useful for detecting most problem causes.           But it is also probable that some queries may be using server resources excessively. How to detect them? Well, there is one simple query to check that: select s.sql_text,sl.sid,sl.target||'-'||sl.opname Target,sl.totalwork,sl.sofar,sl.time_remaining Seconds_remaining,sl.elapsed_seconds,sl.sql_id,sl.username from v$session_longops sl,v$sql s,v$session se where s1.sid=se.sid and se.sql_id=s.sql_id and totalwork!=0 and sofar<>totalwork;

     In general, people find dumping their whole databases in sever by pg_dumpall easily. But it takes too long since this tool is not utilized for using resources efficiently. Thus, it's essential to uncompress and parallelize jobs while taking dumps in PostgreSQL databases via pg_dump. Example command:                pg_dump -Z0 -j 8 -Fd mydb -f dump_folder -Z0 means "no compress" and -j 8 means use 8 cores. When using pg_restore, similar approach is applied to maximize performance. pg_restore -Fd -O -j 8 -d mydb dump_folder               

1. Check Required Packages Ensure the yum packages openssl and pigz are installed on your system. 2. Compress and Encrypt Files Use the following command to compress and encrypt files: tar -c -I pigz backup.dmp | openssl enc -aes-256-cbc -e -k mypassword > backup.tar.gz.enc 3. Decrypt and Decompress Files Use the following command to decrypt and decompress files: openssl enc -aes-256-cbc -d -in backup.tar.gz | tar -I pigz -x

 1-) Check network interface name with the command below: 2-) Go to network interface file location and copy & rename interface config file: ifcfg -a  cd /etc/sysconfig/network-scripts cp ifcfg-ens192 ifcfg-ens192:1 3-) Edit newly copied file, change the parameters below: IPADDR= (new ip address)  DEVICE=ens192:1 NAME=ens192:1  4-)  Edit postgresql.conf for new listen address: vi /<postgresqlfolder>/14/data/postgresql.conf listen_address='(new ip address)' 5-) Edit postgresql service file and add the parameters below at [Unit] section:  vi /usr/lib/systemd/system/postgresql-14.service Wants=network-online.target After=network-online.target 6-) Reboot the server if possible. Otherwise, reload all services. Since you need to be sure everything will work after a planned/unplanned reboot, restarting the server is guaranteed way. After the process, check if service working and virtual ip is up and running. 

 Check if application db drivers (e.g. postgresql jdbc ) support scram method. If not, replace with the newer version. In addition, Postgresql version should be above 10.0 . Uncomment  password_encyrption line if commented, and then set it to scram-sha-256. It should look like below:              password_encryption = scram-sha-256  All passwords in database should be re-entered so they can be encrypted with new method. Before going further, check if all roles are encrypted with scram-sha-256 via running the query below:          select * from pg_authid; In order to disable logins of users with md5 encryption, pg_hba.conf also should be edited. Example entries are as below:               # TYPE DATABASE USER ADDRESS METHOD            host           all           all ...