MonthApril 2014

OCM考试中涉及到的数据仓库知识点整理

OCM考试中涉及到的数据仓库知识点整理

ORACLE官网提供的考纲如下:
Data Warehouse Management

[  ]         Create external tables (Data Loader and Data Pump drivers)
[  ]         Implement Data Pump export and import jobs for data transfer
[  ]         Implement Data Pump to and from remote databases
[  ]         Implement Replication/Streams
[  ]         Configure and manage master replication
[  ]         Configure and manage distributed materialized views
[  ]         Configure and manage Streams for replication

其实涉及到的知识点只有:

外部表,数据泵,流复制,快速刷新物化视图,可更新物化视图,这几个知识点其实不难,但是包含的内容很多,容易出错的有物化视图和流复制,后面我会花点时间做实验,把重点再整理一下。

ORACLE10g OCM 考试流程图

ORACLE10g OCM 考试流程图

 

数据仓库海量数据表拆分和并行

数据仓库海量数据表拆分和并行
表拆分
1.数据仓库海量数据计算解决思路:表拆分
起因:单个表数据量太大,计算时间太长,需要优化

2.表拆分的实际应用思路:
按照某列,做HASH计算,得到一个整数
oracle提供了包dbms_utility
function get_hash_value(name varchar2, base number, hash_size number)
return number
根据键值把数据插入到不同的表,分别计算,然后再汇总。
或者自己定义拆分的计算函数,例如,利用mod函数,利用数字列的尾号等等,只要能把数据均匀分开即可。

Continue reading

bitand( )函数用法

我们查看oracle的底层表和视图的时候,经常会发现bitand函数,所以有必要了解一下。

bitand( ) 函数
返回两个数值型数值在按位进行 AND 运算后的结果。
语法
BITAND(nExpression1, nExpression2)
参数
nExpression1, nExpression2

指定按位进行 AND 运算的两个数值。
如果 nExpression1 和 nExpression2 为非整数型,那么它们在按位进行 AND 运算之前转换为整数。

返回值类型
数值型
说明
BITAND( ) 将 nExpression1 的每一位同 nExpression2 的相应位进行比较。
如果 nExpression1 和 nExpression2 的位都是 1,相应的结果位就是 1;
否则相应的结果位是 0。

下表列出对 nExpression1 和 nExpression2 按位进行 AND 运算的结果:

nExpression1位 nExpression2位 结果位
0 0 0
0 1 0
1 1 1
1 0 0

bitand( ) 函数示例
x = 5 && 二进制为 0101
y = 6 && 二进制为 0110
? bitand(x,y) && 返回值 4,二进制为 0100

———————————————————————
ORACLE中的BITOR和BITXOR
ORACLE中只有BITAND而没有BITOR, BITXOR

原因是,有了BITAND, 很容易实现BITOR和BITXOR
BITOR(x,y) = (x + y) – BITAND(x, y);

BITXOR(x,y) = BITOR(x,y) – BITAND(x,y) = (x + y) – BITAND(x, y) * 2;

如何控制RAC跨节点并行计算

RAC的一大优点就是可以跨节点进行并行计算,那么如何控制并行运算?这就是这篇文章要讨论的内容。

合理设置跨节点并行,需要先设置一些参数:
instance_groups:这个参数主要是设置该节点实例是否属于某一个实例组。每个节点可以设置多个不同的实例组名,实例组名用逗号隔开。
parallel_instance_group设置的值为instance_groups里面设置的值,表明这个节点上面进行的并行操作可以跨越哪些实例组。
Continue reading

对ORACLE的onling redo log files的操作

–手动更改日志组
ALTER SYSTEM SWITCH LOGFILE;
–增加日志组
ALTER DATABASE ADD LOGFILE GROUP 5
(‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG’)
SIZE 8M;
–删除日志组
alter database drop logfile group 4 ;
–添加日志组成员
ALTER DATABASE ADD LOGFILE MEMBER
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO21.LOG’ TO GROUP 1,
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO22.LOG’ TO GROUP 2,
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO23.LOG’ TO GROUP 3;
–删除日志组成员
alter database drop logfile member
‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG’ ;

ALTER DATABASE RENAME FILE command
– Shut down the database.
– Copy the online redo log files to the new location.
– Place the database in MOUNT mode.
– Execute the command.
alter database rename file
‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO15.LOG’ to
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO55.LOG’ ;
– Open database for normal operation.
alter database open ;
补充一点有用的语句
alter database clear unarchived logfile group 3 ;
alter database clear unarchived logfile ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG’ ;

SQL> alter database clear unarchived logfile group 4 unrecoverable datafile;
如果需要清除一個日志文件﹐而這個日志文件剛好又是離線表空間在online時所需要的﹐則必須使用unrecoverable datafile子句來清除這個日志文件。

详解pctused与pctfree

概念:
pctused:一个块的使用水位的百分比,这个水位将使该块返回到可用列表中去等待更多的插入操作。
pctfree:用来为一个块保留的空间百分比,以防止在今后的更新操作中增加一列或多列值的长度。
freelist:可用列表是表中的一组可插入数据的可用块。
行连接:指一行存储在多个块中的情况,这是因为该行的长度超过了一个块的可用空间大小,即行链接是跨越多块的行。
行迁移:指一个数据行不适合放入当前块而被重新定位到另一个块(那里有充足的空间)中,但在原始块中保留一个指针的情形。原始块中的指针是必需的,因为索引的ROWID项仍然指向原始位置。

Continue reading

如何成为ORACLE顶尖高手

介绍
许多年来,我在不同的新闻组上花费了很多时间与那些想知道如何得到数据库管理员(DBA)的工作或者如何成长为一名DBA的IT人进行交谈,现在他们有了工作。这些年来许多人针对如何达到这个目标提出了不同意见。本文即是那些意见的综合并且能够在如何才能出色的完成DBA的工作方面给出好的建议。这篇文章同样对于如何让一名DBA变得对老板更有价值。假如你已经是一名DBA,那么也许你会希望跳过文章的前几段。

 

我应该成为一名DBA么?

我曾问过的一个问题是一个人应该从事DBA这样的职业么。这个问题并不容易回答,因为它因人而异。有的人有成为一名好DBA所需要的天赋。而其他人并不认为能够很容易掌握成为DBA的秘诀。DBA职业需要掌握一定的技能。而且还需要其他IT职业所不必需的要求。因此,为了回答这个问题,我通常给将要成为DBA的人描述DBA职业所必需的要求。下面的段落中,我都将以问题结尾。在继续下一段以前请花一些时间考虑并且回答这些问题。

许多人因为许多原因而立志要成为DBA。其中一个主要原因是薪水。DBA是IT业中薪水最高的职业之一。其他人想成为DBA是因为喜欢赞扬DBA是受到的荣誉,或者因为他看上去很酷!我个人认为,成为DBA是很值得的。它是一个很有意思,令人兴奋的职业。那么,你把DBA作为一个可能的职业选择的原因是什么呢?

除非你已经提前准备好了,否则你可能会发现从事DBA职业充满了挫折和令人头痛。一个数据库存在于操作系统和最终用户应用程序之间。同样地,DBA必须非常精通他的数据库所在的操作系统。DBA并不一定需要知道所有有关操作系统的知识,但是他知道得越多越好。数据库与操作系统联系非常紧密。理解这种关联是十分必要的。DBA还需要知道服务器硬件以及它如何影响与帮助数据库。同时,DBA必须理解应用软件。DBA可能会被要求帮助开发人员创建可靠,健全的数据库应用程序。还有,最重要的也是最明显的,DBA必须十分彻底的理解数据库引擎,它是如何工作的,所有的引擎是如何组合在一起的,以及如何影响数据库引擎向最终用户和应用程序传送数据的能力。我见过的最好的DBA有非常深刻的理解而且不仅仅在数据库本身。他们知道一些系统管理与应用开发。好多时候他们在成为DBA之前有其中一个或两个背景。无论如何,成为一名合格的而不是出色的DBA也需要许多背景知识。你是否已经做好准备开始学习直到你感到已经无法再学下去了?

许多和我交谈过的,在开始DBA职业时遇到困难的人,在尝试着吸收大量DBA所需了解的信息时实际上都会有一些问题。毕竟系统管理员是全职学习操作系统的细节。应用程序开发人员全职学习如何编写好的程序。DBA不仅要非常了解这两种不同的工作,而且还需要花费更多的时间去理解数据库的体系结构,以及理解每一样东西的每一块是如何组合在一起的!听起来是不是很让人畏缩?有许多人失败后这样想,并且把DBA工作看作一项非常困难的事情。也有那些传播和理解所有这些信息,并且使用这些信息做出好的,听起来具有技术性的决定的人。正如我以前是一名DBA时喜欢说的,所有这些对我来说看上去像一个大谜团。把这些所有的很好的组合在一起就是挑战。你是哪一种人?

许多DBA是随时侯命的。他们会在白天或晚上的所有时间接到呼叫去解决他们的数据库出现的致命问题。数据库是商业信息技术基础组织的必不可少的组成部分。没有数据,就没有必要拥有一个计算机系统。数据推动商业。假如amazon.com的网站不能在数据库中搜索产品并且假如没有人能够为他们的产品下订单,那么它会变成什么样?它就不会在商业中存在很久。当数据库down掉,即使只是很短的时间,公司也会损失重大。基于这个原因,DBA到达现场后必须尽可能迅速的解决问题。许多公司有自己的DBA团队以便可以轮流待命。这些DBA 24×7小时的维持数据库应用程序。假如工作需要的话,你准备好随时候命了么?

一些DBA的职责包括为软件打补丁或者对数据库做些改变。通常,这些操作不能在公司职员工作的时候做,因为此时数据库必须运行以便他们能够工作。这意味着DBA经常不得不在很早或者深夜甚至周末,总之,在正常工作时间以外来完成工作。你准备好在非正常时间工作,或者你在找一个朝九晚五的工作?

对DBA而言,需要掌握的一个重要内容就是通常被称为”软技术”的东西。DBA需要在团队中很好的工作,通常团队是在变化的,如系统管理员,网络管理员,应用程序开发人员,项目经理和其他人。DBA要能够用流利的英语解释复杂的技术概念,让团队中其他人明白。DBA还要能够在数据库相关问题上指挥团队队员。你的软技术怎么样?

下面不是全部列表,但是包括了DBA的典型职责:
· 把监视数据库实例当作每日必做工作以保证其可用性。解决不可用的问题。
· 收集系统统计和性能信息以便定向和配置分析。
· 配置和调整数据库实例以便在应用程序特定要求下达到最佳性能。
· 分析和管理数据库安全性。控制和监视用户对数据库的访问。必要时审计数据库的使用。
· 监视备份程序。必要时提供恢复。测试备份与恢复程序。
· 升级RDBMS软件并且在必要时使用补丁。必要时升级或者迁移数据库实例。
· 通过数据库相关动作来支持应用程序开发人员。
· 跟随数据库趋向和技术。当可应用时使用新技术。安装,测试和评估Oracle新的相关产品。
· 执行存储和物理设计。均衡设计问题以完成性能优化。
· 创建,配置和设计信的数据库实例。
· 诊断,故障检测和解决任何数据库相关问题。必要时联系Oracle支持人员以便使问题得到较好的解决。
· 确保Oracle网络软件(SQL*Net, Net8, Names, OiD)配置和运行的很好。
· 与系统管理员(Unix & NT)一起工作以保证Oracle相关事务得到很好的处理。
· 为有效的,定期的维护数据库创建任何必要的脚本。

前面各段的问题是为了使你考虑一名DBA该做些什么,帮助你决定这是不是适合你的职业。我并非意味着假如你的目标是成为DBA这些会阻止你。我只是尝试着展现一些事实。我看到过一些DBA一旦被实际工作打击了就一蹶不振。他们花费时间,精力和一些金钱获得了他们的第一份DBA工作。我个人认为这个职业非常有价值。而且我无法想像现在做任何其他的会怎样。所以,这一段帮助你决定这是不是你希望从事的。假如它是,那么尽你所有去得到它!
我怎样得到第一份DBA工作?
你已经阅读了前面的段落并且认为成为一名DBA是一个好的职业。祝贺你!我希望你的职业能变成你想像得那么令人兴奋和有意义。那么,你如何找到第一份DBA工作?这个问题我已经听别人问了许多许多遍。

在90年代早期,因特网急速发展。它使公司象草一般萌芽。公司蜂拥而至并且开始创建他们在网上的形象。几乎所有这些有网站的公司都需要一个数据库作为web应用的后台。不幸的是,当时在该领域却没有那么多DBA。在IT业,DBA变得奇缺。那段时间里,得到一份DBA工作看上去只要可以拼出”Oracle”或者可能只是在大学里接触过一学期的数据库就行。为了使生活变得更好,DBA的匮乏促使公司付给有潜力的职员很高的薪水。假如你想要成为一名DBA,很容易,非常容易。你需要做的就是证明你了解什么是数据库然后工作就会比你预期的更早的出现在你面前。

然后因特网的泡沫破灭了。大量投产因特网的公司破产。许多给公司工作的DBA重新寻找工作。缺少DBA的公司找到一名有DBA经验的人比以前容易得多。在21世纪初期,由于经济并不十分稳固,因此生活并不容易(至少在美国如此)。公司都勒紧了他们的裤腰带。所有这些转化为更少的工作机会和DBA候选人更少的工作空缺。

得到第一份DBA工作的最艰难的部分是每一个职位都要求有一些工作经验。如果你从公司的角度出发,你就可以理解为什么对DBA职位而言经验是必需的。假如他没有一点经验,你会付给这个人很高的工资让他去操作,维护和运行你IT基础组织的最大最重要的一部分么?你的公司会付钱给一名没有经验的DBA么?并且,在等待他成长起来的过程中可能会损失上百万美元的收入。对大多数公司而言,这些问题的答案肯定是’不’。所以,没有经验,获得你的第一份DBA工作是很困难的。

第一份DBA工作现在成了恶性循环的境遇。假如我没有任何经验,我怎样才能得到DBA的工作呢?我没有工作的话又怎么能得到DBA经验呢?这是要战胜的最困难的障碍。最困难的部分是获得第一个DBA工作。这部分的剩下部分将针对实现你第一个DBA工作的目标给你一些建议。

提示#1:接受教育。–尽可能多的学习有关数据库的知识。这很可能将占用你正常工作以外的部分时间也精力。参加本地大学举办的数据库培训班。许多培训公司都会举办数据库管理员的培训班。假如你的老板不资助你的学习,那么你可能不得不自己支付这笔费用。许多DBA职业要求至少为计算机科学或相关专业本科以上学历,因此你必须至少有那样的文凭。

提示#2:锻炼成为DBA。–许多数据库供应商都允许你下载他们数据库系统的测试版或评估版。下载一份并且在自己的个人电脑上安装软件。练习使用数据库。故意破坏数据库并且尝试修复它。尝试着履行你所能想到的尽可能多的DBA职责。测试和磨练你在自己的测试平台上的技能这样你就可以证明你的数据库管理能力。

提示#3:获得认证。–许多数据库提供商都提供自己的数据库产品的认证。许多公司现在都把认证看作是一种标准。需要记住的一件事是仅获得认证是不够的。通过DBA认证测试并不意味着你知道如何管理一个数据库。它只是告诉你以后可能的老板,现在你拥有了一定的技术。它还告诉你的老板你对DBA工作的态度是很认真的。我看到许多人抱怨他们已经得到了认证但是没有经验,却仍然不能得到第一份DBA工作。认证本身并不能使你得到工作,但它是无害的。即使没有其他的,在你进行认证的时候你也已经学到了许多知识。只是不要依赖认证来给你带来你要找的工作。你需要的比这还要多。但它会在最后帮助你。

提示#4:利用你现有的技能。–许多DBA具有系统管理员背景。其他的有应用程序开发背景。假如可能,查看你能否利用现有的技能来得到工作。现在的目标就是为你和你的老板创造一个双赢的局面。例如,让我们假设你已经是一名系统管理员而想进入DBA领域。也许你会找到一份工作,这份工作一部分时间里可以用到你的系统管理技能,并且在剩下的时间里可以使你涉及到数据库管理领域。假如你已经是一名某个产品平台上的DBA但你希望转到其他产品平台,那么看看你能否找到一份同时接触两个产品平台的工作。这样,公司和你都得到了想要的。在你定向到了DBA工作后,你可以试着得到一个能让你全职作它的职位,也许还可以在同一个公司中。

提示#5:利用现在的机会。–有时候,一个人进入DBA领域仅仅需要的是正确的地方和正确的时机。假如你现在的老板有一个机会让你进行任何数据库的项目,抓住这个机会!任何数据库经验就比没有数据库经验要好。让你的管理者知道你十分积极的在寻找任何可能的数据库机会。他们就有可能在下次机会到来的时候想到你。进行这些数据库项目以及看到你要成为一个DBA的渴望以后,他们可能会决定培训你,提拔你。许多许多人都是以这种方式获得他的第一个DBA工作,在进行了一些数据库相关的项目后不知不觉的成为一名较低级的DBA。通常当一名DBA离开公司后,公司将在内部寻找一个候选人,假如他们认为这名候选人是可训练的话。

提示#6:寻找较低级的DBA职位。–假如你只是为了一个较低级的DBA工作,看到DBA职位的需求描述说他们正在寻找高级DBA或者其他的。所以,让我们严谨一些。你并没有一个高级方面的经验。我已经讨论过了对于这样的职位为什么公司不会考虑你。但是他们会在低级的职位上考虑你。低级的DBA在高级DBA的指导下完成工作。他们摸索窍门。一般来说,高级DBA对数据库承担责任,同时也获得所有的荣誉。但是不要焦急。随着你的事业发展,你将会有越来越多的责任和得到越来越多的信任。因为你没有任何经验,你应该从这里开始启航。

我也听到过一些公司寻找一名高级DBA,但是到最后,他们实际想要雇一名低级的DBA。你或许希望申请这样的职位虽然你也许没有资格。他们可能还是会决定雇佣你。但是提前说明你仍然在摸索阶段并且已经是较低级的DBA水平。不要试图欺骗他们让他们认为你是高级DBA的水平。这只会降低你得到这项工作的机会。

这些提示将帮助你得到第一份DBA的工作。祝你在寻找工作时有好运气。当你已经找到了第一份DBA工作后,继续下面的部分来学习如何往下走下去。

我刚得到我的第一份工作!现在该怎样?

祝贺你!你现在是DBA俱乐部的正式成员了!对于这份梦寐以求的令人激动的职业,你准备好了么?你的工作才刚开始并且你在学习上已经落在后面。你将会发现要成为一名高效的数据库管理员有大量的知识你必须掌握。你的第一年或前两年将花费比以前更多的时间来学习。假如你发现学习知识的数量使你大脑超负荷,那么休息一下,歇口气,然后再回到学习中去。为了帮助你继续走下去,你可以按照下面的方法进行:

步骤#1: 关系型数据库理论 -这部分我假设你将管理的数据库是一个”关系型”数据库。其他数据库模型也存在,但是关系型模式是近二十年工业上占统治地位的一种数据库模式。假如你的数据库系统是其他的模式,那么学习它的理论。相关数据库理论是十分重要的。它是其他一切的基础。我也看到许多跳到数据库管理职位的人从不想去学习纯粹的关系型数据库理论。不可避免的,在他们的事业中对理论基础的匮乏作为缺点暴露了出来。假如你对关系型数据库理论理解得很好,那么你将非常容易的在任何平台的关系型数据库管理系统(RDBMS)中转变。我使用Oracle数据库,或者IBM的DB2,或者微软的SQL Server是无关紧要的。他们都是关系型数据库系统。他们在最底层都在做着相同的事情。区别在于他们怎样去做相同的事情。纯粹的关系型数据库理论对于较低级的DBA来说并非必需的。但是假如你想要超越低级DBA的水平它就是十分重要的。许多大学的教科书都很好的包含了关系型数据库的理论。其中一本被广泛使用的教科书就是由Elmasri and Navathe编写的数据库系统基础,Bejamin/Cummings Press。

步骤#2: 彻底的学习查询语言 -数据库都有语言让你能够从数据库中得到数据,把数据放到数据库中,以及修改数据库中的数据。对于关系型数据库而言,这种语言就是结构化查询语言(SQL)。这门语言是你与数据库接触的工具。不能让这个工具成为以后学习的障碍,这一点很重要。在你的测试数据库中练习不同的SQL语句直到他们变成了你的习惯。这方面的一本非常好的书叫做Oracle 9i完全参考(Oracle 9i The Complete Reference)由Loney 和Koch编写,Oracle Press。每一名Oracle DBA都应该在他事业的早期阅读这本书。Oracle 9i参考手册(Oracle 9i SQL Reference manual)是另一个很重要的知识来源。在他们的技术网站TechNet上(http://technet.oracle.com)你可以访…t上有一个账号。

步骤#3: 开始学习基本的数据库管理工作 -这难道不是你最开始在这里的原因?为什么它在列表的第三位?我们尝试着建造一个知识的金字塔,我强烈的感觉到一个人需要知道关系型数据库理论和SQL,并且在你学习如何进行基本的数据库管理工作时把他们当作工具来使用。这些工作包括启动和关闭数据库,备份和恢复数据库,以及创建/删除/修改数据库对象。对于Oracle数据库管理而言,在市面上有大量的书籍可以给你所期望的一个很好的体会。这本书是Oracle 9i DBA手册(Oracle 9i DBA Handbook by Loney on Oracle Press)。我知道的大多数DBA都在他们事业的早期不只一遍的阅读过这本书。这里,你应该同时阅读和理解Oracle 9i 概念指导,Oracle 9i管理员指导,以及Oracle 9i备份与恢复指导(Oracle 9i Concepts Guide, the Oracle 9i Administrator’s Guide, and the Oracle 9i Backup and Recovery Guide)都来自Oracle文档。

步骤#4: 阅读,阅读,再阅读 -由于你才刚开始你的DBA职业生涯,因此你正在开始为你的技能奠定基础。这需要一段很长的时间去形成,吸收和领会所有你将学到的知识。毫无疑问的,比你资深的DBA由许多工作要做,因此他们可能不会总是腾出大量时间辅导你的学习。你不得不靠自己学习很多东西。这就是阅读的目的。市面上有许多书籍可以解答许多数据库相关的话题。Oracle Press是Oracle公司的官方出版社,有大量的Oracle相关书籍。同时也有其他的出版社,如Wrox Press 和 O’Reilly Press。你也可以找到Oracle文档来阅读。并且还有许多网站和新闻组。尽可能多的读书使你能够继续下去。还有,不只一遍的阅读它们可以使你吸收你第一次阅读时错过的内容。

步骤#5: 创建测试案例 -我经常看到初学者问一些很基础的问题,其实假如他们花一些时间来考虑,这些问题都是很容易解答的。毫无疑问的,在你开始学习Oracle的时候你会有许多的问题。看看这些问题你能不能自己回答出来。例如,我又一次被问到能不能向有唯一性约束的列中插入空值。最开始,这看上去也许不是很容易回答的问题。但它却是非常容易去试验的!只需要创建一个简单的表。在其中的一列,假如唯一性约束。尝试着在该列插入一个空值。有效么?你应该能够非常容易的回答出这个问题了。那么,为什么要创建这些案例呢?一个原因是这样做可以提高你解决问题的能力。创建这些案例需要的技能就是解决问题用到的技能。解决问题的技能将会对你的DBA事业有很大的帮助。另一个原因是随着你的事业的发展,你将经常需要创建更复杂的测试案例以便保证数据库和应用程序的成功。在将来,甚至简单的测试案例也可以组成更复杂的数据库和应用程序分解。

步骤#6: 找一个良师 -一个良师能够为你的DBA生涯(或者其它类似的职业)引领方向。他们能够给你指示,回答问题以及在你的DBA的成长过程中帮助你节约一些时间。但愿这篇文章能够在你事业发展的一段时间内起到良师益友的作用。假如你与一名资深的DBA共同工作,那么那个人应该有责任为你的事业进行有益的指导。你也可以同时选择其他的人指导你。

步骤#7: 参加本地用户群 -许多跨国家的城市有本地用户群,他们定期聚会讨论数据库相关的话题。假如可能,参加其中一个本地用户群。这将给你一个与他人相互交流的很好的方法。
我如何能够从一名DBA初学者变为一个具有中级水平的DBA?

你已经成为DBA一段时间了,你现在希望你的技术水平提高一阶么?下一步该怎么做?首先,往回看前面的部分,确认你已经完成了所有的步骤。彻底理解SQL语言是十分重要的。

理解关系型数据库理论和掌握基本的数据库管理任务也是非常重要的。到如今,你应该阅读文档和其他书籍到已经郁闷了。假如没有,那么你还没准备好继续深造,增长你的DBA的技术水平。假如你已经准备好继续了,我已为你的继续深造准备了一些方法。

步骤#1: 学习操作系统和你的服务器硬件 – 正如我前面所说,数据库存在于操作系统和服务器硬件之上。理解这些组成部分如何工作是很必要的。你应该知道如何与特殊的操作系统相合。你如何删除或者编辑文件?假如你的操作系统是Unix,你应该掌握命令行以及Unix命令如何辅助你工作。对于运行在Windows或其他操作系统上而言也是一样的。你同时需要对服务器的硬件有一定的了解。物理内存和虚拟内存有什么区别?RAID是什么以及不同的级别是如何产生影响的?为什么数据库喜欢更多的物理硬盘而非一个大硬盘卷?你需要知道这些事情以便你能够容易的与系统管理员进行如何配置好你的服务器以便使它能够充分的支持数据库方面的交谈。

步骤#2: 学习应用程序设计因为它与数据库相关 – 如前面所述,数据库存在于操作系统与数据库应用程序之间。你真的需要这两者。SQL语言是如何帮助创建好的应用程序的?绑定变量是什么并且为什么他们很重要?Tom Kyte 写了一本非常好的书,在Oracle应用程序设计上给出了很好的建议。他的Expert One-on-one Oracle书可在 Wrox Press找到。我强烈推荐阅读此书。他详细的叙述了那些能够生成和破坏Oracle应用程序的东西。你需要知道这些,因为你的应用程序开发人员希望从你这里得到指导和数据库知识。学习任何与应用程序设计有关的知识。也许参加一个关于软件工程,操作系统或数据结构的课程班会有好处。

步骤#4: 取得认证 – 也许你的工作并不需要,但是取得认证一定对你有益。作为DBA的每一天里,你学到了许多新的和令人激动的事情。也许在你职业生涯的这段时间里,有几天你没学到任何新的东西。但你仍然有很多要学习。成为一名OCP(Oracle Certified Professional) DBA要求你必须已经学到了数据库管理所有方面的基础。我发现在OCP考试的学习过程中,我学到了在我工作中从未接触过的东西。一次我学到了我从未碰到过的一个特殊课题,在后来的日子里我就能够使用那个知识解决问题。假如我从为在OCP考试中学倒它,那么我永远也不会用那种特殊的方法去解决问题。这已经一次次的发生在我的面前。有的人可能会说认证实际上真的不值得。我要说它只会对你有益无害。所以,去取得认证吧

步骤#5: 获得一个资源库 – 在前面的部分中,我指出每个DBA都应该在Technet上有个账号。这是你其中一个主要资源。但是同时还有许多其他资源。很多人共享他们的Oracle知识。假如你还没有开始,你应该用网络浏览器去搜索并收集很多Oracle资源。愿意的话,你可以从访问我的网站http://www.peasland.net)开始。下面是一些Oracle DBA必须了解得网站列表:
ü Ask Tom – http://asktom.oracle.com
ü Jonathan Lewis web site – http://www.jlcomp.demon.co.uk/
ü Ixora (Steve Adams) – http://www.ixora.com.au
ü Orapub – http://www.orapub.com
ü Metalink (Oracle支持网站) – http://metalink.oracle.com

还有许多其它的好网站。

步骤#6: 开始在不同的新闻组和论坛上交流 – 也许你已经发现了他们,但假如现在你还没有那么是时候去开始了。有许多的新闻组和论坛可以回答你的任何Oracle问题。在Oracle群落里还有许多高手愿意和你共享他们的知识。你所要做的就是提问。下面是一个列表包含了可以开始交流的最好的因特网团体:ü Usenet newsgroups – comp.databases.oracle.server 和 omp.databases.oracle.misc 是两个可以交流的非常著名的世界性的新闻组。他们拥有大量的针对Oracle问题的交流卷宗。观看这些组的最好的方法式使用新闻广播员。但是假如你想通过基于web的方式访问,也可以通过Google搜索引擎搜索它。(http://groups.google.com/groups?

hl=…atabases.oracle)
ü Quest Pipelines – 当他们在最开始还属于软件提供商RevealNet的时候,被称为the RevealNet Pipelines。现在,Quest购买了RevealNet 并且拥有Pipelines 。因为Pipelines是中等的,所以这些是我最喜欢的。你可以在这里找到Pipelines (http://www.quest-pipelines.com/index.asp)。

观察别人是如何经历考验和磨难的是一件好事。假如你有问题,可以自由的在群里提出来。假如你要提出问题,通常应该包括一些信息,比如你的Oracle版本和Oracle运行的平台。这些将会得到有很大的差别的答案。假如你忘记了,会有人提醒你!甚至你不用提问也可以从其他人的答案中学到许多知识。我已经记不得多少次我之所以能够解决问题完全是因为我记得其他人在新闻组里问过相同的问题。
我如何从一名具有中级水平的DBA转变为一名高级DBA?

好,作为DBA你已经坚持不懈的努力了很长时间。你感到自己已经准备好往前再走一步。成为一名高级DBA需要什么?下面的部分将帮助你走下去。

步骤#1: 阅读所有的文档 – Oracle文档通常并不是很容易阅读的。无数次,你翻阅文档只是为了要弄清整件事情。假如文档是最好的东西,那么那些站在你的书架里的Oracle书籍就不会有市场。但是文档确实包含了一些无法在任何其他地方找到的信息。例如,你无法找到每一个专门的INIT.ORA参数或V$视图的详细说明。书本上也许会涉及一部分,但是Oracle文档却包含它们所有。我遇到过一个非常厉害的高级DBA,他没有从头至尾的阅读过Oracle文档。这不是偶然的。Oracle文档是必须阅读的。也许到现在为止你已经读过Oracle概念指导十二遍了。但是当Oracle 10i发布了,你还要再次阅读。任何有关10i的新概念将在文档里记录。假如你真的想更上一层,那么,去阅读那些文档。不要逃避它。

步骤#2: 成为一名专家 – Oracle数据库是一个非常复杂的东西。为了更上一层,你必须精通产品的许多组成部分。以备份与恢复开始。成为一名备份与恢复的专家。故意的破坏数据库然后察看如何恢复它。尝试以任何可能的方式破坏数据库然后查看还有没有可能恢复。你将彻底的理解备份与恢复的概念。在你成为了备份与恢复的专家以后,再去成为其他领域的专家。你会有无穷无尽的题目要去掌握。在你整个职业生涯中都保持如此。但是请记住,无论你有多么专业,在某些领域,某些人会在某些方面知道的比你多。不要带个人情绪。只是尽可能多的从那个人那里学到知识。

Route #3: 积极参加新闻组,论坛和用户组 – 在前面,我提过为什么不同的新闻组和论坛是学习新知识的很好的地方。现在轮到你进行下一步并且去回答任何你能够回答的问题。你将会惊讶于在这过程中你能学到那么多!

Route #4: 写白皮书并且展示它们 – 这与前面提到的方法类似。首先,共享你拥有的知识是很重要的。假如你的职业生涯已经走到这一步,那么从某种意义上讲,是离不开他人的贡献的。所以,现在是你为他人奉献的时候了。第二,当你企图共享你的信息的时候会有令人惊异的事情发生。在你要用清楚的,简练的语言表述问题以便其他人能够使用时,那些信息在你的头脑里经过了一个令人瞠目的过程。这个过程使你巩固了知识,这是无法通过其它方法进行的。所以在白皮书中共享那些信息,讨论会,以及新闻组和论坛都是你学习和使你的能力更上一层的非常好的方法。顺着这条路,你应该作两件事。第一,认识到你将会犯错误。其他人将会很高兴的指出那些错误,有时在某种意义上那并不是很好。

不要企图掩藏你的错误。承认它们并从它们那里学习。第二,学会说你不知道答案而不是企图以欺骗的方式通过。人们早晚会知道你在企图蒙蔽他们。简单的告诉他们现在你对答案并不肯定,但你会在查到答案后回来告诉他们。假如你时刻谨记这两件事,你就不会违背你的诚实而且你将成长为一名职业的IT人。

步骤#5: 成为解决Oracle问题的专家- 高级DBA通常都是被看作是解决复杂的Oracle问题的人。你将会用到你所有的技术来解决许多问题,这些技术都是你的职业生涯中积累下来的。我前面提到的任何事都将会在解决问题的过程中用到;文档,书籍,新闻组,测试案例,和其它DBA都将辅助你解决问题。

步骤#6: 成为性能调优的专家- 高级DBA通常都是被看作是调整数据库和应用程序性能的人。假如你是高级DBA但是你却不能分析性能瓶颈,那么你的公司将会到别处寻找这些服务。

步骤#7: 成为承载能力计划的专家- 高级DBA通常都是被看作是根据数据增长量和交易增长来计划数据库承载能力的人。高级DBA需要在影响应用程序性能以前发现系统瓶颈。例如,DBA应该知道在数据库将可用的磁盘空间用完以前预置更多的磁盘空间。不密切关注承载能力计划将会导致生产数据库宕机。

步骤#8: 密切关注新的技术- 高级DBA应该对IT界的关数据库技术的未来有好的建议。有什么可用的技术可以帮助数据库?例如,学习存储领域网的优缺点以及如何把它们应用到数据库系统。有什么技术在不远的将来可以用,哪些能够帮助我们?例如,写这篇文章的时候,linux操作系统正在变得越来越流行。Lunix会给你的数据库操作系统平台带来些什么?它能为你的公司工作么?
结论

从获得第一份工作,到从一名初级的DBA成长成一名高级DBA,我希望这篇文章在如何度过你的职业生涯的各个阶段方面给了你一些建议。无论你现在处在你的DBA职业生涯的哪一阶段都可以用到这篇文章。

oracle精细策略dmbs_rls

–要求:YY只可以访问 scott.test ID=1 的信息 user zz 只可访问 scott.test id=2,3 的信息,
–当USER 为ZZ时候可以更新ID=3 的 信息 其他USER 不能更新任何, (sysdba可以 )

create user yy identified by oracle
default tablespace users ;

create user zz identified by oracle
default tablespace users ;

grant create any context to scott ;
grant connect,resource to yy,zz ;
grant select any table ,update any table to yy,zz ;

create table test(ID number(2) , NAME varchar2(10) ) ;
insert into test values (1,’xh’) ;
insert into test values (2,’hr’) ;
insert into test values (3,’cc’) ;
insert into test values (4,’dd’) ;
select * from test ;

–自定义环境
create or replace context actest using scott.test_pkg;

SQL> create or replace package scott.test_pkg as
procedure set_test;
end;
/

–用包scott.test_pkg 设置环境
create or replace package body scott.test_pkg
as
procedure set_test is
begin
if sys_context(‘userenv’,’session_user’)=’YY’ then
dbms_session.set_context(‘actest’,’yy_attr’,1);
elsif sys_context(‘userenv’,’session_user’)=’ZZ’ then
dbms_session.set_context(‘actest’,’zz_attr1′,2);
dbms_session.set_context(‘actest’,’zz_attr2′,3);
end if;
end;
end;
/

–创建一个安全策略函数
create or replace package scott.test_p as
function p_select(object_schema varchar2,object_name varchar2) return varchar2;
function p_update(object_schema varchar2,object_name varchar2) return varchar2;
end;
/

create or replace package body scott.test_p as
function p_select(object_schema varchar2,object_name varchar2) return varchar2
is
rtn_predicate varchar2(500);
begin
rtn_predicate :=’1=1′;
if user=’YY’ then
rtn_predicate := ‘id =sys_context(”actest”,”yy_attr”)’;
elsif user=’ZZ’ then
rtn_predicate := ‘id=sys_context(”actest”,”zz_attr1”)’||’ or ‘||’id=sys_context(”actest”,”zz_attr2”)’;
end if;
return rtn_predicate;
end;

function p_update(object_schema varchar2,object_name varchar2) return varchar2 is
rtn_predicate varchar2(500);
begin
rtn_predicate:=’1=2′;
if user=’ZZ’ then
rtn_predicate :=’id=sys_context(”actest”,”zz_attr2”)’;
end if;
return rtn_predicate;
end;

end;
/

desc dbms_rls.add_policy ;

SQL> exec dbms_rls.add_policy(‘scott’,’test’,’sel_policy’,’scott’,’test_p.p_select’,’select’);

SQL> exec dbms_rls.add_policy(‘scott’,’test’,’upd_policy’,’scott’,’test_p.p_update’,’insert,update,delete’);

create or replace trigger logon_t
after logon on database call scott.test_pkg.set_test
/

select * from scott.test ;
select * from test ;

update scott.test set name=’hello’ where id=1
update scott.test set name=’hello’ where id=3
select sys_context(‘actest’,’yy_attr’) from dual ;
select sys_context(‘actest’,’zz_attr1′) from dual ;
select sys_context(‘actest’,’zz_attr2′) from dual ;

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_codition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

 

创建监控表的DML的触发器–Oracle审计

在生产环境中,总是可能出现这样的情况:某张或者某些表的数据被莫名其妙的修改了,但是很难定位出是哪个用户、哪个过程修改的。这是一个很让DBA头痛的事情(往往DBA对于整个代码逻辑并不是非常了解)。要定位出”问题”语句,有几种方法可以选择:log miner;细节粒度审计;触发器。Log miner要求要有归档日志(这个并非所有系统都可以做),而且需要有相当的磁盘空间,好处就是可以离线做;细节粒度升级能够根据条件记录下表的DML操作(9i及之前只能记录SELECT语句),比较复杂的FGA需要较高权限的用户来实现;触发器比较灵活,能够按照比较复杂的条件来记录需要的信息。下面介绍触发器如何实现。

要建立这样的触发器,需要利用到几张系统视图:v$session, v$sql, v$cursor,(10g, 9.2.0.1中可以,9.2.0.5, 9.2.0.之前存在bug)
SQL> connect “/ as sysdba”
grant select on SYS.V_$SQL to demo;
grant select on SYS.V_$SQL_BIND_DATA to demo;
grant select on SYS.V_$SQL_CURSOR to demo;
grant select on SYS.V_$SESSION to demo;
grant create trigger to demo;
CREATE TABLE trig_sql(lt DATE, sid NUMBER, SERIAL# NUMBER,
USERNAME VARCHAR2(30), OSUSER VARCHAR2(64),
MACHINE VARCHAR2(32), TERMINAL VARCHAR2(16),
PROGRAM VARCHAR2(64), sqlText VARCHAR2(2000),
status VARCHAR2(30));
方法1:

create or replace trigger ttt_trig

after insert or update on pga_ttt

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

begin

INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘NONE’

from v$sql q, v$session s

where s.audsid=(select userenv(‘SESSIONID’) from dual)

and s.prev_sql_addr=q.address

AND s.PREV_HASH_VALUE = q.hash_value;

 

COMMIT;

end;
——————-2008-04-22—-改进版一————————————–
create table TRIG_SQL
(
LT DATE,
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(30),
OSUSER VARCHAR2(64),
MACHINE VARCHAR2(32),
TERMINAL VARCHAR2(16),
PROGRAM VARCHAR2(64),
SQLTEXT VARCHAR2(2000),
STATUS VARCHAR2(30),
CLIENT_IP VARCHAR2(60)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);

—————————————-触发器
create or replace trigger ttt_trig
after insert or update or delete on scott.schema
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘INSERT’
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF deleting then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘DELETE’
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF updating then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘UPDATE’
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END IF;
END;

————————-改进版二 加Client IP地址
create or replace trigger ttt_trig
after insert or update or delete on scott.emp
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘INSERT’,
sys_context(‘userenv’,’ip_address’)
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF deleting then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘DELETE’,
sys_context(‘userenv’,’ip_address’)
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF updating then
INSERT INTO trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
‘UPDATE’,
sys_context(‘userenv’,’ip_address’)
from v$sql q, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END IF;
END;

——————————- 简化版3——————————–
create or replace trigger ttt_trig
after insert or update or delete on scott.emp
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
DML_TYPE VARCHAR2(20);
BEGIN

IF inserting THEN
DML_TYPE:=’INSERT’;
ELSIF deleting THEN
DML_TYPE:=’DELETE’;
ELSIF updating THEN
DML_TYPE:= ‘UPDATE’;
END IF;

INSERT INTO trig_sql
SELECT sysdate,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.sql_text line,
DML_TYPE,
sys_context(‘userenv’,’ip_address’)
FROM v$sql q, v$session s
WHERE s.audsid=(SELECT userenv(‘SESSIONID’) FROM dual)
AND s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END;

————————————–20080421————————————-
create sequence mtt_num_seq;

create sequence member_row_track_seq;

create or replace package dml_count is
v_mtt_count Member_Tran_Trace.Mtt_Count%TYPE:=0;
end;

第一个触发器:
create or replace trigger trig_member_row
after insert or delete or update on member
for each row
begin
if inserting then
insert into member_row_track
(member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:new.member_id,:new.name,:new.hire,:new.jikwi,:new.sal,:new.bonus,
:new.mgr,:new.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF deleting then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF updating then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

END IF;
end;

第二个触发器代码:
create or replace trigger trig_member_statement
after insert or delete or update on member
begin
if inserting THEN
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’insert’,dml_count.v_mtt_count);
ELSIF deleting then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’delete’,dml_count.v_mtt_count);
ELSIF updating then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’update’,dml_count.v_mtt_count);
END IF;
end;

3,
wan jia sheng :
member
wan jia sheng :
member_tran_trace
wan jia sheng :
member_row_track
—————————————————————
方法2:

create or replace trigger ttt_trig
after insert or update on pga_ttt
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
for cr in (select s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM,
q.sql_text line, c.status stat
from v$sql q, v$sql_cursor c, v$session s
where s.audsid=(select userenv(‘SESSIONID’) from dual)
and s.prev_sql_addr=q.address
AND c.STATUS = ‘CURFETCH’)
loop
INSERT INTO trig_sql VALUES(SYSDATE, cr.sid, cr.SERIAL#,
cr.USERNAME, cr.OSUSER, cr.MACHINE,
cr.TERMINAL, cr.PROGRAM, cr.line,
cr.stat);
end loop;

COMMIT;
end;
第一种方法是通过前一SQL的地址(pre_sql_addr)和HASH(prev_hash_value)值来定位出发trigger的语句的,不能用sql_address和hash_value来定位,否则获取到是触发器里面向日志表插入记录数据的语句本身了。

第二个方法是通过通过地址加游标的方法,按照视图各个字段的解释,应该是可以通过v$sql_cursor.parent_handle来定位的。但是通过测试发现,只有当前一条语句和查找前一条语句的语句在一个PLSQL块中的时候才有效,

SQL> set serveroutput on
SQL> declare
2 v_date date;
3 v_sql varchar2(2000);
4 begin
5 select sysdate into v_date from dual;
6
7 select q.sql_text into v_sql
8 from v$sql q, v$sql_cursor c, v$session s
9 where s.audsid=(select userenv(‘SESSIONID’) from dual)
10 and s.prev_sql_addr=q.address and q.address=c.parent_handle;
11
12 dbms_output.put_line(v_sql);
13 end;
14 /
SELECT SYSDATE FROM DUAL

PL/SQL procedure successfully completed.
因为触发器本身是一个PLSQL块,所以总是无法获得正确语句,最后只有通过cursor的状态来获取。下面简单了解一下CURSOR各个状态的含义:

· CURNULL:游标已经存在,但没有任何SQL语句在使用它(即cache在每个session内存中的游标)

· CURSYNTAX:解析SQL语句过程的一个游标状态,说明调用游标的SQL语句语法正确,但是没有解析完成。

· CURPARSE:调用游标的语句解析完毕

· CURBOUND:游标使用了帮定变量,并定义好了帮定变量

· CURFETCH:游标执行完毕,并fetch了数据

· CURROW:游标正指向某一行

· ERROR:游标错误,一般是有BUG了。

当一条INSERT或者UPDATE语句执行以后才会触发触发器,所以这时候的游标状态是CURFETCH,我们这就通过状态为CURFETCH来定位。

比较郁闷的是,我的系统是9.2.0.5的,因为存在BUG,这样的触发器做不了,FGA又无法监控INSERT和UPDATE语句。还没找到好的办法

——————————————
create sequence mtt_num_seq;

create sequence member_row_track_seq;

create or replace package dml_count is
v_mtt_count Member_Tran_Trace.Mtt_Count%TYPE:=0;
end;

第一个触发器:
create or replace trigger trig_member_row
after insert or delete or update on member
for each row
begin
if inserting then
insert into member_row_track
(member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:new.member_id,:new.name,:new.hire,:new.jikwi,:new.sal,:new.bonus,
:new.mgr,:new.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF deleting then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

ELSIF updating then
insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num)
values (:old.member_id,:old.name,:old.hire,:old.jikwi,:old.sal,
:old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

dml_count.v_mtt_count:=dml_count.v_mtt_count+1;

END IF;
end;

第二个触发器代码:
create or replace trigger trig_member_statement
after insert or delete or update on member
begin
if inserting THEN
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’insert’,dml_count.v_mtt_count);
ELSIF deleting then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’delete’,dml_count.v_mtt_count);
ELSIF updating then
INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count)
VALUES (member_row_track_seq.nextval,user,’update’,dml_count.v_mtt_count);
END IF;
end;

3,
wan jia sheng :
member
wan jia sheng :
member_tran_trace
wan jia sheng :
member_row_track

————————————————–

用来记录审计所有的DDL操作
1:事先在SYS下建了一个表ddl$trace。
2:创建tr_trace_ddl,创建时可以通过,创建后显示状态不正常,
CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;

INSERT INTO ddl$trace(login_user,audsid,ipaddress,
schema_user,schema_object,ddl_time,ddl_sql)
VALUES(ora_login_user,’0′, ora_client_ip_address,
ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);
EXCEPTION
WHEN OTHERS THEN
sp_write_log( ‘捕获DDL语句异常错误: ‘||SQLERRM);
END tr_trace_ddl;
3:在TOAD里面重新编译时,提示出错,
RIGGER SYS.TR_TRACE_DDL
On line: 2
PLS-00103: 出现符号 ” “在需要下列之一时:
begin function package pragma
procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor

那位知道为是什么
4,
以下两个过程呢,其实是辅助过程,一个是完成写日志,一个是完成发邮件,在以后的程序中,可能会经常用到。

1、写日志过程

name:sp_Write_log
parameter:textContext in varchar2 日志内容
create date:2003-06-01
creater:chen jiping
desc: &#8226;写日志,把内容记到服务器指定目录下
&#8226;必须配置Utl_file_dir初始化参数,
并保证日志路径与Utl_file_dir路径一致或者是其中一个

create or replace PROCEDURE sp_Write_log(textContext VARCHAR2)
IS
file_handle   UTL_FILE.file_type;
Write_content  VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
–打开文件
–Write_file_name := rtrim(to_char(SYSDATE,’YYYY-MM-DD’))||’.log’;
Write_file_name := ‘db108_alert.log’;
file_handle   := UTL_FILE.FOPEN(‘/u01/product/admin/ora81/logs’,Write_file_name,’a’);
Write_content  := to_char(SYSDATE,’yyyy-mm-dd hh24:mi:ss’)||’||’||textContext;
–写文件
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.PUT_LINE(file_handle,Write_content);
END IF;
–关闭文件
UTL_FILE.Fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.Fclose(file_handle);
END IF;
END sp_Write_log;

2、发送Email的过程

name:sp_Send_mail
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
create date:2003-06-01
creater:chen jiping
desc: &#8226;发送邮件到指定邮箱
&#8226;只能指定一个邮箱,如果需要发送到多个邮箱,需要另外的辅助程序

create or replace procedure sp_send_mail(
Rcpter IN VARCHAR2,
Mail_Content IN VARCHAR2)
IS
conn utl_smtp.connection;
PROCEDURE send_header(NAME IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME || ‘: ‘ || header || utl_tcp.CRLF);
END;

BEGIN
conn := utl_smtp.open_connection(‘smtp.ur.net.cn’);
utl_smtp.helo(conn, ‘oracle’);
utl_smtp.mail(conn, ‘oracle info’);
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
send_header(‘From’,  ’Oracle Database’);
send_header(‘To’,   '”Recipient” <‘||Rcpter||’>’);
send_header(‘Subject’, ‘Hello’);
utl_smtp.write_data(conn, utl_tcp.CRLF || Mail_Content);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; — When the SMTP server is down or unavailable, we don’t have
— a connection to the server. The quit call will raise an
— exception that we can ignore.
END;
raise_application_error(-20000,
‘Failed to send mail due to the following error: ‘ || SQLERRM);
END sp_send_mail;

3、监控数据库关闭/启动的触发器

create or replace trigger TR_DB_SHUTDOWN
before shutdown ON DATABASE
DECLARE
msMsg VARCHAR2(500);
BEGIN
msMsg :=’user ‘||ora_login_user||’ in ‘
||ora_client_ip_address||’ ready shutdown database ‘
||ora_database_name|| ‘ now’;
sp_send_mail(‘urmail@mail.com’,msMsg);
EXCEPTION
WHEN OTHERS THEN
sp_send_mail(‘urmail@mail.com’,ora_database_name||’ shutdown error’);
END;
说明:当数据库关闭之前,发送Mail到指定邮箱.不要在关闭/启动数据库的触发器中调用utl_file包写文件,可能会导致BUG,引起数据库不能启动,如果实在有必要,则关闭数据库与启动数据库不要使用同一个会话。

4、监控登录用户的触发器

先需要建立一张表,用于存放登陆信息。
create table LOG$INFORMATION
(
ID    NUMBER(10),
USERNAME VARCHAR2(30),
LOGINTIME DATE,
TERMINAL VARCHAR2(50),
IPADRESS VARCHAR2(20),
OSUSER  VARCHAR2(30),
MACHINE  VARCHAR2(64),
PROGRAM  VARCHAR2(64),
SID    NUMBER,
SERIAL#  NUMBER,
AUSID   NUMBER
)

然后需要创建一个序列,才产生连续的序列号,根据序列的信息,可以更好的得到登录的信息。

create sequence SQ_LOGIN
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;

最后创建触发器,记载登录信息。
CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
SELECT * FROM v$session
WHERE audsid = iiQuerySid;
BEGIN
OPEN cSession(userenv(‘SESSIONID’));
FETCH cSession INTO mtSession;
IF cSession%FOUND THEN
INSERT INTO log$information(id,username,logintime,terminal,ipadress,osuser,machine,
program,sid,serial#,ausid)
VALUES(sq_login.nextval,USER,SYSDATE,mtSession.Terminal,
SYS_CONTEXT (‘USERENV’,’IP_ADDRESS’),mtSession.Osuser,
mtSession.Machine,mtSession.Program,mtSession.
Sid,mtSession.Serial#,userenv(‘SESSIONID’));
ELSE
sp_write_log(‘session信息错误:’||SQLERRM);
raise_application_error(-20099,’登录异常错误’,FALSE);
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
sp_write_log(‘登记登录信息错误:’||SQLERRM);
RAISE;
END;

说明:这个触发器监控所有登录用户,并把其信息存入到以上表中。根据表中记载的信息,可以获得所有登录信息,用于审计用户的登陆是否许可。

 

利用Oracle FGA实现审计

在《初识企业内容管理平台-Documentum》中提到,由于Documentum的极其保守,所以无法对其系统,尤其是数据库设计部分进行深入了解(不提供对数据库表的说明书)。为了对数据库部分进行了解,需要进行一下反向的工作:通过在Webtop/Da中进行操作,观察数据库表的变化。这就需要实现对Oracle数据库表操作的审计。当然,这样的反向工作,对了解其它未知的应用系统也同样有作用。

Oracle9i Database 推出了一种称为细粒度审计 (FGA) 的新特性。现在下面就利用FGA实现审计对表的审计。其中,EDMS是数据库的一个测试帐户。

1.建立测试表(用户EDMS下)
Create Table T_AUDIT_DEMO
(
CID INT NOT NULL, CNAME VARCHAR2(20) NULL, ENAME VARCHAR2(20) NULL,
CONSTRAINT PK_T_AUDIT_DEMO PRIMARY KEY (CID)
);

2.创建审计策略(用户SYS下)
begin
dbms_fga.add_policy
(
object_schema=>’EDMS’, object_name=>’T_AUDIT_DEMO’,
policy_name=>’T_AUDIT_DEMO_AUDIT’
);
end;

3.测试审计效果(用户EDMS下)
INSERT INTO T_AUDIT_DEMO VALUES(1,’曾勋’,’ZENG XUN’);
INSERT INTO T_AUDIT_DEMO VALUES(2,’翁黎明’,’WENG LI MING’);
INSERT INTO T_AUDIT_DEMO VALUES(3,’刘帝勇’,’LIU DI YONG’);

4.使用Select查询测试表(用户EDMS下)
SQL> SELECT * FROM T_AUDIT_DEMO;
CID CNAME ENAME
—– ——————– ——-
1曾勋ZENG XUN
2翁黎明WENG LI MING
3刘帝勇LIU DI YONG

5.再次查看审计效果(用户SYS下)
SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;
STATEMENT_TYPE SQL_TEXT
————– ———–
SELECT SELECT * FROM T_AUDIT_DEMO

注意:之前的Insert语句并不在审计中。默认的只对Select进行审计。在Oracle 9i中它只捕获 SELECT 语句。而在Oracle 10i中进行了扩展,支持对所有类型的DML进行审计。

6.修改审计粒度(用户SYS下)
begin
dbms_fga.add_policy
(
object_schema=>’EDMS’, object_name=>’T_AUDIT_DEMO’, policy_name=>’T_AUDIT_DEMO_AUDIT’, statement_types=>’INSERT, UPDATE, DELETE, SELECT’
);
end;
注意:不能实现对Truncat Table的审计。

7.测试审计(用户EDMS、SYS下)
INSERT INTO T_AUDIT_DEMO VALUES(4,’黄智洪’,’HUANG ZHI HONG’);
DELETE FROM T_AUDIT_DEMO WHERE CID < 4;
SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;
STATEMENT_TYPE SQL_TEXT
————– ——————–
SELECT SELECT * FROM T_AUDIT_DEMO
INSERT INSERT INTO T_AUDIT_DEMO VALUES(4,’黄智洪’,’HUANG ZHI HONG’)
DELETE DELETE FROM T_AUDIT_DEMO WHERE CID < 4
SELECT SELECT * FROM T_AUDIT_DEMO
至此,我们已经实现了对表T_AUDIT_DEMO的审计。与FGA相关的表或者视图:

select * from fga$
select * from fga_log$
select * from fgacol$
select * from dba_fga_audit_trail
select * from dba_common_audit_trail
select * from dba_audit_policies
select * from dba_fga_audit_trail
与FGA相关的包或者过程:
dbms_fga.add_policy dbms_fga.drop_policy
至于这些表、视图、包的列或者参数的使用方法,可以Describe或者查看相关文档。

© 2019 Init dba

Theme by Anders NorenUp ↑