oracle数据库分区的备份和恢复

• 备份过程

1 查看创建表空间时指定的数据文件

show parameter db_create_file


2 设置表空间地址
alter system set DB_CREATE_FILE_DEST ='/opt/oracle/product/oradata/orcl'


3 创建6个表空间，3个给表分区，三给分区索引
create tablespace p1;
create tablespace p2;
create tablespace p3;
create tablespace p1_idx;
create tablespace p2_idx;
create tablespace p3_idx;


4 创建分区表
create table t(id int, name varchar2(1000)) partition by range(id)
(
partition p1 values less than(10000) tablespace p1,
partition p2 values less than(20000) tablespace p2,
partition p3 values less than(maxvalue) tablespace p3
);


5 创建索引分区
create index t_idx on t(id) local
(
partition p1 tablespace p1_idx,
partition p2 tablespace p2_idx,
partition p3 tablespace p3_idx
);
/


6 查看分区与分区索引和表空间的对应关系
select partition_name,tablespace_name from user_segments where segment_name='T';
select partition_name,tablespace_name from user_segments where segment_name='T_IDX';


7 向分区表中插入数据
begin
for i in 1..3 loop
insert into t select object_id*i,object_name from dba_objects where object_id<10000;
end loop;
commit;
end;
/




8 查看分区表中的数据
select 'p1',count(*) from t partition(p1)
union
select 'p2',count(*) from t partition(p2)
union
select 'p3',count(*) from t partition(p3)


9 将分区p1以及p1索引所在的表空间设置为只读
alter tablespace p1 read only;
alter tablespace p1_idx read only;


10 创建数据库对象--目录，用于导出和导入时设置路径
create directory tts as '/bak';


11 尝试对表空间进行导出
cd /opt/oracle/product/11.1.0/bin
expdp viexpress_bbb/iflytek directory=tts dumpfile=tts_p1.dmp transport_tablespaces=p1,p1_idx logfile=tts_p1.log
由于表空间的对象并非是自包含，




12 创建一个临时表和临时表索引
create table t_temp as select * from t where 1=2;
create index t_temp_idx on t_temp(id);
select segment_name,tablespace_name from user_segments where segment_name in ('T_TEMP','T_TEMP_IDX');


13 进行分区交换
alter table  t exchange partition p1 with table t_temp including indexes;


14 获得导出文件的数据文件路径
select file_name from dba_data_files where tablespace_name in ('P1', 'P1_IDX');


15 拷贝数据文件到指定的备份文件路径
cp /opt/oracle/product/oradata/orcl/ORCL/datafile/*_p1_*.dbf /bak/


14 进行表空间导出
expdp viexpress_bbb/iflytek directory=tts dumpfile=tts_p1.dmp transport_tablespaces=p1,p1_idx logfile=tts_p1.log


15 删除已经两个表空间p1和p1_idx
drop tablespace p1 including contents;
drop tablespace p1_idx including contents;


16 最后在/bak路径下就保存了所有的归档数据

• 数据恢复过程：

1 导入临时表和它的索引数据 
cp /bak/tts_p1.dmp .
impdp viexpress_bbb/iflytek directory=tts dumpfile=tts_p1.dmp transport_datafiles='/bak/o1_mf_p1_9swvl7ms_.dbf','/bak/o1_mf_p1_idx_9swvnp1z_.dbf' logfile=imp_tts.log


2 将数据交换到分区表中
alter table t exchange partition p1 with table t_temp including indexes;