Oracle database recovery with data unloading

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



Bookmark this on Delicious

How to Flush your database caches

Ver este articulo en Español

Flushing the SGA memory areas, Shared Pool and Buffer Cache, it's an uncommon task, however, it gets useful when you're doing some tests and want to override the memory and go direct to disk, or when you have shared pool issues (here is a workaround, however I encourage you to find the root cause).

Shared Pool flush
This is the only sentence you have to know for releases 9i and up...

alter system flush shared_pool

Buffer Cache flush
For Oracle 9i I didn't know how to do this, fortunately found it today on Rahat Agivetova's blog

alter session set events = 'immediate trace name flush_cache';

I've tested it and does the job well...

And for Oracle 10g and up, the syntaxis is as follows:

alter system flush buffer_cache;

But you already knew that, isn't it? ...well, this was a "snack" post until Friday's follow up to Index Dynamics, which I may anticipate interesting results...

Thanks for reading... and for your comments, too!


You have UNDO space issues?: please read Who is using your UNDO space? Improved Script"
 
Subscribe to Oracle Database Disected by Email




Bookmark this on Delicious