Heim >Datenbank >MySQL-Tutorial >大数据备份和恢复应用案例--通过分区表备份和恢复数据
大数据备份和恢复应用案例--通过分区表备份和恢复数据
- WBOYOriginal
- 2016-06-07 16:10:271428Durchsuche
大数据备份和恢复应用案例--通过分区表备份和恢复数据 海量数据备份和恢复方案 对于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 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.
三、备份载入的原介质
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：数据库排它操作(2)Nächster Artikel：XMPP协议实现即时通讯底层书写（二）--IOSXMPPFrameworkDemo+分