OraFAQ Forum: Backup & Recovery

Home » RDBMS Server » Backup & Recovery » RESETLOGS (Linux, ORACLE 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

RESETLOGS [message #537133]

Tue, 27 December 2011 00:10

zutty
Messages: 45
Registered: December 2011

Member

Hi
When i try to startup database i got error like "ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
".
how to solve this ?
i want to open the database

Report message to a moderator

Re: RESETLOGS [message #537135 is a reply to message #537133]

Tue, 27 December 2011 00:13

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

It seems you have performed an incomplete recovery. Open it with RESETLOGS option

alter database open resetlogs;

Report message to a moderator

Re: RESETLOGS [message #537136 is a reply to message #537135]

Tue, 27 December 2011 00:15

zutty
Messages: 45
Registered: December 2011

Member

teh i got
alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dbs1AG1.dbf'

Report message to a moderator

Re: RESETLOGS [message #537137 is a reply to message #537136]

Tue, 27 December 2011 00:16

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Could you please let us know what have you done before? You need to recover the mentioned file using backups

Report message to a moderator

Re: RESETLOGS [message #537138 is a reply to message #537137]

Tue, 27 December 2011 00:20

zutty
Messages: 45
Registered: December 2011

Member

I tried to create a backup . using RMAN
back up database

Report message to a moderator

Re: RESETLOGS [message #537139 is a reply to message #537138]

Tue, 27 December 2011 00:21

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Please post all steps you've perform on the database before getting the following error. It's not possible to corrupt the database by backing it up using RMAN

Report message to a moderator

Re: RESETLOGS [message #537140 is a reply to message #537139]

Tue, 27 December 2011 00:26

zutty
Messages: 45
Registered: December 2011

Member

alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 637101 generated at 12/25/2011 16:46:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_29_770468386.dbf
ORA-00280: change 637101 for thread 1 is in sequence #29

Report message to a moderator

Re: RESETLOGS [message #537141 is a reply to message #537140]

Tue, 27 December 2011 00:27

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Please post all steps you've perform on the database before getting the following error

Report message to a moderator

Re: RESETLOGS [message #537142 is a reply to message #537141]

Tue, 27 December 2011 00:28

zutty
Messages: 45
Registered: December 2011

Member

rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 26 10:11:05 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target

connected to target database: AG1 (DBID=1110920802, not open)

RMAN> list backup
2> ;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 29.87M DISK 00:00:27 25-DEC-11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20111225T164224
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/03mv403h_1_1

List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 27 620340 25-DEC-11 636985 25-DEC-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 279.58M DISK 00:03:35 25-DEC-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20111225T164304
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/04mv404p_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 637020 25-DEC-11 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dbs1AG1.dbf
2 Full 637020 25-DEC-11 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dbx1AG1.dbf
3 Full 637020 25-DEC-11 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dbu1AG1.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 9.50K DISK 00:00:00 25-DEC-11
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20111225T164643
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/05mv40bk_1_1

List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 28 636985 25-DEC-11 637101 25-DEC-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 7.55M DISK 00:00:08 25-DEC-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20111225T164645
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-1110920802-20111225-00
SPFILE Included: Modification time: 22-DEC-11
SPFILE db_unique_name: AG1
Control File Included: Ckp SCN: 637110 Ckp time: 25-DEC-11

RMAN> list copy
2> ;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name AG1
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 27 A 25-DEC-11
Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_27_770468386.dbf

2 1 28 A 25-DEC-11
Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_28_770468386.dbf

RMAN> restore database validate
2> ;

Starting restore at 26-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/04mv404p_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/04mv404p_1_1 tag=TAG20111225T164304
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:02:51
Finished restore at 26-DEC-11

RMAN> exit;

Recovery Manager complete.

Report message to a moderator

Re: RESETLOGS [message #537143 is a reply to message #537142]

Tue, 27 December 2011 00:30

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Have you tried to recover the specified file using RMAN?

recover datafile 1;

It seems to be a SYSTEM file

Report message to a moderator

Re: RESETLOGS [message #537144 is a reply to message #537143]

Tue, 27 December 2011 00:34

zutty
Messages: 45
Registered: December 2011

Member

recover datafile 1;

Starting recover at 26-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/26/2011 12:00:00
RMAN-06067: RECOVER DATABASE required with a backup or created control file

Report message to a moderator

Re: RESETLOGS [message #537145 is a reply to message #537144]

Tue, 27 December 2011 00:35

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Control file seems to be old

Could you please post the output of the following sql

set linesize 150
select * from v$log;

Report message to a moderator

Re: RESETLOGS [message #537146 is a reply to message #537145]

Tue, 27 December 2011 00:39

zutty
Messages: 45
Registered: December 2011

Member

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
2 1 28 52428800 512 1 YES INACTIVE 636985 25-DEC-11 637101 25-DEC-11
1 1 29 52428800 512 1 NO CURRENT 637101 25-DEC-11 2.8147E+14

Report message to a moderator

Re: RESETLOGS [message #537148 is a reply to message #537146]

Tue, 27 December 2011 00:40

zutty
Messages: 45
Registered: December 2011

Member

i am new in oracle what is v$log?

Report message to a moderator

Re: RESETLOGS [message #537149 is a reply to message #537148]

Tue, 27 December 2011 00:42

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

So we got the first_Change# from v$Log.
Login to sqlplus and run the following command

SQL> recover database until cancel;

And when it will ask the archive redo log file with change number 637101 , provide the logfile name of the group 1
You can get the Log file name of the first group from the v$logfile view

Report message to a moderator

Re: RESETLOGS [message #537152 is a reply to message #537149]

Tue, 27 December 2011 00:49

zutty
Messages: 45
Registered: December 2011

Member

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Report message to a moderator

Re: RESETLOGS [message #537153 is a reply to message #537149]

Tue, 27 December 2011 00:50

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

run
recover database until cancel using backup controlfile;

Report message to a moderator

Re: RESETLOGS [message #537156 is a reply to message #537153]

Tue, 27 December 2011 00:59

zutty
Messages: 45
Registered: December 2011

Member

recover database until cancel using backup controlfile;
ORA-00279: change 637101 generated at 12/25/2011 16:46:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_29_770468386.dbf
ORA-00280: change 637101 for thread 1 is in sequence #29

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

ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_29_770468386.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-10879: error signaled in parallel recovery slave
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: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dbs1AG1.dbf'

Report message to a moderator

Re: RESETLOGS [message #537158 is a reply to message #537156]

Tue, 27 December 2011 01:02

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

I've told you to provide the redo log file name of the group 2
POst the following output:

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

Report message to a moderator

Re: RESETLOGS [message #537164 is a reply to message #537158]

Tue, 27 December 2011 01:21

zutty
Messages: 45
Registered: December 2011

Member

MEMBER
----------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/
dbs/log1AG1.dbf

Report message to a moderator

Re: RESETLOGS [message #537169 is a reply to message #537164]

Tue, 27 December 2011 01:31

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Ok, now run the following command and when it asks for the file, copy and paste the above file name

recover datbase until cancel using backup controlfile;

Report message to a moderator

Re: RESETLOGS [message #537175 is a reply to message #537169]

Tue, 27 December 2011 02:49

zutty
Messages: 45
Registered: December 2011

Member

Report message to a moderator

Re: RESETLOGS [message #537183 is a reply to message #537175]

Tue, 27 December 2011 03:25

zutty
Messages: 45
Registered: December 2011

Member

Tanx Kamran Agayev you are really great

Report message to a moderator

Previous Topic:	recovery window issue
Next Topic:	Oracle database backup using data pump

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 04 11:22:47 CDT 2021