碧海蓝天

#ls | wc -l

29592

#du -sk

14390876        .

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

System name:    HP-UX

Release:        B.11.31

Version:        U

Machine:        ia64

Redo thread mounted by this instance: 1

Oracle process number: 0

Ioctl ASYNC_CONFIG error, errno = 1

#cat /etc/privgroup

# ORACLE async i/o

dba MLOCK RTPRIO RTSCHED

#getprivgrp dba

dba:

#fuser /dev/async

/dev/async:

#vi /etc/privgroup

“/etc/privgroup” 2 lines, 44 characters

dba MLOCK RTSCHED RTPRIO

#setprivgrp -f /etc/privgroup

#getprivgrp dba

dba: RTPRIO MLOCK RTSCHED

#fuser /dev/async

/dev/async:     3613o    3622o    3624o    3583o    3585o    3588o

公司：www.hollycrm.com

地点：北京

要求：

• 至少2年ORACLE DBA经验；
• 熟悉ORACLE基本概念，熟练掌握各种管理维护操作；
• 能独立处理数据库故障，有丰富的数据库调优经验，尤其是SQL语句的优化；
• 熟悉linux，unix，掌握基本操作；
• 正规本科学历
• 有钻研精神，较强的自学能力；
• OCP持有优先，有java开发经验优先

根据能力，待遇范围7k-9k

有意请发邮件到
wajoynece at gmail.com

今天删除一个表空间

drop tablespace including contents and datafiles

执行完毕，df -h查看空间没有被释放，ls -l已经看不到被删除文件；

解决：

root@lab-rd-01:/# lsof | grep km_stress
oracle 19436 oracle 19u REG 8,7 10737426432 244535 /var/opt/oradata/rd/km_stress_test.dbf (deleted)

kill -9 19436后，空间立即得到释放。

_optimizer_sortmerge_join_enabled

10g以后，可以使用/*+ opt_param(‘_optimizer_sortmerge_join_enabled’,'false’) */提示，

在语句级别让CBO放弃smj连接选项。

ubuntu 8.04 64位server上，oracle用户下有一个备份的crontab任务，

任务调度异常，到时间不执行；

查看syslog发现了问题：

Sep 3 15:48:01 app6G /usr/sbin/cron[30235]: (oracle) RELOAD (crontabs/oracle)
Sep 3 15:48:01 app6G CRON[32285]: User account has expired

因为我在创建用户时指定了passwd -l选项锁定该用户，导致了问题；

取消了锁定，调度正常了。

现场报告连接数据库报错：ORA-12519 TNS: no appropriate service handler found

这个错误没遇到过，登录现场环境确认报错情况属实，sqlplus / as sysdba不能登录数据库；

检查操作系统oracle进程数目，与processes=600的设定比较接近；

检查应用服务器连接池连接情况，2台weblogic连接池current都是200，加起来400，有2台tomcat，current情况未知，配置最大75；

检查listener，发现state是blocked

‘”DEDICATED” established:1 refused:0 state:blocked’

这里比较异常，正常情况下应该是ready，不解；

求助metalink， 240710.1 找到了原因：

By way of instance registration, PMON is responsible for updating the listener
with information about a particular instance such as load and dispatcher
information. Maximum load for dedicated connections is determined by the
PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE
information varies according to the workload of the instance. The maximum
interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance
but does not immediately get information about connections that have terminated.
Only when PMON updates the listener via SERVICE_UPDATE is the listener
informed of current load. Since this can take as long as 10 minutes, there can be
a difference between the current instance load according to the listener
and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to “blocked”
and begin refusing incoming client connections with either of the following
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack

pmon动态instance信息到listener，这其中就包括process数目的信息，当到达限制时，pmon会通知listener别再申请连接了，listener把相应的服务状态修改为：blocked，客户端再次连接就会报12516错误。

开始解决：两台tomcat服务器运行了归档查询业务，是最近新添加的服务，问题也就出在这里；

关闭tomcat服务器；过一会查看监听状态，发现已经变为ready，sqlplus / as sysdba尝试连接数据库，已经可以连接；

进一步查看session信息，发现tomcat服务器仍然有144个连接，说明tomcat连接有严重泄露情况；

最终，把processes增大，tomcat＝>weblogic，期望可以解决这一问题。

系统中有很多表，中间有一些blankn类似名字的字段；

这种字段用于不断变化的灵活需求；

更糟糕的是，有一些查询条件基于这些灵活的字段，并且条件都是定制的……

我们还能作些什么呢？

oracle@wayne-laptop:~$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Aug 11 16:20:41 2009

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

connected to target database: ORA10G (DBID=4013050375)

RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0  DATABASE FORMAT ‘+dg1′TAG ‘ORA_ASM_MIGRATION’;

Starting backup at 11-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/app/oradata/ora10g/system01.dbf
output filename=+DG1/ora10g/datafile/system.264.694628679 tag=ORA_ASM_MIGRATION recid=7 stamp=694628715
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/home/oracle/app/oradata/ora10g/undotbs01.dbf
output filename=+DG1/ora10g/datafile/undotbs1.265.694628721 tag=ORA_ASM_MIGRATION recid=8 stamp=694628745
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/home/oracle/app/oradata/ora10g/sysaux01.dbf
output filename=+DG1/ora10g/datafile/sysaux.266.694628757 tag=ORA_ASM_MIGRATION recid=9 stamp=694628773
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/home/oracle/app/oradata/ORA10G/datafile/o1_mf_wayne_57q7y8hp_.dbf
output filename=+DG1/ora10g/datafile/wayne.268.694628781 tag=ORA_ASM_MIGRATION recid=10 stamp=694628787
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/home/oracle/app/oradata/ora10g/users01.dbf
output filename=+DG1/ora10g/datafile/users.261.694628797 tag=ORA_ASM_MIGRATION recid=11 stamp=694628796
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DG1/ora10g/controlfile/backup.262.694628797 tag=ORA_ASM_MIGRATION recid=12 stamp=694628799
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 11-AUG-09
channel ORA_DISK_1: finished piece 1 at 11-AUG-09
piece handle=+DG1/ora10g/backupset/2009_08_11/nnsnn0_ora_asm_migration_0.263.694628803 tag=ORA_ASM_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-AUG-09

RMAN> run {
2> backup as backupset spfile;
3> restore spfile to ‘+dg1/spfile’;
4> }

Starting backup at 11-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 11-AUG-09
channel ORA_DISK_1: finished piece 1 at 11-AUG-09
piece handle=/home/oracle/app/flash_recovery_area/ORA10G/backupset/2009_08_11/o1_mf_nnsnf_TAG20090811T162849_582by1ts_.bkp tag=TAG20090811T162849 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-AUG-09

Starting restore at 11-AUG-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=+dg1/spfile
channel ORA_DISK_1: reading from backup piece /home/oracle/app/flash_recovery_area/ORA10G/backupset/2009_08_11/o1_mf_nnsnf_TAG20090811T162849_582by1ts_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/app/flash_recovery_area/ORA10G/backupset/2009_08_11/o1_mf_nnsnf_TAG20090811T162849_582by1ts_.bkp tag=TAG20090811T162849
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 11-AUG-09

RMAN> exit

Recovery Manager complete.
oracle@wayne-laptop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 11 16:29:42 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force nomount PFILE=”/tmp/pfile.ora”;
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size            1266584 bytes
Variable Size          100666472 bytes
Database Buffers       96468992 bytes
Redo Buffers            7118848 bytes
SQL> alter system set control_files=’+dg1/ct1.f’,'+fra/ct2.f’ scope=spfile;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=spfile;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST=’+fra’ scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount PFILE=”/tmp/pfile.ora”;
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size            1266584 bytes
Variable Size          100666472 bytes
Database Buffers       96468992 bytes
Redo Buffers            7118848 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@wayne-laptop:~$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Aug 11 16:33:18 2009

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

connected to target database: ora10g (not mounted)

RMAN> restore controlfile from ‘/home/oracle/app/oradata/ora10g/control03.ctl’;

Starting restore at 11-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DG1/ct1.f
output filename=+FRA/ct2.f
Finished restore at 11-AUG-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy “+DG1/ora10g/datafile/system.264.694628679″
datafile 2 switched to datafile copy “+DG1/ora10g/datafile/undotbs1.265.694628721″
datafile 3 switched to datafile copy “+DG1/ora10g/datafile/sysaux.266.694628757″
datafile 4 switched to datafile copy “+DG1/ora10g/datafile/users.261.694628797″
datafile 5 switched to datafile copy “+DG1/ora10g/datafile/wayne.268.694628781″

RMAN> recover database;

Starting recover at 11-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 11-AUG-09

RMAN> run {
set newname for tempfile 1 to ‘+dg1′;
switch tempfile all;
}
2> 3> 4>
executing command: SET NEWNAME

renamed temporary file 1 to +dg1 in control file

RMAN> ALTER DATABASE OPEN;

database opened

RMAN> exit

Recovery Manager complete.

oracle@wayne-laptop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 11 16:39:03 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
declare
cursor rlc is
select group# grp, thread# thr, bytes/1024 bytes_k, ‘NO’ srl
from v$log
union
select group# grp, thread# thr, bytes/1024 bytes_k, ‘YES’ srl
from v$standby_log
order by 1;
stmt     varchar2(2048);
swtstmt  varchar2(1024) := ‘alter system switch logfile’;
ckpstmt  varchar2(1024) := ‘alter system checkpoint global’;
begin
for rlcRec in rlc loop
if (rlcRec.srl = ‘YES’) then
stmt := ‘alter database add standby logfile thread ‘ ||
rlcRec.thr || ‘ ”+DG1” size ‘ ||
rlcRec.bytes_k || ‘K’;
execute immediate stmt;
stmt := ‘alter database drop standby logfile group ‘ || rlcRec.grp;
execute immediate stmt;
else
stmt := ‘alter database add logfile thread ‘ ||
rlcRec.thr || ‘ ”+DG1” size ‘ ||
rlcRec.bytes_k || ‘K’;
execute immediate stmt;
begin
stmt := ‘alter database drop logfile group ‘ || rlcRec.grp;
dbms_output.put_line(stmt);
execute immediate stmt;
exception
when others then
execute immediate swtstmt;
execute immediate ckpstmt;
execute immediate stmt;
end;
end if;
end loop;
38  end;
39  /

PL/SQL procedure successfully completed.

准备模拟磁盘文件

root@wayne-laptop:/home/oracle/test# for i in 0 1 2 3 ;do dd if=/dev/zero of=file_disk$i bs=1024 count=524288 ; done
524288+0 records in
524288+0 records out
536870912 bytes (537 MB) copied, 14.0764 s, 38.1 MB/s
524288+0 records in
524288+0 records out
536870912 bytes (537 MB) copied, 15.2912 s, 35.1 MB/s
524288+0 records in
524288+0 records out
536870912 bytes (537 MB) copied, 18.8882 s, 28.4 MB/s
524288+0 records in
524288+0 records out
536870912 bytes (537 MB) copied, 18.276 s, 29.4 MB/s
root@wayne-laptop:/home/oracle/test# ls -l
total 2099216
-rw-r–r– 1 root root 536870912 2009-08-10 10:48 file_disk0
-rw-r–r– 1 root root 536870912 2009-08-10 10:48 file_disk1
-rw-r–r– 1 root root 536870912 2009-08-10 10:49 file_disk2
-rw-r–r– 1 root root 536870912 2009-08-10 10:49 file_disk3

创建loop设备

root@wayne-laptop:/home/oracle/test# for i in 0 1 2 3 ; do losetup /dev/loop$i file_disk$i; done
root@wayne-laptop:/home/oracle/test# losetup -a
/dev/loop0: [0805]:1917697 (file_disk0)
/dev/loop1: [0805]:1917709 (file_disk1)
/dev/loop2: [0805]:1917711 (file_disk2)
/dev/loop3: [0805]:1917712 (file_disk3)
root@wayne-laptop:/dev# chown oracle:dba /dev/loop*

配置css

+++++++++++++++出现的问题以及解决++++++++++++++++++++++++++
root@wayne-laptop:/home/oracle/app/10.2.0/db_1/bin# localconfig add
/home/oracle/app/10.2.0/db_1/bin/crsctl.bin: /usr/lib/libstdc++.so.5: version `CXXABI_1.2′ not found (required by /home/oracle/app/10.2.0/db_1/bin/crsctl.bin)
/home/oracle/app/10.2.0/db_1/bin/crsctl.bin: /usr/lib/libstdc++.so.5: version `GLIBCPP_3.2′ not found (required by /home/oracle/app/10.2.0/db_1/bin/crsctl.bin)

root@wayne-laptop:/home/oracle/app/10.2.0/db_1/bin# cd /usr/lib
root@wayne-laptop:/usr/lib# ls -l | grep libstdc__
root@wayne-laptop:/usr/lib# ls -l | grep libstdc++
lrwxrwxrwx 1 root root 23 2009-05-12 11:16 libstdc++.so.5 -> /usr/lib/libstdc++.so.6
lrwxrwxrwx 1 root root 18 2009-05-11 18:32 libstdc++.so.6 -> libstdc++.so.6.0.9
-rw-r–r– 1 root root 970680 2009-02-19 18:42 libstdc++.so.6.0.9

root@wayne-laptop:/usr/lib# aptitude install libstdc++5
root@wayne-laptop:/usr/lib# ln -sf libstdc++.so.5.0.7 libstdc++.so.5
+++++++++++++++++++++++++++++++++++++++++

root@wayne-laptop:/home/oracle/app/10.2.0/db_1/bin# mkdir -p /var/lock/subsys
root@wayne-laptop:/home/oracle/app/10.2.0/db_1/bin# localconfig reset
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process…
Expecting the CRS daemons to be up within 600 seconds.

^c

root@wayne-laptop:/home/oracle/app/10.2.0/db_1/bin# nohup /etc/init.d/init.cssd run >/dev/null 2>&1
[1] 17991

创建asm实例

$ dbca -silent -configureASM \
-asmSysPassword change_on_install \
-diskString “/dev/loop*” \
-diskList /dev/loop1 \
-diskGroupName DG1 \
-redundancy EXTERNAL

话说从前有一个从excel导入到数据库中的功能，近来该功能“超级”缓慢，严重影响生产活动。

从告警日志中，可以找到一些线索：日志切换非常频繁，出现多处checkpoint not complete。

同时，在系统中查询产生redo最多的会话，正是上面提到的那个功能，导入不到1w条数据，竟然累计产生近10G的redo！！！

到这里，已经有理由怀疑是这个功能出现的问题，导致过多的redo，进一步产生checkpoint not complete等待；

OK，10046，终于找到了问题的根源：不合理使用的物化视图

在导入功能相关的数据库表上，建有一个物化视图，我们知道物化视图刷新可以on commit,on demand，而这里使用了on commit，而且每次都是全刷新；更糟糕的是，导入功能是每条记录提交一次！

也就是说：每导入一条数据，物化视图需要清空，重新刷新数据。

解决思路：

• 物化视图刷新模式修改为on demand
• 物化视图全刷新变为快速刷新
• 导入数据使用分批提交方式