ANBOB™

因一次硬件原因导致了服务器异常停机，防止蝴蝶效应及时进行了数据迁移，一周发现了这个决定正确的。

申请了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 MACLEAN‘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 .
http://www.anbob.com/archives/511.html

Configuration ORACLE database backup job.

打赏

微信扫一扫，打赏作者吧～