What is database replication?
Database replication is like having a backup generator in your home. The equipment that will ensure you continue getting your electricity supply even when the whole country is in blackout. In the world of databases, we always want to ensure that our databases run 24/7 without any issues or without downtime. To achieve this, some people ensure they come up with a backup plan. A replica of the main database is created and this replica stores up to date data of the original database. This replica is mostly hosted in a separate environment from the primary database so that in case the primary database goes down, the replica database takes over. Whenever the primary database and replica or secondary database are up to date with each other, we say these two are synchronized. Replication is achieved when these two are synchronized.
What is a replication issue?
Replication issue occurs when both the primary and secondary database fall out of sync. In this case the secondary database falls behind, and it no longer has the up to date data that is the same as the primary database. A real world example of this is the different time zones between continents or countries. We can say Chicago, USA and Nairobi, Kenya are out of sync with each other since these two are not in the same time zone.
How do we solve the replication issues?
The best way to solve any issue is always to find the root cause of the issue. One needs to dig deep and find out what led to the disaster. In this article, we will focus on the oracle database although some of these methods apply in other database management systems too.
1. Managed Recovery Process (MRP) not running
This is the process that ensures consistency between the primary and secondary database. It is the one that applies the changes made in primary to the secondary database. Whenever this process is down, changes made in primary database will not be seen in the secondary database. If you realize the MRP process, is down, you can always start it by running below command:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Sometimes the MRP might be running but still replication fails. A restart of the database can also help solve this.
Log into your standby database and run below commands:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
2. Full archivelogs in the secondary database
Too many archivelogs in the secondary database can lead to replication issues making the secondary database to lag behind. To solve this issue, navigate to the RMAN in the secondary database and delete the old archivelogs. After doing this, your primary and secondary database will be in a synchronized state.
Caution: Avoid deleting archivelogs directly from the disk.
3. Check for network issues
In order for the secondary database to get the updated data from the primary database, there should be communication between the two. A real life example is communication between two people over the phone. Whenever there is a network challenge, the two people on the phone will find it difficult to talk or communicate. The same applies to databases. Ensure you can successfully ping or you can easily reach the secondary database from the primary database and vice versa. You can do this by pinging the ip address of the secondary database from the primary server. When the ping is successful ie it does not fail, that shows primary database can reach the secondary database host. Do the same in the secondary database server by pinging the primary database host ip address. In case one server fails to reach the other, that shows there is connectivity issue. Fix the network issue and monitor the primary and secondary database until they are synchronized.
4. Listener issues
Listener problems in the secondary database may lead to replication issues. A listener in this case is like your middle-man or your transport personnel. The information from the primary database goes through the listener then to the secondary database. Whenever the listener is down or is not well configured, the secondary database will not receive updates from the primary database.
How do you troubleshoot listener issues?
a. ensure the listener is up and running in the secondary database.
b. Check the listener host and port configuration and ensure it matches the ones registered in the tnsnames.ora file
c. Ensure the listener is registered with the right database or the right service. you can do this by restarting the oracle database instance and run the below in the secondary database.
ALTER SYSTEM REGISTER;
Conclusion
Replication issues can sometimes cause you sleepless nights but with the right troubleshooting strategies, we can always overcome them. Have you encountered replication issues before? How did you overcome them? Feel free to share in the comments section how you solved them. In case you would like me to write about other database related topics, let me know too.
