deadlock during export process....

[sprabhakar]

Hi,
Recently I recovered my database after a system crash by importing the previous day's export backup...Now I am facing a new problem.
Everyday at 22hrs my database goes for an export backup
(Inc/Cum/Com). During this export process, all the front end applications accessing the database tables throw an exception indicating that a deadlock has been detected. I am facing this exception only after my database restoration.
I am using the same init.ora parameter setting as they existed before the system crash.
Should I set any init.ora parameters or any others? Could anyone throw some light on how to handle this situation.

Thanks and Regards
S.Prabhakar

[aarroyob]

How did you do the restore?

Angel

[sprabhakar]

Hi,
I created a new instance with the same settings as the previous one and did a import of the previous complete export taken the night before.

Regards
S.Prabhakar

[aarroyob]

Have you checked that all the objects in the database are valid? Did you have any creation problem in the import statement?

Cheers

Angel

[sprabhakar]

Hi aarroyob,

There were no problems when I imported the database. The locking occurs only when the export of the database is taken. At all other times, everything is fine.

I had found that the amount of redo information generated was more than that of the size of the available online redo log files and that caused problems when flushing of data was done.
I have added more online redo log files and I am waiting for any exceptions to be thrown.

Will get back if I get have any more issues.
Thanks for your timely help

Regards
S.Prabhakar

[sprabhakar]

Hi aarroyob,

Could you tell me how to find all the invalid objects in the database?

Regards
S.Prabhakar

[Sharma]

Try these, first one for everything but package and second one is for packages only.

select 'alter '||object_type||' "'||owner||'".'||object_name||' compile;'
from all_objects where
status = 'INVALID'
and object_type <> 'PACKAGE BODY';


select 'alter '||'package "'||owner||'".'||object_name||' compile body;'
from all_objects where
status = 'INVALID'
and object_type = 'PACKAGE BODY';

[sprabhakar]

Hi All,

The error is repeating. Could anyone help me out with this..

*** SESSION ID:(26.2712) 2002.08.28.01.12.34.453
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SCIOMS.FM_ORDER_LINE
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
1aa4aafc 1c25605c 14f37ad8 X 1c25605c 1db6818c S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 14f37ad8, type: 23, owner: 1dadf11c, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=14f37ad8 handle=1aa4aafc request=X
call pin=0 session pin=0
user=1c25605c session=1c25605c count=0 flags=[00] savepoint=210
LIBRARY OBJECT HANDLE: handle=1aa4aafc
name=SCIOMS.FM_ORDER_LINE
hash=cfdab1cf timestamp=08-19-2002 03:49:56
namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-070d-070d lock=S pin=S latch=2
lwt=1aa4ab14[14f37ae8,14f37ae8] ltm=1aa4ab1c[1aa4ab1c,1aa4ab1c]
pwt=1aa4ab2c[1aa4ab2c,1aa4ab2c] ptm=1aa4ab84[1aa4ab84,1aa4ab84]
ref=1aa4ab04[16fa41d0,1a8495a0]
LIBRARY OBJECT: object=1aa4a884
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 1aa4aa8c 1aa4a908 I/P/A 0 NONE
2 1aa3f88c 16fe2c5c I/P/A 1 NONE
3 1aa3f8ec 12982718 I/-/A 0 NONE
8 1aa4aa14 10fce5d8 I/-/A 0 NONE
9 1aa3f82c 10b7dc90 I/-/A 0 NONE
10 1aa3f7cc 1983e3ac I/-/A 0 NONE
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 1db6818c, type: 23, owner: 1c2bdfb8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=1db6818c handle=1aa4aafc mode=S
call pin=1db68740 session pin=0
user=1c25605c session=1c25605c count=2 flags=PNC/[04] savepoint=178
LIBRARY OBJECT HANDLE: handle=1aa4aafc
name=SCIOMS.FM_ORDER_LINE
hash=cfdab1cf timestamp=08-19-2002 03:49:56
namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-070d-070d lock=S pin=S latch=2
lwt=1aa4ab14[14f37ae8,14f37ae8] ltm=1aa4ab1c[1aa4ab1c,1aa4ab1c]
pwt=1aa4ab2c[1aa4ab2c,1aa4ab2c] ptm=1aa4ab84[1aa4ab84,1aa4ab84]
ref=1aa4ab04[16fa41d0,1a8495a0]
LIBRARY OBJECT: object=1aa4a884
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 1aa4aa8c 1aa4a908 I/P/A 0 NONE
2 1aa3f88c 16fe2c5c I/P/A 1 NONE
3 1aa3f8ec 12982718 I/-/A 0 NONE
8 1aa4aa14 10fce5d8 I/-/A 0 NONE
9 1aa3f82c 10b7dc90 I/-/A 0 NONE
10 1aa3f7cc 1983e3ac I/-/A 0 NONE
--------------------------------------------------------
This lock request was aborted.

[pando]

you have concurrent processes running?

[sprabhakar]

Hi Pando,
Yes we have an 3 concurrent applications that insert data into the database. But I face this error only when the export backup is running.
Before the database crash, the export backup was running without any trouble and the same script is being used now.
I don't know whether I have missed some settings

Regards
S.Prabhakar