Re: recover lost triggers

From: <sybrandb_at_hccnet.nl>
Date: Tue, 19 Jun 2007 20:44:25 +0200
Message-ID: <ik8g735rmifb8arf1k4sobcflvkbr2v3tq@4ax.com>

On Tue, 19 Jun 2007 11:23:49 -0700, rich <rschneid_at_nsf.gov> wrote:

>Hello all, I have joined a project which used to have a bunch of
>triggers. At some point prior to my arrival, some key tables (which
>had triggers) were dropped and re-created. However, the triggers
>never got recreated. They were not doing oracle wise backups.
>Sysadmin was just backing up all of the unix file systems. Now they
>have restored the backup (100GB +-) to the unix system. Noone wants
>to restore this old system over the new one. Is there a way for me to
>find the trigger text in this mass of unix files? Where or where are
>the triggers?
>
>Rich Schneider

Likely they are gone forever.
It looks the person calling himself sys-admin made a copy of an *open* database. That copy will be inconsistent. You could try to
- run alter database backup controlfile to trace on the original database.
- edit the trace file you get (it has a 'create controlfile ' statement in it), making sure

- you change the SID
- you change the database name
- you rename all files to the restored files
- then you set up a correct init<sid>.ora using the sid and the

database name you used in the previous step. - then (important) you cross your fingers and *pray* - then you the create controlfile statement on the new database.

If that doesn't work you can either
- dig up that old full database export
- search your creditcard in your wallet to have Oracle come in with their DUL (Data Unloader). No guarantees however.

If it does work, make a full export of the database with rows=n import it on the original database.

-- 

Sybrand Bakker
Senior Oracle DBA

Received on Tue Jun 19 2007 - 13:44:25 CDT