在 primary 看到日志成功传到了 physical standby （active dataguard）上，但是没有在 standby 上应用。SSH到备库主机上，"sqlplus / as sysdba" 长时间挂起并且不报错，通过连接描述符连接时，
本地：
-bash-3.00$ sqlplus system/password@ads_standby
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 8 11:24:41 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-03135: connection lost contact
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name:
客户端：
C:\Users\系统部>sqlplus system/password@172.11.173.120/ads.china.com.cn
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 10月 8 11:44:28 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-03135: 连接失去联系
进程 ID: 0
会话 ID: 0 序列号: 0
请输入用户名:
alert日志会产生如下报错：
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 08-OCT-2012 11:45:28
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.11.2.108)(PORT=50728))
解决办法：
[oracle@ora11gR2 ~]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 18 17:56:24 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug hanganalyze 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/test/test/trace/test_ora_6979.trc
SQL> exit
Disconnected from ORACLE
[oracle@ora11gR2 ~]$ more /u01/oracle/diag/rdbms/test/test/trace/test_ora_6979.trc
Trace file /u01/oracle/diag/rdbms/test/test/trace/test_ora_6979.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/oracle/product/11.2.0/db_1
System name: Linux
Node name: ora11gR2
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010
Machine: i686
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 6979, image: oracle@ora11gR2
*** 2012-10-18 17:56:25.908
Processing Oradebug command 'setmypid'
*** 2012-10-18 17:56:25.908
Oradebug command 'setmypid' console output:
*** 2012-10-18 17:56:29.408
Processing Oradebug command 'hanganalyze 3'
===============================================================================
HANG ANALYSIS:
ERROR: Can not perform hang analysis dump without a process
state object and a session state object.
( process=(nil), sess=(nil) )
===============================================================================
*** 2012-10-18 17:56:29.438
Oradebug command 'hanganalyze 3' console output:
*** 2012-10-18 17:56:59.144
Processing Oradebug command 'tracefile_name'
*** 2012-10-18 17:56:59.144
Oradebug command 'tracefile_name' console output:
/u01/oracle/diag/rdbms/test/test/trace/test_ora_6979.trc
[oracle@ora11gR2 ~]$
由于11g使用 "sqlplus -prelim / as sysdba" 未能分析出hang的原因，所以直接重启备库后恢复正常
=========================================================================================
关于oradebug的用法
会话一对数据做update未提交：
SCOTT1@test> select spid from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID
------------------------------------------------------------------------
6520
SQL> update t1 set loc='china' where deptno=10;
1 row updated.
会话二对同一条数据做delete被阻塞，会话挂起：
SCOTT2@test> select spid from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID
------------------------------------------------------------------------
6523
SQL> delete from t1 where deptno=10;
通过oradebug找到会话二hang住的原因：
SYS@test> oradebug setospid 6523;
Oracle pid: 32, Unix process pid: 6523, image: oracle@ora11gR2 (TNS V1-V3)
SYS@test> oradebug hanganalyze 3;
Hang Analysis in /u01/oracle/diag/rdbms/test/test/trace/test_ora_6679.trc
SYS@test>
[oracle@ora11gR2 ~]$ more /u01/oracle/diag/rdbms/test/test/trace/test_ora_6679.trc
......
*** 2012-10-18 17:17:30.012
Processing Oradebug command 'setospid 6523'
*** 2012-10-18 17:17:30.013
Oradebug command 'setospid 6523' console output:
Oracle pid: 32, Unix process pid: 6523, image: oracle@ora11gR2 (TNS V1-V3)
*** 2012-10-18 17:17:37.961
Processing Oradebug command 'hanganalyze 3'
*** 2012-10-18 17:17:38.859
===============================================================================
HANG ANALYSIS:
instances (db_name.oracle_sid): test.test
oradebug_node_dump_level: 3
analysis initiated by oradebug
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contenti
on'
Chain 1 Signature Hash: 0x38c48850
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (test.test)
os id: 6523
process id: 32, oracle@ora11gR2 (TNS V1-V3)
session id: 27
session serial #: 36
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x50010
p3: 'sequence'=0x2d8
......
oradebug 的使用方法：
Oradebug command 'help' console output:
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID Set OS pid of process to debug
SETORAPID ['force'] Set Oracle pid of process to debug
SETORAPNAME Set Oracle process name to debug
SHORT_STACK Get abridged OS stack
CURRENT_SQL Get current SQL
DUMP [addr] Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT Set trace event in process
SESSION_EVENT Set trace event in session
DUMPVAR [level] Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE Print/dump an address with type info
SETVAR Modify a fixed PGA/SGA/UGA variable
PEEK [level] Print/Dump memory
POKE Modify memory
WAKEUP Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G Parallel oradebug command prefix
-R Parallel oradebug prefix (return output
SETINST Set instance list in double quotes
SGATOFILE Dump SGA to file; dirname in double quotes
DMPCOWSGA Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS Helps translate PCs to names
WATCH Watch a region of memory
DELETE watchpoint Delete a watchpoint
SHOW watchpoints Show watchpoints
DIRECT_ACCESS Fixed table access
CORE Dump core without crashing process
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL [-t count] [arg1]...[argn] Invoke function with arguments
阅读(4391) | 评论(0) | 转发(0) |
