| Import taking forever to finish [message #624446] |
Mon, 22 September 2014 16:32  |
Ricky_1362002
Messages: 111
Registered: February 2009
|
Senior Member |
|
|
Hello everyone,
The scenario is that I had to take an export of a few tables (total size=2TB), when the database was in read only mode.
I could not use expdp because the DB was in read only mode. As expdp creates some temporary export tables, it did not allow. So I ran export on the required tables.
Now, the database is in read/write mode, and its time to import those tables.
Tables already exist, have the constraints/indexes as well. I disabled the constraints and dropped the indexes.
And then started the import.
The import parameter file has these options:
file=/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_1.dmp
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_2.dmp,
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_3.dmp,
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_4.dmp,
/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0918_5.dmp
buffer=50000000
filesize=42949672960
log=/orabkup1/acfsmounts/acfs_dg1_acfs_vol/CRDS/expdp/exp_tables_0922.log
GRANTS=N
INDEXES=N
ROWS=Y
CONSTRAINTS=N
STATISTICS=NONE
ignore=y
commit=n
fromuser=CRDS_22
touser=CRDS_22
TABLES=DIAG,LINE,PRVDR,RSN,INDVD
I tried to track the import, but can't really predict how much time its going to take
SQL> select sofar,totalwork,time_remaining,START_TIME from gv$session_longops where sofar!=totalwork;
no rows selected
SQL> select USED_UREC,USED_UBLK from V$TRANSACTION;
USED_UREC USED_UBLK
---------- ----------
2994956 15641
select
substr(sql_text,instr(sql_text,'into "'),30) table_name,
rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from
sys.v_$sqlarea
where
SQL_TEXT like 'insert %into "%' and COMMAND_TYPE = 2 and OPEN_VERSIONS > 0;
TABLE_NAME ROWS_PROCESSED MINUTES ROWS_PER_MINUTE
---------------
into "SYS"."SYS$SERVICE_METRIC 5022 4123.2 1
into "SYS"."ALERT_QT" (q_name 64 4123.4 0
into "SYS"."AQ$_SYS$SERVICE_ME 10043 4123.1 2
Is there any other better way to track the import?
Are there any other options that would make the import faster?
We don't have PARALLEL option in import, do we?
Please advise.
Thank you,
Ricky
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Import taking forever to finish [message #624691 is a reply to message #624570] |
Wed, 24 September 2014 10:59  |
Ricky_1362002
Messages: 111
Registered: February 2009
|
Senior Member |
|
|
Michel - No LOB on the tables, thanks, will try that.
Gazzaq - No worries, thanks for the concern though.
LKBrwn - Will probably try that next time. Thank you.
I had to run the import for the biggest table, and it took close to 6 hours. Import is really slow - this has been a very good test.
Will try with the options you guys have suggested in a day or 2 and update here.
Thanks again, everyone.
Cheers!
Ricky
|
|
|
|
| Re: Import taking forever to finish [message #624693 is a reply to message #624446] |
Wed, 24 September 2014 12:25 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Some possible things that will affect the speed:
Are you importing across a network?
Is the filesystem where the files reside a remote mount?
Are there triggers on the tables being imported?
|
|
|
|
|
|
| Re: Import taking forever to finish [message #624853 is a reply to message #624694] |
Fri, 26 September 2014 10:51 |
Ricky_1362002
Messages: 111
Registered: February 2009
|
Senior Member |
|
|
Thank you Joy, and for your questions:
Quote:
Are you importing across a network?
Is the filesystem where the files reside a remote mount?
Are there triggers on the tables being imported?
-No, import is NOT being done across a network. Its done on the same server, same DB.
-Filesystem does not reside on a remote host, but they do in ACFS.
-Triggers are not imported, I don't see them in import log, and not in export log too.
Thank you BlackSwan. Will do that next time.
|
|
|
|
| Re: Import taking forever to finish [message #624863 is a reply to message #624853] |
Fri, 26 September 2014 12:50 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Ricky_1362002 wrote on Fri, 26 September 2014 11:51Thank you Joy, and for your questions:
-Triggers are not imported, I don't see them in import log, and not in export log too.
Sorry, my message was a little unclear. I meant to ask are there any triggers in the destination tablesthat will be triggered upon importing rows?
|
|
|
|
| Re: Import taking forever to finish [message #624865 is a reply to message #624446] |
Fri, 26 September 2014 13:10 |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
To speed things up:
Set all the constraints to enabled novalidate before you export. That will save you a lot of time in the import, it can take hours to validate constraints.
Set workarea_size_policy=manual and sort_area_size=2147483647 (which as big as you can go) and the index creation phase will be much faster.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
