sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle TDE 创建加密表空间table move出现异常

时间:2016-11-26 22:18   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle TDE 实施中遇到的小问题,Oracle研究中心案例分析:运维DBA反映Oracle TDE创建加密表空间以后,准备将需要加密的table move到加密表空间中时,发现如下对象是曾经drop过的,但是使用purge dba_recyclebin 发现不管用。

SQL> SELECT owner,segment_name,segment_type
  2    FROM dba_segments
  3   WHERE segment_name LIKE '%BIN$%';            

OWNER                SEGMENT_NAME                                       SEGMENT_TYPE                       
-------------------- -------------------------------------------------- ------------------------------------
SYS                  RECYCLEBIN$                                        TABLE                              
SYS                  RECYCLEBIN$_OBJ                                    INDEX                              
SYS                  RECYCLEBIN$_TS                                     INDEX                              
SYS                  RECYCLEBIN$_OWNER                                  INDEX                              
DMSB01               BIN$eaUSockPX4jgRAAhWnkSBA==$0                     INDEX                              
DMSB01               BIN$eaUnQVOZBL3gRAAhWnkSBA==$0                     INDEX                              
DMSB01               BIN$eaUSockGX4jgRAAhWnkSBA==$0                     INDEX                              
DMSB01               BIN$eaUSockYX4jgRAAhWnkSBA==$0                     INDEX                              
DMSB01               BIN$eaUnQVOPBL3gRAAhWnkSBA==$0                     INDEX                              

9 ROWS selected.     

SQL> DROP INDEX "BIN$eaUSockPX4jgRAAhWnkSBA==$0";                     
DROP INDEX "BIN$eaUSockPX4jgRAAhWnkSBA==$0"                           
           *                                                          
ERROR at line 1:                                                      
ORA-02429: cannot DROP INDEX used FOR enforcement OF UNIQUE/PRIMARY KEY

SQL> SELECT TABLE_NAME, index_type
  2    FROM dba_indexes
  3   WHERE index_name IN (SELECT segment_name
  4                          FROM dba_segments
  5                         WHERE segment_name LIKE '%BIN$%' AND owner = 'DMSB01');

TABLE_NAME                     INDEX_TYPE                                                                                   
------------------------------ ------------------                                                                           
DROP1_CSTMSLD                  NORMAL                                                                                       
CSTMSLH                        NORMAL                                                                                       
CSTMSLE                        NORMAL                                                                                       
CSTMSLD                        NORMAL                                                                                       
CSTMSSH                        NORMAL                                                                                       


SQL> SHOW USER
USER IS "DMSB01"

SQL> ALTER TABLE DROP1_CSTMSLD MODIFY PRIMARY KEY disable; 
TABLE altered.

SQL> ALTER TABLE CSTMSLH MODIFY PRIMARY KEY disable;      
TABLE altered.

SQL> ALTER TABLE CSTMSLE MODIFY PRIMARY KEY disable;      
TABLE altered.

SQL> ALTER TABLE CSTMSLD MODIFY PRIMARY KEY disable;      
TABLE altered.

SQL> ALTER TABLE CSTMSSH MODIFY PRIMARY KEY disable;      
TABLE altered.                                             


SQL> DROP INDEX "BIN$eaUSockPX4jgRAAhWnkSBA==$0" ;
INDEX dropped.

SQL> DROP INDEX "BIN$eaUnQVOZBL3gRAAhWnkSBA==$0" ;    
INDEX dropped.

SQL> DROP INDEX "BIN$eaUSockGX4jgRAAhWnkSBA==$0" ;    
INDEX dropped.

SQL> DROP INDEX "BIN$eaUSockYX4jgRAAhWnkSBA==$0" ;    
INDEX dropped.

SQL> DROP INDEX "BIN$eaUnQVOPBL3gRAAhWnkSBA==$0" ;                                                      
INDEX dropped.

SQL> SELECT owner,segment_name,segment_type
  2    FROM dba_segments
  3   WHERE segment_name LIKE '%BIN$%';

OWNER                SEGMENT_NAME              SEGMENT_TYPE
-------------------- ------------------------- ------------------------------------
SYS                  RECYCLEBIN$               TABLE
SYS                  RECYCLEBIN$_OBJ           INDEX
SYS                  RECYCLEBIN$_TS            INDEX
SYS                  RECYCLEBIN$_OWNER         INDEX


SQL> ALTER TABLE DROP1_CSTMSLD MODIFY PRIMARY KEY enable;   
TABLE altered.

SQL> ALTER TABLE CSTMSLH       MODIFY PRIMARY KEY enable;   
TABLE altered.

SQL> ALTER TABLE CSTMSLE       MODIFY PRIMARY KEY enable;   
TABLE altered.

SQL> ALTER TABLE CSTMSLD       MODIFY PRIMARY KEY enable;   
TABLE altered.

SQL> ALTER TABLE CSTMSSH       MODIFY PRIMARY KEY enable;
TABLE altered.

另外一点是rebuild index时,发现数据量较大,而目前存储空间不足,由于将部分表move了以后,原表空间是可以缩小的,但是问题是如何知道该表空间的那些数据文件可以进行resize 呢?如何知道每个datafile 的使用情况呢? 通过如下加班实现:

SQL> SELECT *
  2    FROM (SELECT /*+ ordered use_hash(a,b,c) */
  3           a.file_id,
  4           a.file_name,
  5           a.filesize,
  6           b.freesize,
  7           (a.filesize - b.freesize) usedsize,
  8           c.hwmsize,
  9           c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
10           a.filesize - c.hwmsize canshrinksize
11            FROM (SELECT file_id,
12                         file_name,
13                         round(bytes / 1024 / 1024) filesize
14                    FROM dba_data_files) a,
15                 (SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize
16                    FROM dba_free_space dfs
17                   GROUP BY file_id) b,
18                 (SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize
19                    FROM dba_extents
20                   GROUP BY file_id) c
21           WHERE a.file_id = b.file_id
22             AND a.file_id = c.file_id
23           ORDER BY unsedsize_belowhwm DESC)
24   WHERE file_id IN (SELECT file_id
25                       FROM dba_data_files
26                      WHERE tablespace_name = 'DMSB_TS01')
27   ORDER BY file_id;

FILE_ID FILE_NAME                               FILESIZE   FREESIZE   USEDSIZE    HWMSIZE UNSEDSIZE_BELOWHWM CANSHRINKSIZE
-------- --------------------------------------- -------- ---------- ---------- ---------- ------------------ -------------
       5 /dms/oradata/DMSBHMC/datafile/DMSB_TS01     4096       2177       1919       3097               1178           999
       6 /dms/oradata/DMSBHMC/datafile/DMSB_TS02     4096       2221       1875       3053               1178          1043
       7 /dms/oradata/DMSBHMC/datafile/DMSB_TS03     4096       2244       1852       2894               1042          1202
       8 /dms/oradata/DMSBHMC/datafile/DMSB_TS04     4096       2292       1804       2845               1041          1251
       9 /dms/oradata/DMSBHMC/datafile/DMSB_TS05     4096       1421       2675       4021               1346            75
      10 /dms/oradata/DMSBHMC/datafile/DMSB_TS06     4096       1452       2644       3989               1345           107
      11 /dms/oradata/DMSBHMC/datafile/DMSB_TS07     4096       1503       2593       3935               1342           161
      12 /dms/oradata/DMSBHMC/datafile/DMSB_TS08     4096       1523       2573       3855               1282           241
      13 /dms/oradata/DMSBHMC/datafile/DMSB_TS09     4096       1615       2481       3750               1269           346
      15 /dms/oradata/DMSBHMC/datafile/DMSB_TS10     4096       1674       2422       3628               1206           468
      20 /dms/oradata/DMSBHMC/datafile/DMSB_TS11     4096       1848       2248       3454               1206           642
      21 /dms/oradata/DMSBHMC/datafile/DMSB_TS12     4096       1867       2229       3432               1203           664
      23 /dms/oradata/DMSBHMC/datafile/DMSB_TS13     4096       1964       2132       3335               1203           761
      25 /dms/oradata/DMSBHMC/datafile/DMSB_TS14     4096       2095       2001       3195               1194           901

14 ROWS shttp://www.oracleplus.netelected.

++++++ 从上可以看出,如果我们需要对某个datafile进行resize,那么必须大于HWMSIZE值。++++++
++++++ resize以后的情况如下:++++++

FILE_ID FILE_NAME                                FILESIZE   FREESIZE   USEDSIZE    HWMSIZE UNSEDSIZE_BELOWHWM CANSHRINKSIZE
------- ---------------------------------------- -------- ---------- ---------- ---------- ------------------ -------------
      5 /dms/oradata/DMSBHMC/datafile/DMSB_TS01      3100       1242       1858       3097               1239             3
      6 /dms/oradata/DMSBHMC/datafile/DMSB_TS02      3072       1258       1814       3053               1239            19
      7 /dms/oradata/DMSBHMC/datafile/DMSB_TS03      3000       1209       1791       2894               1103           106
      8 /dms/oradata/DMSBHMC/datafile/DMSB_TS04      3000       1257       1743       2845               1102           155
      9 /dms/oradata/DMSBHMC/datafile/DMSB_TS05      4025       1482       2543       4021               1478             4
     10 /dms/oradata/DMSBHMC/datafile/DMSB_TS06      4096       1584       2512       3989               1477           107
     11 /dms/oradata/DMSBHMC/datafile/DMSB_TS07      4096       1632       2464       3935               1471           161
     12 /dms/oradata/DMSBHMC/datafile/DMSB_TS08      3858       1416       2442       3855               1413             3
     13 /dms/oradata/DMSBHMC/datafile/DMSB_TS09      3755       1404       2351       3750               1399             5
     15 /dms/oradata/DMSBHMC/datafile/DMSB_TS10      3630       1321       2309       3628               1319             2
     20 /dms/oradata/DMSBHMC/datafile/DMSB_TS11      3455       1304       2151       3452               1301             3
     21 /dms/oradata/DMSBHMC/datafile/DMSB_TS12      3440       1291       2149       3431               1282             9
     23 /dms/oradata/DMSBHMC/datafile/DMSB_TS13      3340       1287       2053       3335               1282             5
     25 /dms/oradata/DMSBHMC/datafile/DMSB_TS14      3200       1262       1938       3195               1257             5

14 ROWS selected.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle TDE 创建加密表空间table move出现异常

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1327.html

Oracle研究中心

关键词:

Oracle TDE

Oracle表空间加密技术详细分析