分类目录归档：数据库

跳过obj$坏块方法

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

1.确定obj$坏块存在

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 /*+full(obj$)*/ count(*) from obj$;
select /*+full(obj$)*/ count(*) from obj$
                                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

2.使用dbms_repair跳过坏块

SQL> exec dbms_repair.skip_corrupt_blocks('SYS','OBJ$');
BEGIN dbms_repair.skip_corrupt_blocks('SYS','OBJ$'); END;

*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
ORA-06512: at "SYS.DBMS_REPAIR", line 419
ORA-06512: at line 1

--ORA-00701原因
SQL> set pages 100
SQL> SELECT * FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%OBJ$%';

     LINE#       OBJ#
---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
        18      90724
CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT N
ULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(
30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME"
DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" VAR
CHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3
" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 16384 NEXT 106496 MINEX
TENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 90724 EXTENTS (FILE 1 BLOCK 94
505))
--obj$是BOOTSTRAP$中对象，不能被修改
--这里打破一个传奇:一般人都说BOOTSTRAP$中对象都是object_id是非常小靠前。
--但是我这个从10g升级过来的库，obj$的object_id为90724

3.使用event跳过坏块

pfile中添加
event="10231 trace name context forever, level 10"

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

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             398460288 bytes
Database Buffers          213909504 bytes
Redo Buffers                7548928 bytes
Database mounted.
Database opened.
SQL> select /*+full(obj$)*/ count(*) from obj$;

  COUNT(*)
----------
     74503
发表在 Oracle | 标签为 | 评论关闭

obj$坏块情况下exp导出单个表解决方案

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

在前面一篇(obj$坏块exp不能执行原因探讨)已经研究了在obj$出现坏块的情况下，导致exp导出单个表不能成功的原因，这篇给出解决方案
1.重新创建exu81javt视图

SQL> CREATE OR REPLACE view exu81javt (objid) AS
  2      SELECT  obj#
  3          FROM    sys.obj$
  4          WHERE   name = 'oracle/aurora/rdbms/DbmsJava' AND
  5                  type# = 29 AND
  6                  owner# = 0 AND
  7                  status = 1
  8   /    

View created.

SQL> GRANT SELECT ON sys.exu81javt TO PUBLIC;

Grant succeeded.

SQL>  set autot  trace
SQL> SELECT COUNT(*)      FROM   SYS.EXU81JAVT;


Execution Plan
----------------------------------------------------------
Plan hash value: 2521745379

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

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

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

|   0 | SELECT STATEMENT             |        |     1 |    35 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |        |     1 |    35 |            |      |

|*  2 |   TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    35 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I_OBJ2 |     1 |       |     3   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"=1)
   3 - access("OWNER#"=0 AND "NAME"='oracle/aurora/rdbms/DbmsJava' AND
              "TYPE#"=29)
       filter("TYPE#"=29)

通过对这个视图的重新创建，是的原来需要对obj$表全表扫描，改为走I_OBJ2索引，从而避免了部分坏块导致的exp异常。

2.测试exp导出单表

[oracle@node1 tmp]$ exp "'/ as sysdba'" tables=chf.t_undo file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log INDEXES =n \
> COMPRESS =n CONSISTENT =n GRANTS =n STATISTICS =none TRIGGERS =n CONSTRAINTS =n

Export: Release 11.2.0.3.0 - Production on Sun Jan 15 23:39:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                         T_UNDO       1636 rows exported
Export terminated successfully without warnings.

测试证明修改了exu81javt视图后，exp导出单个表成功

3.生成导出脚本

SELECT 'exp "''' || '/ as sysdba''" tables=' || U.NAME || '.' || O.NAME ||
       ' file=' || '&PATH' || U.NAME || '_' || O.NAME || '.dmp log=' ||
       '&PATH' || U.NAME || '_' || O.NAME ||
       '.log buffer=1024000  COMPRESS =N STATISTICS =NONE'
  FROM TAB$ T, OBJ$ O, USER$ U
 WHERE O.TYPE# = 2
   AND T.OBJ# = O.OBJ#
   AND U.USER# = O.OWNER#
   AND u.name IN('CHF');

Execution Plan
----------------------------------------------------------
Plan hash value: 3095026863

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

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

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

|   0 | SELECT STATEMENT              |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |         |    33 |  1782 |    31   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| USER$   |     1 |    17 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | I_USER1 |     1 |       |     0   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | I_OBJ2  |    33 |  1221 |    30   (0)| 00:00:01 |

|   6 |   TABLE ACCESS CLUSTER        | TAB$    |     1 |     5 |     1   (0)| 00:00:01 |

|*  7 |    INDEX UNIQUE SCAN          | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |

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

4.补充说明
1)并非所有的obj$坏块都可以通过该方法，使得exp导出单个表正常
2)在系统确实无救，有不想使用dul/odu的情况下，可以尝试这种方法抢救数据。

发表在 ORA-xxxxx, 逻辑备份/恢复 | 标签为 | 评论关闭

obj$坏块exp不能执行原因探讨

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

上篇(obj$坏块exp/expdp导出不能执行)，验证了在obj$有坏块的情况下，不能执行exp/expdp操作，这篇是说明是什么原因导致在obj$有坏块的情况下exp不能正常执行
一.启动数据库级别会话跟踪

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 15 11:37:07 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

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

File created.

--------------------------------------------------
在pfile中添加
event='10046 trace name context forever,level 12'
--------------------------------------------------

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

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             398460288 bytes
Database Buffers          213909504 bytes
Redo Buffers                7548928 bytes
Database mounted.
Database opened.

二.执行单表导出，找到trace文件

[oracle@node1 trace]$ exp "'/ as sysdba'" tables=chf.t1 file=/tmp/xifenfei.dmp \
> log=/tmp/xifenfei.log INDEXES =n  COMPRESS =n CONSISTENT =n GRANTS =n \
> STATISTICS =none TRIGGERS =n CONSTRAINTS =n

Export: Release 11.2.0.3.0 - Production on Sun Jan 15 11:48:50 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                             T1

--另外会话观察
Tasks: 241 total,   1 running, 240 sleeping,   0 stopped,   0 zombie
Cpu(s):  8.9%us,  1.2%sy,  0.0%ni, 85.1%id,  4.8%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8165060k total,  7168288k used,   996772k free,   266028k buffers
Swap:  8289500k total,      168k used,  8289332k free,  4653408k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                         
 4829 oracle    18   0 69812  12m 9144 S 51.1  0.2   0:03.64 exp               tables=chf.t1 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log INDEXES =n COMPRESS
 4830 oracle    18   0  829m  62m  58m D 27.9  0.8   0:03.85 oraclechf (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))  

[oracle@node1 trace]$ ll |grep 4830
-rw-r----- 1 oracle oinstall 14101447 01-15 11:49 chf_ora_4830.trc
-rw-r----- 1 oracle oinstall    75398 01-15 11:49 chf_ora_4830.trm
1

<strong>三.阅读trace文件</strong>
因为是obj$对象出现坏块，导致exp不能执行，如果是使用了obj$表的index，那么不会每次都报错，而我测试了多次都报错，所以怀疑是对obj$表进行全表扫描导致该错误发生，而使得exp不能继续下去。所以这次查找trace文件，重点是关注obj$表的全表扫描操作，经过耐心查找，终于发现了一个对obj$全表扫描的操作
1
PARSING IN CURSOR #46986932266584 len=41 dep=0 uid=0 oct=3 lid=0 tim=1326599330636591 hv=2311813821 ad='7be773c8' sqlid='ftx7dd64wqypx'
SELECT COUNT(*)      FROM   SYS.EXU81JAVT
END OF STMT
PARSE #46986932266584:c=2999,e=2938,p=5,cr=23,cu=0,mis=1,r=0,dep=0,og=1,plh=23986678,tim=1326599330636590
WAIT #46986932266584: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636682
WAIT #46986932266584: nam='SQL*Net message from client' ela= 42 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636738
EXEC #46986932266584:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=23986678,tim=1326599330636788
WAIT #46986932266584: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636810
WAIT #46986932266584: nam='SQL*Net message from client' ela= 91 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636913
WAIT #46986932266584: nam='SQL*Net message to client' ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668126
FETCH #46986932266584:c=30995,e=31256,p=0,cr=989,cu=0,mis=0,r=1,dep=0,og=1,plh=23986678,tim=1326599330668198
STAT #46986932266584 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=989 pr=0 pw=0 time=31173 us)'
STAT #46986932266584 id=2 cnt=1 pid=1 pos=1 obj=90724 op='TABLE ACCESS FULL OBJ$ (cr=989 pr=0 pw=0 time=31156 us cost=220 size=18270 card=522)'
WAIT #46986932266584: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668403
CLOSE #46986932266584:c=0,e=10,dep=0,type=0,tim=1326599330668452
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668481
WAIT #0: nam='SQL*Net message from client' ela= 113 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668606

四.对EXU81JAVT对象深究

SQL> select object_type from dba_objects where object_name='EXU81JAVT';

OBJECT_TYPE
-------------------
VIEW

SQL> set long 1000
SQL> select TEXT from dba_views where view_name='EXU81JAVT';

TEXT
------------------------------------------------------
SELECT  obj#
        FROM    sys.obj$
        WHERE   name LIKE '%DbmsJava' AND
                type# = 29 AND
                owner# = 0 AND
                status = 1


SQL> SELECT  obj#
  2          FROM    sys.obj$
  3          WHERE   name LIKE '%DbmsJava' AND
  4                type# = 29 AND
  5                owner# = 0 AND
  6                status = 1     ;

      OBJ#
----------
     17671

SQL> select name from obj$ where obj#=17671;

NAME
------------------------------
oracle/aurora/rdbms/DbmsJava

现在稳定已经定位到，是因为exp判断是否使用了java，是去找”/oracle/aurora/rdbms/DbmsJava”.这个对象的，如果java enabled，那么它就会使用dbms_java做一些转换，实际上oracle是查找视图exu81javt来确定DbmsJava的。
这里的EXU81JAVT是查询obj$而是通过name LIKE ‘%DbmsJava’,导致index不能正常使用，从而使得obj$全表扫描，而obj$有坏块，从而使得exp在obj$有坏块的情况下，不能正常执行

发表在 ORA-xxxxx, 逻辑备份/恢复 | 标签为 | 评论关闭