Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ora-01190

Posted on 2003-11-03
13
Medium Priority
?
9,376 Views
Last Modified: 2009-07-29


While moving some files around, i must have overwritten som log files.   I can startup mount, but if i try opne i get error messages.  How can i open the database even with  with that 'before last reset logs' problem?
 SQL> startup
ORACLE instance started.

Total System Global Area  466618336 bytes
Fixed Size                   731104 bytes
Variable Size             352321536 bytes
Database Buffers          100663296 bytes
Redo Buffers               12902400 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u02/app/oracle/oradata/haypsdmd/system01.dbf'


SQL>
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9676245
The error message "ORA-01190: controlfile or data file 1 is from before the last RESETLOGS" means that controlfile backup is made before the last incomplete recovery.

to fix this, try

SQL> alter database open resetlogs;


then shutdown the database, do a complete database backup
0
 

Author Comment

by:xoxomos
ID: 9676292
Ok.  Thought I'd tried that!
0
 

Author Comment

by:xoxomos
ID: 9676295
Yep,  gives the error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u02/app/oracle/oradata/haypsdmd/system01.dbf'


SQL>
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 23

Expert Comment

by:seazodiac
ID: 9676304
In that case, you have to resort to the last backup.

Use RMAN to recover the database to the last full backup.

0
 

Author Comment

by:xoxomos
ID: 9676314
Oh Boy.  I don't have the last backup from rman.  All I have are the copies from a backup i made with system commands.
Any hope?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 1200 total points
ID: 9676316
specifically,

1. shutdown the database "shutdown immediate"

2. $RMAN catalog rman/rman@<Rman_SID> target sys/password@<target_sid>

3. RMAN>startup nomount

4. RMAN> restore database;

5. RMAN> recover database until cancel;


then open the database
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9676322
Yes, that will work too.

Put the copies from that backup to the exact location where database data files are.

and mount the database and open it.


you will need the control file, database data files from the backup.
0
 

Author Comment

by:xoxomos
ID: 9676411
Back to the original problem.  The files are from a backup I made from cp commands.  Then I started experimenting with rman backups.  Then I tried to wipe out all the rman recovered files and put the original files back.

This is what happens

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  466618336 bytes
Fixed Size                   731104 bytes
Variable Size             352321536 bytes
Database Buffers          100663296 bytes
Redo Buffers               12902400 bytes
Database mounted.
SQL> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>
0
 

Author Comment

by:xoxomos
ID: 9676459
Is it possible creating a controlfile from a trace file would get it to open?
0
 

Author Comment

by:xoxomos
ID: 9676583
I do have a rman backup that I made.  At that time there was no catalog I just typed in run {
....
...
backup include current controlfile
...

Is there any way to catalog the two files that produced and use it?  
I put out another question.
0
 
LVL 2

Expert Comment

by:Kong
ID: 9676585
Did you perform a resetlogs?

If so, you will need to use controlfiles & datafiles prior to the resetlogs and then issue recover until cancel.
0
 

Expert Comment

by:gguzman2
ID: 9860445
We have te error:

SVRMGR> startup
ORACLE instance started.
Total System Global Area                        768128064 bytes
Fixed Size                                          50240 bytes
Variable Size                                   154988544 bytes
Database Buffers                                612032512 bytes
Redo Buffers                                      1056768 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SVRMGR>

but, don't have the backup, what can i do??
0
 
LVL 2

Expert Comment

by:Kong
ID: 9862224
Try open with resetlogs...
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question