Archive for the ‘Oracle技术’ Category.
, 12:50 pm August 25 2010
–查表空间使用量
set linesize 150 pagesize 500
select
a.tablespace_name "tablespace_name",
trunc(sum_mb) "sum_mb",
trunc((sum_mb-free_mb)) "used_mb",
trunc(free_mb) "free_mb",
(trunc((sum_mb-free_mb)/sum_mb,4)*100) "used_percent"
from
(select tablespace_name,sum(bytes)/1024/1024 sum_mb from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+)
and (trunc((sum_mb-free_mb)/sum_mb,4)*100) > 80
order by (trunc((sum_mb-free_mb)/sum_mb,4)*100) ;
–查看所有文件
select member from v$logfile
union all
select name from v$datafile
union all
select name from v$tempfile
union all
select name from v$controlfile
order by 1;
Continue reading ‘oracle dba常用维护SQL集锦’ »
, 18:00 pm June 2 2010
面：
1 客户端配置使用连接管理器(CMAN),可以让客户端通过装有CMAN的服务器来访问DB，而不是直接访问DB，因此CMAN整个配置涉及到三方
2 CMAN服务器配置 192.168.110.200
3 数据库服务器配置 192.168.110.100
以下是配置过程
1 客户端配置
这个比较简单，只需要配置TNSNAMES.ORA就可以了，例如： Continue reading ‘配置CMAN让客户端访问数据库’ »
, 15:05 pm April 21 2009
我们先回顾一下checkpoint的一些知识点
1.checkpoint是一个数据库事件，它将已修改的数据从高速缓存刷新到磁盘，并更新控制文件和数据文件。
2.以下几种情况会触发checkpoint。
1).当发生日志组切换的时候 ，或手工切换的时候 ALTER SYSTEM SWITCH LOGFILE
2).当符合LOG_CHECKPOINT_TIMEOUT，LOG_CHECKPOINT_INTERVAL，fast_start_io_target,fast_start_mttr_target参数设置的时候
3).当运行ALTER SYSTEM CHECKPOINT的时候
4).当运行alter tablespace XXX begin backup，end backup的时候
5).当运行alter tablespace ,datafile offline的时候;
接下来我做实验看下具体情况: Continue reading ‘checkpoint技术探究’ »
, 5:22 am February 12 2009
sessions = 1105
sga_max_size = 42949672960
__shared_pool_size = 6157238272
shared_pool_size = 4294967296
__large_pool_size = 16777216
__java_pool_size = 16777216
__streams_pool_size = 16777216
shared_pool_reserved_size= 1073741824
sga_target = 42949672960
control_files = +DISKGROUP2/uwnms2/controlfile/current.260.669402989, +DISKGROUP2/uwnms2/controlfile/current.426.670094
605, +DISKGROUP2/uwnms2/controlfile/current.427.670094801
db_block_size = 8192
__db_cache_size = 35651584000 Continue reading ‘最漫长的一次实例启动过程’ »
, 23:08 pm January 17 2009
step1：上传介质
ftp -n 10.243.216.7 << EOF
user oracle password
prompt off
bin
lcd /oracle
mkdir /oracle/ora_install
cd /oracle/ora_install
put p6663057_10202_AIX64-5L.zip
bye
EOF Continue reading ‘apply one-off patch for 21 db’ »
, 10:47 am January 8 2009
环境：sun 5.8 + 10g RAC
客户急电，数据库无法登录，系统无法办理业务。
检查了下两个节点的listener和crs，状态都正常。
oracle@GDPSPDB1 $ sqlplus a/a@pspdb
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 8 10:21:24 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-00257: archiver error. Connect internal only, until freed. Continue reading ‘Trouble-Shooting for DB can’t login’ »
, 11:12 am January 7 2009
问题初因开发商原话:
现在遇到的问题是，删除其余市公司试用数据很慢。
因为在删除表的记录的时候，还是要对记录筛选及关联其余表。
举例一个表的删除：
工作计划表 pfm_work_plan_baseinfo ,总记录数193660。
delete from pfm_work_plan_baseinfo t where t.plan_man_id not in
(select b.EMPLOYEE_ID from v_org_employee_now b where b.ORG_ID in(12,19));
所需删除记录数为148375条，耗时：60808.328秒
关联的视图：v_org_employee_now,总记录数：55293 Continue reading ‘sql语句优化之not in 改成not exists’ »
, 23:48 pm December 25 2008
我有一个timesten实例,处于ASP模式
偶认为跟踪tt的日志对于进一步的了解tt的工作机制是很有好处的.
以下为active和standby两台的日志,大概每隔3秒左右刷新一次日志
—Active support log
2008-12-25 23:20:40.83 Info: ORA: 1097892: ora-1097892-2314-eporter00387: object_id 110409, bookmark 7
2008-12-25 23:20:40.84 Info: ORA: 1097892: ora-1097892-2314-eporter00387: object_id 611296, bookmark 44674
2008-12-25 23:20:40.85 Info: ORA: 1097892: ora-1097892-2314-eporter00387: object_id 597997, bookmark 22945 Continue reading ‘timesten log 解析’ »
, 12:36 pm December 22 2008
开发商发映某映查询巨慢,而该表只有区区几千条记录.
以下这句查询需要15秒：
select count(*) from PMS.SUP_SUPPLIER this_ where (1=1) order by this_.SUP_CODE asc;
以下这名查询45秒还没有反应：
select * from SUP_SUPPLIER where name like ‘%消防%’ ;
处理过程如下:
1.分析执行计划:该表为全表扫描
2.查询该表耗时近30 sec Continue reading ‘表调优之碎片过多一例’ »
, 21:14 pm December 8 2008
1.Oracle Clusterware Software Component Processing Details
Cluster Synchronization Services (CSS)—Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members when a node joins or leaves the cluster. If you are using third-party clusterware, then the css process interfaces with your clusterware to manage node membership information.
Cluster Ready Services (CRS)—The primary program for managing high availability operations within a cluster. Anything that the crs process manages is known as a cluster resource which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on. The crs process manages cluster resources based on the resource’s configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. The crs process generates events when a resource status changes. When you have installed Oracle RAC, crs monitors the Oracle instance, Listener, and so on, and automatically restarts these components when a failure occurs. By default, the crs process makes five attempts to restart a resource and then does not make further restart attempts if the resource does not restart. Continue reading ‘10g Real Application Clusters Architecture and Oracle Clusterware Processing’ »