Emergency!!! Can't start Oracle .

[KY.Chuang]

Hi all,
Yestoday I imported data via application, Oracle is my databse.But Today Oracle process was terminated.
Today when I try to start oracle, It can mounted ,but it can't open(ORA-00470)... Below is the error message which I check my alert_.log of Oracle :
Oracle Version 8.0.5
OS HP unix 11.0
----------------------------------------
Sat Aug 23 18:48:52 2003
Errors in file /oracle/app/admin/baan/bdump/lgwr_2236.trc:
ORA-07445: exception encountered: core dump [11] [3221175688] [21472733] [0] [] []
Sat Aug 23 18:49:32 2003
PMON: terminating instance due to error 470
Instance terminated by PMON, pid = 2230
Sun Aug 24 10:15:57 2003
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 8.0.5.0.1.
System parameters with non-default values:
processes = 200
shared_pool_size = 500000000
control_files = /baan1/oracle/u01/oradata/baan/control01.ctl, /baan1/oracle/u02/oradata/baan/control02.ctl, /baan1/oracle/u03/oradata/baan/control03.ctl
db_block_buffers = 19200
db_block_size = 8192
log_buffer = 32768
log_checkpoint_interval = 10000
db_files = 240
db_file_multiblock_read_count= 32
dml_locks = 100
rollback_segments = r01, r02, r03, r04
sequence_cache_entries = 10
sequence_cache_hash_buckets= 10
global_names = TRUE
sort_area_size = 1048576
db_name = baan
open_cursors = 5000
ifile = /oracle/app/admin/baan/pfile/configbaan.ora
background_dump_dest = /oracle/app/admin/baan/bdump
user_dump_dest = /oracle/app/admin/baan/udump
max_dump_file_size = 10240
core_dump_dest = /oracle/app/admin/baan/cdump
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Sun Aug 24 10:15:58 2003
alter database mount
Sun Aug 24 10:16:03 2003
Successful mount of redo thread 1, with mount id 3782927202.
Sun Aug 24 10:16:03 2003
Database mounted in Exclusive Mode.
Completed: alter database mount
Sun Aug 24 10:16:03 2003
alter database open
Beginning crash recovery of 1 threads
Recovery of Online Redo Log: Thread 1 Group 2 Seq 72115 Reading mem 0
Mem# 0 errs 0: /baan1/oracle/u02/oradata/baan/redobaan02.log
Crash recovery completed successfully
Sun Aug 24 10:16:04 2003
Rolling back half complete log switch of thread 1
Sun Aug 24 10:16:04 2003
Errors in file /oracle/app/admin/baan/bdump/lgwr_3406.trc:
ORA-07445: exception encountered: core dump [11] [3221177624] [21873781] [0] [] []
Sun Aug 24 10:16:08 2003
ORA-470 signalled during: alter database open ...
Sun Aug 24 10:17:01 2003
PMON: terminating instance due to error 470
Instance terminated by PMON, pid = 3399
----------------------------------------------
It seen like Oracle's bug .
The problem is that if MAXLOGHISTORY is 65535 (default value of CREATE DATABASE), then when log sequence number reaches 65535, LGWR dies.

I try to copy another confile file 02( I have three confile files ) to instead confile file 01 and restart Oracle.
But It still have no useful(The Error message is still ORA-00470)

What can I do???

I have a cool backup, but it did four month ago.

Please give me a hint to slove this question.
Thanks.

Kychuang.

[Steven E. Protter]

So the backup from four months ago is cold and of use? Then restore it.

As you already note the Oracle Error Code is useless, most likely generated by doing import/export with the wrong versions of the import/export utilitiies.

The four month warning concerns me, which means you didn't back up this database right before trying the import.

In Chicago IT we have a saying, backup early, backup often(A twist on the the now untrue advice to Democratic machine voters). You always need a good checkpoint before doing anything to an Oracle database.

Check your process and data source and verify the version of the data source, that sounds like the cause.

SEP

[Hein van den Heuvel]

On a side note.... your REDO LOG settings appear very small causing overly frequent checkpoint and logsync delays.


Consider setting log_checkpoint_interval to 0 and just let the redo log size govern the need to logswitch / checkpoint. Make those logfiles large: 100MB? 1GB? more still?. Better stiller, read up on the new MTTR machanisme.

You *must* bump the log_buffer from 32K to 1MB or so. 32KB just will not do for a production DB of any usage.

hth,
Hein.

http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90190/ch195.htm

[Indira Aramandla]

Hi,

As you suspected the problem is with MAXHISTORY. MAXLOGHISTORY determines the maximum number of archived redo log files information that will be kept in the control file for automatic media recovery with Oracle OPS option and also Oracle determines space requirement in the control file for archived
redo log files based on the value of this parameter.

The archive log history can be queried on V$LOG_HISTORY view.

Here is the solution.

You need to recreate the controlfile with a smaller value for MAXLOGHISTORY. Right now it is 65535. Set it to ,say, 5000 in the control file creation script and recreate the control file.

Perform the following steps:

1. SVRMGR> connect internal

2. SVRMGR> shutdown immediate

3. SVRMGR> startup mount

4. SVRMGR> alter database backup controlfile to trace;

5. SVRMGR> show parameter user_

Go to the directory specified in USER_DUMP_DEST parameter for the last trace file based on time stamp.

Open the trace file in "vi" and delete all the rows before the line STARTUP NOMOUNT and save it as, say, CRCTL.SQL.

Also, change the value of MAXLOGHISTORY from 65535 to 5000 or even less.

SVRMGR> connect internal

SVRMGR> select name from v$controlfile;

Move the controlfiles to some other name using "mv" command.

SVRMGR> shutdown immediate

SVRMGR> @CRCTL.SQL

This will recreate the control file with new value for MAXLOGHISTORY and open up the database.

For example,

SVRMGR> select * from DBA_DATA_FILES;

If this command returns some rows then your database is up and running.


I hope this helps.

[Indira Aramandla]

Hi,

As you suspected, it is with MAXLOGHISTORY.

MAXLOGHISTORY determines the maximum number of archived redo log files information that will be kept in the control file for automatic media recovery with Oracle OPS option and also Oracle determines space requirement in the control file for archived
redo log files based on the value of this parameter.

The archive log history can be queried on V$LOG_HISTORY view.

Solution Description
You need to recreate the controlfile will a smaller value for MAXLOGHISTORY. Right now it is 65535. Set it to ,say, 5000 in the control file creation script and recreate the control file.

Perform the following steps:

SVRMGR> connect internal

SVRMGR> shutdown immediate

SVRMGR> startup mount

SVRMGR> alter database backup controlfile to trace;

SVRMGR> show parameter user_

Go to the directory specified in USER_DUMP_DEST parameter for the last trace file based on time stamp.

Open the trace file in "vi" and delete all the rows before the line STARTUP NOMOUNT and save it as, say, CRCTL.SQL.

Also, change the value of MAXLOGHISTORY from 65535 to 5000 or even less.

SVRMGR> connect internal

SVRMGR> select name from v$controlfile;

Move the controlfiles to some other name using "mv" command.

SVRMGR> shutdown immediate

SVRMGR> @CRCTL.SQL

This will recreate the control file with new value for MAXLOGHISTORY and open up the database.

For example,

SVRMGR> select * from DBA_DATA_FILES;

If this command returns some rows then your database is up and running.

[T G Manikandan]

MAXLOGHISTORY increases dynamically when the init.ora parameter CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0.

you can re-create the controlfile using the procedure above and make sure that you add
CONTROL_FILE_RECORD_KEEP_TIME =0 in the initSID.ora file.

Also check the document attached

[KY.Chuang]

Hi all,
Finally I got the solution in Metalink website
and through Oracle Support to slove.
It describe the solution as yours' replys.

I want to ask, do I need to add CONTROL_FILE_RECORD_KEEP_TIME =0 in my init.ora .

I have another machines which have the same situations But not happen yet.

How to prevent this kind of error occur and If I don't recreate control file as Website's step, This error will occur in my another machine future