Thursday, September 2, 2010

Oracle database recovery with data unloading

Add to Technorati Favorites
Ver este articulo en Español

What I'm going to share with you is an example of how you can recover from an apparently dead-end situation when a database crashes due to storage failure and gets corrupted.

During past two days I was engaged with a customer where the disk array had a double failure on a RAID-5 arrangement, hence lossing information. When asking for backup... none!!!; unfortunately this database wasn't backed-up regularly (was managed by an application team, not customer's formal Storage-OS-DB areas), database was in NOARCHIVE mode and on top of that NOLOGGING for all objects, including objects (remember we can turnoff logging on indexes),

Well this seems like a terminal case, one that you cannot escape ... but Oracle provides a Data Unloading service for this cases when either corruption or hardware failure renders a database unable to open, I'm part of Support - Advanced Customer Services within Oracle Corporation and our team is entitled to realize those services, under a service contract. There is no guarantee of how much information can be recovered and our tool only recovers data within datafiles/blocks, but hey! that's good help. (Hint: database dictionary included)

But this case was difficult, since customer didn't had QA nor DEV environments, where we can get DDL for objects like stored procedures or indexes, I had to individually extract database dictionary objects, load them on new database distinct schema (of course not SYS) and modify some DBA_ views in order to point these objects. Of course wasn't able to use DBMS_METADATA.GET_DDL because it uses SYS objects, and I didn't wanted to mess with database's DD. So, I had to handcraft scripts to get DDL for sequences, indexes, constraints, views and stored procedures/functions ... lot of work!!! but customer got almost all data back, all structure and very happy, so worth the time invested ... this experience is something they won't forget, as a matter of fact they are going to transfer administration to operational departments.

Thank you for your time and see you later!
Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Delicious Bookmark this on Delicious

No comments:

Custom Search