ORA-00322,ORA-00312 排错

最新推荐文章于 2025-10-08 11:12:58 发布

原创最新推荐文章于 2025-10-08 11:12:58 发布 · 1.4w 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #thread #archive #oracle #transformation #statistics

ORACLE 专栏收录该内容

106 篇文章

订阅专栏

本文记录了一次Oracle 9i数据库启动失败的问题处理过程。主要错误为ORA-00322和ORA-00312，涉及在线重做日志文件问题。通过关闭数据库并清除指定的日志文件后，成功恢复了数据库的正常运行。

**财政局数据库问题
问题描述
接到**的电话，说他们的数据库起不来了，
从远程连到他的机器，上面报了这个错误

从远程登陆到数据库服务器，查看alert.log
里面有这一段错误信息
Fri Oct 24 12:34:20 2008
Restarting dead background process QMN0
QMN0 started with pid=9
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
All dispatchers and shared servers shutdown
Fri Oct 24 12:37:16 2008
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Fri Oct 24 12:37:16 2008
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Oct 24 12:41:07 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
processes                = 150
timed_statistics         = TRUE
shared_pool_size         = 83886080
large_pool_size          = 16777216
java_pool_size           = 83886080
control_files            = /oracle/oradata/yscz/control01.ctl, /oracle/oradata/yscz/control02.ctl, /oracle/oradata/yscz/control03.ctl
db_block_size            = 8192
db_cache_size            = 33554432
compatible               = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target   = 300
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
undo_retention           = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain                =
instance_name            = yscz
dispatchers              = (PROTOCOL=TCP) (SERVICE=ysczXDB)
job_queue_processes      = 10
hash_join_enabled        = TRUE
background_dump_dest     = /oracle/admin/yscz/bdump
user_dump_dest           = /oracle/admin/yscz/udump
core_dump_dest           = /oracle/admin/yscz/cdump
sort_area_size           = 524288
db_name                  = yscz
open_cursors             = 300
star_transformation_enabled= FALSE
query_rewrite_enabled    = FALSE
pga_aggregate_target     = 25165824
aq_tm_processes          = 1
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
CJQ0 started with pid=8
QMN0 started with pid=9
Fri Oct 24 12:41:09 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Oct 24 12:41:09 2008
ALTER DATABASE   MOUNT
Fri Oct 24 12:41:14 2008
Successful mount of redo thread 1, with mount id 677969253.
Fri Oct 24 12:41:14 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Fri Oct 24 12:41:14 2008
ALTER DATABASE OPEN
Fri Oct 24 12:41:14 2008
Errors in file /oracle/admin/yscz/bdump/yscz_lgwr_5281.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/oracle/oradata/yscz/redo02.log'
Fri Oct 24 12:41:14 2008
Errors in file /oracle/admin/yscz/bdump/yscz_lgwr_5281.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/oracle/oradata/yscz/redo02.log'
ORA-322 signalled during: ALTER DATABASE OPEN...
Fri Oct 24 12:42:09 2008
Restarting dead background process QMN0
QMN0 started with pid=9
Fri Oct 24 12:44:57 2008
Starting ORACLE instance (normal)
Fri Oct 24 12:45:04 2008
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
All dispatchers and shared servers shutdown
Fri Oct 24 12:45:07 2008
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Fri Oct 24 12:45:07 2008
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Oct 24 12:46:36 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
processes                = 150
timed_statistics         = TRUE
shared_pool_size         = 83886080
large_pool_size          = 16777216
java_pool_size           = 83886080
control_files            = /oracle/oradata/yscz/control01.ctl, /oracle/oradata/yscz/control02.ctl, /oracle/oradata/yscz/control03.ctl
db_block_size            = 8192
db_cache_size            = 33554432
compatible               = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target   = 300
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
undo_retention           = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain                =
instance_name            = yscz
dispatchers              = (PROTOCOL=TCP) (SERVICE=ysczXDB)
job_queue_processes      = 10
hash_join_enabled        = TRUE
background_dump_dest     = /oracle/admin/yscz/bdump
user_dump_dest           = /oracle/admin/yscz/udump
core_dump_dest           = /oracle/admin/yscz/cdump
sort_area_size           = 524288
db_name                  = yscz
open_cursors             = 300
star_transformation_enabled= FALSE
query_rewrite_enabled    = FALSE
pga_aggregate_target     = 25165824
aq_tm_processes          = 1
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
CJQ0 started with pid=8
QMN0 started with pid=9
Fri Oct 24 12:46:38 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Oct 24 12:46:39 2008
ALTER DATABASE   MOUNT
Fri Oct 24 12:46:43 2008
Successful mount of redo thread 1, with mount id 677927087.
Fri Oct 24 12:46:43 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Fri Oct 24 12:46:43 2008
ALTER DATABASE OPEN
Fri Oct 24 12:46:43 2008
Errors in file /oracle/admin/yscz/bdump/yscz_lgwr_5323.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/oracle/oradata/yscz/redo02.log'
Fri Oct 24 12:46:43 2008
Errors in file /oracle/admin/yscz/bdump/yscz_lgwr_5323.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/oracle/oradata/yscz/redo02.log'
ORA-322 signalled during: ALTER DATABASE OPEN...
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
All dispatchers and shared servers shutdown
Fri Oct 24 12:46:54 2008
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Fri Oct 24 12:46:54 2008
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Oct 24 13:43:17 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled

从上面的代码中，发现红字部分正式出错根源，错误号码是ORA-00322和ORA-00312；

从网上查看相关的文档，里面说了一个解决办法：

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

今天有台Oracle9i无法启动，出现如下错误：
ora-00322:log 1of thread 1 is not current copy
ora-00312:online log 1thread 1:'opt/oracle/oradata/oradb/REDO02.LOG'

解决办法如下：
1. shutdown immediate;
2. startup mount;
3. alter database clear logfile 'opt/oracle/oradata/oradb/REDO02.LOG';
4. alter database open;
5. select status from v$instance;
看到Oracle可以正常启动了；

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

登上去，做了类似的操做后，问题解决了。

metalink的解释

Problem Description:
====================

You have a media failure, which causes the database to crash.

Problem Explanation:
====================

At startup, the database will mount, but gives the following errors at open:

ORA-00312: "online log %s thread %s: '%s'"
      Cause: This message reports the filename for details of another message.
     Action: Other messages will accompany this message. See the
             associated messages for the appropriate action to take.

ORA-00322: "log %s of thread %s is not current copy"
      Cause: Check of log file header at database open found that an online log
             appears to be an incorrectly restored backup.
     Action: Restore correct file or reset logs.

Search Words:
=============

Hardware, recover

Solution Description:
=====================

First check that ORACLE_HOME and ORACLE_SID are set correctly, and if not, do
so and re-start. Else, continue as follows.

Recover the loss of an inactive, online redo log group.

ALTER DATABASE CLEAR LOGFILE 'filename';

If there is more than one LOGFILE in the redo log group then you must
specify all the log files.

For example, the following fails:

SVRMGR> alter database clear logfile '/vobs/oracle/dbs/log3.log';
alter database clear logfile '/vobs/oracle/dbs/log3.log'
*
ORA-1514: error in log specification: no such log
ORA-1517: log member: '/vobs/oracle/dbs/log3.log

But, specifying all the log files in the group works:
SVRMGR> ALTER DATABASE CLEAR LOGFILE ('/vobs/oracle/dbs/t4.log','/vobs/oracle/;
Statement processed.

For more information Ref: Bug 763186

Solution Explanation:
=====================

The database, at open, does a check of log file headers and
found an online log that appears to be an incorrected restored file.
In this case it was not incorrect, but inactive. By issuing
the clear logfile, you can avoid going through an uneccessary
open resetlogs when applicable.