Phone: +86 13764045638 Email: email@example.com 7 x 24 online support!
Salvage data from Oracle database with data dictionary corruptions
A database has experienced some damages - finally we have a running instance (with things like _allow_resetlogs_corruption=true).
We can only connect to the database as sys (works through network too). Connection as ordinary user doesn't work (results in ORA-01033)
Export doesn't work (since some data dictionary objects are not available)
Creating new objects, granting some privileges - doesn't work either
We can select data from some of the application tables (i.e. they have not been completely damaged)
We can't use some of the data dictionary views (e.g. dba_data_files, dba_segments fail). Others seem to work - e.g. dba_tables, dba_objects.
Seems database links to sys user doesn't work (so we can't easily connect to that database from another one)
My initial plan was to create db-link in another database and "create as select" for each table we want to salvage. However due to (6) we can't do that.
The number of affected tables is relatively large - dealing with each of them one by one is not feasible.
Any ideas for an easy way to save the data? What I'm thinking at the moment:
- Using some existing tool which connects to two databases and moves the data from the damaged database to the another one. (Should be able to connect to the damaged one as sysdba and not to rely on extensively querying objects' metadata since queries on data dictionary are very likely to fail)
- Writing own custom tool to do the tings in the previous point.
- Dumping data to external files (e.g. as insert statements; or other format): should be easy to automate bot the export and the import to the other database. (We don't want to spend many days to map flat file structures to tables).
- Finding some way to allow oracle creating database links to sys user (the it will be relatively easy to follow my initial plan).
Any suggestions? Recommendations for tools or other action plans?
PRM-DUL can unload table data from failed database even dictionary is corrupted .
You can reference this document http://www.askmaclean.com/archives/prm-dul-data-unloader-tool-manual-user-guide-0-4.html