Home » RDBMS Server » Server Utilities » Problems with import on Oracle 8i
Problems with import on Oracle 8i [message #134338] Wed, 24 August 2005 07:24 Go to next message
muffty
Messages: 2
Registered: August 2005
Junior Member

Hi,

I'm using Oracle 8.1.7 Enterprise on Linux.
I backup the database daily using 'exp' to export on user bases. Regulary i'm checking the archives as importing them back and never seem to have problems. The export task is sheduled and I'm saving a log from the export procedure every night. All logs are with no problems at all. That's why I really surprised when importing back one of the exported dmp files it hanged. I tried the last 10 files from the last 10 days - 5 went fine and 5 hang with no error given. The export logs of the 'bad' files also contain no error.
What can I do to isolate where the problem is?
Re: Problems with import on Oracle 8i [message #134341 is a reply to message #134338] Wed, 24 August 2005 07:29 Go to previous messageGo to next message
deepa_balu
Messages: 73
Registered: March 2005
Member
Found in metalink..


Problem Description
-------------------

Import hangs. To get more information, you query:

select sid, event from v$session_wait where wait_time = 0

The result shows that a number of sessions are waiting for 'parallel query dequeue wait'.
The import session itself is waiting for 'parallel query signal server'.

You kill the import process on the OS. The same problem reoccurs when you retry the import.

There are no error messages in the alert log or in any trace files.

Problem Explanation
-------------------

You are using parallel query and a number of query slaves would be spawned
when you start the import session. When the import session hangs, you find
out from the v$session_wait or from a systemstate dump that the import
session is waiting for event 'parallel query signal server' and the query
slave sessions are waiting for dequeue.

These query slave sessions are spinning but your import session is hanging.

Solution Description:
---------------------

When you cancel out or ctrl-C from your import session that is hanging, an error message returned,
for Example: ORA-1658 unable to create initial extent.

Solution Explanation:
---------------------

As the import session is hanging, no error messages would be reported in alert log nor in Logfile.
If the session is killed on the operating system, no error messages would be reported either.

Only when we exit from the same session, an error message would be returned. This error message
would help to resolve the problem.

The information in v$session_wait or in systemstate dump is misleading here. The query slaves
are spinning as they wake up and timeout continuously when waiting for dequeue messages.
Re: Problems with import on Oracle 8i [message #134342 is a reply to message #134338] Wed, 24 August 2005 07:30 Go to previous messageGo to next message
deepa_balu
Messages: 73
Registered: March 2005
Member
goal: How To Find The Cause of a Hanging Import Session
fact: Oracle Server - Enterprise Edition



fix:

When an import session hangs, it is important that the underlying problem is
identified to be able to solve the problem.

First identify the session in the database serving the import :

select sid, user, program
from v$session
where upper(program) like '%IMP%';

Then verify the wait events for that session :

select sid, event, state, wait_time, seconds_in_wait
from v$session_wait
where sid = <sid>;

This query will list the event that is being waited on, hence it will reveal
the underlying problem. Then act accordingly to solve the problem. Typically
this will be events like :

- log file switch archiving needed
Cause: Oracle is waiting on a log switch because the required redolog has not
yet been archived.
Action: Check that the archiver process is started and that Oracle is able to
archive the redolog files.

- library cache lock
Cause: A user session is holding a lock on a table that is being requested by
the import session.
Action: Remove the lock by asking the user to commit/rollback or by killing the
locking user session.

icon4.gif  Re: Problems with import on Oracle 8i [message #262141 is a reply to message #134342] Fri, 24 August 2007 12:50 Go to previous message
rwilkes@benefitpoint.com
Messages: 1
Registered: August 2007
Junior Member
Hi there and please help I'll do anything!

Oracle9i Enterprise Edition Release 9.2.0.8.0

We are experiencing the same import process hang as you were experiencing, in our case we aren't even importing any table rows, just meta-data (the export command was "exp xxx@xxx.xxx rows=n grants=n indexes=y constraints=y statistics=estimate owner=xxxx file=xxxx.meta") and when we performed the queries on the system while it was hung that you suggested above the results were:
1. Query on V$SESSION while import was running for meta file.

SQL> select sid, user, program from V$SESSION WHERE upper(PROGRAM) LIKE '%IMP';

RESULT:
40 SYS IMP.EXE

2. Query on V$SESSION_WAIT script ran a few minutes apart while importing the meta file:

SQL> select sid, event, state, wait_time, seconds_in_wait
from v$session_wait where sid=40;

RESULT - 1st RUN:

40 SQL*Net more data from client WAITING 0 599

RESULT - 2nd RUN:

40 SQL*Net more data from client WAITING 0 1034

RESULT - 3rd RUN:

40 SQL*Net more data from client WAITING 0 1475

RESULT - 4th RUN:

40 SQL*Net more data from client WAITING 0 1748

The only thing I can see is that there is a client process that the server thinks should be sending more data...any help would be so appreciated, I have scoured the net looking for answers and have yet to find any.

Thanks in advance!
-Ryan Wilkes

Previous Topic: How To Overwrite Database 8.1.5 using Imp Command
Next Topic: Can we Add table data via Import Command using oracle database 8.1.5
Goto Forum:
  


Current Time: Sun Dec 04 10:58:57 CST 2016

Total time taken to generate the page: 0.08507 seconds