Log in to the test database and find the normal user logged in and hang on.
[Oracle@rac2 ~]$ Sqlplus Scott/tiger
Sql*plus:release 11.2.0.1.0 Production on Wed Jan 15 19:22:25 2014
Copyright (c) 1982, Oracle. All rights reserved.
。。。。
Hang's not responding here.
Login with SYS no exception
View the event for the hang session
Sql> Select Sid,username,event,p1,p2,p3 from v$session where status= ' ACTIVE ' and username are not null;
SID USERNAME EVENT P1 P2
---------- ------------------------- ------------------------------------------------------- ---------- ----------
P3
----------
SYS sql*net message to client 1650815232 1
0
SCOTT Write complete Waits 5 176
0
SYS Streams aq:waiting for messages in the queue 12884 1388097736
1
is always write complete waits wait
This wait is generally due to the IO performance, or the DBWR process workload caused, and my situation is significantly different
No jobs or other operations are running on the test library
Operating system load is also very low, almost no
This waiting P1, P2 respectively is file#,block#
Sql> Select Name,parameter1,parameter2,parameter3,wait_class from v$event_name where name= ' write complete Waits ';
NAME PARAMETER1 PARAMETER2 PARAMETER3 wait_class
--------------------------------------------------------------------------------
Write Complete Waits file# block#
See what the Waiting object is
Sql> Select Owner,segment_name, Segment_type
2 from Dba_extents
3 Where file_id = &file_id
4 and &block_id between block_id and block_id + blocks-1;
Enter value for File_id:5
Old 3:where file_id = &file_id
New 3:where file_id = 5
Enter value for block_id:176
Old 4:and &block_id between block_id and block_id + blocks-1
New 4:and 176 between block_id and block_id + blocks-1
OWNER segment_name Segment_type
------------------------------------------------------------ -------------------- ------------------------------- -----
SYS _syssmu14_277467141$ TYPE2 UNDO
Also found that many operations could not be completed at this time
such as flush buffer_cahce,switch logfile, etc.
To view the alter log discovery:
Fri Jan 10 20:07:17 2014
Suspending Mmon action ' undo usage ' for 82800 seconds
Fri Jan 10 22:00:01 2014
Setting Resource Manager Plan scheduler[0x3007]:D Efault_maintenance_plan via SCHEDULER window
Setting Resource Manager plan Default_maintenance_plan via parameter
Fri Jan 10 22:00:01 2014
Starting background Process VKRM
Fri Jan 10 22:00:01 2014
VKRM started with pid=38, OS id=13518
Fri Jan 10 22:34:33 2014
DW00 started with pid=51, OS id=14090, wid=2, Job SCOTT. Sys_export_schema_01
Fri Jan 10 22:47:38 2014
Suspending Mmon Slave action Kewrmrfsa_ for 82800 seconds
Fri Jan 10 22:49:52 2014
DW00 started with pid=54, OS id=14344, wid=2, Job SCOTT. Sys_export_schema_01
Fri Jan 10 23:15:20 2014
Suspending Mmon Slave action Kewrmafsa_ for 82800 seconds
Sat Jan 11 19:29:00 2014
Suspending Mmon action ' undo usage ' for 82800 seconds
Sat Jan 11 23:05:39 2014
Suspending Mmon Slave action Kewrmafsa_ for 82800 seconds
Sun Jan 12 00:35:17 2014
Suspending Mmon Slave action Kewrmrfsa_ for 82800 seconds
Sun Jan 12 18:43:51 2014
Suspending Mmon action ' undo usage ' for 82800 seconds
Sun Jan 12 23:11:10 2014
Suspending Mmon Slave action Kewrmafsa_ for 82800 seconds
Mon Jan 13 00:35:04 2014
Suspending Mmon Slave action Kewrmrfsa_ for 82800 seconds
Mon Jan 13 18:00:27 2014
Suspending Mmon action ' undo usage ' for 82800 seconds
Mon Jan 13 23:13:24 2014
Suspending Mmon Slave action Kewrmafsa_ for 82800 seconds
Discover 2 notable places
1,setting Resource Manager Plan scheduler[0x3007]:D Efault_maintenance_plan via SCHEDULER window
2,suspending Mmon Slave action Kewrmrfsa_ for 82800 seconds
Suspending Mmon action ' undo usage ' for 82800 seconds
About 1, there is an article that could be a bug caused by resource Manager plan,
Resouce Manager BUG:
Oracle Support-march, 10:35:20 AM gmt-04:00 [ODM Action Plan]
The hang analyze shows several chains where the blocking session was waiting on RESMGR:CPU quantum.
Please disable Resource Manager and check it the issue still occurs.
It's not the same thing with me.
2,mmon process exception, Mmon process is related to AWR, can kill, this is the test environment, so I killed this process directly
[Oracle@rac2 trace]$ ps aux | grep Mmon
Grid 4414 0.0 0.6 419584 13084? Ss Jan10 0:00 asm_mmon_+asm2
Grid 4749 0.0 1.4 306724 30028? Sl Jan10 1:09/OPT/APP/GRID/PRODUCT/11.2.0/JDK/JRE//BIN/JAVA-DORACLE.SUPERCLUSTER.CLUSTER.SERVER=EONSD- djava.net.preferipv4stack=true-djava.util.logging.config.file=/opt/app/grid/product/11.2.0/srvm/admin/ Logging.properties-classpath/opt/app/grid/product/11.2.0/jdk/jre//lib/rt.jar:/opt/app/grid/product/11.2.0/jlib /srvm.jar:/opt/app/grid/product/11.2.0/jlib/srvmhas.jar:/opt/app/grid/product/11.2.0/jlib/supercluster.jar:/ Opt/app/grid/product/11.2.0/jlib/supercluster-common.jar:/opt/app/grid/product/11.2.0/ons/lib/ons.jar Oracle.supercluster.impl.cluster.EONSServerImpl
Oracle 5054 0.0 4.8 992696 100980? Ss Jan10 0:06 Ora_mmon_rac2
Oracle 26959 0.0 0.0 3920 664 PTS/5 r+ 19:21 0:00 grep mmon
[Oracle@rac2 trace]$ kill-9 5054
[Oracle@rac2 trace]$ ps aux | grep Mmon
Grid 4414 0.0 0.6 419584 13084? Ss Jan10 0:00 asm_mmon_+asm2
Grid 4749 0.0 1.4 306724 30028? Sl Jan10 1:09/OPT/APP/GRID/PRODUCT/11.2.0/JDK/JRE//BIN/JAVA-DORACLE.SUPERCLUSTER.CLUSTER.SERVER=EONSD- djava.net.preferipv4stack=true-djava.util.logging.config.file=/opt/app/grid/product/11.2.0/srvm/admin/ Logging.properties-classpath/opt/app/grid/product/11.2.0/jdk/jre//lib/rt.jar:/opt/app/grid/product/11.2.0/jlib /srvm.jar:/opt/app/grid/product/11.2.0/jlib/srvmhas.jar:/opt/app/grid/product/11.2.0/jlib/supercluster.jar:/ Opt/app/grid/product/11.2.0/jlib/supercluster-common.jar:/opt/app/grid/product/11.2.0/ons/lib/ons.jar Oracle.supercluster.impl.cluster.EONSServerImpl
Oracle 26961 0.0 0.0 3920 664 PTS/5 r+ 19:21 0:00 grep mmon
Alter LOG:
Wed Jan 15 19:23:45 2014
Restarting dead background process Mmon
Wed Jan 15 19:23:45 2014
Mmon started with pid=24, OS id=27085
Wed Jan 15 19:26:33 2014
But after kill, you still can't log into the system properly.
Check for other abnormal session blocking
Sql> Select Blocking_instance,blocking_session from v$session where sid=59;
Blocking_instance blocking_session
----------------- ----------------
2 18
Sql> Select Sid,username,event,blocking_instance,blocking_session from v$session where sid=18;
No rows selected
Session 59 is blocked by session 18, but there is no session 18 information
Decide to restart the RAC2 instance
[Grid@rac2 ~]$ srvctl stop instance-d rac-n RAC2
Here waited a few minutes and found nothing to react
To view the alter log, there is only one message:
Wed Jan 15 19:26:33 2014
Pmon failed to delete process, see Pmon trace file
Check Pmon tracefile Information:
2014-01-15 19:27:43.564
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
2014-01-15 19:27:57.503
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
2014-01-15 19:28:11.474
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
2014-01-15 19:28:25.360
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
2014-01-15 19:28:39.244
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
2014-01-15 19:28:53.137
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
Pmon Waiting for Csecs
2014-01-15 19:29:06.990
Found process 0x52bcce2c pid=34 serial=6 ospid = 10671 dead
2014-01-15 19:29:06.990
deleting process 0x52bcce2c pid=34 serial=6 priority=0
Need redo log switch, current log full
Need redo log switch, current log full
Need redo log switch, current log full
Need redo log switch, current log full
Need redo log switch, current log full
Need redo log switch, current log full
Deletion of process 52bcce2c pid=34 seq=6 Prog=false unsuccessful
From the Pmon information can be seen, need redo log switch, current log full
Current redo log needs to be switched, but cannot be completed
To view archived log information:
sql> archive log list;
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination/home/oracle/archivelog
Oldest online log sequence 794
Next log sequence to archive 794
Current log Sequence 796
Sql> Select dest_name,status,error,target,process from V$archive_dest where dest_name=1;
Dest_name STATUS ERROR
------------------------------ ------------------ --------------------
TARGET PROCESS
-------------- --------------------
Log_archive_dest_1 ERROR Ora-19502:write
Error on file "",
Block number
(Block size=)
PRIMARY ARCH
Sql>! Df-h
Filesystem Size used Avail use% mounted on
/dev/sda2 23G 16G 5.5G 75%/
/DEV/SDA1 99M 12M 83M 12%/boot
Tmpfs 1014M 614M 400M 61%/DEV/SHM
Error finding archive path, ORA-19502
To see the disk, the disk also has 5 multi-g space, completely no problem, and then test the disk read and write permissions, no problem
It's weird.
Re-specify an archive path
Sql> alter system set log_archive_dest_1= ' location=/home/oracle/';
System Altered
sql> alter system switch logfile;
System altered.
I found that I can switch logs normally.
Normal users can also connect at this time
[Oracle@rac2 archivelog]$ Sqlplus Scott/tiger
Sql*plus:release 11.2.0.1.0 Production on Wed Jan 15 19:42:18 201
Copyright (c) 1982, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production
With the partitioning, Real application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real application testing options
Sql>