trainingnotes.docx
《trainingnotes.docx》由会员分享，可在线阅读，更多相关《trainingnotes.docx（9页珍藏版）》请在冰豆网上搜索。
trainingnotes
王志刚，OracleSupportServiceChina
DBA/ASSYSDBA
SYS/SYS@rac9iassysdba
OUIpatchset/opatch/DBUA:
globalinventory:
oraclehomelist,
/etc/oraInst.loc:
inventory_loc=/oracle/app/oracle/oraInventory
inst_group=oinstall
localinventory:
components,oneoffpatchlist
$ORACLE_HOME/inventory
root.sh之前检查是否有/var/opt/oracle
/var/opt/oracle/srvConfig.loc
srvconfig_loc=/dev/raw/raw2
一个节点：
srvconfig–init–f
所有节点：
gsdctlstart
==================================
DBA_REGISTRY
JVM
Utltrasearch
…
SQL>selectcomp_name,version,statusfromdba_registry;
rac1.cluster_interconnects=10.1.1.101,10.1.1.103
rac2.cluster_interconnects=10.1.1.102,10.1.1.104
===SRVCTL,SRVCONFIG====
$srvconfig–init–f
$srvctlconfigdatabase
srvctlconfigdatabase
srvctladddatabase–dJOEY–o$ORACLE_HOME
srvctlremovedatabase–dJOEY
srvctlconfigdatabase-dJOEY
srvctladdinstance-dJOEY-iJOEY1-napps7
srvctladdinstance-dJOEY-iJOEY2-napps8
srvctlsetenvdatabase–dt=
TZ
srvctlsetenvdatabase-dJOEY-tTZ=SAT3
servicename:
initparameter:
service_names:
abc,def，
local_listener:
tnsnames.ora中的网络名,if=nullthenPMON->local1521listener
remote_listener:
tnsnames.ora中的网络名
instance_name：
sid（$ORACLE_SID）
PMON->LISTENER
注册（servicename,sid,sidstatus,loadstatus）
instancename
sid
-------------------------------------------------------
dbname:
dbname（controlfile+initpara）
domainname（dbname.db_domain,initpara）
globalname（true/false）
--------------------------------------------------------
buffercachehitratio
=
（1-physicalreads/（consistentgets+dbblockgets））*100%
=
（1-physicalreads/sessionlogicalreads）*100%
inmemoryoperation:
*dbblockgets=currentgets
*consistentgets=（currentimageinmemory+undorecords）
MAA=RAC+DATAGUARD（standbydatabase,maxp,a）
Commit=>
redobuffer->disk
&&or
redobuffer->remotehost
HA->MTTR减小
Apps7（apps7vip）listener（vip）
Apps8（apps8vip）listener（vip）
Connect-timefailover缺省为YES
Clientloadbalance缺省为NO
allocatechannelt1type'sbt_tape'
parms'BLKSIZE=500000,SBT_LIBRARY=oracle.disksbt,ENV=（BACKUP_DIR=/shared/backup1）'
connect'sys/SYS@joey1';
allocatechannelt2type'sbt_tape'
parms'SBT_LIBRARY=oracle.disksbt,ENV=（BACKUP_DIR=/shared/backup2）'
connect'sys/SYS@joey2';
iftape_backup_io_slaves=trueanddbwr_io_slaves>0then
largepool>=
*（16M+4*）;
infact:
largepool>=
16M+*4*=18M
block_sizedefaults256K,parms‘BLKSIZE=xxxx’
============================================
rman:
维护操作,maintenancechannel:
DELETEEXPIREDARCHIVELOGALL;
DELETEARCHIVELOGALLCOMPLETEDBEFORE‘SYSDATE-1’;
select*fromtab;
select*fromrc_datafile;
altersessionsetnls_date_format='yyyymmddhh24:
mi:
ss';
select*fromrc_backup_piecewherecompletion_time>trunc（sysdate）;
select*fromrc_backup_controlfile;
select*fromv$backup_datafileorderbyfile#;
select*fromv$sgastatwherepool='largepool';
select*fromv$datafileorderbyfile#;
select*fromrc_backup_redologwherecompletion_time>trunc（sysdate）;
select*fromrc_backup_setwherebs_KEYIN（131,132）;
SELECT*FROMRC_BACKUP_PIECEWHEREBS_KEYIN（131,132）;
select*fromv$log;
select*fromrc_redo_log;
select*fromrc_archived_log;
select*fromv$archived_logwheredeleted!
='YES';
select*fromv$datafile;
locallyextentmanagement:
management:
local
allocation:
uniform,
auto（system）,
user:
migratedfromdictionarymanagementtablespace;（pctincrease,next）,altertable;
logical:
schema;
physical:
tablespace;
interfacetable:
HWM（move,indexesrebuild）xxxxx|
segment:
segmentfreelist（pctfree,pctused）->freelists
freelist1（100blocks）
freelist2（0）-
index在ASSM管理的tablespace中（9206以下）
insert消耗大量undotablespace，收缩（重建）索引。
Table在ASSM管理的tablespace中（9207以下）
大量delete后，没有commit，大量insert，性能差。
Sequence:
Instance:
Seq1.nextval
Instance:
1,000,000+instance_number*seq.nextval
WHEREA=123458,
854321
Wherea=123457
754321
123456-123457
parallel_execution_message_size=2148*（numberofP00n）
allocatedfrom
ifautotuning=false
shared_pool
elseifautotuning=true
largepool
parallel_max_servers限制一个实例上总共的并行进程数。
实例间的并行只在parallel_instance_group相同的实例中进行。
selecttablespace_name,status,sum（bytes）/1024/1024fromdba_undo_extents
groupbytablespace_name,status
orderby1,2;
selectinst_id,total_blocks,used_blocks,free_blocksfromgv$sort_segmentorderby1;
indexuniquescan:
对唯一索引指定唯一值
root->branch->branch…->leaf
indexrangescan:
root->branch->branch…->leaf……
indexfullscan:
root->allbranch->allleaf
indexfastfullscan:
leaf->leaf->leaf………
librarycachepin
librarycachelock
x$kgllk
x$kglpn
v$lock
selectobject_name,object_typefromdba_objects
wherestatus!
='VALID';
编译数据库中所有无效对象，以SYS用户运行：
$ORACLE_HOME/rdbms/admin/utlrp.sql
JDBC:
Prepare.
Setvalue
Exec
Exec
Exec
….
fetch
create
exec
fetch
（1-（parsecount/execcount））*100%
1.select*fromt01;snapshot;
….Snapshot;
end;v$sql
…………………snapshot;
end;
响应时间＝CPU运算时间+等待时间
CPU运算时间：
logicalreads（processingmemoryblocks）,func,wherea=2,parse
等待时间：
I/O＋LOCK/EQNUEUE,LATCH
pin/lock
sharedpoolissue:
hardparse/
latchfree（librarycachelatch,sharedpoollatch）
升级会员 