Error during System refresh

[Former Member]

Hi Experts

We are facing the problem during the system refresh from PRD to QAS as we are unable to perfoam the recovery phase.Below are the error message which is displayed when recovery phase is start.

OS:- Win 2003

ECC-5.0 with oracle 9i

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1402567 generated at 05/18/2009 20:55:07 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\TAR\ORAARCH\TARARCHARC00001.001

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

So pls guide us towards the correction of said problem.

Thanks in advance.

[Former Member · Accepted solution]

Hi Parul,

I came across the same couple of times.

Actually after restoring the database(offline/online) we will preffer to recover database.

It will recovers and reset logs while using below command.

sqlplus>>>alter database open reset logs;

Now in this situation your database was altered and now asking for first redolog which will get generated after alter.

To avoid this i will recommend below 2 options:

1) If you restored offline database, just shoutdown it again and just create new controlfile and open.

2) If you restored online database, just take copy of controlfile form your PRO and change only SID as QAS. Do not create/change any line in that controlfile and copy the same to exact locations.

run the same, it will forcefully opens your database.(This is one time activity once activity get completed, database will works normally)

Regards

Nick Loy

[Former Member]

Hi Parul,

If you restore an offline backup, you don't need to recover anything.

Just create a new controlfile with execution of a script you can generate on the source system with

sqlplus /nolog

SQL> connect / as sysdba

SQL> alter database backup controlfile to trace;

Then you need to edit the trace file generated (archivelog/ noarchivelog option, replace SID, redolog size,etc.)

After you get message "Control file created.", execute

After that to Recover DB :-

SQL> startup mount

SQL> recover database using backup controlfile until time 'yy-mm-dd:11:00:00' (CHANGE DATE & TIME ACCORDINGLY, time should be after the CONTROL.SQL generated on DB server and Java Export on CI server)

ORA-00279:

ORA-00289:

ORA-00280:

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

Here Give AUTO

Verify recovery finished successfully

SQL> alter database open resetlogs;

Verify database opened successfully

Please check the oracle alert log for more info

SQL> alter system switch logfile; (execute about 10 times)

SQL> shutdown;

SQL> startup (to make sure database is recovered properly)

SQL> shutdown;

It should work if you restored a "good" offline backup.

Hope this helps

Thanks,

Sushil

[Former Member]

Thx a lot all of you .

As per your suggestions now we are able to open the database sucessfully ,so pls advice the steps which are need to perform after this as SAP still not getting open.

The doc which u have shared with us is on unix system that is why we don't have clear idea in which way we need to perfoam as we r performing the same on Windows (Ad ser2003).So pls guide us accordingly.

Thanks in advance

[Former Member]

Hi Parul,

gr8. If your probelm solved then assign point any of helpful answer solved anser.

And raise another question with log what is the error message during sapstart

SurendraJain

[Former Member]

did u check option AUTO ????????

go rman prompt

rman > recover database;

if any missing archivelog then copy manually from PROD to Quality and run again same command

Hope problem will be resolved

[Former Member]

just after ora-00280 row, is it not asking to specify log name ?

'ORA-00280: change 1402567 for thread 1 is in sequence #1'

what is getting displayed on the screen just afte the above statement ?

[Former Member]

Thanks Bhudev for your prompt reply

Nothing has to be come after this message and it returns to the SQL prompt.

Actually firstly we have restore the target system by the full offline backup of the source system and than genrate the control file by the copntrol file script of source system .

We don't have any clear idea about the redolog i.e how we need to apply for the recover the database.It would be great favour if u thow some light on this.

[Former Member]

Hello Parul

To recover the database from offline backup, you do not need the command which you were executing, that command is for recover from Online backup i.e. applying re-do logs after restore from online backup.

you were doing refresh of quality from the production database, so you would have to create control file script form prd and resore the offline backup of prd onto qas and then run that control file script into qas to generate the control files

On PRD: (what we followed apc and aqc are prd and qas here)

7. Server : <source server name>

User : ora<sid>

> sqlplus u201C/ as sysdbau201D

> alter database backup controlfile to trace

> exit

In /oracle/SID/saptrace/usertrace, the last trace (check the time) is the script for recreating the control file. You will need to edit it. Please rename the trace file to ld<TARGET_SID>.sql and send the file to the target system /oracle/SID

Edit the Script:

vi ld<SAPsid>.sql

Delete or comment out all lines at the beginning of the file before STARTUP NOMOUNT which is kept.

Change old controlfile creation statement at the beginnig of the controlfile:

Old line: create controlfile reuse database <SAPsid> NOresetlogs archivelog;

change to

New line: create controlfile set database <SAPsid> resetlogs archivelog;

replace all occurences of string <source_SAPsid> by <SAPsid> in each line where

<source_SAPsid> appears

:g / <source_SAPsid> / s / <source_SAPsid> / <SAPsid> /g

example:

:g/APC/s/APC/AQC/g

Delete the following lines a the end of the script:

;

RECOVER DATABASE;

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

8. Get from the Prod system (APC) the .aff and backDPR.log and copy them on AQC.

(/oracle/<SID>/sapbackup)

then restore the qas from prd using brrestore (brtools)

(since we used tsm backup, so following restore procedure)

DB Restore from offline backup from APC:

1. Stop SAP on AQC -> Login as aqcadm -> stopsap

2. Change the TSM node on AQC-> cd /usr/tivoli/tsm/client/api/bin64

ls -lrt

vi /usr/tivoli/tsm/client/api/bin64/dsm.sys

Change it to point to APC, comment the AQC Node.

and run brtools

QAS:

Post Restore Acitivity (Creation of control file)

1. Stop Database

Delete Old controlfile

rm /oracle/AQC/sapdata1/system_1/cntrl/*

rm /oracle/AQC/saparch/cntrl/*

rm /oracle/AQC/origlogA/cntrl/*

2. Re-create the controlfile and then after that start the database

User : ora<sid>

> cd

> sqlplus u201C/ as sysdbau201D

> @ld<target_sid>.sql

> alter database open resetlogs;

3. Change the sapr3 password

Server : <target server name>

User : ora<sid>

Temporarily, we have to change the sapr3 password to the default password. With

SAPBA, change this user password to pass.

4. Test the connection:

Server : <target server name>

User : <sid>adm

> R3trans u2013d

It should have a return code of 0000

5. Reasign tempfile

alter tablespace psaptemp add tempfile '/oracle/AQC/sapdata1/temp_1/temp.data1'

size 6020M reuse autoextend on next 20971520 maxsize 10000M;

Edited by: Bhudev on May 22, 2009 11:27 AM

Edited by: Bhudev on May 22, 2009 11:29 AM

[Former Member]

This message was moderated.

[Former Member]

This message was moderated.

[matt]

There's a limit of 2500 characters in one post. So split yours into two or more, and it should be readable.