Solved

ora-01190

Posted on 2003-11-03
13
9,177 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
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: 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
 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Web Service from a stored procdure oracle 10 61
Queries 15 48
Deleting Rows from an Oracle Database - Performance 19 53
Oracle DB Slows After Datapump Until Next Reboot 27 93
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

828 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