DB Notes

 -----taking full backup (compressed) ------------ RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/backup/%U';   ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT '/backup/%U';   ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT '/backup/%U';   ALLOCATE CHANNEL ch4 DEVICE TYPE DISK FORMAT '/backup/%U';      BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;      RELEASE CHANNEL ch1;   RELEASE CHANNEL ch2;   RELEASE CHANNEL ch3;   RELEASE CHANNEL ch4; } ------- some datafiles are missing, full backup available ------- rman target / sql 'startup mount'; list backup of database summary; --Buradaki tag bilgisi alınır ve restore başlatılır. RESTORE DATABASE from tag TAG20240317T180940; RECOVER DATABASE; -- RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 126 and starting SCN of 2605758 -- If you get an error like above, run the command below: recover database until sequence 126; alte...

Steps below can be used to recover disconnected & unrecoverable standby databases. The commands below should be run on standby server.   1) Create pfile: create pfile='/tmp/pfile.ora' from spfile;   2) Drop old database. RMAN> startup mount; RMAN> sql 'alter system enable restricted session'; RMAN> drop database including backups noprompt;   3) Create spfile from pfile. sqlplus / as sysdba create spfile from pfile='/tmp/pfile.ora'; startup nomount;   4) Start database recover operation. rman target sys@primarydb auxiliary sys@stbydb (names coming from tnsnames.ora) duplicate target database for standby from active database dorecover nofilenamecheck; 5) After recovery completed, run the command below: alter database recover managed standby database disconnect from session; Note: If you get an error, shutdown the database and re-mount it. (startup mount)    6) Run the select below, if GAP_STATUS column equals to "NO GAP"  then recover the da...

Here are the steps for MSSQL Connections. Download the Compatible Oracle Gateway Version depending on your database version. Setup via GUI. Be sure that ORACLE_BASE is as same as existing database, but ORACLE_HOME should be different than database's ORACLE_HOME.      database oracle_home: /oracle/db/19/dbhome_1     gateway oracle_home: /oracle/db/19/sql_gateway Specify only for SQL Server tools as that you'll need, ignore the rest. You can enter MSSQL Database ip, servername, port and databasename. On the next step where you have to configure listener, assign different port than existing database port (for example, GATEWAY_LISTENER) After GUI Setup has been finished, copy the init file like below and edit it. For the example, QADB is our remote MSSQL database name. [root@testserver admin]# cd /oracle/db/19/sql_gateway/dg4msql/admin [root@testserver admin]#cp initdg4msql.ora initQADB.ora [root@testserver admin]# vi initQADB.ora # # HS init parameters # HS_FDS_CON...

Using the at Command for Scheduled Maintenance We may need some maintenance works for our database systems. If we don't want to spend our evening with all the rebooting processes, the at command is here to help us. echo "<command>" | at 22:00 Jan 18 2024 This command above runs a specific job at the desired date and time. Here's a more specific example for maintenance on a PostgreSQL database: echo "yum update -y\nsystemctl stop postgresql-15.service\nreboot" | at 23:00 Jan 20 2024 We can list all jobs with the atq command. Moreover, using at -c <id> , we can check the details of scheduled commands. If a schedule was entered mistakenly, the at -r <id> command would help us remove it. atq at -c <id> at -r <id> This simple yet powerful tool can save you from staying up late for...

 Note: Before all steps, make sure that postgreSQL is installed to both primary and standby servers. If you don't have any primary server yet, make sure to initialize and put into running state. Do not initialize any database on standby side. 1- Be sure about primary and replica database server's firewall policies. They should have access to each other through postgreSQL port. 2- On the primary side, add a line as below in order to replication user access: host     replication     rep_user     <replica_ip>/32     scram-sha-256 3- Create replication user on primary server with postgres user. createuser -U postgres rep_user -P --replication -p <port> 4- Find the relevant lines in postgresql.conf below and edit: max_wal_senders=10 max_replication_slots=10 wal_keep_size=50000MB max_slot_wal_keep_size=50000MB wal_level=replica Note: Adjust wal_keep_size for your needs. Wal files will override when they reach size. 5- In order to bypass...

 When you need to automatize some batch operations via Python, you can create a function array and loop through it in order to run them from the start. Here is an example: from functions import * def main():     functions=[(BackupDB,(backupname,dbtype)),(ZipBackup,(backupname,)),(RemoveOldBackups,())]     for func,args in functions:          result=func(*args)          function_name=func.__name__          if result == 0:               print(f"{function_name} step has been completed")          else:               print(f"{function_name} step has errors. Program has been stopped.")               return main() As you can see, you can define parameters by opening another bracket after comma(,) in the function array. If no parameters, then only open a...

 Normally, you can't restore a single database from a cluster backup that are taken via pg_dumpall.In order to achieve that, you can follow the steps below: 1- Create a script that greps specific database portion of the dump: # ! / bin / bash [ $# - lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1 ; } sed "/connect.*$2/,\$!d" $ 1 | sed "/PostgreSQL database dump complete/,\$d" 2- Extract the portion via example command below: sh onedbscript.sh full_dump.dmp MY_DATABASE > onlyonedatabase.dmp 3- You may restore and test if it worked if you want: psql -p <portnumber> -d MY_DATABASE < onlyonedatabase.dmp