大数据备份和恢复应用案例--通过分区表备份和恢复数据
大数据备份和恢复应用案例--通过分区表备份和恢复数据海量数据备份和恢复方案对于OLAP的数据库的业务特点，是将批量的数据加载入库，然后对这些数据进行分析处理，比如报表或者数据挖掘，最后给业务提供一种决策支持；另外，这类数据库的数据实时性非常高，一旦这些数据处理完毕后，就很少再次使用（有时，也需要对这类数据进行查询）。对于OLAP数据库的备份和恢复可以考虑这样几种方案：1、使用分布式数据库将数据分布到多个库里，当数据库恢复时，只需要恢复单个库的数据，大大节省恢复时间。http://www.2cto.com/uploadfile/Collfiles/20141120/2014112009265333.jpg2、结合分区技术，以传输表空间方式进行备份和恢复
1、建立分区表，将分区存储在不同的表空间$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 2datafile '/dsk1/oradata/prod/tbs1.dbf' size 10m;Tablespace created. 17:17:00 SYS@ prod >create tablespace tbs217:17:11 2datafile '/dsk2/oradata/prod/tbs2.dbf' size 10m;Tablespace created. 17:17:49 SYS@ prod >create tablespace tbs317:17:57 2datafile '/dsk3/oradata/prod/tbs3.dbf' size 10m;Tablespace created. 17:18:35 SYS@ prod >create tablespace tbs1_indx17:18:49 2datafile '/dsk1/oradata/prod/tbs1_indx.dbf' size 10m;Tablespace created. 17:19:43 SYS@ prod >create tablespace tbs2_indx17:19:54 2datafile '/dsk2/oradata/prod/tbs2_indx.dbf' size 10m;Tablespace created. 17:20:18 SYS@ prod >create tablespace tbs3_indx17:20:30 2datafile '/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 4partition p2 values less than(2000) tablespace tbs2,17:27:23 5partition p3 values less than(maxvalue) tablespace tbs3);Table created. 17:30:33 SCOTT@ prod >create index t1_indx on t1(id) local2(3partition p1 tablespace tbs1_indx,4partition 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 2for i in 1..3 loop17:34:32 3insert into scott.t1 select object_id*i,object_name from dba_objects where object_id <1000;17:34:43 4end loop;17:34:51 5commit;17:34:57 6end;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 2union17:37:47 3select 'p2',count(*) from t1 partition(p2)17:38:11 4union17:38:13 5select '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.
页:
[1]