Printable View
I have a database that will not start. I can not perform an alter database open. I have a corrupted log file. The log file is the current logfile. I'm using 10gR1 on Windows 2000 server. The database is not in archive log mode. I also can not get to the managment screens on the machine. The service will not start. Is there any what to get the logfile removed while in the mount state?
Can you post the alert log messages and relevant error codes?
Is your redo log group multi plexed? ie multiple members per group?
If the database is not in archivelog mode, which logfile is corrupt?
Current Logfile is corrut. We have had a recent history of this machine stopping all processes at different points dount the runs. The alert logs never show a database problem (at this point in time we believe this to be a hardware powersuupply issue). The alter database open command responds that the current relod log group file is curputed at Number therad 1 then stops trying to open the database.
Alert log just before system chrash:
Sun Jul 02 23:57:09 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2032
Current log# 1 seq# 2032 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP1LOG.DBF
Sun Jul 02 23:58:16 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2033
Current log# 2 seq# 2033 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP2LOG.DBF
Sun Jul 02 23:59:22 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2034
Current log# 3 seq# 2034 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF
Mon Jul 03 00:02:12 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2035
Current log# 6 seq# 2035 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\G6LOG.DBF
Mon Jul 03 00:03:34 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2036
Current log# 5 seq# 2036 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\G5LOG.DBF
Mon Jul 03 00:04:46 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2037
Current log# 1 seq# 2037 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP1LOG.DBF
Mon Jul 03 00:05:46 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2038
Current log# 2 seq# 2038 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP2LOG.DBF
Mon Jul 03 00:06:54 2006
Private_strands 18 at log switch
Thread 1 advanced to log sequence 2039
Current log# 3 seq# 2039 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF
Alert log duing atempt to alter database open:
License high water mark = 1
Instance terminated by USER, pid = 1820
Wed Jul 05 15:52:35 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
KCCDEBUG_LEVEL = 0
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
Dynamic strands is set to TRUE
Running with 1 shared and 18 private strand(s). Zero-copy redo is FALSE
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.2.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 79691776
__large_pool_size = 4194304
__java_pool_size = 8388608
sga_target = 167772160
control_files = D:\ORACLE\PRODUCT\10.1.0\ORADATA\ROWELLS\CONTROLFILE\O1_MF_292QM28R_.CTL, D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ROWELLS\CONTROLFILE\O1_MF_292QM3J9_.CTL
db_block_size = 8192
__db_cache_size = 71303168
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_create_file_dest = D:\oracle\product\10.1.0\oradata
db_recovery_file_dest = D:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=ROWELLSXDB)
job_queue_processes = 10
background_dump_dest = D:\ORACLE\PRODUCT\10.1.0\ADMIN\ROWELLS\BDUMP
user_dump_dest = D:\ORACLE\PRODUCT\10.1.0\ADMIN\ROWELLS\UDUMP
core_dump_dest = D:\ORACLE\PRODUCT\10.1.0\ADMIN\ROWELLS\CDUMP
sort_area_size = 65536
db_name = ROWELLS
open_cursors = 300
pga_aggregate_target = 25165824
PMON started with pid=2, OS id=1916
MMAN started with pid=3, OS id=1708
DBW0 started with pid=4, OS id=1292
LGWR started with pid=5, OS id=1756
CKPT started with pid=6, OS id=1140
SMON started with pid=7, OS id=1524
RECO started with pid=8, OS id=1772
Wed Jul 05 15:52:37 2006
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
CJQ0 started with pid=9, OS id=2100
Wed Jul 05 15:52:37 2006
starting up 1 shared server(s) ...
Wed Jul 05 15:52:39 2006
ALTER DATABASE MOUNT
Wed Jul 05 15:52:39 2006
Controlfile identified with block size 16384
Wed Jul 05 15:52:47 2006
Setting recovery target incarnation to 2
Wed Jul 05 15:52:50 2006
Successful mount of redo thread 1, with mount id 1277176455
Wed Jul 05 15:52:50 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Wed Jul 05 15:53:05 2006
alter database open
Wed Jul 05 15:53:12 2006
Beginning crash recovery of 1 threads
Wed Jul 05 15:53:15 2006
Started first pass scan
Wed Jul 05 15:53:32 2006
Errors in file d:\oracle\product\10.1.0\admin\rowells\udump\rowells_ora_1996.trc:
ORA-00313: open failed for members of log group 3 of thread 1
Wed Jul 05 15:54:29 2006
Aborting crash recovery due to error 368
Wed Jul 05 15:54:29 2006
Errors in file d:\oracle\product\10.1.0\admin\rowells\udump\rowells_ora_1996.trc:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 25243 change 17595633 time 07/03/2006 00:09:39
ORA-00312: online log 3 thread 1: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF'
ORA-368 signalled during: alter database open...
Command Prompt Actions:
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\Documents and Settings\gpmazzone>set oracle_sid=rowells
C:\Documents and Settings\gpmazzone>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 5 15:51:03 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> alter database mount
2 /
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted
SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 95418880 bytes
Database Buffers 71303168 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database open
2 /
alter database open
*
ERROR at line 1:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 25243 change 17595633 time 07/03/2006
00:09:39
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF'
what the database shutdown cleanly? if so just open reset logs
Are the Logs multiplexed? is there more than 1 logfile per group or do you have just one logfile per group?
Since it was a crashed instance. It was not shut down cleanly....
And from the alert log messages, I'm guessing the logs were not multiplexed either.
The control files appear to be on the same devices as well.
This DB is now finished. There is no way to recover your corrupted log file. And since it is used to protect the integrity of the data file, they too are unrecoverable. Even after a resetlogs....
Reset logs would do the trick, allowing you to open. But the data is inconsistent/incomplete.
In the future:
Protect the instance... Create multiple redo log members per group on seperate disk devices (at least 3).
You don't need 7-8 groups... 3-4 should suffice.
Use at least 2 control files on seperate disk devices.
Hmm. First things first, when the database is down, backup all the files (datafiles, controlfiles, logfiles, init.ora, spfile, etc.).
Next, I'd try opening resetlogs. In theory, your datafiles should be in a consistent state, but you don't know what got screwed up when you came down hard.
As a last resort, I'd do an incomplete recovery until scn 17595633 or 17595632 and open resetlogs. Of course, you have a backup and are in archivelog mode, right?
Hmm, you want three copies of the log files and only two of the control files? You definitely want multiple copies, but with each copy comes performance implications.Quote:
Protect the instance... Create multiple redo log members per group on seperate disk devices (at least 3).
You don't need 7-8 groups... 3-4 should suffice.
Use at least 2 control files on seperate disk devices.
3-4 log groups may or may not be appropriate. This is a generalization (to which I don't agree because it doesn't fit every case) and needs to be understood before implementing.
Quote:
Originally Posted by marist89
you a funny guy, what is this archive log mode thing you speak of
Hi Friends,
Try to create new logfile groups...and drop the corrupted ones.
This is really the disadvantage of oracle against mssql server,
it is very strict when in comes to integrity.
Supposing I got redolog corruption todate (same scenario with
GARYM) but i want to get my data as of yesterday...since all of the
datafiles are still intact. And i know how to restart the process
or redo it manually all the transaction made during the day coz
supposing i know my application very well, or maybe its just a test
database.
Can I not forced it to startup? Disregarding all the transactions today. My point is since the datafiles are still intact why I am not
allowed to open it? and just put it tob waste :p
Thanks a lot
thats why you multiplex the redo logs isnt it
I agree. Another disadvantage is the blazing fast speed of Oracle, why can't we slow it down? :confused: :confused: :confused: :confused: :confused:Quote:
Originally Posted by yxez
He is not running in archive log mode. Hence my recomendation of 3-4... vs 8. Seriously doubt 8 is needed. 3 or 4 properly sized groups should be adequate for 80% of the systems.Quote:
Originally Posted by marist89
YES 2 control files are probably adequate, 3 would be better. But these should be on seperate mirrored raid devices. The reason is if any one is corrupted the instance will STOP. But too many, not only decreases performance, it also decreases availability since you would be adding them to seperated device, consequently reducing the overall MTBF.
A good strategy: alter database backup control file to trace;
Then if you do loose all of your control files. Its pretty easy to recreate!
Now, for the redo log members. The reason for at least 3, is because of exactly this scenario that this fellow encountered. Say there are 1 or 2 members.. Well if one is logically corrupted, then how can oracle know which one is correct? All it can do is say, there is an error. If there are 3 members. Then the majority would win. There should be no performance penalty for multiple members given that most modern O/S have Async I/O capabilities. (assuming the user properly lays out the disk correctly, isolating random i/o from sequential I/O).
YMMV
Quote:
Originally Posted by yxez
You could restore from a backup, prior to the point in time of interest. Then do a point in time recovery.
Oracle is very flexible, and allows you to shoot yourself in the foot if you so choose.
I agree, lets slow it down! Just put all datafiles and redo logs on one very slow disk device. Size redo logs to 1 meg, 2 groups, and enable archive logging. It should be very slow. Oh, and a tiny sga... And use a Microsoft OS. That way not only will it be slow, it will have a single point of failure.Quote:
Originally Posted by marist89
Mmm......Remembering the days on win NT/95 oracle 7.X......