22 June 2007

Solution of "ORA-00314: log of thread , expected sequence# doesn't match "

ast week i struggled a problem that was caused by ORA 00314. We have an old database :) which was not opened for 3 or more months. When i try to open the database i get an error:
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1302944 bytes
Variable Size 106700384 bytes
Database Buffers 205520896 bytes
Redo Buffers 1048576 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# 50 doesn't match 377
ORA-00312: online log 1 thread 1: '/data07/oradata/testdb/redo01.log'


First i create a backup of control file:

SQL> alter database backup controlfile to trace;
Database altered.

Then i check udump directory. There are some trace files To find out which one contains control file, i simply make a text search :
testdb oracle@aurora:/data06/app/oracle/admin/testdb/udump> grep 'CREATE CONTROLFILE REUSE DATABASE' *.*
testdb_ora_181267.trc:CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS NOARCHIVELOG
testdb_ora_181267.trc:CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS NOARCHIVELOG

I execute the commands which is included with CREATE CONTROLFILE REUSE DATABASE
SQL> startup nomount
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1302944 bytes
Variable Size 106700384 bytes
Database Buffers 205520896 bytes
Redo Buffers 1048576 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 4
3 MAXLOGMEMBERS 4
4 MAXDATAFILES 1000
5 MAXINSTANCES 1
6 MAXLOGHISTORY 4544
7 LOGFILE
8 GROUP 1 '/data07/oradata/testdb/redo01.log' SIZE 50M,
9 GROUP 2 '/data07/oradata/testdb/redo02.log' SIZE 50M,
10 GROUP 3 '/data07/oradata/testdb/redo03.log' SIZE 50M
11 DATAFILE
12 '/data07/oradata/testdb/system01.dbf',
13 '/data07/oradata/testdb/undo01.dbf',
14 '/data07/oradata/testdb/sysaux01.dbf',
15 '/data07/oradata/testdb/users01.dbf',
16 '/data07/oradata/testdb/data01.dbf',
17 '/data07/oradata/testdb/work01.dbf',
18 '/data07/oradata/testdb/ascs01.dbf',
19 '/data07/oradata/testdb/uccs_data01.dbf',
20 '/data07/oradata/testdb/uccs_index01.dbf'
21 CHARACTER SET WE8ISO8859P9
22 ;

Control file created.
Then recover db.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE

No comments: