Home >Database >Mysql Tutorial >大数据备份和恢复应用案例--通过分区表备份和恢复数据

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

WBOY
WBOYOriginal
2016-06-07 16:10:271430browse

大数据备份和恢复应用案例--通过分区表备份和恢复数据 海量数据备份和恢复方案 对于OLAP的数据库的业务特点,是将批量的数据加载入库,然后对这些数据进行分析处理,比如报表或者数据挖掘,最后给业务提供一种决策支持;另外,这类数据库的数据实时性非常高

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

海量数据备份和恢复方案

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

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

1、使用分布式数据库

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

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 &#39;p1&#39;,count(*) from t1 partition(p1)
17:37:42   2  union
17:37:47   3  select &#39;p2&#39;,count(*) from t1 partition(p2)
17:38:11   4  union
17:38:13   5  select &#39;p3&#39;,count(*) from t1 partition(p3);
&#39;P1&#39;                               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 &#39;/home/oracle/data&#39;;
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(&#39;TBS1&#39;,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 (&#39;T1_TMP&#39;,&#39;T1_TMP_INDX&#39;);
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 (&#39;T1_TMP&#39;,&#39;T1_TMP_INDX&#39;);
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
T1_TMP                                                                                 TBS1
T1_TMP_INDX                                                                       TBS1_INDX
 
17:50:44 SYS@ prod >exec dbms_tts.transport_set_check(&#39;TBS1&#39;,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 (&#39;TBS1&#39;,&#39;TBS1_INDX&#39;);
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 (&#39;T1&#39;,&#39;T1_INDX&#39;) 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=&#39;/u01/app/oracle/oradata/prod/tbs1.dbf&#39;,&#39;/u01/app/oracle/oradata/prod/tbs1_indx.dbf&#39; 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 (&#39;T1_TMP&#39;,&#39;T1_TMP_INDX&#39;);
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 (&#39;T1&#39;,&#39;T1_INDX&#39;) 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

 

wKiom1RsARmQiCSkAAG2w6nkODw112.jpg

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn