ORA-00600 [ktbdchk1: bad dscn] 解决

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-00600 [ktbdchk1: bad dscn] 解决

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

启动数据库报错
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1260720 bytes
Variable Size             150995792 bytes
Database Buffers            8388608 bytes
Redo Buffers                7127040 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
alert.log导错
Wed Aug 10 12:31:11 2011
Errors in file /u01/admin/xienfei/udump/xff_ora_8568.trc:
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], []
xff_ora_8568.trc内容
[ktbdchk] -- readers_dsz -- bad dscn
scn: 0x0000.b1e60c00scn: 0x0000.0011fca1
*** 2011-08-10 12:31:11.998
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
根据上面错误判断,错误的scn为b1e60c00,不是整个数据文件的scn错误
而应该是一个对象的scn错误,所以继续在xff_ora_8568.trc文件中查找b1e60c00
找到结果如下:
Block header dump:  0x0040007a
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x00.b1e60c00  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.02a.000001d9  0x00802341.01bb.04  ----    1  fsc 0x0000.0011ae7c
data_block_dump,data header at 0x20fd6044
===============
tsiz: 0x1fb8
hsiz: 0xea
pbl: 0x20fd6044
bdba: 0x0040007a
     76543210
flag=--------
ntab=1
nrow=108
frre=-1
fsbo=0xea
fseo=0x453
avsp=0x369
tosp=0x369
0xe:pti[0]      nrow=108        offs=0
根据这个提示,发现dba为:0040007a的对象异常,查找对应的file_id,block
SQL> SELECT DBMS_UTILITY.data_block_address_file (TO_NUMBER ('40007a', 'XXXXXXXX')) file_id,
  2          DBMS_UTILITY.data_block_address_block (TO_NUMBER ('40007a', 'XXXXXXXX')) block_id
  3    FROM DUAL;
   FILE_ID   BLOCK_ID
---------- ----------
         1        122
使用bbed查看file=1,block=122的scn情况
BBED> p ktbbh
struct ktbbh, 48 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0xb1e60c00
      ub2 kscnwrp                           @32       0x0000
   b2 ktbbhict                              @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44
      struct ktbitxid, 8 bytes              @44
         ub2 kxidusn                        @44       0x0008
         ub2 kxidslt                        @46       0x002a
         ub4 kxidsqn                        @48       0x000001d9
      struct ktbituba, 8 bytes              @52
         ub4 kubadba                        @52       0x00802341
         ub2 kubaseq                        @56       0x01bb
         ub1 kubarec                        @58       0x04
      ub2 ktbitflg                          @60       0x0001 (NONE)
      union _ktbitun, 2 bytes               @62
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x0011ae7c
果然发现scn为0xb1e60c00,现在把其修改为:0x00124ac6(注意规则,一般linux下都是倒序)
BBED> set offset 28
        OFFSET          28
BBED> m /x c64a1200
BBED-00209: invalid number (c64a1200)
小技巧,一次性修改报错,尝试一次修改一点
BBED> m /x c64a
 File: /u01/oradata/xienfei/system01.dbf (0)
 Block: 122              Offsets:   28 to   43           Dba:0x00000000
------------------------------------------------------------------------
 c64ae6b1 00000000 01000200 00000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          30
BBED> m /x 1200
 File: /u01/oradata/xienfei/system01.dbf (0)
 Block: 122              Offsets:   30 to   45           Dba:0x00000000
------------------------------------------------------------------------
 12000000 00000100 02000000 00000800
 <32 bytes per line>
BBED> set offset -2
        OFFSET          28
BBED> dump
 File: /u01/oradata/xienfei/system01.dbf (0)
 Block: 122              Offsets:   28 to   43           Dba:0x00000000
------------------------------------------------------------------------
 c64a1200 00000000 01000200 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 0, Block 122:
current = 0x3a4e, required = 0x3a4e
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1260720 bytes
Variable Size             150995792 bytes
Database Buffers            8388608 bytes
Redo Buffers                7127040 bytes
Database mounted.
Database opened.

ORA-600 [LibraryCacheNotEmptyOnClose] on shutdown

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-600 [LibraryCacheNotEmptyOnClose] on shutdown

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、现象
alert.log中记录
Mon May 9 19:56:10 2011(shutdown 数据库过程中)
Errors in file /opt/oracle/admin/xunzhi/udump/xunzhi_ora_328.trc:
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []
trace中记录

*** 2011-05-09 19:56:10.843
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFB5A19840 ? 7FFFB5A198A0 ?
                                                   7FFFB5A197E0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFB5A19840 ? 7FFFB5A198A0 ?
                                                   7FFFB5A197E0 ? 000000000 ?
ksfdmp()+21          call     ksedmp()             000000003 ? 000000001 ?
                                                   7FFFB5A19840 ? 7FFFB5A198A0 ?
                                                   7FFFB5A197E0 ? 000000000 ?
kgerinv()+161        call     ksfdmp()             000000003 ? 000000001 ?
                                                   7FFFB5A19840 ? 7FFFB5A198A0 ?
                                                   7FFFB5A197E0 ? 000000000 ?
kgeasnmierr()+163    call     kgerinv()            0068966E0 ? 01EFD6610 ?
                                                   7FFFB5A198A0 ? 7FFFB5A197E0 ?
                                                   000000000 ? 000000000 ?
kglshu()+757         call     kgeasnmierr()        0068966E0 ? 01EFD6610 ?
                                                   7FFFB5A198A0 ? 7FFFB5A197E0 ?
                                                   000000000 ? 000000001 ?
kqlnfy()+468         call     kglshu()             0068966E0 ? 000000000 ?
                                                   7FFFB5A198A0 ? 7FFFB5A197E0 ?
                                                   000000000 ? 000000001 ?
kscnfy()+587         call     kqlnfy()             000000018 ? 000000000 ?
                                                   7FFFB5A198A0 ? 7FFFB5A197E0 ?
                                                   000000000 ? 000000001 ?
ksmshu()+269         call     kscnfy()             000000018 ? 000000000 ?
                                                   000000000 ? 7FFFB5A197E0 ?
                                                   000000000 ? 000000001 ?
opistp_real()+1052   call     ksmshu()             000000018 ? 000000000 ?
                                                   000000000 ? 7FFFB5A197E0 ?
                                                   000000000 ? 000000001 ?
opistp()+309         call     opistp_real()        000000031 ? 000000002 ?
                                                   7FFFB5A1E560 ? 000000000 ?
                                                   000000000 ? 000000001 ?
opiodr()+984         call     opistp()             000000031 ? 000000002 ?
                                                   7FFFB5A1E560 ? 000000000 ?
                                                   000000000 ? 000000001 ?
ttcpip()+1012        call     opiodr()             000000031 ? 000000002 ?
                                                   7FFFB5A1E560 ? 000000000 ?
                                                   0059C02A8 ? 000000001 ?
opitsk()+1322        call     ttcpip()             00689E3B0 ? 000000001 ?
                                                   7FFFB5A1E560 ? 000000000 ?
                                                   7FFFB5A1E058 ? 7FFFB5A1E6C8 ?
opiino()+1026        call     opitsk()             000000003 ? 000000000 ?
                                                   7FFFB5A1E560 ? 000000001 ?
                                                   000000000 ? 4E5000B00000000 ?
opiodr()+984         call     opiino()             00000003C ? 000000004 ?
                                                   7FFFB5A1F728 ? 000000001 ?
                                                   000000000 ? 4E5000B00000000 ?
opidrv()+547         call     opiodr()             00000003C ? 000000004 ?
                                                   7FFFB5A1F728 ? 000000000 ?
                                                   0059C0460 ? 4E5000B00000000 ?
sou2o()+114          call     opidrv()             00000003C ? 000000004 ?
                                                   7FFFB5A1F728 ? 000000000 ?
                                                   0059C0460 ? 4E5000B00000000 ?
opimai_real()+163    call     sou2o()              7FFFB5A1F700 ? 00000003C ?
                                                   000000004 ? 7FFFB5A1F728 ?
                                                   0059C0460 ? 4E5000B00000000 ?
main()+116           call     opimai_real()        000000002 ? 7FFFB5A1F790 ?
                                                   000000004 ? 7FFFB5A1F728 ?
                                                   0059C0460 ? 4E5000B00000000 ?
__libc_start_main()  call     main()               000000002 ? 7FFFB5A1F790 ?
+244                                               000000004 ? 7FFFB5A1F728 ?
                                                   0059C0460 ? 4E5000B00000000 ?
_start()+41          call     __libc_start_main()  000723088 ? 000000002 ?
                                                   7FFFB5A1F8E8 ? 000000000 ?
                                                   0059C0460 ? 000000002 ?

二、问题展示形式
ORA-600 [LibraryCacheNotEmptyOnClose] is reported in the alert.log on shutdown
The trace file shows the following call stack trace and will also include a System State:
kglshu kqlnfy kscnfy ksmshu opistp_real opistp opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real main libc_start_main
三、问题原因及其后果
This is a bug in that an ORA-600 error is reported when it is found during shutdown, after database close, that there are still objects in the library cache. It does not indicate any damage or a problem in the system.
Ignore the error as it just indicates that there are some items in the library cache when closing down the instance. The error itself occurs AFTER the database close and dismount stages so only affects the instance shutdown itself. Datafiles have been closed cleanly.
四、影响版本/修复

ORA-00600 [2662]

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-00600 [2662]

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、错误现象(alert日志中)
Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc:
ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], []

二、错误解释
ORA-600 [2662] “Block SCN is ahead of Current SCN”,说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的dependent SCN进行比较,如果当前的SCN小于它,数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数,分别代表不同的含义
ORA-600 [2662] [a] [b] {c} [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg {c} dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
注:897694446<897695488
三、错误原因
1.使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库
2.硬件错误引起数据库没法写控制文件和重做日志文件
3.错误的部分恢复数据库
4.恢复了控制文件但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题

四、解决办法
1、如果SCN相差不多,可以通过多次重起数据库解决(每次加1)
2、通过10015 ADJUST_SCN事件来增进current SCN
1)计算level
1.1) Arg {c}* 4得出一个数值,假设为V_Wrap
1.2) 如果Arg [d]=0,则V_Wrap值为需要的level
Arg [d] < 1073741824,V_Wrap+1为需要的level Arg [d] < 2147483648,V_Wrap+2为需要的level Arg [d] < 3221225472,V_Wrap+3为需要的level 1.3)SCN被增进了1024*1024*1024*level(level*10 billion)
2)执行内部事件
alter session set events ‘10015 trace name adjust_scn level N’;
注:mount状态下执行(open下无效)
alert日志中会出现:
Sat Aug 20 15:41:07 2011
Debugging event used to advance scn to 107374182400

ORA-01244/ORA-01110解决

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-01244/ORA-01110解决

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

rman 恢复过程中出现以下错误

RMAN> recover database;
Starting recover at 20-AUG-11
using channel ORA_DISK_1
starting media recovery
archive log filename=/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/20/2011 03:54:30
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'

错误原因分析
在rman备份后,添加了数据文件,使用的是备份的控制文件进行恢复数据库导致(不能识别新的数据文件)
解决方法
通过sqlplus创建数据文件
SQL> alter database create datafile 2 as ‘/opt/oracle/oradata/xifenfei/xff01.dbf’;
Database altered.
然后继续在rman中执行恢复数据库操作
该情况说明
此中情况只有在oracle 9i中出现;在10g中,rman恢复过程会自动的创建新添加文件,见oracle 10g rman自动创建数据文件

ORA-00205问题处理

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-00205问题处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1、启动数据库异常
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
ORA-00205: error in identifying control file, check alert log for more info
alert.log日志中记录
ALTER DATABASE MOUNT
Mon Jul 18 17:32:58 2011
ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/oradata/xienfei/control01.ctl]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
2、根据提示缺少控制文件,第一步是看看有没有冗余的控制文件,然后修改pfile或者复制控制文件处理,如果没有利用备份控制文件恢复
RMAN> restore controlfile from ‘/tmp/rman_1kmhorc2_1_1’;
Starting restore at 18-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/oradata/xienfei/control01.ctl
Finished restore at 18-JUL-11
3、恢复控制文件后,数据库至于mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
4、添加归档日志到控制文件
RMAN> catalog start with ‘/u01/archive’;
Starting implicit crosscheck backup at 18-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
Crosschecked 29 objects
Crosschecked 12 objects
Finished implicit crosscheck backup at 18-JUL-11
Starting implicit crosscheck copy at 18-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
Crosschecked 1 objects
Finished implicit crosscheck copy at 18-JUL-11
searching for all files in the recovery area
cataloging files…
no files cataloged
searching for all files that match the pattern /u01/archive
List of Files Unknown to the Database
=====================================
…………
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done
List of Cataloged Files
=======================
…………
RMAN> exit
[oracle@node2 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jul 18 17:43:47 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
5、使用备份控制文件恢复
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008’
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008’
6、发现控制文件后又新增数据文件,需要重命名控制文件中的新增数据文件
SQL> select file#,name from v$datafile where file#=8;
FILE# NAME
———- ———————————————-
8 /u01/oracle/dbs/UNNAMED00008
SQL> alter database rename file ‘/u01/oracle/dbs/UNNAMED00008’ to ‘/u01/oradata/xienfei/cfxff01.dbf’;
Database altered.
7、继续进行不完成恢复(利用归档日志)
SQL> recover database using backup controlfile;
ORA-00279: change 1158476 generated at 07/18/2011 16:43:27 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_1_756837539.arc
ORA-00280: change 1158476 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1158594 generated at 07/18/2011 16:44:01 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_2_756837539.arc
ORA-00280: change 1158594 for thread 1 is in sequence #2
ORA-00278: log file ‘/u01/archive/1_1_756837539.arc’ no longer needed for this
recovery
…………
ORA-00308: cannot open archived log ‘/u01/archive/1_7_756837539.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
8、根据提示,应该是归档日志恢复完成,需要利用联机日志继续恢复
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo03.log
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: ‘/u01/oradata/xienfei/redo03.log’
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo01.log
Log applied.
Media recovery complete.
9、resetlogs打开数据库
SQL> alter database open resetlogs;
Database altered.

ORA-01578坏块解决(2)

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-01578坏块解决(2)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

ORA-01578坏块解决(1)续集
如果在坏块之前,有rman备份,可以使用rman的备份来进行恢复,确保数据不会被丢失
1、使用rman进行恢复
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 22:21:13 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> blockrecover datafile 6 block 1477;
Starting blockrecover at 2011-08-14 22:21:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /tmp/0fmk0ii5_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/0fmk0ii5_1_1 tag=TAG20110814T213357
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 2011-08-14 22:21:23
2、检查坏块是否被恢复
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:22:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:22:12
RMAN> exit
Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:17 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file#,block#,blocks from v$database_block_corruption;
no rows selected
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:38 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 1372
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1256690 (0.1256690)
3、验证数据是否正确
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:34:18 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from t_rep;
COUNT(*)
———-
49857
ORA-01578坏块解决(1)中的模拟环境比较,数据恢复正确,坏块问题解决

ORA-01578坏块解决(1)

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-01578坏块解决(1)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、创建测试表
SQL> create table t_rep as
2  select * from all_objects;
Table created.
SQL> select count(*) from  t_rep;
COUNT(*)
———-
49857
二、使用bbed修改数据块
三、错误现象
1、sqlplus窗口
SQL> select count(*) from  t_rep;
select count(*) from  t_rep
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’
2、alert.log文件中
Sun Aug 14 22:01:14 2011
Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x28ef0602
check value in block header: 0x493
computed block checksum: 0x44b9
Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data
Sun Aug 14 22:01:15 2011
Corrupt Block Found
TSN = 6, TSNAME = XFF
RFN = 6, BLK = 1477, RDBA = 25167301
OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =
SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
四、验证是否真的坏块
1、dbv验证
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
Page 1477 is marked corrupt
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x28ef0602
check value in block header: 0x493
computed block checksum: 0x44b9
DBVERIFY – Verification complete
Total Pages Examined         : 2560
Total Pages Processed (Data) : 1371
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1140
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 1256043 (0.1256043)
2、rman验证
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:09:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:09:53
RMAN> exit
Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>  select file#,block#,blocks from v$database_block_corruption;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);
FILE#     BLOCK#     BLOCKS
———- ———- ———-
6       1477          1
五、跳过坏块读取其他数据
SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;
SKIP_COR
——–
ENABLED
SQL> select count(*) from t_rep;
COUNT(*)
———-
49794
说明:数据发生丢失6号文件的1477块中的数据丢失

物化视图on prebuilt table

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:物化视图on prebuilt table

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1、ORA-12059
执行语句:
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
AS
SELECT * FROM SCOTT.emp
错误提示:
ORA-12059: prebuilt table “CHF”.”MV_T2″ does not exist
错误原因:
物化视图对应的表不存在(物化视图需要和表同名,结构相同)
解决方法:
CREATE TABLE mv_t2 AS
SELECT * FROM scott.emp WHERE 1=0;
2、ORA-23413
执行语句:
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on demand
AS
SELECT * FROM SCOTT.emp
错误提示:
ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log
错误原因:
使用fast模式刷新物化视图,需要有物化视图日志
解决方法:
1)创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON scott.emp ;
2)刷新模式改为force,其实实质是采用了complete刷新模式
3、ORA-12058
执行语句(mv log是基于rowid,表无主键)
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
WITH ROWID
AS
SELECT * FROM SCOTT.emp
错误提示:
ORA-12058: materialized view cannot use prebuilt table
错误原因:
fast刷新模式不能基于rowid进行
解决方法:
1)删除mv log,表添加主键,采用基于主键模式重新建mv log和物化视图
DROP MATERIALIZED VIEW mv_t1;
ALTER TABLE mv_t2 ADD PRIMARY KEY (EMPNO);
CREATE MATERIALIZED VIEW LOG ON scott.emp;
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
–WITH ROWID
AS
SELECT * FROM SCOTT.emp;
2)刷新模式改为force,其实实质是采用了complete刷新模式
4、总结
1)在执行创建物化视图之前,需要先创建同名、同结构表
2)如果使用fast模式刷新(疑惑在线重定义可以通过rowid实现,为什么直接通过物化视图就不可以),物化视图同表需要主键,需要物化视图日志
3)通过drop mv,发现同名表还存在,和on prebuilt table本质区别

ORA-01012: not logged on

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-01012: not logged on

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天接到现场反馈,数据库出现ORA-01012: not logged on错误提示,这个问题上次也出现过一次,当时按照网上说的方法,直接重启数据库了,问题解决了,同时也导致因为数据库重启,现场破坏,而alert日志中无任何异常信息,所以不知从何处下手分析。这次我上数据库准备查看时,发现数据库已经正常,监控也显示正常,说明数据库已经恢复正常。从此我推理这个问题应该是外部因素导致,而不是数据库本身的bug,从而决定要找出该问题的原因来。有个重要的因素,该数据库是我几个月前因为undo损坏做过恢复的,查看相关参数,发现processes是默认值150,是不是该值导致的不敢肯定,因为一般process超了会报ORA-00020错误,而这次只有ORA-01012。但是心中还是没有底,总感觉这个的可能性最大,于是想通过试验来证实下自己的想法
1、数据库版本(10g结果相同)
SQL> select * from v$version where rownum=1;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
2、创建pfile,修改processes,重启数据库,查看processes值
SQL> select name,value from v$parameter where name=’processes’;
NAME VALUE
——————– ——————–
processes 30
3、查看系统processes数量
SQL> select count(*) from v$process;
COUNT(*)
———-
13
4、添加数据库连接数(为了增加process数目)
[oracle@localhost ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:11:50 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
SQL> !
5、重复3和4,发现当process数量为29时出现激动人心一幕
5.1)继续访问数据库
[oracle@localhost ~]$ sqlplus system/xifenfei
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:06 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Enter user-name:
5.2)使用其他用户访问
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:49 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
SQL> conn chf/xifenfei
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
[oracle@localhost ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:29:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
现象说明:
1)终于发现了ORA-01012错误,期待了很久。发现只有当sys登录系统,对数据库进行查询或者操作之时才会出现ORA-01012,其他用户只要一登录数据库就会提示ORA-00020错误。
2)当process达到数据库最大限时,sysdba登录系统是也显示不正常
2.1)当使用sqlplus “/ as sysdba”登录时,缺少:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
2.2)当使用conn / as sysdba登录时,提示为:
Connected to an idle instance.

ORA-01031: insufficient privileges

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORA-01031: insufficient privileges

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天接到开发反馈,说数据库动态sql创建表语句无法执行,提示ORA-01031: insufficient privileges
1、数据库版本
SQL> select * from v$version where rownum=1;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
2、创建用户并授权
SQL> create user test identified by xifenfei;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
3、查看权限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST CONNECT NO YES
TEST RESOURCE NO YES
SQL> CONN TEST/XIFENFEI
Connected.
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
—————————————-
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
—————————————-
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
14 rows selected.
注:目前create table权限是通过resource role授予test用户的
4、创建存储过程(Authid Current_User)
SQL> create or replace procedure create_table(t varchar2) Authid Current_User
2 is
3 P_SQL VARCHAR2(300);
4 BEGIN
5 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
6 EXECUTE IMMEDIATE P_SQL;
END; 7
8 /
Procedure created.
5、执行存储过程
SQL> EXEC CREATE_table(‘a’);
PL/SQL procedure successfully completed.
6、重建存储过程(不包括Authid Current_User)
SQL> create or replace procedure create_table(t varchar2)
2 –Authid Current_User
3 is
4 P_SQL VARCHAR2(300);
5 BEGIN
6 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
7 EXECUTE IMMEDIATE P_SQL;
8 END;
9 /
Procedure created.
SQL> EXEC CREATE_table(‘b’);
BEGIN CREATE_table(‘b’); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “TEST.CREATE_TABLE”, line 7
ORA-06512: at line 1
7、授权(create table权限)
SQL> conn / as sysdba
Connected.
SQL> grant create table to test;
Grant succeeded.
SQL> conn test/xifenfei
Connected.
SQL> EXEC CREATE_table(‘c’);
PL/SQL procedure successfully completed.
8、查看结果
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
A TABLE
C TABLE
9、 查询资料
默认情况下,在调用存储过程用户的角色是不起作用的,即在执行存储过程时只有Public权限。所以如果被调用的存储过程中如果有execute immediate ‘create table..’语句,将会引发ORA-01031: insufficient privileges错误。
存储过程分为两种,即DR(Definer’s Rights ) Procedure和IR(Invoker’s Rights ) Procedure。为什么会有两种存储过程呢?其实考虑完下面的问题就清楚了。比如说用户hrch创建了删除表tar_table的存储过程drop_table(),当用户hrch调用时,即删除用户hrch下的表tar_table;如果是另一个用户scott调用呢?是删除用户scott下的tar_table表呢,还是删除用户hrch下的tar_table呢?另外,如果存储过程中包含建表语句,不管是用户hrch还是用户scott调用都会失败,因为Public没有建表权限,除非为Public grant建表权限。
10、动态sql中执行create table解决方案(10g也存在同样问题)
1)加上Authid Current_User
2)直接赋予create table to 该用户
11、匿名块的动态sql不在此限制中
SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> CONN TEST/XIFENFEI
Connected.
SQL> DECLARE
2 P_SQL VARCHAR2(300);
3 BEGIN
4 P_SQL := ‘create table t_t as select * from user_tables where 1=0’;
5 EXECUTE IMMEDIATE P_SQL;
6 END;
7 /
PL/SQL procedure successfully completed.