优化Google Analytics Java Script载入

Google Analytics可说是目前最好的浏览分析工具,我们在使用Google Analytics的时候都需要在页面上加载”google-analytics.com/ga.js”的这段java script代码,就目前来说ga.js的载入还是比较快的,而且在第一次载入后就会被缓存下来了;但实际访问页面时偶尔还是会发现瓶颈出现在访问google-analytics.com上。那么有什么好办法进一步加速ga.js的载入吗?
最近Google code推出的pagespeed里就推荐了一种方法:即使用异步载入的ga.js脚本;换而言之就是让页面先完全载入,之后在后台继续完成该java script代码的工作。使用这种最新的异步调用方式后,页面的载入速度几乎和不使用Google Analytics一样快了,实现的方法也十分简单,直接替换页面上调用ga.js的语句就可以了:

<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-XXXXXX']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>

记得要将UA-XXXXXX替换成你自己的Google Analytics ID,如果你是使用wordpress ultimate Google Analytics插件的话,只需要将该插件删除或停用,然后将上述代码插入到footer.php的</html>标记前就行了。

享受asynchronous异步脚本带来的高性能吧!

V$LOCK视图显示TX锁没有对应的TM锁的几种可能现象

v$lock视图是Oracle中经典的锁检测接口之一,该视图列出了当前实例中被持有的锁信息;其中TYPE列指出了该持有锁的类型,比较常见的TX锁即事务队列锁(Transaction enqueue)和TM锁即DML enqueue锁(俗称表锁);一般某个会话在其事务(transaction)结束前总是持有一个TX锁,并一个或多个TM ROW-X(SX)锁 (一个事务中可能对多个表或对象进行了更新). 但偶尔我们会发现某个会话持有一个TX锁,却没有对应的TM锁的情况. 第一次接触到该问题,可能会觉得有些不可思议,但这种情况却的确存在:

[Read more…]

Oracle网络TNS协议的几个基础类描述(revised)

首先是接口SQLnetDef,定义了参数

public interface SQLnetDef
{

public static final boolean DEBUG = false;
public static final boolean ASSERT = false;
public static final int NSPTCN = 1;
public static final int NSPTAC = 2;
public static final int NSPTAK = 3;
public static final int NSPTRF = 4;
public static final int NSPTRD = 5;
public static final int NSPTDA = 6;
public static final int NSPTNL = 7;
public static final int NSPTAB = 9;
public static final int NSPTRS = 11;
public static final int NSPTMK = 12;
public static final int NSPTAT = 13;
public static final int NSPTCNL = 14;
public static final int NSPTHI = 19;
public static final byte NSPHDLEN = 0;
public static final byte NSPHDPSM = 2;
public static final byte NSPHDTYP = 4;
public static final byte NSPHDFLGS = 5;
public static final byte NSPHDHSM = 6;
public static final byte NSPSIZHD = 8;
public static final byte NO_HEADER_FLAGS = 0;
public static final byte NSPCNVSN = 8;
public static final byte NSPCNLOV = 10;
public static final byte NSPCNOPT = 12;
public static final byte NSPCNSDU = 14;
public static final byte NSPCNTDU = 16;
public static final byte NSPCNNTC = 18;
public static final byte NSPCNTNA = 20;
public static final byte NSPCNONE = 22;
public static final byte NSPCNLEN = 24;
public static final byte NSPCNOFF = 26;
public static final byte NSPCNMXC = 28;
public static final byte NSPCNFL0 = 32;
public static final byte NSPCNFL1 = 33;
public static final byte NSPCNDAT = 34;
public static final int NSPMXCDATA = 230;
public static final int NSINAWANTED = 1;
public static final int NSINAINTCHG = 2;
public static final int NSINADISABLEFORCONNECTION = 4;
public static final int NSINANOSERVICES = 8;
public static final int NSINAREQUIRED = 16;
public static final int NSINAAUTHWANTED = 32;
public static final byte NSPACVSN = 8;
public static final byte NSPACOPT = 10;
public static final byte NSPACSDU = 12;
public static final byte NSPACTDU = 14;
public static final byte NSPACONE = 16;
public static final byte NSPACLEN = 18;
public static final byte NSPACOFF = 20;
public static final byte NSPACFL0 = 22;
public static final byte NSPACFL1 = 23;
public static final byte NSPRFURS = 8;
public static final byte NSPRFSRS = 9;
public static final byte NSPRFLEN = 10;
public static final byte NSPRFDAT = 12;
public static final byte NSPRDLEN = 8;
public static final byte NSPRDDAT = 10;
public static final int NSPDAFLG = 8;
public static final int NSPDADAT = 10;
public static final int NSPDAFZER = 0;
public static final int NSPDAFTKN = 1;
public static final int NSPDAFRCF = 2;
public static final int NSPDAFCFM = 4;
public static final int NSPDAFRSV = 8;
public static final int NSPDAFMOR = 32;
public static final int NSPDAFEOF = 64;
public static final int NSPDAFIMM = 128;
public static final int NSPDAFRTS = 256;
public static final int NSPDAFRNT = 512;
public static final int NSPMKTYP = 8;
public static final int NSPMKODT = 9;
public static final int NSPMKDAT = 10;
public static final int NSPMKTD0 = 0;
public static final int NSPMKTD1 = 1;
public static final byte NIQBMARK = 1;
public static final byte NIQRMARK = 2;
public static final byte NIQIMARK = 3;
public static final int NSPDFSDULN = 2048;
public static final int NSPMXSDULN = 32767;
public static final int NSPMNSDULN = 512;
public static final int NSPDFTDULN = 32767;
public static final int NSPMXTDULN = 32767;
public static final int NSPMNTDULN = 255;
public static final int NSPINSDULN = 255;
public static final String TCP_NODELAY_STR = "TCP.NODELAY";
public static final String TCP_CONNTIMEOUT_STR = "oracle.net.CONNECT_TIMEOUT";
public static final String TCP_READTIMEOUT_STR = "oracle.net.READ_TIMEOUT";
public static final int TCP_NODELAY_OFF = 0;
public static final int TCP_KEEPALIVE_OFF = 1;
public static final int TCP_CONNTIMEOUT_OFF = 2;
public static final int TCP_READTIMEOUT_OFF = 3;
public static final int ORACLE_NET_NTMINOPT = 0;
public static final int ORACLE_NET_READ_TIMEOUT = 1;
public static final int ORACLE_NET_NTMAXOPT = 10;
}

[Read more…]

Oracle网络TNS协议介绍(revised)

在开发源工具解决Oracle中的问题时,必须了解TNS协议。在努力理解TNS协议的时候,Oracle JDBC驱动程序(classes12.zip)会是一个很有用的资源

TNS头

每个TNS包都有一个8字节的包头。包最前面的两个字节用来表示包的长度–不包括包头的大小。和所有的值一样,大小是大端字节的方式存储的。如果进行了校验,那么校验和会被存放在下一个字(WORD)内–默认情况下会对校验和进行校验,该字(WORD)的值为0x0000。下面的一个字节用来表示包的类型–例如,最常见的类型如下所示:

Connect packet Type        1
Accept packet    Type       2
Ack  packet          Type      3
Refuse Packet     Type      4
Redirect Packet    Type   5
Data   Packet         Type    6
NULL Packet          Type    7
Abort  Packet          Type   9
Resend    Packet       Type 11
Marker   Packet         Type 12
Attention Packet       Type 13
Control   Packet        Type 14

要连接Oracle的时候,在TNS级,客户机向服务器发送一个链接包(类型1),这个包会指定客户将要访问的服务器名称。假定Listener知道这样的服务,那么接下来有两种可能:Listener可能会发送一个接收包(类型2)或者它可能用一个重定向包(类型5)将客户机指定向到另一个端口。如果出现的是前一种情况,那么客户机将尝试进行身份验证。如果出现的是后一种情况,那么客户机会向自己被重定向的端口发送一个连接包并请求访问该服务。如果一切顺利的话,服务器会发出一个接收包并开始进行身份验证。所有的身份验证包都是类型为6的数据包。

如果Listener不知道客户机要求访问那个服务,那么它就会发送一个拒绝包–类型4。一旦通过身份验证,查询包和结果包都是数据包。偶尔您会看到类型为12(oxoC的包)–这是用来中断的标记包。例如,如果服务器希望客户机停止发送数据,那么它会向客户机发送一个标记包。

接下来继续介绍TNS包头的细节,下面的一个字节是包头标志位(header flag)。一般这些包头标志位是不使用的,不过在10g中客户机可能会将它的值设定为0x04。

最后的两个字节构成一个字(WORD),他用来存放头校验和–在默认情况下不使用这个字并将其设为0x000:

WORD   00 00  Pocket Size
WORD   00 00  Packet Checksum
Byte     00  Packet Type
Byte     00   Flags
Word     00 00 Header Checksum

在对包进行深入研究前,观察一下拒绝包(类型4)应该会有所帮助。拒绝包表示某种错误–例如,一个因“用户名/密码无效”而被拒绝登陆的错误–即ORA-01017。根绝这些错误,第54个字节指出了问题所在:A3代表无效的密码,a2表明没有这样的用户。很明显,即使从拒绝包中也可以得到可能有用的信息。

发现这篇文章被网友转载了:地址;而且因为163 blog的权重比这里高导致原帖变成了”转帖”,真是很令人头痛的事情,
转载请注明出处:www.askmac.cn  谢谢!

Exadata V2 Pricing

从最新流出的《Exadata Guided Learning Path Exadata:Pricing》内部文档中可以进一步了解Oracle Sun Database Machine:Exadata V2的实际价格;该文档中列出的价目表和我之前文章中介绍的出入较大,因为是视频截图可能不是十分清晰:
exadata_price1

以下为满配全机架的价目表:

可以看到其实际的价格中Exadata Storage Server Software软件的价格占到了很大的比例,需要支付每块磁盘10000USD的高昂软件费用;而Exadata Storage Server软件一年的Support费用则高达369600美元。如果购买包括Oracle Database 11g及RAC,Partition特性的License,把这些费用合计则达到了$6,413,768;很难想象出去银行和政府外有哪些企业会为数据库投入如此庞大的预算。

以下为四分之一机架的价目表,其中包含了数据库软件及RAC和分区特性的License:

相对而言四分之一机架价格不会让人那么心惊肉跳,因为CPU和磁盘的数量相应减少,所以数据库软件的License价格没有满配那么夸张。
同时该文档明确指出了Exadata V2的硬件最高discount(折扣)为25%:

Purchase Exadata Database Machine Hardware

  • Purchase complete machine
  • Hardware discount still maximum 25%

Purchase the appropriate amount of software

  • Exadata Storage Server software must be purchased in increments of 12 ‘Disk Drives'(each Storage Server cell must be fully licensed, no licensing of partial Storage Servers)
  • Database Software and Options must be purchased in increments of 4 ‘Processors’,no partial licensing within database server
  • Leverage existing database licenses the customer already owns

Ratio of database servers to Exadata Storage Servers should remain at approximately 1:2

Exadata Database Machine License Requirement Cheat Sheet

同志们!有机会的话好好学学Exadata Storage软件的知识吧,大有钱途啊!

logfile switch causes incremental checkpoint?

不少Oracle的初学者都会因为checkpoint这个知识点而头痛,绝大多数Oracle文档对完全检查点和增量检查点的描述又都略显朦胧;譬如在线日志的切换引起的是完全检查点还是增量检查点这个问题,就有不少的争论。实际上增量检查点与完全检查点有一个显著的区别:完全检查点发生时控制文件和数据文件头中的checkpoint scn都会被更新,而增量检查点发生时只有控制文件中的checkpoint scn更新;
我们可以通过以下演示证明日志切换引发的到底是何种检查点?:

SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
           1665476
           1665476
           1665476
           1665476
           1665476
           1665476
6 rows selected.

SQL> alter system checkpoint;
System altered.

SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#

------------------
           1697131
           1697131
           1697131
           1697131
           1697131
           1697131
6 rows selected.

/* 手动执行checkpoint,数据文件头的checkpoint scn立即更新了 */

SQL> alter system flush buffer_cache;
System altered.

SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
           1697131
           1697131
           1697131
           1697131
           1697131
           1697131
6 rows selected.

/* 单纯flush buffer cache冲刷数据库高速缓存不会更新数据文件头的checkpoint scn */

SQL> alter system set log_checkpoints_to_alert=true;
System altered.

SQL> alter system set log_checkpoint_timeout=20;
System altered.

/* 设置log_checkpoint_timeout为20s,频繁引发增量检查点 */

alert log:
Wed Nov  3 20:24:49 2010
Incremental checkpoint up to RBA [0x3d.dff1.0], current log tail at RBA [0x3d.dff6.0]
Wed Nov  3 20:25:07 2010
Incremental checkpoint up to RBA [0x3d.dff7.0], current log tail at RBA [0x3d.dffc.0]
Wed Nov  3 20:25:25 2010
Incremental checkpoint up to RBA [0x3d.dffd.0], current log tail at RBA [0x3d.e002.0]
Wed Nov  3 20:25:43 2010
Incremental checkpoint up to RBA [0x3d.e003.0], current log tail at RBA [0x3d.e008.0]
Wed Nov  3 20:26:01 2010
Incremental checkpoint up to RBA [0x3d.e009.0], current log tail at RBA [0x3d.e00e.0]

SQL> set time on;

20:26:38 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
           1697131
           1697131
           1697131
           1697131
           1697131
           1697131

6 rows selected.

/* 可以看到增量检查点并不会引起数据文件头的checkpoint scn 被更新 */

20:26:43 SQL>  alter system set log_checkpoint_timeout=1800;
System altered.

/* 那么日志文件切换就会引起数据文件头的checkpoint scn被更新吗?*/

20:28:10 SQL> alter system switch logfile;
System altered.

20:29:16 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
           1697131
           1697131
           1697131
           1697131
           1697131
           1697131
6 rows selected.

/* logfile switch 日志文件切换引起的是一种slow慢的完全检查点,它不同于alter system checkpoint(ASC),
   ASC要求的脏块写出和控制文件及数据文件头更新时要立即完成的,也就是说当alter system checkpoint语句返回"System altered."
   后以上工作都已经完成了;而alter system switch logfile或者自然的日志切换引发的是一种慢的完全检查点,
   它在返回"System altered"时不要求写脏块等工作必须已经完成
*/

/* 我们可以用冲刷高速缓存的方式保证脏块写出的工作被督促完成 */

20:33:39 SQL> alter system flush buffer_cache;
System altered.

20:33:45 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
           1697544
           1697544
           1697544
           1697544
           1697544
           1697544

6 rows selected.

/* 虽然日志切换所引发的slow checkpoint(慢的检查点)并无立即完成的要求,但也并非全无限制;
   当某次日志切换由1号日志组切换到2号日志组时,
   将引发一个slow checkpoint,之后日志连续切换又要切到1号日志组时要求之前的那个slow checkpoint在切换前必须完成
*/

20:41:35 SQL> set timing on;

20:42:02 SQL>  select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1         67   52428800          2 YES INACTIVE               1698288 2010-11-03 20:41:19
         2          1         68   52428800          2 YES INACTIVE               1698292 2010-11-03 20:41:21
         3          1         69   52428800          2 NO  CURRENT                1698302 2010-11-03 20:41:35
Elapsed: 00:00:00.00

20:42:17 SQL> delete tv;
51134 rows deleted.
Elapsed: 00:00:01.68

20:42:34 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00

20:42:36 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01

20:42:40 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01

20:42:43 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:02.00

20:45:28 SQL>  select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
           1700686
           1700686
           1700686
           1700686
           1700686
           1700686
6 rows selected.
Elapsed: 00:00:00.00

alter.log告警日志中的内容:

Wed Nov  3 20:42:40 2010
Beginning log switch checkpoint up to RBA [0x46.2.10], SCN: 1700686
...........................
Wed Nov  3 20:42:45 2010
Thread 1 cannot allocate new log, sequence 72
Checkpoint not complete
....................
Completed checkpoint up to RBA [0x46.2.10], SCN: 1700686

/* 最近一次的日志切换耗费2s,在告警日志中可以看到此次slow checkpoint的相关记录 */

single quote needed in expdp query?

如果在使用数据泵时不采用parfile参数文件的话,query参数指定的查询条件是需要使用单引号括起来的,而当使用parfile时则不需要加上单引号,加上后反而会出现LPX-314: an internal failure occurred错误:

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:"where t1<2000"

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:32:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:where t1<2000
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-00936: missing expression
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:32:36

/* 不采用参数文件形式,没有加单引号的情况下出现ORA-00936: missing expression错误*/

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:33:39

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:33:43

/* 不采用参数文件,在query参数中加上单引号则导出成功 */

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:'"where t1<2000"'
directory=dump

[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp

[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par 

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:35:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:35:11

/* 采用parfile选项,参数文件中query参数加入单引号出现LPX-314: an internal failure occurred错误,导出失败*/

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:"where t1<2000"
directory=dump
[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp
[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par           

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:36:27

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:36:30

/* 采用parfile,参数文件中query未加入单引号,导出成功 */

1. QUERY in Parameter file.

Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

* from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and
* from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.

File: expdp_q.par
—————–
DIRECTORY = my_dir
DUMPFILE = exp_query.dmp
LOGFILE = exp_query.log
SCHEMAS = hr, scott
INCLUDE = TABLE:”IN (‘EMP’, ‘DEPARTMENTS’)”
QUERY = scott.emp:”WHERE job = ‘ANALYST’ OR sal >= 3000″
# place following 3 lines on one single line:
QUERY = hr.departments:”WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = ‘ANALYST’ OR e.salary >= 3000)”

— Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par

Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.
2. QUERY on Command line.

The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

* table scott.dept; and
* from table scott.emp all employees whose name starts with an ‘A’

— Example Windows platforms:
— Note that the double quote character needs to be ‘escaped’
— Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\”WHERE ename LIKE ‘A%’\”

— Example Unix platforms:
— Note that all special characters need to be ‘escaped’

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\”WHERE ename LIKE \’A\%\’\”

— Example VMS platform:
— Using three double-quote characters

$ expdp scott/tiger DIRECTORY=my_dir –
DUMPFILE=exp_cmd.dmp LOGFILE=exp_cmd.log TABLES=emp,dept –
QUERY=emp:”””WHERE ename LIKE ‘A%'”””

Note that with the original export client two jobs were required:
— Example Windows platforms:
— Place following statement on one single line:

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp
QUERY=\”WHERE ename LIKE ‘A%’\”

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

— Example Unix platforms:

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \
QUERY=\”WHERE ename LIKE \’A\%\’\”

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

— Example VMS platform:

$ exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp –
QUERY=”””WHERE ename LIKE ‘A%'”””

$ exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

Note that with original export client in Oracle8i on VMS, the syntax was different (also note the extra space that is needed between two single quotes):
… QUERY=”‘WHERE ename LIKE \’A%\’ ‘”
That is: [double_quote][single_quote]WHERE ename LIKE [backslash][single_quote]A%[backslash][single_quote][space][single_quote][double_quote]

No way to disable datapump estimate?

昨天晚上去客户现场配合一个导入导出作业;这是一套Solaris 10上的10.2.0.1系统,导出采用expdp数据泵工具,需要导出的数据源是一张大小在120G左右的单表,该表存放了最近一年的数据,实际导出时只需要最近三个月的数据,所以使用了QUERY参数,并指定了并行度为2。
该导出作业之前未经测试过,语句也是临时写的,实际执行导出工作时发现在评估阶段(estimate phase)耗费了大约十多分钟的时间,estimate默认使用blocks模式,即通过计算对象段占用的数据库块来预估dumpfile的大小;此外还有statistics模式通过对象的统计信息来推算导出文件的大小:

The block method of estimate is the least accurate. We investigate how many blocks were allocated for the storage segments for the tables specified. If the table had been recently analyzed, the estimate=statistics would be the most accurate. So remember, it is important to generate statistics before calculating the estimate using estimate=statistics method. Additionally, ESTIMATE=BLOCKS method could be inaccurate when:

a) The table was created with a much bigger initial extent size than was needed for the actual table data

b) Many rows have been deleted from the table, or a very small percentage of each block is used.

# When statistics have been explicitly set via the DBMS_STATS package in order to force certain optimizer plans, instead of being directly collected from the data, ESTIMATE=STATISTICS may not report correct results.

但实际上这2中方式在使用QUERY的情况下都无法正确预估导出文件的大小:

[maclean@rh2 udump]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'  estimate=statistics

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 21:28:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000" estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                     104.7 MB
Total estimation using STATISTICS method: 104.7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 21:28:29

/* statistics模式使用统计来判断导出文件大小,比较容易控制 */

begin
 dbms_stats.set_table_stats(ownname => 'MACLEAN',tabname => 'ESTIMATE_ME',numrows => 999999999999999,numblks => 99999999999999999999);
  end;
/

PL/SQL procedure successfully completed.

Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                     10244 GB
Total estimation using STATISTICS method: 10244 GB

/* 再次导出时数据泵采用了"作假的"统计信息*/

[maclean@rh2 udump]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'  estimate=blocks

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 21:31:58

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000" estimate=blocks
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                       192 MB
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 21:32:01

/* 可以看到以上BLOCKS method预估的dumpfile大小为192MB,而STATISTICS方式预估为104.7MB;
    然而实际的使用QUERY参数导出的1999行数据只占用32k的空间!
    estimate在这里摆了一个大乌龙
*/

查了一下metalink发现10.2.0.1上存在expdp在estimate评估阶段耗时很久的bug:

EXPDP Slow and Estimate Takes Long Time [ID 822283.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms
Data pump Export is very slow and long time is spent in Estimating the data size.
EXPDP is stuck for long at :

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=dpexp.par
Estimate in progress using BLOCKS method...

AWR report taken when EXPDP is running slow show the below query took long time to complete .

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME
,KU$.BASE_OBJ.OWNER_NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,
to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.NONSCOPED_REF ,KU$.XMLSCHEMACOLS
,KU$.NAME , KU$.NAME ,'TABLE_DATA' ,KU$.PART_NAME ,KU$.SCHEMA_OBJ.OWNER_NAME
,KU$.TS_NAME , KU$.TRIGFLAG
,decode(KU$.SCHEMA_OBJ.TYPE_NUM,2,decode(bitand(KU$.PROPERTY,8192),8192,
'NESTED TABLE','T'),19,'PARTITION',20,'PARTITION','SUBPARTITION')
,to_char(KU$.UNLOAD_METHOD)
FROM SYS.KU$_TABLE_DATA_VIEW KU$
WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT (BITAND
(KU$.BASE_OBJ.FLAGS,16)=16) AND KU$.BASE_OBJ.NAME IN
('PA_DRAFT_INVOICES_ALL') AND KU$.BASE_OBJ.OWNER_NAME IN ('PA') AND NOT
EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND
A.NAME=KU$.BASE_OBJ.NAME AND A.SCHEMA=KU$.BASE_OBJ.OWNER_NAME) AND NOT EXISTS
(SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND
A.NAME=KU$.BASE_OBJ.OWNER_NAME);

Cause
The issue here is with using RBO.
Data pump is not choosing Cost Based Optimizer.
This is because of the 'unpublished' bug 5929373 -DATA PUMP TAKES LONG TIME TO EXPORT DATA

This bug is closed as duplicate of another bug << 5464834 >>

Solution

   1. Upgrade to 10.2.0.4 . The bug is fixed in 10.2.0.4
      Or
   2. Apply the << patch 5464834 >> if available for your platform .

        * Login to metalink
        * Go to 'Patches and Updates'
        * Click on 'Simple Search'
        * Enter the patch number 5464834
        * Select your platform from the dropdown list .
        * Patch,if available , will be displayed .
        * Download and apply the patch .

该Bug可以通过升级版本解决,但远水解不了近火,最初想disable掉estimate功能;但查阅若干资料后发现目前似乎没有能够关掉estimate功能的办法,这个就是悲哀所在了!耐心等estimate阶段完成后,实际数据导出阶段倒也只花了十来分钟,就这一点看来datapump很有中国式的办事风格。

另外当指定的导出文件数小于指定的parallelism,且导出数据量较大时可能出现ORA-39095错误,会导致导出作业意外终止:

39095, 00000, "Dump file space has been exhausted: Unable to allocate %s bytes"
// *Cause:  The Export job ran out of dump file space before the job was
//          completed.
// *Action: Reattach to the job and add additional dump files to the job
//          restarting the job.

EXPDP generating ORA-39095 : " dump file space has been exhausted"
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
Checked for relevance on 10-22-2010

Errors while performing Datapump Export:

- when exporting a large number of tables then it completely stops
- the jobs may remain in the datapump export tables
- No trace generated at the time of the failure.
ERROR:
ORA - 39095 : " dump file space has been exhausted. Unable to allocate 4096 bytes".jobsystem.sys_export_full_02 stops due to fatal error.

Cause
Customer chose only limited number of files in "dumpfile" parameter with limited "filesize" , i.e.:

   full=Y
   directory=dpump_dir
   dumpfile=full_1.dmp,full_2.dmp,full_3.dmp,full_4.dmp
   filesize=3000000000

This means 4 dump files with filesize 3000000000 byte (2.79 G).

the dumpfile parameter is the issue here.

.
Solution
1- You can use the dynamic format (i.e dumpfile=full_%U.dmp) :

The 'wildcard' specification for the dump file can expand up to 99 files. If 99 files have been generated before the export has completed, it will again return the ORA-39095 error.

2- If this is yet not enough and more files are needed, a workaround would be to speficy a bigger 'filesize' parameter.

3- If this is inconvenient, another option is to use this syntax:

      dumpfile=fullexp%U.dmp, fullexp2_%U.dmp, fullexp3_%U.dmp

which can expand up to 3*99 files.

If encountering problems containing the dump in a single directory using this solution, you may prefer this syntax:

     dumpfile=dmpdir1:fullexp1_%U.dmp, dmpdir2:fullexp2_%U.dmp, dmpdir3:fullexp3_U.dmp

(assuming the 3 directory objects listed above had been already created first).

解决方法很简单就是去掉并行度,或者指定动态的导出文件名。
记以录之,回去补觉了!

  • The block method of estimate is the least accurate. We investigate how many blocks were allocated for the storage segments for the tables specified. If the table had been recently analyzed, the estimate=statistics would be the most accurate. So remember, it is important to generate statistics before calculating the estimate using estimate=statistics method. Additionally, ESTIMATE=BLOCKS method could be inaccurate when:a) The table was created with a much bigger initial extent size than was needed for the actual table data

    b) Many rows have been deleted from the table, or a very small percentage of each block is used.

  • When statistics have been explicitly set via the DBMS_STATS package in order to force certain optimizer plans, instead of being directly collected from the data, ESTIMATE=STATISTICS may not report correct results.

Poor Data Export Performance(revised)

这是一个十分简单的单表导出作业,表上大约有200万条数据;但实际exp导出时dump文件每秒增长不足1MB,速度十分缓慢。

SQL> show user;
USER is "SH"

SQL> select count(*) from orders;

  COUNT(*)
----------
   2319232

SQL> select bytes/1024/1024 from dba_segments where owner='SH' and segment_name='ORDERS';

BYTES/1024/1024
---------------
            261

/*该表大小为261MB*/

/*这里我们使用直接路径导出*/
[maclean@rh2 ~]$ exp sh/sh file=/s01/orders.dmp direct=y tables=orders
Export: Release 11.2.0.1.0 - Production on Wed Aug 18 21:52:56 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table                         ORDERS

/* 我们通过脚本观察导出文件大小增长速度,每秒大约0.4MB */

[maclean@rh2 s01]$ while (1>0) do ls -lh orders.dmp ; sleep 1; done
-rw-r--r-- 1 maclean oinstall 912K Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 1.4M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 1.8M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 2.2M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 2.6M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 3.0M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 3.5M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 3.9M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 4.3M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 4.7M Aug 18 21:54 orders.dmp
-rw-r--r-- 1 maclean oinstall 5.1M Aug 18 21:54 orders.dmp

/*是什么导致了exp如此缓慢呢,有必看一下该导出服务进程的trace文件*/

SQL> select spid from v$process where addr=(select paddr from v$session where module like 'exp@%');
SPID
------------------------
22624

SQL> oradebug setospid 22624;
Oracle pid: 37, Unix process pid: 22624, image: oracle@rh2 (TNS V1-V3)
SQL> oradebug tracefile_name;
/rdbms/sprod/SPROD/trace/SPROD_ora_22624.trc
SQL> host
[maclean@rh2 trace]$ ls -lh SPROD_ora_22624.trc
-rw-r----- 1 maclean oinstall 1.2G Aug 18 21:58 SPROD_ora_22624.trc

/* 这个trace文件居然有1.2G之巨,会存放了些什么呢?*/

[maclean@rh2 trace]$ tail -100 SPROD_ora_22624.trc
2010-08-18 21:59:14.748194 : nsbasic_bsd:41 20 43 4C 41 53 53 07  |A.CLASS.|
2010-08-18 21:59:14.748201 : nsbasic_bsd:00 78 6D 08 0F 01 1A 2E  |.xm.....|
2010-08-18 21:59:14.748212 : nsbasic_bsd:07 00 78 6D 08 0F 01 1A  |..xm....|
2010-08-18 21:59:14.748219 : nsbasic_bsd:2E 13 00 32 30 30 39 2D  |...2009-|
2010-08-18 21:59:14.748225 : nsbasic_bsd:30 38 2D 31 35 3A 30 30  |08-15:00|
2010-08-18 21:59:14.748232 : nsbasic_bsd:3A 32 35 3A 34 35 05 00  |:25:45..|
2010-08-18 21:59:14.748239 : nsbasic_bsd:43 48 45 43 4B 01 00 4E  |CHECK..N|
2010-08-18 21:59:14.748246 : nsbasic_bsd:01 00 4E 01 00 4E 02 00  |..N..N..|
2010-08-18 21:59:14.748253 : nsbasic_bsd:C1 02 FE FF 06 00 50 55  |......PU|
2010-08-18 21:59:14.748260 : nsbasic_bsd:42 4C 49 43 15 00 2F 39  |BLIC../9|

/*绝大多数是Oracle sqlnet trace的内容,是不是因为设置了Oracle SqlNet端的trace选项,从而导致了导出服务进程写出大量sqlnet trace,最终引发exp的缓慢?*/

[maclean@rh2 trace]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = SERVER
TRACE_TIMESTAMP_ SERVER = ON
TRACE_UNIQUE_SERVER = ON

/*的确启用了server端sqlnet trace*/

[maclean@rh2 trace]$ echo "" >  /s01/11gdb/network/admin/sqlnet.ora

/*清空sqlnet.ora配置文件,禁用server端sqlnet trace*/

/*再次尝试exp导出*/

[maclean@rh2 ~]$ exp sh/sh file=/s01/orders.dmp direct=y tables=orders
Export: Release 11.2.0.1.0 - Production on Wed Aug 18 22:00:35 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                         ORDERS

[maclean@rh2 s01]$ while (1>0) do ls -lh orders.dmp ; sleep 1; done
-rw-r--r-- 1 maclean oinstall 61M Aug 18 22:00 orders.dmp
-rw-r--r-- 1 maclean oinstall 94M Aug 18 22:00 orders.dmp
-rw-r--r-- 1 maclean oinstall 108M Aug 18 22:00 orders.dmp
-rw-r--r-- 1 maclean oinstall 140M Aug 18 22:00 orders.dmp
-rw-r--r-- 1 maclean oinstall 162M Aug 18 22:00 orders.dmp
-rw-r--r-- 1 maclean oinstall 181M Aug 18 22:00 orders.dmp

/*dump文件每秒增长20MB左右,exp导出速度恢复正常*/

/*验证了之前设置server端16级的sqlnet trace会引起exp性能下降的猜测*/

/*如果是expdp数据泵导出工具是否会受到该sqlnet trace的影响呢*/

[maclean@rh2 s01]$ echo "TRACE_LEVEL_SERVER = 16
> TRACE_FILE_SERVER = SERVER
> TRACE_TIMESTAMP_ SERVER = ON
> TRACE_UNIQUE_SERVER = ON" > $ORACLE_HOME/network/admin/sqlnet.ora

[maclean@rh2 dump]$ expdp sh/sh directory=ordump dumpfile=orders.dmp tables=orders
Export: Release 11.2.0.1.0 - Production on Wed Aug 18 22:05:17 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SH"."SYS_EXPORT_TABLE_01":  sh/******** directory=ordump dumpfile=orders.dmp tables=orders
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 261 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SH"."ORDERS"                               223.8 MB 2319232 rows
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SH.SYS_EXPORT_TABLE_01 is:
  /s01/dump/orders.dmp
Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at 22:05:29

/* expdp数据泵在12s内完成了导出工作,可见其并不受到sqlnet trace的不良影响*/

/* 如果我们降低Sqlnet trace的级别,是否可以降低其对exp导出性能的影响呢?*/

/*尝试将trace level改成1*/

[maclean@rh2 s01]$ echo "TRACE_LEVEL_SERVER = 1
> TRACE_FILE_SERVER = SERVER
> TRACE_TIMESTAMP_ SERVER = ON
> TRACE_UNIQUE_SERVER = ON"   >  $ORACLE_HOME/network/admin/sqlnet.ora

maclean@rh2 ~]$ exp sh/sh file=/s01/orders.dmp direct=y tables=orders
Export: Release 11.2.0.1.0 - Production on Wed Aug 18 22:11:57 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                         ORDERS    2319232 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

[maclean@rh2 s01]$ while (1>0) do ls -lh orders.dmp ; sleep 1; done
-rw-r--r-- 1 maclean oinstall 0 Aug 18 22:11 orders.dmp
-rw-r--r-- 1 maclean oinstall 33M Aug 18 22:11 orders.dmp
-rw-r--r-- 1 maclean oinstall 74M Aug 18 22:11 orders.dmp
-rw-r--r-- 1 maclean oinstall 108M Aug 18 22:12 orders.dmp
-rw-r--r-- 1 maclean oinstall 146M Aug 18 22:12 orders.dmp
-rw-r--r-- 1 maclean oinstall 183M Aug 18 22:12 orders.dmp

/* 导出速度没有明显下降,较低级别的server端sqlnet trace不会对exp导出性能造成影响*/

这个例子告诉我们在使用高级别的sqlnet trace诊断Oracle网络问题后,一定要记得要还原现场的配置,否则可能造成”莫名的性能问题”。

图文详解安装NetBackup 6.5备份恢复Oracle 10g rac 数据库(修订)

我们使用Linux平台进行测试,OS版本为Oracle Enterprise Linux 5.5 x86_64:
[root@nas servsoft]# cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m

Netbackup 6.0仅支持2.4内核的Linux版本,2.6内核的Linux版本(主流的包括RHEL4,5 Centos等)需要使用NBU,只能安装Netbackup 6.5或以上版本。
现在我们有三台主机: rh1(rac的2号节点),rh2(rac的1号节点),nas(NBU Server)。
首先需要安装的是Netbackup Server端软件,当然你需要用到安装介质,你可以尝试在Veritas的官方网站下载到最新的版本;获取到安装介质后,我们首先要解压它:

[root@nas netbackup]# cp NetBackup_6.5_LinuxRedhat2.6.tar.gz /tmp

[root@nas tmp]# gunzip NetBackup_6.5_LinuxRedhat2.6.tar.gz

[root@nas tmp]# tar -xvf NetBackup_6.5_LinuxRedhat2.6.tar

在进入安装前确认xinetd服务正确运行着:

[root@nas tmp]# service xinetd status

xinetd (pid  2886) is running...

[root@nas NB_65_LinuxR_x86_20070723]# ./install

Do you want to install NetBackup and Media Manager files? [y,n] (y) y

NetBackup and Media Manager are normally installed in /usr/openv.

Is it OK to install in /usr/openv? [y,n] (y) y

Reading NetBackup files from /tmp/NB_65_LinuxR_x86_20070723/linuxR_x86/anb

...................

Enter the full path name to the directory where the

appropriate installics script is located followed by

a  to continue. This script will then install

the package(s).

        OR

Enter q to stop this install and abort.

此时我们需要输入Netbackup ISC(Infrastructure Core Services)软件所在的目录,当然你也可以从Veritas官方网站下载到该软件包,尝试解压:

[root@nas tmp]# cp NetBackup_6.5_ICS_LinuxX86.tar.gz /tmp

[root@nas tmp]# cd /tmp

[root@nas tmp]# gunzip NetBackup_6.5_ICS_LinuxX86.tar.gz

[root@nas tmp]# tar -xvf NetBackup_6.5_ICS_LinuxX86.tar

则此时ISC安装介质位于/tmp/NB_65_ICS_1.4.37.0_LinuxX86下,在原终端窗口中输入该目录

Enter q to stop this install and abort.

/tmp/NB_65_ICS_1.4.37.0_LinuxX86

Installing VRTSpbx...

A NetBackup Server or Enterprise Server license key is needed

for installation to continue.

Enter license key:

继续安装,此时需要输入您所购买的License注册码;如果您没有购买该软件但仍想使用的话,

可以尝试下面一串字符:DEX6-23FJ-T92R-O4O4-O4O4-K777-7777-EPXP-3XO。

Enter license key: DEX6-23FJ-T92R-O4O4-O4O4-K777-7777-EPXP-3XO

DEX6-23FJ-T92R-O4O4-O4O4-K777-7777-EPXP-3XO:

        NetBackup Enterprise Server Base product with all the features enabled

        has been registered.

All additional keys should be added at this time.

Do you want to add additional license keys now? [y,n] (y) n

Use /usr/openv/netbackup/bin/admincmd/get_license_key

to add, delete or list license keys at a later time.

Installing NetBackup Enterprise Server version: 6.5

If this machine will be using a different network interface than the

default (nas), the name of the preferred interface should be used

as the configured server name.  If this machine will be part of a

cluster, the virtual name should be used as the configured server name.

Would you like to use "nas" as the configured

name of the NetBackup server? [y,n] (y) y

Is nas the master server? [y,n] (y) y

Do you have any media servers? [y,n] (n) n

Checking /etc/services for the needed NetBackup and Media Manager services.

Copying original /etc/services file to /etc/services.NBU_062910.14:27:41

Editing /etc/services to update NetBackup and Media Manager services.

/etc/services will be updated to add the following entries for

NetBackup/Media Manager.

bpjobd  13723/tcp       bpjobd

vmd     13701/tcp       vmd

acsd    13702/tcp       acsd

tl8cd   13705/tcp       tl8cd

tldcd   13711/tcp       tldcd

odld    13706/tcp       odld

tl4d    13713/tcp       tl4d

tshd    13715/tcp       tshd

tlmd    13716/tcp       tlmd

tlhcd   13717/tcp       tlhcd

rsmd    13719/tcp       rsmd

...................

好了Netbackup Server端软件已经在NAS主机上安装完成,接下来我们需要进一步配置备份策略。

将/usr/openv/netbackup/bin路径加入到你的用户环境变量PATH中,以方便调用相关执行文件;

并在具有X11 forwarding功能的软件中(譬如Xmanager)中输入jnbSA命令,

您可能遭遇java.lang.UnsatisfiedLinkError: /usr/openv/java/jre/lib/i386/libawt.so: libXp.so的错误,

一般是由于没有安装libXp(i386和x86_64版本的都装一下)包所导致的。

正确安装的话输入jnbSA命令可以看到以下界面:

接着我们需要定义存储单元(Storage Unit),如果你同我一样没有真实的磁带机的话那么我们可以定义普通Disk

类型的存储单元, 选择Netbackup Management->Storage -> Storage Unit,在右边分隔栏右键点击New Storage Unit,

为你的存储单元起一个名字,并输入相关存储目录:

接下来点击NetBackup Management->Policies 选项定义Oracle备份使用到的备份策略,启用Backup Policy Configuration Wizard,并选择Oracle为备份策略类型:

在客户端列表(client list)中加入需要备份2台RAC所在主机,分别为rh1,rh2;硬件与操作系统选择Linux,Redhat2.6

好了,server端的配置完成了,接下来我们安装client端软件,安装前确认你已经获得了相关安装介质,以NBU6.5举例来说你需要有:NetBackup_6.5_CLIENTS2.tar.gz和NetBackup_6.5_UnixOptions.tar.gz 分别为Client端和Oracle Agent软件。

[root@rh2 tmp]# gunzip  NetBackup_6.5_UnixOptions.tar.gz
[root@rh2 tmp]# gunzip  NetBackup_6.5_CLIENTS2.tar.gz
[root@rh2 tmp]# tar -xvf NetBackup_6.5_UnixOptions.tar
[root@rh2 tmp]# tar -xvf NetBackup_6.5_CLIENTS2.tar
[root@rh2 tmp]# cd NB_65_CLIENTS2_20070723/
[root@rh2 NB_65_CLIENTS2_20070723]# ./install

Symantec Installation Script
Copyright 1993 - 2007 Symantec Corporation, All Rights Reserved.

Installing NetBackup Client Software

NOTE:  To install NetBackup Server software, insert the appropriate
NetBackup Server cdrom.

Do you wish to continue? [y,n] (y) y
Do you want to install the NetBackup client software for this client? [y,n] (y) y

This package will install Linux/RedHat2.6 client.

This package will install NetBackup client 6.5.

Enter the name of the NetBackup server : nas

Would you like to use "rh2" as the configured
name of the NetBackup client? [y,n] (y) y
........................
File /usr/openv/tmp/install_trace.10994 contains a trace of this install.
That file can be deleted after you are sure the install was successful.

[root@rh2 tmp]# cd NB_65_UOptions_20070723/
root@rh2 NB_65_UOptions_20070723]# ./install

Symantec Installation Script
Copyright 1993 - 2007 Symantec Corporation, All Rights Reserved.

Installation Options

1 NetBackup Add-On Product Software
2 NetBackup Database Agent Software

q To quit from this script
Choose an option [default: q]: 2

**********

There are two ways to install database agent software.

1.  Remote Installation:  Loads the software on a server with
the intent of pushing database software out to affected clients.

2.  Local Installation:   Loads and installs the software only to this
local machine.

**********

Do you want to do a local installation? [y,n] (n) y

**********

NetBackup Database Agent Installation

Choose the Database Agents you wish to install
one at a time or select Install All Database Agents.

1)  NetBackup for DB2
2)  NetBackup for Informix
3)  NetBackup for Lotus Notes
4)  NetBackup for Oracle
5)  NetBackup for SAP
6)  NetBackup for Sybase

7)  Install All Database Agents

q)  Done Selecting Agents
x)  Exit from this Script

Choose an option: 4

Choose an option: q

You have chosen to install these Database Agents:

NetBackup for Oracle

Is this list correct? [y,n] (y) y

**********

Of the agents selected, the following are supported
on this platform and will be installed:

Oracle

Loading the Database Agent packages into the
/usr/openv/netbackup/dbext directory and installing.

**********

Installing NetBackup for Oracle

Installing NetBackup for Oracle...
..........................
NetBackup for Oracle installation completed.

完成NBU客户端和Netbackup for Oracle Agent安装后,我们还需要对MML介质库文件进行链接,使用dba或oinstall组账户执行/usr/openv/netbackup/bin/oracle_link文件:
[root@rh2 NB_65_UOptions_20070723]# su - maclean
[maclean@rh2 ~]$ cd /usr/openv/netbackup/bin/
[maclean@rh2 bin]$ ./oracle_link
Tue Jun 29 19:22:28 EDT 2010
All Oracle instances should be shutdown before running this script.

Please log into the Unix system as the Oracle owner for running this script

Do you want to continue? (y/n) [n]
[maclean@rh2 bin]$ echo $ORACLE_HOME
/s01/rac10g
[maclean@rh2 bin]$ ./oracle_link
Tue Jun 29 19:22:35 EDT 2010
All Oracle instances should be shutdown before running this script.

Please log into the Unix system as the Oracle owner for running this script

Do you want to continue? (y/n) [n] y

LIBOBK path: /usr/openv/netbackup/bin
ORACLE_HOME: /s01/rac10g
Oracle version: 10.2.0.5.0
Platform type: x86_64
Linking LIBOBK:
ln -s /usr/openv/netbackup/bin/libobk.so64 /s01/rac10g/lib/libobk.so
Done

接下来在rh2主机上进行备份测试:

[maclean@rh2 bin]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jun 29 19:26:00 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: RACDB (DBID=720516428)

RMAN> run
2> { allocate channel c1 type sbt parms="ENV=(NB_ORA_SERV=nas,NB_ORA_POLICY=racdb,NB_ORA_CLIENT=rh2)";
3> backup current controlfile;
4> release channel c1;
5> }
RMAN> run
2> { allocate channel c1 type sbt parms="ENV=(NB_ORA_SERV=nas,NB_ORA_POLICY=racdb,NB_ORA_CLIENT=rh2)";
3> backup current controlfile;
4> release channel c1;
5> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=136 instance=racdb1 devtype=SBT_TAPE
channel c1: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

Starting backup at 29-JUN-10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 29-JUN-10
channel c1: finished piece 1 at 29-JUN-10
piece handle=03lhfi11_1_1 tag=TAG20100629T192729 comment=API Version 2.0,MMS Version 5.0.0.0
channel c1: backup set complete, elapsed time: 00:00:37
Finished backup at 29-JUN-10

released channel: c1

如上所示成功备份了当前控制文件。
RMAN> run
2> {
3> allocate channel c1 type sbt parms="ENV=(NB_ORA_SERV=nas,NB_ORA_POLICY=racdb)";
4> backup archivelog all delete input;
5> release channel c1;
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=136 instance=racdb1 devtype=SBT_TAPE
channel c1: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

Starting backup at 29-JUN-10
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=2 stamp=722901460
input archive log thread=1 sequence=2 recid=4 stamp=722901476
input archive log thread=1 sequence=3 recid=5 stamp=722901499
input archive log thread=1 sequence=4 recid=6 stamp=722904852
input archive log thread=2 sequence=1 recid=1 stamp=722901426
input archive log thread=2 sequence=2 recid=3 stamp=722901470
input archive log thread=2 sequence=3 recid=7 stamp=722904852
channel c1: starting piece 1 at 29-JUN-10
channel c1: finished piece 1 at 29-JUN-10
piece handle=06lhfjqr_1_1 tag=TAG20100629T195819 comment=API Version 2.0,MMS Version 5.0.0.0
channel c1: backup set complete, elapsed time: 00:00:46
channel c1: deleting archive log(s)
archive log filename=/arch/1_1_722899663.dbf recid=2 stamp=722901460
archive log filename=/arch/1_2_722899663.dbf recid=4 stamp=722901476
archive log filename=/arch/1_3_722899663.dbf recid=5 stamp=722901499
archive log filename=/arch/1_4_722899663.dbf recid=6 stamp=722904852
archive log filename=/arch/2_1_722899663.dbf recid=1 stamp=722901426
archive log filename=/arch/2_2_722899663.dbf recid=3 stamp=722901470
archive log filename=/arch/2_3_722899663.dbf recid=7 stamp=722904852
Finished backup at 29-JUN-10

Starting Control File and SPFILE Autobackup at 29-JUN-10
piece handle=c-720516428-20100629-01 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 29-JUN-10

released channel: c1

沪ICP备14014813号-2

沪公网安备 31010802001379号