大数据备份和恢复应用案例--通过分区表备份和恢复数据

电玩女神 2022-08-14 01:42 230阅读 0赞

海量数据备份和恢复方案

对于OLAP的数据库的业务特点，是将批量的数据加载入库，然后对这些数据进行分析处理，比如报表或者数据挖掘，最后给业务提供一种决策支持；另外，这类数据库的数据实时性非常高，一旦这些数据处理完毕后，就很少再次使用（有时，也需要对这类数据进行查询）。

对于OLAP数据库的备份和恢复可以考虑这样几种方案：

1、使用分布式数据库

将数据分布到多个库里，当数据库恢复时，只需要恢复单个库的数据，大大节省恢复时间。

[![wKiom1Rr-0eyHB5SAAJmv2cd82o348.jpg][]][wKiom1Rr-0eyHB5SAAJmv2cd82o348.jpg]

2、结合分区技术，以传输表空间方式进行备份和恢复

1、建立分区表，将分区存储在不同的表空间
    [oracle@RH6 ~]$sqlplus '/as sysdba'
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 18 17:15:47 2014
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    17:15:47 SYS@ prod >create tablespace tbs1
    17:16:03   2  datafile '/dsk1/oradata/prod/tbs1.dbf' size 10m;
    Tablespace created.
     
    17:17:00 SYS@ prod >create tablespace tbs2
    17:17:11   2  datafile '/dsk2/oradata/prod/tbs2.dbf' size 10m;
    Tablespace created.
     
    17:17:49 SYS@ prod >create tablespace tbs3
    17:17:57   2  datafile '/dsk3/oradata/prod/tbs3.dbf' size 10m;
    Tablespace created.
     
    17:18:35 SYS@ prod >create tablespace tbs1_indx
    17:18:49   2  datafile '/dsk1/oradata/prod/tbs1_indx.dbf' size 10m;
    Tablespace created.
     
    17:19:43 SYS@ prod >create tablespace tbs2_indx
    17:19:54   2  datafile '/dsk2/oradata/prod/tbs2_indx.dbf' size 10m;
    Tablespace created.
     
    17:20:18 SYS@ prod >create tablespace tbs3_indx
    17: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                             TBS1
    P2                             TBS2
    P3                             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_INDX
    P2                             TBS2_INDX
    P3                             TBS3_INDX
     
    插入数据：
    17:34:09 SYS@ prod >begin
    17:34:26   2  for i in 1..3 loop
    17: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  union
    17:37:47   3  select 'p2',count(*) from t1 partition(p2)
    17:38:11   4  union
    17:38:13   5  select 'p3',count(*) from t1 partition(p3);
    'P1'                               COUNT(*)
    -------------------------------- ----------
    p1                                     1740
    p2                                      774
    p3                                      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.log
    Export: Release 11.2.0.1.0 - Production on Tue Nov 18 17:44:25 2014
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.log
    ORA-39123: Data Pump transportable tablespace job aborted
    ORA-39187: The transportable set is not self-contained, violation list is
    ORA-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.20
    17: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_segments
    17:47:18   2   where segment_name in ('T1_TMP','T1_TMP_INDX');
    SEGMENT_NAME                                                                      TABLESPACE_NAME
    --------------------------------------------------------------------------------- ------------------------------
    T1_TMP                                                                            USERS
    T1_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_segments
    17:49:35   2   where segment_name in ('T1_TMP','T1_TMP_INDX');
    SEGMENT_NAME                                                                      TABLESPACE_NAME
    --------------------------------------------------------------------------------- ------------------------------
    T1_TMP                                                                                 TBS1
    T1_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.log
    Export: Release 11.2.0.1.0 - Production on Tue Nov 18 17:52:55 2014
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.log
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/TABLE
    Processing object type TRANSPORTABLE_EXPORT/INDEX
    Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Master 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.dbf
    Datafiles required for transportable tablespace TBS1_INDX:
      /dsk1/oradata/prod/tbs1_indx.dbf
    Job "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/data
    total 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 a20
    17:59:42 SYS@ prod >col partition_name for a10
    17:59:49 SYS@ prod >col tablespace_name for a10
    17:59:59 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments
    18:00:32   2   where segment_name in ('T1','T1_INDX') order by 2;
    SEGMENT_NAME         PARTITION_ TABLESPACE
    -------------------- ---------- ----------
    T1                   P1         USERS
    T1_INDX              P1         USERS
    T1_INDX              P2         TBS2_INDX
    T1                   P2         TBS2
    T1_INDX              P3         TBS3_INDX
    T1                   P3         TBS3
    6 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 2014
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    Starting "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.log
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/TABLE
    Processing object type TRANSPORTABLE_EXPORT/INDEX
    Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Job "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_segments
    18:07:37   2  where segment_name in ('T1_TMP','T1_TMP_INDX');
    SEGMENT_NAME         PARTITION_ TABLESPACE
    -------------------- ---------- ----------
    T1_TMP                          TBS1
    T1_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_segments
    18:10:46   2  where segment_name in ('T1','T1_INDX') order by 2;
    SEGMENT_NAME         PARTITION_ TABLESPACE
    -------------------- ---------- ----------
    T1                   P1         TBS1
    T1_INDX              P1         TBS1_INDX
    T1_INDX              P2         TBS2_INDX
    T1                   P2         TBS2
    T1_INDX              P3         TBS3_INDX
    T1                   P3         TBS3
    6 rows selected.
     
    访问正常（索引亦导入成功）
    18:12:07 SCOTT@ prod >col name for a50
    18:12:19 SCOTT@ prod >r
      1* select * from t1 where id=4
            ID NAME
    ---------- --------------------------------------------------
             4 C_OBJ#
             4 TAB$
    Elapsed: 00:00:00.00
    Execution 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.10
    18:14:34 SYS@ prod >alter tablespace tbs1_indx read write;
    Tablespace altered.

三、备份载入的原介质

[![wKioL1RsASeisT3lAAEZkx474KQ032.jpg][]][wKioL1RsASeisT3lAAEZkx474KQ032.jpg]

[![wKiom1RsARmQiCSkAAG2w6nkODw112.jpg][]][wKiom1RsARmQiCSkAAG2w6nkODw112.jpg]  以上文档参考：谭怀远《让Oracle跑的更快2-基于海量数据的数据库设计和优化》，感谢作者！

[![041405283617635.gif][]][041405283617635.gif 1]

欢迎大家访问我的个人网站 [萌萌的IT人][IT]

[wKiom1Rr-0eyHB5SAAJmv2cd82o348.jpg]: /images/20220810/4c2e7f36b1b6488a933a093237a26100.png
[wKioL1RsASeisT3lAAEZkx474KQ032.jpg]: /images/20220810/b9471b1642f04f51a3d48a08b80b23a6.png
[wKiom1RsARmQiCSkAAG2w6nkODw112.jpg]: /images/20220810/2b35d62a0da6480cb8a94d4ad124392a.png
[041405283617635.gif]: /images/20220810/d29cdaa169f34a6db219a2486a6138c5.png
[041405283617635.gif 1]: http://www.itmmd.com/boke.html
[IT]: http://www.itmmd.com