解决了两个错误：
ORA-02429: cannot drop index used for enforcement of unique/primary key
ORA-02297: cannot disable constraint (XXX_DJCM.PK_CONTAINER) - dependencies exist
开发的同事说有一索引已经无用，要求删除，那知道执行的时候出错：
SQL>drop index CONTAINER_I_CONTAINER_REF_NO_C;
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>ALTER TABLE CONTAINER MODIFY PRIMARY KEY DISABLE;
ORA-02297: cannot disable constraint (XXX_DJCM.PK_CONTAINER) - dependencies exist
SQL>select owner, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
from user_constraints
where table_name = 'CONTAINER';
SQL>alter table CONTAINER disable constraint PK_CONTAINER cascade;
SQL>ALTER TABLE CONTAINER MODIFY PRIMARY KEY DISABLE;
-- Drop indexes
SQL>drop index CONTAINER_I_CONTAINER_REF_NO_C;
SQL>ALTER TABLE CONTAINER MODIFY PRIMARY KEY ENABLE;
SQL>alter table CONTAINER ENABLE constraint PK_CONTAINER;
参考：
Blog Tags: oracle
最近生产系统的oracle出了几次性能突然下降的问题，天天老是执行那几个捉取、生成报告的命令实在麻烦，所以，参考网上的指导，修改成在自己的环境中可每天自动执行的脚本！
说明:从早上8点到晚上8点之间进行快照收集,晚上9点执行cron进程启动,产生报表的快照也限于当天收集的快照,将当天最小的snap_id与最大的snap_id放到两个文件中,在sheel中读出,并计算出一个报表名称,最后产生的报表通过sendmail发送到相关人员的邮箱,然后每天晚只需要收邮件就可以看到当天的报表啦。
运行环境：redhat as 4.5 64bit, aix 5.3，oracle 10.2.0.3 64bit
1, redhat as 4.5 64bit
[oracle@fsdb autoperf]$ cat auto_perf.sh
#!/bin/sh
source ~/.bash_profile
/u01/oracle/product/10.2.0/db_1/bin/sqlplus -s /nolog<<!
conn perfstat/perfstat
set head off
set timing off
spool /backup/autoperf/snap_begin.lst
select min(snap_id) snap_id
from stats\$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /backup/autoperf/snap_end.lst
select max(snap_id) snap_id
from stats\$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!
BEGIN_SNAP=`cat /backup/autoperf/snap_begin.lst | tail -n 2`
END_SNAP=`cat /backup/autoperf/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/backup/autoperf/sp`date +%m%d`_ac
# echo $REPORT_NAME
/u01/oracle/product/10.2.0/db_1/bin/sqlplus -s /nolog<<!
conn perfstat/perfstat
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!
# echo $REPORT_NAME".lst"
/u01/oracle/product/10.2.0/db_1/bin/sqlplus -s /nolog<<!
conn perfstat/perfstat
set echo off
delete from stats\$snapshot where snap_id <= $END_SNAP;
commit;
exit
!
# cat sp0918_ac.lst | mail admin@webmaster.com# crontab -l
# 0 21 * * * /backup/autoperf/auto_perf.sh >> /backup/autoperf/perf.lst 2>&1
!
2, AIX
$vi autoperf.ksh
#!/bin/ksh
. ~/.profile
/u01/oracle/product/bin/sqlplus -s /nolog<<!
conn perfstat/perfstat
set head off
set timing off
spool /u01/backup/snap_begin.lst
select min(snap_id) snap_id
from stats\$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /u01/backup/snap_end.lst
select max(snap_id) snap_id
from stats\$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!
BEGIN_SNAP=`cat /u01/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /u01/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/u01/backup/sp`date +%m%d`_ac
/u01/oracle/product/bin/sqlplus -s /nolog<<!
conn perfstat/perfstat
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!
/u01/oracle/product/bin/sqlplus -s /nolog<<!
conn perfstat/perfstat
set echo off
delete from stats\$snapshot where snap_id <= $END_SNAP;
commit;
exit
!
脚本不是很完善，如应该保留几天的备份，如检查oracle是否启动，如检查两个snpa_id 之间是否有停机等，留待以后完善！也可参考如下：
清除statspack所产生的snapshot script
本文转译自
http://www.jlcomp.demon.co.uk/ 下面的 script可以利用cron排程来自动执行清除超过保留数目的旧有snapshot数据，这个script不需要知道PERFSTAT此账号的密码就可执行，并已经经由Oracle8.1.7和9.2.0上测试过。
Blog Tags: linux
前几天，外地的一台服务器由于UPS故障，导致死机，重新开机后服务不正常，而我又刚巧不在公司，就电话告诉一个同事登录，重启服务，还告知其操作完要退出登录，那知道今天常规检查，还是发现他没有退出。只能考虑远程退出，有几个命令据说可以，如 pkill -U uid ,skill -KILL -v /dev/pts/* 等，但执行不大成功，后来，还是用 who -u 检查出相关的进程号，直接kill, 具体操作如下：
[root@dbzj ~]# who
root :0 Sep 5 11:40
root pts/1 Sep 5 11:40 (:0.0)
root pts/2 Sep 18 09:40 (192.168.1.222)
[root@dbzj ~]# w
09:52:53 up 12 days, 22:31, 3 users, load average: 0.00, 0.00, 0.00
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root :0 - 05Sep07 ?xdm? 15:35 0.12s /usr/bin/gnome-
root pts/1 :0.0 05Sep07 12days 0.00s 0.00s bash
root pts/2 192.168.1.222 09:40 0.00s 0.00s 0.00s w
[root@dbzj ~]# who -u
root :0 Sep 5 11:40 ? 5054
root pts/1 Sep 5 11:40 old 5208 (:0.0)
root pts/2 Sep 18 09:40 . 28524 (192.168.1.222)
[root@dbzj ~]# kill -9 5208
[root@dbzj ~]# who -u
root :0 Sep 5 11:40 ? 5054
root pts/2 Sep 18 09:40 . 28524 (192.168.1.222)
[root@dbzj ~]# kill -9 5054
[root@dbzj ~]# who
root pts/2 Sep 18 09:40 (192.168.1.222)
Blog Tags: linux
公司一台旧的IBM小型机，数据都已经迁移到新服务器，暂时空置无用，酝酿了一段时间，今天开始在上面安装新的操作系统AIX5.3。
安装过程比较顺利，就是有时候等的时间比较长，大概花了两个多小时，下周再对系统软件进行安装配置。
今天大概作了如下工作：
1, 升级微码
2, 安装系统,选择完全安装,选择磁盘,选择支持64为,cfs2文件格式
第一次按说明文档，按F5，进入不了光盘的安装界面，第二次启动按5才进去了；
3, mirror rootvg
安装步骤笔记如下：
检查系统是否支持64位
# bootinfo -y
64
# /usr/sbin/prtconf -c
CPU Type: 64-bit
当前系统使用32还是64位
# bootinfo -K
64
# ls -l /unix
lrwxrwxrwx 1 root system 21 Sep 08 2007 /unix -> /usr/lib/boot/unix_64
当前使用的版本
# oslevel
4.3.3.0
用lsfs来查看文件系统
# lsfs -q /usr
Name Nodename Mount Pt VFS Size Options Auto Accounting
/dev/hd2 -- /usr jfs2 2424832 -- yes no
(lv size: 2424832, fs size: 2424832, block size: 4096, sparse files: yes, inline log: no, inline log size: 0, EAformat: v1, Quota: no, DMAPI: no, VIX: no)
#
检查当前磁盘
# lsdev -Cc disk
hdisk0 Available 40-60-00-4,0 16 Bit LVD SCSI Disk Drive
hdisk2 Available 21-08-L SSA Logical Disk Drive
hdisk3 Available 21-08-L SSA Logical Disk Drive
hdisk1 Available 40-60-00-8,0 16 Bit LVD SCSI Disk Drive
检查当前vg
# lsvg -p rootvg
rootvg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk0 active 542 137 00..00..00..28..109
hdisk1 active 542 137 108..09..00..00..20
检查机器微码版本，并升级
升级前:
# lscfg -vp | grep alterable
ROM Level.(alterable).......M2P020329
ROM Level.(alterable).......0000CMD02252
ROM Level.(alterable).......CM020422
升级步骤：
cd /usr/lpp/diagnostics/bin ./update_flash -f /tmp/CM_MM_060505.img
升级后：
# lscfg -vp | grep alterable
ROM Level.(alterable).......M2P060505_condor_
ROM Level.(alterable).......0000CMD02252
ROM Level.(alterable).......CM060505
如何查看硬盘是否做过mirror
# lsvg -l rootvg
rootvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
hd5 boot 1 2 2 closed/syncd N/A
hd6 paging 32 64 2 open/syncd N/A
hd8 jfs2log 1 2 2 open/syncd N/A
hd4 jfs2 2 4 2 open/syncd /
hd2 jfs2 24 48 2 open/syncd /usr
hd9var jfs2 4 8 2 open/syncd /var
hd3 jfs2 4 8 2 open/syncd /tmp
hd1 jfs2 1 2 2 open/syncd /home
hd10opt jfs2 1 2 2 open/syncd /opt
fwdump jfs2 3 6 2 open/syncd /var/adm/ras/platform
paging00 paging 1 2 2 open/syncd N/A
oracle jfs2 72 144 2 open/syncd /oracle
lp=2pp 表示一份镜像，lp=pp表示没做，lp=3pp表示有二份
检查是否做QUORUM
# lsvg rootvg
VOLUME GROUP: rootvg VG IDENTIFIER: 00c9025e00004c00000001028e382d4b
VG STATE: active PP SIZE: 128 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 1092 (139776 megabytes)
MAX LVs: 256 FREE PPs: 712 (91136 megabytes)
LVs: 14 USED PPs: 380 (48640 megabytes)
OPEN LVs: 13 QUORUM: 1 TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
QUORUM: 1 表示没有，
QUORUM: 2表示有。
镜像硬盘
# bootinfo -b
hdisk0
# extendvg rootvg hdisk1
0516-1398 extendvg: The physical volume hdisk1, appears to belong to
another volume group. Use the force option to add this physical volume
to a volume group.
0516-792 extendvg: Unable to extend volume group.
# extendvg -f rootvg hdisk1
# chvg -Qn rootvg
# mirrorvg rootvg hdisk1
0516-1126 mirrorvg: rootvg successfully mirrored, user should perform
bosboot of system to initialize boot records. Then, user must modify
bootlist to include: hdisk1 hdisk0.
# bosboot -ad /dev/hdisk1
bosboot: Boot image is 30420 512 byte blocks.
# bootlist -m normal hdisk0 hdisk1 cd0
# shutdown -Fr
# lsvg -p rootvg
rootvg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk0 active 542 448 108..60..63..108..109
hdisk1 active 542 480 108..92..63..108..109
Blog Tags: aix