Sunday, March 23, 2008

Recovery Error: ORA-01194 and ORA-01110

SQL> startup;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2011832 bytes
Variable Size             297798984 bytes
Database Buffers          767557632 bytes
Redo Buffers                6373376 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Explanation:

Specify RESETLOGS if you want Oracle to ignore the contents of the files listed in the LOGFILE clause.

The log files do not have to exist but each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter.

Oracle will assign all online redo log file groups to thread 1 and will enable this thread for public use by any instance.

You must then open the database using ALTER DATABASE RESETLOGS.

NORESETLOGS will use all files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups.
Oracle will reassign the redo log file groups to reenabled threads as previously assigned.

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oracle_home/data/orcl/system01.dbf'

The meaning of the error message is : your system
tablespace datafile is not up to date and requires recovery.

 

Metalink Doc ID: Note:417710.1

               Doc ID: Note:434013.1

In this state no logging takes place:

 

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oracle_home/data/orcl/redo_mirror
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0

SQL> shutdown immediate;

SQL> startup mount;

*************************************************************

-- RECOVER : with AUTO mentioned ---

*************************************************************

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1508019 generated at 03/20/2008 20:00:36 needed for thread 1
ORA-00289: suggestion : /oracle/oracle_home/data/orcl/1_35_648659006.dbf
ORA-00280: change 1508019 for thread 1 is in sequence #35

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/oracle/oracle_home/data/orcl/1_35_648659006.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
'/oracle/oracle_home/data/orcl/1_35_648659006.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

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: '/oracle/oracle_home/data/orcl/system01.dbf

 

********************************************************************************************

- RECOVER : with CANCEL mentioned ---

**************************************************************************************************

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1508019 generated at 03/20/2008 20:00:36 needed for thread 1
ORA-00289: suggestion : /oracle/oracle_home/data/orcl/1_35_648659006.dbf
ORA-00280: change 1508019 for thread 1 is in sequence #35

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
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: '/oracle/oracle_home/data/orcl/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oracle_home/data/orcl/system01.dbf'

------------------------------------------------------------------------------------------------------------

Solution 1.

Selecting from v$log ,say, you see that current log (#1) had 254 sequence number. You can rename the log file #1 (to met archived logs format) with sequence number 254 and put it into archive logs directory. Then issue a "recover database using backup controlfile until cancel" from sqlplus as sysdba, and recovery has been completed with no errors...
I hope it can help other users with the same problem.

Working to provide more solutions ;-)

No comments:

Post a Comment