ORACLE基本技能技巧.ppt

,基本技能常用技巧,主要内容,Oracle 数据库的组成结构管理临时表空间使用 SPFILE 作为初始化参数文件使用 Profile 加强数据库安全数据库监控调优-statspack备份,2023年11月8日,Oracle 数据库的基本结构,Oracle 服务器,An Oracle server:Is a database management system that provides an open,comprehensive,integrated approach to information managementConsists of an Oracle instance and an Oracle database,Oracle 服务器的主要组件,Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,LibraryCache,DBWR,SMON,PMON,CKPT,LGWR,Others,Userprocess,Serverprocess,PGA,Control files,Datafiles,Database,DatabaseBuffer Cache,Redo Log files,Java Pool,Large Pool,Parameterfile,Archived Log files,Oracle 实例,An Oracle instance:Is a means to access an Oracle databaseAlways opens one and only one databaseConsists of memory and background process structures,Background process structures,Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,LibraryCache,DBWR,SMON,PMON,CKPT,LGWR,Others,DatabaseBuffer Cache,Java Pool,Large Pool,Memory structures,Oracle Database,An Oracle database:Is a collection of data that is treated as a unitConsists of three file types,数据库空间=表空间（由一个或多个数据文件组成）存放 数据对象（表、索引、函数、）=segment=extent,服务器进程,test:/oracle ps-ef|grep oracle oracle 2904150 2379866 0 Jan 27-0:06 oracletest(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)oracle 3866722 1 0 08:50:21-0:00 oracletest(LOCAL=NO)oracle 3870846 1 0 11:34:20-0:00 oracletest(LOCAL=NO)oracle 3895388 1 0 02:37:35-0:00 oracletest(LOCAL=NO)oracle 3903652 1 0 20:14:28-1:28 oracletest(LOCAL=NO),进程的所有者通常为 oracle 用户进程的名称为：oracleSID可以被安全的 kill,服务器进程,test:/oracle topas 或 ps aux|grep oracleName PID CPU%PgSp Owneroracle 561246 36.6 7.1 oracleoracle 2711644 6.7 2.3 rate oracle 3358774 2.0 7.4 oracleoracle 454698 0.1 16.1 oracle,察看 Top CPU 进程找到该进程的 SID,服务器进程,-杀死服务器进程ps-ef|grep-v grep|grep LOCAL=NO|awk print$2|xargs kill-9,杀死服务器进程，以加快实例的关闭,后台进程,test:/oracle ps-ef|grep ora_ oracle 466988 1 0 Jan 06-11:54 ora_smon_test oracle 487480 1 0 Jan 06-15:08 ora_lmon_test oracle 602182 1 0 Jan 06-14:54 ora_dbw0_test oracle 704594 1 0 Jan 06-19:20 ora_pmon_test oracle 737408 1 0 Jan 06-23:33 ora_ckpt_test,进程的所有者通常为 oracle 用户进程的名称为：ora_后台进程类型_SID被 kill 后可能会导致实例终止,后台进程,Maintains and enforces relationships between physical and memory structures:Mandatory background processes:DBWn PMON CKPTLGWR SMONOptional background processes:ARCn LMDn QMNnCJQ0 LMON RECODnnn LMS Snnn LCKn Pnnn,ARCn:Archiver CJQ0:Coordinator Job Queue background processDnnn:Dispatcher LCKn:RAC Lock ManagerInstance LocksLMDn:RAC DLM MonitorRemote LocksLMON:RAC DLM MonitorGlobal LocksLMS:RAC Global Cache ServicePnnn:Parallel Query SlavesQMNn:Advanced QueuingRECO:RecovererSnnn:Shared Server,后台进程,可以通过#ps ef|grep ora_ 命令察看,临时表空间中的空间管理,OPS/RAC 中的每个实例仅创建一个临时段，实例中的所有进程将共享该排序段实例启动后第一个利用磁盘执行排序操作的进程创建临时段；实例关闭后，系统将清空临时表空间临时空间的分配以 extent 为单位进行，并通过 SGA 中的Sort Extent Pool 对这些 extents 进行管理。每个实例会维护一个属于自己的 SEP，并通过视图v$sort_segment 向用户开放,临时表空间中的空间管理,临时表空间已经 100%满了遇到了 ORA-01652“unable to extend temp segment by%s in tablespace%s”错误 查询 v$sort_segment查询 v$tempseg_usage,临时表空间中的空间管理,select s.tablespace_name,s.total_blocks,s.used_blocks from v$sort_segment s;TABLESPACE_NAMETOTAL_BLOCKSUSED_BLOCKS-TEMP442086415104select s.sid,s.program,s.machine,t.extents,t.blocks from v$tempseg_usage t,v$session s where t.session_addr=s.saddr;SIDPROGRAMMACHINEEXTENTSBLOCKS-1247plsqldev.exeJSJZXZXY3384,临时表空间中的空间管理,在 OPS/RAC 中，当一个实例需要临时空间并且 SEP 中没有可用的 extent 时：或者增长临时段或者系统自动从另一个实例 re-assign extent增大参数 PGA_AGGREGAGE_TARGET 参数值以尽量避免磁盘排序,select*from v$sysstat where name like%sort%;STATISTIC#NAMECLASSVALUESTAT_ID-312sorts(memory)64797009872091983730313sorts(disk)6469002533123502314sorts(rows)64603954468743757672740,使用 SPFILE 作为初始化参数文件,初始化参数文件,From Oracle9i onwards,there are two types of initialization parameter file used:Static parameter file,PFILE Persistent server parameter file,SPFILE,数据库的启动关闭,-数据库启动,su oraclelsnrctl startsqlplus/as sysdba“startup,-数据库关闭,su oraclelsnrctl stopsqlplus/as sysdba“shutdown immediate,spfiledb01.ora,初始化参数文件,CONNECT/AS SYSDBASTARTUP,Oracle Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,LibraryCache,DBW0,SMON,PMON,CKPT,LGWR,Others,DatabaseBuffer Cache,Java Pool,Large Pool,PFILEinitSID.ora,文本文件通过文本编辑器进行修改采用手工的方式进行修改所作的修改在实例下次启动时生效仅在实例启动时打开确省的存取目录是$ORACLE_HOME/dbs,SPFILEspfileSID.ora,二进制文件通过 Oracle 服务器进行修改（oem)总是存放在服务器上Ability to make changes persistent across shut down and start up,创建 SPFILE,通过 PFILE 创建 SPFILE(目录必须写全）whereSPFILE-NAME:SPFILE to be createdPFILE-NAME:PFILE creating the SPFILE可以在实例启动之前或者之后执行,CREATE SPFILE=$ORACLE_HOME/dbs/spfileDBA01.oraFROM PFILE=$ORACLE_HOME/dbs/initDBA01.ora;,创建 SPFILE,SQL sqlplus/as sysdbaConnected to an idle instance.SQL create pfile from spfile;create pfile from spfile*ERROR at line 1:ORA-27037:unable to obtain file statusIBM AIX RISC System/6000 Error:2:No such file or directoryAdditional information:3SQL create pfile from spfile=/dev/rjf_spfile;File created.,不指定文件名，系统将使用缺省值，或者 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=jzjftest1.thread=1test2.thread=2test1.undo_tablespace=UNDOTBS1test2.undo_tablespace=UNDOTBS2,检查当前是否使用了 SPFILE,察看初始化参数 spfile,sqlplus/as sysdbaSQL show parameter spfileNAME TYPE VALUE-spfile string/dev/rjf_spfile,修改 SPFILE 中的参数,修改参数值指明是临时修改还是永久保存,ALTER SYSTEM SET undo_tablespace=UNDO2;,ALTER SYSTEM SET undo_tablespace=UNDO2 SCOPE=BOTH;ALTER SYSTEM SET undo_retention=3600 SCOPE=memory SID=test1;,STARTUP 命令,处理顺序:spfileSID.orainitSID.oraSpecified PFILE can override precedence.PFILE 中可以指明使用 SPFILE,STARTUP PFILE=$ORACLE_HOME/dbs/inittest1.ora,SPFILE=/database/startup/spfiletest1.ora,使用 Profile 加强数据库安全,使用 Profile 加强数据库安全,A profile is a named set of password and resource limits.通过 CREATE USER 或者 ALTER USER command 将 profile 赋予用户概要文件可以被启用或者禁用Profiles can relate to the DEFAULT profile.,User,Password expiration and aging,Password verification,Setting up profiles,Password 管理,Password Management For greater control over database security,Oracle password management is controlled by database administrators with profiles.:Account locking:Enables automatic locking of an account when a user fails to log in to the system in the specified number of attemptsPassword aging and expiration:Enables the password to have a lifetime,after which it expires and must be changedPassword history:Checks the new password to ensure that the password is not reused for a specified amount of time or a specified number of password changesPassword complexity verification:Performs a complexity check on the password to verify that it is complex enough to provide protection against intruders who might try to break into the system by guessing the password,Set up password management by using profiles and assigning them to users.Lock,unlock,and expire accounts using the CREATE USER or ALTER USER command.Password limits are always enforced.To enable password management,run the utlpwdmg.sql script as the user SYS.,启用 Password 管理,账号锁定,alter user to lock,Password 过期,Password 历史,Parameter,Number of days before a password can be reusedMaximum number of changes required before a password can be reused,PASSWORD_REUSE_TIMEPASSWORD_REUSE_MAX,Description,Password 校验,用户定义的 Password 函数,This function must be created in the SYS schema and must have the following specification:,function_name(userid_parameter IN VARCHAR2(30),password_parameter IN VARCHAR2(30),old_password_parameter IN VARCHAR2(30)RETURN BOOLEAN,Password 校验函数 VERIFY_FUNCTION,Minimum length is four characters.Password should not be equal to username.Password should have at least one alphabetic,one numeric,and one special character.Password should differ from the previous password by at least three letters.,CREATE PROFILE grace_5 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_TIME 30 PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_GRACE_TIME 5;,设置 Password 选项：创建 Profile,ALTER USER“test PROFILE“grace5,ALTER PROFILE default LIMITFAILED_LOGIN_ATTEMPTS 3PASSWORD_LIFE_TIME 60PASSWORD_GRACE_TIME 10;,设置 Password 选项：修改 Profile,Use ALTER PROFILE to change password limits.,ALTER USER“test PROFILE LIMIT,DROP PROFILE developer_prof;,DROP PROFILE developer_prof CASCADE;,删除 Profile,Drop the profile using DROP PROFILE command.DEFAULT profile cannot be dropped.CASCADE revokes the profile from the user to whom it was assigned.,数据库基本监控,-查看表空间大小,select*from(select sum(bytes)/(1024*1024)as free_space(M),tablespace_name from dba_free_spacegroup by tablespace_name)order by free_space(M);,-监控表空间空余,select t.tablespace_name,round(sum(bytes/(1024*1024),0)ts_size from dba_tablespaces t,dba_data_files d where t.tablespace_name=d.tablespace_name group by t.tablespace_name;,数据库基本监控,-通过操作系统进程查看用户执行操作,select created，object_name from user_objects where object_name=upper(,-查看表的创建时间及占空间大小,-通过SPID 查语句select sql_text from v$sql where address in(select sql_address from v$session where sid in(select sid from v$session where paddr in(select addr from v$process where spid=3561);,创建用户,-用户创建,CREATE USER ddbh IDENTIFIED BY ddbh DEFAULT TABLESPACE TSPPP TEMPORARY TABLESPACE temp;,-用户授权,grant connect,resources,imp_full_database,exp_full_database,create public synonym,drop public synonym to ddbh;GRANT SELECT ANY TABLE TO ddbh;GRANT SYSDBA TO ddbh,GRANT UNLIMITED TABLESPACE TO ddbh;,对象的移动,alter table STAFF_STATION move tablespace USERS;alter index PK_DISTRIBUTE_INFO rebuild tablespace USERS;alter table CNC_CALL_STAT_TMP move partition PART16 tablespace ts_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 alter system set timed_statistics=true;System altered,调优工具statspack-安装,安装：$ORACLE_HOME/RDBMSADMINsqlplus“/AS SYSDBA”sqlspcreateSpecify PERFSTAT users default tablespace 输入 default_tablespace 的值:perfstat Using perfstat for the default tablespace 用户已更改。Specify PERFSTAT users temporary tablespace 输入 temporary_tablespace 的值:temp,调优工具statspack-使用,使用：运行statspack.snap可以产生系统快照，运行两次，然后执行spreport.sql就可以生成一个基于两个时间点的报告。SQLexecute statspack.snap PL/SQL procedure successfully completed.SQLexecute statspack.snap PL/SQL procedure successfully completed.SQLspreport.sql 在当前目录$ORACLE_HOME/RDBMSADMIN下生成报告sp_1_2.lst可以用vi查看该文件。找出性能低的语句等信息,数据库备份,Exp/impArchive 归档数据文件copy冷备份,