DB Notes

 You can see the python code below. It's suitable for RHEL/Centos/OEL servers: import subprocess import datetime import sys output = subprocess.check_output(["rpm","-qa","kernel-uek*"]) output = output.decode().strip().split("\n") output.sort(key=lambda x: subprocess.check_output(["rpm","-q","--queryformat","%{INSTALLTIME}",x])) output.reverse() active_kernel=subprocess.check_output(["uname","-r"]).decode().strip() active_kernel="kernel-uek-"+active_kernel #print(active_kernel) #print(output[0]) if active_kernel == output[0]:     print("Aktif kernel en güncel sürümdedir.")     sys.exit(0) else:     most_recent_install_date = subprocess.check_output(["rpm","-q", "--queryformat","%{INSTALLTIME}",output[0]])     most_recent_install_date = datetime.datetime.fromtimestamp(int(most_recent_install_date))     active_install_date = s...

Initial Steps Summary Let’s sum up the initial steps, then dive into key points: Ensure that the primary and replica servers have proper firewall configurations. Both servers should be able to communicate over PostgreSQL service ports (default: 5432 ). Edit pg_hba.conf on the Primary Database by adding the following line to allow the replica server to connect: host replication rep_user /32 scram-sha-256 Create a replica user on the Primary Database with the following command: createuser -U postgres rep_user -P --replication -p Edit the postgresql.conf file on the Primary Database: wal_keep_size = 10000 MB max_slot_wal_keep_size = 10000 MB Note: Adjust these values based on your needs. For a high data load, increase the value. For a low data load, decrease it. The above values assume that, at most, 10GB of data might fail to be sent during an outage. Key Points 1. Handling Large Databases For large databases, replication may take hou...

SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD ' || ' TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_INDEXES WHERE STATUS='UNUSABLE' UNION SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE' UNION SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME || ' REBUILD SUBPARTITION '||SUBPARTITION_NAME|| ' TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_IND_SUBPARTITIONS WHERE STATUS='UNUSABLE';

 Sometimes in production servers, you might need to check specific record(s) in order get older versions. Oracle has Flashback technology to revert your datas to a specific SCN or timestamp. In order to use it, Flashback option should be enabled. After enabling, Oracle will start to save tables at specific times. select * from <table_name> as of timestamp(sysdate - interval '10' minute) The query above shows you the table's older version. You can also narrow results with WHERE clause: select * from <table_name> as of timestamp(sysdate - interval '10' minute) where proc_id=3843

 When we need to connect somewhere via cron job, batch script etc. , .pgpass file can be really useful for authorization. Connection information is saved within the .pgpass file, allowing us to connect to database without password prompts. General text format: host:port:db_name:user_name:password You can also put asterisk(*) to first 3 parameters to match anything. Example .pgpass file content: 192.168.1.40:5432:postgres:myadmin:On3GoodP@ssW0rD Running commands to activate for pg_basebackup (Linux Environment): $ echo "192.168.1.40:5432:*:rep_user:V3ryStr0ngP4ss" > /var/lib/pgsql/.pgpass $ chown postgres.postgres /var/lib/pgsql/.pgpass $ chmod 0600 /var/lib/pgsql/.pgpass $ su - postgres $ /usr/pgsql-14/bin/pg_basebackup -h 192.168.1.40 -D /pgdata/14/data/ -U rep_user -p 5432 -v -P --wall-method=stream --write-recovery-conf

 We can run our scripts in background & write output to a log file as below: nohup sh myscript.sh > myscript.log 2>&1 & disown That will create a separate process that runs the script, and you can tail the log for output & errors.

 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.