admin – 第78页 – Database SOS

ORA-00600[KSSADP1]

检查数据库发现ORA-00600[KSSADP1]错误

Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Trace dumping is performing id=[cdmp_20120419211645]
Thu Apr 19 21:16:46 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:47 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []

分析crm1_ora_442896.trc信息

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 2354
Unix process pid: 442896, image: oracle@zwq_crm1 (TNS V1-V3)
*** SESSION ID:(927.39278) 2012-04-19 21:16:45.317
*** 2012-04-19 21:16:45.317
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014
kgerinv+00e8         bl       _ptrgl
kgesinv+0020         bl       kgerinv              9001000A02B56F8 ?
                                                   9001000A02B9450 ?
                                                   FFFFFFFFFFF8430 ? 000000458 ?
                                                   900000000CBAFA4 ?
ksesin+005c          bl       kgesinv              FFFFFFFFFFF88E0 ? 1101FAF78 ?
                                                   900000000C0ECC0 ? 000010000 ?
                                                   000000002 ?
kssadpm_stage+00c4   bl       ksesin               102973C84 ? 000000000 ?
                                                   00000001E ? 000000000 ?
                                                   000000069 ? 00000000C ?
                                                   000000000 ? 000000000 ?
ksqgel+0138          bl       kssadpm_stage        000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kcftis+003c          bl       ksqgel               000000000 ? 4029C61E0 ?
                                                   000000002 ? 0FFFFC16C ?
                                                   102A7977C ? 000000000 ?
                                                   000000003 ? 002A36408 ?
kcfhis+001c          bl       kcftis
krbbcc+0238          bl       kcfhis               11043B590 ?
krbpgc+001c          bl       krbbcc
ksmupg+0074          bl       _ptrgl
ksuded+00b8          bl       ksmupg               102924988 ? 000000020 ?
ksupucg+10ec         bl       ksuded               700000C376F5740 ? 000000000 ?
                                                   000000000 ?
opiodr+0474          bl       ksupucg              100000001 ?
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF90 ?
                                                   442442216B736800 ?
                                                   FFFFFFFFFFFBF00 ? 1102E04BC ?
                                                   1102D7D20 ? 0000006A0 ?
                                                   1102D83C0 ? 0000006A0 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF840 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF840 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
Cursor Dump:
----------------------------------------
Cursor 1 (110360418): CURROW  curiob: 110369b78
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: select nvl(max(cpmid),0) from x$kcccp                                        where cpsta = 2
 child pin: 0, child lock: 700000d9b9c5bb8, parent lock: 700000d088e0fa0
 xscflg: 1100024, parent handle: 70000031d588d88, xscfl2: 4040401
 bhp size: 160/600
----------------------------------------
Cursor 2 (110360468): CURBOUND  curiob: 1103656f0
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1,INSTR(VERSION,'.') - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d21e60930, parent lock: 700000327837ce0
 xscflg: 141024, parent handle: 700000304e2f020, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 3 (1103604b8): CURBOUND  curiob: 1103b6aa8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,1) ,INSTR(VERSION,'.',1,2) -
 INSTR(VERSION,'.',1,1)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d5e382ee8, parent lock: 700000c93581d40
 xscflg: 141024, parent handle: 700000d73daa1c0, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 4 (110360508): CURBOUND  curiob: 1103b66b8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,2) ,INSTR(VERSION,'.',1,3) -
 INSTR(VERSION,'.',1,2)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d16de7978, parent lock: 700000c44059d30
 xscflg: 141024, parent handle: 700000259c4a700, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 5 (110360558): CURBOUND  curiob: 1103b3868
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SYSDATE   FROM SYS.DUAL
 child pin: 0, child lock: 700000d589cea48, parent lock: 70000026b311fb0
 xscflg: 100024, parent handle: 700000d2eaee328, xscfl2: 4600409
 bhp size: 280/632
----------------------------------------
Cursor 6 (1103605a8): CURBOUND  curiob: 1103b3408
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),TO_CHAR(SYSDATE,'MM','NLS_CALENDAR=Gregorian'),
TO_CHAR(SYSDATE,'DD','NLS_CALENDAR=Gregorian') FROM X$DUAL
 child pin: 0, child lock: 70000033f1753c8, parent lock: 700000db8c6dd18
 xscflg: 100024, parent handle: 700000cbc6ad8b0, xscfl2: 4600409
 bhp size: 160/600
End of cursor dump
ksedmp: no current context area
----- Dump of the Fixed PGA -----

找到相关文档Note:262996.1,经过分析,产生错误的原因是由在本版本的数据库中SGA管理中存在的漏洞造成,但此错误没有对数据库的数据造成损坏及性能影响.

处理建议
1．当前版本ORACLE已经不再提供补丁支持,建议升级到高版本解决(有sr中介绍10.2中解决);
2．由于此报错并没有对数据库的数据及性能造成损坏及影响,可以忽此错误。

记录一次ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned解决

Data Guard主库出现如下错误
导致归档日志不同通过LOG_ARCHIVE_DEST_2传输到备库

Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:00:26 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC1]: Error 3113 when pinging standby standby.
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:33:27 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
[oracle@localhost ~]$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup.
//          Also, look in the alert.log file for any errors. Finally, test to
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
提示连接错误

orcl_ora_6756.trc文件内容
这里没有得任何重要的有效信息

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      fcdb1
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6756, image: oracle@fcdb1 (TNS V1-V3)
*** 2012-04-19 19:51:32.033
*** ACTION NAME:(0000045 STARTED16) 2012-04-19 19:51:32.026
*** MODULE NAME:(backup incr datafile) 2012-04-19 19:51:32.026
*** SERVICE NAME:(SYS$USERS) 2012-04-19 19:51:32.026
*** SESSION ID:(1518.294) 2012-04-19 19:51:32.026
*** ACTION NAME:(0000062 STARTED68) 2012-04-19 19:58:40.083
*** MODULE NAME:(backup full datafile) 2012-04-19 19:58:40.083
*** 2012-04-19 19:58:40.083
*** ACTION NAME:(0000068 STARTED16) 2012-04-19 19:58:40.156
*** 2012-04-19 20:18:18.436
*** ACTION NAME:(0000118 STARTED16) 2012-04-19 20:18:18.436
*** MODULE NAME:(backup incr datafile) 2012-04-19 20:18:18.436

查看相关参数

SQL> show parameter archive;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(primary,standby)
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/arch
                                                 VALID_FOR=(ALL_LOGFILES,ALL_RO
                                                 LES) DB_UNIQUE_NAME=primary
log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VAL
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                 Y_ROLE) DB_UNIQUE_NAME=standby
log_archive_dest_state_1             string      ENABLE
log_archive_dest_state_2             string      ENABLE

测试TNS

[oracle@fcdb1 bdump]$ tnsping standby
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-APR-2012 20:47:51
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@fcdb1 bdump]$ sqlplus sys/oracle@standby as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 20:49:05 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

问题原因分析
从log_archive_dest_2 参数上可以看出默认是配置lgwr进程传输日志，但是因为备库没有配置standby redo log，所以使得启动arch传输日志，然后出现该问题，因为在传输过程中出现异常，导致arch不能继续和备库建立连接。

解决方法
查看了很多资料，给出的解决方法都是重启主库或者备库解决，我想既然是ARCH建立连接的失败，那么重启log_archive_dest_state_2参数，让arch进程重启。

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
System altered.
SQL> alter system switch logfile;
System altered.
--alert日志
Thu Apr 19 20:51:12 2012
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Thu Apr 19 20:51:32 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
LNS1 started with pid=35, OS id=7012
Thu Apr 19 20:51:47 2012
Thread 1 advanced to log sequence 2025
  Current log# 2 seq# 2025 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Thu Apr 19 20:51:48 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Apr 19 20:52:02 2012
Thread 1 advanced to log sequence 2026
  Current log# 3 seq# 2026 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Thread 1 cannot allocate new log, sequence 2027

这个时候，查看备库日志也已经传输过去，通过修改log_archive_dest_state_2解决

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

利用scn增量备份实现数据库增量恢复

数据库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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

全备数据库

RMAN> backup as compressed backupset database  format '/u01/oracle/oradata/tmp/ora11g_0_%U';
Starting backup at 18-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-12

创建测试数据库验证恢复标准

SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;
Table dropped.
SQL> create table xifenfei as
  2  select * from dba_objects;
Table created.
SQL> insert into xifenfei
  2  select * from dba_objects;
74534 rows created.
SQL> /
74534 rows created.
SQL> /
74534 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from xifenfei;
  COUNT(*)
----------
    298136
SQL> create table xifenfei01 as
  2  select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create table chf.xifenfei02 as
  2  select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.

异机恢复库

RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';
Starting restore at 19-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/oradata/ora11g/control01.ctl
output file name=/u01/oracle/oradata/ora11g/control02.ctl
Finished restore at 19-APR-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    262.79M    DISK        00:01:08     18-APR-12
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20120418T234958
        Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf
  2       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf
  3       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf
  4       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf
  6       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf
--这里可以发现备份时的scn,增量备份时取这里的最小scn为起点
--为了排除影响,删除所有归档日志记录
RMAN> delete archivelog all;
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> restore database;
Starting restore at 19-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 19-APR-12
[oracle@xifenfei oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;
     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1155510                             787897
         2           1155510                             787897
         3           1155510                             787897
         4           1155510                             787897
         6           1155510                             787897
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1155510
         2          1155510
         3          1155510
         4          1155510
         6          1155510

基于scn增量备份

RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE
2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U' tag 'XIFENFEI';
Starting backup at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
using channel ORA_DISK_1
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-12

增量恢复

RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';
searching for all files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
List of Files Unknown to the Database
=====================================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
RMAN> recover database;
Starting recover at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oracle/oradata/ora11g/system01.dbf
destination for restore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf
destination for restore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf
destination for restore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf
destination for restore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
--这里表现出来的是利用归档日志恢复，实质是基于scn增量备份的备份集恢复
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57
unable to find archived log
archived log thread=1 sequence=58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2012 00:55:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 58 and starting SCN of 1157379
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;
     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1157379                             787897
         2           1157379                             787897
         3           1157379                             787897
         4           1157379                             787897
         6           1157379                             787897
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1157379
         2          1157379
         3          1157379
         4          1157379
         6          1157379
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from chf.xifenfei;
  COUNT(*)
----------
    298136
SQL> select TABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';
TABLE_NAME
------------------------------------------------------------
XIFENFEI02
XIFENFEI01
XIFENFEI

使用rman基于scn实现数据库增量恢复是在dg中修复gap的时候常见的方法,其实该方法也可以使用常规的增量恢复,通过人工控制,实现数据库的某种特殊的业务需求(特殊的数据迁移).处理思路主要是获得备库的数据文件最小scn(这个scn可能是通过全备恢复或者增量恢复产生),然后基于该SCN实现数据库增量备份,然后利用该备份进行增量恢复.

DB2日志参数介绍和修改归档模式

cfg关于log参数

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

LOGPRIMARY:主日志组日志文件个数(首次连接或激活时直接分配)
LOGSECOND:辅助日志组日志文件个数(主日志文件写满时按需分配)
LOGFILSIZ:每个日志文件页数,每页大小为4K
LOGARCHMETH1/LOGARCHMETH2:OFF表示循环模式,其他值表示归档模式(disk:/xifenfei/archive)
NEWLOGPATH:修改新的日志路径
LOGBUFSZ:日志缓冲区大小
MIRRORLOGPATH:日志镜像路径
LOGRETAIN:归档日志保留在日志文件中(不推荐该做法)
USEREXIT:归档日志通过用户出口程序管理(8.2后不推荐该做法)

当前日志位置

[db2inst1@xifenfei ~]$ ls -l /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
total 53404
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000000.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000001.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000002.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000003.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000004.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000006.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000007.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000008.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000009.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000010.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000011.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000012.LOG

修改cfg参数(改为归档模式)

[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGPRIMARY 6
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@xifenfei ~]$ mkdir -p  xff/redolog
[db2inst1@xifenfei ~]$ mkdir -p  xff/archivelog
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using NEWLOGPATH /home/db2inst1/xff/redolog
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGARCHMETH1 disk:/home/db2inst1/xff/archivelog
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

查看修改后参数

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO   --没有生效
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) = /home/db2inst1/xff/redolog/NODE0000/
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

参数生效情况

[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 0
[db2inst1@xifenfei ~]$ db2stop
04/06/2012 05:33:24     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ db2start
04/06/2012 05:33:34     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 0
[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1116N  A connection to or activation of database "XIFENFEI" cannot be made
because of BACKUP PENDING.  SQLSTATE=57019
[db2inst1@xifenfei ~]$ db2 backup db xff to /tmp
Backup successful. The timestamp for this backup image is : 20120406053431
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 24652
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000000.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000001.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000002.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000003.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000004.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1     512 Apr  6 05:33 SQLLPATH.TAG
[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/xff/redolog/NODE0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000005.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

修改备份模式参数需要重启并且备份库后生效

测试归档日志

[db2inst1@xifenfei ~]$ db2 list history archive log  all for xff
                    List History File for xff
Number of matching file entries = 0
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from t_xifenfei"
DB20000I  The SQL command completed successfully.
……N次……
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei"
1
-----------
      94208
  1 record(s) selected.
[db2inst1@xifenfei ~]$ db2 list history archive log  all for xff
                    List History File for xff
Number of matching file entries = 11
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20120406054854      1    D  S0000000.LOG C0000000
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20120406054854
   End Time: 20120406055029
     Status: A
 ----------------------------------------------------------------------------
  EID: 2 Location: /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000/S0000000.LOG
……省略……
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20120406055041      P    D  S0000010.LOG C0000000
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20120406055041
   End Time:
     Status: A
 ----------------------------------------------------------------------------
  EID: 14 Location: /home/db2inst1/xff/redolog/NODE0000/S0000010.LOG
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000
total 41080
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000000.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000001.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000002.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000003.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000004.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000005.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000006.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000007.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000008.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000009.LOG
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000
total 32868
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000006.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000007.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000008.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000009.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000010.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000011.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000012.LOG
-rw-------  1 db2inst1 db2iadm1     512 Apr  6 05:33 SQLLPATH.TAG

手工修复ASM DISK HEADER 异常

今天有网友对asm中的磁盘做了fdisk操作,导致asm disk异常,通过手工修复ASM DISK HEADER 解决该问题,这里通过实验重现,提醒大家操作asm中的硬盘分区需要慎重,平时对ASM DISK HEADER 做好备份
初始化信息

SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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>  show parameter instance_name;
NAME                                 TYPE       VALUE
------------------------------------ ---------- ----------------
instance_name                        string     +ASM1
SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;
GROUP_NUMBER DISK_NUMBER PATH                           HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
           1           1 /dev/oracleasm/disks/VOL2      MEMBER
           1           0 /dev/oracleasm/disks/VOL1      MEMBER
           2           1 /dev/oracleasm/disks/VOL4      MEMBER
           2           0 /dev/oracleasm/disks/VOL3      MEMBER
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 17
brw-r----- 1 root disk     8,  17 Apr 17 11:37 sdb1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 18
brw-r----- 1 root disk     8,  18 Apr 17 11:37 sdb2

备份ASM DISK HEADER

[root@rac1 backup_asmheader]# dd if=/dev/sdb1 of=vol3header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000143581 seconds, 28.5 MB/s
[root@rac1 backup_asmheader]# dd if=/dev/sdb2 of=vol4header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000147727 seconds, 27.7 MB/s

破坏ASM DISK HEADER

[root@rac1 ~]# dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 4.4421e-05 seconds, 92.2 MB/s
[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL3
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
B4C83200 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;
GROUP_NUMBER DISK_NUMBER PATH                           HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
           1           1 /dev/oracleasm/disks/VOL2      MEMBER
           1           0 /dev/oracleasm/disks/VOL1      MEMBER
           2           1 /dev/oracleasm/disks/VOL4      MEMBER
           2           0 /dev/oracleasm/disks/VOL3      CANDIDATE

remount diskgroup

SQL> alter diskgroup  xifenfei dismount;
Diskgroup altered.
SQL> alter diskgroup xifenfei mount;
alter diskgroup xifenfei mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

查看同一DISKGROUP中其他磁盘kfed

[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL4
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483649 ; 0x008: disk=1
kfbh.check:                   349717291 ; 0x00c: 0x14d8432b
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:     ORCLDISKVOL4 ; 0x000: length=12
kfdhdb.driver.reserved[0]:    877416278 ; 0x008: 0x344c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        1 ; 0x024: 0x0001
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0001 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0001 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    3788 ; 0x0c4: 0x00000ecc
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0x00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[5]:                   0 ; 0x110: 0x00000000
kfdhdb.ub4spare[6]:                   0 ; 0x114: 0x00000000
kfdhdb.ub4spare[7]:                   0 ; 0x118: 0x00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[9]:                   0 ; 0x120: 0x00000000
kfdhdb.ub4spare[10]:                  0 ; 0x124: 0x00000000
kfdhdb.ub4spare[11]:                  0 ; 0x128: 0x00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[13]:                  0 ; 0x130: 0x00000000
kfdhdb.ub4spare[14]:                  0 ; 0x134: 0x00000000
kfdhdb.ub4spare[15]:                  0 ; 0x138: 0x00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000
kfdhdb.ub4spare[18]:                  0 ; 0x144: 0x00000000
kfdhdb.ub4spare[19]:                  0 ; 0x148: 0x00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[21]:                  0 ; 0x150: 0x00000000
kfdhdb.ub4spare[22]:                  0 ; 0x154: 0x00000000
kfdhdb.ub4spare[23]:                  0 ; 0x158: 0x00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[25]:                  0 ; 0x160: 0x00000000
kfdhdb.ub4spare[26]:                  0 ; 0x164: 0x00000000
kfdhdb.ub4spare[27]:                  0 ; 0x168: 0x00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[29]:                  0 ; 0x170: 0x00000000
kfdhdb.ub4spare[30]:                  0 ; 0x174: 0x00000000
kfdhdb.ub4spare[31]:                  0 ; 0x178: 0x00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[33]:                  0 ; 0x180: 0x00000000
kfdhdb.ub4spare[34]:                  0 ; 0x184: 0x00000000
kfdhdb.ub4spare[35]:                  0 ; 0x188: 0x00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[37]:                  0 ; 0x190: 0x00000000
kfdhdb.ub4spare[38]:                  0 ; 0x194: 0x00000000
kfdhdb.ub4spare[39]:                  0 ; 0x198: 0x00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000

通过VOL4 kfed修改出VOL3 kfed

[grid@rac1 ~]$ cat vol3.txt
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
*kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                   332940500 ; 0x00c: 0x13d844d4
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
*kfdhdb.driver.provstr:     ORCLDISKVOL3 ; 0x000: length=12
*kfdhdb.driver.reserved[0]:    860639062 ; 0x008: 0x334c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
*kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
*kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
*kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
*kfdhdb.dsksize:                    2353 ; 0x0c4: 0x00000931
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
*kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000

后续处理

--导入kfed
[grid@rac1 ~]$ kfed merge /dev/oracleasm/disks/VOL3 text=vol3.txt
--MOUNT diskgroup
SQL> alter diskgroup xifenfei mount;
Diskgroup altered.

如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

ASM DISK HEADER 备份与恢复

最近遇到几次ASM DATA HEADER出问题导致DISK GROUP 不能正常的MOUNT,是的数据库不能正常工作,从来带来了无穷的麻烦,这个时候心想,如果我做了ASM DATA HEADER的备份该多好啊,可惜世上没有后悔药,建议大家检查下自己的ASM库,ASM HEADER是否已经做了备份,如果没有请及时处理下.这里试验提供了dd和kfed备份和恢复ASM HEADER
查询ASM相关信息

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                           STATE        TOTAL_MB    FREE_MB
------------ ------------------------------ ---------- ---------- ----------
           1 DATA                           MOUNTED          2046       1314
           2 XIFENFEI                       MOUNTED          6141       4378
SQL> select group_number,DISK_NUMBER,PATH,STATE from v$asm_disk;
GROUP_NUMBER DISK_NUMBER PATH                           STATE
------------ ----------- ------------------------------ ----------------
           1           1 /dev/oracleasm/disks/VOL2      NORMAL
           1           0 /dev/oracleasm/disks/VOL1      NORMAL
           2           1 /dev/oracleasm/disks/VOL4      NORMAL
           2           0 /dev/oracleasm/disks/VOL3      NORMAL

找出ASM DISK对应磁盘分区

[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL1
Disk "VOL1" is a valid ASM disk on device [8,33]
[grid@rac1 ~]$ ll /dev |grep 8|grep 33
brw-r----- 1 root disk     8,  33 Apr 17 11:37 sdc1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL2
Disk "VOL2" is a valid ASM disk on device [8,34]
[grid@rac1 ~]$ ll /dev |grep 8|grep 34
brw-r----- 1 root disk     8,  34 Apr 17 11:37 sdc2
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 17
brw-r----- 1 root disk     8,  17 Apr 17 11:37 sdb1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 18
brw-r----- 1 root disk     8,  18 Apr 17 11:37 sdb2

备份ASM HEADER信息(备份XIFENFEI磁盘组)

--dd方式备份
[root@rac1 backup_asmheader]# dd if=/dev/sdb1 of=vol3header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000143581 seconds, 28.5 MB/s
[root@rac1 backup_asmheader]# dd if=/dev/sdb2 of=vol4header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000147727 seconds, 27.7 MB/s
--kfed方式备份
[grid@rac1 backup_asmheader]$ kfed read /dev/oracleasm/disks/VOL3 text=vol3header.txt
[grid@rac1 backup_asmheader]$ more vol3header.txt
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                   198826195 ; 0x00c: 0x0bd9d8d3
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:     ORCLDISKVOL3 ; 0x000: length=12
kfdhdb.driver.reserved[0]:    860639062 ; 0x008: 0x334c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969259 ; 0x0b0: HOUR=0xb DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           2707277824 ; 0x0b4: USEC=0x0 MSEC=0x372 SECS=0x15 MINS=0x28
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    2353 ; 0x0c4: 0x00000931
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0x00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[5]:                   0 ; 0x110: 0x00000000
kfdhdb.ub4spare[6]:                   0 ; 0x114: 0x00000000
kfdhdb.ub4spare[7]:                   0 ; 0x118: 0x00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[9]:                   0 ; 0x120: 0x00000000
kfdhdb.ub4spare[10]:                  0 ; 0x124: 0x00000000
kfdhdb.ub4spare[11]:                  0 ; 0x128: 0x00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[13]:                  0 ; 0x130: 0x00000000
kfdhdb.ub4spare[14]:                  0 ; 0x134: 0x00000000
kfdhdb.ub4spare[15]:                  0 ; 0x138: 0x00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000
kfdhdb.ub4spare[18]:                  0 ; 0x144: 0x00000000
kfdhdb.ub4spare[19]:                  0 ; 0x148: 0x00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[21]:                  0 ; 0x150: 0x00000000
kfdhdb.ub4spare[22]:                  0 ; 0x154: 0x00000000
kfdhdb.ub4spare[23]:                  0 ; 0x158: 0x00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[25]:                  0 ; 0x160: 0x00000000
kfdhdb.ub4spare[26]:                  0 ; 0x164: 0x00000000
kfdhdb.ub4spare[27]:                  0 ; 0x168: 0x00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[29]:                  0 ; 0x170: 0x00000000
kfdhdb.ub4spare[30]:                  0 ; 0x174: 0x00000000
kfdhdb.ub4spare[31]:                  0 ; 0x178: 0x00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[33]:                  0 ; 0x180: 0x00000000
kfdhdb.ub4spare[34]:                  0 ; 0x184: 0x00000000
kfdhdb.ub4spare[35]:                  0 ; 0x188: 0x00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[37]:                  0 ; 0x190: 0x00000000
kfdhdb.ub4spare[38]:                  0 ; 0x194: 0x00000000
kfdhdb.ub4spare[39]:                  0 ; 0x198: 0x00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000
[grid@rac1 backup_asmheader]$ kfed read /dev/oracleasm/disks/VOL4 text=vol4header.txt

破坏VOL3 ASM磁盘

[root@rac1 backup_asmheader]# dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.00165264 seconds, 2.5 MB/s
[grid@rac1 backup_asmheader]$ kfed read /dev/oracleasm/disks/VOL3
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
B4BFE200 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

启动ASM

[grid@rac1 backup_asmheader]$ sqlplus / as sysoper
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 17 12:29:42 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

恢复ASM DATE HEADER

--dd 恢复
[root@rac1 backup_asmheader]# dd if=vol3header.dd of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.00197913 seconds, 2.1 MB/s
--kfed 恢复
[grid@rac1 backup_asmheader]$ kfed merge /dev/oracleasm/disks/VOL3 text=vol3header.txt

MOUNT DISKGROUP

SQL> alter diskgroup xifenfei mount;
Diskgroup altered.

找出调用DBLINK的SESSION信息

怎么找出通过dblink访问的用户信息?这个问题困惑了很久,今天在朋友的帮助下,终于通过基表实现了这个功能,记录下来

SELECT /*+ ORDERED */
 S.KSUSEMNM "O_HOSTNAME",S.KSUSEPID "O_SPID",--操作dblink用户信息
 G.K2GTITID_ORA "O_TXID",
 S.INDX "S_SID",S.KSUSESER "S_SERIAL#",--dblink session信息
 DECODE(BITAND(KSUSEIDL, 11),
               1,
               'ACTIVE',
               0,
               DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
               2,
               'SNIPED',
               3,
               'SNIPED',
               'KILLED') "S_STATUS",
                S.KSUUDNAM "DBLINK_USER"
  FROM SYS.X$K2GTE G, SYS.X$KTCXB T, SYS.X$KSUSE S
 WHERE G.K2GTDXCB = T.KTCXBXBA
   AND G.K2GTDSES = T.KTCXBSES
   AND S.ADDR = G.K2GTDSES;

查询结果如下

O_HOSTNAME             O_SPID       O_TXID                      S_SID  S_SERIAL#  S_STATUS   DBLINK_USER
----------------------- ---------- -----------------------  ---------- ---------- --------  ------------
localhost.localdomain  2571         MCRM.757120d4.8.31.21425     5462         20  INACTIVE   TEST1
localhost.localdomain  1021         MCRM.757120d4.6.17.21298     5467        664  INACTIVE   TEST
localhost.localdomain  1385         MCRM.757120d4.10.2.16138     5473        155  INACTIVE   TEST

如果需要获取更加信息的信息,可以通过结合两端的v$session和v$process视图获得

ORA-01075: you are currently logged on

rm删除文件后alert中出现错误

Mon Apr 16 21:36:59 2012
Errors in file /home/oracle/oracle/admin/XGS/bdump/xgs_j000_1349.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

数据库进程还在运行

oracle     779     1  0 21:21 ?        00:00:01 ora_pmon_XGS
oracle     781     1  0 21:21 ?        00:00:10 ora_psp0_XGS
oracle     783     1  0 21:21 ?        00:00:00 ora_mman_XGS
oracle     785     1  0 21:21 ?        00:00:00 ora_dbw0_XGS
oracle     787     1  0 21:21 ?        00:00:00 ora_lgwr_XGS
oracle     789     1  0 21:21 ?        00:00:00 ora_ckpt_XGS
oracle     791     1  0 21:21 ?        00:00:00 ora_smon_XGS
oracle     793     1  0 21:21 ?        00:00:00 ora_reco_XGS
oracle     795     1  0 21:21 ?        00:00:00 ora_cjq0_XGS
oracle     797     1  0 21:21 ?        00:00:01 ora_mmon_XGS
oracle     799     1  0 21:21 ?        00:00:00 ora_mmnl_XGS
oracle     801     1  0 21:21 ?        00:00:00 ora_d000_XGS
oracle     803     1  0 21:21 ?        00:00:00 ora_s000_XGS

尝试登陆数据库

[oracle@dbtest ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 16 21:40:06 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-01075: you are currently logged on
Enter user-name: sys
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oracle/oradata/XGS/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

问题原因

Internal triggers are trying to fire but one or more datafiles for the SYSAUX tablespace is offline,
this is preventing the database from allowing new connections.
NOTE: At this point, you cannot connect to verify the status in V$DATAFILE,
but you may find an indication of the offline datafile(s) in the alert.log file.
For example:
In one case, a media problem occurred which made disks unavailable.
This caused several files to be taken offline automatically including a SYSAUX datafile.

解决方法
kill进程,重启数据库到mount状态,然后根据特定情况恢复数据库或者online相关文件

DB2数据迁移之db2lock/db2move

1.模拟带有identity表

[db2inst1@xifenfei ~]$ db2 "create table t_xff(xid smallint not null generated always as identity
> (start with 1,increment by 1),x_name varchar(200)) in ts_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from t_ff"
SQL0204N  "DB2INST1.T_FF" is an undefined name.  SQLSTATE=42704
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"
XID    X_NAME
------ -------------
     1 www.xifenfei.com
     2 XIFENFEI
     3 xifenfei
  3 record(s) selected.

2.导出表结构

[db2inst1@xifenfei ~]$ mkdir move_s
[db2inst1@xifenfei ~]$ cd move_s/
[db2inst1@xifenfei move_s]$ db2look -d sample -e -l -o db2_sample.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- Output is sent to file: db2_sample.ddl

3.导出数据

[db2inst1@xifenfei move_s]$ db2move sample export
Application code page not determined, using ANSI codepage 1208
*****  DB2MOVE  *****
Action:  EXPORT
Start time:  Thu Apr  5 20:21:28 2012
Connecting to database SAMPLE ... successful!  Server : DB2 Common Server V9.5.9
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!
EXPORT:     18 rows from table "DB2INST1"."ACT"
EXPORT:      5 rows from table "DB2INST1"."CL_SCHED"
EXPORT:     14 rows from table "DB2INST1"."DEPARTMENT"
EXPORT:     42 rows from table "DB2INST1"."EMPLOYEE"
EXPORT:  10000 rows from table "DB2INST1"."EMPMDC"
EXPORT:     73 rows from table "DB2INST1"."EMPPROJACT"
EXPORT:      8 rows from table "DB2INST1"."EMP_PHOTO"
EXPORT:      8 rows from table "DB2INST1"."EMP_RESUME"
EXPORT:    145 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT:      0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT:      3 rows from table "DB2INST1"."IN_TRAY"
EXPORT:      8 rows from table "DB2INST1"."ORG"
EXPORT:      5 rows from table "SYSTOOLS"."POLICY"
EXPORT:     65 rows from table "DB2INST1"."PROJACT"
EXPORT:     20 rows from table "DB2INST1"."PROJECT"
EXPORT:     41 rows from table "DB2INST1"."SALES"
EXPORT:     35 rows from table "DB2INST1"."STAFF"
EXPORT:     35 rows from table "DB2INST1"."STAFFG"
EXPORT:      3 rows from table "DB2INST1"."T_XFF"
Disconnecting from database ... successful!
End time:  Thu Apr  5 20:21:32 2012

4.目标端创建数据库

C:\Windows\system32>db2 "create db db_XFF pagesize 8 k"
DB20000I  CREATE DATABASE命令成功完成。

5.目标端创建对象

C:\Windows\system32>DB2 -tvf D:\move_s\db2_sample.ddl -l d:\xifenfei.log
--检查xifenfei.log文件,发现错误,手工修复

6.导入数据文件

D:\move_s>db2move db_xff load
*****  DB2MOVE  *****
Action:  LOAD
Start time:  Sun Apr 15 23:00:17 2012
Connecting to database DB_XFF ... successful!  Server : DB2 Common Server V9.5.0
Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2COMMON.BND ... successful!
Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2MOVE.BND ... successful!
* LOAD:  table "DB2INST1"."ACT"
  -Rows read:         18
  -Loaded:            18
  -Rejected:           0
  -Deleted:            0
  -Committed:         18
* LOAD:  table "DB2INST1"."CL_SCHED"
  -Rows read:          5
  -Loaded:             5
  -Rejected:           0
  -Deleted:            0
  -Committed:          5
--中间很多记录省略
--发现identity表导入失败，需要手工处理
* LOAD:  table "DB2INST1"."T_XFF"
***  WARNING 3107.  Check message file tab19.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  消息文件中至少有一条警告消息。
  -Rows read:          3
  -Loaded:             0
  -Rejected:           3
  -Deleted:            0
  -Committed:          3
Disconnecting from database ... successful!
End time:  Sun Apr 15 23:00:26 2012

7.重新导入identity表

D:\move_s>db2 connect to db_xff
   数据库连接信息
 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = XIFENFEI
 本地数据库别名       = DB_XFF
D:\move_s>DB2 "load from tab19.ixf of ixf modified by identityoverride insert into db2inst1.t_xff"
SQL3501W  由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。
SQL3551W  表至少包含实用程序将覆盖的一个 GENERATED ALWAYS 列。
SQL3109N  实用程序正在开始从文件 "D:\move_s\tab19.ixf" 装入数据。
SQL3500W  在时间 "2012-04-15 23:06:52.393775"，实用程序在开始 "LOAD"。
SQL3150N  PC/IXF 文件中的 H 记录具有产品 "DB2    02.00"，日期 "20120405"和时间 "202132"。
SQL3153N  PC/IXF 文件中的 T 记录具有名称 "tab19.ixf"，限定符 "" 和源 ""。
SQL3519W  开始装入一致点。输入记录数 = "0"。
SQL3520W  “装入一致点”成功。
SQL3110N  实用程序已完成处理。从输入文件读了 "3" 行。
SQL3519W  开始装入一致点。输入记录数 = "3"。
SQL3520W  “装入一致点”成功。
SQL3515W  在时间 "2012-04-15 23:06:52.451619"，实用程序已经完成了 "LOAD"。
读取行数         = 3
跳过行数         = 0
装入行数         = 3
拒绝行数         = 0
删除行数         = 0
落实行数         = 3
D:\move_s>db2 "select * from db2inst1.t_xff"
XID    X_NAME
------ ------------------------------
     1 www.xifenfei.com
     2 XIFENFEI
     3 xifenfei
  3 条记录已选择。

DB2数据迁移之load

一.load原理性知识
1.为什么要使用LOAD
load不需要写日志(或很少日志),不做检查约束和参照完整性约束,不触发Trigger,锁的时间比较短,因此特别适合大数据量的导入.

2.load过程分为4个阶段
load/build/delete/index copy.
load阶段是将源文件parser成物理数据存储的格式,直接装入到页中,而不通过db2引擎,load阶段会检查表定义,违背定义的数据不会装入到表中.
build阶段建议索引（如果装入表有索引的话）,会检查唯一性约束,违背了唯一性的数据会在delete阶段删除.
index copy阶段将index数据从指定的临时表空间拷贝到初始的表空间里.
index copy只适应于allow read access场景.load的4个阶段会记录在messages文件里.

3.load的offline和online
缺省情况下,load过程不允许其他应用访问表,即allow no access,或叫offline load（离线加载）.Allow read access,或叫online load(在线加载）,只有在load …insert into的时候才允许使用,其他应用读到的数据是加载前的数据,load … replace into会将数据先删除,再load,只能是离线加载.

4.load表的状态
load可能出现的几种状态,某一时刻可能会同时处于几种状态.只有当表是normal状态时,表才能进行正常的增删改查操作.
normal: 正常状态
set integrity pending: 如果目标表有check约束或reference约束,那么Load后此表处于set integrity pending,表明表有约束还未检查,稍后解释.
load in progress:load正在数据加载过程中.
load pending:数据提交前出现了故障,需要通过load..terminate,load..replace或load..restart解除暂挂状态
read access only:目标表数据是可以读的,当load时指定了allow read access,那表就会处于read access only状态
unavailable:表可能被删除了或从backup中恢复了.
unknown:通过load..query命令无法得知表的状态.

二.load试验测试
需要导入数据

[db2inst2@xifenfei ~]$ more /tmp/xifenfei.data
10,"MANAGE","MANAGE/ADVISE"
20,"ECOST ","ESTIMATE COST"
30,,"DEFINE SPECS"
40,"LEADPR","LEAD PROGRAM/DESIGN"
50,"SPECS ",
60,"LOGIC ","DESCRIBE LOGIC"
70,"CODE  ","CODE PROGRAMS"
80,"TEST  ","TEST PROGRAMS"
90,"ADMQS ","ADM QUERY SYSTEM"
100,"TEACH ","TEACH CLASSES"
110,"COURSE","DEVELOP COURSES"
60,"STAFF ","PERS AND STAFFING"
130,"OPERAT","OPER COMPUTER SYS"
140,"MAINT ","MAINT SOFTWARE SYS"
150,"ADMSYS","ADM OPERATING SYS"
160,"ADMDB ","ADM DATA BASES"
170,"ADMDC ","ADM DATA COMM"
80,"DOC   ","DOCUMENT"

一共18条记录

创建目标表

[db2inst2@xifenfei ~]$ db2 "CREATE TABLE XIFENFEI_LOAD(ACTNO SMALLINT NOT NULL primary key,
> ACTKWD CHAR(6) NOT NULL,ACTDESC VARCHAR(20) NOT NULL)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
XIFENFEI_LOAD                   DB2INST2        T     2012-04-05-18.53.42.296503
  1 record(s) selected.
[db2inst2@xifenfei ~]$ db2 describe table xifenfei_load
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No
ACTKWD                          SYSIBM    CHARACTER                    6     0 No
ACTDESC                         SYSIBM    VARCHAR                     20     0 No
  3 record(s) selected.

创建异常表

[db2inst2@xifenfei ~]$ db2 "create table xifenfei_exp like xifenfei_load"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 "alter table xifenfei_exp add column ts timestamp
> add column msg clob(32k)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 describe table xifenfei_exp
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No
ACTKWD                          SYSIBM    CHARACTER                    6     0 No
ACTDESC                         SYSIBM    VARCHAR                     20     0 No
TS                              SYSIBM    TIMESTAMP                   10     0 Yes
MSG                             SYSIBM    CLOB                     32768     0 Yes
  5 record(s) selected.

load导入数据

[db2inst2@xifenfei ~]$ db2 "load from /tmp/xifenfei.data of del modified by dumpfile=/tmp/xifenfei.dmp
messages xifenfei.msg insert into xifenfei_load for exception xifenfei_exp"
Number of rows read         = 18
Number of rows skipped      = 0
Number of rows loaded       = 16
Number of rows rejected     = 2
Number of rows deleted      = 2
Number of rows committed    = 18
SQL3107W  There is at least one warning message in the message file.

提示一共18条记录,成功了14条,拒绝2条,删除2条

查看表中记录

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_load"
ACTNO  ACTKWD ACTDESC
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE
    20 ECOST  ESTIMATE COST
    40 LEADPR LEAD PROGRAM/DESIGN
    60 LOGIC  DESCRIBE LOGIC
    70 CODE   CODE PROGRAMS
    80 TEST   TEST PROGRAMS
    90 ADMQS  ADM QUERY SYSTEM
   100 TEACH  TEACH CLASSES
   110 COURSE DEVELOP COURSES
   130 OPERAT OPER COMPUTER SYS
   140 MAINT  MAINT SOFTWARE SYS
   150 ADMSYS ADM OPERATING SYS
   160 ADMDB  ADM DATA BASES
   170 ADMDC  ADM DATA COMM
  14 record(s) selected.

果真14条记录,和文件相比缺少4条记录

查看dump file

[db2inst2@xifenfei ~]$ more /tmp/xienfei.dmp.load.000
30,,"DEFINE SPECS"
50,"SPECS ",

发现两条违背表定义记录,导入过程中,直接被拒绝的两条

查看异常表

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_exp"
ACTNO  ACTKWD ACTDESC              TS                         MSG
------ ------ --------------------
    60 STAFF  PERS AND STAFFING    2012-04-05-19.02.19.984440 00001I0000500001
    80 DOC    DOCUMENT             2012-04-05-19.02.19.984440 00001I0000500001

发现两条违背唯一性约束记录,构建index的过程中删除

作者：admin