细心专业：作者这几年几乎把他的全部心思都扑到了Oracle上，看看他的文章，或者是他的新书就可以看到这个特点，如他Oracle内部结构的研究，一些参数（包括隐含参数）的研究上面，发现了很多别人没有发现的问题，给广大的Oracle爱好者提供了非常大的帮助。包括我自己，就是他的网站的忠实读者，因为可以经常在这里找到一些好东西。而他的新书，则是这些长久的经验的积累与汇总。

经验丰富：我曾经看过作者很多实际中的诊断案例，可以看到其思维非常严谨，能从一点点蛛丝马迹中发现问题的所在。这种功底同样反映到他的新书上面，对于一些别人不曾注意的角落，他都可以挖掘出来很有用的信息，并最终诊断出来问题的所在。在本书中，收录了作者大量作为资深DBA所遇到的很多实际案例，我相信，这种实际的经验总结能给大家非常大的帮助。

本书是作者的第三本著作，也算是对《深入浅出Oracle》的再版，继承了浅显易懂，也不缺乏深入研究的传统。对于初级读者，可以从作者的新书中马上获得很多有用的信息；对于中高级读者，也可以从书中获得很多深入的研究信息；对于实际工作中的DBA，则可以从中获得很多有价值的案例分析方法与实际的处理技巧。

陈吉平（淘宝网首席DBA，Oracle ACE，《构建Oracle高可用环境》一书作者）

2008年10月于杭州

10月
14th

新书马上第三次印刷

新书服务|Book, 所有|All Blogs

piner

Read on..

出版社告诉我，第二次印刷的2000本也马上就没有了，现在要准备第三次印刷。该书从1月份出版到4月份，就进行了二次印刷，现在马上又要第三次印刷了。也就是说，到现在为止，大约有6000本书被各位读者买走了。

感谢各位读者对本书的支持，非常感谢。

4月
1st

新书前言（第二次印刷）

新书服务|Book, 所有|All Blogs

piner

Read on..

这里，首先要感谢广大读者的支持与认可，该书第一次印刷到现在，2个多月的时间，就要准备第二次印刷了。在此期间，我也看到了很多读者反馈（包括读者书评与读者来信），他们对本书的肯定，让我深表感动。本书在写作与印刷过程中，也出现了部分的错误，感谢广大读者通过来信，或者是在我的网站www.ixdba.com上留言指出了这些错误。本书在第二次印刷时会修正这些错误。

作为本书的作者，当然更希望广大的读者能从本书中获得他们想要的知识，并能帮助他们获得自己的成功。高可用性，在未来的一些年中，也将是越来越被引起重视的技术。需要强调的是，高可用性不仅仅局限在一两个技术上面，它应当是一个范畴，包括高可用的规划与设计，建设与实施，管理与维护，等等。

正因为高可用的范围如此广泛，所以本书所铺设的内容也特别之多。也有读者反映，因为内容太多，而在具体的细节内容上面，没有过分深入地进行介绍。这个问题其实与本书的写作规划有关，当初就是从如下三个方面来规划本书的：

（1）扩充视野

现在不管是国内还是国外，能独立管理从最底层的存储、主机、数据库，到支持最上层的应用产品设计的个人与团队是很少的，而我们团队就可以接触到以上相关技术。接触的东西越多，视野其实就越开阔，能看到的问题也越多，所以，在整个高可用性环境方面，我希望大家能从整体上去考虑，并了解影响到系统高可用性的有哪些东西。

随着科技的日新月易，传统企业应用与互联网应用开始产生越来越多的差异：传统的企业应用更关注于单个数据库（或数据库集群）的处理能力以及系统硬件本身的可靠性；而互联网应用，由于分布系统的大规模出现，可以使用普通的硬件设备（如PC Server）来实现大规模冗余，通过廉价的设备也可以达到整个系统的高可用性（如Google与Amazon在这方面就做的很不错）。所以，当在互联网应用中评价系统压力时，不再是单纯地评估数据库的处理能力，而是评估整个系统的处理能力，因为数据可能不存在于任何传统的数据库中，DBA也不再仅仅是数据库管理人员，而有可能是数据管理人员。

很难说，互联网技术以后就不影响传统企业现有的技术，广泛的了解与对比，是本书的目的之一。

（2）拓展思路

在写作本书之前，我就已经跟非常多的厂家及很多服务提供商打过交道，经常遇到其销售人员把自己的产品说成金条，把别人的产品贬低为稻草的情况。如我在刚开始接触硬件的时候，听到IBM介绍IBM的产品，就觉得它是最好的了；马上，HP过来介绍HP的产品，我又觉得HP是最好的。但是，如果经验多了，了解也多了，情况就不一样了，别人的吹捧已经不能影响我自己的思路。所以，在本书的前几章，介绍产品选择与构架选择时，也一直在表达这一个观点，即一定要有自己的思路与观点，不要让别人左右你自己的思想，否则销售人员很容易就影响你了（关于销售人员的技巧，大家有兴趣的话，可以看看《影响力》这本书）。

本书后面的一些章节，如数据迁移、监控体系，也是从构架设计的思路开始来描述具体的实现方法的。在信息化如此完善的今天，本身获得一两个方法是很简单的，但是，如果把方法系统化地规划起来，则是另外一种能力。好的思路，可以很快地帮大家获得成功，仅仅知道好的方法，不表示就有好的思路。

不要用简单的思维去看那些构架，不要仅仅是关注其中的技术而抛弃了思想，否则，我只能说你获得了皮毛而抛弃了精髓，成语”买椟还珠”也就是这个意思。

（3）获得经验

因为很多人认为高可用性技术就是几个技术那么简单，所以他们理解的Oracle高可用就是RAC技术与Data guard技术。而高可用性的管理与维护，有时候比高可用技术本身更值得关注与借鉴。因为技术本身，是在不断地变化过程中的。简单地说，如RAC的安装与配置，在不同的OS、不同的版本中，就有很大的差异，如RAC方面的bug，在不同的环境下也有差异。而维护与管理经验是通用的，也是最直接的、最宝贵的技术。

本书的一些案例，操作使用方法，维护经验与优化经验其实都是高可用经验的总结。通过学习这些经验，可以让读者在面对真正的高可用环境时，不至于惊慌失措。

所以本书

对于高级读者，可能不需要关注方法，而仅仅是关注自己不曾理解的领域与思路。

对于初级读者，如果有了一定的基础，一次看不懂没有关系，通过知识面的不断扩充，再看一次，可能会有新的收获。

对于未入门者，个人建议先了解一些基础知识，然后再看本书可能更有收获。

最后，再次感谢对本书提出宝贵意见与建议的读者们，也希望你们获得更大更多的成功。

陈吉平

2008年3月于杭州

3月
10th

新书要第二次印刷了

新书服务|Book, 所有|All Blogs

piner

Read on..

根据出版社的信息，书库中的书已经不多了，马上就要第二次印刷了。第二次印刷可以修正掉已知的错误，所以质量将会比第一版更好。

一直比较忙，没有太关心书的事情，想不到卖得还是挺快的，从第一次印刷到现在，也就3个月的时间。

在此，非常感谢博文视点与出版社，非常感谢大家的支持，非常感谢广大读者提出的宝贵意见。

2月
27th

读者来信：piner的书《构建oracle高可用环境》读后感

新书服务|Book, 所有|All Blogs

piner

Read on..

很感谢这些读者对我，以及这本书的肯定，非常感谢。来信内容也可以参考：

http://blog.csdn.net/yzsind/archive/2008/02/27/2124819.aspx

piner的书《构建oracle高可用环境》一出来就看到了，由于还有几本书未读完，所以没有立即买，但是建议了同事去买来看看，春节前两天买到了。由于对书的内容及大师的景仰，所以就先看了。春节放假后回到老家，也把书带回家了看了。在春节期间，我们那刚好全县停电，感觉基本什么都做不了，除了睡觉，就是吃饭了。所以自然春节几天一直读这本书，感觉越看越深入，在读的过程中感觉非常流畅，感觉是一个大师在旁边一直介绍似的。基本上三天就看完了一半，上班后又接着看，花了一个星期的晚上时间看完，收益非浅啊。

这本书是介绍了大部份高可用性ORACLE配置及管理的知识，包括主机、存储、安全、灾容、RAC、StandBy、Stream、物化视图、FlashBack、备份、数据迁移、优化、在线维护、监控等等，基本上都全了。里面的理论架构介绍不多，但很实用，实践指导性非常强，脚本及案例都很直观并非常具有说服力，让人看得比较轻松，也感觉到大师的ORACLE功底之深及知识之广。

虽然这是一本DBA管理方面主题的书，但作为一名软件架构师或系统分析师，感觉自己在数据库架构设计方面又有了新的认识。

系统监控也长见识了。对数据存储及主机方面也增长了许多见识，对一些高端的存储也有了新的了解。对高可用性方面有了新的认识，以前我们开发的系统虽然称是7*24的，但实际上基本上是7*8小时的要求，所以对高可用性也没有那么深入，理解也不深入，看了书后深深感觉到7*24*365小时服务的可靠性要求。对于7*8小时的应用基本上数据库结构更新都是考虑越方便越好，因为可以有一晚上的时间给你更新。以前对于oralce11g的新特性都了解过，感觉对自己意义不大，不过看过书后才恍然大悟，发现11g基本上每个更新在高可用性方面都有重大意义。

现在国内的计算机技术书每年都有很多，但感觉大部份都是重复作品，从软件的参考手册中COPY，源代码及软件截图一大堆，一本书下来8,9百页，实际作者自己的内容不过100页。oracle方面的书也是如此，看过许多，除了eygle的几本书外，其它的大部份如此，不过在我印象最深的，看得最投入的就是去年看的TOM的《oracle9i/10g编程艺术》，和这次看到了piner的《构建oracle高可用环境》，这两本是我看得最舒服的书了。

在这里也谢谢piner献给我们ORALCE技术人员这么好的礼物！！！

2月
23rd

新书代码服务

新书服务|Book, 所有|All Blogs, 代码服务|code

piner

Read on..

新书出来好久了，因为我也一直比较忙，代码服务一直还没有跟上。这里很感谢广大读者的支持与理解，今天我将把本书的代码整理出来并共享给广大读者。

第1章

P12

Piner@10gR2>select f,b from (
select dbms_rowid.rowid_relative_fno(rowid) f,
　　　　　 dbms_rowid.rowid_block_number(rowid) b
from test) group by f,b;
Piner@10gR2>select file#,dbablk,tch from x$bh where obj=
(select data_object_id from dba_objects
where owner='PINER' and object_name='TEST')
order by dbablk;

P15

select count(*) from v$bh where objd=
(select data_object_id from dba_objects
where owner='PINER' and object_name='TEST')
and status !='free'

P16

select /*+ rule */ owner,object_name from dba_objects
where data_object_id in
(select obj from
(select obj from x$bh order by tch desc)
where rownum < 11) ;
SQL> select tch, flag,
decode(bitand(flag,1), 0, 'N', 'Y') dirty,
decode(bitand(flag,16), 0, 'N', 'Y') temp,
decode(bitand(flag,1536), 0, 'N', 'Y') ping,
decode(bitand(flag,16384), 0, 'N', 'Y') stale,
decode(bitand(flag,65536), 0, 'N', 'Y') direct,
decode(bitand(flag,1048576), 0, 'N', 'Y') new
from x$bh
where dbablk = 12
and obj=11835
and tch>0;

P38

select dbms_rowid.rowid_object('AAAAeNAADAAAAWZAAA') data_object_id#,
dbms_rowid.rowid_relative_fno('AAAAeNAADAAAAWZAAA') rfile#,
dbms_rowid.rowid_block_number('AAAAeNAADAAAAWZAAA') block#,
dbms_rowid.rowid_row_number('AAAAeNAADAAAAWZAAA') row# from dual;

第2章

P77

#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 8 -type rand &
#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 1024 -size_large 1024 -type seq &
#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 128 -matrix col -num_small 500 -type rand &
#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 128 -matrix detailed -type rand &

第4章

P161

rac1.local_listener='(address=(protocol=tcp)(host=dbrac01)(port=1521)) '
rac1.remote_listener='(address=(protocol=tcp)(host=dbrac02)(port=1521)) '
rac2.local_listener='(address=(protocol=tcp)(host=dbrac02)(port=1521)) '
rac2.remote_listener='(address=(protocol=tcp)(host=dbrac01)(port=1521)) '
$ more tnsnames.ora
# tnsnames.ora.Network Configuration File
rac=
(description=
(load_balance=on)
(failover=on)
(address_list=
(address=(protocol=tcp)(host=dbrac01)(port=1521))
(address=(protocol=tcp)(host=dbrac02)(port=1521)))
(connect_data=
(service_name=rac)))
[oracle@dbtest admin]$ more test.sh
#!/bin/sh
sqlplus "test/test@rac" <<EOF
select instance_name from v\$instance;
exit
EOF

P162

Listeners_rac =
(address_list =
(address = (protocol = tcp)(host =dbrac01-vip)(port = 1521))
(address = (protocol = tcp)(host =dbrac02-vip)(port = 1521))
(address = (protocol = tcp)(host =dbrac03-vip)(port = 1521))
(address = (protocol = tcp)(host =dbrac04-vip)(port = 1521))
)
Rac =
(description =
(load_balance = on)
(address = (protocol = tcp)(host = dbrac01-vip)(port = 1521))
(address = (protocol = tcp)(host = dbrac02-vip)(port = 1521))
(address = (protocol = tcp)(host = dbrac03-vip)(port = 1521))
(address = (protocol = tcp)(host = dbrac04-vip)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = rac)
)
)

P164

rac=
(description=
(load_balance=on)
(failover=on)
(address_list=
(address=(protocol=tcp)(host=dbtest)(port=1521))
(address=(protocol=tcp)(host=dbtest)(port=1522)))
(connect_data=
(service_name=rac)
(failover_mode=(type=select)(method=basic))
))

P183

SQL>create diskgroup dgroup1
Normal reduadancy
failgroup controller1
disk '/devices/diska1', '/devices/diska2','/devices/diska3','/devices/diska4',
failgroup controller2
disk '/devices/diskb1', '/devices/diskb2', '/devices/diskb3', '/devices/diskb4';

P187

create database orcl
controlfile reuse
logfile '+dgroup1/redo1' size 100M
datafile '+dgroup1/system' size 1G
sysaux datafile '+dgroup1/sysaux' size 2G
default temporary tablespace temp
tempfile '+dgroup1/temp' size 8G
undo tablespace undo
datafile '+dgroup1/rollback' size 8G;

第5章

P205

db_unique_name=pridb
log_archive_config='DG_CONFIG=(pridb,stbdb)'
log_archive_dest_1='LOCATION=/arc/archive/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pridb'
log_archive_dest_state_1=ENABLE
log_archive_dest_2='SERVICE=standby_test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stb_db'
log_archive_dest_state_2=ENABLE
SQL>alter database add standby logfile
group 4 ('/u01/oracle/oradata/tbdb/stdy_redo04.log') size 102400K,
group 5 ('/u01/oracle/oradata/tbdb/stdy_redo05.log') size 102400K,
group 6 ('/u01/oracle/oradata/tbdb/stdy_redo06.log') size 102400K,
group 7 ('/u01/oracle/oradata/tbdb/stdy_redo07.log') size 102400K;

P208

$more removearchive.sh
#!/usr/bin/ksh
# creator: piner
# function: clear archive log of standby database if it had applied
# usage: crontab on standby
# last modify: 2007-10-10 create
#env variable
export ORACLE_HOME=/u01/oracle/product/9.2
export HOME=/home/oracle
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=test
#local variable
BDUMP=/u01/oracle/admin/${ORACLE_SID}/bdump
ALERT=$BDUMP/alert_${ORACLE_SID}.log
ALERTBAK=$BDUMP/alert_${ORACLE_SID}.log.bak
RMARC=$HOME/worksh/rmarchlog.sh
TMLOG=$HOME/logs/logtmp.log
#get rm archivelog script，thread 1
grep "Media Recovery Log" $ALERT|grep 1_|sed '$d'|awk '{print $4}'|sed -e 's/^/rm /' > $RMARC
grep "Media Recovery Log" $ALERT|grep 1_|sed -n '$p' > $TMLOG
#if rac env,then thread 2
#grep "Media Recovery Log" $ALERT|grep 2_|sed '$d'|awk '{print $4}'|sed -e 's/^/rm /' >> $RMARC
#grep "Media Recovery Log" $ALERT|grep 2_|sed -n '$p' >> $TMLOG
#start rm archive log
ksh $RMARC
#clear alert log
cat $ALERT >> $ALERTBAK
cat $TMLOG > $ALERT
#rm tmp file
rm -f $RMARC
rm -f $TMLOG
echo "DB done"
ARCHIVE_DIR=/arc/archive/test
#find $ ARCHIVE_DIR -name "*.arc" -ctime +3 -exec rm {} \;

P209

#!/bin/ksh
# creator: piner
# function: check standby archive send and applied
# usage: crontab on standby
# last modify: piner 2005-4-20 create
#environment variable
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=american_america.zhs16gbk
#local variable
LOGFILE=/home/oracle/logs/logstatus.log
SQLPLUS=$ORACLE_HOME/bin/sqlplus
DATE=`date +%Y-%m-%d:%H:%M:%S`
dbname=`hostname`
#monitor value
appdoff=0.5
sendoff=0.02
#check database status
$SQLPLUS /nolog <<EOF
connect / as sysdba
spool $LOGFILE
SELECT status FROM v\$instance;
spool off
exit
EOF
sleep 1
if [ -f $LOGFILE ] ; then
STATUS=`egrep -i "(OPEN|MOUNT)" $LOGFILE|wc -l`
echo $STATUS
else
touch $LOGFILE
echo "log file not exists" > $LOGFILE
fi
if [ $STATUS -eq 0 ] ; then
mesglog="$dbname standby status error,db not mount or open!"
##Standby没有open或者是mount
##发送邮件，关于发送邮件的方法，参考本书第16章
exit 1
fi
#check archive log applied
$SQLPLUS /nolog <<EOF
connect / as sysdba
spool $LOGFILE
select decode(Sign(max(Next_time) - (sysdate - $appdoff)),-1,'false','true') from v\$archived_log t
where applied = 'YES' and first_time >sysdate-1;
select thread#,sequence#, to_char(next_time,'yyyy-mm-dd hh24:mi:ss') log_time,applied from v\$archived_log t
where applied = 'NO' and first_time>sysdate-1
order by sequence#;
spool off
exit
EOF
APPSTATUS=`grep -i "false" $LOGFILE|grep -v "'false','true'"|wc -l`
if [ $APPSTATUS -ge 1 ] ; then
##应用日志错误
##发送邮件，关于发送邮件的方法，参考本书第16章
fi
#check archive log sended
$SQLPLUS /nolog <<EOF
connect / as sysdba
spool $LOGFILE
select decode(Sign(max(next_time) - (sysdate - $sendoff)),-1,'false','true') from v\$archived_log t
where first_time>sysdate-1;
select max(sequbnce#) max_sequbnce#,to_char(max(next_time),'yyyy-mm-dd hh24:mi:ss') log_time from v\$archived_log t
where first_time>sysdate-1;
spool off
exit
EOF
SENDSTATUS=`grep -i "false" $LOGFILE|grep -v "'false','true'"|wc -l`
if [ $SENDSTATUS -ge 1 ] ; then
##发送日志错误
##发送邮件，关于发送邮件的方法，参考本书第16章
fi

P214

LISTENER_PRI =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_db)(PORT = 1521))
)
)
)
SID_LIST_LISTENER_PRI =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/product/9.2)
(SID_NAME = test)
)
)
LISTENER_STB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby_db)(PORT = 1522))
)
)
)
SID_LIST_LISTENER_STB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/product/9.2)
(SID_NAME = test)
)
)
test =
(DESCRIPTION =
(failover = on )
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_db)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = standby_db)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
)

第6章

P238

SQL>create tablespace streams_tbs datafile 'file_name' size 500M;
SQL> create user strmadmin identified by password
2 default tablespace streams_tbs
3 quota unlimited on streams_tbs;
SQL> grant dba to strmadmin;
SQL> connect strmadmin/password
SQL> create database link dbdest.net connect to strmadmin
2 identified by password using 'dest_db';
SQL> connect strmadmin/password
SQL> create database link dbsour.net connect to strmadmin
2 identified by password using 'source_db';

P240

SQL>connect strmadmin/test
SQL> declare
2 empty_tbs dbms_streams_talespace_adm.tablespace_set;
3 begin
4 dbms_streams_adm.pre_instantiation_setup(
5 maintain_mode => 'GLOBAL',
6 tablespace_names => empty_tbs,
7 source_database => 'dbsour.net',
8 destination_database => 'dbdest.net',
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 exclude_schemas => NULL,
14 exclude_flags => dbms_streams_adm.exclude_flags_unsupported +
15 dbms_streams_adm.exclude_flags_dml +
16 dbms_streams_adm.exclude_flags_ddl);
17 end;
18 /

P242

SQL> set serveroutput on size 1000000
SQL> declare
2 until_scn number;
3 begin
4 until_scn:= dbma_flashback.get_system_change_number;
5 dbms_output.put_line('Until SCN: ' || until_scn);
6 end;
7 /
RMAN> run
2> {
3> set until scn 533910;
4> duplicate target database to dbdest nofilenamecheck open restricted;
5> }

P244

SQL>DECLARE
2 empty_tbs dbms_streams_tablespace_adm.tablespace_set;
3 BEGIN
4 dbms_streams_adm.post_instantiation_setup (
5 maintain_mode => 'GLOBAL',
6 tablespace_names => empty_tbs,
7 source_database => 'dbsour.net',
8 destination_database => 'dbdest.net',
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 instantiation_scn => 533909,
14 exclude_schemas => NULL,
15 exclude_flags => dbms_streams_adm.exclude_flasgs_unsupported +
16 dbms_streams_adm.exclude_flags_dml +
17 dbms_streams_adm.exclude_flags_ddl);
18 end;
19 /

P245

STRMADM@dest>begin
2 dbms_streams_adm.maintain_global(
3 source_directory_object => 'DIR_SOURCE',
4 destination_directory_object => 'DIR_DEST',
5 source_database => 'dbsour.test',
6 destination_database => 'dbdest.test',
7 perform_actions => true,
8 dump_file_name => 'streams_rep.dmp',
9 bi_directional => false,
10 include_ddl => true,
11 instantiation => dbms_streams_adm.instantiation_full);
12 end;
13 /

P246

declare
tables dbms_utility.uncl_apply;
begin
tables(1) := 'piner.test1';
tables(2) := 'piner.test2';
tables(3) := 'piner.test3';
tables(4) := 'piner.test4';
dbms_streams_adm.maintain_tables(
table_names => tables,
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'dbsour.net',
destination_database => 'dbdest.net',
perform_actions => false,
script_name => 'configure_rep.sql',
script_directory_object => 'SCRIPT_DIRECTORY',
bi_directional => false,
include_ddl => false,
instantiation => dbms_streams_adm.instantiation_table_network );
end;
/

P247

SQL>begin
2 dbms_capture_adm.set_parameter(
3 capture_name => 'PINER$CAP',
4 parameter => 'downstream_real_time_mine',
5 value => 'y');
6 end;
7 /

P249

SQL>col forward_block heading 'Forward Block' format A50
SQL>col forward_block_dblink heading 'Forward Block|Database Link' format A13
SQL>col status heading 'Status' format A12
SQL>set long 10000
SQL>select forward_block,forward_block_dblink,status
2 from dba_recoverable_script_blocks
3 where script_id = '39EDB8E5AB6CD5C3E040010A3D0552BE'
4 and block_num = 7;
SQL>begin
2 dbms_streams_adm.recover_operation(
3 script_id => '39EDB8E5AB6CD5C3E040010A3D0552BE',
4 operation_mode => 'FORWARD');
5 end;
6 /

P250

begin
dbms_propagation_sdm.start_ propagation (
propagation_name => 'PROPAGATION$_195');
end;
/
begin
dbms_apply_sdm.set_parameter(apply_name=>'APPLY$_DBSOUR_187',
parameter=>'disable_on_error',
value=>'N');
end;
/
exec dbms_apply_sdm.start_apply(apply_name=>'APPLY$_DBSOUR_187');

P251

exec dbms_capture_adm.stop_capture(capture_name);
exec dbms_capture_adm.drop_capture(capture_name);
exec dbms_propagation_adm.stop_propagation(propagation_name);
exec dbms_propagation_adm.drop_propagation(propagation_name);
exec dbms_apply_adm.stop_apply(apply_name);
exec dbms_apply_adm.drop_apply(apply_name);
exec dbms_streams_adm.remove_streams_configuration;

第8章

P295

Piner@10gR2> select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, a,b
3 from test versions between timestamp minvalue and maxvalue
4 order by versions_starttime;

P298

13:25:41 Piner@10gR2>select versions_xid, versions_operation
13:25:42 2 from test versions between timestamp
13:25:42 3 to_date('2007-08-27 13:21:56','yyyy-mm-dd hh24:mi:ss')
13:25:42 4 and MAXVALUE
13:25:42 5 WHERE versions_xid is not null
13:25:42 6 order by VERSIONS_STARTTIME;

P307

Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
Piner@9iR2>exec sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc');
Piner@9iR2>select t.SCN,t.TIMESTAMP,t.SEG_OWNER,t.OPERATION,
2 t.SQL_REDO,t.SQL_UNDO
3 from v$logmnr_contents t where t.SEG_NAME='TEST';

P309

declare
mysql varchar2(4000);
num number :=0;
begin
for c_tmp in (select sql_undo from logmnr_contents where operation = 'DELETE') loop
--去掉语句结尾的分号，如果语句中本身有分号，则不能采用这个方法。
mysql := replace(c_tmp,sql_undo,';','');
execute immediate mysql;
num := num + 1;
if mod(num,1000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
--异常处理
end;

P310

SQL>select name,sequence#,dictionary_begin d_beg, dictionary_end d_end
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log
where dictionary_end = 'YES' and sequence# <= 210);--或者是>=210
SQL> select name,sequence#,dictionary_begin d_beg, dictionary_end d_end
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log
where dictionary_begin = 'YES' and sequence# <= 208);
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_210_482701534.dbf',options => dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_onlinr_catalog + dbms_logmnr.committed_data_only);
SQL>alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
exec dbms_logmnr.start_logmnr(dictfilename => '/oracle/database/dictionary.ora',
starttime => '2007-04-11 08:30:00',
endtime => '2007-04-11 08:45:00');

P314

SQL> declare i integer;
2 begin
3 for i in 1..100 loop
4 insert into test values(i,100-i);
5 end loop;
6 commit;
7 end;
8 /
SQL> create or replace trigger tr_test
2 before insert or update or delete on test
3 for each row
4 declare
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 begin
7 update audit_test set c=c+1;
8 commit;
9 end;
10 /

第9章

P338

$more backup_exp.sh
#!/bin/sh
# creator: piner
# function: backup database with expdp
# usage: crontab on linux
# last modify: tuolei 2007-08-29 create
#set environment variable
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#开始备份，假定目录dump_test已经创建，并且dump_test=/u01/oracle/backup
FILE=`date date +%Y%m%d`
expdp user/pass directory=dump_test dumpfile=$FILE.dmp
#删除以前过期的备份
DUMP=/u01/oracle/backup
find $DUMP -name "*.dmp" -mtime +7 -exec rm {} \;

P342

[oracle@db worksh]$ more rmanback.sh
#!/bin/ksh
#环境变量
export ORACLE_HOME=/opt/oracle/product/9.2
export ORACLE_SID=test
export NLS_LANG="AMERICAN_AMERICA.zhs16gbk"
export PATH=$PATH:$ORACLE_HOME/bin
#备份脚本
echo "-----------------------------start-----------------------------";date
$ORACLE_HOME/bin/rman <<EOF
connect target
delete noprompt obsolete;
backup database format '/netappdata1/rmanback/tbdb2/%U_%s.bak' filesperset = 4;
exit;
EOF
echo "------------------------------end------------------------------";date

P345

run{
allocate channel node_c1 device type disk connect 'sys/pass@dbin1';
allocate channel node_c2 device type disk connect 'sys/pass@dbin2';
sql 'alter system archive log thread 2 current';
sql 'alter system archive log thread 1 current';
backup archivelog all delete input format '/u01/dbbak/%U_%s.bak' filesperset = 10;
}

P348

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> set until time = '2007-11-09:11:44:00';
5> restore database;
6> recover database;
7> alter database open resetlogs; }
RMAN> run {
2> set until sequence 120 thread 1;
3> alter database mount;
4> restore database;
5> recover database; # 恢复到日志119
6> alter database open resetlogs;
7> }
RMAN> run {
2>set newname for datafile
3>'/u01/oradata/tools01.dbf' to '/tmp/tools01.dbf';
4>restore datafile '/u01/oradata/tools01.dbf';
5>switch datafile all;
6>}

P355

#!/bin/sh
# creator: Piner
# function: backup database or archive with rman
# usage: crontab on linux
# last modify: piner 2007-08-29 create
#环境变量
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#运行变量
RMAN=$ORACLE_HOME/bin/rman
SQLPLUS=$ORACLE_HOME/bin/sqlplus
TEE=/usr/bin/tee
LOGFILE=/home/oracle/logs/rmanback.log
#目录结构为类似这样：/back1/rmanbak/test/week1
DBDEST1=/back1/rmanbak/${ORACLE_SID}/week`date +%w`
DBDEST2=/back2/rmanbak/${ORACLE_SID}/week`date +%w`
CTLFILE=/back1/rmanbak/${ORACLE_SID}/week`date +%w`/${ORACLE_SID}_ctl_`date +%Y%m%d%H%M`.ctl
#开始运行RMAN来备份数据库与归档日志，并记录备份的日志文件
echo "----------------backup start----------------------" > $LOGFILE
$RMAN <<EOF | $TEE -a $LOGFILE
connect target
#分配多通道备份，只备份数据库
Run{
allocate channel c1 type disk format '$DBDEST1/%U_%s.bak' maxpiecesize = 2000M;
allocate channel c2 type disk format '$DBDEST2/%U_%s.bak' maxpiecesize = 2000M;
backup database filesperset = 4;
}
#保留两次有效备份
delete noprompt obsolete redundancy = 2 device type disk;
exit;
EOF
#备份控制文件
$SQLPLUS "/ as sysdba" <<EOF | $TEE -a $LOGFILE
alter database backup controlfile to '$CTLFILE';
exit
EOF
echo "-----------------backup end-----------------------" >> $LOGFILE

P356

RMAN> allocate channel c1 device type sbt
2> parms='env=(nsr_server=tape_srv,
3> nsr_group=Oracle_tapes)';
相关的数据库与归档日志备份脚本如下：
#全备份脚本
backup tag 'full'
database filesperset = 4
plus archivelog delete input filesperset = 10;
#零级备份脚本
backup incremental level 0 tag 'db0'
database filesperset = 4
database skip readonly
plus archivelog delete input filesperset = 10;
#一级备份脚本
backup incremental level 1 tag 'db1'
database filesperset = 4
database skip readonly
plus archivelog delete input filesperset = 10;

P359

RMAN> connect target sys/password@prod #源数据库
RMAN>connect auxiliary sys/password@aux #辅助数据库
connected to target database: TEST (DBID=290922305)
connected to auxiliary database: TEST (not mounted)
RMAN>RUN{
allocate auxiliary channel c1 type disk;
recover tablespace 'USERS' until time "to_date('2007-10-22 15:48:17' , 'yyyy-mm-dd
hh24:mi:ss')" AUXILIARY DESTINATION '/u01/oracle/new/';
sql 'alter tablespace USERS online';
}

P365

RMAN>duplicate target database
to dupdb
password file
spfile
from active database
db_file_name_convert '/u01/Oracle/oradata/','/u02/Oracle/oradata/'
parameter_value_convert '/u01/oracle/pfile/','/u02/Oracle/oradata/'
set sga_max_size '300M'
set sga_target '250M'
set log_file_name_convert '/u01/oracle/redo/','/u02/oracle/redo/';
run{
set newname for datefile 1 to '/u01/oradatd1/system01.dbf';
set newname for datefile 2 to '/u01/oradata2/undotbs01.dbf';
…….
duplicate ……
}
duplicate
……
parameter_values_convert '/u01/oracle/pfile/', '+DISK1'
set db_create_file_dest '+DISK1';

第10章

P376

SQL> begin
2 dbms_streams_adm.maintain_schemas(
3 schema_names => 'piner',
4 source_directory_object => null,
5 destination_directory_object => null,
6 source_database => 'sour.net',
7 destination_database => 'dest.net',
8 perform_actions => true,
9 bi_directional => false,
10 include_ddl => true,
11 instantiation => dbms_streams_adm.instantiation_schema_network);
12 end;
13 /

P378

SQL>create table table_name as select * from table_name@db_link where 1=0;
SQL> alter table table_name
2 add constraint constraint_name primary key [unique] (field_name);
SQL>create materialized view table_name on prebuilt table refresh fast as
2 select * from table_name@db_link;
SQL>exec dbms_mview.refresh('table_name',method => 'Complete');--全刷新
SQL>exec dbms_mview.refresh('table_name');--快速刷新
SQL>create index index_name on table_name(field_name);
SQL>declare jobid number;
SQL>begin
2 sys.dbms_job.submit(job => jobid,
3 what => 'dbms_mview.refresh(''table_name'');',
4 next_date => sysdate,
5 interval => 'sysdate+1/1440');
6 commit;
7 end;
8/

P379

create table rep_table_logs
(
Rep_key number not null,
Dml_type varchar2(1) not null,
Dml_time date default sysdate not null,
Rep_flag number default 0 not null
)

P380

create or replace trigger tr_table_repl
after insert or update or delete
on table_name
for each row
/**********************************************************
*create date: *
*creator:piner *
*function description: *
* *
*modify history: *
***********************************************************/
declare
m_dml_type varchar2(1);
m_rep_id number;
errormsg varchar2(500);
begin
if inserting then
m_dml_type := 'I';
m_rep_id := :new.id;
elsif updating then
if :new.id != :old.id then
raise_application_error(-20001,'You can not modify primary key');
end if;
m_dml_type := 'U';
m_rep_id := :old.id;
elsif deleting then
m_dml_type := 'D';
m_rep_id := :old.id;
else
m_dml_type := 'X';
m_rep_id := -1;
end if;
insert into rep_table_logs(rep_key,dml_type,dml_time)
values(m_rep_id,m_dml_type,sysdate);
exception
when others then
raise;
end tr_table_repl;

P381

create or replace procedure sp_table_repl
is
--复制参数
m_rep_flag number := 1;
m_exc_flag number := 2;
m_sp_name varchar2(10) := 'rep_table';
--过程参数
end_stamp date;
num_row number;
errormsg varchar2(256);
pages_nums number;
per_pages number := 10000;
begin
/***************************************************************************
*create date: *
*creator: piner *
*function: *
* *
*modify history: *
* *
*注意，该过程加入了并发控制，保证不能被并发运行，但是，该过程在运行的过程中*
*不能被异常kill，如果被kill了，需要手工修改该标志从runing到stop。 *
****************************************************************************/
--处理标志，为了保证过程不被并发执行
update rep_table_flag f set f.run_flag='runing',f.sp_time=sysdate
where f.run_flag='stop' and f.sp_type = m_sp_name;
num_row := sql%rowcount;
commit;
--更新到0行，表示有过程正在执行，该进程返回，不再执行
if num_row = 0 then
return;
end if;
---------------------------------------------------------------------
----------------------start------------------------------------------
--未处理日志数
select count(*) into num_row
from rep_table_logs_syn t
where t.rep_flag = 0;
pages_nums := trunc(num_row/per_pages) + 1;
--分页处理。每次处理一个per_pages的数目
for pages in 1 .. pages_nums loop
select max(dml_time) into end_stamp from (
select dml_time from rep_table_logs_syn
where rep_flag = 0
order by dml_time)
where rownum <= per_pages;
--把这部分正在处理的日志，标记置为m_exc_flag，表示正在处理
update rep_table_logs_syn set rep_flag = m_exc_flag
where rep_flag = 0
and dml_time < end_stamp;
commit;
-----------------------------------------------------------------
-------------------------delete----------------------------------
--处理delete的记录
delete /*+ ordered use_nl(l) */ from rep_table u where id in
(select rep_key from rep_table_logs_syn l
where l.dml_type = 'D'
and l.rep_flag = m_exc_flag);
--更新处理过的标志位，注意，这里包含了insert,update,delete的所有日志
--因为如果该记录有删除日志存在，它的insert update是没有必要做的
update rep_table_logs_syn t set t.rep_flag = t.rep_flag + m_rep_flag
where t.rep_key in
(select rep_key from rep_table_logs_syn l
where l.dml_type = 'D'
and l.rep_flag = m_exc_flag);
commit;
---------------------------------------------------------------------
-------------------------insert--------------------------------------
--处理insert的记录，查看要插入的日志在目标表中是否已经存在
--一般情况下应当是不存在的，除了开始取数据可能存在的合并区
select /*+ ordered use_nl(l,u) */ count(*) into num_row
from rep_table_logs_syn l,
rep_table u
where l.rep_key = u.id
and l.dml_type = 'I'
and l.rep_flag = m_exc_flag;
--如果发现要更新的记录存在于目标表中，则删除该记录
if num_row > 0 then
delete from rep_table where id in
(select /*+ ordered use_nl(l,u) */ l.rep_key
from rep_table_logs_syn l,
rep_table u
where l.rep_key = u.id
and l.dml_type = 'I'
and l.rep_flag = m_exc_flag);
end if;
--把需要同步的数据插入到目标表中
insert into rep_table
select /*+ ordered use_nl(l,u) */ u.*
from rep_table_logs_syn l,
syn_rep_table_db1 u
where l.rep_key = u.id
and l.dml_type = 'I'
and l.rep_flag = m_exc_flag;
--更新insert的标志位
update rep_table_logs_syn l set rep_flag = rep_flag + m_rep_flag
where dml_type = 'I'
and rep_flag = m_exc_flag;
commit;
---------------------------------------------------------------------
-------------------------update--------------------------------------
--处理update的记录
--处理重复记录，其实多个update只需要同步一次即可
update rep_table_logs_syn set rep_flag = rep_flag + m_rep_flag
where rowid in
(select rid from (
select rowid rid,row_number() over(partition by rep_key
order by rowid) rn
from rep_table_logs_syn
where rep_flag = m_exc_flag
and dml_type = 'U')
where rn >= 2);
commit;
--更新，先插入到临时表
insert into rep_table_temp
select /*+ ordered use_nl(l,u) */ u.*
from rep_table_logs_syn l,
syn_rep_table_db1 u
where l.rep_key = u.id
and l.dml_type = 'U'
and l.rep_flag = m_exc_flag;
--从临时表更新数据
update /*+ ordered use_nl(u) */ rep_table t
set (field1,field2...fieldn)
= (select * from rep_table_temp u
where u.id=t.id)
where exists
(select null from rep_table_temp tt
where t.id = tt.id);
--更新update的标志位
update rep_table_logs_syn l set rep_flag = rep_flag + m_rep_flag
where dml_type = 'U'
and rep_flag = m_exc_flag;
commit;
----------------------当前页全部更新完毕----------------------------
--更新时间戳
update rep_table_flag f set f.sp_time=sysdate
where f.sp_type = m_sp_name;
commit;
------------------------------------------------------------------
-------------------------end loop---------------------------------
end loop;
--删除处理完的日志
delete from rep_table_logs_syn where rep_flag = m_rep_flag + m_exc_flag;
--做完标志
update rep_table_flag f set f.run_flag='stop',f.sp_time=sysdate
where f.sp_type = m_sp_name;
commit;
exception
when others then
--如果失败，则回滚
rollback;
--提交错误信息
errormsg := sqlerrm;
--做完标志
update rep_table_flag f set f.run_flag='stop',f.sp_time=sysdate
where f.sp_type = m_sp_name;
insert into rep_table_sp_errors(error_id,error_msg,error_time)
values(m_sp_name,errormsg,sysdate);
commit;
raise;
end sp_table_repl;

P385

#!/usr/bin/perl
###########################################################################
#creator: #
#create date: #
#function: #
# #
#modify history: #
# #
###########################################################################
use strict;
use warnings;
use DBI;
#$OUTPUT_AUTOFLUSH = false
$|=1;
if (my $pid = open(CHILD,"-|")) {
#parent process
#environment variable for oracle nls_lang
$ENV{"NLS_LANG"} = 'AMERICAN_AMERICA.US7ASCII';
#这里连接远程数据库
#user/pass@dbname
my $dbname1="test";
my $user1="test";
my $passwd1="test";
my $count=0;
#connect oracle database
my $dbh1 = DBI->connect("dbi:Oracle:$dbname1",$user1,$passwd1) or die ("can't connect
to oracle database ",$DBI::errstr);
#这里采用了进程间通信的方式，这里是父进程
#父进程把从子进程读到的信息，按照"::"作为分界符的方法，切换成多个字段的记录
#这个分界符是可以自定义的
#read child process information
while (<CHILD>){
my @newrec = split(/::/,"$_");
chomp(@newrec);
my $sqls = "insert into test(id,name) values('$newrec[0]','$newrec[1]')";
my $rows = $dbh1 -> do($sqls);
$count += $rows;
}
close(CHILD) or die("Cannot close:$!");
$dbh1->disconnect();
print "inert rows:$count.ok\n";
}
elsif(defined($pid)){
#child process
#这里是子进程，可以拥有跟父进程不一样的字符集环境变量
#environment variable for oracle nls_lang
$ENV{"NLS_LANG"} = 'AMERICAN_AMERICA.ZHS16GBK';
#这里连接字符集不同的另外一个数据库
#user/pass@dbname
my $dbname2="";
my $user2="test";
my $passwd2="test";
#connect oracle database
my $dbh2 = DBI->connect("dbi:Oracle:$dbname2",$user2,$passwd2) or die ("can't connect
to oracle database ",$DBI::errstr);
#query sql
my $sth=$dbh2->prepare("select * from test");
#execute sql
my $rc=$sth->execute;
my @recs;
#get row data and send to parent process
#把记录拼合在一起，用"::"来分隔记录
#然后把分隔好的记录传递给父进程
while(@recs = $sth -> fetchrow_array()){
print $recs[0]."::".$recs[1]."\n";
}
$dbh2->disconnect();
$sth->finish();
#exit child process
exit();
}
else{
print "could not fork process\n";
}

P386

### $dbh1->{AutoCommit} = 0;
### $dbh1->{RaiseError} = 1;
### #data
### my @col1;
### my @col2;
### #read child process information
### while (<CHILD>){
### my @newrec = split(/::/,"$_");
### chomp(@newrec);
### $col1[$count] = $newrec[0];
### $col2[$count] = $newrec[1];
### $count++;
### }
###
### my (@rowstats, $rv);
### my $sth1 = $dbh1->prepare(
### "insert into test(TNAME,TABTYPE) values (?, ?)");
### $sth1->bind_param_array(1, \@col1);
### $sth1->bind_param_array(2, \@col2);
### $rv = $sth1->execute_array(
### {ArrayTupleStatus => \@rowstats});
###
### close(CHILD) or die("Cannot close:$!");
### $dbh1->disconnect();
### print "inert rows:$rv,ok\n";

P391

RMAN>convert datafile
2> '/home/ora10g/data_user01.dbf'
3> to platform='AIX-Based Systems (64-bit)'
4> from platfrom='Linux IA (32-bit)'
5> db_file_name_convert='/home/ora10g/','/u01/oracle/oradata/test/';

P394

$ more test.ctl
Load data
insert
into table test
Fields terminated by "," Optionally enclosed by "'"
(id,
name,
SQL>create_date date "yyyy-mm-dd hh24:mi:ss" nullif(create_date="NULL"))
$ sqlldr userid=piner/xxxxxx control=test.ctl data=test.dat

P397

Piner@9iR2>create table ext_test
2 (employee_id NUMBER(4),
3 name VARCHAR2(20),
4 job_id VARCHAR2(10),
5 hire_date DATE,
6 salary NUMBER(8,2),
7 email VARCHAR2(25)
8 )
9 organization external
10 (
11 type oracle_loader
12 default directory dump_dir
13 access parameters
14 (
15 records delimited by newline
16 badfile dump_dir:'test%a_%p.bad'
17 logfile dump_dir:'test%a_%p.log'
18 fields terminated by ',' optionally enclosed by '"'
19 missing field values are null
20 ( employee_id,name, job_id,
21 hire_date char date_format date mask "yyyy-mm-dd",
22 salary, email
23 )
24 )
25 location ('test1.dat', 'test2.dat')
26 )
27 parallel
28 reject limit unlimited;
Table created.
Piner@10gR2>create table ext_test
organization external
(
type oracle_datapump
default directory dump_dir
location ('test.dmp')
)
as
select * from test;
Piner@10gR2>create table ext_test
(field1 number,
field2 varchar2(100)
)
organization external
(type oracle_datapump
default directory dump_dir
location('test.dmp'));

第11章

P413

SELECT D.TABLESPACE_NAME,SPACE SUM_SPACE,
SPACE-NVL(FREE_SPACE,0) USED_SPACE,
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) USED_RATE,
FREE_SPACE FREE_SPACE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

第12章

P430

$more statpack.sh
#!/bin/ksh
# creator: piner
# function:produce statpack snapshot
date
#设置环境变量
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#收集快照点
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect perfstat/perfstat
exec statspack.snap
exit
EOF
$more spreport.sh
#!/bin/ksh
# creator: piner
# function:get statpack report
date
#设置环境变量
export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/9.2
export NLS_LANG=american_america.zhs16gbk
#设置运行时变量
SQLPLUS=$ORACLE_HOME/bin/sqlplus
LOGFILE=/home/oracle/logs/spreport.txt #日志文件
REPFILE=/home/oracle/worksh/spreport.lst #报表文件
#获得最后2个快照点
$SQLPLUS -S perfstat/perfstat <<EOF
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 1000
set trimspool on
spool $LOGFILE
select snap_id from
(select snap_id from stats\$snapshot where instance_number=1
order by snap_time desc) where rownum<3;
spool off;
set echo on
set feedback on
set heading on
exit
EOF
line1=`tail -1 $LOGFILE`
line2=`head -1 $LOGFILE`
echo "line1 is"$line1
echo "line2 is"$line2
#产生报表
rm $REPFILE
$SQLPLUS -S perfstat/perfstat <<EOF
define begin_snap=$line1
define end_snap=$line2
define report_name=$REPFILE
@?/rdbms/admin/spreport.sql
exit
EOF
#发送邮件
mail -s `date +%Y%m%d`'-'`hostname`spreport test@mail.com < $REPFILE

P451

SQL> select to_char(snap_time,'yyyy-mm-dd hh24') time,
2 round(sum(read/1024)) "write(kb)",round(sum(write/1024)) "read(kb)" from
3 (select sn.snap_time snap_time,(newr.value-oldr.value) read,
4 (neww.value-oldw.value) write
5 from stats$sysstat newr,
6 stats$sysstat oldr,
7 stats$sysstat neww,
8 stats$sysstat oldw,
9 stats$snapshot sn
10 where sn.snap_id=newr.snap_id
11 and sn.snap_id=neww.snap_id
12 and sn.snap_id-1=oldr.snap_id
13 and sn.snap_id-1=oldw.snap_id
14 and newr.name='physical reads'
15 and oldr.name='physical reads'
16 and neww.name='physical writes'
17 and oldw.name='physical writes'
18 ) v
19 group by to_char(snap_time,'yyyy-mm-dd hh24');

第13章

P467

select * from (
select rownum rn,object_id,object_name,subobject_name,status from
(select object_id,object_name,subobject_name,status
from test
where owner='SYS'
and object_type='TABLE'
order by created desc)
where rownum <= m) t
where rn >=n
Piner@10g R2>create index ind_test on test(owner,object_type,created);
Index created.
Piner@10g R2>select * from (
2 select rownum rn,object_id,object_name,subobject_name,status from
3 (select object_id,object_name,subobject_name,status
4 from test
5 where owner='SYS'
6 and object_type='TABLE'
7 order by created desc)
8 where rownum <= 50) t
9 where rn >=1;

P468

Piner@10g R2>select /*+ ordered use_nl(t,test) */
2 object_id,object_name,subobject_name,status
3 from (select rid from (
4 select rownum rn,rid from (
5 select rowid rid from test
6 where owner='SYS'
7 and object_type='TABLE'
8 order by created desc)
9 where rownum <= 500)
10 where rn >=451) t,
11 test
12 where t.rid=test.rowid;

P471

SQL> create or replace type numtabletype as table of number;
SQL>create or replace type vartabletype as table of varchar2(1000);
SQL>create or replace function str2numList( p_string in varchar2 ) return numTableType
as
v_str long default p_string || ',';
v_n number;
v_data numTableType := numTableType();
begin
loop
v_n := to_number(instr( v_str, ',' ));
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
SQL>create or replace function str2varList( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || ',';
v_n varchar2(2000);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, ',' );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
Select /*+ ordered use_nl(a,u) */ id, name,nick
from table(STR2NUMLIST(:bind0)) a,
users u
where u.id = a.column_value;
Select /*+ leading(a) */ id, name,nick
from users u
where id in
(select * from table(STR2NUMLIST(:bind0)) a);
Select /*+ index(u ind_users_id) */ id, name,nick
from users u
where id in
(select * from the (select cast(STR2NUMLIST(:bind0) AS NUMTABLETYPE)
from dual) where rownum<1000);

P477

declare
resource_busy exception;
pragma exception_init(resource_busy, -54);
begin
loop
begin
execute immediate 'alter index ...... noparallel';
exit;
exception
when resource_busy then
dbms_lock.sleep(1);
end;
end loop;
end;

第14章

P488

Piner@Ora9R2> create or replace procedure sp_test is
num number;
begin
runstats_pgk.rs_start;
for i in 1..1000 loop
execute immediate 'select b from test where a=:x' using i;
end loop;
runstats_pgk.rs_middle;
for i in 1..1000 loop
execute immediate 'select b from test where a='||i;
end loop;
runstats_pgk.rs_stop(100);
end sp_test;
/

P490

SQL 10G>col OBJECT_NAME format a20;
SQL 10G>col USER_NAME format a20;
SQL 10G>select /*+ rule */ lpad('--',decode(b.BLOCK,1,0,4))||s.username user_name,
2 b.TYPE,o.owner||'.'||o.object_name object_name,
3 s.sid,s.serial#,decode(b.REQUEST,0,'BLOCKED','WAITING') status
4 FROM dba_objects o,v$session s,v$lock v,v$lock b
5 WHERE v.ID1=o.object_id AND v.SID=s.sid
6 and v.SID=b.SID and (b.BLOCK=1 or b.REQUEST>0)
7 and v.TYPE='TM'
8 order by by b.ID2,v.ID1,User_name desc;

P492

SQL 10G> select sql_text
2 from v$sqlarea
3 where (v$sqlarea.address, v$sqlarea.hash_value) in (
4 select sql_address, sql_hash_value
5 from v$session
6 where sid in (
7 select sid
8 from v$session a, x$kglpn b
9 where a.saddr = b.kglpnuse
10 and b.kglpnmod <> 0
11 and b.kglpnhdl IN (select p1raw
12 from v$session_wait
13* where sid=160 and event like 'library%')))

P509

one session other session
------------------------------------ -----------------------------------
T1> SQL>insert into test values()
1 row inserted
T2> SQL>insert into test values()
1 row inserted
T3> SQL> update mystat set user_nums =
(select count(*) from test
where username=?)
where id=?;
1 row updated
T4> SQL> update mystat set user_nums =
(select count(*) from test
where username=?)
where id=?;
1 row updated
T5>SQL> commit;
Commit complete
T6> SQL> commit;
Commit complete
其中时间T1<T2<T3<T5<T5<T6

第15章

P516

declare
row_num number := 0;
begin
for c_test in (select id from my_test t where ftype is null) loop
update my_test t set t.ftype = 1 where id = c_test.id;
row_num := row_num + 1;
--1000条提交一次，可根据需要修改
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;/

P517

update my_users u set user_cnt=
(select user_cnt from
(select user_id,count(*) user_cnt from my_test group by user_id) tmp
where tmp.user_id=u.id)
where exists
(select null from my_test t where u.id=t.user_id);
SQL>select tablespace_name,
round(sum(bytes)/(1024*1024),2) free_space
from dba_free_space
where tablespace_name='TBS_UNDO'
group by tablespace_name;
declare
row_num number := 0;
begin
for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop
--更新目标表
update my_users t set t.user_cnt = c_user.cnt where id = c_user.user_id;
--更新临时表的标记位
update tmp_test f set f.flags = 1 where user_id = c_user.user_id;
row_num := row_num + 1;
--1000条提交一次
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;

P518

declare
row_num number := 0;
begin
for c_user in (select user_id,user_cnt from tmp_test t
where flags = 0 and rownum <= 50000) loop
update my_users t set t.user_cnt = c_user.cnt where id = c_user.user_id;
update tmp_test f set f.flags = 1 where user_id = c_user.user_id;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;
begin
for i in 1.. 5 loop
dbms_lock.sleep(300);
--这里嵌入更新数据的循环。
end loop;
end;

P520

declare
row_num number := 0;
begin
for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop
--更新备份表，备份需要更新的数据
update tmp_test t set t. user_old_cnt =
(select user_cnt from my_users s where s.id=t.user_id)
where t.user_id = c_user.user_id;
--再更新业务表
update my_users t set t.user_cnt = c_user.cnt where id = c_user.user_id;
update tmp_test t set t.flags = 1 where user_id = c_user.user_id;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;

P523

declare
row_num number := 0;
begin
for c_test in (select rowed rid from table_name t where field_name is null) loop
update table_name t set t.field_name = 0 where rowid = c_test.rid;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;

第16章

P548

$ more send_dbmail.sh
#!/bin/sh
# creator: Piner
# function:
# usage:send mail
# last modify:
# modifier history:
#这里表示，至少需要有符合条件的三个参数，否则，会提示该脚本的用法
if [ $# -ne 3 ]; then
echo "Usage: `basename $0`(title file group1|group2)"
exit 1
fi
#分组，把需要发送的对象分成不同的组，如组1的人员可能是主机管理者
#组2的人员可能是DB管理者
group1_mail="test1@gmail.com test2@gmail.com"
group2_mail="test2@gmail.com test3@gmail.com test4@gmail.com"
TITLE=$1
MFILE=$2
#send mail procedure
case $3 in
group1)echo "send mail to dba-list"
mail -s "$TITLE" $group1_mail < $MFILE
;;
group2)echo "send mail to dw"
mail -s "$TITLE" $group2_mail < $MFILE
;;
*)echo "Usage: `basename $0`(title file group1|group2)"
;;
esac
#send end
echo "send mail end"

P549

$ more send_dbmobile.sh
#!/bin/sh
# creator: piner
# function:
# usage:send message to mobile
# last modify:
# modifier :
#这里表示至少需要两个参数，信息内容与分组信息
if [ $# -ne 2 ]; then
echo "Usage: `basename $0`(message group1|group2)"
exit 1
fi
#列出每个人的手机号码，仅仅是存在这个脚本中，
#如果有人更换了号码，只需要修改这里即可
user1=138xxxxxxxx
user2=139xxxxxxxx
user3=137xxxxxxxx
user4=130xxxxxxxx
#写一个函数，来包装这个发送过程，这里假定短信网关是一个单独的服务器，
#IP地址是192.168.1.1，服务端口为8080，则可以用wget来调用这个发送短信的接口
#该函数仅仅是在这个脚本中被调用。
sendmobile() {
SURL='sms/index.php?bid=TEST&receiver='
SURL="${SURL}$1"
message=`echo $2 | sed -e 's/ /%20/g'`
SURL="${SURL}&message=$message"
wget -O /dev/null -o /dev/null "http://192.168.1.1:8080/$SURL" > /dev/null 2>&1
echo "succed to mobile"
}
#如果发现接收到的消息是空，则不发送，无意义，并返回失败
if [ -z "$1" ]
then
echo "message is null!"
exit 1
fi
mesglog="$1"
#这里开始发送手机短信，也是采用分组的方式
#把需要发送到的目标按要求分成不同的组，
#到时候，如目标是主机，则可以发送短信到主机组，数据库则可以发送短信到DB组。
#send procedure
case $2 in
group1)echo "send message to group1"
sendmobile "$user1" "$mesglog"
sendmobile "$user2" "$mesglog"
;;
group2)echo "send message to group2"
sendmobile "$user3" "$mesglog"
sendmobile "$user4" "$mesglog"
;;
*)echo "Usage: `basename $0`(message group1|group2)"
;;
esac
#send end
echo "send message to mobile end"

P550

$ more monitor_api.sh
#!/bin/sh
# creator: piner
# function:
# usage:call monitor's interface
# last modify:
# modifier :
#判断参数是否合适
#如果是发送短信，需要传递信息内容与发送组
#如果是发送邮件，需要传递标题，与文件名称与发送组
#如果仅仅是拷贝文件，需要提供源文件名称与目标文件名称
if [ $# -ge 1 ]; then
if [ $1 == 'mobile' ]; then
echo "send mobile message"
if [ $# -ne 3 ]
echo "Usage: `basename $0`(mobile message group)"
exit 1
fi
elif [ $1 == 'mail' ]; then
echo "send mail message"
if [ $# -ne 4 ]
echo "Usage: `basename $0`(mail title group filename)"
exit 1
fi
elif [ $1 == 'scp' ]; then
echo "only scp file to monitor"
if [ $# -ne 3 ]; then
echo "Usage: `basename $0`(scp source_file dest_file)"
exit 1
fi
else
echo "Usage: `basename $0`(mobile message group)"
echo "or Usage: `basename $0`(mail title group filename)"
echo "or Usage: `basename $0`(scp source_file dest_file)"
exit 1
fi
fi
#定义monitor上的脚本位置
SENDMSG= /home/admin/bin/send_dbmobile.sh
SENDDBM= /home/admin/bin/send_dbmail.sh
#选择方式，如果是发送短信
if [ $1 == 'mobile' ]; then
message=$2
group=$3
ssh admin@monitor "$SENDMSG '$message' '$group'"
fi
#选择方式，如果是发送邮件
#注意这里的文件是在monitor上的，是全路径名称
if [ $1 == 'mail' ]; then
title=$2
group=$3
mfile=$5
ssh admin@monitor "$SENDDBM '$title' '$mfile' '$group'"
fi
#选择方式，如果是拷贝文件，注意这里的文件是全路径名称
if [ $1 == 'scp' ]; then
sfile=$2
dfile=$3
scp $sfile admin@monitor:$dfile
fi

P555

$more server_info.sh
#!/bin/ksh
# creator: piner
# function: copy system load and alert log file to monitor
# usage: crontab on host
# last modify:
#get host tag and Oracle sid
dbname=`hostname`
ORACLE_SID=$1
ORACLE_BASE=/u01/Oracle
ALERT=$ORACLE_BASE/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log
#这里简单地指定monitor接口程序的位置，至于这个接口成本，见本章前