Re: how to recover the DB from a dmp file?

On Jun 30, 9:49 am, Fabio Zanotti <zanotti.fa..._at_gmail.com> wrote:
> Hi all,
> we had corrupted one datafile and now the DB could not be open, but
> can be
> mount. Unfortunately, we are running our DB in NOARCHIVE mode and we
> don't have a backup
> too. We have a full dmp file only.
>
> STARTUP FORCE
> ORACLE instance started.
>
> Total System Global Area   27480224 bytes
> Fixed Size                    73888 bytes
> Variable Size              18845696 bytes
> Database Buffers            8388608 bytes
> Redo Buffers                 172032 bytes
> Database mounted.
> ORA-01113: file 5 needs media recovery
> ORA-01110: data file 5: '/opt/oracle/oradata/st0918bo/indx01.dbf'
>
> plz. help me, how to recover the DB from a dmp file?.
>
> regards.
>
> Fabio Zanotti.

After trying alter database recover datafile to see if the necessary information is in the online redo logs then shut down the current database and make a cold backup. This way you can also put what you have back.

Then run a CREATE DATABASE command to recreate the database, control files, and spfile. Removing all existing files may be the easier approach to recreating the database so I suggest you do this. All you really need is the system tablespace if you have a full backup but I like to preallocate all my tablespaces.

Once the database is re-created start a full import. The import will recreate the database tablespaces, datafiles, users, tables, indexes, etc ....

There may be some minor data inconsistencies in the result depending on the actual data relationships verse defined FK relationships in the data but your database will basically be back to the time of the export. If the export was made when the database was not in use then you should be back at that point.

We backup up development databases this way and have done this a couple of times for recovery or migration purposes.

If you have any doubts open and review the Backup and Recovery manual then the Utilities manual for exp/imp or expdp/impdp.

HTH -- Mark D Powell -- Received on Mon Jun 30 2008 - 09:13:36 CDT