I have created a database and am importing a full dump file from another server, using expdp/impdp. I am only concered with impdp. The dmp file is around 2.5GB, and I had to increase the REDO logs from 50MB to 100MB as I kept running into the ORA-00257: Archiver error. Connect internal only until freed" error. The full database, once imported, is supposed to be roughly 7GB so I'm not even sure 100MB is the ideal size for redo logs.
Now, I want to import the dump file into a new schema and tablespace, for certain testing purposes. This is where I'm having issues. I'm using the options to remap the schema and to remap the tablespace. I believe I have everything in tact, but I'm again running into the same ORA-00257 issue. Based on the impdp routine below, is there anything different I should be doing? It all seems fairly straight forward but I'm getting tired of wrestling with this redo log issue. Maybe 100MB isn't large enough?
impdp system/passward dumpfile=expdat.dmp nologfile=y REMAP_SCHEMA=company:companytemp full=y REMAP_TABLESPACE=company_prod:companytemp_prod
I know there is an redo log advisor I can use, but I don't even have the tables imported yet or user connections to analyze against.
Thank you!!
Oracle 10G on Linux Ent
Full Version: impdp Options and Issues
that is nothing to do with the options or the redo logs, its due to the fact oracle cant archive the logs - check your archive log folders for being full - find out why the logs cant be archived
>> I kept running into the ORA-00257: Archiver error. Connect internal only until freed" error.
ORA-00257 is a common error in Oracle 10g. You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery are, or db_recovery_file_dest_size .
http://www.dba-oracle.com/sf_ora_00257_arc...until_freed.htm
MetaLink offers a wealth of information concerning the resolution of ORA-00257 in Oracle 10g.
https://metalink.oracle.com/metalink/plsql/...1,1,1,helvetica
****************************************************************************
>> Based on the impdp routine below, is there anything different I should be doing?
Yes, lots of tuning options to make impdp run faster, please read carefully:
http://www.dba-oracle.com/oracle_tips_load_speed.htm
Analyze once after the load - Set analyze=n and analyze with dbms_stats after the load has completed.
Use Solid-state disk - For a fully-tuned import job, only faster devices can speed-up import rates. Many large companies used partitioned tables, and keep the current partition on SSD for fast imports.
Increase recordlength - Many set recordlength to 64k, but it needs to be a multiple of your I/O chunk size and db_block_size (or your multiple block size, e.g. db_32k_block_size).
Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase. Larger tables may not be suitable for this option due to the required rollback/undo space.
Dedicate a single, large rollback segment - Many professionals create a single large rollback segment and take all others offline during theimport.
Set indexes=n – Index creation can be postponed until after import completes, by specifying indexes=n. If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table. Setting indexes=n eliminates this maintenance overhead. You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.
Defer CBO stats - Using impdp with the parameter exclude=statistics will greatly improve the import speed, but statistics will need to be re-analyzed or imported later.
Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.
Disable logging - You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.
ORA-00257 is a common error in Oracle 10g. You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery are, or db_recovery_file_dest_size .
http://www.dba-oracle.com/sf_ora_00257_arc...until_freed.htm
MetaLink offers a wealth of information concerning the resolution of ORA-00257 in Oracle 10g.
https://metalink.oracle.com/metalink/plsql/...1,1,1,helvetica
****************************************************************************
>> Based on the impdp routine below, is there anything different I should be doing?
Yes, lots of tuning options to make impdp run faster, please read carefully:
http://www.dba-oracle.com/oracle_tips_load_speed.htm
Analyze once after the load - Set analyze=n and analyze with dbms_stats after the load has completed.
Use Solid-state disk - For a fully-tuned import job, only faster devices can speed-up import rates. Many large companies used partitioned tables, and keep the current partition on SSD for fast imports.
Increase recordlength - Many set recordlength to 64k, but it needs to be a multiple of your I/O chunk size and db_block_size (or your multiple block size, e.g. db_32k_block_size).
Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase. Larger tables may not be suitable for this option due to the required rollback/undo space.
Dedicate a single, large rollback segment - Many professionals create a single large rollback segment and take all others offline during theimport.
Set indexes=n – Index creation can be postponed until after import completes, by specifying indexes=n. If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table. Setting indexes=n eliminates this maintenance overhead. You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.
Defer CBO stats - Using impdp with the parameter exclude=statistics will greatly improve the import speed, but statistics will need to be re-analyzed or imported later.
Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.
Disable logging - You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.
QUOTE (dave @ Aug 1 2008, 10:02 AM) 
that is nothing to do with the options or the redo logs, its due to the fact oracle cant archive the logs - check your archive log folders for being full - find out why the logs cant be archived
Thank you! I did increase the parameter DB_RECOVERY_FILE_DEST from 2G to 10G. Is this related or is there another setting I need to go after? Disk space itself is not an issue.
Thanks again!
QUOTE (burleson @ Aug 1 2008, 10:19 AM)
Yes, lots of tuning options to make impdp run faster, please read carefully:
Certainly a lot to learn coming over from MSSQL. You have great articles and I'm finding your site to be a huge help! Thank you very much for the information! I am looking into the options you have specified.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.