Skip to content

Oracle 排障配置与调整 - 23. page

ORA-16014 ORA-00312 ORA-00312

在一次迁移数据库过程中，遭遇如此问题：

首先遭遇这个问题处理过程就是看告警，第二show db_recovery

查看归档路径和归档目录大小是否正常。

这里错误是由于归档目录空间不足

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 1 sequence# 47 not archived, no available destinations
ORA-00312: online log 1 thread 1:
‘/oradata/IMSBASE/onlinelog/o1_mf_1_6dnpv5f9_.log’
ORA-00312: online log 1 thread 1:
‘/oracle/flash_recovery_area/IMSBASE/onlinelog/o1_mf_1_6dnpv5rh_.log’

SQL> alter system set log_archive_start=false scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 3472883712 bytes
Fixed Size 2024792 bytes
Variable Size 738200232 bytes
Database Buffers 2717908992 bytes
Redo Buffers 14749696 bytes
Database mounted.
SQL> alter database flashback off
2 ;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

Database Oracle 排障配置与调整 | Guang Cai Li |

误删除唯一索引的补救办法

SQL> create table t1 (t_id number);

Table created.

SQL> select index_name from user_indexes where table_name=’T1′;

no rows selected

SQL> insert into t1 values(110);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(130);

1 row created.

SQL> insert into t1 values(140);

1 row created.

SQL> create index idx_t1_id on t1(id) online;
create index idx_t1_id on t1(id) online
*
ERROR at line 1:
ORA-00904: “ID”: invalid identifier

SQL> create index idx_t1_id on t1(t_id) online;

Index created.

SQL> select * from t1;

T_ID
———-
110
120
130
140

SQL> commit
2 ;

Commit complete.

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable validate;

Table altered.

SQL> insert into t1 values(110);
insert into t1 values(110)
*
ERROR at line 1:
ORA-00001: unique constraint (LUDA.PK_T1_ID) violated

SQL>
enable validate 是对当前存在的数据进行唯一性效验。如果当前表中存在重复数据，那么添加pk_t1_id的主键就会出错。

下面来测试
enable novalidate

SQL> drop table t1
2 ;

Table dropped.

SQL>
SQL> create table t1 (t_id number);

Table created.

SQL> insert into t1 values(110);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(130);

1 row created.

SQL> create index idx_t1_id on t1(t_id);

Index created.

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable validate;
alter table t1 add constraint pk_t1_id primary key(t_id) enable validate
*
ERROR at line 1:
ORA-02437: cannot validate (LUDA.PK_T1_ID) – primary key violated

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable novalidate;

Table altered.

SQL> insert into t1 values(120);
insert into t1 values(120)
*
ERROR at line 1:
ORA-00001: unique constraint (LUDA.PK_T1_ID) violated

SQL>

这里novalidate的作用就是不对旧的数据进行效验，只对新加进来的数据进行效验。

Database Oracle 排障配置与调整 Oracle恢复 | Guang Cai Li |

在安装完oracle后DBCA或者NETCA无法打开

确认这个错误我一共分三个步骤：

1：oracle正确安装了，但是建库时候dbca无法打开，提示没有这个命令。

那么就可以确认这个是环境变量的问题，

env | PATH

没有发现$ORACLE_HOME

cd $ORACLE_HOME | pwd

提示为： /home/oracle

我的oracle_home目录为：

/oracle/product/10.2.0/db_1

————————————————-

很显然问题出在这里，

vi .profile

修改

export PATH=$ORACLE_HOME/bin:.:$PATH:/usr/local/bin:/usr/ccs/bin

为

export PATH=$PATH:$ORACLE_HOME/bin:.:$PATH:/usr/local/bin:/usr/ccs/bin

经过发现这个错误一般在aix5.3下面产生。

Database Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li |

ksvcreate: Process(m000) creation failed

10. 7. 2009, 12.09
Alert.log:
==========
Fri Jul 10 09:24:19 2009
WARNING: inbound connection timed out (ORA-3136)
Fri Jul 10 09:30:11 2009
Process m000 died, see its trace file
Fri Jul 10 09:30:11 2009
ksvcreate: Process(m000) creation failed
Fri Jul 10 09:31:12 2009
Process m000 died, see its trace file

Trace file:
===========
*** SERVICE NAME:(SYS$BACKGROUND) 2009-07-09 20:35:37.278
*** SESSION ID:(3295.1) 2009-07-09 20:35:37.278
ktsmgtur(): TUR was not tuned for 1644 secs
ktsmg_advance_slot(): MMNL advances slot after 1786 seconds
*** 2009-07-10 09:30:11.004
Process m000 is dead (pid=32562, state=3):
Unable to schedule a MMON slave at: Auto Flush Main 1
Attempt to create slave process failed.
Can happen for several reasons:
– No process state objects
– Reached OS set limits
– A shutdown was going on
Check alert log for more details.

CHECKS:
=======
SQL> show parameter process

NAME TYPE VALUE
———————————— ———– ——————————
processes integer 3000

SQL> show parameter session

NAME TYPE VALUE
———————————— ———– ——————————
sessions integer 3305

SQL> select count(*) from v$session;

COUNT(*)
———-
2994

SELECT COUNT(*) FROM v$process;

COUNT(*)
———-
2990

Solution
========
– kill processes which generates the processes ( usually from http)
– restart database
– this error coresponds with ORA-3136

Grid and Cluster Oracle 排障配置与调整 | Guang Cai Li |

ORA 600 [4000] 一则

aix p570，oracle 925

首先检查v$datafile_header，发现checkpoint_change#都是一致的。
于是按着一般的当前在线日志文件损坏步骤处理：
增加下列参数至Oracle启动文件:
_allow_resetlogs_corruption=TRUE
_corrupted_rollback_segments=(list of all your rollback segments)
注释掉启动文件中的rollback_segments参数或undo_tablespaces参数
startup mount
recover database until cancel
alter database open resetlogs;
一般情况下，open resetlogs后最容易出现的600号错误为ora-600 [2662]和ora-600 [2256]。这两个错误也相对来说好处理一些，只需要采用10015事件adjust scn号即可。
但是这次我却是碰到了ora-600 [4000]号错误。
Errors in file /home/oracle/app/oracle/admin/test/udump/test_ora_2838638.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [46], [], [], [], [], [], []
Mon Feb 13 04:01:22 2011
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 2838638
metalink上对该错误的解释是：
DESCRIPTION:

This has the potential to be a very serious error.

It means that Oracle has tried to find an undo segment number in the
dictionary cache and failed.

ARGUMENTS:
Arg [a] Undo segment number

FUNCTIONALITY:
KERNEL TRANSACTION UNDO

IMPACT:
INSTANCE FAILURE – Instance will not restart
STATEMENT FAILURE
由于一开始_corrupted_rollback_segments里面只是列到_syssmu20$，于是将它列到_syssmu60$。重试后还是报这个错。
增加10513事件，禁止smon进程回滚，结果还是一样。
在600号的Trace文件中有：
ORA-00600: internal error code, arguments: [4000], [46], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
于是我怀疑会不会是undo$基表中没有46号回滚段的信息？
采用bbed检查undo$表格，发现里面是有这个回滚段的信息。
于是我想这个错误是出现在访问obj$基表上面，也就是说该表格的scn号与系统当前的scn号是不一致的。于是我想偿试修改该块的scn号。依然采用bbed，偿试修改该块的scn号。修改后，结果还是一样的。
于是我想应该是obj$基表上还有一个未提交的事务。于是继续查看trace文件，发现如下信息：
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x002e.025.00005b2c 0x00800f78.080c.01 –U- 1 fsc 0x0000.c5b527cf
data_block_dump,data header at 0x700000001f6e044
===============
tsiz: 0x1fb8
hsiz: 0xea
pbl: 0x700000001f6e044
bdba: 0x0040007a
76543210
flag=——–
很明显，是有一个未提交的事务，用bbed修改该事务的状态，将该事务改成提交状态。
首先找到itl信息:find /x 00005b2c，找到flag状态，现在其状态是20，也就是未提交，将之修改为80(提交状态)，并修改checkval。
之后去掉所有隐含参数，正常启动数据库，发现后台报出了ora-600[2662]错误。哈哈，事情至此就好办了，采用10015 adjust scn号，正常启动数据库：
Mon Feb 14 15:47:23 2011
Completed: ALTER DATABASE OPEN
Mon Feb 14 15:47:23 2011
Fatal internal error happened while SMON was doing active transaction recovery.
Mon Feb 14 15:47:23 2011
Errors in file oracle/admin/test/bdump/test_smon_2293872.trc:
ORA-00600: internal error code, arguments: [ktpridestroy2], []
SMON: terminating instance due to error 600
Instance terminated by SMON, pid = 2293872
从这块日志可以看出数据库正常启动后，马上因为smon回滚又导致了实例宕下来。
增加10513事件，启动数据库，一切正常。
想drop tablespce undotbs1，但是报出59号回滚段还有active事务无法删除。
于是增加_corrupted_rollback_segments参数，将数据库启来，新建一个回滚表空间，将原来的回滚表空间重建后，一切正常。

Database Oracle 排障配置与调整 Oracle恢复 | Guang Cai Li | |   ora 00600 [2662] [2256] [4000]