大数据备份和恢复应用案例–通过分区表备份和恢复数据 海量数据备份和恢复方案 对于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 TBS3 17:31:33 SCOTT@ <b style="color:transparent">本文来源gao@!dai!ma.com搞$$代^@码!网!</b>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(*)---------- 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.
三、备份载入的原介质
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：大数据备份和恢复应用案例–通过分区表备份和恢复数据
转载请注明原文链接：大数据备份和恢复应用案例–通过分区表备份和恢复数据
