分类目录归档：数据库

dul 10支持oracle 11g r2

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

以前一直以为dul对应的版本只能恢复最高的数据库版本一致,今天测试发现dul 10可以恢复11g最新版的数据库.
模拟环境

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> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') "WWW.XIFENFEI.COM" FROM DUAL;

WWW.XIFENFEI.COM
-------------------
2012-08-04 12:49:29

SQL> create table t_xifenfei 
  2  as
  3  select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     74491

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

dul 参数配置

[oracle@xifenfei dul]$ more init.dul 
osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=0
osd_word_size = 32
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
control_file = control11.dul
db_block_size=8192
export_mode=false
compatible=11
BUFFER=10000000
LDR_ENCLOSE_CHAR=|

[oracle@xifenfei dul]$ more control11.dul 
         0          1 /u01/oracle/oradata/ora11g/system01.dbf
         1          2 /u01/oracle/oradata/ora11g/sysaux01.dbf
         2          3 /u01/oracle/oradata/ora11g/undotbs01.dbf
         4          4 /u01/oracle/oradata/ora11g/users01.dbf

dul恢复11g数据库

[oracle@xifenfei dul]$ ./dul10

Data UnLoader: 10.2.0.5.13 - Internal Only - on Sat Aug  4 00:36:15 2012
with 64-bit io functions

Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Found db_id = 4185048347
Found db_name = ORA11G
DUL>  bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 11
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   74493 rows unloaded
. unloading table                      TAB$    2858 rows unloaded
. unloading table                      COL$   93503 rows unloaded
. unloading table                     USER$      88 rows unloaded
Reading USER.dat 88 entries loaded
Reading OBJ.dat 74493 entries loaded and sorted 74493 entries
Reading TAB.dat 2858 entries loaded
Reading COL.dat 93503 entries loaded and sorted 93503 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 11
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 568, file 1 block 3872
 INDPART$: segobjno 573, file 1 block 3912
 TABCOMPART$: segobjno 590, file 1 block 4056
 INDCOMPART$: segobjno 595, file 1 block 4096
 TABSUBPART$: segobjno 580, file 1 block 3976
 INDSUBPART$: segobjno 585, file 1 block 4016
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 495, tabno 1, file 1  block 3344
 COLLECTION$: segobjno 495, tabno 2, file 1  block 3344
 ATTRIBUTE$: segobjno 495, tabno 3, file 1  block 3344
 LOBFRAG$: segobjno 601, file 1 block 4144
 LOBCOMPPART$: segobjno 604, file 1 block 4168
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 98, file 1 block 800
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   74493 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2858 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
   93503 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      88 rows unloaded
. unloading table                  TABPART$      90 rows unloaded
. unloading table                  INDPART$     106 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$      32 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    5092 rows unloaded
. unloading table                     ICOL$    7518 rows unloaded
. unloading table                      LOB$    1251 rows unloaded
. unloading table                  COLTYPE$    2967 rows unloaded
. unloading table                     TYPE$    2872 rows unloaded
. unloading table               COLLECTION$     985 rows unloaded
. unloading table                ATTRIBUTE$   11127 rows unloaded
. unloading table                  LOBFRAG$       1 row  unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      21 rows unloaded
. unloading table                       TS$       6 rows unloaded
. unloading table                    PROPS$      36 rows unloaded
Reading USER.dat 88 entries loaded
Reading OBJ.dat 74493 entries loaded and sorted 74493 entries
Reading TAB.dat 2858 entries loaded
Reading COL.dat 93503 entries loaded and sorted 93503 entries
Reading TABPART.dat 90 entries loaded and sorted 90 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 106 entries loaded and sorted 106 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 5092 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
 1251 entries loaded
Reading ICOL.dat 7518 entries loaded
Reading COLTYPE.dat 2967 entries loaded
Reading TYPE.dat 2872 entries loaded
Reading ATTRIBUTE.dat 11127 entries loaded
Reading COLLECTION.dat 985 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
DUL> desc chf.t_xifenfei;
Table CHF.T_XIFENFEI
obj#= 75562, dataobj#= 75562, ts#= 4, file#= 4, block#=170
      tab#= 0, segcols= 15, clucols= 0
Column information:
icol# 01 segcol# 01        OWNER len   30 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 02 segcol# 02  OBJECT_NAME len  128 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 03 segcol# 03 SUBOBJECT_NAME len   30 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 04 segcol# 04    OBJECT_ID len   22 type  2 NUMBER(0,-127)
icol# 05 segcol# 05 DATA_OBJECT_ID len   22 type  2 NUMBER(0,-127)
icol# 06 segcol# 06  OBJECT_TYPE len   19 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 07 segcol# 07      CREATED len    7 type 12 DATE
icol# 08 segcol# 08 LAST_DDL_TIME len    7 type 12 DATE
icol# 09 segcol# 09    TIMESTAMP len   19 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 10 segcol# 10       STATUS len    7 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 11 segcol# 11    TEMPORARY len    1 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 12 segcol# 12    GENERATED len    1 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 13 segcol# 13    SECONDARY len    1 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 14 segcol# 14    NAMESPACE len   22 type  2 NUMBER(0,-127)
icol# 15 segcol# 15 EDITION_NAME len   30 type  1 VARCHAR2 cs 852(ZHS16GBK)
DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI   74491 rows unloaded

通过一些列的dul 10测试,发现dul 10功能确实较9强大了很多.支持asm,支持11g,支持字符串换行+lob类型

发表在非常规恢复 | 标签为 dul, dul 11g | 6 条评论

kill oracle session相关描述

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

近期看到不少朋友都对kill session相关的参数不太熟悉,下面是摘自Oracle® Database SQL Reference

语法参考

DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process 
(or virtual circuit if the connection was made by way of a Shared Sever). 
To use this clause, your instance must have the database open. 
You must identify the session with both of the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
If system parameters are appropriately configured, then application failover will take effect.
•	The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected.
        If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.
•	The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, 
        without waiting for ongoing transactions to complete.
o	If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.
o	If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, 
        then this clause has the same effect as described for KILL SESSION IMMEDIATE.

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, 
release all session locks, and partially recover session resources. 
To use this clause, your instance must have the database open. 
Your session and the session to be terminated must be on the same instance unless you specify integer3.
You must identify the session with the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
•	For the optional integer3, specify the ID of the instance where the target session to be killed exists.
        You can find the instance ID by querying the GV$ tables.
If the session is performing some activity that must be completed, 
such as waiting for a reply from a remote database or rolling back a transaction, 
then Oracle Database waits for this activity to complete, marks the session as terminated, 
and then returns control to you. 
If the waiting lasts a minute, then Oracle Database marks the session to be terminated 
and returns control to you with a message that the session is marked to be terminated. 
The PMON background process then marks the session as terminated when the activity is complete.
Whether or not the session has an ongoing transaction, Oracle Database does not recover 
the entire session state until the session user issues a request to the session 
and receives a message that the session has been terminated.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, 
release all session locks, recover the entire session state, and return control to you immediately.

执行案例

--Oracle 级别kill
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

--Linux/Unix kill
kill -9 spid

--Windows kill
orakill ORACLE_SID spid

发表在 Oracle | 一条评论

TOAD导致ORA-00600[17281]

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

alert日志报ORA-00600[17281]

Thu Aug  2 01:49:39 2012
Errors in file /oracle9/app/admin/acc/udump/acc2_ora_647350.trc:
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor
Thu Aug  2 01:49:39 2012
Errors in file /oracle9/app/admin/acc/udump/acc2_ora_647350.trc:
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor

分析trace文件

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_acc2
Release:        3
Version:        5
Machine:        00CFFB554C00
Instance name: acc2
Redo thread mounted by this instance: 2
Oracle process number: 1598
Unix process pid: 647350, image: oracle@zwq_acc2 (TNS V1-V3)

*** SESSION ID:(1643.29233) 2012-08-02 00:01:44.423
*** 2012-08-02 00:01:44.423
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17281], [1001], [0x7000007AB9F0A30], [], [], [], [], []
ORA-01001: invalid cursor
Current SQL statement for this session:
DECLARE
   CURSOR NlsParamsCursor
   IS
      SELECT * FROM nls_session_parameters;
BEGIN
   SELECT NVL (LENGTHB (CHR (65536)), NVL (LENGTHB (CHR (65536)), 1))
     INTO :CharLength
     FROM DUAL;
   FOR NlsRecord IN NlsParamsCursor
   LOOP
      IF NlsRecord.parameter = 'NLS_DATE_LANGUAGE'
      THEN
         :NlsDateLanguage := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_DATE_FORMAT'
      THEN
         :NlsDateFormat := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_NUMERIC_CHARACTERS'
      THEN
         :NlsNumericCharacters := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_TIMESTAMP_FORMAT'
      THEN
         :NlsTimeStampFormat := NlsRecord.VALUE;
      ELSIF NlsRecord.parameter = 'NLS_TIMESTAMP_TZ_FORMAT'
      THEN
         :NlsTimeStampTZFormat := NlsRecord.VALUE;
      END IF;
   END LOOP;
END;
----- 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
kgeriv+0118          bl       _ptrgl
kgeasi+00cc          bl       kgeriv               000000000 ? 110347AE0 ?
                                                   1101A1738 ? 110380110 ?
                                                   110380420 ?
kgicli+0164          bl       kgeasi               110006308 ? 110386088 ?
                                                   438100004381 ? 200000002 ?
                                                   200000002 ? 000000000 ?
                                                   0000003E9 ? 000000002 ?
kgidlt+03a0          bl       kgicli               110287AA0 ? 000000168 ?
kgidel+0018          bl       kgidlt               11003DD08 ? 110062138 ?
                                                   000000069 ? 000000000 ?
                                                   000000000 ?
perabo+00cc          bl       kgidel               000000000 ? 000000000 ?
perdcs+0038          bl       perabo               000000120 ? 000000012 ?
                                                   70000000005C390 ?
peidcs+00ac          bl       perdcs               110002A20 ? 70000000007A450 ?
kkxcls+00bc          bl       peidcs               FFFFFFFFFFFA298 ? 110349708 ?
kxscln+0048          bl       kkxcls               1031628A8 ?
kkscls+0268          bl       kxscln               110349F60 ?
opicca+00f0          bl       kkscls               1009E8034 ? FFFFFFFFFFFA4C0 ?
opiclo+0020          bl       opicca               FFFFFFFFFFFA810 ?
kpoclsa+004c         bl       opiclo               000000002 ?
opiodr+08cc          bl       _ptrgl
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF7A0 ? 0A000F000 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A02C3B50 ?
                                                   FFFFFFFFFFFF7A0 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?

--------------------- Binary Stack Dump ---------------------

SO: 70000067d802e90, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=1598, calls cur/top: 7000006f50c45f0/7000006f50c45f0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: 70000067d5eedf8 2 6
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000006815fe518
    O/S info: user: oraacc, term: UNKNOWN, ospid: 647350
    OSD pid info: Unix process pid: 647350, image: oracle@zwq_acc2 (TNS V1-V3)
    ----------------------------------------
    SO: 70000068169e7b8, type: 4, owner: 70000067d802e90, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 70000067d802e90, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 700000980c32508, psql: 700000980c32508, user: 44/DBRPT
    O/S info: user: wusp9, term: AHMWUSP9, ospid: 2812:1900, machine: AH\AHMWUSP9
              program:
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=13 wait_time=3647540
                driver id=54435000, #bytes=1, =0
    temporary object counter: 0

这里可以看出当前执行的sql是一种框架中发生,但是从PROCESS STATE未找到登录客户端名称,但是从machine中大概可以估算出来是windows机器.通过查询V$sql找出MODULE

SQL> select module,last_load_time  from V$sql where sql_text like 'declare  cursor NlsParamsCursor is%';

MODULE          LAST_LOAD_TIME
--------------- ----------------------------------
Toad.exe        2012-08-02/00:01:44

从这里可以确定,是因为TOAD登录数据库出现该错误,查询MOS发现相关内容ORA-600 [17281] using TOAD [ID 329531.1]

解决方法

 You can also overcome this by patching the Oracle Client software as follows.:  

1. Fix unpublished Bug 4359111, if the Oracle software is 10.1.0.4 or below. 
   Patches for this bug can be found on My Oracle Support under Patch:4359111.

2. Fix unpublished bug 5910901 by applying Patch:5910901 if the Oracle software is 10.1.0.5 or above,

3. Apply the 10.2.0.4 patch set or above on the client.

4. You can change the non-Oracle client software being used, 

5. You can just ignore the error as it is non-corruptive.

6. Run the queries directly through SQLPLUS instead of using TOAD.

**Ensure that your version of TOAD is compatible with the version of the database as well.  
If the errors continue when using TOAD, but not with SQL*Plus, contact TOAD support to request a fix.**

发表在 ORA-xxxxx | 标签为 ORA-600 17281 | 2 条评论

17813235971

QQ 咨询

分类目录归档：数据库

dul 10支持oracle 11g r2

kill oracle session相关描述

TOAD导致ORA-00600[17281]

文章分类

最近发表

分类目录归档：数据库

dul 10支持oracle 11g r2

kill oracle session相关描述

TOAD导致ORA-00600[17281]

标签云

文章分类

最近发表