RMAN Backup Validate

 Sometimes RMAN backup files might get corrupted. Sometimes disk block corruption might happen. So it is better we do validate the database backups more frequently in order to make sure it is recoverable.

Oracle Blocksize selection

I had gone through Oracle documents, forums, and other websites but could not get a clear picture of what blocksize to choose for my environment where 64 bit OLTP databases are running. I came across the following websites that give practical and more useful information about Oracle Block size selection.

http://www.ixora.com.au/tips/buffered_block_size.htm

http://www.nextre.it/oracledocs/blocksize.html

The Surprising Impact of Medium-Size Texts on PostgreSQL Performance

 This is a great explanation of Postgres toast internals for anyone who doesn't know about them 

https://hakibenita.com/sql-medium-text-performance

PostgreSQL : Influencing Query Execution Plan

By enabling and disabling the following parameters, we can influence the query execution plan generated by the optimizer.

MySQL : Troubleshooting Replication

If you have followed the instructions to make a replica (If not have a visit at this quick post), and your replication setup is not working, than the first thing to do is check MySQL error log. (if you dont know where it is? Just check my.cnf for the location of that file). Most of time, you will get the actual error from the log if not than you can try below things.

 

• Check master server and verify that binlog is enable or not and position is rotating or not by using “show master status” command.

• Master and slave server both must have unique server_id.

• Run “show slave status” command on slave server and check below values first.Slave_IO_Running:Slave_SQL_Running: Last_errno: 0 Last_error:

• If “Slave_IO_Running” is “YES” than slave is able to connect master and getting log entries from master but if it’s “NO” that means slave is not able to connect to master so check the values of Master_Host, Master_User, Master_Pass that it is right or not. Also check the connectivity between master and slave for mysql as well as network side.

• If “Slave_SQL_Running” is “YES” than no issue with sql thread butit its “NO” than you should get error on “Last_error”.

• Most of time, you will get the query related error in “Last_error” section.

• If the slave was running previously but stopped suddenly than the reason usually is that some statement that succeeded on the master failed on the slave.

• If a statement that succeeded on the master but refuses to run on the slave, try to do the below things. Because it‘s not feasible to do a full database resynchronization from the masterby copying a new snapshot/backup from the master.

• Sometimes, its happening when table changes are done in master server but somehow its not replicated on slave so slave will refuse to run the statement and give error. In this situation, we have synchronize the changes and start replication with simple “Start Slave” command.

• Sometimes, we are getting some error which can be solve by running that statement manually. At that time we can just stop slave completely, skip the statement with “ set global sql_slave_skip_counter=1” command, start the slave.

ORA-00845: MEMORY_TARGET not supported on this system

Recently, when I tried to increase the memory_target of Oracle database, I got the following error:

ORA-00845: MEMORY_TARGET not supported on this system

While checking, we found that /dev/shm size was 2 GB, but we were trying to increase the memory_target values to 7 GB that is more than the size of /dev/shm.  So we got the error.

Then we increased the /dev/shm size to 8 GB using the command as below

 mount -t tmpfs shmfs -o size=8192m /dev/shm

After that, we were able to bring up the database with the parameter value memory_target =7G without the issue.

Create Extension Fails : ERROR: could not access file "$libdir/dblink"

 

After my recent DB upgrade activity, I was unable to create extension and was failing with the below errors.

Find Uptime in PostgreSQL

 There are various ways to know since how long PostgreSQL is running. 

Below will cover 3 ways to find this.