oracle错误汇总2

转载

mb5fe55a455f6b0 2018-02-25 16:17:00

文章标签 ide 错误代码 sql oracle 日志文件 文章分类 Java 后端开发

http://blog.itpub.net/30430420/viewspace-1799925/

=============================

现象！！！！！！！！！！！！！！！！！

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 3373858816 bytes

Fixed Size 2180424 bytes

Variable Size 2415921848 bytes

Database Buffers 939524096 bytes

Redo Buffers 16232448 bytes

数据库装载完毕。

ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724],[43805], [43810], [], [], [], [], [], [], []

[1], [4724],[43805], [43810]

线程1，实例需要恢复日志序列号为4724的联机日志文件，需要恢复到编号为43810的日志块，而实际上只能恢复到第43805个日志块，所以库就启不来了

这可能是由于控制文件的缺失，或者在线日志文件在实例恢复时不完整

查看告警日志：

less alert_orcldata.log

Completed: ALTER DATABASE MOUNT

Sun Feb 25 14:49:04 2018

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

parallel recovery started with 3 processes

Started redo scan

Completed redo scan

read 312 KB redo, 55 data blocks need recovery

Errors in file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc (incident=465222):

ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []

Incident details in: f:\app\asus\diag\rdbms\orcldata\orcldata\incident\incdir_465222\orcldata_ora_2896_i465222.trc

Aborting crash recovery due to error 600

Errors in file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc:

ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []

Errors in file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc:

ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []

ORA-600 signalled during: ALTER DATABASE OPEN...

Trace dumping is performing id=[cdmp_20180225144908]

Sun Feb 25 14:50:04 2018

Sweep [inc][465222]: completed

Sweep [inc2][465222]: completed

查看相应的trace文件：

less orcldata_ora_2896.trc

Trace file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1 Service Pack 1

CPU : 4 - type 8664, 4 Physical Cores

Process Affinity : 0x0x0000000000000000

Memory (Avail/Total): Ph:1599M/8062M, Ph+PgF:6927M/16124M

Instance name: orcldata

Redo thread mounted by this instance: 1

Oracle process number: 20

Windows thread id: 2896, image: ORACLE.EXE (SHAD)

*** 2018-02-25 14:49:05.149

*** SESSION ID:(5.3) 2018-02-25 14:49:05.149

*** CLIENT ID:() 2018-02-25 14:49:05.149

*** SERVICE NAME:() 2018-02-25 14:49:05.149

*** MODULE NAME:(sqlplus.exe) 2018-02-25 14:49:05.149

*** ACTION NAME:() 2018-02-25 14:49:05.149

Successfully allocated 3 recovery slaves

Using 45 overflow buffers per recovery slave

Thread 1 checkpoint: logseq 4724, block 2, scn 144933326

cache-low rba: logseq 4724, block 43181

on-disk rba: logseq 4724, block 43810, scn 144958360

start recovery at logseq 4724, block 43181, scn 0

*** 2018-02-25 14:49:05.307

Started writing zeroblks thread 1 seq 4724 blocks 43805-43812

*** 2018-02-25 14:49:05.308

Completed writing zeroblks thread 1 seq 4724

*** 2018-02-25 14:49:05.492

==== Redo read statistics for thread 1 ====

Total physical reads (from disk and memory): 4096Kb

-- Redo read_disk statistics --

Read rate (ASYNC): 312Kb in 0.30s => 1.02 Mb/sec

Longest record: 6Kb, moves: 0/595 (0%)

Change moves: 1/73 (1%), moved: 0Mb

Longest LWN: 6Kb, moves: 0/238 (0%), moved: 0Mb

Last redo scn: 0x0000.08a3e393 (144958355)

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

----- Recovery Hash Table Statistics ---------

Hash table buckets = 32768

Longest hash chain = 1

Average hash chain = 55/55 = 1.0

Max compares per lookup = 1

Avg compares per lookup = 1514/1663 = 0.9

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

WARNING! Crash recovery of thread 1 seq 4724 is

ending at redo block 43805 but should not have ended before

redo block 43810

*** 2018-02-25 14:49:06.969

Incident 465222 created, dump file: f:\app\asus\diag\rdbms\orcldata\orcldata\incident\incdir_465222\orcldata_ora_2896_i465222.trc

ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []

应该是由于我强制关闭电脑，导致LGWR写联机日志文件时失败，下次重新启动数据库时，需要做实例级恢复，而又无法从联机日志文件里获取到这些redo信息，因为上次关闭时，写日志失败了。

===================================

http://blog.itpub.net/28883355/viewspace-1080879/

oradebug help

SETMYPID Debug current process

===================================

http://blog.csdn.net/haibusuanyun/article/details/36868269

方法1：这个方法不行

RECOVER DATABASE;

RECOVER DATABASE UNTIL CANCEL;

方法2：最终通过重建控制文件、再进行不完全恢复来OPEN数据库。（前提是客户只要求OPEN库，是客户的测试库，丢些数据没关系，如果是生产库不允许丢数据，此方法就不适用了）

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS'/home/oracle/a.txt';

Alter database recover database until cancel using backup controlfile;

Alter database open resetlogs;

===================================

下面这个方法有效

http://blog.itpub.net/30430420/viewspace-1799925/

Show parameter control_files;

oradebug setmypid;

Alter session set tracefile_identifier='controlfilerecreate';

Alter database backup controlfile to trace noresetlogs;

oradebug tracefile_name;

shutdown immediate;

startup nomount;

执行trace文件中的创建数据库脚本，orcldata_ora_2896_controlfilerecreate.trc

=====================================

最后验证一下相关表的数据

select uat.table_name from user_all_tables uat;

select object_name, created,last_ddl_time from user_objects;

SELECT

uat.table_name AS 表名,

(

SELECT

last_ddl_time

FROM

user_objects

WHERE

object_name = uat.table_name

) AS 最后修改日期

FROM

user_all_tables uat;

==========================================================

windows启动dbconsole

C:\Windows\System32\drivers\etc\hosts修改这个文件添加主机名的解析

否则只能本地访问EM

F:\app\ASUS\product\11.2.0\dbhome_1\BIN>emctl start dbconsole

OC4J Configuration issue. F:\app\ASUS\product\11.2.0\dbhome_1/oc4j/j2ee/OC4J_DBConsole_ASUS-PC_orcldata not found.

复制该目录下的“OC4J_DBConsole_localhost_orcl”文件夹放在同一目录下,且把名称改成“OC4J_DBConsole_ASUS-PC_orcldata”。

F:\app\ASUS\product\11.2.0\dbhome_1\BIN>emctl start dbconsole

EM Configuration issue. F:\app\ASUS\product\11.2.0\dbhome_1/ASUS-PC_orcldata not found.

复制该目录下的“localhost_orcl”文件夹放在同一目录下,且把名称改成“ASUS-PC_orcldata”。

==========================================

windows登录EM问题

1、问题描述：

打开OEM页面发现这个错误：界面如下

ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)

2问题原因

造成这个问题的主要原因是因为DBSNMP 、SYSMAN用户密码已经过期。

3解决办法

可以使用sys以管理员的身份登录数据库，然后执行select username,account_status from dba_users;语句查询用户状态，可以发现有如下两句：

DBSNMP EXPIRED

SYSMAN EXPIRED

把这俩用户、密码修改了就行。

本机使用sqlplus登陆

sqlplus / as sysdba

SQL> alter user sysman identified by sys123;

用户已更改。

SQL> alter user dbsnmp identified by sys123;

用户已更改。

重启em dbconsole 登陆

================================

三步走，重新创建EM

emctl stop dbconsole

emca -repos recreate

emca -config dbcontrol db

=================================

Wed Jul 31 18:13:51 2019

ORA-1652: unable to extend temp segment by 128 in tablespace KYC_TEMP

Wed Jul 31 18:14:04 2019

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large v

alue. Currently, ASH size is 100663296 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

select total_size,awr_flush_emergency_count from v$ash_info;

Wed Jul 31 18:14:29 2019

ORA-1652: unable to extend temp segment by 128 in tablespace KYC_TEMP

更换临时表空间，数据不影响

create temporary tablespace kyc_temp1;

alter user kyc_loa temporary tablespace kyc_temp1;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。