月归档：三月 2012

Multiple-table cache group配置

发表于 2012 年 3 月 9 日 由 惜分飞

1.准备测试表和数据

SQL> CREATE TABLE customer
  2  (cust_num NUMBER(6) NOT NULL PRIMARY KEY,
  3   name     VARCHAR2(50)
  4  );

Table created.

SQL> CREATE TABLE orders
  2  (ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
  3   cust_num     NUMBER(6) NOT NULL
  4  );

Table created.


SQL> insert into customer values(1,'wwww.xifenfei.com1');

1 row created.

SQL> insert into customer values(2,'wwww.xifenfei.com2');

1 row created.

SQL> insert into customer values(3,'wwww.xifenfei.com3');

1 row created.

SQL> insert into customer values(4,'wwww.xifenfei.com4');

1 row created.

SQL> insert into orders(cust_num,ord_num) values(1,1);

1 row created.

SQL> insert into orders (cust_num,ord_num) values(1,2);

1 row created.

SQL> insert into orders (cust_num,ord_num) values(3,5);

1 row created.

SQL> insert into orders (cust_num,ord_num) values(3,6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from customer;

  CUST_NUM NAME
---------- --------------------------------------------------
         1 wwww.xifenfei.com1
         2 wwww.xifenfei.com2
         3 wwww.xifenfei.com3
         4 wwww.xifenfei.com4

SQL> select * from orders;

   ORD_NUM   CUST_NUM
---------- ----------
         1          1
         2          1
         5          3
         6          3

SQL> grant select on oratt.customer to cacheuser;

Grant succeeded.

SQL> grant select on oratt.orders to cacheuser;

Grant succeeded.

2.创建cache group

[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"

Command>  drop cache group cacheuser.customer_orders;
Command> CREATE READONLY CACHE GROUP customer_orders
       > AUTOREFRESH INTERVAL 5 SECONDS
       > STATE ON
       > FROM oratt.customer
       >  (cust_num NUMBER(6) NOT NULL,
       >   name     VARCHAR2(50),
       >   PRIMARY KEY(cust_num)),
       > oratt.orders
       >  (ord_num      NUMBER(10) NOT NULL,
       >   cust_num     NUMBER(6) NOT NULL,
       >   PRIMARY KEY(ord_num),
       >   FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));

Command> cachegroups;

Cache Group CACHEUSER.CUSTOMER_ORDERS:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: ORATT.CUSTOMER
  Table Type: Read Only


  Child Table: ORATT.ORDERS
  Table Type: Read Only

1 cache groups found.

3.TT中表访问授权

[oracle@xifenfei ~]$ ttisql tt_1122

Command> grant select on oratt.customer to cacheuser;
Command> grant select on oratt.orders to cacheuser;

4.测试数据初始化

[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> select * from oratt.customer;
< 1, wwww.xifenfei.com1 >
< 2, wwww.xifenfei.com2 >
< 3, wwww.xifenfei.com3 >
< 4, wwww.xifenfei.com4 >
4 rows found.
Command> select * from oratt.orders;
< 1, 1 >
< 2, 1 >
< 5, 3 >
< 6, 3 >
4 rows found.

5.ORACLE修改数据

SQL> update customer set name='xifenfei' where cust_num=2;

1 row updated.

SQL> insert into customer values(5,'wwww.xifenfei.com5');

1 row created.

SQL> delete from customer where cust_num=1;

1 row deleted.

SQL> commit;

Commit complete.

6.TT中验证数据

Command> select * from oratt.customer;
< 2, xifenfei >
< 3, wwww.xifenfei.com3 >
< 4, wwww.xifenfei.com4 >
< 5, wwww.xifenfei.com5 >
4 rows found.
Command> select * from oratt.orders;
< 5, 3 >
< 6, 3 >
2 rows found.

7.补充说明
7.1)在oracle中需要授权cacheuser有访问oratt中相关表权限，不然创建cache group失败
7.2)自动刷新数据需要设置AUTOREFRESH STATE ON，其他方法初始化关联表的数据暂未知
7.3)在TT中，关联表删除是级联的

发表在 TimesTen | 评论关闭

记录一次ORA-600[13013]处理过程

发表于 2012 年 3 月 9 日 由 惜分飞

在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误

Thu Mar 08 23:29:37 2012
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc  (incident=38681):
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/chf/chf/incident/incdir_38681/chf_smon_24137_i38681.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 1 out of maximum 100 non-fatal internal errors.

trace文件中信息
从这里可以看出是对sys.col_usage$表进行update操作导致该错误发生

Dump continued from file: /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []

========= Dump for incident 38681 (ORA 600 [13013]) ========

*** 2012-03-08 23:29:37.400
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3c1kubcdjnppq) -----
update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitand(:flag,1),0,0,1),   equijoin_preds    = equijoin_preds    + decode(bitand(:flag,2),0,0
,1),   nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1),   range_preds       = range_preds       + decode(bitand(:flag,8),0,0,1),   like_preds    
    = like_preds        + decode(bitand(:flag,16),0,0,1),   null_preds        = null_preds        + decode(bitand(:flag,32),0,0,1),  timestamp = :time where obj# = :ob
jn and intcol# = :coln

MOS中关于ORA-600 [13013]描述

Format: ORA-600 [13013] [a] [b] {c} [d] [e] [f]
Arg [a] Passcount
Arg [b] Data Object number
Arg {c} Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Decimal RDBA of block being updated (Typically same as {c})
Arg [f] Code

验证MOS中描述

SQL> select dbms_utility.data_block_address_file(4198427) rfile,
  2  dbms_utility.data_block_address_block(4198427) blocks
  3  from dual;

     RFILE     BLOCKS
---------- ----------
         1       4123

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 4123
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 4123 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE PARTI
----- ------------ ---------- ---------- -----
SYS   COL_USAGE$   TABLE      SYSTEM
--和trace文件中异常表一致

SQL>  select object_type,object_name from dba_objects where object_id=518;

OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
TABLE               COL_USAGE$
--也和trace文件中异常表一致

分析异常表

SQL> ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

SQL> select index_name,COLUMN_NAME,COLUMN_POSITION FROM DBA_IND_COLUMNS 
2    WHERE TABLE_NAME='COL_USAGE$';

INDEX_NAME      COLUMN_NAM COLUMN_POSITION
--------------- ---------- ---------------
I_COL_USAGE$    OBJ#                     1
I_COL_USAGE$    INTCOL#                  2

SQL> set autot trace exp
SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t1
  3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null;

no rows selected
--无记录返回

Execution Plan
----------------------------------------------------------
Plan hash value: 399371572

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
   |

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

|   0 | SELECT STATEMENT    |              |  4262 | 76716 |    27  (71)| 00:00:01 |
|   1 |  MINUS              |              |       |       |            |          |
|   2 |   SORT UNIQUE       |              |  4262 | 38358 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| COL_USAGE$   |  4262 | 38358 |     8   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|              |  4262 | 38358 |    18   (6)| 00:00:01 |
|*  5 |    INDEX FULL SCAN  | I_COL_USAGE$ |  4262 | 38358 |    17   (0)| 00:00:01 |

------------------------------------------------------------------------------------
--验证表两个sql是否正确(一个全表扫描，另个index 快速扫描)


SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;

      OBJ#    INTCOL#
---------- ----------
4294951004          2
4294951004          3
4294951004          4
4294951004         26
4294951004         27
4294951037          4
4294951037          5
4294951037          6
4294951037          9
4294951037         10
4294951840         11

      OBJ#    INTCOL#
---------- ----------
4294951840         12
4294951906          4
4294952709          3
4294952867          4
4294952867          9

16 rows selected.
--证明index中的记录比表中多了16条

解决问题并验证

SQL> alter index sys.I_COL_USAGE$ rebuild online;

Index altered.

SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
 FROM sys.COL_USAGE$ t1
  2    3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  6  ;

no rows selected

SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;

no rows selected

这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引
官方关于ORA-600[13013]说明

发表在 ORA-xxxxx | 标签为 , | 3 条评论

使用_allow_resetlogs_corruption打开无归档日志rman备份库

发表于 2012 年 3 月 8 日 由 惜分飞

rman还原恢复操作

--还原数据库
RMAN> restore database;
--恢复数据库
RMAN> recover database;

Starting recover at 2012-03-08 21:20:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2012 21:20:47
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 2936 and starting SCN of 25991695 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2935 and starting SCN of 25991652 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2934 and starting SCN of 25991649 found to restore
……………………
RMAN-06025: no backup of archived log for thread 1 with sequence 2902 and starting SCN of 25991156 found to restore
这里报日志缺少，实际上是备份的数据库文件后，没有备份归档日志，归档日志全部丢失

进行不完全恢复

SQL> recover database until cancel;
ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf
ORA-00280: change 25991194 for thread 1 is in sequence #2902


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

查看相关SCN

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25992214
         2      25992214
         3      25992214
         4      25992214
         5      25992214
         6      25992214
         7      25992214
         8      25992214
         9      25992214
        10      25992214
        11      25992214

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25992214
        14      25992214

13 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         1 ONLINE       25991194
         2 ONLINE       25991194
         3 ONLINE       25991194
         4 ONLINE       25991194
         5 ONLINE       25991194
         6 ONLINE       25991194
         7 ONLINE       25991194
         8 ONLINE       25991194
         9 ONLINE       25991194
        10 ONLINE       25991194
        11 ONLINE       25991194

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
        13 ONLINE       25991194
        14 ONLINE       25991194

13 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194

13 rows selected.

--发现数据文件scn和控制文件不一致，重建控制文件，然后查询相关scn
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194

13 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         1 ONLINE       25991194
         2 ONLINE       25991194
         3 ONLINE       25991194
         4 ONLINE       25991194
         5 ONLINE       25991194
         6 ONLINE       25991194
         7 ONLINE       25991194
         8 ONLINE       25991194
         9 ONLINE       25991194
        10 ONLINE       25991194
        11 ONLINE       25991194

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
        13 ONLINE       25991194
        14 ONLINE       25991194

13 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194

13 rows selected.
--此时所有scn均一致

尝试打开数据库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf
ORA-00280: change 25991194 for thread 1 is in sequence #2902


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

使用隐含参数打开数据库

SQL> create pfile='/tmp/pfile' from spfile;

File created.

-------/tmp/pfile中加上----------
_allow_resetlogs_corruption= TRUE
---------------------------------

SQL> startup mount pfile='/tmp/pfile' force
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             419431808 bytes
Database Buffers          192937984 bytes
Redo Buffers                7548928 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

总结
这次的试验没有多少实际意义，但是可以说明几个问题：
1.所有的数据文件的scn都一致，甚至和控制文件的也一致，数据库不一定可以open成功
(怀疑是数据文件中的scn大于data header scn)
2.对于这样的问题，如果使用bbed修改所有数据文件header的scn不知道是否可以解决
3.如果rman只备份了数据文件而没有任何一个归档日志，数据库通过隐含参数还是可以open，抢救数据

发表在 rman备份/恢复 | 标签为 | 一条评论