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 | Nov 25, 2014 | Backup and Recovery, Best Practices, Oracle DBA
Regulatory compliance issues are top-of-mind for today’s senior executives. New laws and industry regulations are changing how organizations acquire, store, manage, retain and dispose of data. Every Oracle DBA should be aware of these changes because of their sweeping impacts on the DBA job role.
Compliance goes hand-in-hand with security because regulations often mandate that organizations be able to attest or even prove that data—and therefore databases—are secure and controlled. In this context, Oracle DBAs are directly involved in implementing and managing the policies and technologies that support compliance.
What are some of the key regulations that impact Oracle DBAs? Here in the US, one of the most prevalent is Sarbanes-Oxley (SOX), aka the U.S. Public Accounting Reform and Investor Protection Act of 2002. SOX is meant to reduce fraud and improve financial reporting. Its impact on IT is sweeping. In particular, it holds the CFO responsible to guarantee the processes used to produce financial reports, which invariably involve software accessing data stored in databases via processes maintained by DBAs.
For healthcare organizations the major regulatory worry is HIPAA, the Health Insurance Portability and Accountability Act. HIPAA mandates security measures for patients’ personal health information (PHI)—to the extent that an organization must be able to document every time a PHI data element was viewed. HIPAA audits often focus on the processes that drive exception logs and reports. Database auditing is critical in this regard.
Here are some typical Oracle DBA tasks that directly relate to compliance:
- Data quality and metadata management. Ensuring data quality is key to regulatory compliance. If data or metadata aren’t accurate, how can the right data elements be subject to the appropriate regulatory controls?
- Database auditing. As mentioned above, robust database audit capabilities can be essential for compliance with HIPAA and other regulations and policies that mandate tracking database usage. What data was accessed when and by whom? Database audit software can tell you. Database auditing is also vital for overall information security and detection of security breaches, especially against internal threats.
- Data masking and obfuscation. Data masking practices are generally used to render original data suitable for testing purposes. It looks and functions consistently with the original data, but no longer constitutes personally identifiable information or credit card data, etc. for regulatory purposes. It’s also important for protecting sensitive data from staff (e.g., third-party contractors) working in non-production environments.
- Database archiving and long-term data retention. Regulations often mandate what data must be stored for what period of time. This is also important for legal/eDiscovery purposes.
- Database recovery. Database recovery is also a compliance issue, because it relates to database integrity and availability. If data is lost and can’t be recovered, that can be as problematic as a security breach from a regulatory perspective.
If you’re not sure whether your Oracle database policies, procedures and controls are adequate to support regulatory compliance, Buda Consulting can help. Contact us to discuss a database security assessment to identify areas of noncompliance and provide whatever assistance you need to address them.
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.
by Robert Buda | Apr 23, 2013 | Backup and Recovery, Best Practices, Database
Every now and then things happen that make us realize how important double checking things can be.
I had a recent experience in which four unrelated parties (including me) did the wrong thing regarding one business transaction. If any one of us had done the right thing, there would have been no problem. But all four parties dropped the ball, causing potentially serious problem. This is why it is important to leave nothing to chance. Here is the story.
A few months ago, our health insurance company asked us to send it a report detailing who was on our payroll last year. The following sequence of mistakes turned this simple request into potentially serious problem for our business.
Mistake number 1: I put that request that I received from the insurance company to the side and did not send the report to them by the due date. This would normally be OK, because the insurance company sends out a certified letter in the event that they do not receive the report in time.
But…
Mistake number 2: We have a mailing address and a different physical address. For some inexplicable reason, the insurance company sent out the certified letter to our physical address instead of our mailing address, where they send all other correspondence, and where we would expect important business mail to arrive. This would normally be OK because if we receive mail to our physical address, it will eventually get to our bookkeeper and be addressed.
But…
Mistake number 3: The US Postal service delivered the certified letter notices to wrong suite They delivered it to the neighboring business instead of to us. This happens occasionally. I dont understand why this happens but since most of our mail comes to our mailing address, and our neighbor usually brings our mail over when this happens, it is normally not a problem.
But…
Mistake number 4: For some reason that puzzles me most of all, the neighboring business never bothered to tell me or the mailman that they received two certified letter notices for me over a month ago! While I acknowledge that this party had the least obligation in the transaction, I am shocked at the lack of consideration that they showed.
So, what does all this have to do with your Database?
All four parties involved in the above scenario had a well defined job to do, or at least a well understood and expected course of action. All are trusted parties that usually do what is expected of them. We had every reason to believe that all four parties involved would perform their jobs properly. And yet not one of the parties involved did the right thing.
The same can be true of your disaster recovery processes. Just because you have specified what should happen with your database backup, and who should do it, does not mean it us actually being done!
As I mentioned in an earlier post, when we examine a client’s disaster recovery posture, we find in a large percentage of systems that the backups are not working the way the client thinks they are, and in many cases, they are not working at all!
So be sure to have an independent party validate that you are really as protected as you think you are.
Important Steps to take to validate database backups.
- Validate Database Backup Schedules.
- Validate that ALL required databases and Schemas are being backed up
- Validate that ALL database stored procedures, etc are being backed up
- Validate that all external files (such as Oracle control and redo log files and SQL Server transaction logs) are included properly in the backup.
- Validate that the database files are placed into a backup-safe state (eg, Hot Backups in Oracle) prior to backing them up.
- Perform Test Restores using actual backup files from the nightly production backup routine to ensure that the backups are usable in the event of a disaster.
Bottom line: Validate that your backups really do what you think they are doing, and that they will enable you to recover fully when the inevitable system failure occurs.
