Solved

ora-01190

Posted on 2003-11-03
13
9,093 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
  • 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
 
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 300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now