ORACLE DBA笔试题
一:SQL tuning 类

　　1:列举几种表连接方式（left join ,union）

　　2:不借助第三方工具，怎样查看sql的执行计划 explan

　　3:如何使用CBO,CBO与RULE的区别

　　4:如何定位重要(消耗资源多)的SQL
      select sql from v$sql where 查詢最多次數

　　5:如何跟踪某个session的SQL
     alter session trace=true
     執行sql
     alter session trace=false

　　6:SQL调整最关注的是什么
索引，執行效率

　　7:说说你对索引的认识(索引的结构、对dml影响、对查询影响、为什么提高查询性能)
快速定位

　　8:使用索引查询一定能提高查询的性能吗?为什么
不一定，當一個表資料不大效果不是很明顯

　　9:绑定变量是什么?绑定变量有什么优缺点?
通過傳值的方式，傳sql語句重，達到sql共享

　　10:如何稳定(固定)执行计划

rule指定
　　11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么
用於PGA排序

　　12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
select rownmun,a.* from (select a,b,c,d from t order by c ) a where rownum>21 and rownum <30

　　二:数据库基本概念类

　　1:pctused and pctfree 表示什么含义有什么作用
表是空間使用比例和空閒比例
　　2:简单描述table / segment / extent / block之间的关系

　　3:描述tablespace和datafile之间的关系
      tablespace 是邏輯文件 datafile 是物理文件，一個表空間可以存放多個數據文件中，一個數據文件只可以歸屬一個表空間。
　　4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点
      本地管理表空间（Locally Managed Tablespace简称LMT）
      8i以后出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用。
      字典管理表空间（Dictionary-Managed Tablespace简称DMT）
      8i以前包括以后都还可以使用的一种表空间管理模式，通过数据字典管理表空间的空间使用。
      动段空间管理（ASSM），
      它首次出现在Oracle920里有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，
       能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，
       ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。

　　5:回滚段的作用是什么
       事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，
          当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原        来的值。
　
       事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，
           ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

     　读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。
           当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）
      当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）
      来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，
      若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。

　　6:日志的作用是什么
记录数据库事务,最大限度地保证数据的一致性与安全性

重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复,一个数据库至少需要两个重做日志文件
归档日志文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

　　7:SGA主要有那些部分，主要作用是什么
     SGA：db_cache/shared_pool/large_pool/java_pool
db_cache:
数据库缓存（Block Buffer）对于Oracle数据库的运转和性能起着非常关键的作用，
   它占据Oracle数据库SGA（系统共享内存区）的主要部分。Oracle数据库通过使用LRU
   算法，将最近访问的数据块存放到缓存中，从而优化对磁盘数据的访问.
shared_pool:
共享池的大小对于Oracle 性能来说都是很重要的。
共享池中保存数据字典高速缓冲和完全解析或编译的的PL/SQL 块和SQL 语句及控制结构
large_pool:
使用MTS配置时，因为要在SGA中分配UGA来保持用户的会话，就是用Large_pool来保持这个会话内存
   使用RMAN做备份的时候，要使用Large_pool这个内存结构来做磁盘I/O缓存器
java_pool:
为java procedure预备的内存区域,如果没有使用java proc,java_pool不是必须的

　　8:oracle系统进程主要有哪些，作用是什么
数据写进程(dbwr)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件
日志写进程(lgwr)：将重做日志缓冲区中的更改写入在线重做日志文件
系统监控(smon) ：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复
进程监控(pmon) ：负责在一个Oracle 进程失败时清理资源
检查点进程(chpt)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。
归档进程(arcn) ：在每次日志切换时把已满的日志组进行备份或归档
作业调度器(cjq) :负责将调度与执行系统中已定义好的job,完成一些预定义的工作.
恢复进程(reco) :保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;

　　三:备份恢复类

　　1:备份如何分类
      导出/导入(EXPORT/IMPORT) 、冷备份(數據庫關閉的情況下)、热备份(archivelog 備份)
　　2:归档是什么含义
     oracle使用循环日志方式 ,当日志文件被循环使用的时候,日志内容就被覆盖了。当打开日志归档时,可以将日志内容被覆盖前存储到归档文件中。
     关于归档日志：Oracle要将填满的在线日志文件组归档时,则要建立归档日志（archived redo log）。
其对数据库备份和恢复有下列用处：
    <1>数据库后备以及在线和归档日志文件，在操作系统和磁盘故障中可保证全部提交的事物可被恢复。
    <2>在数据库打开和正常系统使用下，如果归档日志是永久保存，在线后备可以进行和使用。
数据库可运行在两种不同方式下：
   NOARCHIVELOG方式或ARCHIVELOG 方式
数据库在NOARCHIVELOG方式下使用时，不能进行在线日志的归档,
如果数据库在ARCHIVELOG方式下运行，可实施在线日志的归档。

　　3:如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复
     手工拷贝回所有备份的数据文件
sql>startup mount;
sql>alter database recover automatic until time '2004-08-04:10:30:00';
sql>alter database open resetlogs

　　4:rman是什么，有何特点
      RMAN(Recovery Manager)是DBA的一个重要工具，用于备份、还原和恢复oracle数据库,
RMAN 可以用来备份和恢复数据库文件、归档日志、控制文件、系统参数文件,也可以用来执行完全或不完全的数据库恢复。
RMAN有三种不同的用户接口：
COMMAND LINE方式、GUI 方式（集成在OEM 中的备份管理器）、API 方式（用于集成到第三方的备份软件中）。
具有如下特点：
1）功能类似物理备份，但比物理备份强大N倍；
2）可以压缩空块；
3）可以在块水平上实现增量；
4）可以把备份的输出打包成备份集，也可以按固定大小分割备份集；
5）备份与恢复的过程可以自动管理；
6）可以使用脚本（存在Recovery catalog 中）
7）可以做坏块监测

　　5:standby的特点
     备用数据库（standby database）：ORACLE推出的一种高可用性(HIGH AVAILABLE)数据库方案，
在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份
可以实现快速切换与灾难性恢复,从920开始，还开始支持物理与逻辑备用服务器。
9i中的三种数据保护模式分别是：
1)、MAXIMIZE PROTECTION ：最大数据保护与无数据分歧，LGWR将同时传送到备用节点，
    在主节点事务确认之前，备用节点也必须完全收到日志数据。如果网络不好，引起LGWR不能传送数据，将引起严重的性能问题，导致主节点DOWN机。
2)、MAXIMIZE AVAILABILITY ：无数据丢失模式，允许数据分歧，允许异步传送。
    正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式，
   主节点的操作还是可以继续的。在网络不好的情况下有较大的性能影响。
3)、MAXIMIZE PERFORMANCE：这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送，
    无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE
　　6:对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略
    rman/每月一号 level 0 每周末/周三 level 1 其它每天level 2

　　四:系统管理类

　　1:对于一个存在系统性能的系统，说出你的诊断处理思路
1 做statspack收集系统相关信息
了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等
2 查v$system_event/v$session_event/v$session_wait
从v$system_event开始,确定需要什么资源(db file sequential read)等
深入研究v$session_event,确定等待事件涉及的会话
从v$session_wait确定详细的资源争用情况(p1-p3的值:file_id/block_id/blocks等)
3 通过v$sql/v$sqltext/v$sqlarea表确定disk_reads、(buffer_gets/executions)值较大的SQL

　　2:列举几种诊断IO、CPU、性能状况的方法
top/vmstat
statspack
sql_trace/tkprof
查v$system_event/v$session_event/v$session_wait
查v$sqlarea(disk_reads或buffer_gets/executions较大的SQL)

　　3:对statspack有何认识
StapSpack是Oracle公司提供的一个收集数据库运行性能指标的软件包，该软件包从8i起，在9i、10g都有显著的增强
该软件包的辅助表（存储相关参数与收集的性能指标的表）由最初的25个增长到43个
收集级别参数由原来的3个（0、5、10）增加到5个（0、5、6、7、10）
通过分析收集的性能指标，数据库管理员可以详细地了解数据库目前的运行情况，对数据库实例、等待事件、SQL等进行优化调整
利用statspack收集的snapshot,可以统计制作数据库的各种性能指标的统计趋势图表。

　　4:如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
     在系统比较空闲时
   nologging选项（如果有dataguard则不可以使用nologging）
   大的sort_ared_size或pga_aggregate_target较大

　　5:对raid10 和raid5有何认识
前者是先做磁碟的鏡像再疊加，讀寫性能比較好、
後者的通過奇偶校驗方式存籌數據，寫的性能比較差，可以壞一塊硬碟。
　　五:综合随意类

　　1:你最擅长的是oracle哪部分?
體系結構，

　　2:喜欢oracle吗?喜欢上论坛吗?或者偏好oracle的哪一部分?
喜欢,sql的优化

　　3:随意说说你觉得oracle最有意思的部分或者最困难的部分

　　4:为何要选择做DBA呢?

兴趣所在

一：SQL tuning 类
1：列举几种表连接方式答：merge join，hash join，nested loop

2：不借助第三方工具，怎样查看sql的执行计划？
答：sqlplus
set autotrace …
utlxplan.sql 创建 plan_table 表

3：如何使用CBO，CBO与RULE的区别？答：在初始化参数里面设置 optimizer_mode=choose/all_rows/first_row 等可以使用 cbo。
rbo会选择不合适的索引，cbo需要统计信息。

4：如何定位重要（消耗资源多）的SQL？答：根据v$sqlarea 中的逻辑读/disk_read。以及寻找CPU使用过量的session，查出当前session的当前SQL语句，或者：监控WIN平台Oracle的运行

5：如何跟踪某个session的SQL？答：先找出对应的’sid,serial’，然后调用system_system.set_sql_trace_in_session(sid,serial,true)；参考：跟踪某个会话

6：SQL调整最关注的是什么？答：逻辑读。IO量

7：说说你对索引的认识（索引的结构、对dml影响、对查询影响、为什么提高查询性能）答：默认的索引是b-tree。
对insert的影响：分裂，要保证tree的平衡。
对delete的影响：删除行的时候要标记改节点为删除。
对update的影响：如果更新表中的索引字段，则要相应的更新索引中的键值。查询中包含索引字段的键值和行的物理地址。

8：使用索引查询一定能提高查询的性能吗？为什么？答：不能。如果返回的行数目较大，使用全表扫描的性能较好。

9：绑定变量是什么？绑定变量有什么优缺点？答：通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少只有变量值不同的语句的解析。

10：如何稳定(固定)执行计划？答：使用stored outline。

11：和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么？答：8i：使用sort_area_size，hash_area_size，每个session分配相同的值，不管有无使用。
9i：使用pga_aggregate来统一管理。临时表空间的作用：
在sort_area_size中不能完成的部分在临时表空间完成，临时表空间在重建索引，创建临时表等都要用到。
还有hash join不能完成的也在临时表空间中做。

12：存在表T(a,b,c,d)，要根据字段c排序后取第21—30条记录显示，请给出sql？ select a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;
或者：
select * from (select rownum rn,a.* from (select a,b,c,d from T order by c) a )where rn between 21 and 30;

三：备份恢复类

1 备份如何分类

逻辑备份：exp/imp 指定表的逻辑备份

物理备份：

热备份:alter tablespace begin/end backup;

冷备份:脱机备份(database shutdown)

RMAN备份

full backup/incremental backup(累积/差异)

物理备份

物理备份是最主要的备份方式。用于保证数据库在最小的数据库丢失或没有数据丢失的情况下得到恢复。

冷物理

冷物理备份提供了最简单和最直接的方法保护数据库因物理损坏丢失。建议在以下几种情况中使用。

对一个已经存在大最数据量的数据库，在晚间数据库可以关闭，此时应用冷物理备份。

对需对数据库服务器进行升级，（如更换硬盘），此时需要备份数据库信息，并在新的硬盘中恢复这些数据信息，建议采用冷物理备份。

热物理

主要是指备份过程在数据库打开并且用户可以使用的情况下进行。需要执行热物理备份的情况有：

由于数据库性质要求不间断工作，因而此时只能采用热物理备份。

由于备份的要求的时间过长，而数据库只能短时间关闭时。

逻辑备份 (EXP/IMP)

逻辑备份用于实现数据库对象的恢复。但不是基于时间点可完全恢复的备份策略。只能作为联机备份和脱机备份的一种补充。

完全逻辑备份

完全逻辑备份是将整个数据库导出到一个数据库的格式文件中，该文件可以在不同的数据库版本、操作系统和硬件平台之间进行移植。

指定表的逻辑备份

通过备份工具，可以将指定的数据库表备份出来，这可以避免完全逻辑备份所带来的时间和财力上的浪费。

2 归档是什么含义

关于归档日志：Oracle要将填满的在线日志文件组归档时,则要建立归档日志（archived redo log）。其对数据库备份和恢复有下列用处：

数据库后备以及在线和归档日志文件，在操作系统和磁盘故障中可保证全部提交的事物可被恢复。

在数据库打开和正常系统使用下，如果归档日志是永久保存，在线后备可以进行和使用。

数据库可运行在两种不同方式下：NOARCHIVELOG方式或ARCHIVELOG 方式

数据库在NOARCHIVELOG方式下使用时，不能进行在线日志的归档,

数据库在ARCHIVELOG方式下运行，可实施在线日志的归档

归档是归档当前的联机redo日志文件。

SVRMGR> alter system archive log current;

数据库只有运行在ARCHIVELOG模式下，并且能够进行自动归档，才可以进行联机备份。有了联机备份才有可能进行完全恢复。

3 如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复

9i 新增的FLASH BACK 应该可以;

Logminer应该可以找出DML。

有完善的归档和备份，先归档当前数据，然后可以先恢复到删除的时间点之前，把DROP 的表导出来，然后再恢复到最后归档时间；

手工拷贝回所有备份的数据文件

Sql〉startup mount;

sql〉alter database recover automatic until time '2004-08-04:10:30:00';

sql〉alter database open resetlogs;

4 rman是什么，有何特点

RMAN(Recovery Manager)是DBA的一个重要工具，用于备份、还原和恢复oracle数据库, RMAN 可以用来备份和恢复数据库文件、归档日志、控制文件、系统参数文件,也可以用来执行完全或不完全的数据库恢复。

RMAN有三种不同的用户接口：COMMAND LINE方式、GUI 方式（集成在OEM 中的备份管理器）、API 方式（用于集成到第三方的备份软件中）。

具有如下特点：

1）功能类似物理备份，但比物理备份强大N倍；

2）可以压缩空块；

3）可以在块水平上实现增量；

4）可以把备份的输出打包成备份集，也可以按固定大小分割备份集；

5）备份与恢复的过程可以自动管理；

6）可以使用脚本（存在Recovery catalog 中）

7）可以做坏块监测

5 standby的特点

备用数据库（standby database）：ORACLE推出的一种高可用性(HIGH AVAILABLE)数据库方案，在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份，可以实现快速切换与灾难性恢复,从920开始，还开始支持物理与逻辑备用服务器。

9i中的三种数据保护模式分别是：

1)、MAXIMIZE PROTECTION ：最大数据保护与无数据分歧，LGWR将同时传送到备用节点，在主节点事务确认之前，备用节点也必须完全收到日志数据。如果网络不好，引起LGWR不能传送数据，将引起严重的性能问题，导致主节点DOWN机。

2)、MAXIMIZE AVAILABILITY ：无数据丢失模式，允许数据分歧，允许异步传送。

正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式，主节点的操作还是可以继续的。在网络不好的情况下有较大的性能影响。

3)、MAXIMIZE PERFORMANCE：这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送，无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE

6 对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略

数据库比较大逻辑备份没什么必要，每天归档5G，每周三/周六自动归档10G，每月RMAN归档全库。应该有standby。

rman/每月一号 level 0 每周末/周三 level 1 其它每天level 2

ITPub博客

一些小試題

最新文章