一次expdp/impdp遷移案例

轉載於---&gt>
因一次硬體原因導致了伺服器異常停機，防止蝴蝶效應及時進行了資料遷移，一週發現了這個決定正確的。

申請了2小時的停機時間。因資料不大且表空間開始使用不規範決定用expdp資料泵方式遷移。

在新裝置
1, raid10+安裝OS
2，安裝oracle 10.2.0.1(single instance)+
3，升級oracle 10.2.0.5
4，建庫,
5, 調整INSTANCE引數,調整temp tbs、redo file size、undo tbs大小
6，建立app schema、建立data\index tablespace

用時1小時左右

stop web app+掛通知

stop listener

sys@TOPBOX>select object_type,count(*) cnt from dba_objects where owner='TOPBOX' GROUP BY OBJECT_TYPE;

OBJECT_TYPE                          CNT
------------------- --------------------
SEQUENCE                             802
PROCEDURE                             25
DATABASE LINK                          2
LOB                                    3
TRIGGER                                3
MATERIALIZED VIEW                      2
TABLE                                 87
FUNCTION                              12
VIEW                                  41
INDEX                                142

sys@TOPBOX>@schemas_space
SCHEMA                              Object type                                    Space [MB]
----------------------------------- --------------------------------------------- -----------
...
TOPBOX                              INDEX                                            34102.25
                                    LOBINDEX                                              .19
                                    LOBSEGMENT                                            .44
                                    TABLE                                            31132.69
***********************************                                               -----------
sum                                                                                  65235.56
...

######################
expdp 用時14分鐘
######################
expdp system/xxxxxx schemas=topbox directory=datapump dumpfile=newtopbox%U.dump filesize=5G parallel=6 logfile=topbox_parallel_expdp.log;

[oracle@topbox datapump]$ du newtopbox0*
829016 newtopbox01.dump
5248012 newtopbox02.dump
5248012 newtopbox03.dump
5248012 newtopbox04.dump
4338316 newtopbox05.dump
3681564 newtopbox06.dump
2029228 newtopbox07.dump
673872 newtopbox08.dump
80164 newtopbox09.dump

[oracle@topbox datapump]$ du newtopbox0*|awk ‘{sum += $1};END {print sum}’
27376196

TIP:
DUMP file only contains of indexes metadata data.

#######################
scp 用時11分鐘
#######################
同為兩臺伺服器DELL 2950 ，找個閒置網路卡埠設定任意同網段IP，如192.168.0.1和192.168.0.2，用平時用的交叉線(不一定要直連線)網線不經交換機直連兩臺機器，發現有自適應很方便。
scp傳輸dump檔案到另一臺機器，速度可達40MB/s.

#########################
IMPDP 用時16分鐘 EXCLUDE=STATISTICS,constraint,index
#########################

impdp system/xxxxxxxx directory=datapump dumpfile=newtopbox%U.dump EXCLUDE=STATISTICS,constraint,index remap_tablespace=users:topbox parallel=6

#########################
CREATE ConstraintS and INDEXS
#########################

impdp username/XXXX directory=xxx DUMPFILE=newtopbox%U.dump SQLFILE=create_index.sql INCLUDE=constraint,index remap_tablespace=users:topbox_idx

TIP:
Edit create_index.sql, Replaced the degree of parallel and nologging options

”
Bug 8604502 – IMPDP creates indexes with parallel degree 1 during import

This issue is fixed in:
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 7 for Exadata Database
11.1.0.7 Patch 24 on Windows Platforms
”

References ‘s post Speed up the index creation.

alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set events '10351 trace name context forever, level 128';
alter session set sort_area_size=734003200;
alter session set "_sort_multiblock_read_count"=128;
alter session enable parallel ddl;
alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;
alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;

spool create_index.log
@create_index.sql
spool off

#######################
Compile invalid objects
#######################

begin
dbms_utility.COMPILE_SCHEMA('TOPBOX');
end
/

#########################
Gather Schema Statistics
#########################

begin
　　dbms_stats.gather_schema_stats(
　　ownname          => 'TOPBOX',
　　estimate_percent => dbms_stats.auto_sample_size,
　　method_opt       => 'for all columns size 1',
　　degree           => 7
　　);

end
/

TIP：
METHOD_OPT was ‘FOR ALL COLUMNS SIZE 1′. This basically says to Oracle please only collect basic column statistics (min, max, distinct values etc.), do not collect histograms

on these columns. For columns that are evenly distributed and for columns that are not referenced in SQL statements, this is perfectly adequate. If a column was unevenly

distributed and detrimentally impacted the CBO’s costings of an execution plan, then one could generate histograms for those particular columns separately.

##################
others
##################

Configuration Oracle database automatically with the Linux OS startup and shutdown .

Configuration ORACLE database backup job.