Heim  >  Artikel  >  Datenbank  >  Oracle DBA面对全新工作环境应该了解什么

Oracle DBA面对全新工作环境应该了解什么

WBOY
WBOYOriginal
2016-06-07 15:31:191141Durchsuche

在这里 , 不谈那些大的方面 , 比如了解整个 IT 环境整体情况 , 假设你已经知道了这些 , 接下来需要面对的就是这些一个个活生生的 database 了 . 这里总结了一些一般的思路来面对一个全先的 database, 从而快速了解你面对的环境概要。 这也不是教科书 , 只是

在这里,不谈那些大的方面,比如了解整个IT环境整体情况,假设你已经知道了这些,接下来需要面对的就是这些一个个活生生的database.这里总结了一些一般的思路来面对一个全先的database,从而快速了解你面对的环境概要。

这也不是教科书,只是一些心得和体会,对于这种问题,每个人的认识的角度是不一样的。

1、先要了解当前的Oracle 数据库的版本和平台和相关信息
这个很重要,忙乎了半天还知道你的数据库是哪个版本,跑在什么系统上,那岂不是很悲哀,所以我个人认为这是第一步需要了解的。下面的这些脚本可以帮助你获取你需要的相关信息。

<span>  <span><span>SELECT</span></span> <span>*</span> <span><span>FROM</span></span> v$version;</span> 
<span>  <span><span>SELECT</span></span> <span>*</span> <span><span>FROM</span></span> dba_registry_database;</span> 
<span>  <span><span>SELECT</span></span> dbid<span>,</span> name<span>,</span> open_mode<span>,</span> database_role<span>,</span> platform_name <span><span>FROM</span></span> v$instance;</span> 
<span>  <span><span>SELECT</span></span> dbms_utility<span>.</span>port_string <span><span>FROM</span></span> dual;</span> 
 
<span>   <span><span>SET</span></span> serveroutput <span><span>ON</span></span></span> 
<span>  declare</span> 
<span>    ver VARCHAR2<span>(</span><span>100</span><span>)</span>;</span> 
<span>    compat VARCHAR2<span>(</span><span>100</span><span>)</span>;</span> 
<span>  begin</span> 
<span>    dbms_utility<span>.</span>db_version<span>(</span>ver<span>,</span> compat<span>)</span>;</span> 
<span>    dbms_output<span>.</span>put_line<span>(</span><span>'Version: '</span> <span>||</span> ver <span>||</span><span>' Compatible: '</span> <span>||</span> compat<span>)</span>;</span> 
<span>  end;</span> 
<span>  <span>/</span></span> 

2、其次要了解你的数据库中装了哪些组件

<span><span>SELECT</span></span><span> <span>*</span> <span><span>FROM</span></span> dba_registry</span> 

3、搞清楚这个环境是单机还是集群?
这个判断方法很多,我这里给出一个借助dbms_utility来判断的方法。

<span>  <span><span>SET</span></span> serveroutput <span><span>ON</span></span></span> 
 
<span>   declare</span> 
<span>    inst_tab dbms_utility<span>.</span>instance_table;</span> 
<span>    inst_cnt NUMBER; </span> 
<span>   begin</span> 
<span>    <span><span>IF</span></span> dbms_utility<span>.</span>is_cluster_database then</span> 
<span>       dbms_utility<span>.</span>active_instances<span>(</span>inst_tab<span>,</span> inst_cnt<span>)</span>;</span> 
<span>       dbms_output<span>.</span>put_line<span>(</span><span>'-'</span> <span>||</span> inst_tab<span>.</span>FIRST<span>)</span>;</span> 
<span>       dbms_output<span>.</span>put_line<span>(</span>TO_CHAR<span>(</span>inst_cnt<span>))</span>;</span> 
<span>    else</span> 
<span>       dbms_output<span>.</span>put_line<span>(</span><span>'Not A Clustered Database'</span><span>)</span>;</span> 
<span>    end <span><span>IF</span></span>;</span> 
<span>  end;</span> 
<span>  <span>/</span></span> 

4、是否配置了DataGuard?

<span>  <span><span>SELECT</span></span> protection_mode<span>,</span> protection_level<span>,</span> remote_archive<span>,</span> </span> 
<span>          database_role<span>,</span> dataguard_broker<span>,</span>guard_status</span> 
<span>  <span><span>FROM</span></span> v$database;</span> 

5、是否起用了归档模式?

<span>  conn <span>/</span> <span><span>AS</span></span> sysdba</span> 
<span>  archive log list;</span> 
<span>  <span><span>SELECT</span></span> log_mode <span><span>FROM</span></span> v$database;</span> 

6、是否起用了flashback database特性?

<span>  <span><span>SELECT</span></span> flashback_on <span><span>FROM</span></span> v$database;</span> 

如果是,再进一步查看FRA的配置情况
7、是否起用了force logging和补充日志?

<span>  <span><span>SELECT</span></span> force_logging<span>,</span>supplemental_log_data_min<span>,</span> supplemental_log_data_pk<span>,</span> </span> 
<span>        supplemental_log_data_ui<span>,</span>supplemental_log_data_fk<span>,</span> supplemental_log_data_all</span> 
<span>  <span><span>FROM</span></span> v$database;</span> 

8、了解控制文件的组成

<span>  <span><span>SELECT</span></span> <span>*</span> <span><span>FROM</span></span> v$controlfile;</span> 

9、了解日志文件的组成

<span>  <span><span>SELECT</span></span> l<span>.</span><span><span>GROUP</span></span><span><span>#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, </span></span></span> 
<span>          lf<span>.</span><span><span>STATUS</span></span> LOGFILE_STATUS</span> 
<span>  <span><span>FROM</span></span> v$log l<span>,</span> v$logfile lf</span> 
<span>  <span><span>WHERE</span></span> l<span>.</span><span><span>GROUP</span></span><span><span># = lf.group#</span></span></span> 
<span>  <span><span>ORDER</span> <span>BY</span></span> <span>1</span><span>,</span><span>3</span>;</span> 

10、了解参数文件的组成和位置

<span>   <span><span>SHOW</span></span> parameter spfile</span> 
<span>   <span><span>CREATE</span></span> spfile <span><span>FROM</span></span> pfile<span>...</span></span> 
<span>   <span><span>CREATE</span></span> pfile <span><span>FROM</span></span> spfile;</span> 
<span>   <span><span>CREATE</span></span> spfile <span><span>FROM</span></span> memory;</span> 
<span>   <span><span>CREATE</span></span> pfile <span><span>FROM</span></span> memory; <span><span>--</span></span></span><span><span>记得这个好像是</span><span>11g</span><span>才有的语法吧</span></span> 

11、了解instance的相关信息

<span>   <span><span>SELECT</span></span> instance_name<span>,</span> host_name<span>,</span> <span><span>STATUS</span></span><span>,</span> archiver<span>,</span> </span> 
<span>           database_status<span>,</span> instance_role<span>,</span> active_state</span> 
<span>   <span><span>FROM</span></span> v$instance;</span> 

12、用户和密码相关
是否使用了缺省密码?
是否使用了profile?
是否起用了密码验证函数?
用户身份验证的方法?
密码是否区分大小写等。

<span>   <span><span>SELECT</span></span> name<span>,</span> value <span><span>FROM</span></span> gv$parameter <span><span>WHERE</span></span> name <span>=</span> <span>'resource_limit'</span>;</span> 
<span>   <span><span>SELECT</span></span> profile<span>,</span> resource_name<span>,</span> <span><span>LIMIT</span> <span>FROM</span></span> dba_profiles <span><span>ORDER</span> <span>BY</span></span> <span>1</span><span>,</span><span>2</span>;</span> 
 
<span>    <span><span>SELECT</span></span> username<span>,</span> profile <span><span>FROM</span></span> dba_users <span><span>WHERE</span></span> account_status<span>=</span><span>'OPEN'</span> <span><span>ORDER</span> <span>BY</span></span> <span>1</span>;</span> 
 
<span>   <span><span>SELECT</span></span> d<span>.</span>username<span>,</span> u<span>.</span>account_status</span> 
<span>   <span><span>FROM</span></span> dba_users_with_defpwd d<span>,</span> dba_users u</span> 
<span>   <span><span>WHERE</span></span> d<span>.</span>username <span>=</span> u<span>.</span>username <span><span>AND</span></span> account_status <span>=</span> <span>'OPEN'</span> </span> 
<span>    <span><span>ORDER</span> <span>BY</span></span> <span>2</span><span>,</span><span>1</span>;</span> 

 
13、是否打开了BLOCK CHANGE TRACKING

<span>   <span><span>SELECT</span></span> filename<span>,</span> <span><span>STATUS</span></span><span>,</span> bytes <span><span>FROM</span></span> v$block_change_tracking;</span> 

14、起用了那些特性(Feature)?
DBMS_FEATURE_USAGE_REPORT
 
15、表空间和数据文件的规划
这个大家都很熟悉,就不写了
 
16、字符集相关

<span>   <span><span>SELECT</span></span> <span>*</span> <span><span>FROM</span></span> database_properties;</span> 

 
17、系统中是否存在invalid对象

<span>   <span><span>SELECT</span></span> owner<span>,</span> object_type<span>,</span> COUNT<span>(*)</span></span> 
<span>   <span><span>FROM</span></span> dba_objects</span> 
<span>   <span><span>WHERE</span> <span>STATUS</span></span> <span>=</span> <span>'INVALID'</span></span> 
<span>   <span><span>GROUP</span> <span>BY</span></span> owner<span>,</span> object_type;</span> 

18、更进一步的
是否使用了ASM
当前系统的备份方法和策略是什么?
网络文件的配置是如何的?
 
19、查看一下最近的alert日志,获取一些有用的信息
20、跑几个性能分析报告,看看最近系统的运行状态如何
21、跑一个RDA报告,收集完整的系统状态报告
 
好了,有了这一些基本信息(不完全),你基本上对你新接触的这个系统有了一个大概的了解,接来下,你再慢慢的深入分析,然后制订出一套符合实际情况的运维规范来

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:coretext2Nächster Artikel:新浪微博OAuth2.0 VS OAuth1.0 主要区别总结~