Category Archives: Oracle

【RMAN】I/O Read Rate of Backups

如何控制RMAN备份时的I/O速度呢?
这是在客户生产环境中遇到的现实问题,因为客户的存储实在是弱爆了,RMAN备份的时候由于I/O较高,导致应用出现停顿,严重时存储直接挂掉。所以考虑限制RMAN备份时的速度。
在Oracle的Channel Control中提示可以通过两种哦国内方式控制I/O,单位为KB/s,MB/s,GB/s
Set limits on I/O bandwidth consumption in kilobytes, megabytes, or gigabytes (ALLOCATE CHANNEL … RATE, CONFIGURE CHANNEL … RATE)
下面是Oracle对Rate设置的案例介绍
I/O Read Rate of Backups
By default, RMAN uses all available I/O bandwidth to read/write to disk. You can limit the I/O resources consumed by a backup job with the RATE option of the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. The RATE option specifies the maximum number of bytes for each second that RMAN reads on the channel.
For example, you can configure automatic channels to limit each channel to read 1 MB a second:
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CHANNEL DEVICE TYPE sbt RATE 1M;

根据Oracle给出的提示,我们在11.2.0.2的库上分别测试I/O限制为5M,20M及不限制IO时RMAN的备份速度,借助osw观察I/O变化情况。
备份的数据文件为system01.dbf,大小约为700MB。

-bash-3.2$ ls -lh system01.dbf
 -rw-r----- 1 oracle dba 701M Nov 21 15:30 system01.dbf

操作系统的块大小为4096(4KB),数据库的块大小为8192(8KB)

[[email protected] ~]# getconf PAGESIZE
 4096
 SQL> show parameter db_block_size
NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_block_size integer 8192

开启OSW(OS Watcher),收集信息时间间隔为10秒,保留10天(240小时)。

[[email protected] osw]# ./startOSW.sh 10 240 >> osw.log &
 [1] 16589
 [[email protected] osw]#
 [1]+ Done ./startOSW.sh 10 240 >> osw.log
 [[email protected] osw]#
 [[email protected] osw]# cat osw.log
Testing for discovery of OS Utilities...
VMSTAT found on your system.
 IOSTAT found on your system.
 MPSTAT found on your system.
 NETSTAT found on your system.
 TOP found on your system.
Discovery completed.
Starting OSWatcher v3.0.1 on Mon Nov 21 15:56:22 CST 2011
 With SnapshotInterval = 10
 With ArchiveInterval = 240
OSWatcher - Written by Carl Davis, Center of Expertise, Oracle Corporation
Starting Data Collection...
osw heartbeat:Mon Nov 21 15:56:22 CST 2011

1.I/O限制为5M,备份时间为2分25秒

RMAN> configure CHANNEL DEVICE TYPE DISK RATE 5M;
new RMAN configuration parameters:
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 5 M;
 new RMAN configuration parameters are successfully stored
RMAN> show CHANNEL;
RMAN configuration parameters for database with db_unique_name ORCL are:
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 5 M;
RMAN> backup datafile '/oracle/oradata/orcl/system01.dbf';
Starting backup at 21-NOV-11
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=143 device type=DISK
 channel ORA_DISK_1: starting full datafile backup set
 channel ORA_DISK_1: specifying datafile(s) in backup set
 input datafile file number=00001 name=/oracle/oradata/orcl/system01.dbf
 channel ORA_DISK_1: starting piece 1 at 21-NOV-11
 channel ORA_DISK_1: finished piece 1 at 21-NOV-11
 piece handle=/oracle/fast_recovery_area/ORCL/backupset/2011_11_21/o1_mf_nnndf_TAG20111121T155646_7dn11yc3_.bkp tag=TAG20111121T155646 comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
 channel ORA_DISK_1: throttle time: 0:02:14
 Finished backup at 21-NOV-11
Starting Control File and SPFILE Autobackup at 21-NOV-11
 piece handle=/oracle/fast_recovery_area/ORCL/autobackup/2011_11_21/o1_mf_s_767807951_7dn16hq2_.bkp comment=NONE
 Finished Control File and SPFILE Autobackup at 21-NOV-11

2.I/O限制为20M,备份时间为45秒

RMAN> configure CHANNEL DEVICE TYPE DISK RATE 20M;
old RMAN configuration parameters:
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 5 M;
 new RMAN configuration parameters:
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 20 M;
 new RMAN configuration parameters are successfully stored
 released channel: ORA_DISK_1
RMAN> show CHANNEL;
RMAN configuration parameters for database with db_unique_name ORCL are:
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 20 M;
RMAN> backup datafile '/oracle/oradata/orcl/system01.dbf';
Starting backup at 21-NOV-11
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=143 device type=DISK
 channel ORA_DISK_1: starting full datafile backup set
 channel ORA_DISK_1: specifying datafile(s) in backup set
 input datafile file number=00001 name=/oracle/oradata/orcl/system01.dbf
 channel ORA_DISK_1: starting piece 1 at 21-NOV-11
 channel ORA_DISK_1: finished piece 1 at 21-NOV-11
 piece handle=/oracle/fast_recovery_area/ORCL/backupset/2011_11_21/o1_mf_nnndf_TAG20111121T160009_7dn1896h_.bkp tag=TAG20111121T160009 comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
 channel ORA_DISK_1: throttle time: 0:00:30
 Finished backup at 21-NOV-11
Starting Control File and SPFILE Autobackup at 21-NOV-11
 piece handle=/oracle/fast_recovery_area/ORCL/autobackup/2011_11_21/o1_mf_s_767808054_7dn19ptr_.bkp comment=NONE
 Finished Control File and SPFILE Autobackup at 21-NOV-11

3.RMAN I/O无限制,备份时间为15秒

 RMAN> configure CHANNEL DEVICE TYPE DISK clear;
old RMAN configuration parameters:
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 20 M;
 old RMAN configuration parameters are successfully deleted
 released channel: ORA_DISK_1
RMAN> show CHANNEL;
RMAN configuration parameters for database with db_unique_name ORCL are:
 RMAN configuration has no stored or default parameters
RMAN> backup datafile '/oracle/oradata/orcl/system01.dbf';
Starting backup at 21-NOV-11
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=143 device type=DISK
 channel ORA_DISK_1: starting full datafile backup set
 channel ORA_DISK_1: specifying datafile(s) in backup set
 input datafile file number=00001 name=/oracle/oradata/orcl/system01.dbf
 channel ORA_DISK_1: starting piece 1 at 21-NOV-11
 channel ORA_DISK_1: finished piece 1 at 21-NOV-11
 piece handle=/oracle/fast_recovery_area/ORCL/backupset/2011_11_21/o1_mf_nnndf_TAG20111121T160152_7dn1cjf8_.bkp tag=TAG20111121T160152 comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
 Finished backup at 21-NOV-11
Starting Control File and SPFILE Autobackup at 21-NOV-11
 piece handle=/oracle/fast_recovery_area/ORCL/autobackup/2011_11_21/o1_mf_s_767808127_7dn1czqj_.bkp comment=NONE
 Finished Control File and SPFILE Autobackup at 21-NOV-11

根据数据文件大小列出预计的RMAN备份时间与实际RMAN备份时间:
IO test chat

可以通过OSW收集的数据,很明显的看到percent busy(PB), reads per second(RPS) and writes per second(WPS)变化
percent busy(PB)
OSWg sda OS IO PB
reads per second(RPS)
OSWg sda OS IO RPS
writes per second(WPS)
OSWg sda OS IO WPS

从OSW生成的图片的整体趋势上,已经能看到设置RATE参数对磁盘I/O的影响。
但是可能是由于一些暂时未知的原因,RPS的趋势并非如我们所愿,限制为10M后仍有个别时段物理读超出限制。
今天看到一款可以监控程序IO的软件,改天重新进行一下测试。

总结:
Oracle 11gR2在做RMAN备份时,可以通过设置RATE参数限制磁盘IO。
设置方式如下:
configure CHANNEL DEVICE TYPE DISK RATE 20M;
重置方式如下:
configure CHANNEL DEVICE TYPE DISK clear;
单位可以设置为KB,MB,GB

参考:

Oracle® Database Backup and Recovery Advanced User’s Guide 10g Release 1 (10.1) Part Number B10734-01
计算机中KB与Kb的技术差别详解
RMAN备份数据文件和控制文件
利用RATE参数限制RMAN备份的IO速度
Unix/Linux 磁盘 I/O 性能监控命令

EXP-00091 Exporting questionable statistics

导出数据库基表时遇到EXP-00091错误,虽然据说不影响导出结果,但看起来还是很让人不爽:

[[[email protected] HCscripts]$ exp "'/ as sysdba'" tables = col$,icol$,file$,ind$,indpart$,obj$,partcol$,partlob$,partobj$,seg$,seq$,tab$,tabpart$,ts$,user$,attrcol$,view$ file=basetable.dmp log=basetable.log 

Export: Release 11.2.0.2.0 - Production on Wed Nov 16 15:53:40 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 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 AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           COL$      61078 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                          ICOL$       3101 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                          FILE$          8 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           IND$       1560 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       INDPART$        572 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           OBJ$      15233 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       PARTCOL$        200 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       PARTLOB$          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                       PARTOBJ$        149 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           SEG$       3720 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           SEQ$        147 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           TAB$       1261 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       TABPART$        475 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            TS$         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                          USER$         46 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       ATTRCOL$        665 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                          VIEW$       3833 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

这个问题是由于exp工具所在的环境变量中的NLS_LANG与DB中的NLS_CHARACTERSET不一致导致的。
首先查看DB中的NLS_CHARCTERSET:

[SQL> col parameter for a20
SQL> col value for a30
SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER	     VALUE
-------------------- ------------------------------
NLS_CHARACTERSET     AL32UTF8

设置exp的环境变量NLS_LANG为对应值

[[[email protected] HCscripts]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[[email protected] HCscripts]$ env | grep NLS
NLS_LANG=AMERICAN_AMERICA.AL32UTF8

再次执行导出

[[[email protected] HCscripts]$ exp "'/ as sysdba'" tables = col$,icol$,file$,ind$,indpart$,obj$,partcol$,partlob$,partobj$,seg$,seq$,tab$,tabpart$,ts$,user$,attrcol$,view$ file=basetable_new.dmp log=basetable_new.log 

Export: Release 11.2.0.2.0 - Production on Wed Nov 16 16:09:56 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           COL$      61078 rows exported
. . exporting table                          ICOL$       3101 rows exported
. . exporting table                          FILE$          8 rows exported
. . exporting table                           IND$       1560 rows exported
. . exporting table                       INDPART$        572 rows exported
. . exporting table                           OBJ$      15233 rows exported
. . exporting table                       PARTCOL$        200 rows exported
. . exporting table                       PARTLOB$          1 rows exported
. . exporting table                       PARTOBJ$        149 rows exported
. . exporting table                           SEG$       3720 rows exported
. . exporting table                           SEQ$        147 rows exported
. . exporting table                           TAB$       1261 rows exported
. . exporting table                       TABPART$        475 rows exported
. . exporting table                            TS$         14 rows exported
. . exporting table                          USER$         46 rows exported
. . exporting table                       ATTRCOL$        665 rows exported
. . exporting table                          VIEW$       3833 rows exported
Export terminated successfully without warnings.

总结:
字符集对于我来说是个比较头疼的问题,有空还得仔细研究下

参考资料:

http://www.eygle.com/digest/2009/04/exp00091_exporting_questionabl.html

http://xiexiejiao.cn/oracle/exp-00091-error.html

Oracle技术嘉年华闭幕

Oracle技术嘉年华胜利闭幕!
忙了一个多月的时间,尤其是会议之前的半个月连续工作无休,会议结束后,突然感觉神经无法放松了。
会议前半个月,每天50+邮件,其中一半为英文。
会议前一天,下午到会场,布置、安排、接待,忙碌到深夜,不进油盐。通宵未睡,回复各位嘉宾邮件、联系前台调换房间、准备会议当天文件,以防万一。
会议当天,6点制作出ACOUG 3D Timeline,7点进入会场确认各项事宜、9点正式开幕。全天未休未吃饭。晚上瘫倒。
第二天一切正常。
现在,每天10封邮件,睡不醒。
从始至终,了解到很多新的知识,各方面的经验也增加了不少。
流水账一般记录一下,我需要好好休息几天。

Use orabf to crack Oracle user’s password

看到PUB上一个帖子,询问Oracle用户密码忘记如何找回,结果8楼一个哥直接上了个破解工具,遂学习之。

可使用orabf工具破解,工具介绍:

Orabf is an extremely fast offline brute force/dictionary attack tool that can be used when the particular username and hash are known for an Oracle account.  Obviously the speed of the brute force attack slows down the longer the amount of characters that it is trying to brute force with but for short username/hash combinations it can be over a million tries per second.

Command Syntax

C:\orabf-v0.7.5>orabf [hash]:[username] [options]
Options:

-c     [num] complexity: a number in [1..6] or a filename
– read words from stdin
[file] read words from file
1 numbers
2 alpha
3 alphanum
4 standard oracle (alpha)(alpha,num,_,#,$)… (default)
5 entire keyspace (‘ ‘..’~’)
6 custom (charset read from first line of file: charset.orabf)
-m [num] max pwd len: must be in the interval [1..14] (default: 14)
-n [num] min pwd len: must be in the interval [1..14] (default: 1)
-r resume: tries to resume a previous session

测试:

read more »

Oracle 10g新特性: Temporary Tablespaces Group

10G的新特性:临时表空间组
Grouping temporary tablespaces within a single group enables a user to consume temporary space from multiple tablespaces. (将临时表空间打包成组后,用户就能同时使用多个表空间作为临时表空间)

临时表空间组的内容:
. A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces.(临时表空间组包含至少一个表空间,至多无上限)
. It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.(共享表空间的命名空间,也就是说,临时表空间组的名称不能与任何一个表空间名称相同)
. A temporary tablespace group name can appear where a tablespace name would appear when assigning a default temporary tablespace for the database or assigning a temporary tablespace for a user.(当为数据库指定临时表空间或为用户指定临时表空间时,可以使用临时表空间组的名称)
. It is not created explicitly. It gets created implicitly when the first temporary tablespace is assigned to it and it is dropped when the last temporary tablespace is removed from it.(临时表空间组无法显式的创建。当第一个临时表空间指定给它时则隐式创建,当最后一个临时表空间移除时则隐式的被删除)
. Any temporary tablespace can be
–> moved from a group to another (if the group does not exist, it gets created)
–> removed from a group
–> added to a group if standalone

read more »

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress