Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9427
  • Last Modified:

ora-01190



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
xoxomos
Asked:
xoxomos
  • 6
  • 4
  • 2
  • +1
1 Solution
 
seazodiacCommented:
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
 
xoxomosAuthor Commented:
Ok.  Thought I'd tried that!
0
 
xoxomosAuthor Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
seazodiacCommented:
In that case, you have to resort to the last backup.

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

0
 
xoxomosAuthor Commented:
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
 
seazodiacCommented:
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
 
seazodiacCommented:
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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
Is it possible creating a controlfile from a trace file would get it to open?
0
 
xoxomosAuthor Commented:
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
 
KongCommented:
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
 
gguzman2Commented:
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
 
KongCommented:
Try open with resetlogs...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now