Case Study: Automating the Activation of Standby Database in Read-Write Mode for Testing and Report Collection

- January 23, 2024

by Akshay Pawar, Oracle Database Administrator

Client needs MIS reports to run on a dedicated server from 8 am to 8 pm for real-time access to the latest data, alleviating the load on the production database. Outside these hours, the MIS server transitions into recovery mode.

Client Requirement: 
To alleviate the load on the production database, the client needs MIS reports to run on a dedicated server, ensuring real-time access to the latest data. The MIS server is set to read/write mode from 8:00 am to 8:00 pm specifically for generating MIS reports. Outside of these hours, the MIS server shifts into recovery mode.

Challenge:
In the ever-evolving realm of database management, maintaining uninterrupted testing and reporting while safeguarding production server performance poses a significant hurdle. Acknowledging this concern, our client presented us with a precise demand: the standby database should function in read-write mode for testing while seamlessly collecting reports without compromising efficiency.

To tackle this challenge, we deployed Dbvisit within our client's infrastructure, encompassing both a production database and a standby database. The frequent need for testing and report collection from the production server prompted our intervention, as executing these tasks directly on the production server resulted in performance bottlenecks.

Solution:
Dbvisit provided an effective resolution by enabling the standby database to be activated and restored as necessary. Our recommended strategy involved transforming the standby database into read-write mode (activating the standby database) for testing purposes. After completing the required tasks, the database could be switched back to mount mode (restoring the standby database), and Dbvisit seamlessly applied logs automatically. To streamline this procedure, we implemented a scheduled cronjob. Between 8 am and 8 pm, the standby database would function in read-write mode (activated standby), allowing the client to perform tests and retrieve data efficiently. Subsequently, from 8 pm to 8 am, the database would revert to mount mode (restored standby), ensuring minimal impact on production server performance.

• To activate the standby database, the subsequent steps were carried out:

Steps:
1. Initiate Backup: Commence a backup of the standby database.
2. Validation: Verify the backup on the standby database.
3. Activation: Activate the standby database.

• The process of reinstating the standby database included the subsequent steps:

Steps:
1. Drop Existing Database
2. Restore Backup: Recover the backup obtained during the activation phase.
3. Open Database in Mount Mode
4. Apply Logs: Between 8 am and 8 pm, archive logs from the production server are automatically applied, ensuring synchronization with the production server.

Crontab Output:

Activate Standby Script:

Reinstate Standby Script:

Through the implementation of this methodical approach, we provided our client with the capability to seamlessly carry out testing activities and gather reports without jeopardizing the integrity of the production server. The automated scheduling facilitated by Dbvisit additionally enhanced the efficiency of the process, ensuring optimal resource utilization and maintaining a streamlined database operation.