Oracle IMPDP导入数据案例之注意事项(undo/temp)

2018-01-22 19:49:23来源:cnblogs.com作者:Rangle人点击

分享

针对Oracle数据迁移,我们可能会用到expdp/impdp的方式,有时候需要大表、lob字段等可能会消耗过大的临时表空间和undo表空间,所以一般我们根据导出日志,在导入前适当调整表空间大小。否则我们可能会遇到以下问题:

1、临时表空间爆满,无法扩展

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 

解决方案如下:

(1)临时表空间使用情况查看:col file_name for a85;set line 200;select file_name, BYTES/1024/1024/1024 Size_GB, MAXBYTES/1024/1024/1024 Msize_GB, AUTOEXTENSIBLE from dba_temp_files;(2)使用临时段session查看:SELECT se.username,       se.sid,       se.serial#,       se.machine,       se.program,       su.segtype,       su.contents   FROM v$session se,       v$sort_usage suWHERE se.saddr=su.session_addr ;USERNAME          SID    SERIAL# MACHINE      PROGRAM                  SEGTYPE   CONTENTS--------------- ---------- ---------- -------------------- ----------------------------------- --------- ---------impdpuser          5  28002 test.localhost.com    oracle@test.localhost.com (DW00)     LOB_DATA  TEMPORARYimpdpuser          5  28002 test.localhost.com    oracle@test.localhost.com (DW00)     SORT    TEMPORARYimpdpuser          5  28002 test.localhost.com    oracle@test.localhost.com (DW00)     SORT    TEMPORARYimpdpuser          5  28002 test.localhost.com    oracle@test.localhost.com (DW00)     SORT    TEMPORARY(3)增加临时表空间数据文件:alter    tablespace temp add TEMPFILE '/home/U01/app/oracle/oradata/testdb/temp01.dbf ' size 50M autoextend on next 50M;

2、查看impdp进度情况

(1)登陆交互模式impdp impdpuser/impdpuser@service_name directory=datadump_dir dumpfile=impdp.dmp logfile=impdp.log  attach=SYS_IMPORT_FULL_01使用status查看进度信息Import> status Job: SYS_IMPORT_FULL_01  Operation: IMPORT                           Mode: FULL                             State: EXECUTING                        Bytes Processed: 0  Current Parallelism: 1  Job Error Count: 0  Dump File: /home/oracle/data.dmp  Worker 1 Status:  Instance ID: 1  Instance name: testdb  Host name: test.localhost.com  Process Name: DW00  State: EXECUTING                        Object Schema: USERNAME01  Object Name: TABLE_T1  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA  Completed Objects: 1  Total Objects: 34  Completed Rows: 816,282,438  Completed Bytes: 64,412,720,616  Percent Done: 99  Worker Parallelism: 1(2)数据字典查看select * from dba_datapump_jobs  ;

3、undo表空间爆满

    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

解决方案:

(1)查看undo表空间使用情况col file_name for a85;set line 200;select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';SELECT  UPPER(F.TABLESPACE_NAME)    AS "TABLESPACE_NAME",ROUND(D.MAX_BYTES,2)          AS "TBS_TOTAL_SIZE" ,ROUND(D.AVAILB_BYTES ,2)        AS "TABLESPACE_SIZE",ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2)  AS "TBS_AVABLE_SIZE",ROUND((D.AVAILB_BYTES - F.USED_BYTES),2)           AS "TBS_USED_SIZE",TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,2),'999.99')   AS "USED_RATE(%)",ROUND(F.USED_BYTES, 6)                             AS "FREE_SIZE(G)"FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTESFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6)  AVAILB_BYTES,ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6)   MAX_BYTESFROM DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEAND D.TABLESPACE_NAME=&UNDO_TABLESPACE_NAMEORDER BY 5 DESC;2)查看undo段使用情况select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s  where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinksfrom v$rollstat order by rssize;3)查看归档日志切换情况SELECT  to_char(trunc(first_time),'DD-Mon-YY') "Date",        to_char(first_time, 'Dy') "Day",         '|'                                               separator,        count(1) Total,         '|'                                               separator,        SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",        SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",        SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",        SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",        SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",        SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",        SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23" from v$log_history-- where standby_dest = 'NO'-- and   CREATOR IN ('ARCH' , 'FGRD')group by trunc(first_time), to_char(first_time, 'Dy')order by trunc(first_time);--logswitchfreq.sql (4)查看相关SQLset line 200;set pagesize 20000;col sql_text for a45;col   elapsed_rank for 999;select *from (select v.sql_id, v.sql_text,v.elapsed_time/1000000 elapsed_time ,v.cpu_time/1000000 cpu_time,v.disk_reads,v.executions,rank() over(order by v.disk_reads desc) elapsed_rankfrom v$sqlarea v) awhere elapsed_rank <= 10;(4)增加表空间数据文件alter  undo tablespace undotbs1 add datafile '/home/U01/oracle/oradata/test/UNDOTBS1a.dbf' size 100m reuse autoextend on next 100M;(5)改变默认undo表空间create undo tablespace undotbs2 datafile '/home/U01/oracle/oradata/test/UNDOTBS2.dbf' size 100m reuse autoextend on next 100m;alter system set undo_tablespace=undotbs2 scope=both;  

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台