October 17, 2006
使用Oracle的外部表访问跟踪文件
作者:eygle
出处:http://blog.eygle.com
前面说过Oracle的外部表可以用来访问警告日志文件，其实Oracle的外部表可以非常灵活的被使用。
通过Create Directory命令创建相应的Directory之后，我们可以将目录的访问权限授予其他用户，这样其他用户就能通过外部表访问很多主机上的文件。
关于Directory的使用方法，可以参考:
http://www.eygle.com/archives/2005/04/using_create_di.html
我们看一下使用外部表访问跟踪文件的例子(我的例子是用SYS用户来完成的)。
首先创建一个指向跟踪文件的Directory：
[oracle@jumper oracle]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 17 22:10:05 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /opt/oracle/admin/eygle/udump
SQL> create or replace directory udump
2 as '/opt/oracle/admin/eygle/udump';Directory created.
SQL> col DIRECTORY_PATH for a30
SQL> select * from dba_directories;OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ------------------------------
SYS BDUMP /opt/oracle/admin/eygle/bdump
SYS UDUMP /opt/oracle/admin/eygle/udump
如果我们的某些操作需要生成跟踪文件:
SQL> alter session set sql_trace=true;Session altered.
SQL> select count(*) from dba_users;
COUNT(*)
----------
7SQL> alter session set sql_trace=false;
Session altered.
通过简单的脚本我们可以获得跟踪文件的名称:
SQL> set echo on
SQL> @gettrcname
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10 (SELECT t.INSTANCE
11 FROM SYS.v$thread t, SYS.v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM SYS.v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /TRACE_FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/admin/eygle/udump/eygle_ora_29731.trcSQL>
然后我们可以创建外部表用以访问这个跟踪文件:
SQL> create table eygle_ora_29731 ( text varchar2(400) )
2 organization external (
3 type oracle_loader
4 default directory UDUMP
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile
10 )
11 location('eygle_ora_29731.trc')
12 ) reject limit unlimited
13 /Table created.
最后，我们可以很容易的使用外部表访问和查询这个跟踪文件的内容:
SQL> set pagesize 100
SQL> select * from eygle_ora_29731;TEXT
-----------------------------------------------------------------
/opt/oracle/admin/eygle/udump/eygle_ora_29731.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0
System name: Linux
Node name: jumper.hurray.com.cn
Release: 2.4.21-15.EL
Version: #1 Thu Apr 22 00:27:41 EDT 2004
Machine: i686
Instance name: eygle
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 29731
*** 2006-10-17 22:15:50.310
*** SESSION ID:(7.141) 2006-10-17 22:15:50.310
APPNAME mod='sqlplus@jumper.hurray.com.cn (TNS V1-V3)' mh=0 act='' ah=0
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1133881396787772 hv=3943786303 ad='5ac609d4'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0
=====================
PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=1133881402205250 hv=3468666020 ad='5adae2d8'
select text from view$ where rowid=:1
END OF STMT
PARSE #3:c=0
EXEC #3:c=0
FETCH #3:c=0
=====================
PARSING IN CURSOR #4 len=116 dep=2 uid=0 oct=3 lid=0 tim=1133881402238727 hv=431456802 ad='5af20410'
select o.owner#
END OF STMT
PARSE #4:c=10000
EXEC #4:c=0
FETCH #4:c=0
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=79 us)'
=====================
PARSING IN CURSOR #1 len=30 dep=0 uid=0 oct=3 lid=0 tim=1133881402246452 hv=3198913505 ad='5adaa4ac'
select count(*) from dba_users
END OF STMT
PARSE #1:c=10000
EXEC #1:c=0
FETCH #1:c=10000
FETCH #1:c=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=48 r=0 w=0 time=5276 us)'
STAT #1 id=2 cnt=7 pid=1 pos=1 obj=0 op='MERGE JOIN (cr=48 r=0 w=0 time=5250 us)'
STAT #1 id=3 cnt=7 pid=2 pos=1 obj=0 op='SORT JOIN (cr=45 r=0 w=0 time=5051 us)'
=====================
PARSING IN CURSOR #3 len=116 dep=1 uid=0 oct=3 lid=0 tim=1133881410335382 hv=431456802 ad='5af20410'
select o.owner#
END OF STMT
PARSE #3:c=0
EXEC #3:c=0
FETCH #3:c=0
STAT #1 id=4 cnt=7 pid=3 pos=1 obj=94 op='TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=45 r=0 w=0 time=4881 us)'
STAT #1 id=5 cnt=127 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=44 r=0 w=0 time=3951 us)'
STAT #1 id=6 cnt=7 pid=5 pos=1 obj=0 op='NESTED LOOPS (cr=42 r=0 w=0 time=3235 us)'
STAT #1 id=7 cnt=7 pid=6 pos=1 obj=0 op='NESTED LOOPS (cr=26 r=0 w=0 time=3021 us)'
STAT #1 id=8 cnt=7 pid=7 pos=1 obj=0 op='MERGE JOIN (cr=10 r=0 w=0 time=2760 us)'
STAT #1 id=9 cnt=9 pid=8 pos=1 obj=0 op='SORT JOIN (cr=6 r=0 w=0 time=2479 us)'
STAT #1 id=10 cnt=9 pid=9 pos=1 obj=94 op='TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=6 r=0 w=0 time=2284 us)'
STAT #1 id=11 cnt=163 pid=10 pos=1 obj=0 op='NESTED LOOPS (cr=5 r=0 w=0 time=1303 us)'
EXEC #3:c=0
FETCH #3:c=0
STAT #1 id=12 cnt=9 pid=11 pos=1 obj=280 op='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=3 r=0 w=0 time=244 us)'
STAT #1 id=13 cnt=153 pid=11 pos=2 obj=139 op='INDEX RANGE SCAN I_PROFILE (cr=2 r=0 w=0 time=518 us)'
STAT #1 id=14 cnt=7 pid=8 pos=2 obj=0 op='SORT JOIN (cr=4 r=0 w=0 time=200 us)'
STAT #1 id=15 cnt=7 pid=14 pos=1 obj=22 op='TABLE ACCESS FULL USER$ (cr=4 r=0 w=0 time=117 us)'
STAT #1 id=16 cnt=7 pid=7 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=16 r=0 w=0 time=199 us)'
STAT #1 id=17 cnt=7 pid=16 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=2 r=0 w=0 time=69 us)'
STAT #1 id=18 cnt=7 pid=6 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=16 r=0 w=0 time=166 us)'
STAT #1 id=19 cnt=7 pid=18 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=2 r=0 w=0 time=37 us)'
STAT #1 id=20 cnt=119 pid=5 pos=2 obj=139 op='INDEX RANGE SCAN I_PROFILE (cr=2 r=0 w=0 time=342 us)'
STAT #1 id=21 cnt=7 pid=2 pos=2 obj=0 op='SORT JOIN (cr=3 r=0 w=0 time=121 us)'
EXEC #3:c=0
FETCH #3:c=0
STAT #1 id=22 cnt=1 pid=21 pos=1 obj=95 op='TABLE ACCESS FULL PROFNAME$ (cr=3 r=0 w=0 time=66 us)'
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1133881410339660 hv=4238949625 ad='5ac49f60'
alter session set sql_trace=false
END OF STMT
PARSE #1:c=0
EXEC #1:c=084 rows selected.
SQL>
外部表极大的简化了我们的很多管理工作。
-The End-
Posted by eygle at 10:42 PM | Comments (1)
获得Redo Block Size的非典型方法
作者:eygle
出处:http://blog.eygle.com
我以前介绍过获得redo block size的方法，当然也可以通过dbfsize工具获得。
现在在这里我想介绍的是另外一种非典型方法，有多非典型呢?
问题来自于N年前我在一个客户的机器上部署的监控脚本，现在那些脚本看来还在运行着，我最近收到了大量类似如下内容的邮件:
ORA-16038: log 2 sequence# 17151 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 2 thread 1: '/export/home/oracle/oradata/cuwap/redo02.log'
ORA-19504: failed to create file "/data1/oraarc/PTL017151.arc"
ORA-19504: failed to create file "/data1/oraarc/PTL017151.arc"
ORA-27044: unable to write the header block of file
ORA-16038: log 2 sequence# 17151 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 2 thread 1: '/export/home/oracle/oradata/cuwap/redo02.log'
ORA-19504: failed to create file "/data1/oraarc/PTL017151.arc"
ORA-19504: failed to create file "/data1/oraarc/PTL017151.arc"
ORA-27044: unable to write the header block of file
ORA-16038: log 2 sequence# 17151 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 2 thread 1: '/export/home/oracle/oradata/cuwap/redo02.log'
ORA-19502: write error on file "/data1/oraarc/PTL017151.arc", blockno 169985 (blocksize=512)
ORA-27072: skgfdisp: I/O error
ORA-19502: write error on file "/data1/oraarc/PTL017151.arc", blockno 169985 (blocksize=512)
ORA-19502: write error on file "/data1/oraarc/PTL017152.arc", blockno 57345 (blocksize=512)
ORA-27072: skgfdisp: I/O error
ORA-19502: write error on file "/data1/oraarc/PTL017152.arc", blockno 57345 (blocksize=512)
ORA-16038: log 2 sequence# 17151 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/export/home/oracle/oradata/cuwap/redo02.log'
ORA-16038: log 2 sequence# 17151 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/export/home/oracle/oradata/cuwap/redo02.log'
ORA-16038: log 2 sequence# 17151 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 2 thread 1: '/export/home/oracle/oradata/cuwap/redo02.log'
ORA-16038: log 3 sequence# 17152 cannot be archived
ORA-19502: write error on file "", blockno (blocksize=)
ORA-00312: online log 3 thread 1: '/export/home/oracle/oradata/cuwap/redo03.log'
我们注意到，在这些错误信息中，这样的提示至关重要:
ORA-19502: write error on file "/data1/oraarc/PTL017151.arc", blockno 169985 (blocksize=512)
ORA-27072: skgfdisp: I/O error
ORA-19502: write error on file "/data1/oraarc/PTL017151.arc", blockno 169985 (blocksize=512)
ORA-19502: write error on file "/data1/oraarc/PTL017152.arc", blockno 57345 (blocksize=512)
这里的Blocksize就告诉我们，在该系统上（Solaris），Redo Block Size是512 bytes。
这里的BlockNo让我想到了另外一个内容。
参考Steve Adams网站上的关于Log Buffer Usage的说明我们能找到另外一个有意思的内容，引用一下Steve的图：
我们知道，Redo Log Buffer和Redo Log File是通过Block映射（map）来完成空间分配和使用的，于是也就有了两个变量：
1.用以标示SGA中Redo Log Buffer基准位置的变量b
2.用以标示Redo Log File基准位置的变量i
如果这里存在问题的是redo log file,那么无法写入的blockno实际上就应该和variable (i)有关；当然对于归档日志文件，这里的blockno自然也是由最初的变量推进和定界的。
由以上输出的错误信息我们可以知道:
169985 * 512 /1024 /1024 = 83M
所以我们还可以判断Redo Log File的大小肯定是大于83M的，在Oracle9i中，缺省的Redo Log File大小是100M。
-The End-
Posted by eygle at 4:57 PM | Comments (1)
如何备份OutLook Express的邮件规则
作者:eygle
出处:http://blog.eygle.com
以前重装系统，每次都会丢失了Outlook Express的邮件规则，安装之后又要重新定义。
最近一次安装系统是在7月底，虽然不是那么频繁，也让我感觉到邮件规则的麻烦。
今天定义另一条规则时想起来，就Google了一下，找到了备份邮件规则的方法。
有点让我意外的是，Microsoft的邮件规则居然是存放在注册表中的。
我目前用的是OutLook Express6，备份方法大致如下，在注册表找到如下键值：
HKEY_CURRENT_USER\Identities\{Identity Number}\Software\Microsoft\Outlook Express\5.0\Rules\Mail
注：启动注册表编辑器的步骤如下:开始->运行->regedit
备份时将该键备份即可，选“注册表”菜单上的“导出注册表文件”命令，把这个邮件规则备份到安全的地方。
以后要导入此邮件规则，只要双击所备份的reg文件，资料就会自动导入了。
注意：Identity Number 就是在 Identities 下的一个一串很长数值的文件夹，每台计算机的数值都不相同。
所以恢复的时候，如果本机恢复没有问题，如果是不同主机，通过修改相应的Identity Number应该也可以做到。备份的注册表文件可以通过Notepad进行编辑，所以异机恢复完全可以实现的。
记录一下，供参考!
-The End-
Posted by eygle at 1:57 PM | Comments (3)