Re: Calling in the Cavalry - Hanging 8i database

Johne_uk wrote:
> There are 3 control files all in diff locations
> There are 5 logfile groups each with one logfile of 25MB
> The server is a e450 Sun Sparc, with quad processors and 2GB RAM
> I've never attempted to kill a session as user internal.
>
> Oracle is patched up to 8174. The policy with the Solaris box is not
to
> patch unless absolutely necessary. Essentially, we still have the
same
> setup as before the hanging started and our USA box (non-hanging) is
> identical and has no problems (however, it does not host as many
> applications as the hanging box - mainly javva).
>
> As I'm new to the role I can't really comment on the changes to the
> init.ora file. However, I have appended all the values below (sorry
for
> the clutter).
>
> regards
> John
>
>
> PARAMETERS
> ----------
> NAME TYPE VALUE
> processes 3 250
> sessions 3 280
> timed_statistics 1 FALSE
> timed_os_statistics 3 0
> resource_limit 1 FALSE
> license_max_sessions 3 0
> license_sessions_warning 3 0
> cpu_count 3 4
> instance_groups 2
> event 2
> shared_pool_size 2 209715200
> shared_pool_reserved_size 2 10485760
> large_pool_size 2 2097512
> java_pool_size 2 15728640
> java_soft_sessionspace_limit 3 0
> java_max_sessionspace_size 3 0
> pre_page_sga 1 FALSE
> shared_memory_address 3 0
> hi_shared_memory_address 3 0
> use_indirect_data_buffers 1 FALSE
> lock_sga 1 FALSE
> lock_name_space 2
> enqueue_resources 3 4220
> nls_language 2 AMERICAN
> nls_territory 2 AMERICA
> nls_sort 2 binary
> nls_date_language 2
> nls_date_format 2
> nls_currency 2
> nls_numeric_characters 2
> nls_iso_currency 2
> nls_calendar 2
> nls_time_format 2
> nls_timestamp_format 2
> nls_time_tz_format 2
> nls_timestamp_tz_format 2
> nls_dual_currency 2
> nls_comp 2
> disk_asynch_io 1 TRUE
> tape_asynch_io 1 TRUE
> dbwr_io_slaves 3 0
> backup_tape_io_slaves 1 FALSE
> ops_interconnects 2
> db_file_direct_io_count 3 64
> resource_manager_plan 2
> hpux_sched_noage 3
> lm_ress 3 6000
> lm_locks 3 12000
> active_instance_count 3
> control_files 2 /data01/oradata/oranc1/control01.ctl,
> /data02/oradata/oranc1/control02.ctl,
> /data03/oradata/oranc1/control03.ctl
> db_file_name_convert 2
> log_file_name_convert 2
> db_block_buffers 3 76800
> db_block_checksum 1 FALSE
> db_block_size 3 8192
> db_block_lru_latches 3 2
> db_writer_processes 3 1
> db_block_max_dirty_target 3 76800
> buffer_pool_keep 2
> buffer_pool_recycle 2
> max_commit_propagation_delay 3 700
> compatible 2 8.1.7
> log_archive_start 1 TRUE
> log_archive_dest 2
> log_archive_duplex_dest 2
> log_archive_dest_1 2

location=/data02/oradata/oranc1/arch
>
> log_archive_dest_2 2
> log_archive_dest_3 2
> log_archive_dest_4 2
> log_archive_dest_5 2
> log_archive_dest_state_1 2 enable
> log_archive_dest_state_2 2 enable
> log_archive_dest_state_3 2 enable
> log_archive_dest_state_4 2 enable
> log_archive_dest_state_5 2 enable
> log_archive_max_processes 3 1
> log_archive_min_succeed_dest 3 1
> standby_archive_dest 2 ?/dbs/arch

Maybe you just didn't show everything, but it looks like you have 5 log archive destinations enabled but only one defined.

Are you using a standby database? If so, are you using managed recovery? Are you doing it over a network? It is possible for a network error to hang everything Oracle on your production system in that case.

This all sounds like an archival issue - if you can't archive, your database stops. Things you might be watching for during the problem are: pmon, smon or dbw processes at 100% (run top during the problem); one or more of the archive destinations locked up somehow, for example, a journaling filesystem going nuts or swapper process; Almost out of memory, do a swapinfo -mat (although if sybase isn't complaining, not that); lsnrctl as someone else mentioned - if you can't get a status, might be something bogus there; check to be sure you haven't run out of network ports, netstat -a - with tcp, you have a timeout, and if that is too long, you might see a lot of FINWAIT2 status, which may mean someone (like your app) is not ending handshaking properly; and of course, what everyone else said.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20050516/news_mz1b16game.html