Skip to content

Oracle 11g OCM之ADR自动诊断信息库

ADR自动诊断信息库

1.ADR概念（链接介绍）

Document 422893.1 – 11g Understanding Automatic Diagnostic Repository.
Document 453125.1 – 11g Diagnosability: Frequently Asked Questions
《Database Administrator’s Guide》 第九章

2.ADR设定

Automatic Diagnostic Repository（ADR)是oracle 11g推出专门用于管理数据库运行日志和dump文件管理的一个
功能。10g之前的USER_DUMP_DEST,CORE_DUMP_DEST,BACKGROUND_DUMP_DEST参数都废弃，整合在指
定的目录中，DIAGNOSTIC_DEST默认指向$ORACLE_BASE,如果不存在$ORACLE_BASE,指定$ORACLE_HOME/log.

SQL> show parameter dia

NAME                                 TYPE        VALUE
———————————— ———– ——————————
diagnostic_dest                      string      /oracle
SQL> !echo $ORACLE_BASE
/oracle

3.ADR的目录结构

ADR的目录存在多级结构，按照实例，功能区分，目录结构如下：

  • 3.1 系统层面结构
  1. diag/
  2. |– rdbms
  3. |   `– luda
  4. |       `– luda
  5. |           |– alert
  6. |           |– cdump
  7. |           |– hm
  8. |           |– incident
  9. |           |– incpkg
  10. |           |– ir
  11. |           |– lck
  12. |           |– metadata
  13. |           |– stage
  14. |           |– sweep
  15. |           `– trace
  16. |– tnslsnr
  17.     |– luda
  18.         |– listener
  19.             |– alert
  20.             |– cdump
  21.             |– incident
  22.             |– incpkg
  23.             |– lck
  24.             |– metadata
  25.             |– stage
  26.             |– sweep
  27.             |– trace

 

  • 3.2 数据库层面结构

在数据库层面可以通过视图v$diag_info查询ADR相关信息：

SQL> col value for a40
SQL> /

INST_ID NAME                                     VALUE
———- —————————————- —————————————-
1 Diag Enabled                             TRUE
1 ADR Base                                 /oracle
1 ADR Home                                 /oracle/diag/rdbms/luda/luda
1 Diag Trace                               /oracle/diag/rdbms/luda/luda/trace
1 Diag Alert                               /oracle/diag/rdbms/luda/luda/alert
1 Diag Incident                            /oracle/diag/rdbms/luda/luda/incident
1 Diag Cdump                               /oracle/diag/rdbms/luda/luda/cdump
1 Health Monitor                           /oracle/diag/rdbms/luda/luda/hm
1 Default Trace File                       /oracle/diag/rdbms/luda/luda/trace/luda_
ora_13859.trc

INST_ID NAME                                     VALUE
———- —————————————- —————————————-
1 Active Problem Count                     0
1 Active Incident Count                    0

11 rows selected.

SQL> l
1  select * from v$diag_info

  • 3.3 目录存储信息

alter：包含实例的预警日志（XML格式）；
cdump：包含核心文件；
hm：包含Health Monitor报表；
incident：包含每个意外事件的子目录，每个意外事件的子目录中还包含该意外事件的所有跟踪转储；
incpkg：包含你为上传到Oracle Support而创建的意外事件包；
ir：包含为每个实例创建的意外事件报表；
trace：存储用户会话跟踪文件。

4.ADRCI接口

Oracle提供了一个名为adrci的新命令行工具，用来帮助挖掘存储在ADR中的诊断数据。除了让你查看数据库存储在
ADR中的诊断数据外，adrci还提供了其他一些重要功能，它允许把意外事件和问题信息打包成ZIP文件发送给
Oracle Support。诊断数据包括跟踪和转储文件、预警日志条目以及Oracle Database 11g的新Health Monitor报
表等

  • 4.1登陆ADRCI

[oracle@luda 17:42:27|/oracle]adrci

ADRCI: Release 11.2.0.1.0 – Production on Wed Nov 14 17:42:31 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = “/oracle”
adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
“HELP EXTENDED” to see the list

  • 4.2 alert日志信息查询

adrci> show home
ADR Homes:
diag/rdbms/luda/luda
diag/tnslsnr/luda/listener
adrci> set home diag/rdbms/luda/luda
adrci> show alert -tail
2012-11-14 11:20:40.846000 +08:00
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2012-11-14 11:20:42.561000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2012-11-14 11:20:44.834000 +08:00
Starting background process QMNC
QMNC started with pid=27, OS id=6792
2012-11-14 11:20:47.933000 +08:00
Completed: ALTER DATABASE OPEN
2012-11-14 11:20:50.439000 +08:00
Starting background process CJQ0
CJQ0 started with pid=32, OS id=6843
2012-11-14 11:25:45.943000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=6956

adrci> show alert -tail -f
2012-11-14 11:20:40.846000 +08:00
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2012-11-14 11:20:42.561000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2012-11-14 11:20:44.834000 +08:00
Starting background process QMNC
QMNC started with pid=27, OS id=6792
2012-11-14 11:20:47.933000 +08:00
Completed: ALTER DATABASE OPEN
2012-11-14 11:20:50.439000 +08:00
Starting background process CJQ0
CJQ0 started with pid=32, OS id=6843
2012-11-14 11:25:45.943000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=6956
DIA-48223: Interrupt Requested – Fetch Aborted – Return Code [1] [ALERT_EXT]

  • 4.3 事件查询以及执行状态监控

查看未解决事件

adrci> show incident

ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
0 rows fetched

健康监控状态监控

*SQLPLUS下面

SQL>         BEGIN
2              DBMS_HM.RUN_CHECK(‘Dictionary Integrity Check’, ‘my_run’);
3          END;
4  /

PL/SQL procedure successfully completed.

*ADRCI下面

adrci> show hm_run

ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************

**********************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID                        1
RUN_NAME                      HM_RUN_1
CHECK_NAME                    DB Structure Integrity Check
NAME_ID                       2
MODE                          2
START_TIME                    2012-11-13 14:30:33.946816 +08:00
RESUME_TIME                   <NULL>
END_TIME                      2012-11-13 14:30:40.052261 +08:00
MODIFIED_TIME                 2012-11-13 14:30:40.052261 +08:00
TIMEOUT                       0
FLAGS                         0
STATUS                        5
SRC_INCIDENT_ID               0
NUM_INCIDENTS                 0
ERR_NUMBER                    0
REPORT_FILE                   <NULL>

**********************************************************
HM RUN RECORD 2
**********************************************************
RUN_ID                        21
RUN_NAME                      my_run
CHECK_NAME                    Dictionary Integrity Check
NAME_ID                       24
MODE                          0
START_TIME                    2012-11-14 21:24:32.078107 +08:00
RESUME_TIME                   <NULL>
END_TIME                      2012-11-14 21:24:36.521996 +08:00
MODIFIED_TIME                 2012-11-14 21:24:36.521996 +08:00
TIMEOUT                       0
FLAGS                         0
STATUS                        5
SRC_INCIDENT_ID               0
NUM_INCIDENTS                 0
ERR_NUMBER                    0
REPORT_FILE                   <NULL>
2 rows fetched

  • 4.4诊断信息删除

默认情况下ADR的删除策略如下：

SHORT_POLICY:720小时（30天）
LONG_POLICY：365天

删除策略确认：

adrci> show control

ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME
——————– ——————– ——————– —————————————-
2683996405           720                  8760                 2012-11-13 14:28:14.919439 +08:00
1 rows fetched

设置删除策略

adrci> set control (SHORTP_POLICY = 3600)
adrci> set control (LONGP_POLICY = 7200)
adrci> show control

ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME
——————– ——————– ——————– —————————————-
2683996405           3600                 7200                 2012-11-14 21:31:54.271020 +08:00
1 rows fetched

删除策略对应的归类（目录）：
LONGP_POLICY 删除策略对应长时间保留对象，为如下归类：

ALERT
INCIDENT
SWEEP
STAGE
HM

SHORTP_POLICY 删除策略对应短时间保留对象，为如下归类：

TRACE
CDUMP
UTSCDMP
IPS

（在11.1.0.7之前监听日志和trace文件的是不在ADR删除范围之内，11.2版本之后解决。）

  • 4.5诊断信息删除

在ADRCI中是用purge命令手工删除信息，purge命令语法如下：

purge:诊断信息删除
purge -i <incident_id>:指定事件ID进行删除
purge -age <value(分単位)>:指定时间范围进行删除
purge -age <value> -type <type>:指定删除特定时间范围内的指定类型文件

命令示例：

purge -age 60 -type ALERT

在11g里使用ADRCI删除归档信息只删除XML格式的告警日志，文本格式的告警日志不会被删除，oracle归类为BUG6800147

删除健康监控状态信息：

adrci> show hm_run

ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************

**********************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID                        1
RUN_NAME                      HM_RUN_1
CHECK_NAME                    DB Structure Integrity Check
NAME_ID                       2
MODE                          2
START_TIME                    2012-11-13 14:30:33.946816 +08:00
RESUME_TIME                   <NULL>
END_TIME                      2012-11-13 14:30:40.052261 +08:00
MODIFIED_TIME                 2012-11-13 14:30:40.052261 +08:00
TIMEOUT                       0
FLAGS                         0
STATUS                        5
SRC_INCIDENT_ID               0
NUM_INCIDENTS                 0
ERR_NUMBER                    0
REPORT_FILE                   <NULL>

**********************************************************
HM RUN RECORD 2
**********************************************************
RUN_ID                        21
RUN_NAME                      my_run
CHECK_NAME                    Dictionary Integrity Check
NAME_ID                       24
MODE                          0
START_TIME                    2012-11-14 21:24:32.078107 +08:00
RESUME_TIME                   <NULL>
END_TIME                      2012-11-14 21:24:36.521996 +08:00
MODIFIED_TIME                 2012-11-14 21:24:36.521996 +08:00
TIMEOUT                       0
FLAGS                         0
STATUS                        5
SRC_INCIDENT_ID               0
NUM_INCIDENTS                 0
ERR_NUMBER                    0
REPORT_FILE                   <NULL>
2 rows fetched

adrci> purge -age 1 -type hm
adrci> show hm_run

ADR Home = /oracle/diag/rdbms/luda/luda:
*************************************************************************
0 rows fetched

OCM考试相关 Oracle | Guang Cai Li | |   ADR，自动诊断信息库

Oracle 11g OCM之表空间加密

表空间加密

  • 1.1.wallet链接
  • 1.2.表空间加密解释连接

表空间加密即对表空间里的所有数据进行自动加密，它能够保证在数据文件或者数据备份泄漏的情况下保证数据的安全性。
表空间的加密采用transparent data encryption architecture即透明数据加密结构，加密方式类似以前常见的TDE。加密的密钥再次由一个称为主密钥的密钥二次加密，主密钥存储在wallet中。加密后的表空间加密的密钥存储在数据库中的字典表中。只需要对数据表空间进行加密，临时表空间和回滚表空间并不需要加密，因为在将数据写入redo，undo，temp时候，数据库会自动进行加密。wallet无法对sys用户的对象或者数据进行加密。

加密表空间有一定的限定条件，不能对已经在使用的表空间进行加密，但是可以通过向加密表空间中利用数据泵导入数据，或使用ctas或atm把已经存在数据转储到加密表空间。官网还关于LOB方面注释：
There is no need to use LOB encryption on SECUREFILE LOBs stored in an encrypted tablespace.

使用表空间加密的步骤：

  • 1.3.创建表空间加密的主密匙存放目录（wallet目录）

[oracle@luda 23:03:32|~]mkdir $ORACLE_HOME/luda_wallets

  • 1.4.设置SQLNET.ORA文件中的ENCRYPTION_WALLET_LOCATION 参数指定使用密匙目录。

[oracle@luda 23:07:37|/oracle/product/11.2/network/admin]cat sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=file)

(METHOD_DATA=

(DIRECTORY=/oracle/product/11.2/luda_wallets)))

[oracle@luda 23:07:40|/oracle/product/11.2/network/admin]

  • 1.5.重新启动数据库

[oracle@luda 23:09:23|/oracle/product/11.2/network/admin]sq

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 13 23:09:25 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             318769536 bytes

Database Buffers           96468992 bytes

Redo Buffers                6094848 bytes

Database mounted.

Database opened.

SQL>

  • 1.6.打开wallet

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “oracle”;

System altered.

*已经打开wallet，再次打开就会报错

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “oracle”;

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “oracle”

*

ERROR at line 1:

ORA-28354: wallet already open
SQL>

  • 1.7. 表空加密的算法

透明的数据加密，支持标准的加密算法，包括高级加密标准（AES）和三重数据加密标准（3DES）算法，默认使用AES128：

1）AES256

2）AES192

3）AES128

4）3DES168

oracle官方解释对于加密表空间没有额外的磁盘空间开销。

  • 1.8 创建加密表空间

SQL>   create tablespace luda_ent datafile ‘/data01/ludaent01.dbf’ size 100M

2          encryption default storage(encrypt);

Tablespace created.

  • 1.9 其他加密算法操作

create tablespace luda_ent02 datafile ‘/data01/ludaent02_1.dbf’ size 20M

encryption using ‘3DES168’ default storage(encrypt);

  • 2.0查询表空间的加密信息

SQL> SELECT t.name, e.encryptionalg algorithm  FROM  v$tablespace t, v$encrypted_tablespaces e WHERE t.ts# = e.ts#;

NAME                           ALGORIT

—————————— ——-

LUDA_ENT                       AES128

LUDA_ENT02                     3DES168

  • 2.1.关闭加密表空间

 

drop tablespace luda_ent including contents and datafiles;

drop tablespace luda_ent02 including contents and datafiles;

col WRL_PARAMETER for a40

col status for a10

select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                            STATUS

——————– —————————————- ———-

file                 /oracle/product/11.2/luda_wallets        OPEN

  • 2.2.关闭wallet

SQL> alter system set encryption wallet close identified by “oracle”;

System altered.

*关闭wallet后，对已经加密过的数据将无法查询。

OCM考试相关 Oracle | Guang Cai Li |

Oracle 11g OCM复习项目列表（更新中）

标注：

字体蓝色：表示已经写完，正在整理发布

字体黑色 ：表示正在酝酿怎么写~:)

 

1.数据库，备份恢复管理器，GC，网络配置和管理

 

  1. 服务端的网络配置
  2. 客户端的网络配置
  3. 加密表空间的管理
  4. 使用NFS文件系统创建表空间
  5. 管理使用自动诊断信息库（ADR）
  6. 创建数据库冷备份
  7. 管理用户帐户和使用敏感密码
  8. 使用Opatch安装补丁
  9. GC的Agent的安装和配置
  10. 创建和设置恢复目录
  11. 配置RMAN
  12. 执行数据文件的冗余备份
  13. 创建归档备份

 

2.数据和数据仓库管理

 

  1. 恢复物化视图的快速刷新或查询重写
  2. 传输表空间
  3. 星型转换
  4. 设置并行查询操作
  5. 使用和访问Securefiles的LOBS
  6. 创建分区表
  7. 设置闪回归档
  8. 使用Oracle流捕获和传播表中发生变化的的数据

 

3.Dataguard

 

  1. 创建物理Standby并使用实时模式
  2. 配置快速增量备份，以减少开销
  3. 配置Observer
  4. DG的切换和回切
  5. 将备库转换成快照备库
  6. 配置归档日志删除策略

 

4.性能管理

 

  1. 配置的资源管理器来控制I / O的数量活动会话的数量
  2. 使用高速缓存查询结果
  3. 使用多列统计信息
  4. 使用索引分区
  5. 使用SQL Tuning Advisor
  6. 使用SQLAccess Advisor
  7. 配置基线模板
  8. 使用SQL执行计划管理特性
  9. 数据库重放
OCM考试相关 Oracle | Guang Cai Li | |   11g 11g ocm

更新

最近更新

由于工作占据大量时间，没有充足的时间把所学所用整理，汇成文章，有违自己学习和分享意愿，

马上驻场结束，把已经整理和马上要整理的东西列出，并罗列出来方便自己以及同行点评学习。

1.10g OCM升级11g OCM的复习练习

2.Redo和Undo的运行原理分析

3.SQL性能调整之等待事件，调整手段，锁，索引类型，数据访问

4.备份和恢复技术的结合实例

5.读书笔记卡耐基系列

生活 | Guang Cai Li |

happens to the very best of us

Sep 14:Time is drained: Best Twin Cities outdoor rooms for 2015Aug 14:Make a hike: 9 great hiking spots with Twin CitiesJul 3:10 places to get a great soft serve ice cream coneJun 8:8 Twin Cities high altitude bike trails for all levelsApr 17:13 great mn places to campMar 27:Great finds? Minnesota’s least were excited by state parksMar 17:Minnesota’s 10 most widely used state parksJun 6:Twin villages mini golf: 13 places to putt by means of summerStart early, Drive http://www.packersproshop.cc/ two a number of a number of or less, Paddle for almost all the day and get home in time for bed. Or camp for through the night or Packers jersey two. Direction, Within two hours of the metro lies a lot of routes for canoes and kayaks, From out of law school floats to roaring froth.

90). You are looking for our lifestory, Tallys McAdams(1993), Is ever very concluding. Things can invariably change(L. Starting the 2015 parade, In their normal angle, Are the Canton City Police bicycles and T 3 Motion Vehicles.2. The country Army, Cleveland sales pitching Battalion and Canton enrolling Center.3. The typical in the parade, The tennis Player balloon.

In the event that I called him and he did not come, Avoid get upset and angry. Pro dog trainer asked me how quickly I would go to someone who I know was going to be upset at me. It looks like we can all say, “Not in a short time, I found that to get him to Packers Pro Shop come, My dog had to link being near me with good feelings.

Barbie’s handlers won’t discuss specifics of MGA’s lawsuit. But in a written record, El Segundo based Mattel said MGA got needed for Bryant’s case because”Its sales of the Bratz doll line could be at risk. In our feeling, MGA’s recently filed suit against Mattel is part of its overall litigation strategy pertaining to Mattel’s suit against Mr.

He said they official Packers jersey all been ordered to make them go away after WWII, But he had hidden his in the garage instead. I remember it looking just like the lampshade in this picture like leather at all, But much lighter and thinner and more fit. I never unconsidered how disgusted I felt, And he was SO satisfied with it..

Green Bay played in its third NFC world-class game in the past eight seasons(2007 since 2010), Which is tied with san diego for the most in the NFC over that span. McCarthy obtained 101 career wins(Regarding playoffs), The second most advantages in franchise history behind Lambeau(212). Green Bay bettered to 17 5(.773) All time at home the particular postseason.

未分类 | Guang Cai Li |