Resetlog Failed

[Former Member]

Hi All,

Currently doing Database Refresh of a newly build Production System. Offline backup is restored in the Production System and control file is also successfully built.

While doing "alter database open resetlogs", encounterd the following error:

Mon Feb 15 10:27:08 2010

alter database open resetlogs

Mon Feb 15 10:27:08 2010

RESETLOGS after incomplete recovery UNTIL CHANGE 1649953600146

Resetting resetlogs activation ID 0 (0x0)

Mon Feb 15 10:27:47 2010

LGWR: Primary database is in CLUSTER CONSISTENT mode

Assigning activation ID 1424132593 (0x54e289f1)

Thread 1 opened at log sequence 1

Current log# 14 seq# 1 mem# 0: /oracle/P01/origlogB/log_g14m1.dbf

Current log# 14 seq# 1 mem# 1: /oracle/P01/mirrlogB/log_g14m2.dbf

Successful open of redo thread 1

Mon Feb 15 10:27:47 2010

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Mon Feb 15 10:27:47 2010

SMON: enabling cache recovery

Mon Feb 15 10:27:47 2010

Errors in file /oracle/P01/saptrace/usertrace/p01_ora_10764406.trc:

ORA-01534: rollback segment 'PRS_0' doesn't exist

Mon Feb 15 10:27:47 2010

Error 1534 happened during db open, shutting down database

USER: terminating instance due to error 1534

Instance terminated by USER, pid = 10764406

ORA-1092 signalled during: alter database open resetlogs...

Kindly suggest how to fix this error? Do I need to restore the database from scratch?

Looking forward for your reply

Regards,

Soumya

[markus_doehr2]

> Currently doing Database Refresh of a newly build Production System. Offline backup is restored in the Production System and control file is also successfully built.

> ORA-01534: rollback segment 'PRS_0' doesn't exist

Your rollback segment is not created; did you adapt the SQL file to recreate the control file?

Markus

[Former Member]

Hi Marcus,

In Source System's initSID.ora file I found that the parameter rollback_segments was not existing, so I have copied initSID.ora file from Source System to Target System. Changed all the Source SID to Target SID and re-created the control file freshly.

After that I tried opening the database in resetlog mode and got the below error:

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

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

Note: I have restored offline backup in the system. Then also it is asking for recovery.

Kindly suggest the possible fix.

Regards,

Soumya

[markus_doehr2]

> In Source System's initSID.ora file I found that the parameter rollback_segments was not existing, so I have copied initSID.ora file from Source System to Target System. Changed all the Source SID to Target SID and re-created the control file freshly.

Please note that starting with Oracle 10 the parameters are by default no more stored in an init<sid>.ora file but in a (binary) spfile. Did you create that init<SID>.ora just before you copied it?

> ERROR at line 1:

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

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

> Note: I have restored offline backup in the system. Then also it is asking for recovery.

This shouldn't happen on an offline backup.

Markus

[Former Member]

Hi Markus,

Database is Oracle 9i.

Find the below log:

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1649953600148 generated at 02/15/2010 10:27:47 needed for

thread 1

ORA-00289: suggestion : /oracle/P01/oraarch/P01arch1_1.arc

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

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

CANCEL

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

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

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

ORA-01112: media recovery not started

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Regards,

Soumya

[Former Member]

Hi,

Tried few options to fix the issue but nothing worked out. So scraped the database and did the restore freshly. Resetlogs has worked this time.

Thanks,

Soumya