Big data backup and recovery application case-Using Partitioned Tables to back up and restore Data

Big data backup and recovery application case-Using Partitioned Tables to back up and restore Data

Big data backup and recovery application case-back up and restore data through a partitioned table

Massive Data backup and recovery Solutions

The business feature of OLAP databases is to load batch data into the database, and then analyze and process the data, such as reports or data mining. Finally, it provides a decision support for the business; in addition, the data in such databases is highly Real-time. Once the data is processed, it is rarely used again (sometimes, such data needs to be queried ).

You can consider the following solutions for backup and recovery of OLAP databases:

1. Use distributed databases

Data is distributed to multiple databases. When the database is restored, only the data of a single database needs to be restored, greatly saving the recovery time.

2. backup and recovery using the partition technology as the transmission tablespace

1. Create a partition table and store partitions in different tablespaces [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,200 9, 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 10 M; Tablespace created. 17:17:00 SYS @ prod> create tablespace tbs217: 17: 11 2 datafile '/dsk2/oradata/prod/tbs2.dbf 'size 10 m; Tablespace created. 17:17:49 SYS @ prod> create tablespace tbs317: 17: 57 2 datafile '/dsk3/oradata/prod/tbs3.dbf' size 10 m; Tablespace created. 17:18:35 SYS @ prod> create tablespace tbs1_indx17: 18: 49 2 datafile '/dsk1/oradata/prod/tbs1_indx.dbf' size 10 m; Tablespace created. 17: 19: 43 SYS @ prod> create tablespace tbs2_indx17: 19: 54 2 datafile '/dsk2/oradata/prod/tbs2_indx.dbf' size 10 m; Tablespace created. 17:20:18 SYS @ prod> create tablespace tbs3_indx17: 20: 30 2 datafile '/dsk3/oradata/prod/tbs3_indx.dbf' size 10 m; Tablespace created. 17:22:12 SYS @ prod> select file_id, file_name, tablespace_name from dba_data_files FILE_ID FILE_NAME TABLESPACE_NAME ---------------------------- 20171000011/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/export UNDOTBS1 2/u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX 1/u01/app/ oracle/oradata/prod/system01.dbf SYSTEM 5/u01/app/oracle/orada Ta/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/ogogg_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 create a partition table and index: 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. at 17:30:33 SCOTT @ prod> create index t1_indx on t1 (id) local 2 (3 partition p1 tablespace tbs1_indx, 4 partition p2 tablespace partition, 5 * partition p3 tablespace tbs3_indx)/17:30:37 SCOTT @ prod> select partition_name, tablespace_name from user_segments where segment_name = '1 '; PARTITION_NAME TABLESPACE_NAME ------------------------------ P1 TBS1P2 TBS2P3 TBS3 17:31:33 SCOTT @ prod> select partition_name, tablespace_name from u Ser_segments where segment_name = 'T1 _ INDX '; PARTITION_NAME TABLESPACE_NAME when P1 TBS1_INDXP2 TBS2_INDXP3 TBS3_INDX insert data: 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 <1000; 17:34:43 4 end loop; 17:34:51 5 commit; 17:34:57 6 end; 17:35:02 7/PL/S QL 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 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. Transmit tablespace 17: 35: 04 SYS @ prod> alter tablespace tbs1 read only; Tablespace altered. at 17:41:02 SYS @ prod> alter tablespace tbs1_indx read only; Tablespace altered. at 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_tab Lespaces = tbs1, tbs1_indx logfile = p1.logExport: Release 11.2.0.1.0-Production on Tue Nov 18 17:44:25 2014 Copyright (c) 1982,200 9, 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 An error occurred while the tablespace was in non-self-contained mode: 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 executed ---------------------------------------------------------------------------------------------------------------------- -- 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. to solve this problem, you need to create a temporary table and a temporary table index, switch the partition and partition indexes to the temporary table and the temporary table index tablespace, and then the temporary table and the temporary table index are everywhere. Because temporary tables are not Partitioned Tables, their tablespace must meet the self-contained conditions. At 17:45:37 SCOTT @ prod> create table t1_tmp as select * from t1 where 1 = 3; Table created. elapsed: 00:00:00. TIPS: 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 ---------------------------------------------------------- ----------------------- Partition T1_TMP USERST1_TMP_INDX USERS: swap partition tables to temporary tables: 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 comment --------------------------------- Required bytes 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 already meets the self-contained conditions [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 2014 Copyright (c) 1982,200 9, 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/********* di Rectory = tbs_dir dumpfile = p1.dmp transport_tablespaces = tbs1, tbs1_indx logfile = jsonobject type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/jsonobject type conflict/POST_INSTANCE/dynamic table "SYSTEM ". "SYS_EXPORT_TRA NSPORTABLE_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 ta Blespace TBS1_INDX:/dsk1/oradata/prod/tbs1_indx.dbfJob "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:54:17 the tablespace is exported successfully! 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 partition ----------------------------/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 92 K Nov 18 p1.dmp-rw-r -- 1 oracle oinstall 1.4 K Nov 18 p1.log-rw- r ----- 1 oracle oinstall 11 M Nov 18 tbs1.dbf-rw-r ----- 1 oracle oinstall 11 M Nov 18 tbs1_indx.dbf then back up the data files in the tablespace, as the tablespace is transmitted, only metadata is exported, so the data volume is very small and the speed is very fast. 3. Data Recovery 17:58:29 SYS @ prod> drop tablespace tbs1 including contents and datafiles; Tablespace dropped. at 17:58:55 SYS @ prod> drop tablespace tbs1_indx including contents and datafiles; Tablespace dropped. at 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. copy the backup data file to the database and import the data to [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,200 9, 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/jsonobject type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/jsonobject type TRANSPORTABLE_EXPORT/POST_INSTANCE/category "SYSTEM ". "success" successfully completed at 18:06:37 data import succeeded 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 before -------------- T1_TMP when 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 ready P2 TBS2_INDXT1 P2 TBS2T1_INDX P3 TBS3_INDXT1 P3 TBS36 rows selected. normal access (index imported) 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. 00 Execution Plan -------------------------------------------------------- Plan hash value: 1229066337 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 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 defaults 0 recursive cballs 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. SYS @ prod> alter tablespace tbs1_indx read write; Tablespace altered.

Iii. Back up the loaded original media

For more information, see Tan Huaiyuan "Making Oracle run faster 2-database design and optimization based on massive data". Thank you!

Welcome to my personal website