Menu de navegação

[mpunganParticipante]

Pessoal, este script abaixo foi executado no meu banco, porém demorou muito tempo para ser executado. Após um tempo fechei a sessão onde o mesmo estava sendo executado. Ai os serviços do banco começaram a dar falha de conexão como se o banco estivesse fora.

CREATE OR REPLACE FORCE VIEW “SOLICITACOES”.”SP_MONITORES” (“IDPESSOA”, “CDPESSOA”, “NONOME”, “TXENDERECO”, “NOBAIRRO”, “NOCIDADE”, “CEP”, “SGUFRESIDENCIA”, “FONE”, “FAX”, “CIC”, “TXPAISORIGEM”, “CDUNICO”)
AS
SELECT f.cdpessoa idpessoa,
af.cdmatricula cdpessoa,
f.nonome,
f.txendereco,
f.nobairro,
f.nocidade,
TO_CHAR(f.cep),
f.sgufresidencia,
ip.nudddtelef
|| ip.nutelefone fone,
ip.nudddfax
|| ip.nufax fax,
ip.cic,
SUBSTR(SIU.FUN_SIU_GET_PAIS(S.SG_NACIONALIDADE),1,40) TXPAISORIGEM,
TO_CHAR(f.cd_pessoa) cdunico
FROM fa_identificacao_das_pessoas ip,
fa_pessoas f,
fa_alunos_financeiro af,
fa_cursos c,
solicitacoes.sp_config_area_ocor sp,
siu_pessoa s
WHERE f.cdpessoa = ip.cdpessoa
AND af.cdpessoa = f.cdpessoa
AND af.cdcurso = c.cdcurso
AND af.ano = sp.ano
AND af.ocorrencia = sp.ocorrencia
AND s.id_pessoa = f.id_pessoa
AND sp.cdareaocor = 2 — área financeira
AND af.cdsitacad IS NULL
AND c.cdproreitoria IN (2, 3, 4, 8, 9)
—- Projeto 6 – Informações PROMUSIT
UNION ALL
SELECT m.id_pessoa,
m.cdpessoa,
m.nonome,
m.txendereco,
m.nobairro,
m.nocidade,
TO_CHAR(m.cep),
m.sgufresidencia,
m.dddfone || m.fone,
m.dddfax || m.fax,
m.cic,
TXPAISORIGEM,
TO_CHAR(m.cdunico)
FROM SIPROEX.VW_SPX_EXECUTOR_PROMUSIT M;

Em um nos logs do RAC encontrei os registros abaixo; como se a instance tivesse se perdido, travada. Minha pergunta será que o SCRIPT acima poderia ter causado esse problema? Se alguem tiver alguma dica, ou tiver uma maneira de eu conseguir identificar o que ocorreu, desde já agradeço.

Thread 3 advanced to log sequence 211956 (LGWR switch)
Current log# 301 seq# 211956 mem# 0: +DG_REDOS/rs10/onlinelog/group_301.ora
Current log# 301 seq# 211956 mem# 1: +DG_REDOS/rs10/onlinelog/group_301_m.ora
Mon Jan 02 11:41:38 BRST 2012
LMD0 (ospid: 11761) is not heartbeating for 203 seconds.
Mon Jan 02 11:42:37 BRST 2012
LMON detects unhealthy receivers.
Please check LMON and DIAG trace files for detail.
Mon Jan 02 11:42:37 BRST 2012
LMON (ospid: 11759) is terminating the instance.
LMON: terminating instance due to error 481
Mon Jan 02 11:44:40 BRST 2012
Termination issued to instance processes. Waiting for the processes to exit
Mon Jan 02 11:44:49 BRST 2012
Instance termination failed to kill one or more processes
Mon Jan 02 11:45:25 BRST 2012
Instance terminated by LMON, pid = 11759
Mon Jan 02 11:46:02 BRST 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 eth1 10.40.162.0 configured from OCR for use as a cluster interconnect
Interface type 1 eth0 10.40.161.0 configured from OCR for use as a public interface
Picked latch-free SCN scheme 3
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.5.0.
System parameters with non-default values:
processes = 1200
sessions = 1325
sga_max_size = 16106127360
__shared_pool_size = 2231369728
shared_pool_size = 2147483648
__large_pool_size = 16777216
__java_pool_size = 335544320
java_pool_size = 335544320
__streams_pool_size = 419430400
streams_pool_size = 419430400
spfile = +DG_DB/rs10/spfilers10.ora
resource_manager_plan = INTERNAL_PLAN
sga_target = 10737418240
control_files = +DG_DB/rs10/controlfile/current.260.671219963, +DG_REDOS/RS10/controlfile/current.271.672588549
db_block_size = 8192
__db_cache_size = 7717519360
compatible = 10.2.0.1.0
log_archive_dest_1 = LOCATION=/shared1/archives OPTIONAL
log_archive_dest_2 = LOCATION=+DG_DB/RS10/ARCHIVES
log_archive_format = arch_%t_%s_%r.dbf
db_files = 300
db_file_multiblock_read_count= 16
cluster_database = TRUE
cluster_database_instances= 3
db_create_file_dest = +DG_DB
thread = 3
instance_number = 3
undo_management = AUTO
undo_tablespace = UNDOTBS3
undo_retention = 1287780
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=RS10XDB)
local_listener = (ADDRESS = (PROTOCOL = TCP)(HOST = buzios-vip.RS.br)(PORT = 1521))
remote_listener = LISTENERS_RS10
smtp_out_server = smtp.RS.br:25
plsql_compiler_flags = INTERPRETED, NON_DEBUG
plsql_code_type = INTERPRETED
job_queue_processes = 20
_system_trig_enabled = TRUE
parallel_execution_message_size= 4096
background_dump_dest = /oracle/admin/RS10/bdump
user_dump_dest = /oracle/admin/RS10/udump
max_dump_file_size = UNLIMITED
core_dump_dest = /oracle/admin/RS10/cdump
audit_file_dest = /oracle/admin/RS10/adump
audit_trail = DB
db_name = RS10
open_cursors = 1200
optimizer_index_cost_adj = 80
pga_aggregate_target = 8589934592
_allow_level_without_connect_by= TRUE
aq_tm_processes = 1
Deprecated system parameters with specified values:
plsql_compiler_flags
End of deprecated system parameter listing
Cluster communication is configured to use the following interface(s) for this instance
10.40.162.101
Mon Jan 02 11:46:02 BRST 2012
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
PMON started with pid=2, OS id=3768
DIAG started with pid=3, OS id=3770
PSP0 started with pid=4, OS id=3772
LMON started with pid=5, OS id=3774
LMD0 started with pid=6, OS id=3776
LMS0 started with pid=7, OS id=3778
LMS1 started with pid=8, OS id=3782
MMAN started with pid=9, OS id=3786
DBW0 started with pid=10, OS id=3788
LGWR started with pid=11, OS id=3790
CKPT started with pid=12, OS id=3792
SMON started with pid=13, OS id=3794
RECO started with pid=14, OS id=3796
CJQ0 started with pid=15, OS id=3798
MMON started with pid=16, OS id=3800
Mon Jan 02 11:46:03 BRST 2012
starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
MMNL started with pid=17, OS id=3802
Mon Jan 02 11:46:03 BRST 2012
starting up 1 shared server(s) …
Mon Jan 02 11:46:04 BRST 2012
lmon registered with NM – instance id 3 (internal mem no 2)
Mon Jan 02 11:46:04 BRST 2012
Reconfiguration started (old inc 0, new inc 46)
List of nodes:
0 1 2
Global Resource Directory frozen
* allocate domain 0, invalid = TRUE
Communication channels reestablished
* domain 0 valid = 1 according to instance 0
Mon Jan 02 11:46:04 BRST 2012
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Mon Jan 02 11:46:04 BRST 2012
LMS 0: 0 GCS shadows cancelled, 0 closed
Mon Jan 02 11:46:05 BRST 2012
LMS 1: 0 GCS shadows cancelled, 0 closed
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Mon Jan 02 11:46:05 BRST 2012
LMS 0: 0 GCS shadows traversed, 0 replayed
Mon Jan 02 11:46:05 BRST 2012
LMS 1: 0 GCS shadows traversed, 0 replayed
Mon Jan 02 11:46:05 BRST 2012
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
LCK0 started with pid=20, OS id=3829
Mon Jan 02 11:46:07 BRST 2012
ALTER DATABASE MOUNT
Mon Jan 02 11:46:07 BRST 2012
Starting background process ASMB
ASMB started with pid=22, OS id=3840
Starting background process RBAL
RBAL started with pid=23, OS id=3845
Loaded ASM Library – Generic Linux, version 2.0.3 (KABI_V2) library for asmlib interface
Mon Jan 02 11:46:10 BRST 2012
SUCCESS: diskgroup DG_DB was mounted
SUCCESS: diskgroup DG_REDOS was mounted
Mon Jan 02 11:46:14 BRST 2012
Setting recovery target incarnation to 2
Mon Jan 02 11:46:14 BRST 2012
Successful mount of redo thread 3, with mount id 3639690280
Mon Jan 02 11:46:14 BRST 2012
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE MOUNT
Mon Jan 02 11:46:15 BRST 2012
ALTER DATABASE OPEN
Picked broadcast on commit scheme to generate SCNs
Mon Jan 02 11:46:18 BRST 2012
Sending CIC to internal enable redo thread
Mon Jan 02 11:46:18 BRST 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=25, OS id=4292
Mon Jan 02 11:46:18 BRST 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=26, OS id=4294
Mon Jan 02 11:46:18 BRST 2012
Thread 3 opened at log sequence 211958
Current log# 303 seq# 211958 mem# 0: +DG_REDOS/RS10/onlinelog/group_303.ora
Current log# 303 seq# 211958 mem# 1: +DG_REDOS/RS10/onlinelog/group_303_m.ora
Successful open of redo thread 3
Mon Jan 02 11:46:18 BRST 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jan 02 11:46:18 BRST 2012
SMON: enabling cache recovery
Mon Jan 02 11:46:18 BRST 2012
ARC0: Becoming the ‘no FAL’ ARCH
ARC0: Becoming the ‘no SRL’ ARCH
Mon Jan 02 11:46:18 BRST 2012
ARC1: Becoming the heartbeat ARCH
Mon Jan 02 11:46:20 BRST 2012
Successfully onlined Undo Tablespace 7.
Mon Jan 02 11:46:20 BRST 2012
SMON: enabling tx recovery
Mon Jan 02 11:46:18 BRST 2012
ARC0: Becoming the ‘no FAL’ ARCH
ARC0: Becoming the ‘no SRL’ ARCH
Mon Jan 02 11:46:18 BRST 2012
ARC1: Becoming the heartbeat ARCH
Mon Jan 02 11:46:20 BRST 2012
Successfully onlined Undo Tablespace 7.
Mon Jan 02 11:46:20 BRST 2012
SMON: enabling tx recovery
Mon Jan 02 11:46:20 BRST 2012
Database Characterset is WE8ISO8859P1
Opening with Resource Manager plan: INTERNAL_PLAN
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=27, OS id=4333
Mon Jan 02 11:46:26 BRST 2012
Completed: ALTER DATABASE OPEN
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_PESSOA’ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_PESSOA’,’SRVCO_BUZIOS’ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_BUZIOS’,’SRVCO_PESSOA’,’SRVCO_POSNOVO’ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_POSNOVO’,’SRVCO_BUZIOS’,’SRVCO_PESSOA’,’SRVCO_EAD’ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_EAD’,’SRVCO_POSNOVO’,’SRVCO_BUZIOS’,’SRVCO_PESSOA’,’SRVCO_SEGURANCA’ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_SEGURANCA’,’SRVCO_EAD’,’SRVCO_POSNOVO’,’SRVCO_BUZIOS’,’SRVCO_PESSOA’,’SRVCO_101103′ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:36 BRST 2012
ALTER SYSTEM SET service_names=’SRVCO_101103′,’SRVCO_SEGURANCA’,’SRVCO_EAD’,’SRVCO_POSNOVO’,’SRVCO_BUZIOS’,’SRVCO_PESSOA’,’SRVCO_RS103′ SCOPE=MEMORY SID=’RS103′;
Mon Jan 02 11:46:37 BRST 2012
3′;
Mon Jan 02 11:56:36 BRST 2012
D=’RS103′;
Mon Jan 02 12:00:21 BRST 2012
Thread 3 advanced to log sequence 211959 (LGWR switch)
Current log# 304 seq# 211959 mem# 0: +DG_REDOS/RS10/onlinelog/group_304.ora
Current log# 304 seq# 211959 mem# 1: +DG_REDOS/RS10/onlinelog/group_304_m.ora
Mon Jan 02 12:34:02 BRST 2012

[mpunganParticipante]

Quando executei o código acima para criar a view, o meu sqlplus ficou muito tempo executando para criar, então eu fechei o sqlplus para tentar executar novamente. Verificando o meu alert.log achei as mensagens abaixo:
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)
Mon Jan 02 11:45:31 BRST 2012
WARNING: inbound connection timed out (ORA-3136)

Ouve uma especie de falha de conexão entre os nós no momente em que o script esta sendo executado. Será que esse tipo de create pode acarretar esse problema?

[rmanParticipante]

@mpungan

ORA-3136 é problema de rede, creio não estar relacionado a criar a view, verifique se esse erro já aconteceu em outras ocasiões…

[fabiogaleraParticipante]

Não estão relacionados.

Inbound Connection, como dito ao nosso colega, pode ser um problema de rede, bugs do oracle, ou até problemas de configurações.

Quanto o CREATE VIEW demorar ou não terminar, é porque talvez alguém esteja usando a View.

[Autor]

Posts