[RDBMS 9.2.0.7] : PMON latch

[PpPool]

Bonjour à tous

sur une de nos bases que nous avons upgradé la semaine dernière avec succés (9.2.0.1 vers 9.2.0.7), nous avons eut cette nuit des messages d'erreur dans le fichier alert, au moment du lancement d'un backup à froid.

Je précise qu'à cette heure, aucun user n'était connecté, il n'y avait aucune activité au niveau de la base.

Ces messages sont intervenus, juste après le SHUTDOWN de la base :

PMON failed to acquire latch, see PMON dump

Extrait du fichier log :

Tue Mar 13 00:00:00 2007
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 11
All dispatchers and shared servers shutdown
Tue Mar 13 00:00:07 2007
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Tue Mar 13 00:00:09 2007
ALTER DATABASE CLOSE NORMAL
Tue Mar 13 00:00:09 2007
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Mar 13 00:00:09 2007
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1169
Successful close of redo thread 1
Tue Mar 13 00:00:10 2007
Completed: ALTER DATABASE CLOSE NORMAL
Tue Mar 13 00:00:10 2007
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
Tue Mar 13 00:01:57 2007
Starting ORACLE instance (normal)
Tue Mar 13 00:01:57 2007
Running with 1 strand for Non-Enterprise Edition
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
Running with 1 strand for Non-Enterprise Edition
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.7.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 54525952
large_pool_size = 8388608
java_pool_size = 0
control_files = D:\oracle\oradata\PITRE\CONTROL01.CTL, D:\oracle\oradata\PITRE\CONTROL02.CTL, D:\oracle\oradata\PITRE\CONTROL03.CTL
db_block_size = 4096
db_cache_size = 33554432
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 8
fast_start_mttr_target = 0
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 900
remote_login_passwordfile= EXCLUSIVE
db_domain = aglae.fr
instance_name = PITRE
dispatchers = (PROTOCOL=TCP) (SERVICE=PITREXDB)
utl_file_dir = C:\
job_queue_processes = 10
hash_join_enabled = FALSE
background_dump_dest = D:\oracle\admin\PITRE\bdump
user_dump_dest = D:\oracle\admin\PITRE\udump
core_dump_dest = D:\oracle\admin\PITRE\cdump
sort_area_size = 524288
db_name = PITRE
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 16777216
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
Tue Mar 13 00:02:00 2007
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Oracle Data Guard is not available in this edition of Oracle.
Tue Mar 13 00:02:01 2007
ALTER DATABASE MOUNT
Tue Mar 13 00:02:05 2007
Successful mount of redo thread 1, with mount id 4026913593
Tue Mar 13 00:02:05 2007
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Mar 13 00:02:05 2007
ALTER DATABASE OPEN
Tue Mar 13 00:02:06 2007
Thread 1 opened at log sequence 1169
Current log# 1 seq# 1169 mem# 0: D:\ORACLE\ORADATA\PITRE\REDO01.LOG
Current log# 1 seq# 1169 mem# 1: C:\ORACLE\ORADATA\PITRE\REDO011.LOG
Successful open of redo thread 1
Tue Mar 13 00:02:06 2007
SMON: enabling cache recovery
Tue Mar 13 00:02:07 2007
Successfully onlined Undo Tablespace 1.
Tue Mar 13 00:02:07 2007
SMON: enabling tx recovery
Tue Mar 13 00:02:07 2007
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

extrait du fichier pmon.trc

Dump file d:\oracle\admin\pitre\bdump\pitre_pmon_1576.trc
Tue Mar 13 00:00:07 2007
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Release 9.2.0.7.0 - Production
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: pitre

Redo thread mounted by this instance: 1

Oracle process number: 2

Windows thread id: 1576, image: ORACLE.EXE


*** 2007-03-13 00:00:07.272
*** SESSION ID1.1) 2007-03-13 00:00:07.256
PMON unable to acquire latch 21aea40 process allocation
possible holder pid = 14 ospid=1908
*** 2007-03-13 00:00:07.287
PMON unable to acquire latch 21aea40 process allocation
possible holder pid = 14 ospid=1908
....

Avez vous une idée du pb merci.

@

[orafrance]

j'ai trouvé ce bug : Bug 3634252 - PMON unable to acquire latch when JOB_QUEUE_PROCESSES altered

As-tu changé le paramètre JOB_QUEUE_PROCESSES dynamiquement (ALTER SYSTEM) ?

T'as essayé de faire :

Code :

1
2
3
shutdown abort
startup restrict
shutdown immediate

Sinon, il faudrait que tu listes le contenu de v$session, v$process et v$sql_text juste avant le shutdown pour retrouver ce que fait la session pid=14.

[PpPool]

Envoyé par Fred_D

j'ai trouvé ce bug : Bug 3634252 - PMON unable to acquire latch when JOB_QUEUE_PROCESSES altered

As-tu changé le paramètre JOB_QUEUE_PROCESSES dynamiquement (ALTER SYSTEM) ?

T'as essayé de faire :

Code :

Sinon, il faudrait que tu listes le contenu de v$session, v$process et v$sql_text juste avant le shutdown pour retrouver ce que fait la session pid=14.

salut fred

merci bcq pour ta réponse

1/ ton URL n'est malheureusement pas correcte. Peux tu me la redonner ?
Message metalink : Invalid Document ID

2/ je n'ai pas changé le paramètre JOB_QUEUE_PROCESSES. Il est à 10.

3/ je n'ai pas eut besoin de faire un ABORT, les latchs ont durés 3 s au moment du SHUTDOWN et puis après la base a redémarré sans pb, après le cold backup.

4/ dans v$process, j'ai toujours ma session pid=14

ADDR PID SPID USERNAME SERIAL# TERMINAL PROGRAM TRACEID B LATCHWAI LATCHSPI PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
66E1869C 14 1920 SYSTEM 15 PITRE ORACLE.EXE 196864 241992 0 241992

merci de ton aide

[orafrance]

1/ recherche la note 3634252.8 dans Metalink
2/ ok
3/ bah alors... il n'y a pas de problème
4/ et elle fait quoi cette session ?

[PpPool]

1/ ok, apparement cela ne me concerne pas il s'agit d'un bug fixé dans la 9.2.0.6.nous avons migré en 9.2.0.7

3/ il n'y a pas vraiment de pb sur la base en effet. simplement quand j'ai vu çà dans le fichier alert, j'ai cherché à savoir ce que c'était.
c'est la première fois que j'ai ce genre d'event.
je voudrai connaitre son origine.

4/ je retrouve le pid dans ma vue v$process. mais je n'arrive pas à identifier de quoi il s'agit (OS : W2000 server SP4)
Comment faire ?

sachant que dans mon fichier Alert.log voici les pid de mes process

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

Merci de ton aide

[PpPool]

Je viens de relancer un cold backup et tout s'est bien passé.

Lorsque je consulte ma vue v$process, le pid 14 a disparu.

1/ pour ma culture comment identifier le process oracle quand on a son pid ?
la vue v$process ne me donne pas d'autres explications

2/ je vais ce soir relancer le job, je vous tiens au courant.

3/ kk'un a t-il déjà eut ce genre d'event ? a quoi peut on l'attribuer ? pb de libération de ressources ?
sur ce server, nous avons de manière aléatoire (tous les 2/3 mois) un reboot automatique de l'agent HP => vidage cache mémoire (le bel écran bleu windows !
aussi bien le support hp que celui de microsoft n'ont réuss ià trouver une explication qui tienne la route.
Il renvoi la balle à oracle -) lol

je suis de plus en plus persuadé qu'il y a peut être un pb avec les barrettes mémoires sur ce server.

merci de vos lumières sur ce sujet.

[orafrance]

1/ spid = os process
3/ bah oui... c'est Oracle qui peut répondre c'est un latch dans la base... faut trouver la cause du latch

Moi j'voterai bien pour un buffer mal paramètré

[PpPool]

Envoyé par Fred_D

1/ spid = os process
3/ bah oui... c'est Oracle qui peut répondre c'est un latch dans la base... faut trouver la cause du latch

Moi j'voterai bien pour un buffer mal paramètré

on a du en effet la semaine dernière avant de passer l'upgrade vers la 9.2.0.7
passer :
la JAVA_POOL_SIZE de 0 à 150 M
la SHARED_POOL_SIZE de 52 à 150M

après l'install, on a repassé la java_pool_size à 0M et la shared_pool_size à 52M comme avant !

quand je regarde au niveau d'oem mes pools partagés semblent corrects