OK, sobre o init.ora, acredito não ser necessário postar, já que derrubei e
subi o banco novamente e os parâmetros que não estão com seus valores-padrão
são incluídos no alert no momento da inicialização.
O problema ocorreu por volta das 9:00, derrubei o banco as 10:20 e subi o
banco logo em seguida.
Colocarei as informações das 8:30 até logo após a inicialização do banco.
Entre a descoberta do problema e o shutdown do banco, cheguei a dar shutdown
nos dispatchers e alterar o parâmetro shared_servers para zero a fim de
derrubar os processos e logo em seguida subi novamente...
Aí vão as informações:
Fri Mar 3 08:28:44 2006
ARC0: Evaluating archive log 2 thread 1 sequence 1907
ARC0: Beginning to archive log 2 thread 1 sequence 1907
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/archive/mcca/1_1907.arch'
Fri Mar 3 08:28:44 2006
ARC1: Evaluating archive log 2 thread 1 sequence 1907
ARC1: Unable to archive log 2 thread 1 sequence 1907
Log actively being archived by another process
ARC1: Evaluating archive log 3 thread 1 sequence 1908
ARC1: Beginning to archive log 3 thread 1 sequence 1908
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/archive/mcca/1_1908.arch'
ARC1: Completed archiving log 3 thread 1 sequence 1908
ARC1: Evaluating archive log 2 thread 1 sequence 1907
ARC1: Unable to archive log 2 thread 1 sequence 1907
Log actively being archived by another process
Fri Mar 3 08:28:53 2006
ARC0: Completed archiving log 2 thread 1 sequence 1907
Fri Mar 3 09:24:37 2006
found dead shared server 'S006', pid = (16, 1)
Fri Mar 3 09:26:21 2006
found dead shared server 'S013', pid = (23, 1)
found dead shared server 'S008', pid = (18, 1)
Fri Mar 3 09:27:21 2006
ORA-01555 caused by SQL statement below (Query Duration=11970 sec, SCN:
0x060d.619faa64):
Fri Mar 3 09:27:21 2006
DELETE STORE_CMV WHERE COUNTRY_ID = :B4 AND STORE_ID = :B3 AND
PHYSICAL_COUNT_TYPE_IND = :B2 AND CMV_DAT = :B1
Fri Mar 3 09:27:21 2006
SMON: Restarting fast_start parallel rollback
SMON: Parallel transaction recovery tried
Fri Mar 3 09:27:47 2006
found dead shared server 'S002', pid = (12, 1)
found dead shared server 'S010', pid = (20, 1)
Fri Mar 3 09:27:47 2006
SMON: Restarting fast_start parallel rollback
SMON: Parallel transaction recovery tried
Fri Mar 3 09:29:11 2006
Thread 1 advanced to log sequence 1910
Current log# 2 seq# 1910 mem# 0: /oracle/oradata11/redo02a.log
Current log# 2 seq# 1910 mem# 1: /oracle/oradata21/redo02b.log
Thread 1 advanced to log sequence 1911
Current log# 3 seq# 1911 mem# 0: /oracle/oradata11/redo03a.log
Current log# 3 seq# 1911 mem# 1: /oracle/oradata21/redo03b.log
Fri Mar 3 09:29:11 2006
ARC0: Evaluating archive log 1 thread 1 sequence 1909
ARC0: Beginning to archive log 1 thread 1 sequence 1909
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/archive/mcca/1_1909.arch'
Fri Mar 3 09:29:11 2006
ARC1: Evaluating archive log 1 thread 1 sequence 1909
ARC1: Unable to archive log 1 thread 1 sequence 1909
Log actively being archived by another process
ARC1: Evaluating archive log 2 thread 1 sequence 1910
ARC1: Beginning to archive log 2 thread 1 sequence 1910
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/archive/mcca/1_1910.arch'
ARC1: Completed archiving log 2 thread 1 sequence 1910
ARC1: Evaluating archive log 1 thread 1 sequence 1909
ARC1: Unable to archive log 1 thread 1 sequence 1909
Log actively being archived by another process
ARC1: Evaluating archive log 1 thread 1 sequence 1909
ARC1: Unable to archive log 1 thread 1 sequence 1909
Log actively being archived by another process
Fri Mar 3 09:29:27 2006
ARC0: Completed archiving log 1 thread 1 sequence 1909
Fri Mar 3 09:32:22 2006
ALTER SYSTEM SET DISPATCHERS=''(protocol=TCP)(disp=2)(con=700)'';
Fri Mar 3 09:32:37 2006
ALTER SYSTEM SET DISPATCHERS=''(protocol=TCP)(disp=3)(con=700)'';
Fri Mar 3 09:36:41 2006
ALTER SYSTEM SET shared_servers=0 SCOPE=BOTH;
Fri Mar 3 09:36:44 2006
idle dispatcher 'D002' terminated, pid = (83, 4)
Fri Mar 3 09:36:50 2006
ALTER SYSTEM SET DISPATCHERS=''(protocol=TCP)(disp=2)(con=700)'';
Fri Mar 3 09:36:50 2006
idle dispatcher 'D003' terminated, pid = (84, 2)
Fri Mar 3 09:36:51 2006
ALTER SYSTEM SET shared_servers=60 SCOPE=BOTH;
Fri Mar 3 09:47:38 2006
found dead shared server 'S002', pid = (12, 2)
found dead shared server 'S003', pid = (13, 1)
found dead shared server 'S007', pid = (17, 1)
Fri Mar 3 09:47:39 2006
SMON: Restarting fast_start parallel rollback
SMON: Parallel transaction recovery tried
Fri Mar 3 09:47:39 2006
found dead shared server 'S000', pid = (10, 1)
found dead shared server 'S001', pid = (11, 1)
found dead shared server 'S008', pid = (18, 2)
found dead shared server 'S020', pid = (30, 1)
Fri Mar 3 09:47:42 2006
SMON: Restarting fast_start parallel rollback
SMON: Parallel transaction recovery tried
Fri Mar 3 09:47:47 2006
found dead shared server 'S005', pid = (15, 1)
found dead shared server 'S009', pid = (19, 1)
found dead shared server 'S022', pid = (32, 1)
Fri Mar 3 09:47:47 2006
SMON: Parallel transaction recovery tried
SMON: Restarting fast_start parallel rollback
Fri Mar 3 09:47:50 2006
found dead shared server 'S011', pid = (21, 1)
found dead shared server 'S012', pid = (22, 1)
found dead shared server 'S019', pid = (29, 1)
found dead shared server 'S025', pid = (35, 1)
Fri Mar 3 09:47:51 2006
SMON: Parallel transaction recovery tried
Fri Mar 3 09:47:51 2006
found dead shared server 'S017', pid = (27, 1)
found dead shared server 'S014', pid = (24, 2)
found dead shared server 'S015', pid = (25, 1)
found dead shared server 'S018', pid = (28, 1)
found dead shared server 'S026', pid = (36, 1)
idle dispatcher 'D001' terminated, pid = (76, 3)
Fri Mar 3 09:47:56 2006
SMON: Parallel transaction recovery tried
Fri Mar 3 09:48:03 2006
found dead shared server 'S023', pid = (33, 1)
idle dispatcher 'D000' terminated, pid = (70, 1)
Fri Mar 3 10:00:23 2006
found dead shared server 'S000', pid = (10, 2)
found dead shared server 'S000', pid = (10, 3)
found dead shared server 'S001', pid = (11, 2)
Fri Mar 3 10:00:31 2006
SMON: Restarting fast_start parallel rollback
SMON: Parallel transaction recovery tried
Fri Mar 3 10:18:12 2006
Shutting down instance: further logons disabled
Fri Mar 3 10:19:22 2006
Shutting down instance (immediate)
License high water mark = 74
Waiting for dispatcher 'D003' to shutdown
Waiting for shared server 'S001' to die
All dispatchers and shared servers shutdown
Fri Mar 3 10:19:28 2006
ALTER DATABASE CLOSE NORMAL
Fri Mar 3 10:19:29 2006
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Mar 3 10:20:14 2006
Shutting down archive processes
Archiving is disabled
Fri Mar 3 10:20:14 2006
ARCH shutting down
Fri Mar 3 10:20:14 2006
ARCH shutting down
Fri Mar 3 10:20:14 2006
ARC0: Archival stopped
Fri Mar 3 10:20:14 2006
ARC1: Archival stopped
Fri Mar 3 10:20:15 2006
Thread 1 closed at log sequence 1911
Successful close of redo thread 1
Fri Mar 3 10:20:15 2006
Completed: ALTER DATABASE CLOSE NORMAL
Fri Mar 3 10:20:15 2006
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Mar 3 10:20:22 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 1
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.6.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 419430400
sga_max_size = 3222765832
large_pool_size = 369098752
java_pool_size = 33554432
control_files = /oracle/oradata11/control01.ctl,
/oracle/oradata21/control02.ctl, /oracle/oradata14/control03.ctl
db_block_size = 8192
db_cache_size = 1275068416
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=/oracle/archive/mcca/
log_archive_format = %t_%s.arch
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 600
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = mcca
dispatchers = (protocol=TCP)(disp=1)(con=700)
shared_servers = 60
max_shared_servers = 150
max_dispatchers = 4
local_listener = (ADDRESS = (PROTOCOL = TCP)(HOST =
10.200.0.43)(PORT = 1523))
session_cached_cursors = 100
job_queue_processes = 10
create_bitmap_area_size = 36700160
bitmap_merge_area_size = 20971520
cursor_sharing = FORCE
hash_join_enabled = TRUE
hash_area_size = 62914560
background_dump_dest = /oracle/product/9i/admin/mcca/bdump
user_dump_dest = /oracle/product/9i/admin/mcca/udump
core_dump_dest = /oracle/product/9i/admin/mcca/cdump
sort_area_size = 157286400
sort_area_retained_size = 52428800
db_name = mcca
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 157286400
workarea_size_policy = manual
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Fri Mar 3 10:20:27 2006
starting up 60 shared server(s) ...
starting up 1 dispatcher(s) for network address
'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=71
ARC0: Archival started
ARC1 started with pid=72
Fri Mar 3 10:20:30 2006
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Mar 3 10:20:30 2006
ARC1: Archival started
Fri Mar 3 10:20:30 2006
ARC0: Thread not mounted
Fri Mar 3 10:20:30 2006
ARC1: Thread not mounted
Fri Mar 3 10:20:30 2006
ALTER DATABASE MOUNT
Fri Mar 3 10:20:37 2006
Successful mount of redo thread 1, with mount id 3362685360
Fri Mar 3 10:20:37 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Fri Mar 3 10:20:37 2006
ALTER DATABASE OPEN
Fri Mar 3 10:20:37 2006
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 1911
Current log# 3 seq# 1911 mem# 0: /oracle/oradata11/redo03a.log
Current log# 3 seq# 1911 mem# 1: /oracle/oradata21/redo03b.log
Successful open of redo thread 1
Fri Mar 3 10:20:38 2006
SMON: enabling cache recovery
Fri Mar 3 10:20:38 2006
Successfully onlined Undo Tablespace 1.
Fri Mar 3 10:20:38 2006
SMON: enabling tx recovery
Fri Mar 3 10:20:38 2006
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication
found)
Completed: ALTER DATABASE OPEN
Fri Mar 3 10:27:21 2006
ALTER SYSTEM SET DISPATCHERS=''(protocol=TCP)(disp=2) (con=700)'';
Fri Mar 3 10:28:58 2006
Thread 1 advanced to log sequence 1912
Current log# 1 seq# 1912 mem# 0: /oracle/oradata11/redo01a.log
Current log# 1 seq# 1912 mem# 1: /oracle/oradata21/redo01b.log
Fri Mar 3 10:28:58 2006
ARC0: Evaluating archive log 3 thread 1 sequence 1911
Luis Claudio Arruda Figueiredo <[EMAIL PROTECTED]> escreveu:
Bom dia Marcus você pode disponibilizar as informações
do alert-log e seu init<sid>.ora.
Grato,
Luis Figueiredo.
--- Marcus Vinicius Miguel Pedro
<[EMAIL PROTECTED]> escreveu:
---------------------------------
Olá pessoal, tudo bem?!
Hoje tive um sério problema no meu banco de
produção...
O meu banco é um 9.2.0.6 Enterprise Edition rodando
num HP-UX 11.11, máquina com 4 processadores e 8GB de
memória.
Todos os parâmetros de memória do Oracle estão OK,
caso queiram eu disponibilizo aqui as informações dos
parâmetros que desejarem.
O banco trabalha com uma aplicação PL-Web, onde o
tipo de conexão é o Shared.
Vou citar uma procedure especificamente, que executa
em torno de 3 segs.
Hoje, essa procedure simplesmente não rodava...ao
observar no TOP, o meu servidor estava com 100% de
processamento. Ao se conectar ao banco via SQL*Plus
via dedicado a procedure rodou normal, via shared não
executou...
Analisei a performance do banco todo, assim como dos
shared servers e dispatchers. Não tinha espera
nenhuma, a taxa de busy estava baixíssima, enfim, tudo
aparentemente normal.
A solução encontrada foi dar um shutdown e startup
no banco. Tudo voltou ao normal.
Pelo que parece, os meus processos ora_s000,
ora_s001, ... estavam em colapso sugando 100% de CPU
da máquina e aparentemente não fazendo nada.
Alguém pode ter idéia do problema?
Obrigado!
Abraços
Vinicius
---------------------------------
Yahoo! doce lar. Faça do Yahoo! sua homepage.
[As partes desta mensagem que não continham texto
foram removidas]
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso
público e de inteira responsabilidade de seus
remetentes.
Acesse:
http://www.mail-archive.com/[email protected]/
--------------------------------------------------------------------------------------------------------------------------__________________________________________________________________
Este Grupo recebe o apoio da SQL Magazine -
www.devmedia.com.br/sqlmagazine
Yahoo! Grupos, um serviço oferecido
por: PUBLICIDADE
var lrec_target="_blank";var lrec_URL=new
Array();lrec_URL[1]="http://br.ard.yahoo.com/SIG=12foqt9gs/M=396547.7942355.8909035.2369893/D=brclubs/S=2137114689:HM/Y=BR/EXP=1141400389/A=3347461/R=0/id=flashurl/SIG=154o1osth/*http://lt.dell.com/lt/lt.aspx?CID=11515&LID=317815&DGC=BA&DGSeg=DHS&DURL=http://www1.la.dell.com/content/products/category.aspx/desktops?c%3dbr%26l%3dpt%26s%3ddhs";var
lrec_flashfile="http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.swf?clickTAG=javascript:LRECopenWindow(1)";var
lrec_altURL="http://br.ard.yahoo.com/SIG=12foqt9gs/M=396547.7942355.8909035.2369893/D=brclubs/S=2137114689:HM/Y=BR/EXP=1141400389/A=3347461/R=1/id=altimg/SIG=154o1osth/*http://lt.dell.com/lt/lt.aspx?CID=11515&LID=317815&DGC=BA&DGSeg=DHS&DURL=http://www1.la.dell.com/content/products/category.aspx/desktops?c%3dbr%26l%3dpt%26s%3ddhs";var
lrec_altimg="http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.gif";var
lrec_width=300;var lrec_height=250;
---------------------------------
Links do Yahoo! Grupos
Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
O uso que você faz do Yahoo! Grupos está sujeito
aos Termos do Serviço do Yahoo!.
_______________________________________________________
Yahoo! doce lar. Faça do Yahoo! sua homepage.
http://br.yahoo.com/homepageset.html
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso público e de inteira
responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/[email protected]/
--------------------------------------------------------------------------------------------------------------------------__________________________________________________________________
Este Grupo recebe o apoio da SQL Magazine - www.devmedia.com.br/sqlmagazine
Yahoo! Grupos, um serviço oferecido por: PUBLICIDADE
var lrec_target="_blank";var lrec_URL=new Array();
lrec_URL[1]="http://br.ard.yahoo.com/SIG=12fnkojlf/M=396547.7942355.8909035.2369893/D=brclubs/S=2137114689:HM/Y=BR/EXP=1141401903/A=3347461/R=0/id=flashurl/SIG=154o1osth/*http://lt.dell.com/lt/lt.aspx?CID=11515&LID=317815&DGC=BA&DGSeg=DHS&DURL=http://www1.la.dell.com/content/products/category.aspx/desktops?c%3dbr%26l%3dpt%26s%3ddhs";;
var
lrec_flashfile="http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.swf?clickTAG=javascript:LRECopenWindow(1)";
var
lrec_altURL="http://br.ard.yahoo.com/SIG=12fnkojlf/M=396547.7942355.8909035.2369893/D=brclubs/S=2137114689:HM/Y=BR/EXP=1141401903/A=3347461/R=1/id=altimg/SIG=154o1osth/*http://lt.dell.com/lt/lt.aspx?CID=11515&LID=317815&DGC=BA&DGSeg=DHS&DURL=http://www1.la.dell.com/content/products/category.aspx/desktops?c%3dbr%26l%3dpt%26s%3ddhs";;
var
lrec_altimg="http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.gif";;
var
lrec_width=300;var lrec_height=250;
---------------------------------
Links do Yahoo! Grupos
Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
O uso que você faz do Yahoo! Grupos está sujeito aos Termos do Serviço do
Yahoo!.
---------------------------------
Yahoo! Acesso Grátis
Internet rápida e grátis. Instale o discador agora!
[As partes desta mensagem que não continham texto foram removidas]
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso público e de inteira
responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/[email protected]/
--------------------------------------------------------------------------------------------------------------------------__________________________________________________________________
Este Grupo recebe o apoio da SQL Magazine - www.devmedia.com.br/sqlmagazine
Links do Yahoo! Grupos
<*> Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
<*> Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
<*> O uso que você faz do Yahoo! Grupos está sujeito aos:
http://br.yahoo.com/info/utos.html
![http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.gif";var](http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.gif";var){kind=link}
![http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.gif"](http://br.i1.yimg.com/br.yimg.com/i/br/ads7/020306y300x250_mar05_dhs_fds_dim5150.gif"){kind=link}