Need help with Data recovery

[SSChris]

We had a problem last week with our database, which unfortunately was not fully discovered until Monday. We restored from the Wednesday backup, last week and did not get very far with the recovery before problems arose.

How can I recover the data, that should be good, which I am missing from Thursday, Friday, Saturday and Monday?

Thank You.

 

[LKBrwnDBA]

Complete recovery involves using redo data or incremental backups combined with a backup of a database, tablespace, or datafile to update it to the most current point in time. It is called complete because Oracle applies all of the redo changes contained in the archived and online logs to the backup.

Did you apply the archive logs?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

SSChris,

The first question is, "Has your database been in ARCHIVELOG mode since prior to your failure?" If the answer is, "No", then you cannot "recover" the database; at best, you can only "restore" the database to the point in time of the last backup (or export, if that is what you are using in lieu of ARCHIVELOG mode.)

If you are in ARCHIVELOG mode, and if you have an unbroken chain of archived log files since your last full backup, then you do the following:

1) Ensure that the database instance is shutdown.

2) Restore to disk your most recent full backup of your database data files. (Do not restore your control files and your on-line redo log files; you want them to be up-to-the-moment current for the recovery process.)

3) Issue the following commands:

sqlplus /nolog
connect / as sysdba
startup mount
recover database
alter database open;

If everything worked properly, it results in an open, working database.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SSChris]

Thank you both.

I probably should have explained something first.
I am not a DBA - our DBA does not "work" for our company. Our DBA says he does not know how/if he can recover the data.

How do I know if we are in ARCHIVELOG mode?

Thank you.

 

[SantaMufasa]

SSChris,

Our DBA says he does not know how/if he can recover the data.

Yours is a potentially disastrous situation. I am happy to help. You are welcome to contact me directly in this case. My number appears on

http://www.anywho.com

as "Hunt, David, Salt Lake City, Utah, 84121".

The method to determine if you are in ARCHIVELOG mode is to issue this command while connected as "sysdba":

archive log list

The first line of results confirms whether or not you are in ARCHIVELOG mode. If the results say, "No Archive Mode", then you have "restore" as your only solution, not "recover".

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[marrow]

Just out of interest, once all is running OK can you add an ARCHIVE to an exsisting database?

 

[SantaMufasa]

Absolutely...here is the sequence:

shutdown immediate
startup mount
alter database archivelog;
alter database open;

To take a database out of ARCHIVELOG mode, follow the same sequence of commands, but chage "archivelog" to "noarchivelog".

Before putting your database in ARCHIVELOG mode, ensure that your database parameters that relate to archiving have appropriate values. At minimum:

log_archive_start=true
log_archive_format=ARC%S.%T ("S"=seq # position; "T"=Oracle Instance [thread] #)
log_archive_dest=<path where you want archives>

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[marrow]

Thanks Dave, that's execellent. But how do I actually initiate the "log_archive_<start> <format) & <dest> parameters, via alter or as they are?

 

[SantaMufasa]

Marrow,

If you are using exclusively an "init<SID>.ora" file, then you use a text editor to modify the values prior to a (re)startup of the instance. If you are instead using an "spfile" to define your instance parameters, then you modify the spfile accordingly.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.