Database Restoration

[Former Member]

Hi there,

Can anyone help me with my problem? Below are the details.

I have a working PROD instance. I created/installed a fresh instance in a separate machine. After I completed the installation, i restored the offline backup from my PROD instance to this new instance (called it UAT instance). After restoration, I run the CONTROL.SQL script and I got the error below:

SQL> @CONTROL.SQL

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 9009364992 bytes

Fixed Size 2035392 bytes

Variable Size 4513071424 bytes

Database Buffers 4479516672 bytes

Redo Buffers 14741504 bytes

Control file created.

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/UAT/sapdata1/system_1/system.data1'

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/UAT/sapdata2/temp_1/temp.data1'F

*

ERROR at line 1:

ORA-01109: database not open

SQL>

Is this normal? Why would it consider recovery when it was actually an offline backup Im using?

By the way, I restored my database using the command brrestore.

Thanks in advance.

[Former Member · Accepted solution]

Hi Aurelio,

Whatever the steps you are performing to restore offline backup is correct. But I think you made one mistake after restorting the datafiles...

If possible, restore complete offline backup and follow the below steps..

1. Restore complete offline backup.

2. Create control file or edit if you already have.

3. Change SID, and alter REUSE SET clause, (Assuming you are aware of modifying control file...)

4. Remove any DATABASE OPEN statement from controlfile...

5. Recover database using control file. (DO NOT ATTEMPT TO OPEN IT at this stage)

(see the sample control.sql file in next post for your reference...)

6. Perform recovery of required datafile user "RECOVER DATABASE USING BACKUP CONTROL FILE UNTIL CANCLE" statement.

7. If it ask for redo logs just cancle the recovery at that stage.

8. Once you see the message "database recoverd", then only you can attempt to open the database with "ALTER DATABASE OPEN RESETLOG" command.

Please note, once you attempt to open the database it will update the SCN of datafiles. If it failes to open, SCN on other datafiles will change and it will keep asking for media recovery...

Regards.

Rajesh Narkhede

[Former Member · Accepted solution]

Here is the sample control.sql file which I use to recover database using offline backup..

Please compare with yours....

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "<SID>" RESETLOGS
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 254
MAXINSTANCES 50
MAXLOGHISTORY 9542
LOGFILE
GROUP 1 (
'/oracle/<SID>/origlogA/log_g11m1.dbf',
'/oracle/<SID>/mirrlogA/log_g11m2.dbf'
) SIZE 50M,
GROUP 2 (
'/oracle/<SID>/origlogB/log_g12m1.dbf',
'/oracle/<SID>/mirrlogB/log_g12m2.dbf'
) SIZE 50M,
GROUP 3 (
'/oracle/<SID>/origlogA/log_g13m1.dbf',
'/oracle/<SID>/mirrlogA/log_g13m2.dbf'
) SIZE 50M,
GROUP 4 (
'/oracle/<SID>/origlogB/log_g14m1.dbf',
'/oracle/<SID>/mirrlogB/log_g14m2.dbf'
) SIZE 50M
-- STANDBY LOGFILE

DATAFILE
'/oracle/<SID>/sapdata1/system_1/system.data1',
'/oracle/<SID>/sapdata3/undo_1/undo.data1',
'/oracle/<SID>/sapdata1/sysaux_1/sysaux.data1',
'/oracle/<SID>/sapdata1/sr3_1/sr3.data1',
'/oracle/<SID>/sapdata1/sr3_2/sr3.data2',
'/oracle/<SID>/sapdata1/sr3_3/sr3.data3',
'/oracle/<SID>/sapdata1/sr3_4/sr3.data4',
'/oracle/<SID>/sapdata1/sr3_5/sr3.data5',
'/oracle/<SID>/sapdata2/sr3_6/sr3.data6',
'/oracle/<SID>/sapdata2/sr3_7/sr3.data7',
'/oracle/<SID>/sapdata2/sr3_8/sr3.data8',
'/oracle/<SID>/sapdata2/sr3_9/sr3.data9',
'/oracle/<SID>/sapdata2/sr3_10/sr3.data10',
'/oracle/<SID>/sapdata3/sr3_11/sr3.data11',
'/oracle/<SID>/sapdata3/sr3_12/sr3.data12',
'/oracle/<SID>/sapdata3/sr3_13/sr3.data13',
'/oracle/<SID>/sapdata3/sr3_14/sr3.data14',
'/oracle/<SID>/sapdata1/sr3700_1/sr3700.data1',
'/oracle/<SID>/sapdata1/sr3700_2/sr3700.data2',
'/oracle/<SID>/sapdata1/sr3700_3/sr3700.data3',
'/oracle/<SID>/sapdata1/sr3700_4/sr3700.data4',
'/oracle/<SID>/sapdata2/sr3700_5/sr3700.data5',
'/oracle/<SID>/sapdata2/sr3700_6/sr3700.data6',
'/oracle/<SID>/sapdata2/sr3700_7/sr3700.data7',
'/oracle/<SID>/sapdata2/sr3700_8/sr3700.data8',
'/oracle/<SID>/sapdata3/sr3700_9/sr3700.data9',
'/oracle/<SID>/sapdata1/sr3usr_1/sr3usr.data1',
'/oracle/<SID>/sapdata4/sr3_21/sr3.data21',
'/oracle/<SID>/sapdata4/sr3700_17/sr3700.data17',
'/oracle/<SID>/sapdata4/sr3700_18/sr3700.data18',
'/oracle/<SID>/sapdata4/sr3_22/sr3.data22',
'/oracle/<SID>/sapdata4/sr3700_19/sr3700.data19',
'/oracle/<SID>/sapdata4/sr3700_20/sr3700.data20',
'/oracle/<SID>/sapdata4/sr3700_21/sr3700.data21'
CHARACTER SET UTF8
;

Regards.

Rajesh Narkhede

[Former Member · Accepted solution]

Hi,

Im currently restoring my backup now. Will try your recommendations afterwards.

Thank you very much for the replies

[Former Member · Accepted solution]

Hi Rajesh,

Below is my control file creation script

CONNECT / AS SYSDBA

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE
SET DATABASE "UAT"
RESETLOGS
ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 1022
MAXINSTANCES 50
MAXLOGHISTORY 11680
LOGFILE
GROUP 1 (
'/oracle/UAT/origlogA/log_g11m1.dbf',
'/oracle/UAT/mirrlogA/log_g11m2.dbf'
) SIZE 50M,
GROUP 2 (
'/oracle/UAT/origlogB/log_g12m1.dbf',
'/oracle/UAT/mirrlogB/log_g12m2.dbf'
) SIZE 50M,
'/oracle/UAT/origlogA/log_g13m1.dbf',
'/oracle/UAT/mirrlogA/log_g13m2.dbf'
) SIZE 50M,
GROUP 4 (
'/oracle/UAT/origlogB/log_g14m1.dbf',
'/oracle/UAT/mirrlogB/log_g14m2.dbf'
) SIZE 50M
DATAFILE
'/oracle/UAT/sapdata1/system_1/system.data1',
'/oracle/UAT/sapdata3/undo_1/undo.data1',
'/oracle/UAT/sapdata1/sysaux_1/sysaux.data1',
'/oracle/UAT/sapdata1/sr3_1/sr3.data1',
'/oracle/UAT/sapdata1/sr3_2/sr3.data2',
'/oracle/UAT/sapdata1/sr3_3/sr3.data3',
'/oracle/UAT/sapdata1/sr3_4/sr3.data4',
'/oracle/UAT/sapdata1/sr3_5/sr3.data5',
'/oracle/UAT/sapdata2/sr3_6/sr3.data6',
'/oracle/UAT/sapdata2/sr3_7/sr3.data7',
'/oracle/UAT/sapdata2/sr3_8/sr3.data8',
'/oracle/UAT/sapdata2/sr3_9/sr3.data9',
'/oracle/UAT/sapdata2/sr3_10/sr3.data10',
'/oracle/UAT/sapdata3/sr3_11/sr3.data11',
'/oracle/UAT/sapdata3/sr3_12/sr3.data12',
'/oracle/UAT/sapdata3/sr3_13/sr3.data13',
'/oracle/UAT/sapdata3/sr3_14/sr3.data14',
'/oracle/UAT/sapdata3/sr3_15/sr3.data15',
'/oracle/UAT/sapdata4/sr3_16/sr3.data16',
'/oracle/UAT/sapdata4/sr3_17/sr3.data17',
'/oracle/UAT/sapdata4/sr3_18/sr3.data18',
'/oracle/UAT/sapdata4/sr3_19/sr3.data19',
'/oracle/UAT/sapdata4/sr3_20/sr3.data20',
'/oracle/UAT/sapdata1/sr3700_1/sr3700.data1',
'/oracle/UAT/sapdata1/sr3700_2/sr3700.data2',
'/oracle/UAT/sapdata1/sr3700_3/sr3700.data3',
'/oracle/UAT/sapdata1/sr3700_4/sr3700.data4',
'/oracle/UAT/sapdata2/sr3700_5/sr3700.data5',
'/oracle/UAT/sapdata2/sr3700_6/sr3700.data6',
'/oracle/UAT/sapdata2/sr3700_7/sr3700.data7',
'/oracle/UAT/sapdata2/sr3700_8/sr3700.data8',
'/oracle/UAT/sapdata3/sr3700_9/sr3700.data9',
'/oracle/UAT/sapdata3/sr3700_10/sr3700.data10',
'/oracle/UAT/sapdata3/sr3700_11/sr3700.data11',
'/oracle/UAT/sapdata3/sr3700_12/sr3700.data12',
'/oracle/UAT/sapdata4/sr3700_13/sr3700.data13',
'/oracle/UAT/sapdata4/sr3700_14/sr3700.data14',
'/oracle/UAT/sapdata4/sr3700_15/sr3700.data15',
'/oracle/UAT/sapdata4/sr3700_16/sr3700.data16',
'/oracle/UAT/sapdata1/sr3usr_1/sr3usr.data1',
'/oracle/UAT/sapdata4/sr3db_1/sr3db.data1',
'/oracle/UAT/sapdata4/sr3db_2/sr3db.data2',
'/oracle/UAT/sapdata4/sr3db_3/sr3db.data3',
'/oracle/UAT/sapdata4/sr3_21/sr3.data21',
'/oracle/UAT/sapdata4/sr3700_17/sr3700.data17',
'/oracle/UAT/sapdata4/sr3_22/sr3.data22',
'/oracle/UAT/sapdata1/system_2/system.data2',
'/oracle/UAT/sapdata1/sysaux_2/sysaux.data2',
'/oracle/UAT/sapdata4/sr3_23/sr3.data23',
'/oracle/UAT/sapdata4/sr3700_18/sr3700.data18',
'/oracle/UAT/sapdata4/sr3_24/sr3.data24',
'/oracle/UAT/sapdata4/sr3_25/sr3.data25',
'/oracle/UAT/sapdata4/sr3_26/sr3.data26',
'/oracle/UAT/sapdata4/sr3_27/sr3.data27',
'/oracle/UAT/sapdata4/sr3_28/sr3.data28',
'/oracle/UAT/sapdata4/sr3_29/sr3.data29',
'/oracle/UAT/sapdata4/sr3_30/sr3.data30',
'/oracle/UAT/sapdata4/sr3_31/sr3.data31'
;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/UAT/sapdata2/temp_1/temp.data1'
  SIZE 2000M REUSE AUTOEXTEND OFF;

[Former Member · Accepted solution]

Hi Rajesh,

I have completed the restoration. Then I followed the steps you provided. After I issued RECOVER DATABASE USING BACKUP CONTROL FILE UNTIL CANCEL, and entered CANCEL and it returned Media Recovery Cancelled.

Kindly advise what to do next.

Thanks in advance

[Former Member · Accepted solution]

Hi,

As your first attempt was failed, I would suggest remove the below lines from your "CONTROL.SQL" before attempitng to open the database.

ALTER DATABASE OPEN RESETLOGS;

(So that you can attempt to open database only after successful recovery)

You can perform recovery until you don't have required file which recovery is asking..

You can cancle recovery for ARCHIVE LOGS files, not for DATAFILES...

Make sure that you have restored REDO LOGS at proper location (mentioned in control.sql).

Also check if modified (with new SID) init<SID>.ora and init<SID>.sap file is there in /oracle/<SID>/102_64/dbs location.

If all datafiles are recovered, then you can attempt to start the database using below command.

SQL> ALTER DATABASE OPEN RESETLOG;

it should show "Database opened" message...

Regards.

Rajesh Narkhede

[Former Member · Accepted solution]

Hi Rajesh,

Actually, I have already removed the line ALTER DATABASE OPEN RESETLOGS as per your earlier recommendation. Below is the message after RECOVER DATABASE USING BACKUP CONTROLFILE UNTIIL CANCEL;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 478223377 generated at 04/03/2010 22:15:23 needed for thread

1

ORA-00289: suggestion : /oracle/UAT/oraarch/UATarch1_75886_636198394.dbf

ORA-00280: change 478223377 for thread 1 is in sequence #75886

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

CANCEL

Media recovery cancelled.

SQL>

What should I do next?

Thanks in advance

[Former Member · Accepted solution]

Hi,

First of all, if you are restoring database from OFFLINE backup, you won't need to use RECOVER DATABASE USING BACKUP CONTROL FILE statement...

You can directly execute CONTROL.SQL and once you get "SUCCESS" message, just open the database using below command.

SQL> ALTER DATABASE OPEN RESETLOG;

If you have stucked at the phase as you said in earlier post, You can try to perform point-in-time recovery as:

SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTILl '2010-03-04:22:15:23'

(use the timestamp as per the archive log file...)

Regards.

Rajesh Narkhede

[Former Member · Accepted solution]

Thanks. I will create another offline backup then.

Thanks

[nikolay_kumanov · Accepted solution]

IMHO, "Media recovery cancelled" means that media recovery is successful, even if the message is misleading. If it is not successful, it will also say that some of tablespaces are in an inconsistent state. The message just tells you that the recovery was terminated by a "cancel" response, which is the only way if you are using a backup controlfile. So now you can attempt opening the database by "alter database open resetlogs".

[Former Member · Accepted solution]

Hi,

After creating a new offline backup, I restored it and then run the CONTROL.SQL script. Then I executed ALTER DATABASE OPEN RESETLOGS and I still get the following error.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

Can anybody help me please?

Thanks

[former_member204746 · Accepted solution]

check contents of alert_SID.log to find out why it failed.

Last time I had this problem, it was caused by a different Oracle version between the restored database and the Oracle binaries.

[Former Member · Accepted solution]

Hi Eric,

How do I upgrade my target database? You see, Im using Oracle 10.2.0.2 for my source system and Oracle 10.2.0.1 for my target system. Upgrading the database of my target system also updates its binaries right. Hopefully this will solve my problem. Do you have any guide on how to upgrade our database?

Thanks

[Former Member · Accepted solution]

Hi,

You can download the patchset from Service Market Place..

Installation procedure is same as database installation...

Download the patchset and extract it.

You can use # jar -xvf <patchset_filename.zip> command to extract it...

1. Login as ora<sid>

2. Go to the patchset directory.

3. execute ./RUNINSTALLER file.

4. Check if the proper ORACLE_HOME is selected.

5. Follow the wizard..

Once installation is completed, do the post installation activity as mentioned in the README file available in extracted patchset directory.

I WOULD RECOMMED, UPGRADE YOUR DATABASE TO ORACLE 10.2.0.4 ON ALL SERVERS.

Regards.

Rajesh Narkhede

[former_member204746 · Accepted solution]

Hi Aurelio,

10.2.0.1 is not supported and never was supported by SAP, so, upgrade your destination system to 10.2.0.2 with the same fixes as your original system. This will fix your issue.

once it is fixed, set this topic's status to ASNWERED.

[Former Member · Accepted solution]

Hi

We have recently installed 4.6C with oracle 10 g and had to restore the database whose oracle was on 10.2.0.2 but the new installation had 10.2.0.1 so we had to install the patches to this new installation to upgrade it to 10.2.0.2.

Once this was done we did a system refresh from an offline backup and recovery is not required.All you need to do is just to run the sql script to create the control file and then run alter database open resetlogs and that should be all.

Of course having the patch levels of OS and Oracle is a prerequisite for system refresh

You can try downloading the patch from service marketplace.

Hope it helps

Regards

Prabh

[Former Member]

Thanks Eric.

After upgrading oracle database, I did a restoration once again and it started successfully.

To all who extended their assistance, thanks for the patience and insights.

Best regards

[peter_dzurov]

As you have current controlfile already created, I would suggest to issue command: recover database

After that you should be able to open database and create temporary tablespace afterwards.

[Former Member]

Hi Peter,

Thanks for the response. I did as you recommended but it returned:

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

[Former Member]

Hi,

As you say this is an offline Backup you can do :

recover database using backup controlfile until cancel.

Either give it the last couple of archive logs, or put in the path to the OralogA & B if asked for more recovery plus the name of the .dbf file

Then alter database open resetlogs;

Mark

[Former Member]

Hi Mark,

Thanks for the response. However, I dont have a copy of an archive logs. Its an offline backup from several weeks ago and I didnt keep a copy of the archive logs since its offline anyway. Saying that, is there no way for me to succesfully start my database without the logs?

Thanks in advance

[stefan_koehler]

Hello Aurelio,

if your backup is really an offline backup, you don't need to recover anything (and so no archive logs are needed).

1) Restore (backup control file) or recreate your control file

2) Restore database files

3) ALTER DATABASE OPEN RESETLOGS;

That's all you have to do, if you have performed an offline backup.

Regards

Stefan

[Former Member]

Hi,

We have faced the same issue. This issue is resolved by applying one or more Archieve redo log files and if it is not solved then giving the online Redo logs. I am sure that the problem can be resolved.

Thanks.

Nick S

[Former Member]

Hi,

Use your online redo logs for the recovery as this was a offline backup.

Mark

[Former Member]

Hi Stefan,

That's what I also believed. However, this is not whats happening. Also, the steps that you provided is also the one I used. However, we differ in sequence. What I did was.

1. Restore the database files

2. Recreate the controlfile

3. alter database open resetlogs.

Will the sequence in 1 &2 matter?

Thanks

Edited by: Aurelio Tadlas on Apr 29, 2010 11:20 AM

Edited by: Aurelio Tadlas on Apr 29, 2010 11:21 AM

[Former Member]

Hi Mark,

Can I use any online archive log provided it came from the same instance?

Thanks

[Former Member]

Hi,

Use

/oracle/<SID>/OriglogA/log_<>.dbf

and

OriglogB

hopefully the sequence for you recovery will be in these.

Tho in the my experience in the past when it ask for recovery of the 'system' data file there has been an issue, and you might have start the restore from afresh....

But try it what you got to lose..

Mark

[Former Member]

Hello,

The sequence that you have followes is correct. You revert back the 3rd ane 2nd option and perform the same after the recovery. Like

Change the ORACLE_SID to the new sid. Again

alter database backup controlfile to trace; and create the control file for source system.

Then you system will be same as before you start it in reset log.

then change the ORACLE_SID to source SID. Try to recover some redolog files and proceed with control file creation.

Then try giving alter database open resetlogs;

Thanks,

Nick S

[stefan_koehler]

Hello Aurelio,

> However, this is not whats happening

... then this is not an offline backup or you have done something wrong ....

> Will the sequence in 1 &2 matter?

No it doesn't matter. But how did you restore the control file?

Did you use the "backup controlfile" or did you recreate it with an "backup to trace file"?

If you recreate it with the generated SQL script (="backup to trace file") - then be sure that you have deleted the line with "RECOVER DATABASE".

Just use the "CREATE CONTROLFILE" content and execute the "ALTER DATABASE OPEN RESETLOGS" manually after successfully control file recreation.

Regards

Stefan

[Former Member]

Hi Stephan,

I checked the logs in my backup and its actually an offline backup. I backup using db13

I created my controlfile using the brcopy command. First I run brcopy to my source database.

Thanks

[peter_dzurov]

Stefan: I have experience, that even in case you are restoring offline backup, there was needed "recovery command". It was strange for me as well, but it worked that time.