Skip to content

OCM考试相关

Oracle 11g OCM之Rman 新特性

《Oracle® Database Backup and Recovery User’s Guide》11g Release 1 (11.1)

1.11gRMAN新特性概要（R1）

*Interfile backup parallelism
*Faster backup compression
*Better security
*Active database duplication
Improved handling of long-term backups
*Backup failover for archived redo logs
*Archived redo log deletion policy enhancements
*Recovery catalog enhancements
Undo backup optimization
Block media performance improvements
Block change support for standby databases
Improved RMAN scripting
Backup of read-only transportable tablespaces

本文对重点部分（带*）进行讲解

2.Interfile backup parallelism 并行多段备份

2.1 多段备份（Multisection backup）

BACKUP SECTION SIZE …. TABLESPACE tbs_name/DATAFILE N;

从11gr1开始oracle的在rman的backup中增加了section参数，可以通过section参数指定备份对象（表空间，数据
文件）生成的备份片按照section size指定的单位生成多个备份段。相对10g以前的版本，该特性解决了ORACLE对大
数据量的单数据文件的备份提供支持，并且支持单个数据文件的并行备份，提高备份速度。

在backup命令中，section size不可以和maxsize一起使用，每个数据文件最多可以有256个section，如果指定的
section值超过了datafile的实际大小，Rman不会使用Section指定的size进行备份，会直接进行整个datafile的
直接备份.

例1.进行单个表空间的分段备份

SQL> col file_name for a40
SQL> col tablespace_name for a30
SQL> select file_name,tablespace_name，bytes/1024/1024 from dba_data_files where tablespace_name=’CAT_TBS’;
FILE_NAME                                TABLESPACE_NAME                BYTES/1024/1024
—————————————- —————————— —————
/data01/cat_tbs01.dbf                    CAT_TBS                                     20

对CAT_TBS表空间进行分段备份：

[oracle@luda 22:51:54|~]rman target / catalog rman1/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Nov 22 22:52:06 2012

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

connected to target database: LUDA (DBID=518309061)
connected to recovery catalog database

RMAN> backup section size 10M tablespace cat_tbs;

Starting backup at 22-NOV-12
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1 through 1280
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/06nqvsil_1_1 tag=TAG20121122T225309 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1281 through 2560
channel ORA_DISK_1: starting piece 2 at 22-NOV-12
channel ORA_DISK_1: finished piece 2 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/06nqvsil_2_1 tag=TAG20121122T225309 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

对/data01/cat_tbs01.dbf 进行分段备份：

RMAN> backup section size 5M datafile ‘/data01/cat_tbs01.dbf’;

Starting backup at 22-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1 through 640
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/08nqvspu_1_1 tag=TAG20121122T225702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 641 through 1280
channel ORA_DISK_1: starting piece 2 at 22-NOV-12
channel ORA_DISK_1: finished piece 2 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/08nqvspu_2_1 tag=TAG20121122T225702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1281 through 1920
channel ORA_DISK_1: starting piece 3 at 22-NOV-12
channel ORA_DISK_1: finished piece 3 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/08nqvspu_3_1 tag=TAG20121122T225702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1921 through 2560
channel ORA_DISK_1: starting piece 4 at 22-NOV-12
channel ORA_DISK_1: finished piece 4 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/08nqvspu_4_1 tag=TAG20121122T225702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

指定section size 大于/data01/cat_tbs01.dbf的大小的值进行备份：

RMAN> backup section size 30M datafile ‘/data01/cat_tbs01.dbf’;

Starting backup at 22-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/oracle/product/11.2/dbs/0cnqvss5_1_1 tag=TAG20121122T225813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

可以发现如果section size大于备份的datafile的大小，那么数据库将忽略section的设置。

2.2 并行（parallel）的进行多段（section）备份

RMAN>   run {
2>         allocate channel c1 device type disk format ‘/data01/rman/OMR_%U’;
3>         allocate channel c2 device type disk format ‘/data01/rman/OMR_%U’;
4>         backup section size 10M tablespace CAT_TBS;
5>       }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=201 device type=DISK

allocated channel: c2
channel c2: SID=393 device type=DISK

Starting backup at 22-NOV-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1 through 1280
channel c1: starting piece 1 at 22-NOV-12
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/cat_tbs01.dbf
backing up blocks 1281 through 2560
channel c2: starting piece 2 at 22-NOV-12
channel c1: finished piece 1 at 22-NOV-12
piece handle=/data01/rman/OMR_0dnqvt43_1_1 tag=TAG20121122T230227 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c2: finished piece 2 at 22-NOV-12
piece handle=/data01/rman/OMR_0dnqvt43_2_1 tag=TAG20121122T230227 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12
released channel: c1
released channel: c2

RMAN> list backup of tablespace cat_tbs;
略。。。
Backup Set Copy #1 of backup set 211
Device Type Elapsed Time Completion Time Compressed Tag
———– ———— ————— ———- —
DISK        00:00:01     22-NOV-12       NO         TAG20121122T230227

List of Backup Pieces for backup set 211 Copy #1
BP Key  Pc# Status      Piece Name
——- — ———– ———-
213     1   AVAILABLE   /data01/rman/OMR_0dnqvt43_1_1
214     2   AVAILABLE   /data01/rman/OMR_0dnqvt43_2_1

3 Rman备份压缩算法

在oracle 10g的版本中为Rman提供BZIP2的压缩算法，从11gR1版本开始oracle提供全新的算法ZLIB，两者的主要差异
在于：
1.BZIP2的压缩比要优于ZLIB
2.ZLIB的压缩速度要快于BZIP2
所以对于BZIP2和ZLIB的选择相对来讲就是对备份的速度和备份占用空间的抉择。

备注：

从11g R2开始，Oracle Advanced Compression Option统一了部分压缩算法级别(compression algorithm le
vels)，可同时适用于table data, Data Pump exports 和 RMAN 备份。新的压缩级别分为BASIC、LOW、MEDIUM 和H
IGH.

column ALGORITHM_NAME format a7
column INITIAL_RELEASE format a10
column TERMINAL_RELEASE format a10
column ALGORITHM_DESCRIPTION format a45
select ALGORITHM_NAME, INITIAL_RELEASE, TERMINAL_RELEASE, ALGORITHM_DESCRIPTION, IS_VALID, REQUIRES_ACO from V$RMAN_COMPRESSION_ALGORITHM;

ALGORIT INITIAL_RE TERMINAL_R ALGORITHM_DESCRIPTION                         IS_ REQ
——- ———- ———- ——————————————— — —
BASIC   10.0.0.0.0            good compression ratio                        YES NO
LOW     11.2.0.0.0            maximum possible compression speed            YES YES
MEDIUM  11.2.0.0.0            balance between speed and compression ratio   YES YES
HIGH    11.2.0.0.0            maximum possible compression ratio            YES YES

例：

3.1 更改压缩算法设置(11gR1版本）

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘ZLIB’;

old RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM ‘BZIP2’;
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM ‘ZLIB’;
new RMAN configuration parameters are successfully stored

RMAN> backup as compressed backupset database plus archivelog;
…略

3.2更改压缩算法设置（11gR2版本）

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;

new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

4.定义归档日志（Archived log）删除策略

在11g之前，只有备份集的保存策略，归档日志需要使用delete命令来删除，在11g中可以使用configure命令
来设置oralce的归档保留删除策略。日志删除策略适用于所有日志归档的目的地，包括参数指定的路径，和
闪回区域。另外，除非手工强制删除归档日志，否则归档日志的保留策略遵守定义的归档日志删除策略。

4.1rman的删除策略有4中情况值

4.11：TO APPLIED ON  [ALL] STANDBY
指定归档日志被（所有）备库应用之后才能删除

4.12：TO SHIPPED TO  [ALL] STANDBY
指定归档日志被传送到（所有）备库之后才删除

4.13：NONE
指定归档日志删除策略为空

4.14：BACKED UP integer  TIMES TO DEVICE   TYPE deviceSpecifier
指定归档日志备份到指定设备类型的对应次数之后才删除

测试如下：

4.2默认的策略是none

RMAN> show all;

···略
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

4.3设置删除策略

RMAN> configure archivelog deletion policy to APPLIED ON STANDBY;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

RMAN> configure archivelog deletion policy to SHIPPED TO STANDBY;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
new RMAN configuration parameters are successfully stored

RMAN> configure archivelog deletion policy to backed up 2 times to device type disk;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> configure archivelog deletion policy to none;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored

5.Archived Redo Log Failover

归档重做日志故障转移特性允许RMAN完成备份即使一些归档日志在目的目录是丢失或日志出现损坏情况。如果至少
有一个日志对应于系统固定的日志序列和线程号，那么就可以从闪回区或其他归档的目的地备份完好的日志。这种
特性仅支持使用多路径归档的情况下。

6.Active database duplication
11g Rman快速克隆数据库新特性 参考完整的实验介绍~（另外链接）

OCM考试相关 Oracle | Guang Cai Li | |   11grman rman new features

Oralce 11g OCM之管理恢复目录(CATALOG)

oralce 11g OCM之管理恢复目录

一：11g 版本中catalog的创建以及数据库的注册方式几乎和10g一模一样，如下：

1.创建catalog存放数据的表空间

create tablespace cat_tbs datafile ‘/data01/cat_tbs01.dbf’ size 10M autoextend on next 10M maxsize 500m;

2.创建catalog用户

create user rman identified by oracle default tablespace cat_tbs quota unlimited on cat_tbs;
grant recovery_catalog_owner to rman;

3.创建catalog并注册数据库

[oracle@luda 21:40:23|/oracle/product/11.2/network/admin]rman catalog rman/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Nov 15 21:40:27 2012

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

connected to recovery catalog database

RMAN>
RMAN> create catalog;

recovery catalog created

RMAN> exit

Recovery Manager complete.
[oracle@luda 21:41:54|/oracle/product/11.2/network/admin]rman target / catalog rman/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Nov 15 21:42:08 2012

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

connected to target database: LUDA (DBID=518309061)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> report schema;

Report of database schema for database with db_unique_name LUDA

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    700      SYSTEM               YES     /data01/system01.dbf
2    490      SYSAUX               NO      /data01/sysaux01.dbf
3    90       UNDOTBS1             YES     /data01/undotbs01.dbf
4    5        USERS                NO      /data01/users01.dbf
5    100      EXAMPLE              NO      /data01/example01.dbf
6    10       CAT_TBS              NO      /data01/cat_tbs01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    29       TEMP                 32767       /oracle/oradata/LUDA/datafile/o1_mf_temp_8b3t8vw4_.tmp

RMAN>

二：11g新特性 虚拟恢复目录

《Backup and Recovery User’s Guide》第13章 — Creating and Managing Virtual Private Catalogs

Catalog用来管理存储多个数据库的备份信息，Rman脚本等，在11g中ORACLE推出关于RMAN catalog的一个新特性Virtual Private Catalogs，相对catalog用来管理多个数据库备份等相关信息，Virtual Private Catalogs的主要作用是一个Virtual Private Catalogs对应一个数据库的DBA，在管理的时候可以避免catalog恢复时候导致其他数据库的相关备份信息，脚本等丢失，而每一个Virtual Private Catalogs可以把变动只会针对对应管理员管理的数据库，而不是所有的数据库。一个catalog下不限制Virtual Private Catalog的个数，Virtual Private Catalogs的归属用户和Catalog的归属用户必须不同。

创建虚拟私有目录服务步骤

1.创建相关表空间

SQL> create tablespace vptbs datafile ‘/data01/vptbs01.dbf’ size 50M;

Tablespace created.

2.创建相关用户

SQL> create user luda_vp identified by oracle default tablespace vptbs quota unlimited on vptbs;

User created.

3.给予用户以及catalog权限

给予用户数据库权限

SQL> grant recovery_catalog_owner to luda_vp;

Grant succeeded.

赋予catalog权限

[oracle@luda 23:03:24|~]rman catalog rman/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Nov 20 23:03:43 2012

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

connected to recovery catalog database

RMAN> grant catalog for database luda to luda_vp;

Grant succeeded.

RMAN> grant register database to luda_vp;

Grant succeeded.

RMAN>

4.使用虚拟目录用户登陆rman创建虚拟目录

[oracle@luda 23:06:18|~]rman catalog luda_vp/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Nov 20 23:06:35 2012

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

connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN

5.移除虚拟目录用户权限

使用catalog用户登陆rman

[oracle@luda 23:10:28|~]rman catalog rman/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Nov 20 23:10:45 2012

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

connected to recovery catalog database

RMAN>

使用revoke命令移除相关权限

RMAN> revoke catalog for database luda from luda_vp
2> ;

Revoke succeeded.

RMAN> revoke register database from luda_vp;

Revoke succeeded.

RMAN>

6.删除虚拟目录

使用虚拟目录用户登陆rman，执行删除操作

[oracle@luda 23:14:39|~]rman catalog luda_vp/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Nov 20 23:14:53 2012

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

connected to recovery catalog database
recovery catalog is not installed

RMAN> drop catalog;

recovery catalog owner is LUDA_VP
enter DROP CATALOG command again to confirm catalog removal

RMAN> drop catalog;

virtual catalog dropped

三.catalog的迁移以及删除

catalog的信息可以在用户之间进行迁移，可以是同库的用户也可以是不同库的用户。
通过import catalog相关命令，可以指定需要导入信息的实例的DBID，DB_NAME等。

1.首先测试为同库用户：

在luda实例下创建另外一个catalog用户：

[oracle@luda 21:58:08|~]sq

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 21 21:58:10 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> create user rman1 identified by oracle default tablespace cat_tbs quota unlimited on  cat_tbs;

User created.

SQL> grant recovery_catalog_owner to rman1;

Grant succeeded.

使用rman1用户创建catalog

[oracle@luda 22:02:59|~]rman catalog rman1/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Nov 21 22:03:10 2012

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

导入rman用户管理的catalog 指定数据库实例为luda

[oracle@luda 22:06:35|~]rman catalog rman1/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Nov 21 22:06:42 2012

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

connected to recovery catalog database

RMAN> import catalog rman/oracle@luda db_name=luda;

Starting import catalog at 21-NOV-12
connected to source recovery catalog database
import validation complete
database unregistered from the source recovery catalog
Finished import catalog at 21-NOV-12

查询导入后rman和rman1用户catalog信息

[oracle@luda 22:11:28|~]rman target/ catalog rman/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Nov 21 22:11:48 2012

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

connected to target database: LUDA (DBID=518309061)
connected to recovery catalog database

RMAN> report schema;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 11/21/2012 22:12:08
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

RMAN> exit

Recovery Manager complete.
[oracle@luda 22:12:12|~]rman target/ catalog rman1/oracle@luda

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Nov 21 22:12:15 2012

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

connected to target database: LUDA (DBID=518309061)
connected to recovery catalog database

RMAN> report schema;

starting full resync of recovery catalog
full resync complete
Report of database schema for database with db_unique_name LUDA

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    700      SYSTEM               YES     /data01/system01.dbf
2    500      SYSAUX               NO      /data01/sysaux01.dbf
3    90       UNDOTBS1             YES     /data01/undotbs01.dbf
4    5        USERS                NO      /data01/users01.dbf
5    100      EXAMPLE              NO      /data01/example01.dbf
6    10       CAT_TBS              NO      /data01/cat_tbs01.dbf
7    50       VPTBS                NO      /data01/vptbs01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    29       TEMP                 32767       /oracle/oradata/LUDA/datafile/o1_mf_temp_8b3t8vw4_.tmp

通过测试可以发现rman用户的catalog信息导入rman1用户的catalog之后，rman用户的catalog信息就清除掉了；而实际在使用过程中可以使用NO UNREGISTER参数来指定不删除源用户（这里指RMAN用户）的catalog信息。

2.不同数据库之间的用户的catalog迁移

不同用户数据库用户之间的catalog迁移参考测试1中的用户连接

import catalog rman/oracle@luda db_name=luda;

比如是把rman用户下OMR数据库的catalog信息迁移到rman1
命令如下：

rman catalog rman1/oracle@luda 登陆rman
import catalog rman/oracle@omr db_name=omr;

OCM考试相关 Oracle | Guang Cai Li | |   11g catalog

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 |