分类目录归档：Oracle

truncate table 无论drop storage还是reuse storage不影响数据恢复

发表于 2018 年 6 月 21 日 由 惜分飞

今天有朋友咨询,truncate table有drop storage和reuse storage方式,是否影响后续的数据恢复(在没有其他覆盖的情况下),我做了一个简单的测试证明,这些都不影响truncate table的数据库恢复
创建测试环境

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

Table created.

SQL> create table t_xifenfei2  tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL>  create table t_xifenfei3  tablespace USERNEW
  2  as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     86348

SQL>  select count(*) from t_xifenfei2;

  COUNT(*)
----------
     86349

SQL>  select count(*) from t_xifenfei3;

  COUNT(*)
----------
     86350

SQL> select object_id,data_object_id,object_name from dba_objects where object_name like 't_xifenfei%';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
     88205          88205 t_xifenfei
     88206          88206 t_xifenfei2
     88207          88207 t_xifenfei3

SQL> truncate table t_xifenfei;

Table truncated.

SQL> truncate table t_xifenfei2 drop storage;

Table truncated.

SQL> truncate table t_xifenfei3 reuse storage;

Table truncated.

使用dul进行恢复

  
DUL>  bootstrap;
DUL> scan database;

scanning database...
scanning database finished.

DUL> unload table sys.t_xifenfei object 88205;

Unloading table: t_xifenfei,object ID: 88205
Unloading segment,storage(Obj#=88205 DataObj#=88205 TS#=6 File#=5 Block#=1410 Cluster=0)
86348 rows unloaded

DUL> unload table sys.t_xifenfei2 object 88206;

Unloading table: t_xifenfei2,object ID: 88206
Unloading segment,storage(Obj#=88206 DataObj#=88206 TS#=6 File#=5 Block#=2690 Cluster=0)
86349 rows unloaded

DUL> unload table sys.t_xifenfei3 object 88207;

Unloading table: t_xifenfei3,object ID: 88207
Unloading segment,storage(Obj#=88207 DataObj#=88207 TS#=6 File#=5 Block#=3970 Cluster=0)
86350 rows unloaded

这里证明truncate table不管是drop storage还是reuse storage或者默认,在没有被覆盖的情况下，数据均完全恢复出来

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

OERR: ALL 600 Ora 600 Layers

发表于 2018 年 6 月 19 日 由 惜分飞 
** 0000 ** Service Layer
========================
   KIESCBAS    (1)                              Component notifier component   
   KIESDBAS    (100)                                         Debug component   
   KIESEBAS    (300)                                         Error component   
   KIESLBAS    (500)                                          Lock component   
   KIESMBAS    (700)                                        Memory component   
   KIESPBAS    (900)                             System Parameters component   
   KIESSBAS    (1100)                          System State object component   
   KIESGBAS    (1110)               Generic Linked List management component   
   KIESQBAS    (1140)                                      Enqueue component   
   KIESIBAS    (1180)                               Instance Locks component   
   KIESUBAS    (1200)                            User State object component   
   KIESABAS    (1400)                                   Async Msgs component   
   KIESKBAS    (1700)                                  license Key component   
   KIESRBAS    (1800)                        Instance Registration component
   KIESOBAS    (1850)                                 I/O Services component

** 2000 ** Cache Layer Component Base internal error values
===========================================================
   KIECOBAS    (2000)                                     Cache Op component   
   KIECCBAS    (2100)                            Control File mgmt component   
   KIECMBAS    (2200)                                        Misc (SCN etc.)   
   KIECHBAS    (2400)                             Buffer Instance Hash Table   
   KIECRBAS    (2600)                                    Redo file component   
   KIECFBAS    (2800)                                      Db file component   
   KIECABAS    (3000)                             Redo Application component   
   KIECBBAS    (3200)                                    Buffer manager base   
   KIECZBAS    (3400)               Archival & media recovery component base   
   KIECLBAS    (3500)                           direct Loader component base   
   KIECVBAS    (3600)                                recoVery component base   
   KIECKBAS    (3700)                                  Thread component base   
   KIECKBAS    (3800)                   Compatibility segment component base   
   KIECBBAS    (3900)              Buffer working set manager component base

** 4000 ** Transaction Layer   
============================
       Transaction Layer Component Base internal error values:   
   KIETUBAS    (4000)                             Transaction Undo Component   
      internal errors 4100-4199 used by transaction undo component as well     
   KIETPBAS    (4210)                                   Transaction Parallel
   KIETLBAS    (4250)                                       Transaction List
   KIETSBAS    (4300)                                    Transaction Segment   
   KIETCBAS    (4400)                          Transaction Control Component   
      internal errors 4450-4499 used by transaction distributed component      
   KIETBBAS    (4500)                            Transaction Block Component   
   KIETABAS    (4600)                            Transaction Table Component   
   KIETRBAS    (4800)                              Query Row Cache Component   
   KIETMBAS    (4900)                          Transaction Monitor Component   
   KIETSBAS    (4950)                              Query Bootstrap Component
   KIETEBAS    (5000)                                     Transaction Extent

** 6000 ** Data Layer
=====================

** 8000 ** Access Layer
=======================

   Data Layer Component Base internal error values:   
   8000...8199 reserved for data layer   
   8100...8149 reserved for index component
   8100 - 8109 used in KAU
   8110 - 8124 used in KKRI, KDIC (create index)

** 9000 ** Parallel Server
==========================
   KIECLBAS    (9000)                            KCL component: 9000 .. 9100

** 10000 ** Control Layer   
=========================

** 12000 ** User/Oracle Interface Layer   
=======================================
    User/Oracle Interface & SQL Layer "components" (in a loose sense)
   KIEUTBAS   (12000)                         TAC* component: 12000 .. 12009   
   KIEURBAS   (12010)                         SORT component: 12010 .. 12199   
   KIEUPBAS   (12200)                         OPI* component: 12200 .. 12299   
   KIEUSBAS   (12300)      PRS, APA, EVA, EXP, QBA component: 12300 .. 12399   
   KIEUWBAS   (12400)    QKA, MSQ, and row source components: 12400 .. 12499
                              reserved for rix: 12480 .. 12499   
   KIEUVBAS   (12600)                         *DRV component: 12600 .. 12799   
   KIEUDBAS   (12800)          KKDL, KKDC and KKM components: 12800 .. 12999   
   KIEUXBAS   (13000                     DELEXE, UPDEXE, CRI: 13000 .. 13079   
                                                        REF : 13080 .. 13089   
                                                        WSM : 13090 .. 13099   
   KIEUABAS   (13100)                                 OPIAMR: 13100 .. 13149   
   KIEULBAS   (13200)                            ACL and ATB: 13200 .. 13230   
   KIEUZBAS   (13250)                       AUD, AUSDRV, AOP: 13250 .. 13300
   KIEUOBAS   (13301)             KOK* and object extensions: 13301 .. 13499
   KIELCBAS   (16000)                            loader column array builder   

** 14000 ** System-dependent "Layer"   
====================================
   System Dependent Layer Component Base internal error values:   
   KIESFBAS   (14000)                                         File component   
   KIESCBAS   (14100)                                  Concurrency component   
   KIESPBAS   (14200)                                      Process component   
   KIESXBAS   (14300)                            Exception-handler component   
   KIESMBAS   (14500)                                       Memory component   

   ** Note that multiple ports can use the same internal error numbers.
      We allow this because we don't want to waste numbers on port internal
      errors, something which is very rarely used.  In addition it is
      likely that any bug that is reporting a port internal error to the
      generic (portable) coding group is going to have to supply a lot of
      documentation - so if two ports use the same number, it should never
      confuse anybody.
  
** 15000 ** Security Layer   
==========================
    15000 - 15079 reserved for KZ*, SEC, and GRA;
    15080 - 15099 reserved for KZL;
    15100 - 15159 reserved for KKP;
    15160 - 15199 reserved for KKO and KKE;
        15190 .. 15199 reserved for KKOCRI
    15200 - 15229 reserved for KKS;
    15230 - 15259 reserved for KKT;
    15260 - 15399 reserved for KKD;
    15400 - 15409 reserved for KKY;
    15410 - 15499 reserved for KKX (and PSD);
    15500 - 15549 reserved for KKM;
    15550 - 15599 reserved for KKR; (and KXH)
    15600 - 15619 reserved for KKF; (parallel query)
    15620 - 15639 reserved for KKZ (snapshots);
    15640 - 15659 reserved for KKXA;
    15660 - 15669 reserved for KKJ (job queue);
    15670 - 15679 reserved for KKFS (parallel query)
    15680 - 15689 reserved for KKRT;
    15690 - 15699 reserved for other KK*;
    
    15700 - 15799 reserved for KXFP; (parallel query)
    15800 - 15809 reserved for KXFX; (parallel query)
    15810 - 15819 reserved for KXFQ; (parallel query)
    15820 - 15849 reserved for other KXF*; (parallel query)
    15850 - 15859 reserved for KSX*;
    15860 - 15999 reserved for other KX*;
    
** 16000 ** Loader "Layer"   
==========================
    16000 - 16149 reserved for the direct loader;
    16150 - 16199 reserved for the c level call interface to direct loader;
    16200 - 16229 reserved for KQL;
    16230 - 16249 reserved for KQLR;
    16250 - 16299 reserved for KQA;
    16300 - 16399 reserved for KM*;
    16400 - 16449 reserved for KKB;
    16450 - 16499 reserved for KQLM;
    16500 - 16549 reserved for KQD;
    16550 - 16559 reserved for KQF;
    16600 - 16650 reserved for KQLS;
    16651 - 16700 reserved for KQLD;
    16701 - 16750 reserved for KQLB;

** 17000 ** Generic "Layer"   
===========================
    17000 - 17089 reserved for KGL;
    17090 - 17099 reserved for KGE;
    17100 - 17199 reserved for KGH;
    17200 - 17249 reserved for KGLR/KGLRO;
    17250 - 17269 reserved for KGP;
    17270 - 17299 reserved for KGI;
    17300 - 17399 reserved for KGHX;
    17500 - 17624 reserved for I/O subsystem KG components (KGFD, KGFF, KGK)
    17625 - 17639 reserved for KGL3;
    17640 - 17989 reserved for other KG*;
    17990 - 17999 reserved for PL/SQL;
    
** 18000 **   K2 (2-phase commit),subdiv'd in k2.h: 18000 .. 18499   
==================================================================
    18000 - 18999 reserved for K2*;

** 19000 ** Object Layer   
========================
    19000 - 19999 reserved for KO*;

    20000 - 20399 reserved for bitmap index KDIB*;
    20400 - 20499 reserved for KKPO;;
    20500 - 20999 reserved for SMR;

** 21000 ** Replication Layer
=============================
    21000 - 21999 reserved for KN*;

** 23000 ** OLTP Layer
======================
    23000 - 23999 reserved for KW*;
    24000 - 24049 reserved for QOL;
发表在 Oracle | 评论关闭

oracle 18c依旧支持bbed

发表于 2018 年 6 月 15 日 由 惜分飞

18c数据库版本

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

编译18c的bbed

[oracle@localhost soft]$ make -f ./rdbms/lib/ins_rdbms.mk BBED=./bin/bbed ./bin/bbed

Linking BBED utility (bbed)
rm -f bin/bbed
/u02/soft/bin/orald -o bin/bbed -m64 -z noexecstack -Wl,--disable-new-dtags -L/u02/soft/rdbms/lib/
-L/u02/soft/lib/ -L/u02/soft/lib/stubs/  /u02/soft/lib/s0main.o /u02/soft/rdbms/lib/ssbbded.o
/u02/soft/rdbms/lib/sbbdpt.o `cat /u02/soft/lib/ldflags`    -lncrypt18 -lnsgr18 -lnzjs18 -ln18
-lnl18 -lngsmshd18 -ldbtools18 -lclntsh -lclntshcore  `cat /u02/soft/lib/ldflags`    -lncrypt18
-lnsgr18 -lnzjs18 -ln18 -lnl18 -lngsmshd18 -lnro18 `cat /u02/soft/lib/ldflags`    -lncrypt18
-lnsgr18 -lnzjs18 -ln18 -lnl18 -lngsmshd18 -lnnz18 -lzt18 -lztkg18 -lztkg18 -lclient18 -lnnetd18
-lcommon18 -lgeneric18 -lmm -lsnls18 -lnls18  -lcore18 -lsnls18 -lnls18 -lcore18 -lsnls18 -lnls18
-lxml18 -lcore18 -lunls18 -lsnls18 -lnls18 -lcore18 -lnls18 `cat /u02/soft/lib/ldflags`    -lncrypt18
-lnsgr18 -lnzjs18 -ln18 -lnl18 -lngsmshd18 -lnro18 `cat /u02/soft/lib/ldflags`    -lncrypt18 -lnsgr18
-lnzjs18 -ln18 -lnl18 -lngsmshd18 -lclient18 -lnnetd18  -lcommon18 -lgeneric18   -lsnls18 -lnls18
-lcore18 -lsnls18 -lnls18 -lcore18 -lsnls18 -lnls18 -lxml18 -lcore18 -lunls18 -lsnls18 -lnls18 -lcore18
-lnls18 -lclient18 -lnnetd18  -lcommon18 -lgeneric18 -lsnls18 -lnls18  -lcore18 -lsnls18 -lnls18
-lcore18 -lsnls18 -lnls18 -lxml18 -lcore18 -lunls18 -lsnls18 -lnls18 -lcore18 -lnls18
`cat /u02/soft/lib/sysliblist` -Wl,-rpath,/u02/soft/lib -lm    `cat /u02/soft/lib/sysliblist` -ldl -lm   -L/u02/soft/lib

启动bbed

[oracle@localhost soft]$ bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Fri Jun 15 17:06:28 2018

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u02/app/oracle/oradata/XFFDB/system01.dbf'
        FILENAME        /u02/app/oracle/oradata/XFFDB/system01.dbf

BBED> map
 File: /u02/app/oracle/oradata/XFFDB/system01.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 1272 bytes                   @0

 ub4 tailchk                                @8188

BBED> p kcvfh
struct kcvfh, 1272 bytes                    @0
   struct kcvfhbfh, 20 bytes                @0
      ub1 type_kcbh                         @0        0x0b
      ub1 frmt_kcbh                         @1        0xa2
      ub2 wrp2_kcbh                         @2        0x0000
      ub4 rdba_kcbh                         @4        0x00400001
      ub4 bas_kcbh                          @8        0x00000000
      ub2 wrp_kcbh                          @12       0x0000
      ub1 seq_kcbh                          @14       0x01
      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
      ub2 chkval_kcbh                       @16       0x2df1
      ub2 spare3_kcbh                       @18       0x0000
   struct kcvfhhdr, 76 bytes                @20
      ub4 kccfhswv                          @20       0x00000000
      ub4 kccfhcvn                          @24       0x12000000<--已经为18c版本(16进制12=10进制18)
      ub4 kccfhdbi                          @28       0x386dba85
      text kccfhdbn[0]                      @32      X
      text kccfhdbn[1]                      @33      F
      text kccfhdbn[2]                      @34      F
      text kccfhdbn[3]                      @35      D
      text kccfhdbn[4]                      @36      B
      text kccfhdbn[5]                      @37
      text kccfhdbn[6]                      @38
      text kccfhdbn[7]                      @39
      ub4 kccfhcsq                          @40       0x000027af
      ub4 kccfhfsz                          @44       0x0001ae00
      s_blkz kccfhbsz                       @48       0x00
      ub2 kccfhfno                          @52       0x0001
      ub2 kccfhtyp                          @54       0x0003
      ub4 kccfhacid                         @56       0x00000000
      ub4 kccfhcks                          @60       0x00000000
      text kccfhtag[0]                      @64
      text kccfhtag[1]                      @65
      text kccfhtag[2]                      @66
      text kccfhtag[3]                      @67
      text kccfhtag[4]                      @68
      text kccfhtag[5]                      @69
      text kccfhtag[6]                      @70
      text kccfhtag[7]                      @71
      text kccfhtag[8]                      @72
      text kccfhtag[9]                      @73
      text kccfhtag[10]                     @74
      text kccfhtag[11]                     @75
      text kccfhtag[12]                     @76
      text kccfhtag[13]                     @77
      text kccfhtag[14]                     @78
      text kccfhtag[15]                     @79
      text kccfhtag[16]                     @80
      text kccfhtag[17]                     @81
      text kccfhtag[18]                     @82
      text kccfhtag[19]                     @83
      text kccfhtag[20]                     @84
      text kccfhtag[21]                     @85
      text kccfhtag[22]                     @86
      text kccfhtag[23]                     @87
      text kccfhtag[24]                     @88
      text kccfhtag[25]                     @89
      text kccfhtag[26]                     @90
      text kccfhtag[27]                     @91
      text kccfhtag[28]                     @92
      text kccfhtag[29]                     @93
      text kccfhtag[30]                     @94
      text kccfhtag[31]                     @95
   ub4 kcvfhrdb                             @96       0x00400208
   struct kcvfhcrs, 8 bytes                 @100
      ub4 kscnbas                           @100      0x00000007
      ub2 kscnwrp                           @104      0x8000
      ub2 kscnwrp2                          @106      0x0000
   ub4 kcvfhcrt                             @108      0x39aaed07
   ub4 kcvfhrlc                             @112      0x39c5e108
   struct kcvfhrls, 8 bytes                 @116
      ub4 kscnbas                           @116      0x0016ac11
      ub2 kscnwrp                           @120      0x8000
      ub2 kscnwrp2                          @122      0x0000
   ub4 kcvfhbti                             @124      0x00000000
   struct kcvfhbsc, 8 bytes                 @128
      ub4 kscnbas                           @128      0x00000000
      ub2 kscnwrp                           @132      0x0000
      ub2 kscnwrp2                          @134      0x0000
   ub2 kcvfhbth                             @136      0x0000
   ub2 kcvfhsta                             @138      0x2004 (KCVFHOFZ)
   struct kcvfhckp, 36 bytes                @484
      struct kcvcpscn, 8 bytes              @484
         ub4 kscnbas                        @484      0x001cb769
         ub2 kscnwrp                        @488      0x8000
         ub2 kscnwrp2                       @490      0x0000
      ub4 kcvcptim                          @492      0x3a585ab6
      ub2 kcvcpthr                          @496      0x0001
      union u, 12 bytes                     @500
         struct kcvcprba, 12 bytes          @500
            ub4 kcrbaseq                    @500      0x00000007
            ub4 kcrbabno                    @504      0x0000fce3
            ub2 kcrbabof                    @508      0x0010
      ub1 kcvcpetb[0]                       @512      0x02
      ub1 kcvcpetb[1]                       @513      0x00
      ub1 kcvcpetb[2]                       @514      0x00
      ub1 kcvcpetb[3]                       @515      0x00
      ub1 kcvcpetb[4]                       @516      0x00
      ub1 kcvcpetb[5]                       @517      0x00
      ub1 kcvcpetb[6]                       @518      0x00
      ub1 kcvcpetb[7]                       @519      0x00
   ub4 kcvfhcpc                             @140      0x0000003c
   ub4 kcvfhrts                             @144      0x3a5859f0
   ub4 kcvfhccc                             @148      0x0000003b
   struct kcvfhbcp, 36 bytes                @152
      struct kcvcpscn, 8 bytes              @152
         ub4 kscnbas                        @152      0x00000000
         ub2 kscnwrp                        @156      0x0000
         ub2 kscnwrp2                       @158      0x0000
      ub4 kcvcptim                          @160      0x00000000
      ub2 kcvcpthr                          @164      0x0000
      union u, 12 bytes                     @168
         struct kcvcprba, 12 bytes          @168
            ub4 kcrbaseq                    @168      0x00000000
            ub4 kcrbabno                    @172      0x00000000
            ub2 kcrbabof                    @176      0x0000
      ub1 kcvcpetb[0]                       @180      0x00
      ub1 kcvcpetb[1]                       @181      0x00
      ub1 kcvcpetb[2]                       @182      0x00
      ub1 kcvcpetb[3]                       @183      0x00
      ub1 kcvcpetb[4]                       @184      0x00
      ub1 kcvcpetb[5]                       @185      0x00
      ub1 kcvcpetb[6]                       @186      0x00
      ub1 kcvcpetb[7]                       @187      0x00
   ub4 kcvfhbhz                             @312      0x00000000
   struct kcvfhxcd, 16 bytes                @316
      ub4 space_kcvmxcd[0]                  @316      0x00000000
      ub4 space_kcvmxcd[1]                  @320      0x00000000
      ub4 space_kcvmxcd[2]                  @324      0x00000000
      ub4 space_kcvmxcd[3]                  @328      0x00000000
   sword kcvfhtsn                           @332      0
   ub2 kcvfhtln                             @336      0x0006
   text kcvfhtnm[0]                         @338     S
   text kcvfhtnm[1]                         @339     Y
   text kcvfhtnm[2]                         @340     S
   text kcvfhtnm[3]                         @341     T
   text kcvfhtnm[4]                         @342     E
   text kcvfhtnm[5]                         @343     M
   text kcvfhtnm[6]                         @344
   text kcvfhtnm[7]                         @345
   text kcvfhtnm[8]                         @346
   text kcvfhtnm[9]                         @347
   text kcvfhtnm[10]                        @348
   text kcvfhtnm[11]                        @349
   text kcvfhtnm[12]                        @350
   text kcvfhtnm[13]                        @351
   text kcvfhtnm[14]                        @352
   text kcvfhtnm[15]                        @353
   text kcvfhtnm[16]                        @354
   text kcvfhtnm[17]                        @355
   text kcvfhtnm[18]                        @356
   text kcvfhtnm[19]                        @357
   text kcvfhtnm[20]                        @358
   text kcvfhtnm[21]                        @359
   text kcvfhtnm[22]                        @360
   text kcvfhtnm[23]                        @361
   text kcvfhtnm[24]                        @362
   text kcvfhtnm[25]                        @363
   text kcvfhtnm[26]                        @364
   text kcvfhtnm[27]                        @365
   text kcvfhtnm[28]                        @366
   text kcvfhtnm[29]                        @367
   ub4 kcvfhrfn                             @368      0x00000001
   struct kcvfhrfs, 8 bytes                 @372
      ub4 kscnbas                           @372      0x00000000
      ub2 kscnwrp                           @376      0x0000
      ub2 kscnwrp2                          @378      0x0000
   ub4 kcvfhrft                             @380      0x00000000
   struct kcvfhafs, 8 bytes                 @384
      ub4 kscnbas                           @384      0x00000000
      ub2 kscnwrp                           @388      0x0000
      ub2 kscnwrp2                          @390      0x0000
   ub4 kcvfhbbc                             @392      0x00000000
   ub4 kcvfhncb                             @396      0x00000000
   ub4 kcvfhmcb                             @400      0x00000000
   ub4 kcvfhlcb                             @404      0x00000000
   ub4 kcvfhbcs                             @408      0x00000000
   ub2 kcvfhofb                             @412      0x000a
   ub2 kcvfhnfb                             @414      0x000a
   ub4 kcvfhprc                             @416      0x39aaecfc
   struct kcvfhprs, 8 bytes                 @420
      ub4 kscnbas                           @420      0x00000001
      ub2 kscnwrp                           @424      0x0000
      ub2 kscnwrp2                          @426      0x0000
   struct kcvfhprfs, 8 bytes                @428
      ub4 kscnbas                           @428      0x00000000
      ub2 kscnwrp                           @432      0x0000
      ub2 kscnwrp2                          @434      0x0000
   ub4 kcvfhtrt                             @444      0x00000000

BBED>

这里证明bbed依旧能够在Oracle 18c的数据库版本中工作,oracle 12.2依旧支持bbed

发表在 ORACLE 18C | 标签为 , , | 评论关闭