oracle 查询优化器(optimizer)

1.优化器分类:
基于规则的优化器[Rule-based optimizer] ==> 10g中已经不使用
基于成本的优化器[Cost-Based Optimizer]

2.设置优化器模式:
查看数据库缺省优化:show parameter mode

基于实例:
alter system set optimizer_mode ={Choose|Rule|First_rows|First_rows_n|All_rows}

基于会话:
ALTER SESSION SET optimizer_mode ={Choose|Rule|First_rows|First_rows_n|All_rows}

参数意义:
CHOOSE:仅在9i及之前版本中被支持,10g已经废除。
RULE:仅在9i及之前版本中被支持,10g已经废除。
ALL_ROWS:在10g中为默认值,以吞吐量为基础
FIRST_ROWS:CBO尽可能快速的返回结果集的前面少数行记录,以响应速度为基础
FIRST_ROWS_n:不论是否存在统计信息,都使用CBO优化器,并以最快的速度返回前n行记录,n可以是1,10,100,1000。

3.影响执行计划的因素:

(1).访问路径
优化器可用的访问路径如下:

a.全表扫描(Full Table Scans)
注:访问数据量大,返回数据超过5%,不建议使用索引
oracle扫描时,多块读(一次I/O,读多个块,块必须连续,默认为16个块,具体查看 show parameter db ==> db_file_multiblock-read-count)

b.Rowid扫描(Rowid Scans):最快

c.索引扫描(Index Scans):唯一性索引,索引中最快的一种[空值不入索引,需要加一常量,使空值进入索引]
索引扫描类型:
唯一索引扫描(Index Unique Scans)
索引范围扫描(Index Range Scans):基于规则的,不支持组合索引
索引降序范围扫描(Index Range Scans Descending)
跳跃式索引扫描(Index Skip Scans):数据类型不一致
全索引扫描(Full Index Scans)
快速全索引扫描(Fast Full Index Scans)
索引连接(Index Joins)
注:组合索引中,是将组合看成列,如果查询中包涵非索引的列,则只走组合索引的第一列

d.簇扫描(Cluster Scans):
在被索引的簇中,有着相同簇键值的行存储在同一数据块中。执行簇扫描时,首先通过扫描簇索引获得被检索行的Rowid,然后使用Rowid来定位具体的行。

e.散列扫描(Hash Scans):
散列扫描就是在一个散列簇中定位数据行。在一个散列簇中,具有相同散列值的行存储在相同的数据块中。
在执行散列扫描时,首先通过一个散列函数来获得散列值,然后用散列值在数据块中定位具体行

(2).连接(join)
连接方式:

嵌套循环:[Nested loop join] ==>USE_NL(table1 table2)hint is used.(/*+ use_nl(e d) */)

hash循环:[Hash join] ==> USE_HASH(table1 table2)

排序合并:[Sort-merge join] ==>use_merge(table1 table2)

(3).物化视图(materialized views)
物化视图创建格式:

  1. CREATE MATERIALIZED VIEW mview_name  
  2. [TABLESPACE ts_name]  
  3. [PARALLEL (DEGREE n)]  
  4. [BUILD {IMMEDIATE|DEFERRED}]  
  5. [{ REFRESH {FAST|COMPLETE|FORCE}  
  6. [{ON COMMIT|ON DEMAND}] | NEVER REFRESH } ]  
  7. [{ENABLE|DISABLE} QUERY REWRITE]  
  8. AS SELECT … FROM …  

注:
refresh [fast|complete|force] 视图刷新的方式:
fast: 增量刷新.
假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.
为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。
create materialized view log on 表名 with ……
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
范例:

  1. sys> conn hr/hr  
  2. hr_1>create table e as select * from employees;  
  3. hr_1>create table d as select * from departments;  
  4. hr_1>insert into e select * from e;(54784 row)  
  5. hr_1>insert into d select * from d;(55296 row)  
  6.   
  7. hr_2>set autot on  
  8. hr_2>set timing on  
  9. hr_2>select dd.department_name,sum(ee.salary) from e ee,d dd where ee.department_id=dd.department_id group by dd.department_name;  
  10.   
  11. sys> grant CREATE MATERIALIZED VIEW to hr;  
  12.   
  13. sys> CREATE MATERIALIZED VIEW LOG ON e WITH SEQUENCE, ROWID  
  14.      (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)  
  15. INCLUDING NEW VALUES;  
  16.   
  17. sys> CREATE MATERIALIZED VIEW LOG ON d WITH SEQUENCE, ROWID(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)  
  18. INCLUDING NEW VALUES;  
  19.   
  20. hr_1>create materialized view depart_sal_sum  
  21.      refresh fast  
  22.      on commit  
  23.      ENABLE QUERY REWRITE as  
  24.      select dd.department_name,sum(ee.salary) from e ee,d dd where ee.department_id=dd.department_id group by dd.department_name;  
  25.   
  26. hr_2>select dd.department_name,sum(ee.salary) from e ee,d dd where ee.department_id=dd.department_id group by dd.department_name;  

删除物化视图: drop materialized view 视图名称

物化视图表:user_mviews

查看相应信息:
select MVIEW_NAME,QUERY from user_mviews;

有关oracle优化入门知识

1.优化的几个方面:
应用
实例配置
操作系统

2.解决优化问题的步骤:
收集信息
分析数据
部分解决[每次只解决一个问题]

3.指标:
最短应答时间:服务时间+等待时间
最少资源使用:

4.告警日志的位置
10g下,在bdump目录的下的alert.log,为文本格式,可直接打开,需定期删除,不然会无线增大
11g下,为自动诊断资料库,在diag/rdbms/目录下,为xml格式,建议使用em[企业管理器]查看

5.后台进程跟踪文件,在bdump目录下:
格式:数据库名字+进程号+线程号.trc

6.用户追踪文件
10g下,在udump目录下,
11g下,在trace目录下
格式:数据库名字+ora+进程号

7.跟踪的分类:

(1).用户自己追踪自己:

  1. sys>grant alter session to scott; # 授予用户权限  
  2. scott>alter session set sql_trace=true; # 开启自己跟踪自己的权限  
  3. scott>select * from emp # 这里可以执行任意DML语句….  
  4. scott>alter sessiom set sql_trace=false; # 关闭跟踪  

查看跟踪文件:
存放位置:/u01/app/oracle/admin/orcl/udump/*
在此目录下,执行LL -t,默认最上面一个就是刚才的文件,也可以使用v$session和v$process两个视图查出进程号
因此文件不易读,需转换成可读的文本:

  1. 格式:  
  2. tkprof 原文件名 新文件名 explain=用户名/密码 sys=no  
  3. 如下:  
  4. tkprof orcl_ora_1928.trc 2.txt explain=scott/tiger sys=no  

现在就可以查看输出文件了

(2).自动追踪:

  1. SYS>@/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce; # 自动追踪需要plutrce角色,需要安装  
  2. SYS>grant plustrace to scott; # 将角色授予scott用户  
  3. SCOTT>@/u01/app/oracle/product/10.2.0/db_1/rdbsms/admin/utlxplan; # 安装执行计划表  
  4. demo:  
  5. scott>set autotrace on   # 开启自动追踪,这种是查询时就显示执行计划,比较方便  
  6. scott>select ename,sal from e where empno=7902;  
  7. scott>create index e_empno_ename_sal on e(empno,ename,sal);  
  8. scott>select ename,sal from e where empno=7902;  
  9.   
  10. explain plan   # 手动生成一个执行计划  
  11. scott>explain plan for select ename,sal from e where empno=7902;  
  12. scott>select * from table(dbms_xplan.display);  

8.清空共享池/buffer_cache:

  1. 清空共享池:alter system flush shared_pool    # 字典信息,库信息都被清楚,生产环境下,不要做此操作,影响性能  
  2. 清空buffer_cache:alter system flush buffer_cache # 清空所有缓存的数据信息  

9.dbms_monitor跟踪:

(1).跟踪会话:# 多用于跟踪用户的session_id

  1. SYS>dbms_monitor.SESSION_TRACE_ENABLE(session_id,serial_num,wait,binds);  
  2. SYS>dbms_monitor.SESSION_TRACE_disABLE(session_id,serial_num);  

(2).跟踪服务名:

  1. # 同一用户从不同客户端登陆同一服务  
  2. $sqlplus scott/tiger@orcl  
  3. $sqlplus scott/tiger@orcl  
  4. SYS>exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE(service_name=>’orcl’); # 开启追踪,服务名就是网络服务名  
  5. SCOTT_1>select * from emp where empno=7788;  
  6. SCOTT_2>select * from emp where empno=7369;  
  7. SYS>exec dbms_monitor.SERV_MOD_ACT_TRACE_DISABLE(service_name=>’orcl’); # 关闭追踪  
  8. $trcsess output=trace.txt service=orcl * # 将不同终端信息汇总  
  9. $tkprof trace.txt tk.txt explain=scott/tiger sys=no # 转换文件格式为文本格式  
  10. $vi tk.txt # 查看文本信息  

10.以上的一些操作,oracle提供一个b/s架构的企业管理器,方便管理[非常方便]

(1).查询em是否已经安装[默认,安装数据库时,数据库就已经安装] –> 安装了EM,在数据库家目录会有一个和你主机名差不多的目录

  1. SYS>select comp_name,version from dba_registry;  
  2. SYS>select OCCUPANT_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants;  

(2).创建企业管理器:

  1. a.配置资料库:  
  2. $emca -repos create  
  3. b.配置数据库控制台:  
  4. $emca -config dbcontrol db  
  5. c.开启企业管理器[em]  
  6. $emctl start dbconsole  

(3).删除企业管理器:

  1. a.停止企业管理器[em]  
  2. $emctl stop dbconsole  
  3. b.删除数据库控制台:  
  4. $emca -deconfig dbcontrol db  
  5. c.删除资料库  
  6. $emca -repos drop  

11.statspack:
(1).安装statspack,使用spcreate.sql脚本:

  1. sys>@/u01/oracle/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate; # 删除使用spdrop.sql  
  2. sys>select OCCUPANT_NAME,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;  
  3. sys>exec statspack.snap; # 创建一个snapshot,一般每隔20分钟执行一次  
  4. …  
  5. …  
  6. sys>exec statspack.snap;  
  7. sys>@$ORACLE_HOME/rdbms/admin/spreport # 生成报告,需要至少2个snapshot,才可以生成报告  
  8. # 报告默认存放在用户家目录下  
  9. # 还有一些脚本:  
  10. Automate the collection of statistics with spauto.sql.  
  11. Produce a report by using spreport.sql or sprepsql.sql.  
  12. Purge Statspack data with statspack.purge or sppurge.sql.  
  13. Truncate all Statspack tables with sptrunc.sql.  
  14. Export the Statspack repository with spexp.par.  

12.awr:automatic workload repository ==> 自动负载资料库
# 每小时收集一次,保留7天[放在sysaux下面],后台进程为:mmon
ADDM:自动数据库诊断监控器,自动分析

13.以上这些,oracle的企业管理器都提供了这种操作,非常方便。

oracle 锁机制

1.锁的类型有:[oracle锁机制是自动管理]

dml 锁

ddl 锁

shared 锁

互斥锁 : 主要针对内存的的锁[share pool]

2.锁的模式有:

0:none

1:null

2:row share(RS) (lock a row in shared mode)   ==> 行级锁
加锁语法:LOCK TABLE TableName IN ROW SHARE MODE;
仅能保护表不被删除

3:row exclusive(RX) (lock a row in exclusive mode)    ==> 行级排它锁
加锁语法:LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
不允许改变表的结构,可删除表内数据,但不可删除列

4:share(S) (lock the entire table in shared mode)    ==> 共享锁
加锁语法:LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
不允许两个人同时对一张表执行除select的dml语句或ddl语句

5:share row exclusive(SRX)(lock the table in shared mode but a row in exclusive) ==> 共享行级排它锁
加锁语法:Lock Table TableName In Share Row Exclusive Mode;
仅能查询(独占行,不得执行dml,除select外)

6:exclusive(X) (lock the entire table in exclusive mode) ==> 排他锁
加锁语法:Lock Table TableName In Exclusive Mode;
仅能查询

3.与锁相关的视图
DBMS_LOCK
V$DLM_ALL_LOCKS
V$DLM_LOCKS
V$ENQUEUE_LOCK
V$GLOBAL_BLOCKED_LOCKS
V$LOCK
V$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY
V$LOCK_ELEMENT
V$_LOCK

4.解锁及 Kill Session:
使用下面的语法查出锁并杀掉 Session。
SELECT A.SID,A.SERIAL#,A.USERNAME,B.TYPE FROM V$SESSION A,V$LOCK B WHERE A.SID=B.SID;
ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’;

oracle 分区表管理

1.oracle分区表:
逻辑上是一张大表,物理上是由若干小表组成

2.oracle分区表的分类:
范围分区表(Range partitioning)
列表分区表(List partitioning)
哈希分区表(Hash partitioning)
组合分区表:
(Composite range-hash partitioning:先做hash,再做范围)
(Composite range-list partitioning:先做列表,再做范围)

3.范例:

(1).创建范围分区表

  1. –为各个分区准备独立的表空间  
  2. create tablespace test_space01 datafile ‘/u01/app/oracle/oradata/orcl/ts_test01.dbf’ size 50m  
  3. create tablespace test_space02 datafile ‘/u01/app/oracle/oradata/orcl/ts_test02.dbf’size 50m  
  4. create tablespace test_space03 datafile ‘/u01/app/oracle/oradata/orcl/ts_test03.dbf’size 50m  
  5. create tablespace test_space04 datafile ‘/u01/app/oracle/oradata/orcl/ts_test04.dbf’size 50m  
  6. –创建分区表,  
  7. CREATE TABLE range_example  
  8. (  
  9. range_key_column DATE,  
  10. DATA VARCHAR2(20),  
  11. ID integer  
  12. )  
  13. PARTITION BY RANGE(range_key_column)  
  14. (  
  15. PARTITION part01 VALUES LESS THAN(TO_DATE(‘2008-07-01 00:00:00′,’yyyy-mm-dd hh24:mi:ss’)) TABLESPACE test_space01,  
  16. PARTITION part02 VALUES LESS THAN(TO_DATE(‘2008-08-01 00:00:00′,’yyyy-mm-dd hh24:mi:ss’)) TABLESPACE test_space02,  
  17. PARTITION part03 VALUES LESS THAN(TO_DATE(‘2008-09-01 00:00:00′,’yyyy-mm-dd hh24:mi:ss’)) TABLESPACE test_space03,  
  18. PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test_space04  
  19. );  
  20. –插入测试数据  
  21. insert into range_examplevalues(TO_DATE(‘2008-06-10 00:00:00′,’yyyy-mm-dd hh24:mi:ss’), ‘1111’, 1);  
  22. insert into range_examplevalues(TO_DATE(‘2008-07-20 00:00:00′,’yyyy-mm-dd hh24:mi:ss’), ‘2222’, 2);  
  23. insert into range_examplevalues(TO_DATE(‘2008-08-25 00:00:00′,’yyyy-mm-dd hh24:mi:ss’), ‘3333’, 3);  
  24. commit;  
  25. –在表上执行查询  
  26. select * from range_example;  
  27. –在表分区上执行查询  
  28. select * from range_example partition(part01);  

(2).创建列表分区表
创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中, 否则不能被插入。
示例代码:

  1. CREATE TABLE list_example(  
  2. dname VARCHAR2(10),  
  3. DATA VARCHAR2(20)  
  4. )  
  5. PARTITION BY LIST(dname)  
  6. (  
  7. PARTITION part01 VALUES(‘初始登记’,’转移登记’),  
  8. PARTITION part02 VALUES(‘更名登记’,’楼盘变更’),  
  9. PARTITION part03 VALUES(‘抵押登记’),  
  10. PARTITION part03 VALUES(‘限制登记’)  
  11. );  

(3).创建哈希分区表
创建一个按字段数据 Hash 值分区的表
示例代码:

  1. CREATE TABLE hash_example(  
  2. hash_key_column DATE,  
  3. DATA VARCHAR2(20)  
  4. )  
  5. PARTITION BY HASH(hash_key_cloumn)  
  6. (  
  7. PARTITION part01,  
  8. PARTITION part02  
  9. );  

(4).组合分区表
在分区中可以再建立子分区,以实现分区组合。可任意对上述各类分区进行组合分区。
此例中创建了一个由范围分区和哈希分区组合实现分区的表。
示例代码:

  1. CREATE TABLE range_hash_example(  
  2. range_column_key int,  
  3. hash_column_key INT,  
  4. DATAVARCHAR2(20)  
  5. )  
  6. PARTITION BY RANGE(range_column_key)  
  7. SUBPARTITION BY HASH(hash_column_key) SUBPARTITIONS 2  
  8. (  
  9. PARTITION part_1 VALUES LESS THAN (100000000)  
  10. (  
  11. SUBPARTITION part_1_sub_1,  
  12. SUBPARTITION part_1_sub_2,  
  13. SUBPARTITION part_1_sub_3  
  14. ),  
  15. PARTITION part_2 VALUES LESS THAN (200000000)  
  16. (  
  17. SUBPARTITION part_2_sub_1,  
  18. SUBPARTITION part_2_sub_2  
  19. )  
  20. );  
  21. –注 subpartitions 2 并不是指定 subpartition 的个数一定为2,实际上每个分区的子分区个数可以不同。  
  22. 如果不指定 subpartition 的具体明细,则系统按照 subpartitions 的值  
  23. 指定 subpartition 的个数生成子分区,名称由系统定义 。  

4.分区表其他相关操作:

(1).增加表分区

  1. — range partitioned table  
  2. ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE(‘2008-10-1 00:00:00′,’yyyy-mm-ddhh24:mi:ss’));  
  3. –list partitioned table  
  4. ALTER TABLE list_example ADD PARTITION part04 VALUES (‘TE’);  
  5. –Adding Values for a List Partition  
  6. ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES(‘MIS’);  
  7. –Dropping Values from a List Partition  
  8. ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES(‘MIS’);  
  9. –hash partitioned table  
  10. ALTER TABLE hash_example ADD PARTITION part03;  

(2).增加 subpartition

  1. ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4;  
  2. # 注:hash partitioned table 新增 partition 时,现有表的中所有 data 都有重新计算 hash值,然后重新分配到分区中。所以被重新分配的分区的 indexes 需要 rebuild 。  

(3).删除分区

  1. ALTER TABLE … DROP PARTITION part_name;  

(4).分区合并

  1. 合并父分区  
  2. ALTER TABLE range_example MERGE PARTITIONS part01_1, part01_2 INTO PARTITION part01 UPDATE INDEXES;  
  3. –如果省略 update indexes 子句的话,必须重建受影响的分区的 index;  
  4. ALTER TABLE range_example MODIFY PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;  
  5. 合并子分区  
  6. ALTER TABLE composite_example MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;  

(5).转换分区
可以将分区表转换成非分区表,或者几种不同分区表之间的转换。
如下:

  1. CREATE TABLE hash_part02 AS SELECT * FROM hash_example WHERE 1=2;  
  2. ALTER TABLE hash_example EXCHANGE PARTITION part02 WITH TABLE hash_part02;  
  3. 这时,分区表 hash_example 中的 part02分区的资料将被转移到 hash_part02这个非分区表中。  

(6).移动表分区到另一个表空间

  1. alter table sales move partition part03 tablespace example;  
  2. ALTER TABLE sales RENAME PARTITION part03 TO sales_q1_q2_1998;  

(7).从一个范围分区表分离一个分区

  1. ALTER TABLE sales split PARTITION sales_q1_q2_1998 at (to_date(‘1998-04-01′,’yyyy-mm-dd’)) into (partition sales_q1_1998,partition sales_q2_1998);  

(8).清空分区表内某个分区的数据

  1. alter table sales truncate partition sales_q2_1998 ;  

(9).关于分区表和索引
在分区表上可以建立三种类型的索引:

A.和普通表一样的全局索引;

B.全局分区索引;

C.本地分区索引。

以表 range_example 为例。

  1. A.建立普通的索引  
  2. create index com_index_range_example_id on range_example(id);  
  3.   
  4. B.建立本地分区索引  
  5. create index local_index_range_example_id on range_example(id) local;  
  6.   
  7. C.建立全局分区索引  
  8. create index gidx_range_exampel_id on range_example(id)  
  9. GLOBAL partition by range(id)  
  10. (  
  11. part_01 values less than(1000),  
  12. part_02 values less than(MAXVALUE)  
  13. );  
  14. 对于分区索引的删除,local index 不能指定分区名称,单独的删除分区索引。  
  15. local index 对应的分区会伴随着 data 分区的删除而一起被删除。  
  16. globalpartition index可以指定分区名称,删除某一分区。但是有一点要注意,如果该分区不为空,则会导致更高一级的索引分区被置为 UNUSABLE 。  
  17. ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句将导致 part_02 状态为 UNUSABLE  

5.分区表相关的视图:
user_part_tables
user_tab_partitions
user_part_key_columns

oracle 日志挖掘(LogMiner)

1.LogMiner工具即可以用来分析在线,也可以用来分析离线日志文件,即可以分析本身自己数据库的重作日志文件,也可以用来分析其他数据库的重作日志文件。
总的说来,LogMiner 工具的主要用途有:

(1).跟踪数据库的变化:可以离线的跟踪数据库的变化,而不会影响在线系统的性能。

(2).回退数据库的变化:回退特定的变化数据,减少point-in-time recovery的执行。

(3).优化和扩容计划:可通过分析日志文件中的数据以分析数据增长模式。

(4).确定数据库的逻辑损坏时间:准确定位操作执行的时间和SCN ==> 基于时间和SCN 的恢复

(5).确定事务级要执行的精细逻辑恢复操作

(6).执行后续审计

2.用户执行日志挖掘,需要权限:execute_catralog_role

3.安装LogMiner 工具,必须首先要运行下面这样两个脚本:

  1. $ORACLE_HOME/rdbms/admin/dbmslm.sql      #  创建DBMS_LOGMNR 包,该包用来分析日志文件。  
  2. $ORACLE_HOME/rdbms/admin/dbmslmd.sql    #  用来创建DBMS_LOGMNR_D 包,该包用来创建数据字典文件。  
  3. # 注:这两个脚本必须均以SYS 用户身份运行。  

4.基本对象
Source Database: 日志所属的数据库
Mining Database: 执行LogMiner 操作要使用的数据库,相同硬件平台,相同字符集,版本不低于Source Database
LogMiner字典: 日志文件中,对于表等用户对象,并不是保存名字,而是保存一个ID号;
建立字典文件的目的就是使logminer在分析时可以将Object ID翻译成我们所熟悉的对象名,在Source Database上生成

5.LogMiner字典三种类型:

(1).使用源数据库数据字典[ 表结构无变化 Source-DB必须OPEN,只能跟踪DML不能为DDL ]

  1. SQL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );  

(2).摘取LogMiner字典到重做日志[ Source-DB 必须OPEN, Archivelog模式 ]

  1. SQL> exec DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS );  

(3).摘取LogMiner 字典到字典文件,配置字典文件所在目录: 静态参数: UTL_FILE_DIR
建立字典文件:

  1. SQL> exec dbms_logmnr_d.build (dictionary_filename=>’dictionary.ora’,  
  2. dictionary_location=>’/u02/app/oracle/logminer’,  
  3. OPTIONS=>DBMS_LOGMNR_D.STORE_IN_FLAT_FILE ); # 存储到平面文件  

6.与LogMiner相关的数据字典:

  1. v$loghist   # 显示历史日志文件的一些信息  
  2. v$logmnr_dictionary   # 因logmnr 可以有多个字典文件,显示字典文件信息  
  3. v$logmnr_parameters   # 显示logmnr 的参数  
  4. v$logmnr_logs   # 显示用于分析的日志列表信息  
  5. v$logmnr_contents   # 挖掘结果  
  6. dbms_logmnr_d   # 生成日志挖掘数据字典  
  7. dbms_logmnr   # 日志挖掘相关函数  

7.使用LogMiner 进行分析[完整顺序]

(1).设定用于LogMiner分析的日志文件存放的位置
设置 UTL_FILE_DIR,需要重启数据库。
设置 utl_file_dir=* 表示你能操作任何目录
在initsid.ora文件中加入utl_file_dir 参数或者:

  1. SQL> alter system set utl_file_dir=’/u02/app/oracle/logminer’ scope=spfile;  
  2. SQL> shutdown immediate  
  3. SQL> starup  

(2).生成数据字典文件

  1. SQL> exec dbms_logmnr_d.build (dictionary_filename=>’dict.ora’,dictionary_location=>’/u02/app/oracle/logminer’,);  
  2. # 注 dictionary_location 指的是Logminer 数据字典文件存放的位置,它必须匹配utl_file_dir 的设定。
    dictionary_filename 指的是放于存放位置的字典文件的名字,名字可以任意取。

(3).建立日志分析表
建立日志分析表数据库必须在mount或nomount状态;建立日志分析表,使用dbms_logmnr.add_logfile()

  1. SQL>exec dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/orcl/redo02.log’,options=>dbms_logmnr.new);  
  2. # 其中的options有三种取值:  
  3. dbms_logmnr.new:用于建一个日志分析表  
  4. dbms_logmnr.addfile:用于加入用于分析的的日志文件  
  5. dbms_logmnr.removefile:用于移出用于分析的日志文件  

(4).启动LogMiner进行分析
[分析指定的时间段或者指定的scn号]

  1. SQL>dbms_logmnr.start_logmnr(DictFileName =>’/u02/app/oracle/logminer/dict.ora’,starttime => to_date(‘20080710 09:15:00′,’yyyymmdd hh24:mi:ss’),endtime => to_date(‘20080710 10:00:00′,’yyyymmdd hh24:mi:ss’));  
  2. # 即分析2008 年7 月10 日这天9:15 至10:00 这段时间,并把分析结果放到数据字典中以用于查询。还有两个参数startscn (起始SCN号) 及endscn (终止SCN) 号。  
  3. 或者[全部分析]  
  4. SQL>exec dbms_logmnr.start_logmnr(DictFileName =>’/u02/app/oracle/logminer/dict.ora’);  

(5).查看日志分析的结果[存储在此视图内:V$logmnr_contents] ==> 具体查询操作,需根据实际情况来查询

查看DML操作:

  1. SQL>SELECT operation, sql_redo, sql_undo FROM V$logmnr_contents WHERE table_name=’E’;  
  2. # 注:其中operation指的是操作,sql_redo指的是实际的操作,sql_undo指的是用于取消的相反的操作。  

查看DDL操作:

  1. SQL>SELECT timstamp, sql_redo FROM v$logmnr_contents WHERE upper(sql_redo) like ‘%TRUNCATE%’;  

(6).结束LogMiner的分析

  1. SQL>exec dbms_logmnr.end_logmnr;  

8.案例:

  1. SYS>alter system set utl_file_dir=’/u02/app/oracle/logminer’ scope=spfile;  
  2. SYS>shutdown immediate  
  3. SYS>startup  
  4. SYS>exec dbms_logmnr_d.build(‘dict.ora’,’/u01/app/oracle/logminer’);  
  5. SYS>exec dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/orcl/redo01.log’,options=>dbms_logmnr.new);  
  6. SYS>exec dbms_logmnr.start_logmnr(DictFileName =>’/u02/app/oracle/logminer/dict.ora’);  
  7. SYS>select username,scn,to_char(timestamp,’yyyy-mm-dd hh24:mi:ss’),sql_redo from v$logmnr_contents where table_name=’E’;  
  8. SYS>exec dbms_logmnr.end_logmnr;  

oracle 数据库的升级

1.oracle数据库升级
基于 Linux(Oracle Linux 5.4/2.6.18-164.el5PAE)平台下 Oracle 10.2.0.1 升级到10.2.0.4

2.下载升级的软件包:从oracle官网下载升级包:
注: 9i 以前,无论升级/降级,数据库都是 startup migrate
10g 后增加了 upgrade 参数,升级可直接用 startup upgrade,降级仍是 startup migrate

3.升级的步骤如下:

1)单实例升级先决条件:

(1).表空间需求
确保 system 表空间至少有10M 空间可用,下面给出查询语句

  1. SELECT upper(f.tablespace_name) “tablespace_name”,d.tot_grootte_mb “tablespace_size(M)”,  
  2.        d.tot_grootte_mb – f.total_bytes “used_size(M)”,  
  3.        to_char(round((d.tot_grootte_mb – f.total_bytes) / d.tot_grootte_mb * 100,2),’990.99′) “Usage_Percent”,  
  4.        f.total_bytes “free_size(M)”  
  5. FROM   
  6. (SELECT tablespace_name,round(SUM(bytes) / 1024 / 1024) total_bytes,round(MAX(bytes) / (1024 * 1024), 2) max_bytes  
  7.  FROM sys.dba_free_space  GROUP BY tablespace_name) f,(SELECT dd.tablespace_name,  
  8.          round(SUM(bytes) / 1024 / 1024) tot_grootte_mb  
  9.         FROM sys.dba_data_files dd  GROUP BY dd.tablespace_name) d  
  10.  WHERE d.tablespace_name = f.tablespace_name  
  11.  ORDER BY 2 DESC;  

(2).系统参数:
确保参数 SHARED_POOL_SIZE 和 JAVA_POOL_SIZE 大于150MB 以上,为加快升级速度,在系统内存可用的情况下,可临时调大这2个参数

  1. SQL> SHOW PARAMETER SHARED_POOL_SIZE  
  2. SQL> SHOW PARAMETER JAVA_POOL_SIZE  
  3. SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=’200M’ SCOPE=spfile;  
  4. SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=’200M’ SCOPE=spfile;  

2).实施升级

(1).收集升级前数据库信息[很重要,需要根据里面的内容去更改相应内容,注意保留]

  1. SQL> spool upgrade_info.log  
  2. SQL> @utlu112i.sql  
  3. SQL> spool off  

(2).查询库中是否有invalid的对象:

  1. SQL> select comp_name,version,status from sys.dba_registry;  
  2. 注:如果有无效选项,重新使用此脚本utlrp.sql编译无效对象  

(3).关闭需要升级的实例

  1. SQL> shutdown immediate  
  2. 停止与该实例相关的所有后台进程  
  3. lsnrctl stop  # 关闭监听器  
  4. emctl stop dbconsole # 关闭em  

(4).备份 Oracle Home 目录及数据库

  1. tar -cvf $ORACLE_BASE /orabak/ –确保 Oracle 相关的所有配置都位于$ORACLE_BASE 目录,如监听等  
  2. cp *.dbf con*.ora redo*.log /orabak/ –对数据库实施冷备  

(5).升级软件[ 升级数据库软件 ]

  1. ./runIstanller –> oracle 账户  
  2. root.sh –> root 账户  

(6).更新数据字典:[ 升级数据库 ]

  1. SQL> startup upgrade  
  2. SQL> spool patch.log  
  3. SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql   
  4. SQL> spool off  

(7).重编译失效对象:

  1. sql>shutdown immediate  
  2. sql>startup  
  3. SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql  

(8).升级后的检测

  1. SQL>select comp_name,version,status from sys.dba_registry;  
  2. 检查组件的升级情况  
  3. SQL>select * from utl_recomp_errors;  

(9).修改兼容性参数

  1. SQL> alter system set compatible=’10.2.0.4.0′ scope=spfile;  

(10).重新启动数据库:

  1. SQL> SHUTDOWN  
  2. SQL> STARTUP  

(11).如果使用了恢复目录,则执行下面的命令

  1. $ rman catalog username/password@alias  
  2. RMAN> UPGRADE CATALOG;  

(12).升级回退:

  1. SQL> STARTUP DOWNGRADE  
  2. SQL> SPOOL downgrade.log  
  3. SQL> @catdwgrd.sql (10.2.10 运行的是这个,而10.1 降级用的是 d92000.sql,即dold_release.sql)  
  4. Sql>spool off  
  5. Sql>shutdown immediate  

(13).检查升级后的情况

  1. SQL> select comp_name,version,status from sys.dba_registry;  
  2. SQL> select * from utl_recomp_errors;  

oracle asm存储

1.ASM概念:[需要关闭selinux]
ASM:Automatic Storage Management, 是Oracle 主推的一种面向Oracle的存储解决方案

2.使用ASM的好处:
(1).将I/O平均分部到所有可用磁盘驱动器上以防止产生热点,并且最大化性能。
(2).配置更简单,并且最大化推动数据库合并的存储资源利用。
(3).内在的支持大文件
(4).在增量增加或删除存储容量后执行自动联系重分配
(5).维护数据的冗余副本以提高可用性。
(6).支持10g,11g的数据存储及RAC的共享存储管理
(7).支持第三方的多路径软件
(8).使用OMF方式来管理文件

3.三种不同的冗余方式:
(1).外部冗余(external redundancy):
表示Oracle不帮你管理镜像,功能由外部存储系统实现,比如通过RAID技术;有效磁盘空间是所有磁盘设备空间的大小之和。
(2).默认冗余(normal redundancy):
表示Oracle提供2份镜像来保护数据,有效磁盘空间是所有磁盘设备大小之和的1/2(使用最多)
(3).高度冗余(high redundancy):
表示Oracle提供3份镜像来保护数据,以提高性能和数据的安全,最少需要三块磁盘(3个failure group);有效磁盘空间是所有磁盘设备大小之和的1/3,虽然冗余级别高了,但是硬件的代价也最高。

4.ASM 扩展性
(1).最多支持63个磁盘组;
(2).最多支持10000个磁盘;
(3).最大支持4pb/磁盘;
(4).最大支持40 exabyte/ASM存储;
(5).最大支持1百W个文件/磁盘组;
(6).外部冗余时单个文件最大35tb,标准冗余时单个文件最大5.8tb,高冗余度时单个文件最大3.9tb。

5.ASM搭建:

(1).检查系统环境:

  1. [root@xcg ~]# uname -a     # 下面的asm包必须跟此系统版本内核一致  

(2).必须安装的软件包如下:[和装数据库的软件包一致,可配置yum源来安装]

  1. binutils-2.15.92.0.2-13.EL4  
  2. compat-db-4.1.25-9  
  3. compat-libstdc++-296-2.96-132.7.2  
  4. control-center-2.8.0-12  
  5. gcc-3.4.3-22.1.EL4  
  6. gcc-c++-3.4.3-22.1.EL44  
  7. glibc-2.3.4-2.9  
  8. glibc-common-2.3.4-2.9  
  9. gnome-libs-1.4.1.2.90-44.1  
  10. libstdc++-3.4.3-22.1  
  11. libstdc++-devel-3.4.3-22.1  
  12. make-3.80-5  
  13. pdksh-5.2.14-30  
  14. sysstat-5.0.5-1  
  15. xscreensaver-4.18-5.rhel4.2  
  16. setarch-1.6-1  
  17. libaio-0.3.103-3  
  18. libXp  

(3).创建 Oracle 组和用户帐户

  1. a.以 root 用户身份执行以下命令:  
  2. groupadd oinstall  
  3. groupadd dba  
  4. useradd -m -g oinstall -G dba -d /u01/app/oracle oracle  
  5.   
  6. b.设置 oracle 帐户的口令:  
  7. passwd oracle # 密码就设置为oracle  

(4).创建目录,修改系统环境

  1. a.以 root 用户身份执行以下命令:  
  2. mkdir -p /u01/app/oracle  
  3. chown -R oracle:oinstall /u01  
  4. xhost +  
  5.   
  6. b.修改系统版本[针对oracle 10g]  
  7. vim /etc/redhat-release  
  8. 将  
  9. Red Hat Enterprise Linux Server release 5 (Tikanga)  
  10. 改为  
  11. Red Hat Enterprise Linux Server release 4 (Tikanga)  
  12.   
  13. c.修改内核参数和安全参数  
  14. vim /etc/sysctl.conf  
  15. kernel.shmall = 2097152  
  16. kernel.shmmax = ****   # 内存一半  
  17. kernel.shmmni = 4096  
  18. kernel.sem = 250 32000 100 128  
  19. fs.filemax = 65536  
  20. net.ipv4.ip_local_port_range = 1024 65000  
  21. net.core.rmem_default = 262144  
  22. net.core.rmem_max = 262144  
  23. net.core.wmem_default = 262144  
  24. net.core.wmem_max = 262144  
  25.   
  26. /sbin/sysctl -p # 使其生效  
  27.   
  28. vim /etc/security/limits.conf  
  29. oracle soft nproc 2047  
  30. oracle hard nproc 16384  
  31. oracle soft nofile 1024  
  32. oracle hard nofile 65536  

(5).安装oracleasm相关包[这里需要根据每个人的系统版本情况,下载安装相应版本的包]

  1. oracleasm-support-2.0.3-1.i386.rpm   # 安装顺序就是目前排序的书序,不要装错  
  2. oracleasm-2.6.9-42.EL-2.0.3-1.i686.rpm  
  3. oracleasmlib-2.0.2-1.i386.rpm  

(6).配置ASM

  1. #/etc/init.d/oracleasm configure  
  2. Default user to own the driver interface [oracle]: oracle  
  3. Default group to own the driver interface [oinstall]: oinstall  
  4. Start Oracle ASM library driver on boot (y/n) [y]: y  
  5. Fix permissions of Oracle ASM disks on boot (y/n) [y]: y  
  6. Writing Oracle ASM library driver configuration: [ OK ]  
  7. Loading module “oracleasm”: [ OK ]  
  8. Mounting ASMlib driver filesystem: [ OK ]  
  9. Scanning system for ASM disks: [ OK ]  

(7).创建ASM磁盘[前提是已划分好物理磁盘:没有的话,执行fdisk /dev/sda,划分磁盘]

  1. [root@dbrac bin]# service oracleasm createdisk data1 /dev/sda5  
  2. Marking disk “/dev/sda5” as an ASM disk: [ OK ]  
  3. [root@dbrac bin]# service oracleasm createdisk data2 /dev/sda6  
  4. Marking disk “/dev/sda6” as an ASM disk: [ OK ]  
  5. [root@dbrac bin]# service oracleasm createdisk data3 /dev/sda7  
  6. Marking disk “/dev/sda7” as an ASM disk: [ OK ]  
  7. [root@dbrac bin]# service oracleasm createdisk data4 /dev/sda8  
  8. Marking disk “/dev/sda8” as an ASM disk: [ OK ]  
  9. 注:如果创建错了,删除  
  10. [root@dbrac bin]# service oracleasm deletedisk data4  

(8).查看磁盘,并注意磁盘的权限为oracle.oinstall:

  1. [root@dbrac bin]# /etc/init.d/oracleasm listdisks  

(9).ASM 实例需要CSS 进程,如果是非RAC 环境,在启动ASM 实例之前会提示用脚本:$ORACLE_HOME/bin/localconfig add 启动CSS。

  1. [root@dbrac bin]# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add # 启动后,会出现ocssd、cssd进程  
  2. 注:删除的方法如下:  
  3. [root@dbrac bin]# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig delete  

(10).使用DBCA创建ASM实例:
在这里,出现的图形化选项,选择最后一个选项执行,然后,根据图形化,选择创建磁盘组

(11).ASM 启动,关闭:[启动时先起ASM,停库时先停数据库]

  1. [oracle@node2 dbs]$ export ORACLE_SID=+ASM  
  2. [oracle@node2 dbs]$ sqlplus / as sysdba  
  3. SQL> startup # 在此命令行下,可输入asmcmd ,可执行系统命令,具体查看帮助  
  4. SQL> shutdown immediate;  

(12).使用ASM磁盘:

  1. sys>create tablespace new_tbs datafile ‘+dg1’ size 10m;  

(13).将文件迁移到asm上[migreat to asm(use rman)]

  1. a).control file:  
  2. alter system set control_files=’+dg1′,’+dg1′,’+dg1′ scope=spfile;  
  3. shutdown immediate;  
  4. rman>startup nomount;  
  5. rman>restore controlfile from ‘/u01/app/oracle/oradata/orcl/control01.ctl’;  
  6.   
  7. b) .data file:  
  8. rman>shutdown immediate  
  9. rman>startup mount  
  10. rman>backup as copy database format ‘+dg1’;  
  11. rman>switch database to copy;  
  12. rman>alter database open;  
  13.   
  14. c).temporary file  
  15. sys>alter database rename file ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ to ‘+dg1’;  
  16.   
  17. d).redo log file:  
  18. sys>alter database drop logfile group 3;  
  19. sys>alter database add logfile group 3 ‘+dg1’;  

(14).手动添加磁盘到磁盘组[add disk to dsikgroup]:

  1. asm>alter diskgroup dg1 add disk ‘ORCL:DATA4’; # 这里尽量写磁盘路径  

(15).从磁盘组里删除某个磁盘[delete disk from diskgroup]:

  1. asm>alter diskgroup dg1 drop disk ‘DATA4’; # 这里写磁盘名  

(16).创建磁盘组[create diskgroup]:

  1. asm>create diskgroup dg2 external redundancy disk ‘ORCL:DATA5′,’ORCL:DATA6’;  
  2. 或  
  3. asm>CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY  
  4. FAILGROUP controller1 DISK  
  5. ‘ORCL:DATA1’,  
  6. ‘ORCL:DATA2’,  
  7. ‘ORCL:DATA3’,  
  8. ‘ORCL:DATA4’  
  9. FAILGROUP controller2 DISK  
  10. ‘ORCL:DATA5’,  
  11. ‘ORCL:DATA6’,  
  12. ‘ORCL:DATA7’,  
  13. ‘ORCL:DATA8’;  

(17).删除磁盘组[drop diskgroup]:

  1. asm>drop diskgroup dg2;  

(18).磁盘组挂载[Mounting Disk Groups]

  1. asm>ALTER DISKGROUP dgroup1 MOUNT;  
  2. 磁盘组卸载Dismounting Disk Groups  
  3. asm>ALTER DISKGROUP ALL DISMOUNT;  

(19).Manually Rebalancing a Disk Group:

  1. asm>alter diskgroup dg1 rebalance power 11;(rebalance) # 手动平衡磁盘组  
  2. asm>alter system set asm_power_limit=11;  

6.视图:
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_DISK
V$ASM_DISK_STAT
V$ASM_OPERATION

oracle flashback使用

1.flashback 的描述:[ 仅能解决用户错误 ]

Flashback 技术是以Undo segment中的内容为基础的,因此受限于UNDO_RETENTON参数。要使用flashback 的特性,必须启用自动撤销管理表空间。

在Oracle 11g里又出了一个新特性:Oracle Flashback Data Archive. FDA通过将变化数据另外存储到创建的闪回归档区(Flashback Archive)中,以和undo区别开来,这样就可以为闪回归档区单独设置存储策略,使之可以闪回到指定时间之前的旧数据而不影响undo策略。

在Oracle 10g中, Flashback分为: Flashback Database Flashback Drop,和 Flashback Table

2.设置闪回恢复区(Flash Recovery Area):

(1).主要通过3个初始化参数来设置和管理:
db_recovery_file_dest:指定闪回恢复区的位置
db_recovery_file_dest_size:指定闪回恢复区的可用空间大小
db_flashback_retention_target:指定数据库可以回退的时间,单位为分钟,默认1440分钟(1天)。当然,实际上可回退的时间还决定于闪回恢复区的大小,因为里面保存了回退所需要的flash log。
所以这个参数要和db_recovery_file_dest_size配合修改。

(2).启用闪回恢复区

  1. SQL> ALTER SYSTEM SET db_recovery_file_dest_size=3g SCOPE=BOTH   # 设置闪回恢复区的大小  
  2. SQL> ALTER SYSTEM SET db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’ SCOPE=BOTH   # 设置闪回恢复区的位置  
  3. SQL> show parameter db_recovery_file_dest   # 查看设置情况  
  4. SQL> show parameter db_flashback   # 数据库可以回退的时间  
  5. 注:如果需要修改,使用如下命令:[此参数需与闪回恢复区的有足够的空间存放日志]  
  6. SQL>alter system set db_flashback_retention_target=2880 scope=both;   # 这是设置为两天  

(3).闪回恢复区的取消:[不使用的话,可以取消闪回恢复区]
取消闪回恢复区,只要将存放位置置空即可[前提:已关闭闪回功能]

  1. SQL> shutdown immediate # 因为需要关闭flashback,所以需要关闭数据库,启动到mount状态  
  2. SQL> startup mount  
  3. SQL> alter database flashback off # 关闭闪回功能,此功能关闭后,闪回区域日志自动删除  
  4. SQL> alter database open  
  5. SQL> alter system set db_recovery_file_dest=” # 取消闪回恢复区  

注:

a.DB_RECOVERY_FILE_DEST_SIZE 只有在 DB_RECOVERY_FILE_DEST 清空之后才可以清空

b.如果设置了闪回恢复区,则log_archive_dest和log_archive_duplex_dest将不可用

c.设置闪回恢复区后,如果没有设置过log_archive_dest_n参数,则归档日志默认是保存到该区域的。
实际上,oracle是通过隐式的设置log_archive_dest_10=’location=USE_DB_RECOVERY_FILE_DEST’来实现的。
所以,如果修改过log_archive_dest_n将归档日志保存到其他位置,也可以修改该参数继续使用闪回恢复区。
多个数据库的闪回恢复区可以指定到同一个位置,但是db_name不能一样,或者db_unique_name不一样。
RAC的闪回恢复区必须位于共享磁盘上,能被所有实例访问

d.闪回恢复区的管理:
dba_outstanding_alerts
v$flash_recovery_area_usage
v$flashback_database_log
v$flashback_database_logfile

在闪回恢复区中的空间使用超过 85% 的时候,数据库将会向 alert 文件中写入告警信息。
而当超过 97% 的时候将会写入严重告警信息。当闪回恢复区空间不够的时候,Oracle将报错

3.启用数据库的flashback功能:[数据库必须处于归档模式下]

  1. SQL> archive log list # 查看是否已经是归档模式,没有的话,修改  
  2. SQL> startup mount # 启动数据库到mount状态,open状态也可打开  
  3. SQL> select name,current_scn,flashback_on from v$database; # 检查有没有开启Flashback功能  
  4. SQL> alter database flashback on # 启动Flashback功能  
  5. SQL> select name,current_scn,flashback_on from v$database; # 确认启动  
  6. [oracle@dba ~]$ ps -ef|grep rvw # 查看进程是否启动  

4.闪回表[flashback table] ==> dml语句,闪回时,需打开行移动

  1. flashback table 名称 to scn scn号|timestamp to_timestamp(时间转换)  
  2. 范例:  
  3. scott>alter table emp enable row movement; # 打开行移动  
  4. scott>flashback table emp to timestamp to_timestamp(‘2008-07-29 14:39:57′,’yyyy-mm-dd hh24:mi:ss’);  
  5. scott>alter table emp disable row movement; # 关闭行移动  
  6. 注:show parameter undo ==> 900s 时间不一定能保证:  
  7. 如需保证的话,  
  8. alter tablespace undo表空间名 retention guarantee  

5.闪回drop[flashback drop]

(1).概述:
从Oracle 10g开始出现的,用于恢复用户误删的对象(表,索引)。这个技术依赖于Tablespace Recycle Bin(表空间回收).

  1. SQL> show parameter recyclebin # 闪回drop依赖与此功能,此功能必须打开,默认是开启的  

(2).Flashback Drop 操作[system表是无法恢复的]

  1. flashback table 表名 to before drop [rename to new_table];  

(3).修改recyclebin的方法:

  1. SQL> alter system set recyclebin=off|on;  

(4).清空垃圾:

  1. purge table 表名  
  2. purge index 索引名  
  3. purge tablespace 表空间名  
  4. purge user_recyclebin # 清楚用户垃圾  
  5. purge dba_recyclebin # 清楚所有  

6.flashback database 描述:
Flashback Database 整个架构包括一个进程Recover Writer(RVWR)后台进程,Flashback Database Log日志和Flash Recovery Area
一旦数据库启用了Flash Database, RVWR进程会启动,RVWR进程会向Flash Recovery Area内写入Flashback Database Log, 这些日志包括数据块的”前镜像”。
Flashback Database 功能非常类似与RMAN的不完全恢复,它可以把整个数据库回退到过去的某个时点的状态,这个功能依赖于Flashback log日志。
比RMAN更快速和高效。 因此Flashback Database 可以看作是不完全恢复的替代技术.但它也有某些限制:

(1). Flashback Database 不能解决Media Failure, 这种错误RMAN恢复仍是唯一选择

(2). 如果删除了数据文件或者利用Shrink技术缩小数据文件大小,这时不能用Flashback Database技术回退到改变之前的状态

(3). 如果控制文件是从备份中恢复出来的,或者是重建的控制文件,也不能使用Flashback Database。

(4). 使用Flashback Database锁能恢复到的最早的SCN, 取决与Flashback Log中记录的最早SCN。

7.flashback database[在表被彻底删除,也就是加了purge]

  1. 格式:flashback database|standby to scn scn#|timestamp expr  

(1).确认能够恢复的时间点

  1. SQL> desc v$flashback_database_log;  
  2. 注:OLDEST_FLASHBACK_SCN: 数据库能回退的最早SCN  
  3. OLDEST_FLASHBACK_TIME: 数据库能回退的最早时间  
  4. RETENTION_TARGET:当前系统设置参数  
  5. FLASHBACK_SIZE:当前的Flashback Log空间  
  6. ESTIMATED_FLASHBACK_SIZE: 为了达到Retention_target定义的要求,预计需要多大的空间,这个值用于指导设置Flash Recovery Area大小  

(2).数据库启动到mount状态:

  1. SQL> flashback database to timestamp to_timestamp(‘2013-07-02 17:10:04′,’yyyy-mm-dd hh24:mi:ss’);  
  2. 或 flashback database to scn 710733;  
  3. SQL> alter database open read only; # 以只读形式打开,如果不正确,可以关闭数据库,重新闪回  
  4. SQL> shutdown immediate;  
  5. SQL> startup mount;  
  6. SQL> alter database open resetlogs; # 如果是正确的,则在mount状态,以resetlogs方式打开数据库  

oracle database link

1.database link理解:
db link是定义一个数据库到另一个数据库的路径的对象,db link允许你查询远程表及执行远程程序,注意的是db link是单向的连接

2.建立db link之前需要确认的事项:
确认从local database到remote database的网络连接是正常的。[tnsping要能成功]
确认在remote database上面有相应的访问权限

3.db link的分类:
私有:仅创建者自己可以使用
公有:本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此db link来访问相应的远程数据库。
全局:网络级的

4.创建db link需要的权限

  1. CREATE DATABASE LINK     #    [创建私有db link需要此权限]  
  2. CREATE PUBLIC DATABASE LINK    #   [创建公有db link需要此权限]  
  3. CREATE SESSION    #  必须具有权限  

5.创建格式:

  1. CREATE [SHARED][PUBLIC] database link link_name  
  2. [CONNECT TO [user][current_user] IDENTIFIED BY password]  
  3. [AUTHENTICATED BY user IDENTIFIED BY password]  
  4. [USING ‘connect_string’]  

6.创建私有db link文件范例:

  1. create database link link的名字 connect to 登录远程数据库的用户名 identified by 密码 using ‘实例名SID’;  
  2. ex:  
  3. create database link link_xcg connect to scott identified by tiger using ‘orcl’;  

7.使用db link文件:

(1).本地登录自己的数据库,执行如下语句:[emp表,sys用户下没有]

  1. SYS> select * from emp@link_xcg;  
  2. 注:# 可以操作DML语句,但是,不可操作DDL语句  

(2).使用同义词,使操作更方便:

  1. SYS> create synonym emp_link for emp@link_xcg;  
  2. SYS> select * from emp; # 查询时,直接输入同义词即可查询  

(3).删除db link文件

  1. SYS> drop database link link的名字  

(4).查询用户或dba的db link信息:[ DBA_DB_LINKS / USER_DB_LINKS ] ==> 两个视图可查询具体信息

  1. SYS> select * from dba_db_links;  

oracle 共享服务进程(shared server)

1.独占服务进程:
每一个session单独分配一个server process,直到用户断开连接,才释放该进程所占用的资源。

2.共享服务进程:[ oracle的网络服务正常,监听正常 ]
多个用户连接,通过调度进程共享服务进程

3.配置共享服务进程需要的配置参数:

  1. SYS> show parameter shared;  
  2. NAME                        TYPE       VALUE  
  3. ————————- ———– ————-  
  4. # 指定启动实例时可创建的共享服务器进程数[必设置参数]  
  5. shared_servers            integer        2   
  6. # 指定可以同时运行的共享服务器进程最大数目  
  7. max_shared_servers        integer                
  8. shared_memory_address     integer        0  
  9. shared_pool_reserved_size big integer    9646899  
  10. shared_pool_size          big integer    0  
  11. # 用户会话允许的共享服务进程总数,可以为专门服务进程保留用户会话  
  12. shared_server_sessions    integer       
  13. hi_shared_memory_address  integer        0  
  14. SYS> show parameter dispatch;  
  15. NAME                     TYPE    VALUE  
  16. ——————– ———– ————-  
  17. dispatchers              string  (PROTOCOL=TCP) (SERVICE=orclXDB) # 配置调度程序进程 [必设置参数]  
  18. max_dispatchers          integer               # 指定同时运行的调度程序进程最大数目  

4.监视性能的视图
V$DISPATCHER 提供有关调度程序进程的信息。包括名称、网址,状态,各种使用统计表和索引号码。
V$QUEUE 包含有关共享服务器消息队列的信息。
V$SHARED_SERVER 包含有关共享服务器进程的信息。
V$CIRCUIT 包含有关虚拟回路的信息。也可以说查看调度信息

5.共享服务器响应用户请求的步骤如下:
1).客户传送一个请求到调度程序。
2).调度程序将请求放在SGA中的请求队列中。
3).其中的一个共享服务器进程响应并处理这个请求。
4).共享服务器进程把处理完的请求回复放到SGA中的响应队列中。
5).调度器从响应队列中取出已经完成的请求。
6).调度器把完成的请求回复给客户

6.具体配置

(1).配置共享服务进程

  1. SYS> alter system set shared_servers=2; # 设置共享服务进程为2个  
  2. System altered.  

(2).配置调度进程

  1. SYS> alter system set dispatchers='(PROTOCOL=TCP)(dispatchers=2)’;      #  设置调度进程2个  
  2. System altered.  

(3).调度进程用

  1. ps -ef | grep ora  
  2. 或者  
  3. SYS> select * from v$dispatcher;  

(4).如果有客户端登录,可以查看是哪个调度进程调度的

  1. SYS> select * from v$circuit;  
  2. 注:配置了共享服务后,调度信息可在服务端,lsnrctl > services 里面看到相关调度进程信息[lsnrctl:此命令为监听程序控制命令]  
  3. # 未指定是否使用共享还是专用模式,优先使用共享服务进程模式  
  4. # 关闭某个调度进程:  
  5. ALTER SYSTEM SHUTDOWN IMMEDIATE  ‘调度进程的名字’;    ==> 可以在V$DISPATCHER 视图中查到