分类目录归档：Oracle

ORACEL坏查询对象批量脚本

发表于 2014 年 9 月 2 日 由 惜分飞

查询坏块

SQL> set lines 120
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO      
---------- ---------- ---------- ------------------ ---------      
         3      35418          1                  0 FRACTURED      
         3      61344          1                  0 FRACTURED      
         3      31065          1                  0 CORRUPT        
         3      36673          1                  0 CORRUPT        
         3      36721          1                  0 CORRUPT        
         3      42881          1                  0 CORRUPT        
         1      66738          1                  0 CORRUPT        
         3      36329          1                  0 CORRUPT        
         3      36617          1                  0 CORRUPT        
         3      32404          1                  0 FRACTURED      
         3      36281          1                  0 FRACTURED      

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO      
---------- ---------- ---------- ------------------ ---------      
         3      36625          1                  0 FRACTURED      
         1      39041          1                  0 CORRUPT        
         3      36713          1                  0 CORRUPT        
        10      69927          1                  0 FRACTURED      
        26      94244          1                  0 CORRUPT        

已选择16行。

查询坏块对应对象

SQL> set pagesize 2000
SQL>          set linesize 250
SQL>      SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2       , greatest(e.block_id, c.block#) corr_start_block#
  3       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6       , null description
  7    FROM dba_extents e, v$database_block_corruption c
  8   WHERE e.file_id = c.file#
  9     AND e.block_id <= c.block# + c.blocks - 1
 10     AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13       , header_block corr_start_block#
 14       , header_block corr_end_block#
 15       , 1 blocks_corrupted
 16       , 'Segment Header' description
 17    FROM dba_segments s, v$database_block_corruption c
 18   WHERE s.header_file = c.file#
 19     AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22       , greatest(f.block_id, c.block#) corr_start_block#
 23       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26       , 'Free Block' description
 27    FROM dba_free_space f, v$database_block_corruption c
 28   WHERE f.file_id = c.file#
 29     AND f.block_id <= c.block# + c.blocks - 1
 30     AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, corr_start_block#;

OWNER            SEGMENT_TYPE       SEGMENT_NAME                 PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------------- ----------------- ----------------------- ------------------------------- ------ ----------------- --------------- ---------------- --------------      
SYS              TABLE              OBJ$                                                        1             39041           39041                1                     
                                                                                                1             66738           66738                1 Free Block          
SYSMAN           INDEX              MGMT_METRICS_1HOUR_PK                                       3             31065           31065                1                     
SYS              TABLE              WRH$_SQL_BIND_METADATA                                      3             32404           32404                1                     
SYS              TABLE              WRH$_BG_EVENT_SUMMARY                                       3             35418           35418                1                     
SYS              INDEX PARTITION    WRH$_FILESTATXS_PK      WRH$_FILEST_1232289473_41482        3             36281           36281                1                     
SYS              TABLE PARTITION    WRH$_SYSTEM_EVENT       WRH$_SYSTEM_1232289473_41482        3             36329           36329                1                     
SYS              TABLE PARTITION    WRH$_SGASTAT            WRH$_SGASTA_1232289473_41482        3             36617           36617                1                     
SYS              INDEX PARTITION    WRH$_SGASTAT_U          WRH$_SGASTA_1232289473_41482        3             36625           36625                1                     
SYS              INDEX PARTITION    WRH$_PARAMETER_PK       WRH$_PARAME_1232289473_41482        3             36673           36673                1                     
SYS              TABLE PARTITION    WRH$_SERVICE_STAT       WRH$_SERVIC_1232289473_41482        3             36713           36713                1                     
SYS              INDEX PARTITION    WRH$_SERVICE_STAT_PK    WRH$_SERVIC_1232289473_41482        3             36721           36721                1                     
SYS              TABLE PARTITION    WRH$_LATCH              WRH$_LATCH_1232289473_41482         3             42881           42881                1                     
SYS              TABLE              WRI$_ADV_ACTIONS                                            3             61344           61344                1                     
EXAM             TABLE              EXAM_ITEMS_OLD                                             10             69927           69927                1                     
CPR              TABLE              NEED_MONITOR                                               26             94244           94244                1                     
                                                                                                                                                                         
已选择16行。                                                                                                                                                             
                                                                                                                                                                         
SQL>                                                                                                                                                                     
SQL> spool off
发表在 Oracle 开发 | 标签为 | 评论关闭

在Chrome高版本中配置使用AWR-Format for Chrome插件

发表于 2014 年 8 月 31 日 由 惜分飞

问题背景
从Chrome 21版本开始，不再直接支持非Chrome官网下载，导致AWR-Format无法正常使用，按照网络上描述：
1. 使用 -enable-easy-off-store-extension-install 不行
2. 直接使用开发者模式安装也不行
不能解决问题，通过查资料和分析语句，解决该问题，和大家分享

使用方法
下载地址：新Chrome AWR-Format
处理步骤：解压该文件，启动chrome 开发者模式，增加该目录，关闭开发者模式

具体参考
支持新Chrome AWR-Format

发表在 Oracle | 标签为 | 评论关闭

记录一次system表空间坏块(ORA-01578)数据库恢复

发表于 2014 年 8 月 30 日 由 惜分飞

半夜朋友打来求救电话,说xx医院his系统因为存储异常导致system坏块无法正常启动,因为是win平台无法使用bbed,无法修复system 坏块,请求技术支持
dbv检查system文件报坏块
1

对应具体地址为:file 1 block 39041和66738

判断控制文件异常
通过数据库恢复检查脚本(Oracle Database Recovery Check)脚本检测数据库发现控制文件明显异常(checkpoint scn)
2
3

尝试恢复数据库
4

因此对该库进行了不完全恢复,然后尝试resetlogs打开数据库,数据库报ORA-600 2662错误

Fri Aug 29 02:35:08 2014
alter database open resetlogs
Fri Aug 29 02:35:11 2014
RESETLOGS after complete recovery through change 451371288
Resetting resetlogs activation ID 1232269761 (0x4972f1c1)
Fri Aug 29 02:35:15 2014
Setting recovery target incarnation to 3
Fri Aug 29 02:35:15 2014
Assigning activation ID 1384652231 (0x52881dc7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=1084
Fri Aug 29 02:35:15 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=2836
Fri Aug 29 02:35:15 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: Z:\ORACLE\PRODUCT\10.2.0\ORCL\REDO01.LOG
Successful open of redo thread 1
Fri Aug 29 02:35:15 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Aug 29 02:35:15 2014
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Aug 29 02:35:15 2014
ARC0: Becoming the heartbeat ARCH
Fri Aug 29 02:35:15 2014
SMON: enabling cache recovery
Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []

Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []

Fri Aug 29 02:35:16 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Fri Aug 29 02:35:17 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2928.trc:
ORA-00600: ??????, ??: [], [], [], [], [], [], [], []

Instance terminated by USER, pid = 4824
ORA-1092 signalled during: alter database open resetlogs...

ORA-600 2662 该错误解决思路很明显,推进scn,数据库报ORA-01578

Fri Aug 29 02:42:47 2014
SMON: enabling cache recovery
Fri Aug 29 02:42:47 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Fri Aug 29 02:42:49 2014
SMON: enabling tx recovery
Fri Aug 29 02:42:49 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Aug 29 02:42:50 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_4804.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 39041)
ORA-01110: 数据文件 1: 'Z:\ORACLE\PRODUCT\10.2.0\ORCL\SYSTEM01.DBF'

Fri Aug 29 02:42:50 2014
LOGSTDBY: Validating controlfile with logical metadata
Fri Aug 29 02:42:51 2014
LOGSTDBY: Validation complete
ORA-604 signalled during: alter database open...

对坏块进行处理，启动数据库成功

Fri Aug 29 02:48:59 2014
SMON: enabling cache recovery
Fri Aug 29 02:49:00 2014
Successfully onlined Undo Tablespace 1.
Fri Aug 29 02:49:00 2014
SMON: enabling tx recovery
Fri Aug 29 02:49:00 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=34, OS id=3096
Fri Aug 29 02:49:01 2014
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Aug 29 02:49:01 2014
Completed: alter database open

查询坏块对象
5
6

因为这些对象均不是核心对象,直接进行truncate然后插入老数据

后续还有大量错误修复

ORA-12012: error on auto execute of job 1
ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)

ORA-00600: 内部错误代码, 参数: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

ORA-00600: internal error code, arguments: [6749], [3], [12606796], [173], [], [], [], []

ORA-00600: 内部错误代码, 参数: [13013], [52898], [52895], [38288618], [44], [38288618], [17], []

ORA-00600: 内部错误代码, 参数: [13013], [5001], [52895], [38286476], [5], [38286476], [17], []

再次说明,很多时候数据库恢复不要看成多神秘,就是几个参数搞定,更加不要神化有坏块就bbed修复,当然非常极端,使用N中工具,N种尝试的也存在.做好备份重于一切

发表在 Oracle备份恢复 | 标签为 , , , , , , | 评论关闭