Skip to content

Oracle恢复 - 11. page

诊断事件:10513 禁止smon回滚

禁用smon进行tx recovery(所谓tx recovery就是open后数据文件包含提交和未提交数据,数据不一致),不会造成数据库不一致,虽然禁用了smon自动恢复,但是当查询的时候还是会进行回滚从undo中读取 回滚数据(
等同于用到哪个对象回滚哪个对象,这种方式会带来压力,且若undo损坏就十分麻烦了,那么这将是另一个恢复问题select segment_name,status,tablespace_name from dba_rollback_segs看那个段损坏
使用隐藏参数_offline_rollback_segments 标记,然后drop rollback segment ‘xxx’ ,此时才会造成真正的数据不一致)

实验证明:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  943718400 bytes
Fixed Size                  2025400 bytes
Variable Size             247466056 bytes
Database Buffers          692060160 bytes
Redo Buffers                2166784 bytes
Database mounted.
Database opened.
SQL> show parameter event

NAME                                 TYPE        VALUE
———————————— ———– ——————————
event                                string
SQL> conn luda/luda
Connected.
SQL> drop table t1;
create table t1 (id number,id2 number);

declare
begin
for i in 1..100000 loop
insert into t1 values(i,i+1);
end loop;
end;
/
Table dropped.

SQL>
Table created.

SQL> SQL>   2    3    4    5    6    7 

PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  943718400 bytes
Fixed Size                  2025400 bytes
Variable Size             251660360 bytes
Database Buffers          687865856 bytes
Redo Buffers                2166784 bytes
Database mounted.
SQL> alter system set events ‘10513 trace name context forever,level 2′;

System altered.

SQL> alter database open;

Database altered.

SQL> select usn,UNDOBLOCKSDONE,UNDOBLOCKSDONE from v$fast_start_transactions;

no rows selected

SQL> show parameter event

NAME                                 TYPE        VALUE
———————————— ———– ——————————
event                                string
SQL> alter session set events’10046 trace name context forever ,level 12’;

Session altered.

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from luda.t1;
SQL> alter system set events ‘10046 trace name context off’;

System altered.

在系统的10046的跟踪文件头部记录着:
“znjtepp_ora_26610.trc” 23617 lines, 2172756 characters
/oracle/admin/znjtepp/udump/znjtepp_ora_26610.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2/db_1
System name:    AIX
Node name:      aix53
Release:        3
Version:        5
Machine:        0009AFDA4C00
Instance name: znjtepp
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 26610, image: oracle@aix53 (TNS V1-V3)

*** 2011-05-25 11:46:13.172
*** SERVICE NAME:() 2011-05-25 11:46:13.163
*** SESSION ID:(159.3) 2011-05-25 11:46:13.163
Thread 1 checkpoint: logseq 39, block 8118, scn 3255055
  cache-low rba: logseq 39, block 8119
    on-disk rba: logseq 39, block 59764, scn 3255568
  start recovery at logseq 39, block 8119, scn 0
—– Redo read statistics for thread 1 —–
Read rate (ASYNC): 25823Kb in 0.28s => 90.06 Mb/sec
Total physical reads: 26624Kb
Longest record: 20Kb, moves: 0/101746 (0%)
Change moves: 0/2 (0%), moved: 0Mb
Longest LWN: 683Kb, moves: 25/94 (26%), moved: 16Mb
Last redo scn: 0x0000.0031ad12 (3255570)
———————————————-
—– Recovery Hash Table Statistics ———
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 1251/1246 = 1.0
Max compares per lookup = 2
Avg compares per lookup = 202468/203698 = 1.0
———————————————-
*** 2011-05-25 11:46:13.452
KCRA: start recovery claims for 1251 data blocks
*** 2011-05-25 11:46:13.830
KCRA: blocks processed = 1251/1251, claimed = 1251, eliminated = 0
*** 2011-05-25 11:46:13.831
Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0
—– Recovery Hash Table Statistics ———
Hash table buckets = 32768
Longest hash chain = 2
…..略

证明在扫描T1表时候还是会使用undo的段进行回滚。

关于10513的设置以及oracle的解释

查询smon进程号:
luda_dba:/home/oracle$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Wed May 25 10:28:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select pid,program from v$process where program like ‘%SMON%’;

       PID PROGRAM
———- ————————————————
         8 oracle@aix53 (SMON)

SQL> oradebug setorapid 8
Unix process pid: 22736, image: oracle@aix53 (SMON)
SQL> oradebug event 10513 trace name context forever,level 2
Statement processed.
SQL>

Oracle官方对于event 10513解释,该文件存放在
$ORACLE_HOJE/rdbms/mesg/oraus.msg文件中。

引用
10513, 00000, “turn off wrap source compression”
// *Cause:
// *Action: Set this event if you do not want source of wrapped PL/SQL
//          objects to be concatenated and stored multiple lines to a row.
// *Comment: This event should be set if you want the source of wrapped
//           PL/SQL objects to be stored one line per source row. The
//           new storage method is multiple lines per row, which
//           improves load and compile performance.

Linux:误删除/etc/inittab的解决办法

由于删除oracle cluster sofeware时候误删除了inittab文件

在系统重启时候报错,提示找不到启动模式,找不到inittab

———————————————————–

这时候可以进去rescue模式补救

我这里的经过是这样的:

插入安装光盘,修改cdrom为第一启动模式,

进入到安装界面,输入 linux recsue

在配置网卡那段可以跳过,直接进去到补救模式的系统

这时候挂在原有系统

chroot /mnt/sysimage

挂载完之后

启动网络服务和telnet服务

service network start

service xinetd start

然后 telnet进来

vi  /etc/inittab
把别的机子上的inittab复制过来,保存

重启,机子

Mysql 备份方式

MYSQLDUMP

在linux下的mysql在刚安装时候的默认用户都是root

导出的语句

mysql -uusername -ppassword -hlocalhost database_name > name.sql

导入的语句

mysql -uusername -ppassword -hlocalhost database_name < name.sql

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

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的作用就是不对旧的数据进行效验,只对新加进来的数据进行效验。

AIX夏令时导致应用时间对比异常终端处理

前几天一个客户反应系统时间比现实晚一个小时,导致无法刷卡。因为oracle的时间是通过获取系统当前时间

11月6号。

当时我查了下发现是AIX系统开启夏令时导致的

echo $TZ

beist-8TD

————-

在aix 5.3补丁打齐下可以使用命令

chtz beist-8

修改。

或者直接修改/etc/environment

TZ=Beist-8

或者smit 里修改。

—————————————–

在aix6.1系统中推荐使用后面的2种方法修改,修改后建议重启AIX