ORACLE基本技能技巧.ppt

基本技能常用技巧 主要内容 Oracle数据库的组成结构管理临时表空间使用SPFILE作为初始化参数文件使用Profile加强数据库安全数据库监控调优 statspack备份 2020年2月24日 Oracle数据库的基本结构 Oracle服务器 AnOracleserver Isadatabasemanagementsystemthatprovidesanopen comprehensive integratedapproachtoinformationmanagementConsistsofanOracleinstanceandanOracledatabase Oracle服务器的主要组件 Instance SGA RedoLogBuffer SharedPool DataDictionaryCache LibraryCache DBWR SMON PMON CKPT LGWR Others Userprocess Serverprocess PGA Controlfiles Datafiles Database DatabaseBufferCache RedoLogfiles JavaPool LargePool Parameterfile ArchivedLogfiles Oracle实例 AnOracleinstance IsameanstoaccessanOracledatabaseAlwaysopensoneandonlyonedatabaseConsistsofmemoryandbackgroundprocessstructures Backgroundprocessstructures Instance SGA RedoLogBuffer SharedPool DataDictionaryCache LibraryCache DBWR SMON PMON CKPT LGWR Others DatabaseBufferCache JavaPool LargePool Memorystructures OracleDatabase AnOracledatabase IsacollectionofdatathatistreatedasaunitConsistsofthreefiletypes 数据库空间 表空间 由一个或多个数据文件组成 存放数据对象 表 索引 函数 segment extent 服务器进程 test oracle ps ef greporacleoracle290415023798660Jan27 0 06oracletest DESCRIPTION LOCAL YES ADDRESS PROTOCOL beq oracle38667221008 50 21 0 00oracletest LOCAL NO oracle38708461011 34 20 0 00oracletest LOCAL NO oracle38953881002 37 35 0 00oracletest LOCAL NO oracle39036521020 14 28 1 28oracletest LOCAL NO 进程的所有者通常为oracle用户进程的名称为 oracleSID可以被安全的kill 服务器进程 test oracle topas或psaux greporacleNamePIDCPU PgSpOwneroracle56124636 67 1oracleoracle27116446 72 3rateoracle33587742 07 4oracleoracle4546980 116 1oracle 察看TopCPU进程找到该进程的SID 服务器进程 杀死服务器进程ps ef grep vgrep grepLOCAL NO awk print 2 xargskill 9 杀死服务器进程 以加快实例的关闭 后台进程 test oracle ps ef grepora oracle46698810Jan06 11 54ora smon testoracle48748010Jan06 15 08ora lmon testoracle60218210Jan06 14 54ora dbw0 testoracle70459410Jan06 19 20ora pmon testoracle73740810Jan06 23 33ora ckpt test 进程的所有者通常为oracle用户进程的名称为 ora 后台进程类型 SID被kill后可能会导致实例终止 后台进程 Maintainsandenforcesrelationshipsbetweenphysicalandmemorystructures Mandatorybackgroundprocesses DBWnPMONCKPTLGWRSMONOptionalbackgroundprocesses ARCnLMDnQMNnCJQ0LMONRECODnnnLMSSnnnLCKnPnnn ARCn ArchiverCJQ0 CoordinatorJobQueuebackgroundprocessDnnn DispatcherLCKn RACLockManager InstanceLocksLMDn RACDLMMonitor RemoteLocksLMON RACDLMMonitor GlobalLocksLMS RACGlobalCacheServicePnnn ParallelQuerySlavesQMNn AdvancedQueuingRECO RecovererSnnn SharedServer 后台进程 可以通过 ps ef grepora 命令察看 临时表空间中的空间管理 OPS RAC中的每个实例仅创建一个临时段 实例中的所有进程将共享该排序段实例启动后第一个利用磁盘执行排序操作的进程创建临时段 实例关闭后 系统将清空临时表空间临时空间的分配以extent为单位进行 并通过SGA中的SortExtentPool对这些extents进行管理 每个实例会维护一个属于自己的SEP 并通过视图v sort segment向用户开放 临时表空间中的空间管理 临时表空间已经100 满了遇到了ORA 01652 unabletoextendtempsegmentby sintablespace s 错误查询v sort segment查询v tempseg usage 临时表空间中的空间管理 selects tablespace name s total blocks s used blocksfromv sort segments TABLESPACE NAMETOTAL BLOCKSUSED BLOCKS TEMP442086415104selects sid s program s machine t extents t blocksfromv tempseg usaget v sessionswheret session addr s saddr SIDPROGRAMMACHINEEXTENTSBLOCKS 1247plsqldev exeJSJZX ZXY3384 临时表空间中的空间管理 在OPS RAC中 当一个实例需要临时空间并且SEP中没有可用的extent时 或者增长临时段或者系统自动从另一个实例re assignextent增大参数PGA AGGREGAGE TARGET参数值以尽量避免磁盘排序 select fromv sysstatwherenamelike sort STATISTIC NAMECLASSVALUESTAT ID 312sorts memory 64797009872091983730313sorts disk 6469002533123502314sorts rows 64603954468743757672740 使用SPFILE作为初始化参数文件 初始化参数文件 FromOracle9ionwards therearetwotypesofinitializationparameterfileused Staticparameterfile PFILEPersistentserverparameterfile SPFILE 数据库的启动关闭 数据库启动 su oraclelsnrctlstartsqlplus assysdba startup 数据库关闭 su oraclelsnrctlstopsqlplus assysdba shutdownimmediate spfiledb01 ora 初始化参数文件 CONNECT ASSYSDBASTARTUP OracleInstance SGA RedoLogBuffer SharedPool DataDictionaryCache LibraryCache DBW0 SMON PMON CKPT LGWR Others DatabaseBufferCache JavaPool LargePool PFILEinitSID ora 文本文件通过文本编辑器进行修改采用手工的方式进行修改所作的修改在实例下次启动时生效仅在实例启动时打开确省的存取目录是 ORACLE HOME dbs SPFILEspfileSID ora 二进制文件通过Oracle服务器进行修改 oem 总是存放在服务器上Abilitytomakechangespersistentacrossshutdownandstartup 创建SPFILE 通过PFILE创建SPFILE 目录必须写全 whereSPFILE NAME SPFILEtobecreatedPFILE NAME PFILEcreatingtheSPFILE可以在实例启动之前或者之后执行 CREATESPFILE ORACLE HOME dbs spfileDBA01 ora FROMPFILE ORACLE HOME dbs initDBA01 ora 创建SPFILE SQL sqlplus assysdba Connectedtoanidleinstance SQL createpfilefromspfile createpfilefromspfile ERRORatline1 ORA 27037 unabletoobtainfilestatusIBMAIXRISCSystem 6000Error 2 NosuchfileordirectoryAdditionalinformation 3SQL createpfilefromspfile dev rjf spfile Filecreated 不指定文件名 系统将使用缺省值 或者spfile参数值 SPFILE内容示例 background dump dest home dba01 ADMIN BDUMP control files home dba01 ORADATA u01 ctrl01 ctl core dump dest home dba01 ADMIN CDUMP db block size 8192 db name jzjf test1 thread 1test2 thread 2test1 undo tablespace UNDOTBS1 test2 undo tablespace UNDOTBS2 检查当前是否使用了SPFILE 察看初始化参数spfile sqlplus assysdba SQL showparameterspfileNAMETYPEVALUE spfilestring dev rjf spfile 修改SPFILE中的参数 修改参数值指明是临时修改还是永久保存 ALTERSYSTEMSETundo tablespace UNDO2 ALTERSYSTEMSETundo tablespace UNDO2SCOPE BOTH ALTERSYSTEMSETundo retention 3600SCOPE memorySID test1 STARTUP命令 处理顺序 spfileSID orainitSID oraSpecifiedPFILEcanoverrideprecedence PFILE中可以指明使用SPFILE STARTUPPFILE ORACLE HOME dbs inittest1 ora SPFILE database startup spfiletest1 ora 使用Profile加强数据库安全 使用Profile加强数据库安全 Aprofileisanamedsetofpasswordandresourcelimits 通过CREATEUSER或者ALTERUSERcommand将profile赋予用户概要文件可以被启用或者禁用ProfilescanrelatetotheDEFAULTprofile User Passwordexpirationandaging Passwordverification Settingupprofiles Password管理 PasswordManagementForgreatercontroloverdatabasesecurity Oraclepasswordmanagementiscontrolledbydatabaseadministratorswithprofiles Accountlocking EnablesautomaticlockingofanaccountwhenauserfailstologintothesysteminthespecifiednumberofattemptsPasswordagingandexpiration Enablesthepasswordtohavealifetime afterwhichitexpiresandmustbechangedPasswordhistory ChecksthenewpasswordtoensurethatthepasswordisnotreusedforaspecifiedamountoftimeoraspecifiednumberofpasswordchangesPasswordcomplexityverification Performsacomplexitycheckonthepasswordtoverifythatitiscomplexenoughtoprovideprotectionagainstintruderswhomighttrytobreakintothesystembyguessingthepassword Setuppasswordmanagementbyusingprofilesandassigningthemtousers Lock unlock andexpireaccountsusingtheCREATEUSERorALTERUSERcommand Passwordlimitsarealwaysenforced Toenablepasswordmanagement runtheutlpwdmg sqlscriptastheuserSYS 启用Password管理 账号锁定 alterusertolock Password过期 Password历史 Parameter NumberofdaysbeforeapasswordcanbereusedMaximumnumberofchangesrequiredbeforeapasswordcanbereused PASSWORD REUSE TIMEPASSWORD REUSE MAX Description Password校验 用户定义的Password函数 ThisfunctionmustbecreatedintheSYSschemaandmusthavethefollowingspecification function name userid parameterINVARCHAR2 30 password parameterINVARCHAR2 30 old password parameterINVARCHAR2 30 RETURNBOOLEAN Password校验函数VERIFY FUNCTION Minimumlengthisfourcharacters Passwordshouldnotbeequaltousername Passwordshouldhaveatleastonealphabetic onenumeric andonespecialcharacter Passwordshoulddifferfromthepreviouspasswordbyatleastthreeletters CREATEPROFILEgrace 5LIMITFAILED LOGIN ATTEMPTS3PASSWORD LOCK TIMEUNLIMITEDPASSWORD LIFE TIME30PASSWORD REUSE TIME30PASSWORD VERIFY FUNCTIONverify functionPASSWORD GRACE TIME5 设置Password选项 创建Profile ALTERUSER test PROFILE grace5 ALTERPROFILEdefaultLIMITFAILED LOGIN ATTEMPTS3PASSWORD LIFE TIME60PASSWORD GRACE TIME10 设置Password选项 修改Profile UseALTERPROFILEtochangepasswordlimits ALTERUSER test PROFILE LIMIT DROPPROFILEdeveloper prof DROPPROFILEdeveloper profCASCADE 删除Profile DroptheprofileusingDROPPROFILEcommand DEFAULTprofilecannotbedropped CASCADErevokestheprofilefromtheusertowhomitwasassigned 数据库基本监控 查看表空间大小 select from selectsum bytes 1024 1024 as free space M tablespace namefromdba free spacegroupbytablespace name orderby free space M 监控表空间空余 selectt tablespace name round sum bytes 1024 1024 0 ts sizefromdba tablespacest dba data filesdwheret tablespace name d tablespace namegroupbyt tablespace name 数据库基本监控 通过操作系统进程查看用户执行操作 selectcreated object namefromuser objectswhereobject name upper 查看表的创建时间及占空间大小 通过SPID查语句selectsql textfromv sqlwhereaddressin selectsql addressfromv sessionwheresidin selectsidfromv sessionwherepaddrin selectaddrfromv processwherespid 3561 创建用户 用户创建 CREATEUSERddbhIDENTIFIEDBYddbhDEFAULTTABLESPACETSPPPTEMPORARYTABLESPACEtemp 用户授权 grantconnect resources imp full database exp full database createpublicsynonym droppublicsynonymtoddbh GRANTSELECTANYTABLETOddbh GRANTSYSDBATOddbh GRANTUNLIMITEDTABLESPACETOddbh 对象的移动 altertableSTAFF STATIONmovetablespaceUSERS alterindexPK DISTRIBUTE INFOrebuildtablespaceUSERS altertableCNC CALL STAT TMPmovepartitionPART16tablespacets user tmp 表及索引的移动 调优工具statspack 安装 为了能够顺利安装和运行Statspack你可能需要设置以下系统参数 1 job queue processes为了能够建立自动任务 执行数据收集 该参数需要大于0 你可以在初试化参数文件中修改该参数 2 timed statistics收集操作系统的计时信息 这些信息可被用来显示时间等统计信息 优化数据库和SQL语句 要防止因从操作系统请求时间而引起的开销 请将该值设置为False 使用statspack收集统计信息时建议将该值设置为TRUE 否则收集的统计信息大约只能起到10 的作用 将timed statistics设置为True所带来的性能影响与好处相比是微不足道的 该参数使收集的时间信息存储在在V SESSTATS和V SYSSTATS动态性能视图中 Timed statistics参数可以在实例级进行更改SQL altersystemsettimed statistics true Systemaltered 调优工具statspack 安装 安装 ORACLE HOME RDBMS ADMIN sqlplus ASSYSDBA sql spcreateSpecifyPERFSTATuser sdefaulttablespace输入default tablespace的值 perfstatUsingperfstatforthedefaulttablespace用户已更改 SpecifyPERFSTATuser stemporarytablespace输入temporary tablespace的值 temp 调优工具statspack 使用 使用 运行statspack snap可以产生系统快照 运行两次 然后执行spreport sql就可以生成一个基于两个时间点的报告 SQL executestatspack snapPL SQLproceduresuccessfullycompleted SQL executestatspack snapPL SQLproceduresuccessfullycompleted SQL spreport sql在当前目录 ORACLE HOME RDBMS ADMIN下生成报告sp 1 2 lst可以用vi查看该文件 找出性能低的语句等信息 数据库备份 Exp impArchive归档数据文件copy冷备份