月归档：一月 2012

RBA和实例恢复关系

发表于 2012 年 1 月 12 日 由 惜分飞

1.关于heartbeat和checkpoint
在这次的删除过程中我没有执行commit，而是直接abort数据库。整个删除过程执行了近6分钟，控制文件的心跳每三秒进行一次，心跳是把low cache rba记录到了控制文件中，而没有真正的把全部的脏数据写入到磁盘( 只有发生了checkpoint时候，才会把相关的脏数据写入到磁盘，而这里的控制文件的heartbeat和checkpoint是两回事，checkpoint一般是在切换日志，数据文件正常离线，执行begin backup命令时发生，昨晚晚上后面的一个困惑就是上面的英文描述，让我把这两者搞混淆了)

2.三种rba解释
low rba ：在buffer cache中的数据块第一次数据改变所对应的RAB。
high rba ：在buffer cache中的数据块最近一次数据改变时所对应的RAB。
on-disk rba：是 lgwr 写日志文件的最末位置的地址。

3.实例恢复过程解释
实例恢复的时候，是从控制文件heartbeat记录的low rba开始读redo log数据(会多读取一点，因为heartbeat是每三秒执行一次，假设在2.9秒的时候，数据库异常down了，控制文件中记录的还是2.9秒前的low rba，这个时候，从该点开始读取redo),恢复到on-disk rba，而不是high rba(high rba一般情况下会大于on-disk rba,但是因为high rba比on-disk rba多的部分记录在redo log buffer中，在实例恢复的时候，因为其未被记录到redo log file中，所以不能被恢复，其实也没有必要恢复，因为该数据肯定是没有commit或者rollback)

具体内容和实验请见:RBA和实例恢复关系

发表在 Oracle备份恢复 | 评论关闭

ORA-600 kcratr_nab_less_than_odr故障解决

发表于 2012 年 1 月 11 日 由 惜分飞

朋友的数据库服务器出现ORA-00600[kcratr_nab_less_than_odr]，不能open数据库
1.open数据库报ORA-00600[kcratr_nab_less_than_odr]

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],
[43531], [43569], [], [], [], [], [], [], []

2.查看alert日志

Wed Jan 11 13:56:16 2012
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 read 54591 KB redo, 0 data blocks need recovery
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc  (incident=818557):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
Incident details in: d:\dbdms\diag\rdbms\dbdms\dbdms\incident\incdir_818557\dbdms_ora_3936_i818557.trc
Aborting crash recovery due to error 600
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Trace dumping is performing id=[cdmp_20120110214555]

3.查看trace文件

Trace file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.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                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:2250M/4060M, Ph+PgF:5868M/8119M 
Instance name: dbdms
Redo thread mounted by this instance: 1
Oracle process number: 17
Windows thread id: 3108, image: ORACLE.EXE (SHAD)
…………………………
WARNING! Crash recovery of thread 1 seq 99189 is
ending at redo block 43531 but should not have ended before
redo block 43569
Incident 826550 created, dump file: d:\dbdms\diag\rdbms\dbdms\dbdms\incident\incdir_826550\dbdms_ora_3108_i826550.trc
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []

ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []

通过alert和trace中的内容可以知道，数据库需要恢复到rba到43569，但是因为某种原因实例恢复的时候，只能利用1 thread 99189 seq#，恢复rba到43531。从而导致数据库无法正常open

This Problem is caused by Storage Problem of the Database Files. 
The Subsystem (eg. SAN) crashed while the Database was open. 
The Database then crashed since the Database Files were not accessible anymore. 
This caused a lost Write into the Online RedoLogs and so Instance Recovery is not possible and raising the ORA-600.

4.解决方法

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

--尝试直接recover database
SQL> RECOVER DATABASE ;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
--提示不用恢复

--再打开数据库，还是kcratr_nab_less_than_odr错误警告
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],
[43531], [43569], [], [], [], [], [], [], []

--尝试不完全恢复
SQL> RECOVER DATABASE UNTIL CANCEL;
ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: 'D:\DBDMS\DATA\SYSTEM01.DBF'

--重建控制文件
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:/1.TXT';

数据库已更改。

SQL> SHUTDOWN IMMEDIATE;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP NOMOUNT;
ORACLE 例程已经启动。

Total System Global Area  417546240 bytes
Fixed Size                  2176328 bytes
Variable Size             268438200 bytes
Database Buffers          138412032 bytes
Redo Buffers                8519680 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DBDMS" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 18688
  7  LOGFILE
  8    GROUP 1 'D:\DBDMS\LOG\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\DBDMS\LOG\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\DBDMS\LOG\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\DBDMS\DATA\SYSTEM01.DBF',
 13    'D:\DBDMS\DATA\SYSAUX01.DBF',
 14    'D:\DBDMS\DATA\RBSG01.DBF',
 15    'D:\DBDMS\DATA\DATA01.DBF',
 16    'D:\DBDMS\DATA\INDX01.DBF',
 17    'D:\DBDMS\DATA\DATA02.DBF',
 18    'D:\DBDMS\DATA\DATA03.DBF',
 19    'D:\DBDMS\DATA\DATA04.DBF',
 20    'D:\DBDMS\DATA\INDX02.DBF',
 21    'D:\DBDMS\DATA\SYSTEM02.DBF'
 22  CHARACTER SET ZHS16GBK
 23  ;

控制文件已创建。

--继续尝试恢复
SQL> RECOVER DATABASE ;
完成介质恢复。
SQL> ALTER DATABASE OPEN;

数据库已更改。
--open成功

在这次恢复中，主要就是重建控制文件，然后直接恢复成功，如果redo有损坏，那么可能需要使用不完全恢复，然后使用resetlogs打开数据库

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 , , | 7 条评论

CAST本质探讨

发表于 2012 年 1 月 11 日 由 惜分飞

1.试验前提

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
   2 SUPPLEMENTAL_LOG_DATA_MIN from v$database;

FOR SUP SUP SUPPLEME
--- --- --- --------
NO  NO  NO  NO

SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/archivelog/chf
Oldest online log sequence     1879
Next log sequence to archive   1881
Current log sequence           1881

SQL> select count(*) from t_xifenfei_move;

  COUNT(*)
----------
   7432085

SQL> select bytes/1024/1024 from user_segments where segment_name='T_XIFENFEI_MOVE';

BYTES/1024/1024
---------------
            832

从上面信息可以看到数据库处于归档模式，强制日志和辅助日志为开启，试验测试表t_xifenfei_move有7432085条记录，占用硬盘空间832M

2.常规CAST

SQL>  alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> SET TIMING ON
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            100028
undo change vector size                                               16172

Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_CAST tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
 
Table created.

Elapsed: 00:01:58.10
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873150548
undo change vector size                                              131384

Elapsed: 00:00:00.01

SQL> select 873150548-100028 "redo size" from dual;

 redo size
----------
 873050520

SQL> select 131384-16172 "undo size" from dual;

 undo size
----------
    115212

通过这个可以得出结论，产生redo为873050520，undo为115212

3.普通INSERT

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136

Elapsed: 00:00:00.18
SQL> CREATE TABLE t_xifenfei_move_INSERT 
  2  AS
  3  SELECT * FROM T_XIFENFEI_MOVE WHERE 1=0;

Table created.

Elapsed: 00:00:00.32
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22712
undo change vector size                                                6512

Elapsed: 00:00:00.02
SQL> INSERT INTO t_xifenfei_move_INSERT
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:01:59.47
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862261580
undo change vector size                                            27980508

--redo 
SQL> select 22712-732 "create redo size" from dual;

create redo size
----------------
           21980

SQL> select 862261580-22712 "insert redo size" from dual;

insert redo size
----------------
       862238868

--undo
SQL> select 6512-136 "create undo size" from dual;

create undo size
----------------
            6376

SQL> select 27980508-6512 "insert undo size" from dual;

insert undo size
----------------
        27973996

通过这个可以得出CREATE TABLE 过程中产生redo:21980,undo:6376,而INSERT 过程中产生的redo:862238868,undo:27973996,整个过程总的产生redo:862260848(862238868+21980),undo:27980372(27973996+6376)

4.INSERT+APPEND

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:25.19
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136

Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_INSERT_A tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.18
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             21892
undo change vector size                                                6308

Elapsed: 00:00:00.00
SQL> INSERT /*+ append */INTO t_xifenfei_move_INSERT_A
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:02:26.37
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         872792032
undo change vector size                                               87764

--redo
SQL> select 21892-732 "create redo size" from dual;

create redo size
----------------
           21160

SQL> select 872792032-21892 "insert redo size" from dual;

insert redo size
----------------
       872770140

--undo
SQL> select 6308-136 "create undo size" from dual;

create undo size
----------------
            6172

SQL> select 87764-6308 "insert undo size" from dual;

insert undo size
----------------
           81456

这个过程可以得到结论,create 表的过程产生的redo:21160,undo:6172;insert 表的过程redo:872770140,undo:81456;整个过程产生的redo:872791300(21160+872770140),undo:87628(6172+81456)

5.INSERT+NOLOGGING

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:02.21
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.06
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136

Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_INSERT_N tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.22
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22056
undo change vector size                                                6308

Elapsed: 00:00:00.00
SQL> INSERT /*+ NOLOGGING */INTO t_xifenfei_move_INSERT_N
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:02:30.33
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862123984
undo change vector size                                            27982876

--redo
SQL> select 22056-780 "create redo size" from dual;

create redo size
----------------
           21276

SQL> select 862123984-22056 "insert redo size" from dual;

insert redo size
----------------
       862101928

--undo
SQL> select 6308-136 "create undo size" from dual;

create undo size
----------------
            6172

SQL> select 27982876-6308 "insert undo size" from dual;

insert undo size
----------------
        27976568

这个试验过程得出,create table得到redo:21276,undo:6172,insert table 得到redo:862101928,undo:27976568,整个过程redo:862123204,undo:27982740(27976568+6172)

6.INSERT+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:23.68
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               800
undo change vector size                                                 136

Elapsed: 00:00:00.18
SQL> create table chf.t_xifenfei_move_INSERT_N_new nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.71
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             23296
undo change vector size                                                6756

Elapsed: 00:00:00.00
SQL> INSERT INTO t_xifenfei_move_INSERT_N_new
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:02:37.51
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862031304
undo change vector size                                            27982776

SQL> select 23296-800 "create redo size" from dual;

create redo size
----------------
           22496

SQL> select 862031304-23296 "insert redo size" from dual;

insert redo size
----------------
       862008008

SQL> select 6756-136 "create undo size" from dual;

create undo size
----------------
            6620

SQL> select 27982776-6756 "insert undo size" from dual;

insert undo size
----------------
        27976020

通过该试验得出,create table 产生redo:22496,undo:6620;insert into 产生redo:862008008,undo:27976020;整个过程产生redo:862030504(22496+862008008),undo:27982640(6620+27976020)

7.APPEND+NOLOGGING(TABLE)

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:23.59
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136

Elapsed: 00:00:00.04
SQL> create table chf.t_xifenfei_move_INSERT_NA nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.42
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22000
undo change vector size                                                6308

Elapsed: 00:00:00.00
SQL> INSERT /*+APPEND */ INTO t_xifenfei_move_INSERT_NA
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:01:08.92
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            602352
undo change vector size                                               82756

--redo
SQL> select 22000-780 "create redo size" from dual;

create redo size
----------------
           21220

SQL> select 602352-22000 "insert redo size" from dual;

insert redo size
----------------
          580352

--undo
SQL> select 6308-136 "create undo size" from dual;

create undo size
----------------
            6172

SQL> select 82756-6308 "insert undo size" from dual;

insert undo size
----------------
           76448

在这个试验中，create table产生redo:21220,undo:6172,insert into产生redo:580352,undo:76448;整个过程产生的redo:601572(
21220+580352),undo:82620(6172+76448)

8.CAST+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:03.35
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> 
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136

Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_cast_N nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;

Table created.

Elapsed: 00:00:56.41
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            769848
undo change vector size                                              124944

SQL> select 769848-732 "redo size" from dual;

 redo size
----------
    769116

SQL> select 124944-136 "undo  size" from dual;

undo  size
----------
    124808

这个试验产生的redo:769116,undo:124808

9.通过试验得出结论
1)sql hint中的nologgging无效
2)普通的cast(不含hint)，其本质是append，无nologgging
3)nologgging(表级别)可以使得cast效率较高
4)nologgging(表级别)+append(hint)可以使得insert效率较高

发表在 Oracle | 评论关闭