大資料備份和恢複應用案例--通過分區表備份和恢複資料，應用案例分區表

大資料備份和恢複應用案例--通過分區表備份和恢複資料，應用案例分區表

大資料備份和恢複應用案例--通過分區表備份和恢複資料

海量資料備份和恢複方案

     對於OLAP的資料庫的業務特點，是將批量的資料載入入庫，然後對這些資料進行分析處理，比如報表或者資料採礦，最後給業務提供一種決策支援；另外，這類資料庫的資料即時性非常高，一旦這些資料處理完畢後，就很少再次使用（有時，也需要對這類資料進行查詢）。

 對於OLAP資料庫的備份和恢複可以考慮這樣幾種方案：

1、使用分散式資料庫

    將資料分布到多個庫裡，當資料庫恢複時，只需要恢複單個庫的資料，大大節省恢復。

2、結合分區技術，以傳輸資料表空間方式進行備份和恢複

1、建立分區表，將分區儲存在不同的資料表空間[oracle@RH6 ~]$sqlplus '/as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 18 17:15:47 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options17:15:47 SYS@ prod >create tablespace tbs117:16:03   2  datafile '/dsk1/oradata/prod/tbs1.dbf' size 10m;Tablespace created. 17:17:00 SYS@ prod >create tablespace tbs217:17:11   2  datafile '/dsk2/oradata/prod/tbs2.dbf' size 10m;Tablespace created. 17:17:49 SYS@ prod >create tablespace tbs317:17:57   2  datafile '/dsk3/oradata/prod/tbs3.dbf' size 10m;Tablespace created. 17:18:35 SYS@ prod >create tablespace tbs1_indx17:18:49   2  datafile '/dsk1/oradata/prod/tbs1_indx.dbf' size 10m;Tablespace created. 17:19:43 SYS@ prod >create tablespace tbs2_indx17:19:54   2  datafile '/dsk2/oradata/prod/tbs2_indx.dbf' size 10m;Tablespace created. 17:20:18 SYS@ prod >create tablespace tbs3_indx17:20:30   2  datafile '/dsk3/oradata/prod/tbs3_indx.dbf' size 10m;Tablespace created. 17:22:12 SYS@ prod >select file_id,file_name,tablespace_name from dba_data_files   FILE_ID FILE_NAME                                          TABLESPACE_NAME---------- -------------------------------------------------- ------------------------------        11 /dsk1/oradata/prod/tbs1.dbf                        TBS1        12 /dsk2/oradata/prod/tbs2.dbf                        TBS2        13 /dsk3/oradata/prod/tbs3.dbf                        TBS3         4 /u01/app/oracle/oradata/prod/users01.dbf           USERS         3 /u01/app/oracle/oradata/prod/undotbs01.dbf         UNDOTBS1         2 /u01/app/oracle/oradata/prod/sysaux01.dbf          SYSAUX         1 /u01/app/oracle/oradata/prod/system01.dbf          SYSTEM         5 /u01/app/oracle/oradata/prod/example01.dbf         EXAMPLE         6 /u01/app/oracle/oradata/prod/users02.dbf           USERS         7 /u01/app/oracle/oradata/prod/catatbs1.dbf          CATATBS         8 /u01/app/oracle/oradata/prod/perfertbs1.dbf        PERFERTBS         9 /u01/app/oracle/oradata/prod/oggtbs1.dbf           OGG_TBS        10 /u01/app/oracle/oradata/prod/test1.dbf             TEST1        14 /dsk1/oradata/prod/tbs1_indx.dbf                   TBS1_INDX        15 /dsk2/oradata/prod/tbs2_indx.dbf                   TBS2_INDX        16 /dsk3/oradata/prod/tbs3_indx.dbf                   TBS3_INDX 建立分區表及索引：17:26:41 SCOTT@ prod >create table t1(id int,name varchar2(1000))17:26:57   2   partition by range(id)17:27:01   3  (partition p1 values less than(1000) tablespace tbs1,17:27:13   4  partition p2 values less than(2000) tablespace tbs2,17:27:23   5  partition p3 values less than(maxvalue) tablespace tbs3);Table created. 17:30:33 SCOTT@ prod >create index t1_indx on t1(id) local  2  (  3  partition p1 tablespace tbs1_indx,  4  partition p2 tablespace tbs2_indx,  5* partition p3 tablespace tbs3_indx )/ 17:30:37 SCOTT@ prod >select partition_name,tablespace_name from user_segments where segment_name='T1';PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------P1                             TBS1P2                             TBS2P3                             TBS3 17:31:33 SCOTT@ prod >select partition_name,tablespace_name from user_segments where segment_name='T1_INDX';PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------P1                             TBS1_INDXP2                             TBS2_INDXP3                             TBS3_INDX 插入資料：17:34:09 SYS@ prod >begin17:34:26   2  for i in 1..3 loop17:34:32   3  insert into scott.t1 select object_id*i,object_name from dba_objects where object_id <1000;17:34:43   4  end loop;17:34:51   5  commit;17:34:57   6  end;17:35:02   7  /PL/SQL procedure successfully completed. 17:32:08 SCOTT@ prod >select count(*) from t1;  COUNT(*)----------      2826 17:36:52 SCOTT@ prod >select 'p1',count(*) from t1 partition(p1)17:37:42   2  union17:37:47   3  select 'p2',count(*) from t1 partition(p2)17:38:11   4  union17:38:13   5  select 'p3',count(*) from t1 partition(p3);'P1'                               COUNT(*)-------------------------------- ----------p1                                     1740p2                                      774p3                                      312 2、傳輸資料表空間17:35:04 SYS@ prod >alter tablespace tbs1 read only;Tablespace altered. 17:41:02 SYS@ prod >alter tablespace tbs1_indx read only;Tablespace altered. 17:39:14 SYS@ prod >create directory tbs_dir as '/home/oracle/data';Directory created. 17:40:30 SYS@ prod >grant read,write on directory tbs_dir to scott;Grant succeeded. [oracle@RH6 data]$ expdp system/oracle directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.logExport: Release 11.2.0.1.0 - Production on Tue Nov 18 17:44:25 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.logORA-39123: Data Pump transportable tablespace job abortedORA-39187: The transportable set is not self-contained, violation list isORA-39901: Partitioned table SCOTT.T1 is partially contained in the transportable set.ORA-39921: Default Partition (Table) Tablespace USERS for T1 not contained in transportable set.Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 17:44:49傳輸資料表空間出錯，資料表空間處於非自包含模式：18:14:47 SYS@ prod >exec dbms_tts.transport_set_check('TBS1',true);PL/SQL procedure successfully completed. 18:17:49 SYS@ prod >select * from transport_set_violations;VIOLATIONS------------------------------------------------------------------------------------------------------------------------ORA-39921: Default Partition (Table) Tablespace USERS for T1 not contained in transportable set..ORA-39901: Partitioned table SCOTT.T1 is partially contained in the transportable set. 解決方案，需要建立一個暫存資料表和一個暫存資料表索引，將分區和分區索引交換到暫存資料表和暫存資料表索引資料表空間上，然後到處暫存資料表和暫存資料表索引。由於暫存資料表不是分區表，它們呢所在的資料表空間符合自包含條件。17:45:37 SCOTT@ prod >create table t1_tmp as select * from t1 where 1=3;Table created.Elapsed: 00:00:00.2017:45:58 SCOTT@ prod >create index t1_tmp_indx on t1_tmp(id);Index created.17:46:33 SCOTT@ prod >select segment_name,tablespace_name from user_segments17:47:18   2   where segment_name in ('T1_TMP','T1_TMP_INDX');SEGMENT_NAME                                                                      TABLESPACE_NAME--------------------------------------------------------------------------------- ------------------------------T1_TMP                                                                            USERST1_TMP_INDX                                                                       USERS 將分區表交換到暫存資料表：17:48:32 SCOTT@ prod >alter table t1 exchange partition p1 with table t1_tmp including indexes;Table altered. 17:49:02 SCOTT@ prod >select segment_name,tablespace_name from user_segments17:49:35   2   where segment_name in ('T1_TMP','T1_TMP_INDX');SEGMENT_NAME                                                                      TABLESPACE_NAME--------------------------------------------------------------------------------- ------------------------------T1_TMP                                                                                 TBS1T1_TMP_INDX                                                                       TBS1_INDX 17:50:44 SYS@ prod >exec dbms_tts.transport_set_check('TBS1',true);PL/SQL procedure successfully completed. 17:51:59 SYS@ prod >select * from transport_set_violations;no rows selected已經符合自包含條件 [oracle@RH6 data]$ expdp system/oracle directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.logExport: Release 11.2.0.1.0 - Production on Tue Nov 18 17:52:55 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:  /home/oracle/data/p1.dmp******************************************************************************Datafiles required for transportable tablespace TBS1:  /dsk1/oradata/prod/tbs1.dbfDatafiles required for transportable tablespace TBS1_INDX:  /dsk1/oradata/prod/tbs1_indx.dbfJob "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:54:17資料表空間匯出成功！17:56:16 SYS@ prod >select file_name,tablespace_name from dba_data_files where tablespace_name in ('TBS1','TBS1_INDX');FILE_NAME                                          TABLESPACE_NAME-------------------------------------------------- ------------------------------/dsk1/oradata/prod/tbs1.dbf                        TBS1/dsk1/oradata/prod/tbs1_indx.dbf                   TBS1_INDX [oracle@RH6 ~]$ cp /dsk1/oradata/prod/tbs1* /home/oracle/data[oracle@RH6 ~]$ ls -lh /home/oracle/datatotal 21M-rw-r----- 1 oracle oinstall  92K Nov 18 17:54 p1.dmp-rw-r--r-- 1 oracle oinstall 1.4K Nov 18 17:54 p1.log-rw-r----- 1 oracle oinstall  11M Nov 18 17:57 tbs1.dbf-rw-r----- 1 oracle oinstall  11M Nov 18 17:57 tbs1_indx.dbf然後再將資料表空間的資料檔案進行備份，由於資料表空間傳輸，只是匯出了metadata，所以資料量非常小，速度非常快。 3、資料恢複17:58:29 SYS@ prod >drop tablespace tbs1 including contents and datafiles;Tablespace dropped. 17:58:55 SYS@ prod >drop tablespace tbs1_indx  including contents and datafiles;Tablespace dropped. 17:59:12 SYS@ prod >col segment_name for a2017:59:42 SYS@ prod >col partition_name for a1017:59:49 SYS@ prod >col tablespace_name for a1017:59:59 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments18:00:32   2   where segment_name in ('T1','T1_INDX') order by 2;SEGMENT_NAME         PARTITION_ TABLESPACE-------------------- ---------- ----------T1                   P1         USERST1_INDX              P1         USERST1_INDX              P2         TBS2_INDXT1                   P2         TBS2T1_INDX              P3         TBS3_INDXT1                   P3         TBS36 rows selected. 拷貝備份資料檔案到資料庫下，進行資料匯入[oracle@RH6 oradata]$ cp /home/oracle/data/tbs1*.dbf /u01/app/oracle/oradata/prod/ [oracle@RH6 data]$ impdp system/oracle directory=tbs_dir dumpfile=p1.dmp transport_datafiles='/u01/app/oracle/oradata/prod/tbs1.dbf','/u01/app/oracle/oradata/prod/tbs1_indx.dbf' logfile=imp.log Import: Release 11.2.0.1.0 - Production on Tue Nov 18 18:06:22 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=tbs_dir dumpfile=p1.dmp transport_datafiles=/u01/app/oracle/oradata/prod/tbs1.dbf,/u01/app/oracle/oradata/prod/tbs1_indx.dbf logfile=imp.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:06:37 資料匯入成功18:01:03 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments18:07:37   2  where segment_name in ('T1_TMP','T1_TMP_INDX');SEGMENT_NAME         PARTITION_ TABLESPACE-------------------- ---------- ----------T1_TMP                          TBS1T1_TMP_INDX                     TBS1_INDX 18:09:40 SCOTT@ prod >alter table t1 exchange partition p1 with table t1_tmp including indexes;Table altered. 18:08:15 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments18:10:46   2  where segment_name in ('T1','T1_INDX') order by 2;SEGMENT_NAME         PARTITION_ TABLESPACE-------------------- ---------- ----------T1                   P1         TBS1T1_INDX              P1         TBS1_INDXT1_INDX              P2         TBS2_INDXT1                   P2         TBS2T1_INDX              P3         TBS3_INDXT1                   P3         TBS36 rows selected. 訪問正常（索引亦匯入成功）18:12:07 SCOTT@ prod >col name for a5018:12:19 SCOTT@ prod >r  1* select * from t1 where id=4        ID NAME---------- --------------------------------------------------         4 C_OBJ#         4 TAB$Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 1229066337--------------------------------------------------------------------------------------------------------------| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                   |         |     2 |  1030 |     1   (0)| 00:00:01 |       |       ||   1 |  PARTITION RANGE SINGLE            |         |     2 |  1030 |     1   (0)| 00:00:01 |     1 |     1 ||   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T1      |     2 |  1030 |     1   (0)| 00:00:01 |     1 |     1 ||*  3 |    INDEX RANGE SCAN                | T1_INDX |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("ID"=4)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          5  consistent gets          0  physical reads          0  redo size        524  bytes sent via SQL*Net to client        419  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed           18:11:05 SYS@ prod >alter tablespace tbs1 read write;Tablespace altered.Elapsed: 00:00:02.1018:14:34 SYS@ prod >alter tablespace tbs1_indx read write;Tablespace altered.

三、備份載入的原介質

  以上文檔參考：譚懷遠《讓Oracle跑的更快2-基于海量資料的資料庫設計和最佳化》，感謝作者！

歡迎大家訪問我的個人網站 萌萌的IT人