oracle 01589,ORACLE11的ORA-01589，跪求大神帮帮忙

Recover a ArcServe Backup of Oracle

此问题已回答。

nkammerer Newbie

nkammerer 2011-7-11 下午5:57

Hai, we use ArcServe for our Backups. Now i try to recover a Oracle Database which is backed up with ArcServe.

Unfortunately it doesn´t work.

Here what i tried:

i created a database. with same name, same configuration ...

then i shutdown the oracle tasks (windows system) and delete all files of this "temp"-database

then i restored the database out of our ArcServe backup.

finaly i tried to start the database.

i got the following messages:

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1298184 bytes

Variable Size             167772408 bytes

Database Buffers          440401920 bytes

Redo Buffers                2895872 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'E:\ORADATA\TEST\SYSTEM01.DBF'

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

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

SQL> recover database using backup controlfile;

ORA-00279: change 459494 generated at 07/04/2011 16:40:32 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00022_0755348320.001

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

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 459659 generated at 07/04/2011 16:42:25 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00023_0755348320.001

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

ORA-00278: log file 'F:\ORADATA\TEST\ARCH\ARC00022_0755348320.001' no longer

needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'E:\ORADATA\TEST\SYSTEM01.DBF'

The problem is, that there never was a sequence 23 of archive-logs.

somebody an idea how to recover the database?

or is it the wrong way for database recovery?

thanks a lot!

edit:

system: Windows Server 2003 R2

database: Oracle 10.2.0.4

nkammerer

正确答案 作者： nkammerer 在 2011-7-11 下午5:57

I tested some things and now i know what happend.Apparently i used "recover database" without "until cancel".

After that i were not able to open database without redolog.

Thank you all for your help!

查看上下文中的回答

191 查看

标签：

回复

平均用户评级: 无评分 (0 评级)

平均用户评级

无评分

(0 评级)

我的评级：

评分 差(1 / 5)

评分 中下(2 / 5)

评分 中等(3 / 5)

评分 中上(4 / 5)

评分 优(5 / 5)

1. Re: Recover a ArcServe Backup of Oracle

VenkatB Legend

VenkatB 2011-7-4 下午10:53 (回复 nkammerer)

Hi

Sequence 23 is already applied to the database.

Can you do exactly the following and show us the results? Instead of manual interaction, just set autorecovery on and do the recovery

Please make sure the database is MOUNTED.

SQL> set autorecovery ON

SQL> select * from v$log;

SQL> recover database using backup controlfile until cancel;

Regards

喜爱

显示 0 喜欢

(0) 回复

操作

2. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-4 下午11:46 (回复 VenkatB)

Hai VenkatB,

here´s the output:

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> set autorecovery on

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1          1         22   52428800          1 NO  CURRENT

451445 04-JUL-11

3          1         21   52428800          1 YES INACTIVE

431275 04-JUL-11

2          1         20   52428800          1 YES INACTIVE

427714 04-JUL-11

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 459659 generated at 07/04/2011 16:42:25 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00023_0755348320.001

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

ORA-00308: cannot open archived log

'F:\ORADATA\TEST\ARCH\ARC00023_0755348320.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

喜爱

显示 0 喜欢

(0) 回复

操作

3. Re: Recover a ArcServe Backup of Oracle

VenkatB Legend

VenkatB 2011-7-5 上午12:03 (回复 nkammerer)

Hi

Think the change is still in the redo log and not archived yet. And backup controlfile will not know about this. So please try this

SQL> select member from v$logfile where group#=1;

Note down the log file with full path (For example, C:\ORACLE\ORADATA\REDO01.LOG)

SQL> set autorecovery off

SQL> recover database using backup controlfile until cancel;

When it  says Specify log: {=suggested | filename | AUTO | CANCEL}, just do this

Specify the full path of the log file. Example C:\ORACLE\ORADATA\REDO01.LOG

This should fix the recovery. Once successfully applied, do CANCEL (cancel the recovery) and open resetlogs

Regards

喜爱

显示 0 喜欢

(0) 回复

操作

4. Re: Recover a ArcServe Backup of Oracle

user712697-Oracle Newbie

user712697-Oracle 2011-7-5 上午12:17 (回复 nkammerer)

Hi,

If there was never a seq 23 then the problem might be

this backup was taken after a shutdown abort.

You can also check if "archive log list" shows

current log sequence as 1.  If so, apply the online

log.  The minimum and maximum Seq needed should

be in this query "select min(fhrba_Seq), max(fhrba_Seq)  from X$KCVFH;"

Ray

喜爱

显示 0 喜欢

(0) 回复

操作

5. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-5 下午5:10 (回复 VenkatB)

Hai, which redo log should i use?

from the original database or from the temp database?

The redo log of the original database doesn´t exist.

喜爱

显示 0 喜欢

(0) 回复

操作

6. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-5 下午5:12 (回复 user712697-Oracle)

Hai, i used your query to check which seq i need.

here´s the output:

SQL> select min(fhrba_Seq), max(fhrba_Seq)  from X$KCVFH;

MIN(FHRBA_SEQ) MAX(FHRBA_SEQ)

--------------               --------------

23                          23

喜爱

显示 0 喜欢

(0) 回复

操作

7. Re: Recover a ArcServe Backup of Oracle

user11980574 Rookie

user11980574 2011-7-6 上午2:09 (回复 nkammerer)

You have to apply the current redo log as Venkatb said .

select a.member, b.status from v$logfile a, v$log b

where a.group# = b.group# and b.status='CURRENT'

-- this select will give you current redo to apply in recovery process

喜爱

显示 0 喜欢

(0) 回复

操作

8. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-6 下午6:58 (回复 user11980574)

Hai, i do what Venkatb said.

Here´s the output:

SQL> select member from v$logfile where group#=1;

MEMBER

--------------------------------------------------------------------------------

E:\ORADATA\TEST\REDO01.LOG

SQL> set autorecovery off

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 459659 generated at 07/04/2011 16:42:25 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00023_0755348320.001

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

Specify log: {=suggested | filename | AUTO | CANCEL}

E:\oradata\test\REDO01.LOG

ORA-00310: archived log contains sequence 22; sequence 23 required

ORA-00334: archived log: 'E:\ORADATA\TEST\REDO01.LOG'

Then i used the select from Oracle First .. SQL Server Later and this is the output:

SQL> select a.member, b.status from v$logfile a, v$log b

2  where a.group# = b.group# and b.status='CURRENT' ;

MEMBER

--------------------------------------------------------------------------------

STATUS

----------------

E:\ORADATA\TEST\REDO01.LOG

CURRENT

Somebody an idea, what i can do now?

喜爱

显示 0 喜欢

(0) 回复

操作

9. Re: Recover a ArcServe Backup of Oracle

user11980574 Rookie

user11980574 2011-7-6 下午7:35 (回复 nkammerer)

Hi ,

I would do 2 things .  ( yo have allready tried a lot

      ).

1 ) read this Note . I think it can help you .

How to recover and open the database if the archivelog required for recovery is either missing, lost or corrupted? [ID 465478.1]

2 )

It has been suggested in another post on this thread that a "shutdown abort" has ocurred . So maybe Arcserve was issuing a backup ( begin backup ..end backup for all tablespaces ) , and some of your datafiles remains in backup mode .

I have seen case , that db would not open if datafiles/tablespaces are in backup mode .

SQL> STARTUP MOUNT .

sql > SELECT * FROM V$BACKUP WHERE STATUS ='ACTIVE'   --- will give you datafiles in backup mode .

SQL>  ALTER DATAFILE 'C:/ORACLE/ ETC  ETC ' END BACKUP;  or even  ALTER DATABASE END BACKUP;

SQL > ALTER DATABASE OPEN:

Good Luck  nkammerer

喜爱

显示 0 喜欢

(0) 回复

操作

10. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-6 下午8:38 (回复 user11980574)

Hai,

1. i will read this note.

2. select * from v$backup where status='active' brings "no rows selected"

can this select work? i try to recover a database out of our arcserve backup into a new / emtpy database.

so i created a new database and replace all files with the files in the arcserve backup.

another question.

is this the right way for a recovery?

喜爱

显示 0 喜欢

(0) 回复

操作

11. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-6 下午11:30 (回复 nkammerer)

Now it works!

i tested to recover with all redo-logs.

here´s the output:

SQL> select a.member, b.status from v$logfile a, v$log b

2  where a.group# = b.group# and b.status='CURRENT' ;

MEMBER

--------------------------------------------------------------------------------

STATUS

----------------

E:\ORADATA\TEST\REDO01.LOG

CURRENT

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 459659 generated at 07/04/2011 16:42:25 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00023_0755348320.001

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

Specify log: {=suggested | filename | AUTO | CANCEL}

E:\oradata\test\REDO01.LOG

ORA-00310: archived log contains sequence 22; sequence 23 required

ORA-00334: archived log: 'E:\ORADATA\TEST\REDO01.LOG'

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 459659 generated at 07/04/2011 16:42:25 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00023_0755348320.001

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

Specify log: {=suggested | filename | AUTO | CANCEL}

E:\oradata\test\REDO02.LOG

ORA-00279: change 495621 generated at 07/05/2011 04:25:46 needed for thread 1

ORA-00289: suggestion : F:\ORADATA\TEST\ARCH\ARC00024_0755348320.001

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

ORA-00278: log file 'E:\oradata\test\REDO02.LOG' no longer needed for this

recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

E:\oradata\test\REDO03.LOG

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

somebody an idea, how this works?

i thought the current redolog have to be newer than the other...

喜爱

显示 0 喜欢

(0) 回复

操作

正确答案 12. Re: Recover a ArcServe Backup of Oracle

nkammerer Newbie

nkammerer 2011-7-11 下午5:57 (回复 nkammerer)

I tested some things and now i know what happend.Apparently i used "recover database" without "until cancel".

After that i were not able to open database without redolog.

Thank you all for your help!