Database Recovery using Archive files

[Former Member]

Dears,

Due to some issue our database get crashed.

We have saturday night offline backup and archive backup till yesterday night.

We have restored offline backup and system is Up but it is on saturday night state.

We want to apply these archive files to make the system state to current.

In restore our control file also get replace with our saturday night control file status.

We have copied our archive files in oraarch directory.

Now when using brtools I am applying archive files:

Command executing in brtools:

recover from '/oracle/<SID>/oraarch' database

it shows

unknow command beginning

No recovery required

On sqlplus also when I execute

sql>recover database;

It shows no recovery required.

When I give command:

sql>recover database using backup control file;

it ask for some {file name| AUTO | CANCEL}

I give first file name of oraarch it shows "Can not access the file".

File permissions are ok.

I give AUTO, It also then do not do anything.

Please suggest the correct method to make my system in latest current date.

Shivam

[ashish_mishra2]

Hi Shivam,

1: Do not use 'AUTO' at all during recovery, if the log generated during database crash is applied,

you will again in same position where you were earlier.

2: You have not mentioned that after restore, was there any attempt to open the database ?

after restore:

sqlplus '/as sysdba'

startup mount;

select status from v$instance;

<output must be 'MOUNT'>

3: Start applying redo log :

SQL> recover database until time '2010-03-06:23:30:00' using backup controlfile;

< Date and timestamp must be before the time of your DB crash in format '2010-03-06:23:30:00' >

Output of this command will be a redolog file name (number), check in another session that the required file AND its subsequent files are present with proper authorization in /oracle/<SID>/ORAARCH

THEN ONLY CLICK ON 'AUTO'

4: once you get prompt " Media Recovery completed", you can proceed with openning the database and start SAP

applications.

Cheers !!!

Ashish

[Former Member]

Dears,

Yes after restoring the database we opened it.

Please also confirm the backup control file location.

Shivam

[ashish_mishra2]

Once the database is opened, you can not apply archive logs.

So your database is at the state of completion time of your offline backup.

If your customer ready with the dataloss from the time of time of backup completion till the cash happaned; no need to worry;

start the SAP and release the system.

if dataloss cannot be afforded, you will have to free your sapdatas, start restore again , open in MOUNT state ..and follow the procedure as described in previous message.

By the way:

would you elaborate more on this line:

> Please also confirm the backup control file location.

>

Cheers !!!

Ashish

[JPReyes]

The easiest way to achieve that is to use BRRECOVER, that will allow you to do the restore and the offline redologs in one go

Regards

Juan

[Former Member]

Dears,

As I said that control file also get replaced with old one.

So issue is when I restore with brtools it shows no recovery required.

As in brtools SCN , redo log group picks from control files.

and it of saturday night now.

From command I want to know "from backup control file"....Do SAP take backup of control file as in system only old is

available.

Shivam

[former_member524429]

Hi,

Alon with the suggestion provided by Juan,

Please refer Database Point-In-Time Recovery with BRTools and Restore and Application of Offline Redo Log Files with BRTools sections of this useful document "[Oracle Restore and Recovery|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/97a4f439-0d01-0010-d488-d29b45e2f5ab?quicklink=index&overridelayout=true]" to get more detailed information.

Also refer the following SAP Notes to get more info.

SAP Note 605062 - FAQ: Restore and recovery

Note 4164 - Incomplete Recovery ("...RECOVER UNTIL... ")

Regards,

Bhavik G. Shroff

[ashish_mishra2]

Hi Shivam,

This is absolutely normal that during the restore your control files will be replaced by the old one which were backed up during database backup.

Logic is that you can not apply the redolgs which are having the timestamps older than your restored datafile's / controlfile's timestamp.

If your DB is not yet opened, you can check the status of your database using below command which will tell you, till what timestamp your data is restored or currently available.

select to_char(CHECKPOINT_TIME,'yyyy-mm-dd:hh24:mi:ss') from v$datafile where file#=1;

>

> From command I want to know "from backup control file"....Do SAP take backup of control file as in system only old is

>

> available.

> Shivam

>

recover database using backup controlfile..... this command does not backup the controlfile . this will make use of old controlfile only which came along with the restore and start the recovery using this controlfile only.

Its just make use of available controlfile ..No backup will be triggered.

Please make a note that after DB restore; ..recovery is possible only in case database is not yet opened in OPEN state.

Cheers !!!

Ashish

.

[Former Member]

Hi Shivam,

As I understand from your post, you have restored the saturday night backup and already started the database i.e. you database state is OPEN. You can confirm this by executing "select * from v$instance;"

Archivelogs cannot be applied in OPEN state.

Pls note that the database has to be in MOUNT state in order to apply archive logs.

If your database is already OPEN, the only solution to bring the system to current state is,

1. Restore the offline backup again

2. Do not OPEN the database, keep it in MOUNT state

3. Apply archivelog files - recover database using backup controlfile

4. OPEN the database after all logs are applied.

Hope this helps.

Regards,

Kiran

[Former Member]

Hi Shivam,

From what I can get out of your situation is that you have restored an offline backup and now you want to recover until yesterday night just before the crash happened.

Assuming that all the archive files are available in the oraarch destination, you can give the following command on SQL prompt:

recover database using backup controlfile until cancel;

This will prompt you to provide the required archive log file. If you give auto then it will keep on taking until the last available file. Once you give cancel you can open your database with resetlogs option.

As per your post, you say that archive file cannot be accessed -- this means that the path name which you are mentioning is probably wrong. Give the correct path for the archive log

Else please paste the error..

Regards,

Anup