===========================================================
@@
===========================================================
@表示调用文件
@@表示相对的,一般都要求主文件的同一目录.
是脚本嵌套调用其他的脚本
===========================================================
MTS 的配置
===========================================================
oracle9i
服务器设置:
spfile参数:
*.dispatchers='(protocol=TCP)(disp=5)(con=400)(sess=100)'
*.max_dispatchers=20
*.max_shared_servers=50
*.mts_circuits=170
*.mts_max_dispatchers=20
*.mts_max_servers=50
*.mts_servers=1
*.mts_service=sid
*.mts_sessions=165
参数说明:
dispatchers=(protocol=TCP)(disp=5)(con=400)(sess=100)
为设置使用共享服务器的共享环境而设置调度程序的数量和类型。可以为该参数指定几个选项。有关详细信息,
请参阅“Oracle8i 管理员指南”和“Oracle Net Administrator's
Guide”。这是字符串值的一个示例: ''(PROTOCOL=TCP)(DISPATCHERS=3)''。
值范围: 参数的有效指定值。
max_dispatchers=20
指定在一个共享服务器环境中可同时运行的调度程序进程的最大数量。
值范围: 根据操作系统而定。
默认值 : 如果已配置了调度程序, 则默认值为大于 5 的任何数目或配置的调度程序的数目
max_shared_servers=50
说明 : 指定在一个共享服务器环境中可同时运行的共享服务器进程的最大数量。
值范围: 根据操作系统而定。
默认值 : 20
mts_circuits=170
mts_max_dispatchers=20
说明 : 指定在一个共享服务器环境中可同时运行的调度程序进程的最大数量。
值范围: 根据操作系统而定。
默认值 : 如果已配置了调度程序, 则默认值为大于 5 的任何数目或配置的调度程序的数目
mts_max_servers=50
说明 : 指定在一个共享服务器环境中可同时运行的共享服务器进程的最大数量。
值范围: 根据操作系统而定。
默认值 : 20
mts_servers=1
说明 : 指定在启动例程后, 要为共享服务器环境创建的服务器进程的数量。
值范围: 根据操作系统而定。
默认值 : 1
mts_service=sid
说明 : 一个共享服务器参数, 用于指定已在调度程序上注册,
用来建立数据库连接的唯一服务名。如果要在没有调度程序的情况下仍能连接到数据库, 请将该值设置为与例程名相同。此参数自
8.1.3 版起已废弃。
值范围: 根据操作系统而定。
默认值 :0
保证客户端能用主机名访问到服务器就可以了。
因为dispatcher会把服务器地址发给客户端，客户端再连接，而这个地址是主机名，不是IP。
监听程序设置:(HOST要使用主机名,因为Oracle MTS需要主机名来进行通讯)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bd1ln9)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/app/product/9.2.0.4)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora9)
(ORACLE_HOME = /opt/oracle/app/product/9.2.0.4)
(SID_NAME = ora9)
)
)
客户端tns设置(HOST要使用主机名,因为Oracle MTS需要主机名来进行通讯,使用默认或者共享的方式进行连接,不要使用专用模式)
222 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bd1ln9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora9)
)
)
客户端操作系统设置:
保证客户端能用主机名访问到服务器就可以了。
因为dispatcher会把服务器地址发给客户端，客户端再连接，而这个地址是主机名，不是IP。
就是因为不能通过主机名称访问服务器，
在客户机器的Hosts文件中加入服务器IP和服务器名称的对照关系就可以连接共享进程了。
如:
C:WINDOWSsystem32driversetchosts
127.0.0.1 localhost
10.10.3.222 bd1ln9
10.10.3.225 sxit-bd1
如何判断连接方式是MTS
LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "ora9" has 2 instance(s).
Instance "ora9", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0
LOCAL SERVER
Instance "ora9", status READY, has 6 handler(s) for this service...
Handler(s):
"D004" established:0 refused:0 current:0 max:100 state:ready
DISPATCHER <machine: bd1ln9, pid: 4422>
(ADDRESS=(PROTOCOL=tcp)(HOST=bd1ln9)(PORT=49279))
"D003" established:1 refused:0 current:1 max:100 state:ready
DISPATCHER <machine: bd1ln9, pid: 4420>
(ADDRESS=(PROTOCOL=tcp)(HOST=bd1ln9)(PORT=49278))
"D002" established:1 refused:0 current:1 max:100 state:ready
DISPATCHER <machine: bd1ln9, pid: 4418>
(ADDRESS=(PROTOCOL=tcp)(HOST=bd1ln9)(PORT=49277))
"D001" established:1 refused:0 current:1 max:100 state:ready
DISPATCHER <machine: bd1ln9, pid: 4416>
(ADDRESS=(PROTOCOL=tcp)(HOST=bd1ln9)(PORT=49276))
"D000" established:1 refused:0 current:0 max:100 state:ready
DISPATCHER <machine: bd1ln9, pid: 4414>
(ADDRESS=(PROTOCOL=tcp)(HOST=bd1ln9)(PORT=49271))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
select * from v$shared_server;
select * from v$dispatcher;
select * from V$CIRCUIT;
如何从mts 转到Dedicated
9i
要转到Dedicated server只需要:(动态修改)
1.alter system set shared_servers =0 ;
2.alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=0)';
*.dispatchers='(protocol=TCP)(disp=0)(con=400)(sess=100)'
数据库都不用重启，等一会数据库等所有的shared server进程结束后
就全变成全是dedicated连接了,
要恢复shared server模式把 shared_server和dispatchers参数改回原来的值。
1.alter system set shared_servers =1 ;
2.alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=5)';
===========================================================
创建profile 限制session 对数据库的访问资源
===========================================================
创建profile 限制session 对数据库的访问资源
CREATE|ALTER PROFILE profilename
PASSWORD_LOCK_TIME 1/1440 --用户尝试密码被锁定后,多少天解除
FAILED_LOGIN_ATTEMPS 3 --用户尝试密码多少次错误后被锁定
PASSWORD_LIFE_TIME 4 --用户密码必须4天改一次
PASSWORD_GRACE_TIME 2 --用户密码到期后可以有多少天缓冲
PASSWORD_REUSE_TIME 10 --用户密码多少天内不能重复
PASSWORD_REUSE_MAX 3 --用户密码重复使用最多次数,与上个参数互斥
PASSWORD_VERIFY_FUNCTION fuctionname --用户密码设置时的校验函数,系统默认的用以在SYS用户下运行utlpwdmg.sql产生,此时用户口令至少四位,且至少要有一个字母,一个数字,一个特殊符号
CPU_PER_SESSION 100 --1/100秒
CPU_PER_call 100 --1/100秒
SESSION_PER_USER 20 --每个用户最多可以有多少个会话
CONNECT_TIME 50 --用户最多可以一次连接多少分钟
IDLE_TIME 10 --用户发呆多少分钟后自动断开连接
LOGICAL_READS_PER_SESSION 120 --每个用户会话最多可读120块
LOGICAL_READS_PER_CALL 200 --每个用户调用最多可读120块
PRIVATE_SGA 2000; --每个用户最多使用多少KB SGA
ALTER USER username PROFILE profilename; --将某用户归入某PROFILE管理口令与资源使用;
DROP PROFILE profilename [CASCADE]; --当某用户所在PROFILE被删除后,会转为DEFAULT PROFILE来管理如:
ALTER PROFILE "PROFILE1" LIMIT SESSIONS_PER_USER 300; --个连接
ALTER PROFILE "PROFILE1" LIMIT IDLE_TIME 25; --单位:分钟也可以通过9i图形化管理工具配置以上参数;特别注意: 需要将参数resource_limit设置为TRUE方可;
这个书针对整个数据库的资源限制，可以把这个资源限制profile 赋给某个用户用来用来限制特定的用户的资源
可能是这样设置的:
alter system set resource_limit = true;
create profile pro_test limit sessions_per_user 50;
alter user tom profile pro_test;
首先設置resource_limit=true;
然后創建一個profile使SESSIONS_PER_USER設定為你要限制的數量﹐然后使user的profile為你創建的profile就可以了。用oem比較好操作
===========================================================
监控Oracle数据库的常用shell脚本（三）
===========================================================
分析表和索引（以得到更好的性能）
以下我将展示如果传送参数到一个脚本中：
| #################################################################### ## analyze_table.sh ## #################################################################### #!/bin/ksh # input parameter: 1: password # 2: SID if (($#<1)) then echo "Please enter 'oracle' user password as the first parameter !" exit 0 fi if (($#<2)) then echo "Please enter instance name as the second parameter!" exit 0 fi |
要传入参数以执行该脚本，输入：
| $ analyze_table.sh manager oradb1 |
脚本的第一部分产生了一个analyze.sql文件，里面包含了分析表用的语句。脚本的第二部分分析全部的表：
| ##################################################################### ## analyze_table.sh ## ##################################################################### sqlplus -s < oracle/$1@$2 set heading off set feed off set pagesize 200 set linesize 100 spool analyze_table.sql select 'ANALYZE TABLE ' || owner || '.' || segment_name || ' ESTIMATE STATISTICS SAMPLE 10 PERCENT;' from dba_segments where segment_type = 'TABLE' and owner not in ('SYS', 'SYSTEM'); spool off exit ! sqlplus -s < oracle/$1@$2 @./analyze_table.sql exit ! |
以下是analyze.sql的一个例子：
| $ cat analyze.sql ANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE PATROL.P$LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT; |
检查表空间的使用
以下的脚本检测表空间的使用。如果表空间只剩下10%，它将会发送一个警告email。
| ##################################################################### ## ck_tbsp.sh ## ##################################################################### #!/bin/ksh sqlplus -s < oracle/$1@$2 set feed off set linesize 100 set pagesize 200 spool tablespace.alert SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10; spool off exit ! if [ `cat tablespace.alert|wc -l` -gt 0 ] then cat tablespace.alert -l tablespace.alert > tablespace.tmp mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp fi |
警告email输出的例子如下：
| TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE ------------------- --------- ----------- ------------------- ------------------ SYSTEM 2,047 203 2,250 9 % STBS01 302 25 327 8 % STBS02 241 11 252 4 % STBS03 233 19 252 8 % |
查找出无效的数据库对象
以下查找出无效的数据库对象：
| ##################################################################### ## invalid_object_alert.sh ## ##################################################################### #!/bin/ksh . /etc/oracle.profile sqlplus -s < oracle/$1@$2 set feed off set heading off column object_name format a30 spool invalid_object.alert SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME; spool off exit ! if [ `cat invalid_object.alert|wc -l` -gt 0 ] then mailx -s "INVALID OBJECTS for ${2}" $DBALIST < invalid_object.alert fi$ cat invalid_object.alert OWNER OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------------------------------------- HTOMEH DBMS_SHARED_POOL PACKAGE BODY INVALID HTOMEH X_$KCBFWAIT VIEW INVALID IMON IW_MON PACKAGE INVALID IMON IW_MON PACKAGE BODY INVALID IMON IW_ARCHIVED_LOG VIEW INVALID IMON IW_FILESTAT VIEW INVALID IMON IW_SQL_FULL_TEXT VIEW INVALID IMON IW_SYSTEM_EVENT1 VIEW INVALID IMON IW_SYSTEM_EVENT_CAT VIEW INVALIDLBAILEY CHECK_TABLESPACE_USAGE PROCEDURE INVALID PATROL P$AUTO_EXTEND_TBSP VIEW INVALID SYS DBMS_CRYPTO_TOOLKIT PACKAGE INVALID SYS DBMS_CRYPTO_TOOLKIT PACKAGE BODY INVALID SYS UPGRADE_SYSTEM_TYPES_TO_816 PROCEDURE INVALID SYS AQ$_DEQUEUE_HISTORY_T TYPE INVALID SYS HS_CLASS_CAPS VIEW INVALID SYS HS_CLASS_DD VIEW INVALID |
监视用户和事务（死锁等）
以下的脚本在死锁发生的时候发送一个警告e-mail：
| ################################################################### ## deadlock_alert.sh ## ################################################################### #!/bin/ksh .. /etc/oracle.profile sqlplus -s < oracle/$1@$2 set feed off set heading off spool deadlock.alert SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER, DECODE(REQUEST, 0, 'NO','YES' ) WAITER FROM V$LOCK WHERE REQUEST > 0 OR BLOCK > 0 ORDER BY block DESC; spool off exit ! if [ `cat deadlock.alert|wc -l` -gt 0 ] then mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alert fi |
结论
| 0,20,40 7-17 * * 1-5 /dba/scripts/ckinstance.sh > /dev/null 2>&1 0,20,40 7-17 * * 1-5 /dba/scripts/cklsnr.sh > /dev/null 2>&1 0,20,40 7-17 * * 1-5 /dba/scripts/ckalertlog.sh > /dev/null 2>&1 30 * * * 0-6 /dba/scripts/clean_arch.sh > /dev/null 2>&1 * 5 * * 1,3 /dba/scripts/analyze_table.sh > /dev/null 2>&1 * 5 * * 0-6 /dba/scripts/ck_tbsp.sh > /dev/null 2>&1 * 5 * * 0-6 /dba/scripts/invalid_object_alert.sh > /dev/null 2>&1 0,20,40 7-17 * * 1-5 /dba/scripts/deadlock_alert.sh > /dev/null 2>&1 |
通过以上的脚本，可大大减轻你的工作。你可以使用这些是来做更重要的工作，例如性能调整。
===========================================================
监控Oracle数据库的常用shell脚本（二）
===========================================================
http://www.bc-cn.net/Article/sjk/oracle/200601/3479_2.html
监控数据库的常用Shell脚本
以下提供的8个shell脚本覆盖了DBA每日监控工作的90%，你可能还需要修改UNIX的环境变量。
检查Oracle实例的可用性
oratab文件中列出了服务器上的所有数据库
| $ cat /var/opt/oracle/oratab ################################################################### ## /var/opt/oracle/oratab ## ################################################################### oradb1:/u01/app/oracle/product/8.1.7:Y oradb2:/u01/app/oracle/product/8.1.7:Y oradb3:/u01/app/oracle/product/8.1.7:N oradb4:/u01/app/oracle/product/8.1.7:Y |
以下的脚本检查oratab文件中列出的所有数据库，并且找出该数据库的状态（启动还是关闭）
| ################################################################### ## ckinstance.ksh ## ################################################################### ORATAB=/var/opt/oracle/oratab echo "`date` " echo "Oracle Database(s) Status `hostname` :n" db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "#" | grep -v "*"` pslist="`ps -ef | grep pmon`" for i in $db ; do echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>$1 if (( $? )); then echo "Oracle Instance - $i: Down" else echo "Oracle Instance - $i: Up" fi done |
使用以下的命令来确认该脚本是可以执行的：
| $ chmod 744 ckinstance.ksh $ ls -l ckinstance.ksh -rwxr--r-- 1 oracle dba 657 Mar 5 22:59 ckinstance.ksh* |
以下是实例可用性的报表：
| $ ckinstance.ksh Mon Mar 4 10:44:12 PST 2002 Oracle Database(s) Status for DBHOST server:Oracle Instance - oradb1: UpOracle Instance - oradb2: UpOracle Instance - oradb3: DownOracle Instance - oradb4: Up |
检查Oracle监听器的可用性
以下有一个类似的脚本检查Oracle监听器。如果监听器停了，该脚本将会重新启动监听器：
| ####################################################################### ## cklsnr.sh ## ####################################################################### #!/bin/ksh DBALIST="primary.dba@company.com,another.dba@company.com";export DBALIST cd /var/opt/oracle rm -f lsnr.exist ps -ef | grep mylsnr | grep -v grep > lsnr.exist if [ -s lsnr.exist ] then echo else echo "Alert" | mailx -s "Listener 'mylsnr' on `hostname` is down" $DBALIST TNS_ADMIN=/var/opt/oracle; export TNS_ADMIN ORACLE_SID=db1; export ORACLE_SID ORAENV_ASK=NO; export ORAENV_ASK PATH=$PATH:/bin:/usr/local/bin; export PATH . oraenv LD_LIBRARY_PATH=${ORACLE_HOME}/lib;export LD_LIBRARY_PATH lsnrctl start mylsnr fi |
检查Alert日志（ORA-XXXXX）
每个脚本所使用的一些环境变量可以放到一个profile中：
| ####################################################################### ## oracle.profile ## ####################################################################### EDITOR=vi;export EDITOR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/8.1.7; export ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN NLS_LANG=american; export NLS_LANG NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT ORATAB=/var/opt/oracle/oratab;export ORATAB PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/ sbin:/usr/openwin/bin:/opt/bin:.; export PATH DBALIST="primary.dba@company.com,another.dba@company.com";export DBALIST |
以下的脚本首先调用oracle.profile来设置全部的环境变量。如果发现任何的Oracle错误，该脚本还会给DBA发送一个警告的email。
| #################################################################### ## ckalertlog.sh ## #################################################################### #!/bin/ksh .. /etc/oracle.profile for SID in `cat $ORACLE_HOME/sidlist` do cd $ORACLE_BASE/admin/$SID/bdump if [ -f alert_${SID}.log ] then mv alert_${SID}.log alert_work.log touch alert_${SID}.log cat alert_work.log >> alert_${SID}.hist grep ORA- alert_work.log > alert.err fi if [ `cat alert.err|wc -l` -gt 0 ] then mailx -s "${SID} ORACLE ALERT ERRORS" $DBALIST < alert.err fi rm -f alert.err rm -f alert_work.log done |
清除旧的归档文件
以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件：
| $ df -k | grep arch Filesystem kbytes used avail capacity Mounted on /dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive ####################################################################### ## clean_arch.ksh ## ####################################################################### #!/bin/ksh df -k | grep arch > dfk.result archive_filesystem=`awk -F" " '{ print $6 }' dfk.result` archive_capacity=`awk -F" " '{ print $5 }' dfk.result` if [[ $archive_capacity > 90% ]] then echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled" # try one of the following option depend on your need find $archive_filesystem -type f -mtime +2 -exec rm -r {} ; tar rman fi |
===========================================================
监控Oracle数据库的常用shell脚本（一）
===========================================================
前言http://www.bc-cn.net/Article/sjk/oracle/200601/3479.html 这篇文章介绍了DBA每天在监控Oracle数据库方面的职责，讲述了如何通过shell脚本来完成这些重复的监控工作。本文首先回顾了一些DBA常用的Unix命令，以及解释了如何通过Unix Cron来定时执行DBA脚本。同时文章还介绍了8个重要的脚本来监控Oracle数据库：
检查实例的可用性
检查监听器的可用性
检查alert日志文件中的错误信息
在存放log文件的地方满以前清空旧的log文件
分析table和index以获得更好的性能
检查表空间的使用情况
找出无效的对象
监控用户和事务
DBA需要的Unix基本知识
基本的UNIX命令
以下是一些常用的Unix命令：
ps--显示进程
grep--搜索文件中的某种文本模式
mailx--读取或者发送mail
cat--连接文件或者显示它们
cut--选择显示的列
awk--模式匹配语言
df--显示剩余的磁盘空间
以下是DBA如何使用这些命令的一些例子：
显示服务器上的可用实例：
显示服务器上的可用监听器：
查看Oracle存档目录的文件系统使用情况
统计alter.log文件中的行数：
列出alert.log文件中的全部Oracle错误信息：
CRONTAB基本
一个crontab文件中包含有六个字段：
分钟 0-59
小时 0-23
月中的第几天 1-31
月份 1 - 12
星期几 0 - 6, with 0 = Sunday
Unix命令或者Shell脚本
要编辑一个crontab文件，输入：
Crontab -e
要查看一个crontab文件，输入：
检查实例的可用性
检查监听器的可用性
检查alert日志文件中的错误信息
在存放log文件的地方满以前清空旧的log文件
分析table和index以获得更好的性能
检查表空间的使用情况
找出无效的对象
监控用户和事务
DBA需要的Unix基本知识
基本的UNIX命令
以下是一些常用的Unix命令：
ps--显示进程
grep--搜索文件中的某种文本模式
mailx--读取或者发送mail
cat--连接文件或者显示它们
cut--选择显示的列
awk--模式匹配语言
df--显示剩余的磁盘空间
以下是DBA如何使用这些命令的一些例子：
显示服务器上的可用实例：
| $ ps -ef | grep smon oracle 21832 1 0 Feb 24 ? 19:05 ora_smon_oradb1 oracle 898 1 0 Feb 15 ? 0:00 ora_smon_oradb2 dliu 25199 19038 0 10:48:57 pts/6 0:00 grep smon oracle 27798 1 0 05:43:54 ? 0:00 ora_smon_oradb3 oracle 28781 1 0 Mar 03 ? 0:01 ora_smon_oradb4、 |
显示服务器上的可用监听器：
| $ ps -ef | grep listener | grep -v grep （译者注：grep命令应该加上-i参数，即grep -i listener，该参数的作用是忽略大小写，因为有些时候listener是大写的，这时就会看不到结果） oracle 23879 1 0 Feb 24 ? 33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit oracle 27939 1 0 05:44:02 ? 0:00 /8.1.7/bin/tnslsnr listener_db2 -inherit oracle 23536 1 0 Feb 12 ? 4:19 /8.1.7/bin/tnslsnr listener_db3 -inherit oracle 28891 1 0 Mar 03 ? 0:01 /8.1.7/bin/tnslsnr listener_db4 -inherit |
查看Oracle存档目录的文件系统使用情况
| $ df -k | grep oraarch /dev/vx/dsk/proddg/oraarch 71123968 4754872 65850768 7% /u09/oraarch |
统计alter.log文件中的行数：
| $ cat alert.log | wc -l 2984 |
列出alert.log文件中的全部Oracle错误信息：
| $ grep ORA- alert.log ORA-00600: internal error code, arguments: [kcrrrfswda.1], [], [], [], [], [] ORA-00600: internal error code, arguments: [1881], [25860496], [25857716], [] |
CRONTAB基本
一个crontab文件中包含有六个字段：
分钟 0-59
小时 0-23
月中的第几天 1-31
月份 1 - 12
星期几 0 - 6, with 0 = Sunday
Unix命令或者Shell脚本
要编辑一个crontab文件，输入：
Crontab -e
要查看一个crontab文件，输入：
| Crontab -l 0 4 * * 5 /dba/admin/analyze_table.ksh 30 3 * * 3,6 /dba/admin/hotbackup.ksh /dev/null 2>&1 |
在上面的例子中，第一行显示了一个分析表的脚本在每个星期5的4：00am运行。第二行显示了一个执行热备份的脚本在每个周三和周六的3：00a.m.运行。
===========================================================
oracle 名词解释
===========================================================
Load Testing Monitors
Oracle
http://www.mercury.com/us/products/performance-center/loadrunner/monitors/oracle.html
CPU used by this session - This is the amount of CPU time (in 10s of milliseconds) used by a session between when a user call started and ended. Some user calls can complete within 10 milliseconds and as a result, the start and end user-call time can be the same. In this case, 0 milliseconds are added to the statistic. A similar problem can exist in the reporting by the operating system, especially on systems that suffer from many context switches.
CPU used when call started - The CPU time used when the call is started.
CR blocks created - A buffer in the buffer cache was cloned. The most common reason for cloning is that the buffer is held in an incompatible mode.
Cached Commit SCN referenced - The number of times cached Commit SCN is referenced.
Commit SCN cached - The number of times Commit SCN is cached.
Current blocks converted for CR - A CURRENT buffer (shared or exclusive) is made CR before it can be used.
DBWR Flush object call found no dirty buffers - The DBWR did not find any dirty buffers for an object that was flushed from the cache.
DBWR Flush object cross instance calls - The number of times DBWR received a flush by object number cross instance call (from a remote instance). This includes both checkpoint and invalidate object.
DBWR buffers scanned - The total number of buffers looked at when scanning each LRU set for dirty buffers to clean. This count includes both dirty and clean buffers. Divide by DBWR LRU scans to find the average number of buffers scanned.
DBWR checkpoint buffers written - The number of buffers that were written for checkpoints.
DBWR checkpoints - Number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint.
DBWR cross instance writes - The total number of blocks written for other instances so that they can access the buffers.
DBWR free buffers found - The number of buffers that DBWR found clean when it was requested to make free buffers. Divide by DBWR make free requests to find the average number of reusable buffers at the end of each LRU.
DBWR lru scans - The number of times that DBWR does a scan of the LRU queue looking for buffers to write. This includes times when the scan is to fill a batch being written for another purpose, such as a checkpoint. This statistic is always greater than or equal to DBWR make free requests.
DBWR make free requests - Number of messages received requesting DBWR to make some more free buffers for the LRU.
DBWR revisited being-written buffer - The number of times that DBWR tried to save a buffer for writing and found that it was already in the write batch. This statistic is a measure of the amount of "useless" work that DBWR had to do in trying to fill the batch. This can occur because many sources contribute to a write batch. If the same buffer from different sources is considered for adding to the write batch, then all but the first attempt will be "useless" since the buffer is already marked as being written.
DBWR skip hot writes - The number of times DBWR skipped writing "hot" buffers.
DBWR summed scan depth - The current scan depth (number of buffers examined by DBWR) is added to this statistic every time DBWR scans the LRU for dirty buffers. Divide by DBWR lru scans to find the average scan depth.
DBWR timeouts - The number of times that the DBWR has been idle since the last timeout. These are the times that the DBWR looked for buffers to idle write.
DBWR undo block writes - The number of transaction table blocks written by DBWR. It is an indication of how many "hot" buffers were written, leading to write complete waits.
DDL statements parallelized - The number of DDL statements that were parallelized.
DML statements parallelized - The number of DML statements that were parallelized.
PX local messages received - The number of local messages received for Parallel Executions.
PX local messages sent - The number of local messages sent for Parallel Executions.
PX remote messages received - The number of remote messages received for Parallel Executions.
PX remote messages sent - The number of remote messages sent for Parallel Executions.
SQL*Net roundtrips to/from client - Total number of Net8 messages sent to and received from the client.
SQL*Net roundtrips to/from dblink - Total number of Net8 messages sent over and received from a database link.
Unnecessary process cleanup for SCN batching - The total number of times that the process cleanup was performed unnecessarily because the session/process did not get the next batched SCN. The next batched SCN went to another session instead.
Background checkpoints completed - The number of checkpoints completed by the background. This statistic is incremented when the background successfully advances the thread checkpoint.
Background checkpoints started - The number of checkpoints started by the background. It can be larger than the number completed if a new checkpoint overrides an incomplete checkpoint. This only includes checkpoints of the thread, not individual file checkpoints for operations such as offline or begin backup. This statistic does not include the checkpoints performed in the foreground, such as ALTER SYSTEM CHECKPOINT LOCAL.
Bytes received via SQL*Net from client - The total number of bytes received from the client over Net8.
Bytes received via SQL*Net from dblink - The total number of bytes received from a database link over Net8.
Bytes sent via SQL*Net to client - The total number of bytes sent to the client from the foreground process(es).
Bytes sent via SQL*Net to dblink - The total number of bytes sent over a database link.
Calls to get snapshot SCN: kcmgss - The number of times a snap System Change Number (SCN) was allocated. The SCN is allocated at the start of a transaction.
Change write time - The elapsed time for redo write for changes made to CURRENT blocks in 10s of milliseconds.
Cleanouts and rollbacks - consistent read gets - The number of times CR gets require both block rollbacks, and block cleanouts.
Cleanouts only - consistent read gets - The number of times CR gets require only block cleanouts, no rollbacks.
Cluster key scan block gets - The number of blocks obtained in a cluster scan.
Cluster key scans - The number of cluster scans that were started.
Commit cleanout failures: block lost - The number of times a cleanout at commit was attempted and could not find the correct block due to forced write, replacement, or switch CURRENT.
Commit cleanout failures: buffer being written - The number of times a cleanout at commit was attempted but the buffer was currently being written.
Commit cleanout failures: callback failure - The number of times the cleanout callback function returns FALSE.
Commit cleanout failures: cannot pin - The total number of times a commit cleanout was performed but failed because the block could not be pinned.
Commit cleanout failures: hot backup in progress - The number of times cleanout at commit was attempted during hot backup. The image of the block needs to be logged before the buffer can be made dirty.
Commit cleanout failures: write disabled - The number of times that a cleanout at commit time was performed but the writes to the database had been temporarily disabled.
Commit cleanouts - The total number of times the cleanout block at commit time function was performed.
Commit cleanouts successfully completed - The number of times the cleanout block at commit time function successfully completed.
Consistent changes - The number of times a database block has applied rollback entries to perform a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources.
Consistent gets - The number of times a consistent read was requested for a block. See also consistent changes above.
Cross instance CR read - The number of times this instance made a cross-instance call to write a particular block due to timeout on an instance lock get. The call allowed the block to be read CR rather than CURRENT.
Data blocks consistent reads - undo records applied - The number of undo records applied to CR rollback data blocks.
DB block changes - Closely related to consistent changes, this statistic counts the total number of changes that were made to all blocks in the SGA that were part of an update or delete operation. These are changes that are generating redo log entries and hence will be permanent changes to the database if the transaction is committed. This statistic is a rough indication of total database work. This statistic indicates (possibly on a per-transaction level) the rate at which buffers are being dirtied.
DB block gets - This statistic tracks the number of blocks obtained in CURRENT mode.
Deferred (CURRENT) block cleanout applications - The number of times cleanout records are deferred, piggyback with changes, always current get.
Dirty buffers inspected - The number of dirty buffers found by the foreground while the foreground is looking for a buffer to reuse.
Enqueue conversions - The total number of enqueue converts.
Enqueue deadlocks - The total number of enqueue deadlocks between different sessions.
Enqueue releases - The total number of enqueue releases.
Enqueue requests - The total number of enqueue gets.
Enqueue timeouts - The total number of enqueue operations (get and convert) that timed out before they could complete.
Enqueue waits - The total number of waits that happened during an enqueue convert or get because the enqueue could not be granted right away.
Exchange deadlocks - The number of times that a process detected a potential deadlock when exchanging two buffers and raised an internal, restartable error. Index scans are currently the only operations, which perform exchanges.
Execute count - The total number of calls (user and recursive) that execute SQL statements.
Free buffer inspected - The number of buffers skipped over from the end of an LRU queue in order to find a reusable buffer. The difference between this statistic and dirty buffers inspected is the number of buffers that could not be used because they were busy, needed to be written after rapid aging out, or they have a user, a waiter, or are being read/written. For more information, see "dirty buffers inspected."
Free buffer requested - The count of the number of times a reusable buffer or a free buffer was requested to create or load a block.
Global cache defers - The number of times a ping request was deferred until later.
Global cache freelist waits - The number of pings for free lock elements (when all releasable locks are in use).
Global lock async converts - The total number of asynchronous global lock converts.
Global lock async gets - The total number of asynchronous global lock gets.
Global lock convert time - The total elapsed time of all synchronous (non-asynchronous) global lock converts in 10s of milliseconds.
Global lock get time - The total elapsed time of all synchronous (non-asynchronous) global lock gets in 10s of milliseconds.
Global lock releases - The total number of synchronous global lock releases.
Global lock sync converts - The total number of synchronous global lock converts.
Global lock sync gets - The total number of synchronous global lock gets.
Immediate (CR) block cleanout applications - The number of times cleanout records are applied immediately during CR gets.
Immediate (CURRENT) block cleanout applications - The number of times cleanout records are applied immediately during current gets.
Kcmccs called get current SCN - The number of times the kernel got the CURRENT SCN when there was a need to casually confirm the SCN.
Kcmgss read SCN without going to DLM - The number of times the kernel casually confirmed the SCN without going to the LM.
Kcmgss waited for batching - The number of times the kernel waited on a snapshot SCN.
Logons cumulative - The total number of logons since the instance started. This statistic is useful only in V$SYSSTAT. It gives an instance overview of all processes that logged on.
Logons current - The total number of current logons. This statistic is useful only in V$SYSSTAT.
Next SCNs gotten without going to DLM - The number of SCNs (System Change Numbers) obtained without going to the DLM.
No work - consistent read gets - The number of times CR gets require no block cleanouts nor rollbacks.
Opened cursors cumulative - The total number of opened cursors since the instance has started (in V$SYSSTAT). In V$SESSTAT, this statistic shows the total number of cursors opened since the start of the session.
Opened cursors current - The total number of current open cursors.
Opens of replaced files - The total number of files that needed to be reopened because they were no longer in the process file cache.
Opens requiring cache replacement - The total number of file opens that caused a current file to be closed in the process file cache.
Parse count (hard) - The total number of parse calls (real parses). A hard parse means allocating a workheap and other memory structures, and then building a parse tree. A hard parse is a very expensive operation in terms of memory use.
Parse count (total) - Total number of parse calls (hard and soft). A soft parse is a check to make sure that the permissions on the underlying object have not changed.
Parse time CPU - The total CPU time used for parsing (hard and soft) in 10s of milliseconds.
Parse time elapsed - The total elapsed time for parsing in 10s of milliseconds. By subtracting parse time CPU from this statistic, the total waiting time for parse resources is determined. For more information, see parse time CPU above.
Physical reads - The total number of data blocks read from disk. This equals the number of physical reads direct plus all reads into buffer cache.
Physical writes - The total number of data blocks written to disk. This equals the number of "physical writes direct" plus all writes from buffer cache.
Queries parallelized - The number of SELECT statements that got parallelized.
Recovery array read time - The elapsed time of I/O while doing recovery.
Recovery array reads - The number of reads performed during recovery.
Recovery blocks read - The number of blocks read during recovery.
Recursive calls - Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates a SQL statement. These internal SQL statements generate recursive calls.
Recursive CPU usage - The total CPU time used by non-user calls (recursive calls). Subtract this value from CPU used by this session to determine how much CPU time was used by the user calls.
Redo entries - This statistic increments each time redo entries are copied into the redo log buffer.
Redo entries linearized -The total number of entries of size <= REDO_ENTRY_PREBUILD_THRESHOLD. Building these entries increases CPU time, but may also increase concurren
===========================================================
修改数据库名和sid的工具
===========================================================
C:Documents and SettingsAdministrator>nid
DBNEWID: Release 10.2.0.1.0 - Production on Thu May 25 15:50:27 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Keyword Description (Default)
----------------------------------------------------