大数据备份和恢复应用案例--通过分区表备份和恢复数据海量数据备份和恢复方案对于olap的数据库的业务特点，是将批量的数据加载入库，然后对这些数据进行分析处理
大数据备份和恢复应用案例--通过分区表备份和恢复数据
海量数据备份和恢复方案
对于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 tbs317: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 select count(*) from t1; count(*)---------- 282617: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 3122、传输表空间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_indx17: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.logimport: 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_indx18: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.三、备份载入的原介质