oracle执行命令hang住,使用oradebug hang住某个进程

在一次测试中,需要模拟在归档模式下,数据库发生多次redo切换而这些redo并未被归档的情景,一般来说这样的情况只有在归档目录满的时候会遇到.但是在日常测试中,这样的归档目录满的模拟不太现实,可以通过oradebug SUSPEND来实现该功能,让arcn进程挂起

配置log_archive_max_processes为1(可以配置多个，但是1个更加方便测试)

SQL> show parameter log_archive_max_processes;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_max_processes integer 1

该配置可以在线修改,但是不重启数据库不一定完全生效(测试环境本来是4,修改为1之后,还有arc0和arc1进程)

查找arcn进程

[oracle@localhost trace]$ ps -ef|grep ora_arc

oracle 3686 1 0 21:07 ? 00:00:00 ora_arc0_test

oradebug进程(session 1)

SQL> oradebug setospid 3686

Oracle pid: 57, Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0)

SQL> oradebug SUSPEND

Statement processed.

alert日志

Tue Apr 16 21:09:42 2013

Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0) flash frozen [ command #1 ]

切换日志(session 2)

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 53

Next log sequence to archive 55

Current log sequence 55

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> set lines 134

SQL> col member for a40

SQL> SELECT thread#,

2 a.sequence#,a.ARCHIVED,

3 a.group#,

4 TO_CHAR (first_change#, '9999999999999999') "SCN",

5 a.status,

6 MEMBER

7 FROM v$log a, v$logfile b

8 WHERE a.group# = B.GROUP#

9 ORDER BY a.sequence# DESC;

THREAD# SEQUENCE# ARC GROUP# SCN STATUS MEMBER

---------- ---------- --- ---------- ----------------- ---------------- ----------------------------

1 57 NO 3 261053 CURRENT /data/oracle/oradata/test/redo03.log

1 56 NO 2 261046 INACTIVE /data/oracle/oradata/test/redo02.log

1 55 NO 1 260856 INACTIVE /data/oracle/oradata/test/redo01.log

SQL> alter system switch logfile;--hang住

此时alert日志

Tue Apr 16 21:10:19 2013

Thread 1 advanced to log sequence 56 (LGWR switch)

Current log# 2 seq# 56 mem# 0: /data/oracle/oradata/test/redo02.log

Tue Apr 16 21:10:36 2013

Thread 1 advanced to log sequence 57 (LGWR switch)

Current log# 3 seq# 57 mem# 0: /data/oracle/oradata/test/redo03.log

Tue Apr 16 21:13:13 2013

ORACLE Instance test - Can not allocate log, archival required

Thread 1 cannot allocate new log, sequence 58

All online logs needed archiving

Current log# 3 seq# 57 mem# 0: /data/oracle/oradata/test/redo03.log

oradebug RESUME(session 1)

SQL> oradebug RESUME

Statement processed.

alert日志

Tue Apr 16 21:14:23 2013

Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0) resumed

Archived Log entry 2 added for thread 1 sequence 55 ID 0x7dd4ccb7 dest 1:

Archived Log entry 3 added for thread 1 sequence 56 ID 0x7dd4ccb7 dest 1:

hang住会话继续执行(session 2)

SQL> alter system switch logfile;

System altered.