大数据备份和恢复应用案例--通过分区表备份和恢复数据
Jun 07, 2016 pm 04:10 PM
Partition Tablebackupapplicationrecoverdatadata backupCasepass
大数据备份和恢复应用案例--通过分区表备份和恢复数据 海量数据备份和恢复方案 对于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.
三、备份载入的原介质
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
ThinkPHP6数据备份与恢复：保障数据的安全性Aug 13, 2023 am 08:28 AMThinkPHP6数据备份与恢复：保障数据的安全性随着互联网的快速发展，数据已成为一项极其重要的资产。因此，数据的安全性备受关注。在Web应用开发中，数据备份与恢复是确保数据安全的重要一环。在本文中，我们将介绍如何使用ThinkPHP6框架进行数据备份与恢复，以保障数据的安全性。一、数据备份数据备份是指将数据库中的数据以某种方式进行复制或存储。这样即使在数据
如何使用PHP实现网站备份功能Jun 27, 2023 pm 01:32 PM在网站运营过程中，备份是一项非常重要的任务。如果网站有数据丢失或者损失，备份可以为恢复网站提供便利。PHP是一种常用的服务器端编程语言，可以通过编写PHP脚本实现网站的备份功能。本文将介绍如何使用PHP实现网站备份功能。一、备份文件的类型在备份网站的时候，需要备份数据库和网站文件。通常网站文件包括静态文件、程序文件、图片和上传的附件等，而数据库则包含网站的所
PHP中的数据备份May 24, 2023 am 08:01 AM在进行Web开发的过程中，数据的存储和备份无疑是非常重要的一环。面对万一出现的数据丢失或恢复需要，备份是非常必要的。对于PHP这种开源的后端语言，数据的备份同样也有许多可选的方案，下面我们就来详细了解一下PHP中的数据备份。一、数据库备份1.1MYSQLdump工具MYSQLdump是一个备份MYSQL数据库的命令行工具，它通过执行SQL语句将整个数据库或
如何使用Java编写CMS系统的数据备份功能Aug 04, 2023 pm 11:22 PM如何使用Java编写CMS系统的数据备份功能在一个内容管理系统（ContentManagementSystem,CMS）中，数据备份是一个非常重要且必不可少的功能。通过数据备份，我们可以保证系统中的数据在遭受损坏、丢失或错误操作等情况下能够及时恢复，从而确保系统的稳定性和可靠性。本文将介绍如何使用Java编写CMS系统的数据备份功能，并提供相关的代码示
使用PHP和SQLite实现数据备份和恢复策略Jul 28, 2023 pm 12:21 PM使用PHP和SQLite实现数据备份和恢复策略备份和恢复是数据库管理中非常重要的一个环节，它可以保护我们的数据免受意外损坏或丢失的影响。本文将介绍如何使用PHP和SQLite实现数据备份和恢复的策略，帮助我们更好地管理和保护数据库中的数据。首先，我们需要创建一个使用SQLite的数据库，并建立一些测试数据以便后续操作。下面是一个简单的例子：<?php
MySQL中的数据压缩备份技术Jun 15, 2023 pm 05:23 PM随着数据量的不断增大，数据库备份的难度也越来越大。而备份不仅要求数据的完整性和一致性，还要求备份速度和备份文件大小均能满足实际需求。数据压缩备份技术因此应运而生，成为数据库备份必不可少的一种技术手段之一。MySQL是目前最流行的关系型数据库之一，其官方提供的备份工具mysqldump并不能满足压缩备份的需求。因此，本文将介绍使用Linux系统上的压缩命令ta
PHP表单处理：表单数据备份与恢复Aug 07, 2023 pm 10:19 PMPHP表单处理：表单数据备份与恢复引言在网站开发过程中，表单是非常常见的交互方式，用户通过填写表单将数据提交给服务器端处理。然而，有时候用户可能会因为网络问题、浏览器崩溃或其他意外情况导致表单数据丢失，这会给用户的使用体验带来困扰。因此，为了提升用户体验，我们可以通过PHP实现表单数据的自动备份与恢复功能，以确保用户填写的数据不会丢失。表单数据备份当用户在表
TiDB和MySQL的数据备份与恢复策略对比Jul 12, 2023 pm 11:01 PMTiDB和MySQL的数据备份与恢复策略对比引言：在互联网时代，数据成为了企业最重要的资产之一，因此数据备份与恢复策略显得尤为重要。TiDB和MySQL作为常用的关系型数据库管理系统，具备了高性能和可靠性等特点，但在数据备份和恢复方面还是有所差异。本文将针对TiDB和MySQL的数据备份与恢复策略进行比较，并提供相关的代码示例进行解析。一、数据备份策略比较T
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
3 weeks agoByDDD
R.E.P.O. Save File Location: Where Is It & How to Protect It?
3 weeks agoByDDD
Hot Tools
SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft
MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
