Oracleoem与存储纯自己手写.docx

目录Spfile损坏，没有pfile，如何启动oracle1开启监听，只允许一个IP访问5查看Oracle性能、查看Oracle统计活动回话16编写存储过程的脚本25工作年限操作一年的，工资小于6K涨20%；25收缩表空间，提高表空间的IO31Spfile损坏，没有pfile，如何启动oracle关于Spfile文件首先解释 Spfile文件 里面包含了orcale 运行的参数文件 和 所启动进程文件内容采用二进制代码 是我们不能用系统账户VI 和VIM 编辑的 安装orcale 自动生成 也成为oracle 系统参数文件开启oracle 自动读取 并且加载文件Pfile 称之为 参数文件 内容和 Spfile 文件大致相同俗话成为 备用参数文件当你的Spfile 丢失 系统将会自动读取 Pfile文件Spfile 在系统中以格式存在在 /oracle/app/oracle/product/10.2.0/db_1/dbs/路径下Pfile 默认是没有的 需要人为创建 你可以vi 在上述路径下编辑名称为 initTEST.ora这里的TEST 是你在设置 oracle用户模式下 填加环境变量的ORACLE_SID=TEST的当然你可以选择 在oracle里生成一个 Pfile 文件命令如下这样我们在查看 /oracle/app/oracle/product/10.2.0/db_1/dbs/与上次对比 多了一个 INITTEST.ORA文件VIM 打开 INITTEST.ORA加载各种配置参数和进程数 等等然后查看一下属主 和 属组 这样我们完全可以备份 一份initTEST.ora文件到其他目录下 和 存储设备下 玩意 Spfile 文件丢失 这将是你的救命稻草如果你没有生成 Pfile文件系统 但是 你的Spfile 文件损坏 或者 优化参数 导致 无法启动oracle 这是你必须手动创建 在你的linux系统下关于 Pfile中的内容参数 你可以网上down 一下如果网上各种大神文档让你眼花缭乱 迷茫 感到无从下手没关系 我们可以通过 oracle 日志文件当你启动 oracle 时候 你所加载的参数文件 会被写到日志之中首先你要知道日志存放的路径oraclesql $ cd /oracle/app/admin/TEST/bdump/在这个路径下 日志名称 alert_TEST.log你将不再用tail 来查看 因为这么看是不全的所以 vim 进入alert_TEST.log 里面查看截取出 加载 Spfile的字段从标记黑色字段为起点 此黑色字段为结尾 此部分复制到 你所vi 出的initTEST.ora 文件中 记住 权限 和 属主 属组完成后 再次 重启 oracleStartup force；完成 如果你在优化 参数的时候 出现错误 你可以 rm fr SpfileTEST.ora 在把你创建的 initTEST.ora 添加进来到此 Spfile文件丢失问题 解决开启监听，只允许一个IP访问所谓监听 就是一个客户端 远程连接到数据库 如同 在linux 下的SSHOracle Server 开启监听而客户端 开启 传输配置Oracle 监听 在Rhel 下 以 oracle 用户登录 然后 在使用 图形配置文件如果你想使用字符配置 将在下面看到在linux 下输入netmgr 进入界面 打开local 进入 Listens这是你会发现 什么也没有 很正常 看到左上方的小绿色加号了吗建立一个监听定义一个名字看到这个了选择 listening locations 加入一个监听对象Add Address 后会出下下面的一个界面HOST 会出现你的主机名 这是你最好把他换成IP （你的IP 切记）这样更精确 更便于查找 当然我们只是一个实验环境进入公司 会有一些特殊条件 到时候在另当别论这步完成后选择Add address出现这时候 你需要 把Global Database Name 和 SID 修改成你在.bash_profile 文件下的 ORACLE_SID=的名字完成后选择第二个 Save保存配置这时候 进入Ls一下你发现你刚才的操作生成了一个 listener.ora文件Vim listener.ora 里面的内容就是你在图形界面配置的 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = TEST) (ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1) (SID_NAME = TEST) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 41)(PORT = 1521) )所以 字符配置 就简单了你可以vim listener.ora 文件 输入 上面的内容修改 HOST 字段的IP 就可以了服务器端的监听配置就完成了开启 监听oraclesql $ lsnrctl start当你修改了 配置文件 不会像在 linux服务那样有restart 但是会有 reload、和 stop现在配置 客户端的传输功能下面的配置环境是在Linux 服务器 下必须要 有Oracle Client 端 可以支持 传输功能这里我使用另一台Rhel 5.5 Oracle Server配置同样 在oracle 用户下 使用netmgr 命令但是这时候我们选择的还是老方法 绿色小加号 这个位置输入你的一个名字你可以随便输入但是这里便于理解DB241Next 选择 TCP/IPNext看见host name 了这是你需要 添加你刚才配置的Oracle Server HOST 就是他的IP NEXT同理 TEST 你是在服务器端设置的 名字 Test这时候完成了 但是当你点击 TEST 测试的时候 你会发现失败因为 你所选择的Scott 用户没有权限的保存现在 我们在进入Ls下你会发现 tnsnames.ora 文件Vim tnsnames.oraDB241 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 41)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = TEST) ) )这里面的内容 就是你图形配置的 传输文件内容所以文本配置 就是 Vim tnsnames.ora 添加上面的字段修改 DB241 这个位置的 名字 和 HOST 字段的 设置成 Oracle Server的主机名 或者 IP 等我们可以监听多个 主机 就是 将上述字段 复制多次 名字 和 HOST 修改成对应的一个或多个 Oracle Server的这样就完成了 我们来进行登陆oraclesql $ sqlplus sys/19891101DB186 as sysdba这样是登陆 sys 用户如果你想登陆scott oraclesql $ sqlplus scott/19891101DB186 去掉 as sysdba 你可以查看 Oracle Server 上的表Select * from wcl； 等等 根据你的表来查看 修改 建立等等如果你想限制 IP 或者 只允许一个人访问你 还是 netmgr 管理工具这添加 你客户端服务器的 iP查看Oracle性能、查看Oracle统计活动回话查看Oracle性能 方法很多种 我们今天将会用到 OEM 这块软件先来解释下 什么是OEM Oracle企业管理器（Oracle Enterprise Manager，OEM）是Oracle提供的一个基于Web的图形化数据库管理工具。通过OEM，用户可以完成几乎所有的原来只能通过命令行方式完成的工作，包括数据库对象、用户权限、数据文件、定时任务的管理，数据库参数的配置，备份与恢复，性能的检查与调优等。下面讲一下 OEM 安装过程首先 你要知道你收否安装了 OEM 因为 在你安装oracle 的时候 有一个 OEM 勾选的选项 如果你选择安装了 就已经安装了如果你没有勾选就没有安装所以我们先来查看一下 OEM 状态在 linux下使用 emctl status dbconsole 看到 底下的 not found 显而易见的 就是我没有安装第一步 先开启 监听 Lsnrctl start 下来我安装 OEM 指令很简单如果是重装 先停掉Emctl stop dbconsole重装指令Emca repos recreate 看到 Listener port number：1521 了吧 这就要依靠监听端口的安装中我们进入SQL 里面查看用户 你会发现 有 DBSNMP 用户这样OEM 就安装了 并创建了 对应的用户修改一下 DBSNMP 用户密码现在 配置 OEM 使用指令 emca -config dbcontrol db 配置完成 查看状态 /oracle/app/oracle/product/10.2.0/db_1/sql_TEST/sysman/log 日志存放路径现在已经开启状态了我们上 网页测试一下登陆 查看 Oracle 的 使用状况 和 性能内存使用状况归档日志 当然我们根本没有开启 归档日志 控制文件 哇喔 在OEM 你能看到 你想看的 一切 Oracle 信息 功能如此强大如何查看 Oracle 回话数 指令很简单在你的 SQL 里面 直接看Select count（*） from v$session当然 你如果查看 详细信息 Select * from v$session 就可以了详细信息 如此的嘈杂还一种 性能统计 貌似 还不怎么会 AWR 试试 select * from v$sesstatSelect * from v$sysstat摘自网上大神资料 如何有效地诊断性能问题，则进行性能统计必不可少。Oracle能够产生基于整个系统表现的、或者基于会话的、或者基于单个SQL语句的统计数据。Oracle也可以在段和服务追踪和获取统计数据的累积值。当我们关注某个方面的性能问题时，我们只需将该范围的统计数据拿来，并作出判断。一种典型方法是取得采样期间统计数据的“首”“尾”，判断期间的差异，并据此得出新能问题所在的判断。统计数据，通常是自系统启动后到当前的累计统计数据，可以通过诸如V$SESSTAT V$SYSSTAT等动态视图获得。因此，任何一次数据库系统的启动关闭操作都会导致这些统计数据的重置。Oracle 10g/11g 包含了如下类型的统计数据： AWR的统计数据，关于系统运行的累计统计; 度量值，关于累计统计差异性描述的变化值; 抽样数据，关于激活会话的统计数据。Oracle在10g版本中提供了自动的工作量资料档案库功能，简称AWR，就是自动进行性能取样，数据涵盖了除会话级别统计以外的大多统计。这个过程在一段时期内重复进行，每次取样结果称为一张AWR快照。每张快照之间的差异描述了系统性能状态的改变，因此，这种快照代表了系统在取样期间内的改变。除此之外，Oracle还自动收集另一种类型的统计，称之为：度量值。所谓度量，就是某个累计统计数据，在取样期间内改变的程度、比率。“度量”能涵盖各种性能方面，包括响应时间、事务处理吞吐量、数据库调用数量、磁盘读写数量等。度量值可以通过对V$ 类型的动态视图中获得，v$视图中的数据是一个相当小的时间间隔内的平均值，一般而言是60秒的期间。通过v$视图可以获得近期的度量值，远期的度量值则可以通过AWR快照获得。还有一种Oracle收集的统计数据，称之为抽样数据。Oracle10g中新增了抽样数据的取样机制，称之为自动的会话历史记录 ( ASH )。抽样数据由ASH执行完成。ASH抽样所有活跃会话当前状态，抽样数据被收集到内存中，通过V$视图访问。在AWR进行工作载荷取样时，这类数据也被自动搜集。Oracle10g提供了一种诊断系统性能问题的有力方法：对比法。首先，我们需要建立统计基准线。这个基准线可以采用系统在高效、稳定运行期间的统计数据集合。依据这个基准线集合，在系统出现性能出现问题的情况下，进行对比，发现问题所在。这种方法有效地解决了当我们面对数以千计的数据库性能统计数据而一筹莫展的问题，避免我们陷入统计数据的海洋。统计基准线的获得通过AWR实现。通过AWR，我们指定度量基准线和保存AWR快照的时间范围或一种类似指定，并把这段期间的统计数据作为统计基准线。显然，这个时间范围指定应慎重选择。最合理的度量基线，应该是在系统正常载荷状态下，具有良好性能状态时，获得的累计统计。这样，在系统遭遇性能障碍时，通过好坏比较得到问题根源。这里所说的所有统计数据实时性能数据，或者AWR历史快照数据，我们都可以通过Oracle企业管理器(OEM)查看。不仅如此，企业管理器也能用来捕捉与AWR统计数据相关的操作系统和网络统计的数据。稍后试验编写存储过程的脚本工作年限操作一年的，工资小于6K涨20%；我们先来建立两个表 便于实验我使用脚本 建脚本 很简单在 /home/oracle/下vim “脚本name”.sql将你在 sql 上面要执行的 一句一句写上就好这里实用了 to_date 转换时间格式方便来看绕过回收站彻底删除表创建成功查看时间 查看以后 select ,b.wage*(1+0.2) as salary ,a.time as ruzhi from Staffrecruited a,Currentsalary b where = and b.time-a.time =365 and wage=365 and b.wage12 and wage=365 and wage表组 表 列 values！都是关系型数据库 所以结构大致相同表空间 是一个逻辑单位 表空间 把数据存到硬盘的实际的数据文件中 表空间 能将表数据 自动平均分配到各个数据文件中但是 自动平均分配的不好 不能想ASM 那样平均 空间表大致分为两类 系统表空间 System 与 Sysaux 创建数据库 时自动创建包含数据字典SYSAUX 是 分析工作 抓一些值在 里面非系统表空间就是 用户创建的表空间永久表 就是 存放数据表 以及 索引的 一般不会被删除 如果里面保存的是重要数据 UNDO 表 存放 Rollback 的回滚 很重要 所以 一般给UNDO 的都是自动扩展 临时： 存放 排序数据 （order by ）所以临时文件里 不会存放真实的数据现在 先来创建一个永久表指令Create tablespace wmn datafile /home/oracle/wmn.dbf size 100M 查看 数据文件信息创建UNDO 表 Create undo tablespace undowmn datafile /oracle/app/oradata/WMN/undowmn.dbf size 100M;创建临时表 Create temporary tablespace tempwmn fempfile /oracle/app/oracle/WMN/tempwmn.dbf size 50M;关于 怎么缩减缩减针对于永久表 但是在现实中 很少用到 Alter database datafile /home/oracle/wmn.dbf resize 50M;针对于 IO 问题 因为 一个表空间对应多个文件 所以 我们在生成一个数据文件 让 永久表空间 自动平均写入两个数据文件 可以提升IO alter tablespace wmn add datafile /home/oracle/wmn1.dbf size 100M一个 表空间对应两个数据文件现在添加一个数据 自动平均写入 不平均 了好吧 现在来说下 UNDO 表 UNDO 表可以有多个 但是同一时间 只能使用一个 对用UNDO 我们 会设成自动扩展 如果写满 导致错误 出现 回写 所以占双份IO 很容易 让硬盘扛不住 造成死机 Alter database datafile /oracle/app/oradata/WMN/undowmn.dbf autoextend on;切换 UNDO 表Alter system set undo_tablespace=undowmn;实验一下 效果很明显了把关于临时表 因为里面不会存数据 索引就像地下铁 数据来了走 走了来一样 一般都不用管的额外扩展 Oracle Merge into详细介绍/*Merge into 详细介绍MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERTUPDATE。*/*语法：MERGE INTO schema . table t_aliasUSING schema . table | view | subquery t_aliasON ( condition )WHEN MATCHED THEN merge_update_clauseWHEN NOT MATCHED THEN merge_insert_clause;*/语法：MERGE INTO your table-name rename your table hereUSING ( write your query here )rename your query-sql and using just like a tableON (conditional expression here AND .)WHEN MATHED THEN here you can execute some update sql or something else WHEN NOT MATHED THEN execute something else here ! /*我们还是以sql中的case应用中的表为例。在创建另两个表fzq1和fzq2*/-全部男生记录create table fzq1 as select * from fzq where sex=1;-全部女生记录create table fzq2 as select * from fzq where sex=0;/*涉及到两个表关联的例子*/-更新表fzq1使得id相同的记录中chengji字段1，并且更新name字段。-如果id不相同，则插入到表fzq1中.-将fzq1表中男生记录的成绩1，女生插入到表fzq1中merge into fzq1 aa -fzq1表是需要更新的表using fzq bb - 关