Precautions for importing data from Oracle IMPDP (undo/temp) and impdpundo

Precautions for importing data from Oracle IMPDP (undo/temp) and impdpundo

For Oracle data migration, we may use the expdp/impdp method. Sometimes large tables and lob fields may consume large temporary and undo tablespaces. Therefore, we generally export logs based on, adjust the tablespace size before import. Otherwise, we may encounter the following problems:

1. The temporary tablespace is full and cannot be expanded.

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

The solution is as follows:

(1) view the usage of temporary tablespace: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) Use the temporary segment session to view: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 tables ---------- too many --------- impdpuser 5 28002 bytes 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) add temporary tablespace data files:Alter tablespace temp add TEMPFILE '/home/U01/app/oracle/oradata/testdb/temp01.dbf' size 50 M autoextend on next 50 M;

2. View impdp progress

(1) Login Interaction ModeImpdp impdpuser/impdpuser @ service_name directory = datadump_dir dumpfile = impdp. dmp logfile = impdp. log attach = SYS_IMPORT_FULL_01 use status to view progress information 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) view data dictionariesSelect * from dba_datapump_jobs;

3. The undo tablespace is full.

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

Solution:

(1) View the usage of the undo tablespace.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 * 999), '2017. 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) view the usage of the undo segmentSelect 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) view archiving log SwitchingSELECT 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',) "h2", SUM (decode (to_char (first_time, 'hh24'), '03',) "h3 ", SUM (decode (to_char (first_time, 'hh24'), '04 ', 1, 0) "h4", SUM (decode (to_char (first_time, 'hh24 '), '05 ',) "h5", SUM (decode (to_char (first_time, 'hh24'), '06',) "h6 ", SUM (decode (to_char (first_time, 'hh24'), '07 ', 1, 0) "h7", SUM (decode (to_char (first_time, 'hh24 '), '08 ',) "h8", SUM (decode (to_char (first_time, 'hh24'), '09',) "h9 ", SUM (decode (to_char (first_time, 'hh24'), '10', 1, 0) "h10", SUM (decode (to_char (first_time, 'hh24 '), '11',) "h11", SUM (decode (to_char (first_time, 'hh24'), '12',) "h12 ", SUM (decode (to_char (first_time, 'hh24'), '13', 1, 0) "h13", SUM (decode (to_char (first_time, 'hh24 '), '14',) "h14", SUM (decode (to_char (first_time, 'hh24'), '15',) "h15 ", SUM (decode (to_char (first_time, 'hh24'), '16', 1, 0) "h16", SUM (decode (to_char (first_time, 'hh24 '), '17',) "h17", SUM (decode (to_char (first_time, 'hh24'), '18',) "h18 ", SUM (decode (to_char (first_time, 'hh24'), '19', 1, 0) "h19", SUM (decode (to_char (first_time, 'hh24 '), '20',) "h20", SUM (decode (to_char (first_time, 'hh24'), '21',) "h21 ", SUM (decode (to_char (first_time, 'hh24'), '22', 1, 0) "h2", 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) view related SQL statementsSet 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.exe cutions, rank () over (order by v. disk_reads desc) elapsed_rankfrom v $ sqlarea v) awhere elapsed_rank <= 10;(4) add tablespace data filesAlter undo tablespace undotbs1 add datafile '/home/U01/oracle/oradata/test/UNDOTBS1a. dbf' size 100 m reuse autoextend on next 100 M;(5) change the default undo tablespace.Create undo tablespace undotbs2 datafile '/home/U01/oracle/oradata/test/UNDOTBS2.dbf' size 100 m reuse autoextend on next 100 m; alter system set undo_tablespace = undotbs2 scope = both;