by Robert Buda | Dec 8, 2017 | Backup and Recovery, Best Practices
Don’t let the Backup Grinch steal Christmas
I have written about this topic in the past but it seems that it can never be shared too many times. As we approach the holiday season, we don’t want to be recovering data when we should be spending precious time with family and friends. So I will share another cautionary tale about how what we don’t know can hurt us. And how we can improve the chances of an uninterrupted holiday celebration.
This past weekend, during a routine health check that we perform for one of our clients, one of our Oracle DBAs found that backups starting failing a few days prior. The client had no idea that there was any problem with the backup.
In this case, the database backup itself was successful, but the ongoing control file and archive log backups failed. Basically this means that in the event of a failure, they would be unable to restore any transactions that happened since the backup. This could result in a major loss of data.
This is another illustration of the critical importance of having a dedicated team whose responsibility it is to check the health of your database and your backups on a regular and frequent basis.
And now with my own special version of a holiday classic (a thousand apologies to Clement Moore), I wish you all healthy databases and consistent backups all through the year!
A Visit from The Buda Team
Twas the night before weeks-end, when all through production
Not a backup was running, there was an obstruction
The schedule had been set, so long ago now
No-one thinks they should check it, its just running somehow
And while you were nestled all snug in your bed
You had no way of knowing your backup was dead
But the Buda Team, ready to check out your logs
Are poised to find out that your system has hogs
Bad programs preventing the backup from storing
But now that we fixed it you can go back to snoring
In the morning we’ll tell you what happened last night
And with one great big sigh you’ll say “So glad we’re alright!”
Happy Holidays!
by Robert Buda | Oct 2, 2017 | Backup and Recovery, Best Practices
Planning for Disaster
As we think about the enormous cleanup effort taking place in the wake of
Harvey, Irma, and Maria, we are reminded of the importance of planning to keep your business up and running when disaster strikes. In this blog post I share the thoughts of an expert in the field of Business Continuity Planning and Disaster Recovery.
I recently had an interesting and fun conversation with Bob Cohen, Business Continuity and Disaster Recovery Practice Director at Pivot Point Security, about some key aspects of Business Continuity Planning and some cautionary tales about the risks of not doing so.
[table id=3 /]
Notes:
I hope you enjoyed the conversation that I had with Bob. PivotPoint and Buda work together to ensure that our client’s data assets are secure and protected against all types of threats.
If you have any questions about how to ensure that you are prepared to recover your databases in the event of a disaster, please give us a call at (888) 809-4803 x 700 and if you have further thoughts on the topic, please add comments.
by Robert Buda | Sep 5, 2017 | Backup and Recovery
How is a database backup like a portable generator with a gasoline leak? And why should you care on a beautiful sunny September morning? How does any of this relate to Business Disaster Recovery and Business Continuity planning and testing? Read On!
Lack of Preparation
Until Superstorm Sandy in 2012, we had lived in our NJ town for 25 years without ever having a power outage for more than a few minutes. I thought it would never happen, and never prepared for it. I had no portable generator or other storm supplies.
Then on October 8. 2012, I learned how important it is to anticipate and prepare for the the unexpected. We lost power for about 7 days when the storm ravaged our state. Even after the power went out, I thought for sure it would be back on at any moment. A transformer must have blown, I thought, or a tree came down on a wire, they just have to reroute the power! We don’t get power outages in Robbinsville!
By the time I learned that our sub-station was three feet under water and it would be days before we had power, not a generator was to be had anywhere in NJ. Even if Home Depot had any, we could not get out of our development to get there because of fallen trees. If only I had prepared…
I learned my Lesson
So this morning, for the 10th time in 5 years, on this beautiful Sunny Labor Day, I went out to perform my twice-yearly test of the Generac GP 5500 Generator that I purchased a few months after Sandy when they again became available. The generator sits in my garage, waiting to be hooked up the transfer switch that I installed when I purchased it.
Leakage
I pulled the generator into the driveway, opened the gas valve, and gasoline proceeded to leak all over my driveway! After pulling up a you-tube video, I learned the the cause was likely to be a stuck float in the generator. In order to fix it, I had to take apart the carburetor, and for that I needed a 3/8 inch socket with a 1/4 inch drive and a pivoting socket extension. Why am I telling you this? Because in the middle of a hurricane, you don’t want to run out to Home depot to get a 3/8 inch socket with a 1/4 inch drive and a pivoting socket extension which you likely do not have in your garage.
Are you as prepared as you think you are?
Back to my story, so since I don’t already have the aforementioned tool in my garage, I decide to ask my friend and neighbor if he happens to have something that might work for my task. He did not but while in his garage talking about generators, he told me about his nice new generator that he bought a few years ago that was still sitting in a box in the garage. He had never tested it or even removed it from the box! I thanked him for looking for the tool and then left him with some friendly advice to test the generator soon, and offered assistance to install a transfer switch to make the generator even more useful when the time comes. It made me wonder how many people have generators sitting in a box in their garage.
All Fixed
After a trip to Home Depot to get the proper tool, which I could not have taken during Sandy, I took apart and cleaned the carburetor, with the help of the you tube video, which I could not have watched during Sandy, and the generator started on the first pull, with no leakage! So now at least when it comes to power, I am better prepared for the next storm.
Back to Database Backups
So what does all this have to do with database backups? The generator sitting in the box in my neighbor’s garage is like a database backup sitting in your data center, but that you never tested. It makes you feel good that it is there, but when the wind is blowing and the rain is falling and the disks are crashing and the data center is filling up with water, you don’t really know that it is going to work.
However, a database backup in your data center (with an off-site copy) that gets tested, cleaned up, and maintained on a regular basis, is more like the generator sitting in my garage that is far more likely to be ready for the next storm.
Which database backup would you rather rely on?
If you would like to discuss how to ensure that your backups will work when you need them, give us a call at (888) 809-4803 x 700 and if you have further thoughts on the topic, please add comments!
If you enjoyed this article please like and share!
by Robert Buda | Aug 3, 2017 | Backup and Recovery, Best Practices, Database Architecture, Oracle
Architecting for Backup and Recovery Efficiency with Oracle RMAN
Database architecture is critical to achieving many business objectives. These include application performance, business continuity, security, and geographic distribution. This article will explore another often overlooked objective that can be influenced by the database architecture: backup optimization and recovery efficiency.
Very large databases are common in today’s business environment. Multi-terabyte databases are prevalent in all but the smallest organizations. Despite these large sizes, we tend to find that data that pertains to earlier time periods tends not to change, and tends to be used less frequently than more current data. When architecting the database, the age of the data, and therefore the possibility of data changing can be used as a factor in the physical database design that can optimize backup and recovery efficiency and performance.
RMAN Backup Optimization
It is a given that all data must be backed up. But taking that backup puts load on the database that impacts application performance during the time that the backup is running. Common approaches to mitigating this impact include backing up from a standby database rather than the production database, taking offline database backups when the application is not available, and restricting the backup time to non-peak times so the machine resource usage is minimized.
However, in some environments, those options are not available. The first option, backup up from a standby database, may not be an option of you don’t have a high availability environment. Bringing the database down is not an option in a 24×7 production environment. And there are many databases that are so large that the time it takes to back up the entire database is simply too long and exceeds the non-peak times for the application.
Partitioning and Archiving
Another technique that may be used is to build partitioning and archiving into the database architecture. We can partition the data into physically separate tablespaces, and place each partition into a separate tablespace. This allows us to isolate data from past time periods that are kept for archiving purposes but are not frequently queried and are never going to be updated. These separate tablespaces can be backed up when the data reaches the point that it will not be changed again, and then it can be excluded from the normal backup routine. In many databases, older data represents a very large portion of the overall database, so such a scheme can significantly reduce the backup time, thereby significantly reducing the impact on the application.
There are a number of ways to exclude tablespaces from the backup after they have reached the point where they will not be updated again, including:
- Making the tablespaces readonly, and configuring Backup Optimization in Oracle RMAN. After this, RMAN will backup the the tablespace enough times to satisfy the retention policy and then will exclude them on subsequent backups.
- Using the RMAN command CONFIGURE EXCLUDE FOR TABLESPACE command. Once configured, the specified tablespace will be excluded from future backups. These tablespaces can be manually included explicitly in other backup sets to ensure that the data is backed up but they can be excluded from full backups.
Here is an example of how we might use this: lets say that we have an Oracle Database Application that collects traffic sensor data. Each day we collect a large set of data from traffic sensors from municipalities around the country. We have very large tables that contain hundreds of datapoints per sensor. Each table contains hundreds of gigabytes of data stretching back 10 years. The tables are partitioned so that a new partition is created for each month, and as the data is collected, it is automatically placed into the proper partition. At the beginning of each year, we can take a single backup of all the tablespaces that hold the data from the prior year. We know that data will never change, so we do not have to include those tablespaces in future backups. We can set these tablespaces as readonly, and then with backup optimization turned on, RMAN will then exclude them from subsequent backups, but will still enforce the backup retention policy so you wont lose backup sets that are necessary to restore those tablespaces. An added benefit is that the backup set each week will be significantly smaller thereby reducing disk requirements for the ongoing backup sets.
Restore Efficiency
In addition to significantly reduced backup time, partitioning the data in this way also improves the efficiency of the restore process because if one partition fails, the others do not need to be restored. This can result in significant time savings during a restore.
Other Benefits
There are other benefits to partitioning your data beyond the improvements to the backup and restore process. By separating older data which typically does not change, and is accessed less frequently, from the newer data, we have the ability to place the older data on less costly media. And regardless of the media type, there are performance benefits to separating data onto different drives/controllers (particularly useful when using separate storage arrays as opposed to SAN environments).
Thinking Ahead
When architecting database, think about what the impact of the backup and RMAN database recovery process will look like after 10 years. Architecting the backup and restore efficiency into the database design at that time will save lots of redesign later on.
If you are struggling with cumbersome backup optimization and restore processes or are about to do a database design or redesign, please give us a call at (888) 809-4803 x 700 and if you have further thoughts on the topic, please add comments!
If you enjoyed this article please like and share!
by Robert Buda | Jul 5, 2017 | Backup and Recovery, Best Practices, Oracle
Oracle Recovery Manager Purpose And History
Oracle Recovery Manager (RMAN) was developed essentially as a way to more effectively backup and recover Oracle databases, as an automation mechanism for the same, and as a way to keep track of and managing backup sets. It was introduced in version 8 of the Oracle database, so it has been around for quite a while. In addition to managing backups, it includes other functionality that helps with managing standby databases and creating duplicate databases.
In this article I will discuss several ways that RMAN can be used to duplicate a database and to recover an existing database. My objective is to discuss the different uses for RMAN and some important enhancements that have been made over time. I will not include specific syntax or detailed instructions but there are links to the Oracle documentation at the end of the article where you can find more detail.
In terms of managing backups, RMAN has many benefits over managing backups manually. Some of the more important benefits include its ability to track the available backups and the location of the backup files, and the ability to enforce retention strategies by preventing the removal of backup media (as long as the backup files are not deleted outside of RMAN). The robust way that RMAN manages backup sets contributes to its utility for duplicating and restoring database.
Active Vs. Backup-Based Duplication — The Evolution Of Database Duplication With RMAN
Database Duplication has been enhanced in important ways in the past few versions of Oracle.
In Oracle 10g, the only option was to create a duplicate database using the backup files and archived redo logs of the source database. This meant that the target host had to have access to these files so we had to copy these to the the target host before the duplicate database operation could take place.
Oracle 11g introduced Active Database Duplication as a new option. With this option, the backup files are no longer required because RMAN duplicates the database by copying images of the source database files (not backup files) directly over the network. If desired, however, we can still use the original backup-based duplication.
Oracle 12c introduced Active Database Duplication using backup sets. Now, when using this option, we can choose between using database file image copies (this was the only option when using Active Database Duplication in 11g) or using backup sets. Using the new backup set option has a number of advantages including reduced load on the source system and the ability to use encryption and compression options when creating the new database. This is similar to using the original backup-based duplication except that we don’t have to copy the files over manually.
Key Use cases of RMAN
Create A Test Database Or Refresh A Development Database
RMAN can be used to create a duplicate database for testing or development purposes. A test database is useful for application testing, testing of Oracle Upgrades, or for refreshing a database to be used for development purposes. We can duplicate the entire database or only a portion of it.
Be aware of the security implications of using data from production in your test or dev environments and take proper precautions to protect those environments. Consider using Data Masking to prevent sensitive data from being exposed to your Test and Development Environments. A future blog post will discuss how to use Data Masking for this purpose.
Here are the high level steps for duplicating a database for test purposes (Oracle 12c):
- Determine our naming convention for files on the destination host. This is important if we are creating the duplicate database on the same host as the source database.
- Decide what type of duplication we will be doing (Active vs Backup Based, If active, Image vs Backup Sets)
- Make source database backups available to the destination if necessary (depends on the type of duplication)
- Create and prepare an instance on the destination host to hold the duplicate database.
- Launch RMAN and connect to the source and destination databases (in the RMAN context, the source database is known as the target database and the destination is known as the auxiliary database). The channels and connections that we specify in this step will depend on the type of duplication we are performing.
- Issue the RMAN DUPLICATE command: There are various important options that will need to be set depending on the type of duplication we are performing.
Create A Standby Database For Disaster Recovery
RMAN can be used to create a standby database to be used for disaster recovery purposes. The steps for creating a standby database are similar to the steps for creating a test or dev database except that we will specify ‘FOR STANDBY’ and ‘DORECOVER’ in the RMAN duplicate command.
Optionally, when using a standby database for disaster recovery, we may also wish to configure standby redo logs on the primary database and configure the Data Guard Broker to facilitate automatic switchover and failover operations.
Create A Standby Database For Reporting
RMAN can be used to create a standby database to be used for reporting purposes. And as of Oracle 11g, if using the separately licensed Active Data Guard Option, the reporting database can stay in recovery mode (redo logs being applied) so the data does not get stale during reporting. Prior to 11g, in order to report using a physical standby database for reporting, recovery had to be suspended while the database was open for reporting. The process of creating a standby database for reporting purposes is similar to the process of creating one for disaster recovery except that at the end of the process we open the database in read only mode.
RMAN Database Recovery
In the event of the loss of a datafile or a tablespace, RMAN can be used for database recovery. Because RMAN keeps track of when data file and archive log backups were taken and where they are located, it can easily restore these files when we issue a RESTORE command. And a a RECOVER command then performs the recovery operation. The basic steps that we follow when recovering a database or data file are as follows:
- Determine what needs recovery: (Entire Database, Data File, Tablespace).
- Launch RMAN and connect to the database to be restored and to the RMAN repository.
- Confirm that the required devices and channels are configured and configure them if necessary.
- Issue the required RESTORE and RECOVER commands
Notes And Links To Detailed Instructions
I hope you found this discussion of ORACLE RMAN database recovery and duplication helpful. Please leave any comments or questions and I will do my best to respond.
If you need help managing your Oracle database or just have a question, give us a call at (888) 809-4803 x 700 or visit www.budaconsulting.com.
by Robert Buda | Jul 8, 2016 | Backup and Recovery, Best Practices, Oracle, SQL Server
Another new client, Another bad backup
I originally wrote this post in July but I am updating it today because it is so important. Yesterday we did an initial Oracle Database Health Check for another new client and found yet again that their backups are useless. This time the client was taking backups regularly, but was not preparing the database first and was just copying the database files. Unfortunately this renders the backup useless.
Here is the original post:
One of the first things that we do when engaging a new client for Oracle or SQL Server Database Administration support is to do an inventory of their current databases and to validate that they have a good backup and recovery scheme in place. The vast majority of the time, we find that there are either no backups being taken at all, or that not all of the data and configuration files are being backed up properly, despite the fact that the client thinks they are.
Often, there was a proper backup in place at some time in the past, but over time, the configuration of the system changed rendering the backup useless. These changes include changes in disk configuration, addition of data files, and other changes, that were made at the operating system level, configured properly in the database, but never modified in the backup configuration.
It is a simple oversight that can have catastrophic effects. We recently spent over three months helping a new client recover many years worth of data that they thought they were backup up properly, but they were wrong!
A regularly scheduled review of the backup scheme and a test recovery is the best way to ensure that your Oracle or SQL Server database is being properly backed up.
Contact Buda Consulting today for a comprehensive Disaster Recovery review of your Oracle or SQL Server Databases.
