Home » RDBMS Server » Backup & Recovery » RESETLOGS (Linux, ORACLE 11G)
RESETLOGS [message #537133] Tue, 27 December 2011 00:10 Go to next message
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
Re: RESETLOGS [message #537135 is a reply to message #537133] Tue, 27 December 2011 00:13 Go to previous messageGo to next message
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;
Re: RESETLOGS [message #537136 is a reply to message #537135] Tue, 27 December 2011 00:15 Go to previous messageGo to next message
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'
Re: RESETLOGS [message #537137 is a reply to message #537136] Tue, 27 December 2011 00:16 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537138 is a reply to message #537137] Tue, 27 December 2011 00:20 Go to previous messageGo to next message
zutty
Messages: 45
Registered: December 2011
Member
I tried to create a backup . using RMAN
back up database
Re: RESETLOGS [message #537139 is a reply to message #537138] Tue, 27 December 2011 00:21 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537140 is a reply to message #537139] Tue, 27 December 2011 00:26 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537141 is a reply to message #537140] Tue, 27 December 2011 00:27 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537142 is a reply to message #537141] Tue, 27 December 2011 00:28 Go to previous messageGo to next message
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.
Re: RESETLOGS [message #537143 is a reply to message #537142] Tue, 27 December 2011 00:30 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537144 is a reply to message #537143] Tue, 27 December 2011 00:34 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537145 is a reply to message #537144] Tue, 27 December 2011 00:35 Go to previous messageGo to next message
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;
Re: RESETLOGS [message #537146 is a reply to message #537145] Tue, 27 December 2011 00:39 Go to previous messageGo to next message
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

Re: RESETLOGS [message #537148 is a reply to message #537146] Tue, 27 December 2011 00:40 Go to previous messageGo to next message
zutty
Messages: 45
Registered: December 2011
Member
i am new in oracle what is v$log?
Re: RESETLOGS [message #537149 is a reply to message #537148] Tue, 27 December 2011 00:42 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537152 is a reply to message #537149] Tue, 27 December 2011 00:49 Go to previous messageGo to next message
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
Re: RESETLOGS [message #537153 is a reply to message #537149] Tue, 27 December 2011 00:50 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

run
recover database until cancel using backup controlfile;
Re: RESETLOGS [message #537156 is a reply to message #537153] Tue, 27 December 2011 00:59 Go to previous messageGo to next message
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'
Re: RESETLOGS [message #537158 is a reply to message #537156] Tue, 27 December 2011 01:02 Go to previous messageGo to next message
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;
Re: RESETLOGS [message #537164 is a reply to message #537158] Tue, 27 December 2011 01:21 Go to previous messageGo to next message
zutty
Messages: 45
Registered: December 2011
Member
MEMBER
----------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/
dbs/log1AG1.dbf
Re: RESETLOGS [message #537169 is a reply to message #537164] Tue, 27 December 2011 01:31 Go to previous messageGo to next message
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;
Re: RESETLOGS [message #537175 is a reply to message #537169] Tue, 27 December 2011 02:49 Go to previous messageGo to next message
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}
log1AG1.dbf
Log applied.
Media recovery complete.
Re: RESETLOGS [message #537183 is a reply to message #537175] Tue, 27 December 2011 03:25 Go to previous message
zutty
Messages: 45
Registered: December 2011
Member
Tanx Kamran Agayev you are really great
Previous Topic: recovery window issue
Next Topic: Oracle database backup using data pump
Goto Forum:
  


Current Time: Fri Oct 19 19:29:14 CDT 2018