ZT oracle 分割槽表資料定期遷移到其他資料庫測試方案
--新資料庫的規劃的exp_test的設計：
--第一次使用，建立全部三個資料表空間和索引表空間，以後每週增加下週要使用的新分割槽，移出上上週的資料到31資料庫
--計劃表exp_test是按照日期建立的每週一個的範圍分割槽，將每週的週一的日期拼在分割槽的名字裡面，
--便於自動處理。
--第一次使用，建立三個的資料表空間和索引表空間，大小按照每週的容量建，減少表空間自動擴充套件對效能的消耗
--以後只是刪除舊的分割槽，對應的表空間複用，這樣不用每次建立表空間，可以節省建立表空間的時間，熱備份的指令碼也不用做成動態的。
--資料表空間，按照每個10G建立
CREATE TABLESPACE PEXPTEST_DATA_TB1 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_data_tb1.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE PEXPTEST_DATA_TB2 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_data_tb2.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE PEXPTEST_DATA_TB3 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_data_tb3.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;
--索引表空間，按照每個4G建立
CREATE TABLESPACE PEXPTEST_INDEX_TB1 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_index_tb1.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE PEXPTEST_INDEX_TB2 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_index_tb2.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE PEXPTEST_INDEX_TB3 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_index_tb3.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;
-----建立表，假設從20050801上線，預設建立三個分割槽，這樣定期執行的任務從第二週的資料寫滿以後開始執行
CREATE TABLE dwtest.EXP_TEST
(
SSN VARCHAR2(24 BYTE) NOT NULL,
...
)
PARTITION BY RANGE (CREATE_TIME)
(PARTITION PEXPTEST_DATA_20050801 VALUES LESS THAN (TO_DATE('2005/08/08','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB1,
PARTITION PEXPTEST_DATA_20050808 VALUES LESS THAN (TO_DATE('2005/08/15','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB2,
PARTITION PEXPTEST_DATA_20050815 VALUES LESS THAN (TO_DATE('2005/08/22','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB3)
;
-----建立索引
CREATE INDEX dwtest.PEXPTEST_SSN ON dwtest.EXP_TEST
(SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
LOCAL (
PARTITION PEXPTEST_DATA_20050801 tablespace PEXPTEST_INDEX_TB1,
PARTITION PEXPTEST_DATA_20050808 tablespace PEXPTEST_INDEX_TB2,
PARTITION PEXPTEST_DATA_20050815 tablespace PEXPTEST_INDEX_TB3)
NOPARALLEL;
--至此，應用可以向新表中填寫資料。
--規劃在第三週的週一開始執行增加新分割槽，移出老分割槽的任務，以下是手工執行的指令碼，可以透過定義自動執行的任務完成。
--首先新增加分割槽，保證下週可用，不會因為資料轉出有問題而耽擱了前臺應用的填寫
------增加新分割槽
----新增分割槽（這裡有點疑惑，新增分割槽的時候不能指定索引分割槽，需要再rebuild，索性沒有資料，rebuild也很快）
alter table dwtest.exp_test add partition PEXPTEST_DATA_20050822
VALUES LESS THAN (TO_DATE('2005/08/29','yyyy/mm/dd'))
tablespace PEXPTEST_DATA_TB1;
-----rebuild本地分割槽到新的表空間
alter index dwtest.PEXPTEST_SSN rebuild PARTITION PEXPTEST_DATA_20050822
TABLESPACE PEXPTEST_INDEX_TB1;
-----對新增分割槽進行表分析：
analyze table dwtest.exp_test partition (PEXPTEST_DATA_20050822)
estimate statistics sample 20 percent;
--第二步是將第一週的資料遷移到31資料庫，關於資料遷移的方案，有三個可選，分別是可移動表空間、拖出表空間和資料泵倒入倒出
--前兩個方案要求表空間是自包含的，不能對分割槽的單個表進行遷移，雖然也可以透過將分割槽的表空間先交換到一個臨時表，然後將
--臨時表的表空間透過移動表空間到目標資料庫，然後再做一次分割槽交換到目標資料庫的分割槽表中，應該會是速度上最快的一個方案，
--但是問題是在表空間從源資料庫遷移到到目標資料庫的時候，這一週的資料將是不能訪問的。
--以下的試驗是針對資料泵倒入倒出這個方案的：
--首先源資料庫建立一個目錄物件來存放轉儲的檔案。（只需要建立一次）
CREATE DIRECTORY dumpout_dir AS '/home/oracle/backup/detail';
--用資料泵匯出工具匯出資料。 按照指定分割槽的方式匯出該分割槽的所有資料
expdp system/****** TABLES=dwtest.exp_test:PEXPTEST_DATA_20050801 DUMPFILE=detail_out20050801.dmp DIRECTORY=dumpout_dir CONTENT = DATA_ONLY
指定只匯出資料，不匯出後設資料。
----試驗31的一個分割槽的expdp匯出，記錄142226992，需要11分鐘，不到12個G。
[lisa@bj31 detail]$ expdp system/****** TABLES=dwtest.exp_test:PEXPTEST_P2 DUMPFILE=detail_out20050815.dmp DIRECTORY=dumpout_dir CONTENT = DATA_ONLY
Export: Release 10.1.0.3.0 - Production on Monday, 15 August, 2005 9:58
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** TABLES=dwtest.exp_test:PEXPTEST_P2 DUMPFILE=detail_out20050815.dmp DIRECTORY=dumpout_dir CONTENT = DATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 15.52 GB
. . exported "PCARD"."EXP_TEST":"PEXPTEST_P2" 11.22 GB 142226992 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home2/oracle/lisa/detail/detail_out20050815.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 10:09
--將檔案detail_out20050801.dmp轉移到遠端系統中，放在目錄指定目錄下，可以匯出的時候直接放置在nas裝置上，這樣目標資料庫可以
--直接從nas裝置上取，減少了資料檔案傳輸的時間
--在目標資料庫中建立一個目錄物件。（只需要建立一次）
CREATE DIRECTORY dumpout_dir AS '/home/oracle/backup/detail';
--目標資料庫上建立一個臨時使用者，使用者做分割槽交換前的臨時表
CREATE USER PCARD_TEMP
IDENTIFIED BY VALUES '******'
......;
--建立一個臨時表，該表的名字和結構和分割槽表一樣，但是是個普通表，表空間和目標分割槽使用的表空間一樣。
--這個表需要每次重建，以保證使用的分割槽是正確的
drop table dwtest_temp.exp_test;
CREATE TABLE dwtest_temp.EXP_TEST
(
SSN VARCHAR2(24 BYTE) NOT NULL,
......
)
tablespace PEXPTEST_DATA_20050801
nologging;
--使用資料泵匯入工具將該檔案匯入到該資料庫中。
impdp system/****** DIRECTORY=dumpout_dir DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y
--指定將資料倒入到dwtest_temp.exp_test表中，不需要記錄日誌，不需要使用原有的表空間
----倒入只用了18分鐘
[oracle@localhost local]$ impdp system/****** DIRECTORY=dumpout_dir DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y
Import: Release 10.1.0.2.0 - Production on Tuesday, 16 August, 2005 13:58
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=dumpout_dir DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "PCARD_TEMP"."EXP_TEST":"PEXPTEST_P2" 11.22 GB 142226992 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:16
--資料倒入以後建立索引，這個是所有的步驟中最耗時的部分，測試的時候是非並行的寫日誌的，如果採用並行不寫日誌，應該會再快一些
CREATE INDEX dwtest_temp.PEXPTEST_SSN ON dwtest_temp.EXP_TEST
(SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
tablespace PEXPTEST_INDEX_20050801
NOPARALLEL
nologging;
---測試的時候：建立索引，寫日誌的情況，用了1個半小時
SQL> CREATE INDEX dwtest_temp.PEXPTEST_SSN ON dwtest_temp.EXP_TEST
2 (SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
tablespace PEXPTEST_INDEX_20050801
NOPARALLEL;
--在資料倒入且索引建立完畢以後，交換表空間，包括索引表空間也可以交換過去，速度非常快
ALTER TABLE dwtest.exp_test
EXCHANGE PARTITION PEXPTEST_DATA_20050801
WITH TABLE dwtest_temp.exp_test
INCLUDING INDEXES
WITHOUT VALIDATION;
--在源資料庫上刪除已經遷移過去的分割槽，不刪除對應的表空間，表空間是複用的
alter table dwtest.exp_test drop partition PEXPTEST_DATA_20050801;
--截至到這一步，一週的資料可以遷移到目標資料庫的分割槽表中，測試的資料是1億4千萬條記錄，大概需要的時間是2個小時，
--實際上我們一週的資料應該是7000萬左右，所以時間還可以更少一些。
--對於歷史資料的資料庫，我們需要將3個月以上的記錄移出資料庫，操作實際上比線上資料庫還更簡單些，可以建立13個資料表空間和13個索引表空間，
--每週一在線上資料庫的新資料倒入之前，將最前面一個分割槽刪掉，建立一個新的分割槽使用原有的那個表空間，然後在臨時表處理完畢後
--和臨時表交換表空間，得到新的一週的資料，在這樣的規劃中，歷史資料資料庫預設儲存13周的資料。
--另外在線上資料庫中新增分割槽的指令碼：add_new_partition_35.sh
--線上資料庫使用資料泵匯出最前面一個分割槽的指令碼：expdp_old_partition_35.sh，呼叫parfile_out.sql生成expdp的引數檔案
--歷史資料庫中刪除第一個分割槽，使用資料泵倒入記錄，分割槽交換，並在源資料庫上刪除第一個分割槽的指令碼：exchange_data_31.sh,呼叫parfile_in.sql生成impdp的引數檔案
--最後，因為表空間和資料檔案不是動態重建的，所以對熱備份沒有影響，熱備份的指令碼不是動態的：dwtest_hot.sh
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/82387/viewspace-1016820/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 將ORACLE資料庫資料檔案遷移到其他目錄(ZT)Oracle資料庫
- Oracle資料庫表範圍分割槽策略測試過程Oracle資料庫
- 將pentaho資料庫遷移到oracle資料庫資料庫Oracle
- 詳解ORACLE資料庫的分割槽表Oracle資料庫
- Hash分割槽表分割槽數與資料分佈的測試
- 分割槽表入無分割槽的資料庫資料庫
- Oracle資料庫開發——瞭解分割槽表Oracle資料庫
- 分割槽表匯入資料庫資料庫
- 把正式庫的最新資料全部遷移到測試庫上
- oracle 測試 清除分割槽資料，索引釋放空間Oracle索引
- 資料庫遷移到ASM資料庫ASM
- Oracle資料庫中分割槽表的操作方法Oracle資料庫
- EAS附件表由資料庫遷移到FTP資料庫FTP
- ORACLE刪除-表分割槽和資料Oracle
- Oracle10g 資料遷移方案(zt)Oracle
- 插入遠端資料庫資料遇到分割槽表bug資料庫
- 資料庫分割槽表 什麼情況下需要分割槽資料庫
- MySQL資料庫遷移到PostgresMySql資料庫
- Oracle資料庫中分割槽表的操作方法（轉）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- 【SQL】Oracle資料庫通過job定期重建同步表資料SQLOracle資料庫
- 匯入匯出 Oracle 分割槽表資料Oracle
- Oracle 12cr2 資料庫之間傳輸表，分割槽或子分割槽Oracle資料庫
- 資料表分割槽分割與刪除歷史資料
- Oracle分割槽表遷移Oracle
- Oracle資料庫中分割槽表的操作方法詳解Oracle資料庫
- zabbix上對mysql資料庫做分割槽表MySql資料庫
- oracle xtts資料庫遷移方法測試之一OracleTTS資料庫
- Oracle查詢Interval partition分割槽表內資料Oracle
- 線上過期資料遷移到離線資料庫資料庫
- 資料泵實現資料遷移到異地庫
- Oracle資料庫遷移到國產資料庫核心難點解析 | 聯盟釋出Oracle資料庫
- 從關聯式資料庫遷移到NoSQL雲資料庫資料庫SQL
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（中）Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（下）Oracle資料庫
- Oracle資料庫遷移方案比較Oracle資料庫