| Problems with import on Oracle 8i [message #134338] |
Wed, 24 August 2005 07:24  |
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  |
deepa_balu
Messages: 74
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 |
deepa_balu
Messages: 74
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.
|
|
|
|
 Re: Problems with import on Oracle 8i [message #262141 is a reply to message #134342] |
Fri, 24 August 2007 12:50 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
