Oracle11G日常维护手册

OracleDatabase登陆到数据 服务器端配置 客户端 检查Oracle 登陆数据库的方 数据库的启 关闭数据 用户管 检察用户 查看用户profile参 检查数据库基本状 检查数据库创建日 检查数据库版本信 检查实例状 查看前台进 查看数据库连接的 查看连接到数据库的模 查看并发连接 查看最大的连接 监控系统后台进 查看数据库初始化参 检查PGA使用情 检查SGA状 检查Oracle服务进 检查Oracle监听状 检查监听进程是否存 检查操作系统日志文 检查oracle日志文 检查Oracle核心转储目 检查Root用户和Oracle用户的 检查Oracle对象状 检查Oracle控制文件状 检查Oracle在线日志状 检查Oracle表空间的状 检查Oracle所有数据文件状 检查无效对 检查所有回滚段状 检查用户下的 检查用户默认表空 检查当前用户角色及权 检查用户下的各个表的大 检查一个表的创建时 检查某个表的大 检查每个表占用磁盘空间情 检查Oracle相关资源的使用情 检查Oracle初始化文件中相关参数 检查数据库连接情 检查系统磁盘空 检查表空间使用情 检查一些扩展异常的对 检查表空间碎片情 检查system表空间内的内 检查对象的下一扩展与表空间的最大扩展 检查flashrecoveryarea空 检查Oracle数据库性 查询表空间读写情 查询redologbuffer的繁忙程 判断undo表空间的使用情 分析日志组切换频 查看等待事 检查数据库cpu、I/O、内存性 内存使用情 系统I/O情 系统负载情 查看是否有僵死进 检查缓冲区命中 检查共享池命中 检查排序 检查日志缓冲 检查失效的索 检查不起作用的约 检查无效的 检查尚未建立索引的 检查运行时间长的 检查性能差的前10条 查看占io较大的正在运行的 检查消耗CPU最高的PID对应的 检查占用CPU多的 检查表空间的 检查临时表空间 检查锁和等 检查数据库安全 检查系统安全日志信 检查登录失败的日志 检查用户修改密 数据表空间日常维 查看表空间的一些信 创建表空 表空间扩 创建大数据文 数据表空间文件迁 不停机移动表空间文 存储过程管 找出特定用户的存储过 通过表名找出存储过 查看存储过程内 触发器管 找出数据库中所有触发 找出特定用户的触发 找出当前用户定义的触发 查看某个用户自定义的触发器内 查看某个表关联的触发 查看当前用户所有触发器及存储过 RedoLog管 离线迁移日志文 在线更改RedoLog文件容 服务器端配置LISTENER=(DESCRIPTION_LIST=(DESCRIPTION(ADDRESS=(PROTOCOL=IPC)(KEY(ADDRESS=(PROTOCOL=TCP)(HOST=0)(PORT=))ADR_BASE_LISTENER=/u01/app/oracle#BEQUEATH#PRESPAWNCONFIG)))ServiceSIDGLOBAL_DBNAME客户端根据tnsname.ora中的SERVICE_NAME和地址(ADDRESS=(PROTOCOL==racdb1)(PORT=1521))，到这个地址去访问监听器。然后监听器lisnter.ora文件中的GLOBAL_NAMEGLOBAL_DBNAME和SERVICE_NAME相等则建立客户端到SID标识的服务端实例的连接,在客户端上我们可以使用tnsping命令来测试客户端racdb1=(description=(address_list(address=(protocol=tcp)(host=0)(port=)(service_name))service_name=racdb1GLOBAL_DBNAME=racdb1[oracle@racdb1~]$tnspingracdb1TNSPingUtilityforLinux:Version.0-Productionon21-JAN-201514:51:49Copyright(c)1997,2009,Oracle. Allrightsreserved.TNS-03502:Insufficientarguments. tnsping<address>[<count>][oracle@racdb1~]$tnspingracdb1TNSPingUtilityforLinux:Version.0-Productionon21-JAN-201514:51:55Copyright(c)1997,2009,Oracle. Allrightsreserved.UsedparameterUsedTNSNAMESadaptertoresolvetheAttemptingtocontact(description=(address_list=(address=(protocol=tcp)(host=racdb1)(port=1521)))(connect_data=(service_name=racdb1)(ur=a)))OK(0Oraclelsnrctlstoplsnrctlstartlsnrctlstatuslsnrctloracleoracle安装好后是使用操作系统用户的验证,sys用户不用密码就可以登录,oracle密码文件验证的话我们就要进入下列文件夹cd/u01/app/oracle/product/11.2.0/dbhome_1/network/adminsqlnet.oraSQLNET.AUTHENTICATION_SERVICES=tnsnames登录数据库方式[oracle@racdb1~]$sqlplusscott/111111@racdb1sqlplus/nologscottracdb1connsys/111111@racdb1ASSYSDBA;selectinstance_namefromv$instancesys用户登陆connsys/change_on_installerassysdbaconn/assysconnsys/change_on_installassysdba;conn/assysdba连接数据库connscott/111111scottstartupstartupstartupstartup openstartup启动数据库实例,该步骤只是启动了一个数据库实例.Select*from Select*from Select*from alterdatabaseselect*fromv$database;select*fromv$tablespace;select*fromv$log;alterdatabasestartupaltersystemenablerestrictedsessiongrantrestrictsessiontoscottrestrictselectname,DESCRIPTIONfromv$bgprocesswhereshutdownShutdownshutdown,当前所有做工作回滚到一致状态,shutdownselectusername,profilefromselectusername,profilefromdba_userswhereselect*fromdba_profilesselect*fromdba_profileswhereSelectCreated,Log_Mode FromV$Database;SelectCreated,Log_Mode FromV$Database; 2014/8/171SelectversionSelectversionfromProduct_component_versionwhereSQL>SELECTSQL>SELECTinst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,mm-ddhh24:mi:ss')startup_time,status,archiver,database_statusFROMSELECTinst_id,dbid,NAME,TO_CHAR(created,'yyyy-mm-ddhh24:mi:ss')created,de,TO_CHAR(version_time,'yyyy-mm-ddhh24:mi:ss')version_time,open_modeFROMgv$DBID 942644082014-08-1716:34:32NOARCHIVELOG2014-08-1716:34:32DSQL>selectname,log_mode,open_modefromSQL>selectname,log_mode,open_modefrom READL”eL表示数据库运行在归档模式下，E表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。ps-Cps-Coracle-o'rsz,sid,cmd'|grepfree-m|egrep-v'total|buffers'|awk'{print$1,$3说明Oracle的前台进程是操作系统进程，它和oraclesessionoracle=1.5*processSQL>selectcount(*)SQL>selectcount(*)fromselectprogramselectprogramfromv$processorderbyselectd.NAME,s.NAMEfromv$dispatcherd,v$shared_servers,v$circuitcwhered.PADDR=c.DISPATCHERands.PADDR=c.SERVER说明selectcount(*)selectcount(*)fromv$sessionwhereSQL>selectvalueSQL>selectvaluefromv$parameterwherename=SQL>SelectSQL>Selectname,DescriptionFromV$BGPROCESSWhere name,valuefrom name,valuefromv$parameterwhereisbasic='TRUE'orderby说明selectname,valuefromv$pgastatselectname,valuefromv$pgastatwherenamein('maximumPGAallocated','totalPGASELECTrequest_misses, FROMSELECTrequest_misses, FROMSelectcomponent,current_size,min_size,max_sizefromv$sga_dynamic_components;REQUEST_MISSESREQUEST_FAILURES00期望结果：request_missesrequest_failures0巡检说明：request_missesLRU列表刷新对象的次数；request_failures是未找到满足请求的内存次数。[oracle@racdb1~]$ps-ef|grep[oracle@racdb1~]$ps-ef|grepora_|grep-vgrep&&ps-ef|grepora_|grep-vgrep|wc1015:5300:00:001015:5300:00:171015:5300:00:001015:5300:00:001015:5300:00:001015:5300:00:001015:5300:00:021015:5300:00:001015:5300:00:001015:5300:00:011015:5300:00:051015:5300:00:001015:5300:00:001015:5300:00:00OracleOracleOracleOracleOracleOracleOracleOracle[oracle@racdb1~]$lsnrctl[oracle@racdb1~]$lsnrctlLSNRCTLforLinux:Version.0-Productionon21-JAN-201517:00:42Copyright(c)1991,2009,Oracle. Allrightsreserved.ConnectingtoSTATUSoftheStartDateTraceLevelTNSLSNRforLinux:Version.0-Production21-JAN-201512:37:480days4hr.22min.53secON:LocalOSAuthenticationListenerLogFile ListeningEndpointsSummary...ServicesService"racdb1"has1Instance"racdb1",statusUNKNOWN,has3handler(s)forthisService""has1Instance"racdb1",statusREADY,has1handler(s)forthisService"racdb1XDB."has1Instance"racdb1",statusREADY,has1handler(s)forthisThecommandcompleted“ServicesSummary”Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应[oracle@racdb1 ps[oracle@racdb1 ps-ef|greplsn|grep-v 012:37? 00:00:01/u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnrLISTENER-inherit[root@racdb1~]#[root@racdb1~]#cat/var/log/messages|grep说明OracleSQL>selectvaluefromSQL>selectvaluefromv$diag_infowherename='DiagTrace';catcat/u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log|grepora-cat/u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log|greperr[oracle@racdb1trace]$cat|grep说明e在运行过程中，会在警告日志文件_.及如果因为检查点pi操作没有执行完成造成不能切换，会记录不能切换的原因；－60)TRCOraclebug selectvalue fromv$parameterwhere selectvalue fromv$parameterwherename='core_dump_dest';[oracle@racdb1cdump]$ls/u01/app/oracle/diag/rdbms/racdb1/racdb1/cdump/*.trc|wc-Oracle进程经常发生核心转储。这说明某些用后台进程的核心转储会导致数据库异常终止。Oracle常用日志文件位置查询select*fromv$parameterwherename='background_dump_dest';select*fromv$parameterwherename='user_dump_dest';select*fromv$parameterwherename='core_dump_dest';select*fromv$parameterwherename='audit_file_dest';select*fromv$parameterwherename='audit_syslog_level';[oracle@racdb1racdb1]$tail-n200/var/mail/oracle[oracle@racdb1racdb1]$tail-n200/var/mail/root[oracle@racdb1racdb1]$tail-n200/var/mail/oracle[oracle@racdb1racdb1]$tail-n200/var/mail/root说明Oracle检查OracleOracle所有回滚段的状态，总共六Oracle select selectstatus,namefromv$controlfile; SQL>selectgroup#,status,type,memberfromSQL>selectgroup#,status,type,memberfromGROUP# 321 3条以上（3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”SQL>selecttablespace_name,statusfromSQL>selecttablespace_name,statusfrom 6rowsSTATUSONLINESQL>selectname,statusfromSQL>selectname,statusfrom selectfile_name,statusfrom selectfile_name,statusfrom owner!='SYS'andowner!='SYSTEM';norows说明SELECTowner,SELECTowner,object_name,object_typeFROMdba_objectsWHEREstatus=SQL>selectSQL>selectsegment_name,statusfrom11rowsselect*select*from select*select*fromdba_role_privswhereGRANTEE='SCOTT';select*fromdba_sys_privswhereGRANTEE='SCOTT';select*fromdba_tab_privswhereGRANTEE='SCOTT';SelectSegment_Name,Sum(bytes)/1024/1024SelectSegment_Name,Sum(bytes)/1024/1024MBFromdba_ExtentswhereOWNER='SCOTT'GroupBySegment_Nameorderbysum(bytes)/1024/1024desc;selectobject_name,createdselectobject_name,createdfromdba_objectswhereselectsum(bytes)/(1024*1024)selectsum(bytes)/(1024*1024)as"size(M)"fromdba_segmentswheresegment_name=upper('&table_name');selectb.file_idselectb.file_idfile_ID,b.tablespace_nametablespace_name,b.bytesBytes,(b.bytes-sum(nvl(a.bytes,0)))used,sum(nvl(a.bytes,0))free,sum(nvl(a.bytes,0))/(b.bytes)*100Percentfromdba_free_spacea,dba_data_filesbwherea.file_id=b.file_idgroupbyb.tablespace_name,b.file_id,b.bytesorderbyb.file_id;检查OracleOracleOracle初始化文件中相关的参数Oracle各个表空间使用情况，检查一system表空间内的内容，检查对象的下一扩展与表空间的最大扩SQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;SQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;000000000000000000000000001101023rowsSQL>selectSQL>selectcount(*)fromselects.osuseros_user_name,decode(sign(48-command),'ActionCode#'||to_char(command))action,gramoracle_process,statussession_status,s.terminalterminal,gramprogram,s.usernameuser_name,s.fixed_table_sequenceactivity_meter,''query,000cpu_usage,s.serial#fromv$session v$process wheres.paddr=p.addrs.type=orderbys.username,SQL>selectsid,serial#,username,program,machine,statusfrom其中：SID会话(session)IDSERIAL#SID一起用来唯一标识一个会话；USERNAME建立该会话的用户名；PROGRAMSTATUS当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数sessionsid查到操作系统的spid,使用ps–ef|grepspidno的方式确认spid不是ORACLE的后台进程killaltersystemkillaltersystemkillsessionSID110(USERNAME列为空)Oracle的后台进程，不要对这些[oracle@racdb1 df-[oracle@racdb1 df- UsedAvailUse%Mounted 5% 34% 37%1% 1% 1% 15%2%SELECTdf.file_id,df.file_name,df.size_mb,NVL(free.maxfree,0)ROUND(NVL(free.free_mb,0),2)free_mb,100-ROUND(100.0*NVL(free.free_mb,0)df.size_mb,2)ROUND(100.0*NVL(free.free_mb,0)/df.size_mb,2)FROM(SELECTfile_id,file_name,tablespace_name,BYTES/1048576size_mbFROMdba_data_files)df, file_id,SUM(BYTES)/1048576free_mb,TRUNC(MAX(BYTES/1024/2)FROMdba_free_spaceGROUPBYfile_id)freeWHEREdf.file_id=free.file_id(+)ORDER %6155354SSQL>select(1-(A.total)/B.total)*100used_percent(selecttablespace_name,sum(bytes)totalfromdba_free_spacegroupbytablespace_name)(selecttablespace_name,sum(bytes)totalfromdba_data_filesgroupbytablespace_name)where SELECTD.TABLESPACE_NAME,FILE_NAME"FILE_NAME",SPACE"SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0)"USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)FROM(SELECTFILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMGROUPBYTABLESPACE_NAME,FILE_ID,FILE_NAME)(SELECTFILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME,FILE_ID)(SELECTFILE_ID,AUTOEXTENSIBLEFROMDBA_DATA_FILES)WHERED.TABLESPACE_NAME=E.TABLESPACE_NAME(+)ANDD.FILE_ID=E.FILE_ID(+)ANDD.FILE_ID=F.FILE_ID(+)UNIONALL--ifhaveSELECTD.TABLESPACE_NAME,FILE_NAME"FILE_NAME",SPACE"SUM_SPACE(M)",USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"USED_RATE(%)",AUTOEXTENSIBLE(SELECTFILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMGROUPBYTABLESPACE_NAME,FILE_ID,FILE_NAME)(SELECTFILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACEFROMGROUPBYTABLESPACE_NAME,FILE_ID)(SELECT(SELECTFILE_ID,AUTOEXTENSIBLEFROMDBA_TEMP_FILES)WHERED.TABLESPACE_NAME=E.TABLESPACE_NAME(+)ANDD.FILE_ID=E.FILE_ID(+)ANDD.FILE_ID=F.FILE_ID(+)ORDERBY10%2G4G8G。selectSegment_Name,selectSegment_Name,Segment_Type,TableSpace_Name,(Extents/Max_extents)*100PercentFromWhereMax_Extents!=0and(Extents/Max_extents)*100>=95orderByPercent;norows说明selecttablespace_name,selecttablespace_name,count(*)chunks,max(bytes)/1024/1024max_chunk,sum(bytes)/1024/1024total_spacefromdba_free_spacegroupby说明其中，CHUNKS列表示表空间中有多少可用的空闲块(每个空闲块是由一些连续的Oracle数据块组成)，如果这样的空闲块过多，比如平均到每个数据文件上超过了100个，那么该表空间的碎片状况就比较严重了，可以尝试用以下的SQL命令进行表空间相邻碎片或重建某些数据库对象。碎片接合的方法:altertablespaceuserscoalesce;selectdistinct(owner)fromdba_tableswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM'selectselectdistinct(owner)fromdba_tableswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM'selectdistinct(owner)fromdba_indexeswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM';说明System表空间，同时应selecta.table_name,selecta.table_name,a.next_extent,a.tablespace_namefromall_tablesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)wheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunkselecta.index_name,a.next_extent,a.tablespace_namefromall_indexesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)wheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk;norows说明SELECTsubstr(name,1,30SELECTsubstr(name,1,30)name,space_limitASquota,space_usedASused,space_reclaimableASreclaimable,number_of_files ASfiles v$recovery_file_dest说明检查OracleSQL>SELECTname,phyrds,phywrtsFROMV$datafileSQL>SELECTname,phyrds,phywrtsFROMV$datafiledf,V$filestatfsWHEREdf.file#=fs.file#; PHYRDS代表已完成的物理读次数，PHYWRTSDBWRIOIO，所以一般可以通过操作系统的一些命令来确认一IOiostat，sar等SQL>SELECTretries.value/entries.value"RedoLogBufferSQL>SELECTretries.value/entries.value"RedoLogBufferRetryFROMV$sysstatretries,V$sysstatW 'redobufferallocationAnd= 'redoentries';RedoLogBufferRetryRatio说明redobufferallocationretries01%，否则要redobufferundoSQL>selectSQL>selectsum(bytes),statusfromdba_undo_extentsgroupbystatus;SUM(BYTES)STATUS766771210616832undoextent可以有三种状态expired:已结束的事务，undoundo_retentionunexpired:已经结束的事务，undoundo_retentionundoblockundoundospace.activeextentundosegmentundo TO_CHAR(first_time,'yyyy-mm-dd')DAY,COUNT(*)switch_times,SUM(DECODE(TO_CHAR(first_time,'hh24'),'00',1,0))h00,SUM(DECODE(TO_CHAR(first_time,'hh24'),'01',1,0))h01,SUM(DECODE(first_time,'hh24'),'02',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'03',1,0))h03,SUM(DECODE(first_time,'hh24'),'04',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'05',1,0))h05,SUM(DECODE(first_time,'hh24'),'06',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'07',1,0))h07,SUM(DECODE(first_time,'hh24'),'08',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'09',1,0))h09,SUM(DECODE(first_time,'hh24'),'10',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'11',1,0))h11,SUM(DECODE(first_time,'hh24'),'12',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'13',1,0))h13,SUM(DECODE(first_time,'hh24'),'14',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'15',1,0))h15,SUM(DECODE(first_time,'hh24'),'16',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'17',1,0))h17,SUM(DECODE(first_time,'hh24'),'18',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'19',1,0))h19,SUM(DECODE(first_time,'hh24'),'20',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'21',1,0))h21,SUM(DECODE(first_time,'hh24'),'22',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'23',1,0))FROMv$log_historyWHEREfirst_time>TRUNC(SYSDATE-30)GROUPBYROLLUP(TO_CHAR(first_time,'yyyy-mm-dd'));redofromv$sessionfromv$sessions,v$session_eventseWheres.sid=se.sidAndse.eventnotlike'SQl*Net%'Ands.status='ACTIVE'Ands.usernameisnotSQL>select*(selectevent"waitevent",time_waited"timeround(time_waitedround(time_waited/(selectsum(time_waited)fromv$system_event),4)"%timewaited",total_waits"waits",round(total_waits/(selectsum(total_waits)fromv$system_event),4)"%waited"fromv$system_event wherewait_class!='Idle'orderby2desc)whererownum说值%waited代表等待数据比，此值越小越好,cp检查数据库cpu、I/Otop-10:29:35up73days, 1 loadaverage:0.37,0.38,Tasks:353 2running,351 0 0 1.2%us, 0.1%sy, 0.0%ni,98.8%id, 0.0%wa, 0.0%hi, 0.0%si 16404472ktotal,12887428kused, 3517044kfree, 60796kbuffers 8385920ktotal, 665576kused, 7720344kfree,10358384kcachedPID SHRS%CPU 3049508329m866m861m 7:53.903250108328m1.7g1.7g 2 1:58.383250308329m1.6g1.6g 2 2:06.62说明CPU使用率异常，需记录下该数值，并将状态记为异常。[oracle@racdb1racdb1]$[oracle@racdb1racdb1]$free-0-/+ 10%时视为异常。[oracle@racdb1 iostat-k1[oracle@racdb1 iostat-k1Linux2.6.32-504.1.3.el6.x86_64()_x86_64_(1 %nice%system 0说明cpuIOidleiowaitidle值非常小(20%)iowait值非常大(70%)IO出现问题。IO问题涉及操作系统，存储系统，cp负荷及应用系统等一些因素，故当发现问题请及[oracle@racdb1racdb1]$[oracle@racdb1racdb1]$21:31:08 2 loadaverage:0.00,0.00,说明SQL>SQL>selectspidfromv$processwhereaddrnotin(selectpaddrfromv$session);SQL>SELECTSQL>SELECTa.VALUE+b.VALUElogical_reads,c.VALUEphys_reads,round(100*(1-c.value/(a.value+b.value)),4)hit_ratioFROMv$sysstata,v$sysstatb,v$sysstatcWHEREa.NAME='dbblockgets'ANDb.NAME ='consistentgets'AND ='physicalreads'LOGICAL_READS 90%db_cache_sizeSQL>SQL>selectsum(pinhits)/sum(pins)*100fromv$librarycache;SQL>selectSQL>selectname,valuefromv$sysstatwherenamelikesortssorts(disk)sorts(rows)0disk/(memoty+row)或SQL>SQL>selectname,valuefromv$sysstatwherenamein('redoentries','redobufferredoredobufferallocation1redobufferallocationretries/redoentries1%log_buffer owner='CTAIS2'Andstatus<>'VALID';statusN/Arebuild，如：Sql>alterindexINDEX_NAMErebuildtablespaceTABLESPACE_NAME;SELECTSELECTowner,constraint_name,table_name,constraint_type,statusFROMdba_constraintsWHEREstatus='DISABLE'andSql>alterTableTABLE_NAMEEnableConstraintsSELECTowner,SELECTowner,trigger_name,table_name,statusFROMdba_triggersWHEREstatus=Sql>alterTriggerTRIGGER_NAME /*+rule*/owner,segment_name,segment_type,tablespace_name,TRUNC(BYTES/1024/1024,1)size_mb /*+rule*/owner,segment_name,segment_type,tablespace_name,TRUNC(BYTES/1024/1024,1)size_mbFROMdba_segmentsWHERENOTEXISTS(SELECT'x'FROMdba_indexesWHEREt.owner=ANDt.segment_name=ANDt.segment_typeIN('TABLE','TABLEPARTITION')ANDt.ownerNOTIN('SYS','SYSTEM')ORDERBY5说明SELECTsql_textSELECTsql_text"SQL",executions运行次数buffer_getsdecode(executions0,1,4000"响应时间FROMv$sqlWHEREbuffer_gets/decode(executions,0,1,executions)/4000>10ANDexecutions>0;说明SELECT*FROM(SELECTPARSING_USER_IDSQL_TEXTFROMSELECT*FROM(SELECTPARSING_USER_IDSQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;SELECTse.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,gram,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changesFROMv$sessionse,v$session_waitst,v$sess_iosi,v$processSELECTse.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,gram,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changesFROMv$sessionse,v$session_waitst,v$sess_iosi,v$processprWHEREst.sid=se.sidANDst.sid=si.sidANDse.PADDR=pr.ADDRANDse.sid>6ANDst.wait_time=0ANDst.eventNOTLIKE'%SQL%'ORDERBYphysical_readsDESC;SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,S.osuserosname,P.serial#S_#,P.terminal,P.programprogram,P.background,S.status,RTRIM(SUBSTR(a.sql_text,1,80))SQLFROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)ANDP.spidLIKE'%&1%';CPUPID对应的SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,S.osuserosname,P.serial#S_#,P.terminal,P.programprogram,P.background,S.status,RTRIM(SUBSTR(a.sql_text,1,80))SQLFROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)ANDP.spidLIKE'%&1%';SELECT'++'||S.usernameusername,RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROMv$processSELECT'++'||S.usernameusername,RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)ANDP.spidLIKE'%&&1%';select*fromv$sqlwheresql_idin(selectsql_idfromgv$sessionwherepaddrin(selectaddrfromgv$processwhereprogram='ORACLE.EXE(SHAD)'andspid=188));说明Entervaluefor1:PID（CPUselecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,client_info,osuser,value/60/100selecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,client_info,osuser,value/60/100fromv$sessiona,v$processb,v$sesstatwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvalueselectdf.tablespace_namename,df.file_name"file",f.phyrds"物理读次数",f.phyblkrd"selectdf.tablespace_namename,df.file_name"file",f.phyrds"物理读次数",f.phyblkrd"物理读BLOCKS",f.phywrts"物理写次数",f.phyblkwrt"BLOCKS"fromv$filestatf,dba_data_filesdfwheref.file#=df.file_idorderby说明定期检查，SELECTse.usernameSELECTse.username,se.sid,se.serial#,se.sql_address,se.machine,gram,su.blocks*8192/1024/1024"UsedSpace(M)",su.segtype,su.contentsFROMv$sessionse,v$sort_usagesuWHERE说明多次检查，session FROMv$locked_objectl,dba_objectso,v$sessionsWHEREl.object_id=o.object_idANDl.session_id=s.sidORDERBYo.object_id,xidusnDESC;altersystemkillsession说明多次检查，及早发现并报告锁与等待的对象,Oracle数据库的安全性，包含：检查系统安全信息，定期修改密码，总共[root@racdb1[root@racdb1~]#grep-iacceptedJan2114:36:00racdb1sshd[3455]:Acceptedpasswordfororaclefromport56246ssh2说明系统安全日志文件的目录在/var/log下，主要检查登录成功或失败的用户日志信息。[root@racdb1[root@racdb1~]#grep-iinval/var/log/secure&&grep-ifailedselectproperty_name,property_valuefromdatabase_propertieswhereproperty_namelikeselectfile_name,tablespace_name,bytes,autoextensible,maxbytesfromdba_data_filesorderbyselect,fromv$tablespacet1,v$datafilet2whereselecttablespace_name,file_name,bytesfromdba_data_filesorderbySELECTUPPER(F.TABLESPACE_NAME)"表空间名",D.TOT_GROOTTE_MB"表空间大小(M)",D.TOT_GROOTTE_MBF.TOTAL_BYTES已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'"使用比",F.TOTAL_BYTES空闲空间(M)",F.MAX_BYTES"最大块(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)(SELECTROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)WHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY1;selecttablespace_name,table_namefromuser_tableswhereselect* all_tableswhere TABLESPACE TABLESPACE"TEST"DATAFILE'/u01/app/oracle/oradata/racdb1/TEST01.dbf'100MAUTOEXTENDONNEXT128MMAXSIZE3000MLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOSQL>showparameter integerK,8K、16K、32K、64K等几种大小，ORACLE4194304（由操作系统决定），4194304×DB_BLOCK_SIZE/10244k8K16k32K64kALTERALTERTABLESPACETESTADDDATAFILE'/u01/app/oracle/oradata/racdb1/TEST02.dbf'SIZE100MAUTOEXTENDONNEXT128M MAXSIZE300M;ALTERDATABASEDATAFILE'/u01/app/oracle/oradata/racdb1/TEST01.dbf'RESIZE说明CREATEBIGFILETABLESPACE"BIGFILE03"DATAFILE'/u01/app/oracle/oradata/racdb1/bigdata01.dbf'SIZE100MAUTOEXTENDONNEXT128MMAXSIZE40GLOGGINGEXTENTMANAGEMENTCREATEBIGFILETABLESPACE"BIGFILE03"DATAFILE'/u01/app/oracle/oradata/racdb1/bigdata01.dbf'SIZE100MAUTOEXTENDONNEXT128MMAXSIZE40GLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOSELECTtablespace_name,bigfileFROM说明bigfiletablespaceOracle10g的新特性。Bigfiletablespaces14Gblocksdatafile8KBdatablocksbigfiletablespace32TB。因为BigfileTablespace不能有多个文件，必须保证在同一个磁盘空间有足够的容量.BFT(bigfiletablespace)还受到操作系统的文件系统的限制。理论上我们可以创建最大32T(4G*8K)的表空间。经过测试linux系统不建议超过2T,大文件表空间（bigfile StorageManagement）或其他逻辑卷管理工具（logicalvolumemanager）配合使用，这些工具应该能够支持动态扩展逻辑卷，也能支持striping（数据跨磁盘分布）或RAID。文件系统(块 单个文件大小限 文件系统大小限ext2/3(4K) ext4(4K) [root@racdb1~]#tune2fs-lBlock SQL>selectSQL>selectfile_name,tablespace_name,bytes,autoextensible,maxbytesfromdba_data_filesorderbytablespace_name;SQL>shutdown[oracle@racdb1~]$mv/u01/app/oracle/oradata/data02.dbf/u01/app/oracle/oradata/racdb[oracle@racdb1~]$mv/u01/app/oracle/oradata/data01.dbf/u01/app/oracle/oradata/racdb[oracle@racdb1~]$mv/u01/app/oracle/oradata/TEST02.dbf/u01/app/oracle/oradata/racdbSQL>startupSQL>alter renamefile'/u01/app/oracle/oradata/data02.dbf'toSQL>alter renamefile'/u01/app/oracle/oradata/data01.dbf'toSQL>alter renamefile'/u01/app/oracle/oradata/TEST02.dbf'toSQL>alterdatabaseopen;selectselectfile_name,tablespace_name,bytes,autoextensible,maxbytesfromdba_data_filesorderbytablespace_name;启动数据库到mountalt