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.
by Robert Buda | Apr 21, 2016 | Backup and Recovery, Best Practices, Database Architecture, Oracle DBA
I recently received a frantic call from a client that believed they have been hacked and had to quickly recover data. They said that data belonging to two or more of their customers had been lost.
Our customer functions essentially as an application service provider (ASP). Their customers’ data is in an Oracle database that our client manages. Our client organizes this database such that each of its customers’ applications is served by a separate schema and they all share one database.
We have advised this client on numerous occasions to separate each of their customers’ data into separate databases, or at least separate tablespaces. This is a good idea for several reasons, one of which is recoverability. Unfortunately, they resisted our suggestions and today are probably regretting that decision.
Oracle Recovery Manager (RMAN) offers a few different options for recovery. You can recover an entire database, an individual tablespace or an individual data file. But you cannot recover an individual schema (table owner) and its objects unless they are in their own tablespace.
In the case of our client, it seems that the tables were lost at some time on a Sunday night, just prior to the nightly logical backup (export). The last good logical backup was from Saturday night.
The database is in ARCHIVELOG mode, meaning that RMAN could restore the database to any point in time, including right up to the point of the data loss. However, since the schemas (each of which serves a different customer) all share the same set of tablespaces, this type of recovery would wipe out any data since that point in time—even for the schemas (customers) that were not impacted by the loss.
Because our client’s customers that were not impacted had activity since the data loss event, we had one less tool in our recovery arsenal. If our clients’ customer data had been separated into separate tablespaces or databases, we could have recovered data for their customers that suffered loss without impacting the others at all.
We are now in the process of recovering the lost data from the Saturday logical backups. When that is complete, we will be doing a full RMAN restore to another location, where we will attempt to recover any lost data since the logical backup was taken. This will be a very arduous and time-consuming process.
The moral of the story is to consider recoverability when architecting your Oracle database. If you have users or applications that stand alone and may need to be recovered without impacting others, separate them at least by tablespace; and, if possible, use a separate database. The more you separate, the greater your recoverability options are.
It’s worth noting that the pluggable database option in Oracle 12c might assist in recoverability even further, if the reason for not separating your schemas into separate databases was ease of maintenance or resource concerns. With 12c you can create a pluggable database for each of your logically separate applications or users, while keeping the administration centralized and the overhead minimized. Check it out.
If you have had similar difficulties with restoring data due to the database architecture, please share your experiences. To talk over your database architecture considerations, contact Buda Consulting.
by Robert Buda | Feb 1, 2016 | Backup and Recovery, Best Practices
Your Oracle databases are the heart of business operations. To mitigate the business impacts of downtime and data loss, every Oracle database must be recoverable. This means every Oracle shop needs a strong backup and recovery strategy—one that takes into account the probabilities of hardware, software, network or procedural failure(s) in your current environment.
Losing database transactions against a customer-facing application could mean thousands of dollars in lost purchase orders and invoices. Do your backups still have you covered? Or are you vulnerable to unacceptable data loss or inability to meet recovery point objectives?
Some of the emerging challenges that can leave your Oracle data unprotected over time include:
- Rampant data growth
- Unstructured and/or new data types
- New and more complex storage landscapes
- Cloud and virtualization (of storage, applications, servers… you name it)
- New requirements for 24×7 availability; e.g., e-commerce applications
- New regulatory mandates
- Lack of funding or insufficient staff to manage growing backup demands
Without a viable backup strategy, Oracle database recovery is impossible. Likewise, an inadequate recovery plan leaves the viability of backups to chance. Many Oracle shops rely on Oracle’s Recovery Manager (RMAN) utility for backup and recovery operations. But a tool does not a strategy make. To know your data is protected you need a comprehensive backup and recovery strategy—and you need to test it regularly across a range of scenarios.
According to Oracle’s recent Oracle Database Management and Data Protection Survey, slow backups and long backup windows, backup management hassles and complex backup/recovery scenarios are commonplace even in large enterprises. A couple of interesting statistics:
- Many large organizations are struggling to backup hundreds of siloed databases adding up to petabytes of data daily.
- Overall, less than 5% of Oracle data is protected in real-time, meaning that the other 95% is vulnerable to data loss (usually all the data generated since the last backup).
- Speaking to the dynamic nature of backup needs, 45% of companies surveyed had changed their backup tools as a result of growing data volumes.
For many companies, Oracle database backup and recovery requirements are becoming more complex than their current staff can effectively handle. Can you even identify where your data is actually located, let alone how to resurrect it in the event of an operating system crash, DBMS crash, power loss, disk/hardware failure, cyber attack, disaster or (most commonly) human error?
A third-party Oracle DBA expert can support your data protection strategy in multiple ways, including reducing staffing and management costs associated with backup/recovery, augmenting your in-house expertise and resources, helping you deal with changing technology, and helping you review good practices and develop a cost-effective, practical strategy that will meet your business needs. An outsourced Oracle DBA expert can also leverage specialized tools and skills to help you recover corrupt, missing or deleted Oracle data.
To talk over your current backup/recovery process and challenges, and gain insight into how you can benefit from the latest technology and best practices, contact Buda Consulting.
by Robert Buda | Mar 12, 2014 | Backup and Recovery
While performing a migration from Oracle 10g to Oracle 11g (on new hardware) recently, we encountered a familiar frustration that I am surprised is not resolved yet. Privileges missing after Oracle full import.
There are multiple ways to migrate a database from one version to another. One of the oldest and easiest ways is to create your database in the target location and then use data pump (formerly Export and Import) to bring the data out of the source database and place it in the target.
I am a fan of Oracle and I think its reliability and functionality make it the best database for large, mission-critical applications, but by now I think we should have a full export (expdp) and import (impdp) utility that brings grants over reliably. The problem is that in certain cases not all grants are rebuilt on the target database. This may have to do with the order in which the users are created during the import, resulting in a grant taking place before a user is created, but sometimes they don’t even come over if you do a second import after all the users have been created.
This does not seem like a difficult task for an import routine to handle; multiple intelligent passes through the import file should resolve this. But we are still left to manually rebuild grants at times. Really Oracle?
We found Privileges missing after Oracle full import, So here is how we rebuilt them.
(there are a variety of ways to do this and this seemed like the best way this time).
In this case, there is only one user that owns tables (the application owner) so we needed only to gather the privileges for that user. Also, there were no other objects such as stored procedures that needed privileges set so we needed only to use dba_tab_privs to gather the privileges. Similar techniques can be used for other object types and for roles.
Here are the steps:
1. If using sqlplus, be sure to set the environment variables such as pagesize, heading, and linesize first, and then spool the output to a file.
2. Gather grants from the source database by issuing a select statement against the data dictionary tables that will build grant statements.
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where grantor = ‘APPOWNER'
/
This creates a set of grant statements like this:
grant SELECT on APPOWNER.TABLE1 to APPUSER;
grant UPDATE on APPOWNER.TABLE1 to APPUSER;
grant SELECT on APPOWNER.TABLE1 to APPUSER;
3. Execute the generated grants on the target system in sqlplus to restore your grants. These should be executed as the owner of the tables.
I hope future versions of data pump or successor utilities resolve this issue but until then this is one way to get around this annoying issue. This is only one of a number of ways to work around this annoying deficiency of the data pump utility.
If you have encountered similar issues and either know a way to avoid this entirely when using data pump or if you have a way of collecting the privileges that you like better than this approach please share your thoughts.
by Robert Buda | Jun 17, 2013 | Backup and Recovery, Database Security, Oracle
Infoworld today announced that they have been researching a flaw in oracle that can result in the inability to restore your database. Large oracle shops with interconnected databases are most at risk.
The problem revolves around oracle’s SCN (system change number). The number has a limit that if exceeded, can render a database unusable, even after restoring the database. Under normal circumstances, this limit would never be reached. However, there are two ways that the limit can be reached accidentally.
- A bug in Oracle’s hot backup mechanism results in a sharp increase in the SCN number under certain conditions.
- The SCN can be increased manually, resulting in all connected databases to increase their SCN as well.
The January Oracle Critical Patch Update has a patch that resolves the hot backup problem. We recommend that this patch be applied immediately, especially if you are a large shop or use hot backups. Another fix increases the limit and and makes it less likely to reach it, but the accidental (or deliberate) modification to an SCN remains a vulnerability. Extra care should be taken with all databases that connect to any critical databases in your environment.
Read the full article for more details.
If you have any questions or need assistance, please contact us.
