Re: System scheme information accidentaly deleted

On Jan 18, 10:03 am, christianandradet <christianandra..._at_gmail.com> wrote:
> On 18 ene, 09:59, bdbafh <bdb..._at_gmail.com> wrote:
>
>
>
> > On Jan 18, 9:53 am, christianandradet <christianandra..._at_gmail.com>
> > wrote:
>
> > > Hi everyone,
>
> > > I have acidentaly deleted some information of the System scheme, some
> > > views and tables. Is there any chance to recover the information that
> > > was dropped.
>
> > > Thanks
>
> > what version of the database software is in use
> > (e.g. 10g R2 standard edition one with 10.2.0.3 patchset)
> > and what backups do you have?
> > (e.g. none, full cold backup from last night along with full datapump
> > export file)
>
> > If you have no backups whatsoever, you might want to go ahead and get
> > a cold backup of the database prior to starting to attempt to "fix
> > it".
>
> > If you have a support contract with Oracle Support, now might be the
> > time to use it (and open a service request on the Oracle Metalink
> > site:https://metalink.oracle.com)
>
> > -bdbafh
>
> The version is 10G standard edition one R2, I have the backups but
> what should I do now??

I'm assuming that since you ignored the question concerning a support contract, that Oracle Metalink is not an option to you. (one could have also assumed that if you had such support, you would have opened the support request there, rather than posting here in the first place).

If no transactions exist in the database since the time of the backups, then a full, closed, restore/recover is your best bet, if you can afford the downtime. If you have never tested your backups with a trial restore/recovery ... you might still want to backup what you have so that should the restore/recovery fail, you can still return the database to its existing state. Small steps where you can return to your prior position is very important here.

If you are restoring a hot backup set (user-managed) or using RMAN to restore and recover the database, make sure that you know of the time when the deletion/drop occurred so that you do not apply archived redo logs during media recovery through the time that such changes occurred.

If transactions have occurred in the database or you can't afford the downtime of replacing the existing database with the (unspecificed as to type) backup set, then perhaps a logical recovery may be advisable.

Do you have a full export (produced via exp or expdp)? This includes the system schema.

Is the database in archivelog mode?
Do you have the relevant archived redo logs?

One could also leverage the logminer tool to recover the deleted rows if a delete statement was used. If a drop table statement was used, are such tables in the recyclebin (as pointed out by Frank).

-bdbafh Received on Fri Jan 18 2008 - 09:16:38 CST