Imp paused mid import

importoracle-11g-r2

OK, I'm new to Oracle administration but I'm trying to import a previous dump (no pun intended) which was split into two files.

The import is happening from an Oracle 9i to 11gR2 on Windows

I used the following commands:

imp userid=olduser/oldpassword indexes=n log=import.log  
file(Export_DB_776full_20130829_020000_01,Export_DB_776full_20130829_0200
00_02) fromuser=olduser touser=newadminuser commit=y feedback=100

Now this has progressed fairly well and has imported data from what seems to be the first file, peppering the console with a dot every 100 rows imported. Then when it reaches the table where the export was split (into the second dmp file) its just paused. I thought it was simply collecting its thoughts and left it overnight but with no progress

Trying to connect to Oracle from SQLPLUS on the command line, even as SYS or SYSDBA is not possible, and returns with a 

"ORA-00257: archiver error. Connect internal only, until freed."

Googling has yielded no direct answers to this but what I did find at least yielded some access to the database to perform some alteration function. Some of the other posts I saw mentioned that the log files are filling up, but there is well in excess of 150 GB free on both drives (the tablespace is split across two drives)

A few things:

What is going on? – because the error message seems a bit cryptic to me –
How can I connect to the database?
How can I fix this?
How do I not fall into this trap again?

Thanks

Best Answer

your database is running in archivelog mode. An initial backup + archives allow you to recover your database to any point in time between the backup and the last available archivelog file. If this is a big import you are most likely going to make a backup afterwards. If you have no need to recover the database to a state between now and the previous backup, you could use rman to delete the archived log files, freeing space so the archiver can continue. An other option is to increase the db_recovery_file_dest_size parameter that could be in place since you claim to have plenty of space available.

sqlplus / as sysdba
show parameter db_recovery_file_dest_size
alter system set db_recovery_file_dest_size = {A_BIGGER_SIZE}

or

rman target=/
delete archivelog all;

don't forget to make a backup, deleting the archives makes recovery impossible.

Related Question