Oracle Database Prob - please help me

[TheExcession]

coz I really don't know what I'm doing with this one!

I know that normally you have to pay good money for this type of info but I'm a bit stuck.

We have two Linux machines running Oracle and the Async Replication is suspect.

There are heaps of outstanding deftran records:
SQL> select count(*) from deftran;
COUNT(*)
----------
56451

Now - I know I have a broken job but I can't seem to kill it. And I think it has a lock that is preventing the deftran queue pushing over to the other site.

Last Next
Job fail B Date LAST_SEC Date NEXT_SEC
---- ---- - ----------------- -------- ----------------- --------
382 0 N 07-SEP-04 19:34:52 07-SEP-04 19:44:52
379 0 N 07-SEP-04 19:38:17 07-SEP-04 19:39:17
386 0 N 07-SEP-04 19:34:52 07-SEP-04 19:44:52
380 2 Y 14-JUN-04 10:48:52 01-JAN-00 00:00:00
381 0 N 07-SEP-04 19:34:52 07-SEP-04 19:44:52
384 0 N 07-SEP-04 19:34:52 07-SEP-04 19:44:52
385 0 N 07-SEP-04 19:34:52 07-SEP-04 19:44:52

SQL> SELECT * FROM v$lock WHERE type = 'JQ';

Session
ADDR KADDR ID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------- -- ---------- ---------- ---------- ---------- ---------- ----------
65239298 652392A8 58 JQ 0 380 6 0 2789 0

SQL> select serial# from v$session where sid=58;

Session
Serial#
--------
43

SQL> alter system kill session '58,43';
alter system kill session '58,43'
*
ERROR at line 1:
ORA-00031: session marked for kill


I've tried marking the job as broken and then killing all the snp processes at OS level. But it is still there.

Could anyone offer me a bit of guidance please.

best
Ex

>>> Edited by TheExcession on Tuesday 7th September 20:54


This is from the alertSIDlog showing a fresh restart

oracle sid log said:

Starting up ORACLE RDBMS Version: 8.1.7.0.1. System parameters with non-default values: processes = 250 shared_pool_size = 60000000 shared_pool_reserved_size= 6000000 large_pool_size = 6253744 java_pool_size = 25000000 enqueue_resources = 5000 control_files = /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/control01.ctl, /usr/local/oracle/8i/u01/app/oracle/or adata/HHME1/control02.ctl, /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/control03.ctl db_block_buffers = 32762 db_block_size = 8192 db_block_lru_latches = 6 db_writer_processes = 4 buffer_pool_keep = buffers:12000, lru_latches:3 buffer_pool_recycle = 1000 compatible = 8.1.7 log_archive_start = TRUE log_archive_dest_1 = location=/usr/local/oracle/8i/u01/app/oracle/admin/HHME1/arch log_archive_format = %t_%s.dbf log_buffer = 524288 log_checkpoint_interval = 0 log_checkpoint_timeout = 0 db_file_multiblock_read_count= 64 rollback_segments = RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 max_enabled_roles = 50 remote_login_passwordfile= EXCLUSIVE _distributed_lock_timeout= 1 distributed_transactions = 20 instance_name = HHMEA service_names = HHME1 mts_dispatchers = (protocol=TCP)(PRE=oracle.aurora.server.SGiopServer) mts_servers = 1 mts_max_servers = 20 mts_max_dispatchers = 5 open_links = 10 sort_area_size = 524288 db_name = HHME1 open_cursors = 200 os_authent_prefix = cursor_space_for_time = TRUE job_queue_processes = 7 job_queue_interval = 20 hash_area_size = 524288 hash_multiblock_io_count = 8 background_dump_dest = /usr/local/oracle/8i/u01/app/oracle/admin/HHME1/bdump user_dump_dest = /usr/local/oracle/8i/u01/app/oracle/admin/HHME1/udump core_dump_dest = /usr/local/oracle/8i/u01/app/oracle/admin/HHME1/cdump PMON started with pid=2 Load Indicator not supported by OS ! DBW0 started with pid=3 DBW1 started with pid=4 DBW2 started with pid=5 DBW3 started with pid=6 LGWR started with pid=7 CKPT started with pid=8 SMON started with pid=9 RECO started with pid=10 SNP1 started with pid=12 SNP0 started with pid=11 SNP2 started with pid=13 SNP3 started with pid=14 SNP4 started with pid=15 SNP5 started with pid=16 SNP6 started with pid=17 Tue Sep 7 19:04:02 2004 starting up 1 shared server(s) ... starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... ARCH: STARTING ARCH PROCESSES ARCH: STARTING ARCH PROCESSES COMPLETE ARC0 started with pid=20 ARC0: Archival started Tue Sep 7 19:04:03 2004 alter database mount Tue Sep 7 19:04:07 2004 Successful mount of redo thread 1, with mount id 388756839. Tue Sep 7 19:04:07 2004 Database mounted in Exclusive Mode. Completed: alter database mount Tue Sep 7 19:04:07 2004 alter database open Beginning crash recovery of 1 threads Tue Sep 7 19:04:07 2004 Thread recovery: start rolling forward thread 1 Recovery of Online Redo Log: Thread 1 Group 4 Seq 73488 Reading mem 0 Mem# 0 errs 0: /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/redo04.log Tue Sep 7 19:04:09 2004 Thread recovery: finish rolling forward thread 1 Thread recovery: 90 data blocks read, 90 data blocks written, 2152 redo blocks read Crash recovery completed successfully Tue Sep 7 19:04:09 2004 Thread 1 advanced to log sequence 73489 Thread 1 opened at log sequence 73489 Current log# 6 seq# 73489 mem# 0: /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/redo06.log Successful open of redo thread 1. Tue Sep 7 19:04:09 2004 ARC0: media recovery disabled Tue Sep 7 19:04:09 2004 SMON: enabling cache recovery SMON: enabling tx recovery Tue Sep 7 19:04:10 2004 Completed: alter database open Starting up ORACLE RDBMS Version: 8.1.7.0.1. System parameters with non-default values: processes = 250 shared_pool_size = 60000000 shared_pool_reserved_size= 6000000 large_pool_size = 6253744 java_pool_size = 25000000 enqueue_resources = 5000 control_files = /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/control01.ctl, /usr/local/oracle/8i/u01/app/oracle/or adata/HHME1/control02.ctl, /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/control03.ctl db_block_buffers = 32762 db_block_size = 8192 db_block_lru_latches = 6 db_writer_processes = 4 buffer_pool_keep = buffers:12000, lru_latches:3 buffer_pool_recycle = 1000 compatible = 8.1.7 log_archive_start = TRUE log_archive_dest_1 = location=/usr/local/oracle/8i/u01/app/oracle/admin/HHME1/arch log_archive_format = %t_%s.dbf log_buffer = 524288 log_checkpoint_interval = 0 log_checkpoint_timeout = 0 db_file_multiblock_read_count= 64 rollback_segments = RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 max_enabled_roles = 50 remote_login_passwordfile= EXCLUSIVE _distributed_lock_timeout= 1 distributed_transactions = 20 instance_name = HHMEA service_names = HHME1 mts_dispatchers = (protocol=TCP)(PRE=oracle.aurora.server.SGiopServer) mts_servers = 1 mts_max_servers = 20 mts_max_dispatchers = 5 open_links = 10 sort_area_size = 524288 db_name = HHME1 open_cursors = 200 os_authent_prefix = cursor_space_for_time = TRUE job_queue_processes = 7 job_queue_interval = 20 hash_area_size = 524288 hash_multiblock_io_count = 8 background_dump_dest = /usr/local/oracle/8i/u01/app/oracle/admin/HHME1/bdump user_dump_dest = /usr/local/oracle/8i/u01/app/oracle/admin/HHME1/udump core_dump_dest = /usr/local/oracle/8i/u01/app/oracle/admin/HHME1/cdump PMON started with pid=2 Load Indicator not supported by OS ! DBW0 started with pid=3 DBW1 started with pid=4 DBW2 started with pid=5 DBW3 started with pid=6 LGWR started with pid=7 CKPT started with pid=8 SMON started with pid=9 RECO started with pid=10 SNP1 started with pid=12 SNP0 started with pid=11 SNP2 started with pid=13 SNP3 started with pid=14 SNP4 started with pid=15 SNP5 started with pid=16 SNP6 started with pid=17 Tue Sep 7 19:04:02 2004 starting up 1 shared server(s) ... starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... ARCH: STARTING ARCH PROCESSES ARCH: STARTING ARCH PROCESSES COMPLETE ARC0 started with pid=20 ARC0: Archival started Tue Sep 7 19:04:03 2004 alter database mount Tue Sep 7 19:04:07 2004 Successful mount of redo thread 1, with mount id 388756839. Tue Sep 7 19:04:07 2004 Database mounted in Exclusive Mode. Completed: alter database mount Tue Sep 7 19:04:07 2004 alter database open Beginning crash recovery of 1 threads Tue Sep 7 19:04:07 2004 Thread recovery: start rolling forward thread 1 Recovery of Online Redo Log: Thread 1 Group 4 Seq 73488 Reading mem 0 Mem# 0 errs 0: /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/redo04.log Tue Sep 7 19:04:09 2004 Thread recovery: finish rolling forward thread 1 Thread recovery: 90 data blocks read, 90 data blocks written, 2152 redo blocks read Crash recovery completed successfully Tue Sep 7 19:04:09 2004 Thread 1 advanced to log sequence 73489 Thread 1 opened at log sequence 73489 Current log# 6 seq# 73489 mem# 0: /usr/local/oracle/8i/u01/app/oracle/oradata/HHME1/redo06.log Successful open of redo thread 1. Tue Sep 7 19:04:09 2004 ARC0: media recovery disabled Tue Sep 7 19:04:09 2004 SMON: enabling cache recovery SMON: enabling tx recovery Tue Sep 7 19:04:10 2004 Completed: alter database open

>>> Edited by TheExcession on Tuesday 7th September 21:30

>>> Edited by TheExcession on Tuesday 7th September 21:30

[TheExcession]

Sorted!

If anyone ever comes across this issue mail me and I'll send you the telnet logs of what we did to fix it

best
Ex