Archive 2012年9月24日

使用Log Miner恢复数据的案例一则

使用Log Miner恢复数据的案例一则有6条评论 , , ,

        上周五（9月21日上午11点左右），收到项目组的一封紧急邮件：

生产数据库中，FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的数据9月1号到9月19号的数据都被删除了。

烦请提供下技术支持，恢复这两张表的数据。待回复。谢谢！

经过沟通，初步了解到系统的信息是：这是一套运行在IBM P750的小机上的64位的11gR2的单实例数据库。其实，这套环境也是之前的一篇文章里[记录一次在IBM P750小机上给Oracle动态扩展存储]提到的系统。

进一步了解，确认数据库中FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的数据在9月20号下午3点左右被误删除了，且这两张表是主子表的关系。

我首先想到的方法是，尝试使用事务的闪回查询，看看能否找回数据？结果很不幸，由于是生产数据库，事务繁忙，且误操作离当前时间较长（差不错相差20个小时），UNDO表空间中的回滚数据被覆盖了，遇到了ORA-01555回滚过旧的错误。显然，这条路是走不通了。

接下来，看看系统中是否有之前的有效的逻辑备份？如果有的话，可以用逻辑恢复的方式来尝试找回数据，再次不幸，该系统中采用的RMAN备份，并无逻辑备份。显然，该方法同样不凑效。

那么，我能想到的方法就是对全库做基于时间点的不完全恢复或者使用Oracle 自带的Log Miner工具来挖掘数据了。而该生产库的数据量很大，如果使用基于时间的不完全恢复的话，又有种种弊端和风险。比如，肯定得在一套独立的测试库上执行基于时间的不完全恢复，还有就是rman备份文件很大，这个显然是下下策了。

最后，选择Log Miner工具来尝试找回数据了。下面，记录一下这次的主要过程：

1  首先，找出系统中涵盖误操作时间段的归档日志，这里找出9月20日15点到17点之间的归档：

select name,FIRST_TIME from v$archived_log where first_time between to_date('2012/09/20 14:50:00','yyyy/mm/dd hh24:mi:ss') and to_date('2012/09/20 17:00:00','yyyy/mm/dd hh24:mi:ss');
NAME                                                                   FIRST_TIME
---------------------------------------------------------------------- -------------------
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123956_85ohgybj_.arc    2012/09/20 14:52:41
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123957_85ohn6qh_.arc    2012/09/20 14:55:26
...
...
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123984_85olkh0t_.arc    2012/09/20 15:45:49
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123985_85olo3vb_.arc    2012/09/20 15:47:59
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123986_85olqg4r_.arc    2012/09/20 15:49:55
...
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123992_85om9wo7_.arc    2012/09/20 15:59:07

2  调用dbms_logmnr系统包，添加归档日志：

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123956_85ohgybj_.arc',options=>dbms_logmnr.new);

3  调用dbms_logmnr系统包，启动Log Miner开始挖掘日志：

SQL>exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

4  从v$logmnr_contents系统表中，查看是否包含FIN_CASH_DETAIL表的SQL语句：

SQL>select timestamp,table_name,sql_redo,sql_undo,operation from v$logmnr_contents where table_name='FIN_CASH_DETAIL';

5  调用dbms_logmnr系统包，停止Log Miner：

exec dbms_logmnr.end_logmnr;

6  重复上述2~5步骤的动作，只是每次需要添加的归档日志不同而已。终于，在/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123985_85olo3vb_.arc这份归档日志中，均找到有FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的操作。也就是误操作的时间应该是在2012/09/20 15:47:59到2012/09/20 15:49:55之间。

select sql_redo from v$logmnr_contents where table_name='FIN_CASH_MOVEMENT' and operation='DELETE';
SQL_REDO
---------------------------------------------------------------------------------------------------
delete from "SITCLINE"."FIN_CASH_MOVEMENT" where "CASH_MOVEMENT_ID" = '2c2881d63987424d01398b77fa2f6345' and "RP_ID" = 'R' and "OFFICE_ID" = 'SITTP' and "STATE_IND" = '0' 
and "MOVEMENT_TYPE" = 'CHECK' and "MOVEMENT_NO" = 'CR201209030138' and "BANK_MOVEMENT_NO" = '010060936' and "MOVEMENT_DATE" 
= TO_DATE('03-9月 -12', 'DD-MON-RR') and "LEDGER_PARTNER_CODE" = '80273312' and "LEDGER_PARTNER_NAME" IS NULL 
and "EXTERNAL_BANK_NAME" IS NULL and "EXTERNAL_BANK_ACCOUNT" = '056637' and "EXTERAL_BANK_ACCOUN
T_NAME" IS NULL and "INTERNAL_BANK_ID" = '2c2881d63978294201397a864fe30c1e' and "INTERNAL_BANK_NAME" = '花旗台灣' and "INTERNAL_BACNT_ID" = '2c2881d63978294201397a8c72be0c6c' and "INTERNAL_BANK_ACCOUNT_CODE" = '5049328003' 
and "INTERNAL_BANK_ACCOUNT_NAME" = 'SITC STEAMSHIPS CO LTD TAIWAN BRANCH' and "PRIME_CURRENCY_CODE" = 'NTD' and "PRIME_CURRENCY_VALUE" = '10799' and "BASE_CURRENCY_CODE" IS NULL and "BASE_CURRENCY_VALUE" IS NULL and "REMARK" IS NULL and "REALRP" = '1' and "REALRP_DATE" 
IS NULL and
 "REALRP_PERSON" IS NULL and "REALRP_PERSON_NAME" IS NULL and "DISCOUNT_VALUE" IS NULL and "DISCOUNT_REMARK" 
IS NULL and "RATE_BASE" IS NULL and "ALLOCATION_EVENT_ID" IS NULL and "DEPOSIT_DATE" = TO_DATE('03-9月 -12', 'DD-MON-RR') and "INVOICE_INFO" IS NULL and "CREATED_BY_USER" = 'FIN_TWPEI05' and "CREATED_OFFICE" = 'TP_FIN_DP' and "CREATED_DTM_LOC" = TO_DATE('03-9月 -12', 'DD-MON-RR') and "CREATED_TIME_ZONE" 
IS NULL and "UPDATED_BY_USER" = 'FIN_TWPEI05' and "UPDATED_OFFICE" = 'TP_FIN_DP' and "UP
DATED_DTM_LOC" = TO_DATE('03-9月 -12', 'DD-MON-RR') and "UPDATED_TIME_ZONE" IS NULL and "RECORD_VERSION" = '0' and "PRINCIPAL_GROUP_CODE" = 'SIT' 
and "CHECK_NO" = '6822983' and "PRINTED_PERSON" IS NULL and "IS_PRINTED" = '0' and "PRINTED_PERSON_NAME" IS NULL and "PRINTED_DATE" IS NULL and "BANK_EXCHANGE_NO" 
IS NULL and "INVOICE_AMOUNT" IS NULL and "SHORT_OVER_AMOUNT" = '0' and "SAP_STATUS" = '0' and "ARP_ID" IS NULL and ROWID = 'AAATyIAAUAAAMQ7AAH';

...

7  发现对于主、子表FIN_CASH_MOVEMENT、FIN_CASH_DETAIL的误操作分别删除了1390和1911条数据。生成下述的反向SQL，并把SQL脚本交给项目组确认，数据是否正确？

select sql_undo from v$logmnr_contents where table_name='FIN_CASH_MOVEMENT' and operation='DELETE';
SQL_UNDO
---------------------------------------------------------------------------------------------------
insert into "SITCLINE"."FIN_CASH_MOVEMENT"("CASH_MOVEMENT_ID","RP_ID","OFFICE_ID","
STATE_IND","MOVEMENT_TYPE","MOVEMENT_NO","
BANK_MOVEMENT_NO","MOVEMENT_DATE","LEDGER_PARTNER_CODE","
LEDGER_PARTNER_NAME","EXTERNAL_BANK_NAME","
EXTERNAL_BANK_ACCOUNT","EXTERAL_BANK_ACCOUNT_NAME","
INTERNAL_BANK_ID","INTERNAL_BANK_NAME","INTERNAL_BACNT_ID","
INTERNAL_BANK_ACCOUNT_CODE","INTERNAL_BANK_ACCOUNT_NAME","
PRIME_CURRENCY_CODE","PRIME_CURRENCY_VALUE","BASE_CURRENCY_CODE","
BASE_CURRENCY_VALUE","REMARK","REALRP","REALRP_DATE","
REALRP_PERSON","REALRP_PERSON_NAME","DISCOUNT_VALUE","
DISCOUNT_REMARK","RATE_BASE","A
LLOCATION_EVENT_ID","DEPOSIT_DATE","INVOICE_INFO","
CREATED_BY_USER","CREATED_OFFICE","CREATED_DTM_LOC","
CREATED_TIME_ZONE","UPDATED_BY_USER","UPDATED_OFFICE","
UPDATED_DTM_LOC","UPDATED_TIME_ZONE","RECORD_VERSION","
PRINCIPAL_GROUP_CODE","CHECK_NO","PRINTED_PERSON","
IS_PRINTED","PRINTED_PERSON_NAME","PRINTED_DATE","
BANK_EXCHANGE_NO","INVOICE_AMOUNT","SHORT_OVER_AMOUNT","
SAP_STATUS","ARP_ID") values ('2c2881d63987424d01398b77fa2f6345','R','SITTP','0','CHECK','CR201209030138','010060936',TO_DATE('03-9月 -12', 'DD-MON-RR'),'80273312',NULL,NULL,'056637',NULL,'2c2881d63978294201397a864fe30c1e','花旗台灣',
'2c2881d63978294201397a8c72be0c6c','5049328003','SITC STEAMSHIPS CO LTD TAIWAN BRANCH','NTD','10799',NULL,NULL,NULL,'1',
NULL,NULL,NULL,NULL,NULL,NULL,NULL,TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'FIN_TWPEI05','TP_FIN_DP',TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'FIN_TWPEI05','TP_FIN_DP',TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'0','SIT','6822983',
NULL,'0',NULL,NULL,NULL,NULL,'0','0',NULL);

...
select sql_undo from v$logmnr_contents where table_name='FIN_CASH_DETAIL' and operation='DELETE';
SQL_UNDO
---------------------------------------------------------------------------------------------------
insert into "SITCLINE"."FIN_CASH_DETAIL"("CASH_DETAIL_ID","CASH_MOVEMENT_ID","INVOICE_NO","
VESSEL_CODE","VOYAGE_NO","VOYAGE_LEG","BL_NO","
AMOUNT","CURRENCY","RATE","INVOICE_DOC_ID","
FREIGHT_ITEM_ID","CREATED_BY_USER","CREATED_OFFICE","
CREATED_DTM_LOC","CREATED_TIME_ZONE","UPDATED_BY_USER","
UPDATED_OFFICE","UPDATED_DTM_LOC","UPDATED_TIME_ZONE","
RECORD_VERSION","PRINCIPAL_GROUP_CODE") values ('2c2881d63987424d01398b77fa2f6346','2c2881d63987424d01398b77fa2f6345',
'EZ03404580','STKE','1236','N','SITGKESH002049','
10799',NULL,NULL,'2c2881d63987424d01398a8999e22caa',NULL,'FIN_TWPEI05','
TP_FIN_DP',TO_D
ATE('03-9月 -12', 'DD-MON-RR'),NULL,'FIN_TWPEI05','TP_FIN_DP',TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'0','SIT');
...

8  最后，项目组确认之后，重新执行反向的SQL脚本，并发邮件过来，确认数据全部找回。

后记：项目组发布出来，确认引起该错误的原因是程序bug，已经修复。从这次的恢复数据过程中，我们说在生产系统上的程序也好，人为操作数据库也好，一定要谨慎。同样，数据库的备份也不容忽视！

 

Oracle splitting partitions简单小结

留下评论 , ,

            本文简单记录在Oracle 10g数据库上对范围分区表的Splitting Partitions测试过程和结论，并不涉及到Oracle数据库中分区技术的详细描述。

1 测试环境及平台：

OS：

[root@localhost ~]# uname -rm
2.6.18-164.el5 x86_64
[root@localhost ~]#

Oracle：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL>

2 创建范围分区表：

SQL> show user;
USER is "SYS"
SQL> create table part_range(id number,name varchar2(30))
  2  partition by range(id)
  3  (partition partmax values less than (maxvalue))
  4  tablespace users;

Table created.

SQL>

3 插入测试数据：

SQL> insert into part_range select object_id,object_name from dba_objects
  2  where object_id<2000;

1953 rows created.

SQL>

4 在分区表part_range上创建2种分区索引：

本地分区索引【Locally partitioned index】：

SQL> create index part_range_id_idx on part_range(id) local;

Index created.

SQL>

全局分区索引【Globally partitioned index】：

SQL> create index part_range_name_idx on part_range(name) tablespace users;

Index created.

SQL>

5 查看分区表信息：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS

SQL>

6 查看索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

7 对分区表part_range执行Splitting partitions【分区分裂】操作：

SQL> alter table part_range split partition partmax at (2000) 
  2  into (partition p1,partition partmax);

Table altered.

SQL>

注意，这里的分区分裂操作临界值是2000，即id<2000的记录将全部重组到p1分区，而partmax分区将为空，即0记录。

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
         0

SQL>

8 再次分别查看分区表、索引信息：

分区表：

SQL>  select table_name,partition_name,tablespace_name from user_tab_partitions
  2   where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

小结：对于分区分裂之后，如果包含有空分区的话，那么对于本地分区索引和全局分区索引都是可用的。这种分区分裂的方式通常也叫做快速分裂【Fast Splitting】，索引不需要rebuild。

9 如果在上述步骤7中，执行的分区分裂操作如下：

SQL> alter table part_range split partition partmax at (1000)
  2  into (partition p1,partition partmax);

Table altered.

SQL>

即分区分裂操作临界值是1000，id<1000的记录将重组到p1分区，id>=1000的记录将重组到partmax分区。也就是此时，分裂出来的p1和partmax这两个分区均不为空。

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
       953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
      1000

SQL>

那么，查看到的分区表、索引信息如下：

分区表：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          UNUSABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          UNUSABLE NO

SQL>

小结：对于分区分裂之后，如果不包含空分区的话，那么对于本地分区索引和全局分区索引都将不可用，索引的状态都变为UNUSABLE。均需要重建：

SQL> alter index PART_RANGE_ID_IDX rebuild partition p1;

Index altered.

SQL> alter index PART_RANGE_ID_IDX rebuild partition partmax;

Index altered.

SQL> alter index PART_RANGE_NAME_IDX rebuild;

Index altered.

SQL>

重建之后，本地分区索引、全局分区索引信息，已由UNUSABLE变为USABLE：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

当然，如果在分裂分区的同时带上UPDATE INDEXES的话，可以在分裂分区的同时重建索引【包含本地分区索引和全局分区索引，状态均为USABLE、VALID】：

SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update indexes;

Table altered.

SQL>

分裂分区之后，表信息：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

而如果在分裂分区的同时带上UPDATE GLOBAL INDEXES的话，可以在分裂分区的同时重建全局分区索引【不包含本地分区索引，只有全局分区索引状态为VALID】，而本地分区索引需要重建：

SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update global indexes;

Table altered.

SQL>

分裂分区之后，表信息：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          UNUSABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

需要注意的是，在分裂分区的同时重建索引，将会消耗更多时间来完成分裂工作，以及消耗更多的系统资源。如果系统资源较为充足的话，可以考虑带上UPDATE INDEXES选项。

10 最后，再看看另外一种比较特殊的情况。在分裂分区的时候，如果将新分区指向新的表空间【由USERS到EXAMPLE表空间】的话，并且分裂之后，包含空分区的情况。即，分裂的语句如下：

SQL> alter table part_range split partition partmax at (2000)
  2  into (partition p1 tablespace example,partition partmax tablespace example);

Table altered.

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
         0

SQL>

那么可以看到分区表：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        EXAMPLE
PART_RANGE                     P1                             EXAMPLE

SQL>

索引分区信息：

SQL>  select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             EXAMPLE                        UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        EXAMPLE                        USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          UNUSABLE NO

SQL>

小结：在分裂分区的时候，如果将新分区指向新的表空间的话，并且分裂之后，即使包含空分区的情况下，只有新空分区的本地索引不需要重建，而含有数据的新分区的本地分区索引以及全局分区索引均需重建。这有别于快速分裂，或者说是快速分裂的一种特殊情况。

本文部分内容参考范大师

8月工作小记二三事

留下评论 , ,

    上个月，应该算是一个较为忙碌的工作月。其中：

    1 公事的一DBA同事离职，所有的数据库这块全部压到我1个人的身上，要看的库多了，任务重了，有些库也只能做到粗犷式管理了；

    2 出差2次，月初1次到上海，月末1次同样到上海；2次差旅虽说都是现场实施Oracle数据库环境的搭建，但都不是很顺利，究其原因：①项目前期同客户需求、准备工作都很有问题。如去之前，问项目组人员是实施RAC的数据库还是配置单实例的库，告知于我是单实例的库，结果一到客户现场方知是实施RAC的数据库；②个人的技术经验匮乏的短板也暴露的很明显了，尤其是存储方面的知识；

    3 快速记录这2次出差上海解决问题的方法：

        ① 月初去上海某客户遇到的问题，客户环境采用Dell R910单台服务器，存储采用Dell MD 3200。在阵列的管理界面将存储映射到主机时，发现找不到主机，原因：犯的是低级错误，Dell MD 3200 支持级联扩展存储，误将主机的SAS卡插到阵列上的SAS OUT扩展口上，而应该将其连接到阵列上的SAS IN口。其次，在做好阵列之后，重启主机、阵列之后，发现主机依然无法找到对应的LUN，解决方法：注释掉/etc/udev/rules.d/90-dm.rules配置文件。

        ②第2次去上海另1客户实施RAC时，同样碰到存储的问题。只是这次的主机是2台IBM X3650M3，存储是IBM System Storage DS3500磁盘阵列柜。在阵列上做好LUN并映射到主机后，还是不能正常发现存储设备，解决办法是安装IBM提供的RDAC补丁包。下述，记录安装RDAC软件包的过程，该软件包需要到IBM官方网站下载对应的操作系统版本和阵列型号的补丁。

     –

[root@node1 ~]# cd linuxrdac-09.03.0C05.0638/
[root@node1 linuxrdac-09.03.0C05.0638]# ll
total 1780
-r-xr-xr-x 1 root root     73 Apr 18 04:31 bootMpp26p.sh
drwxr-xr-x 2 root root   4096 Apr 18 04:31 dracutsetup
-r-xr-xr-x 1 root root   3808 Apr 18 04:31 genfileattributes
-r--r--r-- 1 root root   2603 Apr 18 04:31 genuniqueid.c
-r--r--r-- 1 root root   2603 Apr 18 04:31 hbaCheck
-r--r--r-- 1 root root  15136 Apr 18 04:31 License.txt
-r-xr-xr-x 1 root root   6592 Apr 18 04:31 lsvdev
-r--r--r-- 1 root root  22991 Apr 18 04:31 Makefile
-r--r--r-- 1 root root 150075 Apr 18 04:31 mppCmn_s2tos3.c
-r--r--r-- 1 root root  21321 Apr 18 04:31 mppCmn_SysInterface.c
-r--r--r-- 1 root root    814 Apr 18 04:31 mpp.conf
-r--r--r-- 1 root root 520094 Apr 18 04:31 MPP_hba.c
-r-xr-xr-x 1 root root   5155 Apr 18 04:31 mppiscsi_umountall
drwxr-xr-x 2 root root   4096 Apr 18 04:31 mpp_linux_headers
drwxr-xr-x 2 root root   4096 Apr 18 04:31 mpp_linux_sys_headers
-r--r--r-- 1 root root  57111 Apr 18 04:31 mppLnx26p_sysdep.c
-r--r--r-- 1 root root 175028 Apr 18 04:31 mppLnx26p_upper.c
-r--r--r-- 1 root root 206555 Apr 18 04:31 mppLnx26p_vhba.c
-r--r--r-- 1 root root 185100 Apr 18 04:31 mppLnx26p_vhbaio.c
-r--r--r-- 1 root root  95675 Apr 18 04:31 mppLnx26p_vhbalib.c
-r--r--r-- 1 root root  21610 Apr 18 04:31 mppLnx26p_vhbamisc.c
-r--r--r-- 1 root root  33709 Apr 18 04:31 mppLnx26p_vhbaproc.c
-r--r--r-- 1 root root 120597 Apr 18 04:31 mppLnx26p_vhbatask.c
-r--r--r-- 1 root root   1805 Apr 18 04:31 mpp_rcscript.REDHAT
-r--r--r-- 1 root root   1903 Apr 18 04:31 mpp_rcscript.SUSE
-r-xr-xr-x 1 root root  28522 Apr 18 04:31 mppSupport
-r--r--r-- 1 root root  24245 Apr 18 04:31 Readme.txt
-rw-r--r-- 1 root root   5559 Apr 18 04:31 setupDriver.REDHAT
-r--r--r-- 1 root root   8984 Apr 18 04:31 setupDriver.SUSE
-r--r--r-- 1 root root    261 Apr 18 04:31 setupMpp26p.sh
drwxr-xr-x 2 root root   4096 Apr 18 04:31 utility
[root@node1 linuxrdac-09.03.0C05.0638]# make
make[1]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  CC [M]  /root/linuxrdac-09.03.0C05.0638/MPP_hba.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_upper.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_sysdep.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppCmn_s2tos3.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppCmn_SysInterface.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbamisc.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbatask.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhba.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaproc.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbalib.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaio.o
/root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaio.c: In function a€?__mppLnx_scsi_donea€?:
/root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaio.c:3691: warning: label a€?donea€? defined but not used
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppUpper.o
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppVhba.o
  Building modules, stage 2.
  MODPOST
  CC      /root/linuxrdac-09.03.0C05.0638/mppUpper.mod.o
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppUpper.ko
  CC      /root/linuxrdac-09.03.0C05.0638/mppVhba.mod.o
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppVhba.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
make[1]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  Building modules, stage 2.
  MODPOST
make[1]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
gcc  -I/root/linuxrdac-09.03.0C05.0638 -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_headers/ -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_sys_headers/ -c ./utility/mppUtil.c  -o mppUtil.o
/bin/bash ./genfileattributes bld
gcc  -I/root/linuxrdac-09.03.0C05.0638 -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_headers/ -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_sys_headers/ -c ./utility/mppUtil26p_sysdep.c -o mppUtilSysdep.o
gcc mppUtil.o mppUtilSysdep.o -o mppUtil
gcc -o genuniqueid genuniqueid.c
[root@node1 linuxrdac-09.03.0C05.0638]# make install
make[1]: Entering directory `/root/linuxrdac-09.03.0C05.0638'
make[2]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  Building modules, stage 2.
  MODPOST
make[2]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
make[2]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  Building modules, stage 2.
  MODPOST
make[2]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
/bin/bash ./genfileattributes bld
gcc  -I/root/linuxrdac-09.03.0C05.0638 -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_headers/ -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_sys_headers/ -c ./utility/mppUtil26p_sysdep.c -o mppUtilSysdep.o
gcc mppUtil.o mppUtilSysdep.o -o mppUtil
Checking Host Adapter Configuration...
Detected 1 Emulex Host Adapter Port(s) on the system
Please wait while we modify the system configuration files.
Your kernel version is 2.6.18-194.el5
Preparing to install MPP driver against this kernel version...
Generating module dependencies...
Creating new MPP initrd image...
        You must now edit your boot loader configuration file, /boot/grub/menu.lst, to 
        add a new boot menu, which uses mpp-2.6.18-194.el5.img as the initrd image.
        Now Reboot the system for MPP to take effect.
        The new boot menu entry should look something like this (note that it may 
        vary with different system configuration):

        ...

                title Red Hat Linux (2.6.18-194.el5) with MPP support
                root (hd0,5)
                kernel /vmlinuz-2.6.18-194.el5 ro root=LABEL=RH9
                initrd /mpp-2.6.18-194.el5.img
        ...
MPP driver package has been successfully installed on your system.
make[1]: Leaving directory `/root/linuxrdac-09.03.0C05.0638'
[root@node1 linuxrdac-09.03.0C05.0638]#

 

     4 问题得以解决，也深知自己存在的问题！送给自己一句话以自勉：革命尚未成功，同志仍需努力！