R12c 新特性:RMAN 可插拔数据库的备份和恢复 (Doc ID 1945849.1)

适用于:

Oracle Database – Enterprise Edition – 版本 12.1.0.1 和更高版本
Oracle Database Cloud Schema Service – 版本 N/A 和更高版本
Oracle Database Exadata Cloud Machine – 版本 N/A 和更高版本
Oracle Database Exadata Express Cloud Service – 版本 N/A 和更高版本
Oracle Cloud Infrastructure – Database Service – 版本 N/A 和更高版本
本文档所含信息适用于所有平台

 

用途

概述 Oracle 12c 新特性,如何使用 RMAN 备份和恢复可插拔数据库。

 

适用范围

数据库管理员管理大量的数据库,通过 RMAN 备份。他们期望实施一个容器数据库进行统一备份,容器数据库包含一个或者多个可插拔数据库。需要掌握 RMAN 知识。

 

详细信息

什么是容器数据库?

12c 数据库可以是容器数据库或非容器数据库,本文将非容器数据库简称为 non-CDB,容器数据库简称为CDB。

12c 之前的数据库库只能作为非容器数据库即 non-CDBs。

一个容器数据库可以包含 0 个,1 个或多个用户创建可插拔数据库。可插拔数据库简称为 PDB。

容器数据库包括:

  • 一个根容器(即:CDB$ROOT)有 SYSTEM,SYSAUX,UNDO 和 TEMP 表空间,以及控制文件和在线日志文件。
  • 一个种子容器(即:PDB$SEED)有 SYSTEM,SYSAUX,TEMP,EXAMPLE 表空间,种子容器作为创建可插拔数据库(PDB)的模板。

 

什么是可插拔数据库?

可插拔数据库(PDB)就是一个用户创建的容器,存放用户应用系统的数据和代码。例如:人力应用系统,薪酬管理系统等。
可插拔数据库包括:

  • SYSTEM,SYSAUX,TEMP 表空间
  • 任意多用户创建的表空间
  • PDB 向容器数据库的 UNDO 表空间,控制文件和在线日志文件写信息

 

当可插拔数据库往容器数据库的 UNDO 表空间和在线日志文件写信息时,会注释每个 UNDO 和 REDO 属于哪个可插拔数据库。

RMAN 备份可插拔数据库

执行 RMAN 备份的用户必须要有 SYSDBA 或新权限 SYSBACKUP

RMAN 可从根容器端运行,如:rman target sys/<pw>@t12ccdb

rman target /

或者从可插拔数据库端运行,如:rman target sys/<pw>@t12cpdb1

当 RMAN 连接的数据库是可插拔数据库,RMAN 命令仅作用于被连的可插拔数据库。

当 RMAN 连接的数据库是根容器数据库,RMAN 命令默认作用于容器中的所有文件,除非使用 PDB 名称进行了特别限制。

RMAN 命令 REPORT SCHEMA 能显示容器数据库的所有数据文件。
下面例子显示可插拔数据库 T12cPDB1 在容器数据库 T12cCDB 中:

 

% rman target sys/<pw>@t12ccdb
RMAN> report schema;using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name T12CCDB
** (filenames have been edited for clarity)List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    960      SYSTEM                 ***     …/oradata/T12CCDB/datafile/o1_mf_system_8008cm5s_.dbf
3    660      SYSAUX                 ***     …/oradata/T12CCDB/datafile/o1_mf_sysaux_80089voz_.dbf
4      50     UNDOTBS1             ***     …/oradata/T12CCDB/datafile/o1_mf_undotbs1_8gtp7g6l_.dbf
5    250     PDB$SEED:SYSTEM  ***     …/oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_system_8008jc7k_.dbf
6       5     USERS                    ***     …/oradata/T12CCDB/datafile/o1_mf_users_8008fnov_.dbf
7    490     PDB$SEED:SYSAUX  ***     …/oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_sysaux_8008jc8m_.dbf
8    250     T12CPDB1:SYSTEM  ***     …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_system_8008r3wh_.dbf
9    510     T12CPDB1:SYSAUX  ***     …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf
10     5     T12CPDB1:USERS     ***     …/oradata/T12CCDB/datafile/o1_mf_users_8gtp7ghf_.dbf
20  100     T12CPDB1:RECTBL   ***     …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    530      TEMP                      32767       …/oradata/T12CCDB/datafile/o1_mf_temp_8bz0jcxg_.tmp
2    20       PDB$SEED:TEMP        32767       …/oradata/T12CCDB/C40F9B49FC9C19E0E0430BAAE80AFF01/datafile/o1_mf_temp_8bz0jfkj_.tmp
3    20       T12CPDB1:TEMP       32767       …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_temp_8bz0jh7x_.tmp

现在只有 REPORT SCHEMA 命令能很容易的显示容器数据库中数据文件属于哪个可插拔数据库。
假如连接的数据库是可插拔数据库,仅显示它自己的数据文件:

% rman target sys/<pw>@t12cpdb1
RMAN> report schema;List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
8    250      T12CPDB1:SYSTEM      ***     …/oradata/T12CCDB/datafile/o1_mf_system_8hloc72d_.dbf
9    510      T12CPDB1:SYSAUX      ***     …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf
10   5         T12CPDB1:USERS       ***     …/oradata/T12CCDB/datafile/o1_mf_users_8hlowbh2_.dbf
20   100      T12CPDB1:RECTBL      ***     …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbfList of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
3    20       T12CPDB1:TEMP        32767       …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_temp_8bz0jh7x_.tmp

1. 完整容器数据库备份

备份全部数据文件,包括根容器数据库文件,种子数据库文件,以及所有可插拔数据库文件:

% rman target sys/<pw>@t12ccdb
RMAN> BACKUP DATABASE PLUS ARCHIVELOG ALL DELETE INPUT;
RMAN> LIST BACKUP OF DATABASE;List of Backup Sets
===================…
——- —- — ———- ———– ———— —————
82      Full    2.46G      DISK        00:01:01     17-JAN-13
BP Key: 83   Status: AVAILABLE  Compressed: NO  Tag: TAG20130117T114547
Piece Name: …/fast_recovery_area/T12CCDB/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T114547_8hhs3cgs_.bkp
List of Datafiles in backup set 82
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 2139245    17-JAN-13 …/oradata/T12CCDB/datafile/o1_mf_system_8008cm5s_.dbf
3       Full 2139245    17-JAN-13 …/oradata/T12CCDB/datafile/o1_mf_sysaux_80089voz_.dbf
4       Full 2139245    17-JAN-13 …/oradata/T12CCDB/datafile/o1_mf_undotbs1_8gtp7g6l_.dbf
5       Full 1621614    13-JUL-12 …/oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_system_8008jc7k_.dbf
6       Full 2139245    17-JAN-13 …/oradata/T12CCDB/datafile/o1_mf_users_8008fnov_.dbf
7       Full 1621614    13-JUL-12 …/oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_sysaux_8008jc8m_.dbf
8       Full 2139245    17-JAN-13 …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_system_8008r3wh_.dbf
9       Full 2139245    17-JAN-13 …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf
10      Full 2139245    17-JAN-13 …/oradata/T12CCDB/datafile/o1_mf_users_8gtp7ghf_.dbf
20      Full 2139245    17-JAN-13 …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf

2. 容器数据库部分数据备份

下面例子显示仅备份可插拔数据库 T12CPDB1:

%rman target sys/<pw>@t12ccdb
RMAN> BACKUP PLUGGABLE DATABASE T12CPDB1 TAG ‘T12CPDB1’;
RMAN> LIST BACKUP;…
——- —- — ———- ———– ———— —————
85      Full    590.52M    DISK        00:00:14     17-JAN-13
BP Key: 86   Status: AVAILABLE  Compressed: NO  Tag: T12CPDB1
Piece Name: …/fast_recovery_area/T12CCDB/backupset/2013_01_17/o1_mf_nnndf_T12CPDB1_8hhswy1c_.bkp
List of Datafiles in backup set 85
Container ID: 3, PDB Name: T12CPDB1
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
8       Full 2139742    17-JAN-13 …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_system_8008r3wh_.dbf
9       Full 2139742    17-JAN-13 …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf
10      Full 2139742    17-JAN-13 …/oradata/T12CCDB/datafile/o1_mf_users_8gtp7ghf_.dbf
20      Full 2139742    17-JAN-13 …/oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf

 

当备份可插拔数据库时,不需要像上述例子使用 TAG 参数指定可插拔数据库的名字。因为 RMAN LIST BACKUP 命令自动会显示 RMAN 备份数据属于哪个可插拔数据库。

因为在 FRA 会将 GUID 信息显示在文件路径名称上,在这种情况下,使用下面查询识别可插拔数据库名称和 GUID 的对应关系。

下面例子,显示了可插拔数据库 T12CPDB1 对应的 GUID 是 C4B71645EF062616E0437108DC0A91E4

登录容器数据库:

SQL> SET LINES 150
SQL> SELECT CON_ID, DBID, CON_UID, GUID, NAME FROM v$pdbs;

 

CON_ID       DBID    CON_UID                                       GUID                             NAME
———- ———-       ———-       ——————————– ——————————
2  4031181962  4031181962  C40F9B49FC9C19E0E0430BAAE80AFF01       PDB$SEED
       3    575001283     575001283  C4B71645EF062616E0437108DC0A91E4      T12CPDB1

3. 部分可插拔数据库备份

3a. 连接到根容器数据库,仅备份可插拔数据库 T12CPDB1 的 system 和 sysaux 表空间

% rman target sys/<pw>@t12ccdb
RMAN>BACKUP TABLESPACE T12CPDB1:SYSTEM, T12CPDB1:SYSAUX;

3b. 仅备份可插拔数据库的 SYSTEM 表空间和根容器数据库的 SYSAUX 表空间

当连接到根容器数据库执行备份时,如果没有指定可插拔数据库前缀,默认备份数据文件是根容器数据库

% rman target sys/<pw>@t12ccdb
RMAN>BACKUP TABLESPACE T12CPDB1:SYSTEM, SYSAUX;

3c. 容器数据库中每个文件号都是唯一的,可以连接到根容器数据库,不指定容器前缀,使用文件号进行备份。

下面例子显示如何连接到根容器数据库,不用指定容器数据库前缀,仅备份 CDB$ROOT 的 3 号数据文件,和 T12CPDB1 的 20 号数据文件

% rman target sys/<pw>@t12ccdb
RMAN> BACKUP DATAFILE 3,20;

RMAN 可插拔数据库的恢复

1. 可插拔数据库 T12cPDB1 system 数据文件丢失

这种情况下,容器数据库和其它可插拔数据库不受影响,可正常使用,仅 T12CPDB1 可插拔数据库不可用。
必须从根容器数据库进行恢复。

可插拔数据库的system数据文件丢失可能造成和容器数据库的system数据文件丢失一样严重后果,导致整个容器数据库宕掉,所有可插拔数据库都成为不可用

在这种情况下,需要将容器数据库放在 mount 状态下,恢复可插拔数据库的 system 数据文件。

未来的版本将会有所改善,可插拔数据库 system 数据文件的丢失不影响容器数据库或其他可插拔数据运行。

% rman target /
RMAN> RESTORE DATAFILE 8;
RMAN> RECOVER DATAFILE 8;
RMAN> ALTER PLUGGABLE DATABASE T12CPDB1 OPEN;

2. 可插拔数据库的非系统数据文件丢失,比如USER表空间的10号数据文件丢失

根据实际情况,丢失的数据文件可能已经是 offline 状态,如果没有,必须先 offline:

% rman sys/<pw>@t12cpdb1
RMAN> ALTER DATABASE DATAFILE 10 OFFLINE;
RMAN> RESTORE DATAFILE 10;
RMAN> RECOVER DATAFILE 10;
RMAN> ALTER DATABASE DATAFILE 10 ONLINE;

3. 可插拔数据库的整个表空间的数据文件都丢失

某个表空间丢失,但是可插拔数据库 T12CPDB1 还处于打开状态。

% rman target sys/oracle@t12ccpdb1
RMAN> ALTER TABLESPACE USERS OFFLINE;
RMAN> RESTORE TABLESPACE USERS;
RMAN> RECOVER TABLESPACE USERS;
RMAN> ALTER TABLESPACE USERS ONLINE;

4: 可插拔数据库的所有数据文件都丢失

% rman target sys/<pw>@t12ccdb
RMAN> RESTORE PLUGGABLE DATABASE T12CPDB1;
RMAN> RECOVER PLUGGABLE DATABASE T12CPDB1;
RMAN> ALTER PLUGGABLE DATABASE T12cPDB1 open;

注:

丢失了可插拔数据库并不同于删除了可插拔数据库。

-丢失了可插拔数据库是指因为一些意外情况下的数据库以及文件的误删除或者损坏, 但是它的metadata数据还是存在的。在这种metadata还存在的情况下,从备份中恢复出来是可行的。

-如果是删除了可插拔数据库,这样同样会把metadata从repository中删除。这种情况下做restore就会遇到错误:

RMAN-06813: could not translate pluggable database PDB1

 

另外,如果我们对单个可插拔数据库备份进行备份,这对恢复不同服务器中的单个可插拔数据库或执行时间点恢复是没有用处的。

********我们必须拥有root和可插拔数据库的备份**************

如何生成 AWR 报告和 AWR 基线 (Doc ID 2331572.1)

适用于:

Oracle Database – Enterprise Edition – 版本 10.1.0.2 和更高版本
Oracle Net Services – 版本 10.2.0.5 到 10.2.0.5 [发行版 10.2]
本文档所含信息适用于所有平台

目标

本文概括了如何通过 DBMS_WORKLOAD_REPOSITORY 提供的脚本和功能来创建AWR报告和基线。本文介绍了如何生成各种类型的 AWR 报告和手工创建 AWR 快照,也介绍了一些关于 AWR 基线的内容。

解决方案

AWR 是被 sys 用户拥有的一个存放系统性能指标的集合。
它存放于 SYSAUX 表空间. 默认情况下每60分钟产生一个 AWR 快照并且保留8天, 这样能确保捕获一周的性能指标数据(注意在 10g 中保留期是7天)。

AWR 报告输出一系列指标在两个快照之间的差值,用于研究数据库性能以及其他问题。

 

生成一个最基本的 AWR 报告

如果您拥有了相应的 AWR License 授权,那么您可以通过如下脚本来选择两个您想采用的快照,生成一个 AWR 报告:

$ORACLE_HOME/rdbms/admin/awrrpt.sql

基于不同的原因,通常可以采用默认的设置来产生 AWR 快照,但如果需要更精确的报告,那么可能需要采用更短的比如10-15分钟的快照。

在生成 AWR 报告的过程中,会要求提供产生的 AWR 报告格式(text 或者 html)以及报告的名称。

 

生成多种类型的 AWR 报告

可以通过不同脚本来产生不同类型的 AWR 用于满足不同的需求,所有的 AWR 报告都可以是 HTML 或者 TXT 格式:

  • awrrpt.sql 
    展示一段时间范围两个快照之间的数据库性能指标。
  • awrrpti.sql 
    展示一段时间范围两个快照之间的特定数据库和特定实例的性能指标。
  • awrsqrpt.sql
    展示特定 SQL 在一段时间范围两个快照之间的性能指标,运行这个脚本来检查和诊断一个特定 SQL 的性能问题。
  • awrsqrpi.sql
    展示特定 SQL 在特定数据库和特定实例的一段时间范围内两个快照之间的性能指标。
  • awrddrpt.sql
    用于比较两个指定的时间段之间数据库详细性能指标和配置情况。
  • awrddrpi.sql 
    用于在特定的数据库和特定实例上,比较两个指定的时间段之间的数据库详细性能指标和配置情况。

 

 

AWR 快照

 

如何修改 AWR 快照的设置:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( 
    retention => 43200,        -- 单位是分钟 (43200 = 30 Days)。
                               -- 设置成 NULL 代表保持原来设置。
    interval  => 30);          -- 单位是分钟, 设置成 NULL 代表保持原来设置。
END; 
/

 

手工创建一个 AWR 快照:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 
END; 
/

 

 

按照范围删除 AWR 快照:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
low_snap_id=>40, 
High_snap_id=>80); 
END; 
/

 

 

AWR 基线

 

生成 AWR 基线:

BEGIN 
  DBMS_WORKLOAD_REPOSITORY.create_baseline ( 
    start_snap_id => 10,  
    end_snap_id   => 100, 
    baseline_name => 'AWR First baseline'); 
END; 
/

 

注意:在 11g 中引入了一个新的存储过程 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE 可以制定一个模板来管理在未来时间怎样创建 AWR 基线:

 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

 

“expiration => NULL” 代表这个基线将被永远保留。

 

删除 AWR 基线:

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'AWR First baseline');
END;
/

您也可以删除一个在其他数据库或者旧数据库中创建的 AWR 基线:

 

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',cascade => FALSE, dbid => 3310949047);
END;
/

 

我们也可以基于重复时间周期来制定用于创建和删除 AWR 基线的模板:

 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'MONDAY',
hour_in_day => 9,
duration => 3,
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name_prefix => 'MONDAY_MORNING'
template_name => 'MONDAY_MORNING',
expiration => 30 );
END;
/[Insert code here]

 

这样会在指定的时间’&start_date_time’ 到 ‘&end_date_time’期间的每个周一产生一个 AWR 基线

 

 

AWR 相关的视图

如下系统视图与 AWR 相关:

  • V$ACTIVE_SESSION_HISTORY – 展示每秒采样的 active session history (ASH)。
  • V$METRIC – 展示度量信息。
  • V$METRICNAME – 展示每个度量组的度量信息。
  • V$METRIC_HISTORY – 展示历史度量信息。
  • V$METRICGROUP – 展示所有的度量组。
  • DBA_HIST_ACTIVE_SESS_HISTORY – 展示 active session history 的历史信息。
  • DBA_HIST_BASELINE – 展示 AWR 基线信息。
  • DBA_HIST_DATABASE_INSTANCE – 展示数据库环境信息。
  • DBA_HIST_SNAPSHOT – 展示 AWR 快照信息。
  • DBA_HIST_SQL_PLAN – 展示 SQL 执行计划信息。
  • DBA_HIST_WR_CONTROL – 展示 AWR 设置信息。

 

自动创建 AWR 报告?

Oracle 没有提供自动产生 AWR 报告的功能,AWR 报告的生成是一个手工过程,但是可以通过调度(比如 UNIX 的 crontab)在脚本(比如一些 UNIX 的 bash 脚本)中调用 dbms_workload_repository.awr_report_text 并且传入awr_report_text 的参数来实现自动产生 AWR 报告的目的。可以在互联网上找到这些脚本,但是 Oracle 官方并没有作为产品的一部分来提供这样的脚本。

 

使用 AWR 需要的 License

请注意使用 AWR 需要特定的 License,如果没有 AWR License,可以使用 statspack,参见:

Document 1490798.1 AWR Reporting – Licensing Requirements Clarification

对于 SQL 性能问题如何收集诊断信息 (Doc ID 2293025.1)

适用于

产品:

  • Oracle Database – 企业版 – 版本 6.0.0.0 及更高版本
  • 本文档内容适用于任何平台

需要的权限:

  • 数据库管理员 – 需要访问数据库来收集数据

系统影响:

  • 无 – 以下的收集指令不会对系统有任何影响
哪些会被收集以及为什么?

提供一步步的指导来对 SQL 性能问题收集诊断日志。

Action Plan

诊断文件列表

 1) 使用 SQL Health Check 检查语句                                  [  ]
 2) 使用 SQLT 的 XTRXEC 模式收集诊断信息                              [  ]
 
 如果不能收集 SQLT
 
 2) 手工收集诊断日志                                                 [  ]
   a) 语句的全文 – 完整的有问题的语句的 SQL 文本                        [  ]
   b) 收集有问题的执行计划                                            [  ]
   c) 收集好的执行计划(如果有的话)                                    [  ]
   d) 收集有问题时的 10046 trace                                     [  ]
   e) 收集没问题时的 10046 trace(如果有的话)                         [  ]

 整理并上传诊断日志

 3) 整理并上传诊断日志                                                [  ]

 

性能问题的诊断信息

 

1) 使用 SQL Health Check 检查语句

下载并运行 SQLHC 工具

# sqlplus / as sysdba
SQL> START sqlhc.sql [T|D|N] [SQL_ID]

关于如何使用 SQLHC 收集 SQL 性能问题的信息,请参照下面的文章:

Document 1903134.1 How to Collect Standard Diagnostic Information Using SQLHC for SQL Performance and Incorrect Result Issues

检查 SQLHC 的推荐

检查 SQL Health Check 工具的推荐,如果文件 sqlhc_IDENTIFIER_1_health_check.html 中的内容不能帮我们解决问题,那么上传 SQLHC 输出到 SR。

 

 

2) 使用 SQLT 的 XTRXEC 模式收集诊断信息

除了 SQLHC 的输出,收集这个语句的 SQLT 输出。你需要先下载并安装 SQLT。以 XTRXEC 模式收集 SQLT,这样同时收集到运行时以及优化器的信息。

注意:如果语句不能在一个合理的时间内运行完毕或者语句会修改数据(比如 INSERT/UPDATE/DELETE 语句),那么以 XTRACT(sqltxtract.sql)模式收集,它不会真的去执行语句,而仅仅收集之前执行产生的信息。

XTRXEC 模式收集足够的信息(包括运行这个语句),如下:

使用 SQL*PLUS 以执行有问题语句的应用账号连接,并执行 sqlt/run/sqltxtrxec.sql 脚本,并提供 SQL_ID 或者 HASH_VALUE。

# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE]

如果这个语句有正常的时候(比如在其他的系统上,或者不同版本,或者不同的用户下,或者在不同的参数下),那么也同时收集正常时候的 SQLT 以做对比。

如果如何使用 SQLT 收集 SQL 性能问题的诊断日志,请参考下面的文章:

Document 1683772.1 How to Collect Standard Diagnostic Information Using SQLT for SQL Performance and Incorrect Result Issues

 

 

如果不能收集 SQLT

2) 手工收集诊断日志

SQLT 可以一次运行就收集大部分诊断性能问题所需要的信息,但是如果因为一些原因您不能使用 SQLT,那么您需要手工收集下面的信息。

 

2a) 语句的全文 – 完整的有问题的语句的 SQL 文本

收集问题语句的文本全文

 

2b) 收集有问题的执行计划

在 10.2 及更高版本上,如果语句已经执行了,可以从 library cache 中拿到执行计划(和之前的版本只能收集标准的执行计划相比)。要收集最近一次执行的执行计划,执行下面的命令:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> spool problem_explain.txt

SQL> select * from table(dbms_xplan.display_cursor(null,null, ‘ALL’));

SQL> spool off

关于更多方法,请参考:

Document 1683772.1 How to Obtain a Formatted Explain Plan – Recommended Methods

 

2c) 收集好的执行计划(如果可能的话)

如果有好的执行计划,那么像上面一样收集好的执行计划,spool 到一个”good_explain.txt”的文件中。

 

2d) 收集有问题时候的 10046 trace

在 session level 收集这个语句的 10046 trace:

alter session set tracefile_identifier=’problem_10046′;

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

oradebug setmypid
oradebug tracefile_name

alter session set events ‘10046 trace name context forever,level 12’;

— 在这里执行需要被 trace 语句或者操作 —

select * from dual;
exit;

trace 文件会显示在”oradebug tracefile_name” 命令的输出中。

注意:如果并未使用绑定变量,那么使用 level 8,因为 level 12 会产生更大的日志。

如果语句无法在合理的时间内完成,那么就 trace 一个合理的时间后,就取消它。

如果 session 不能退出,那么可以执行下面的语句关闭 trace:

alter session set events ‘10046 trace name context off’;

关于更多收集 10046 trace 的方式,请参考文档:

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning

 

2e) 收集没问题时候的 10046 trace(如果可能的话)

如果语句有正常的时候,那么也对好的时候收集 10046 trace,使用’good_10046′ 作为 trace 的标识: 

alter session set tracefile_identifier=’good_10046′;

 

 

下一步

3) 整理并上传诊断日志

如果在检查和实施了 SQL Health Check 的推荐后,仍然无法解决问题,那么可以从下面的文档中找到更多分析 SQL 性能问题的方式:

Document 742112.1 Troubleshooting Query Performance Degradation – Recommended Actions

如果无法解决问题,整理并上传诊断日志给技术支持,并且提供一个清晰的问题描述。在大部分情况下这些信息已经足够,但还是有些情况会要收集更多的信息。

SQL Health Check 输出文件:sqlhc_IDENTIFIER_1_health_check.html
语句的 XTRACT 模式的 SQLT 输出
如果不能收集 SQLT,上传

  • 语句的全文
  • 有问题的执行计划
  • 收集好的执行计划(如果有的话)
  • 有问题时候的 10046 trace
  • 没问题时候的 10046 trace(如果有的话)

SQL 自动调优以及 SQL Profile (Doc ID 2331566.1)

适用于:

 

Oracle Database – Enterprise Edition – 版本 10.1.0.2 和更高版本
本文档所含信息适用于所有平台

用途

 

SQL Profiles 是10g 引入的新特性,它通过 DBMS_SQLTUNE 包来管理,您也可以通过 Oracle Enterprise Manager 的自动 SQL 调优流程的功能来管理。这个 FAQ 回答关于 SQL profile 基本的问题,并且提供了一个使用 DBMS_SQLTUNE 包的例子。

 

提问,获得帮助,并分享您对于这篇文档的经验。

 

您是否希望与其他 Oracle 客户、Oracle 员工和业内专家进一步探讨此主题?

 

请点击这里 进入 Oracle 社区(中文).
请点击这里进入My Oracle Support 社区的数据库安装/升级(英文)主页发现更多的话题和讨论。

问题和答案

 

什么是自动 SQL 调优?

 

有时候,因为缺乏相关的统计信息,会导致 SQL 优化器不能产生精准的评估,导致产生了差的执行计划。
一般的办法是用户不得不以手工在程序代码里面添加一些 hint 来指导优化器产生好的执行计划。对于已经封装的应用程序来说,修改代码是不可行的,唯一的办法是给应用开发商提 bug 并等其修改。

 

自动 SQL 调优正好是为这种场景而设计的。自动调优优化器(Automatic Tuning Optimizer)可以为 SQL 文本创建一个 Profile,叫SQL profile,它是由和 sql 语句相关的辅助统计信息所组成。在正常模式下的查询优化器在评估数据量(cardinality),选择率(selectivity)和成本(cost)时,可能因为缺乏足够数量的信息而导致不准,SQL profile 可以通过收集附加的信息,如采样、部分执行等技术来验证评估是否准确,必要时会调整优化器的评估。

在 SQL Profile 工作时,自动调优优化器(Automatic Tuning Optimizer)也会使用 SQL 语句的历史执行信息来设置合适的优化器参数,例如把 OPTIMIZER_MODE 从ALL_ROWS 改成 FIRST_ROWS。

 

此类型的分析输出通常是推荐您接受这个 SQL Profile。

对于一个 SQL Profile,一旦被接受,就会持续的存在数据字典中。SQL Profile 是针对某个特定 SQL 有效的,如果被接受,工作在正常模式下的优化器就会连同 SQL Profile 中的信息和其他正常统计信息一起来生成执行计划。这些可用的附加信息使得针对某个特定 SQL 产生更优的执行计划成为可能,而这无需修改任何应用程序代码。

下面的文档提供更多关于 SQL 调优顾问(SQL Tuning Advisor)的信息。

Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-13
10 Monitoring and Tuning the Database

About the SQL Tuning Advisor

 

 

什么是 SQL Profile?

 

SQL Profile 是一些信息的集合体,存储在数据字典中,使得 SQL 优化器可以为 SQL 语句产生最优的执行计划。SQL Profile 包含自动 SQL 调优期间发现的对产生错误执行计划的评估的纠正信息。这些信息能改善优化器的数据量(cardinality),选择率(selectivity)评估,而这些信息会指引优化器找到更好的执行计划。

一个 SQL profile 不包含某个特定执行计划的相关联信息,相反,优化器当选择执行计划的时候参考如下信息数据:

  • 环境信息,包括数据库配置,绑定变量值,统计信息,数据集等等
  • 存储在 SQL profile 中的补充信息

需要提醒的是,SQL Profile 不会像存储纲要(stored outlines)那样固定一个 SQL 的执行计划,当表的数据量增长或者有索引创建或删除时,使用了 SQL Profile 的执行计划仍会及时调整,也就是说,当数据分布或访问路径变化后,存储在 SQL Profile 中的相关信息仍然被参考。当然,经过相当长时间后,它的内容可能变得陈旧过时,需要重新生成。您可以通过再次运行自动 SQL 调优(Automatic SQL Tuning)来重新生成这个 SQL 的 Profile。

 

SQL Profile 的范围如何控制?

 

SQL Profile 的范围可以通过 CATEGORY 属性来控制,这个属性决定了哪个 sessoin 可以使用这个 profile,您可以通过查看 DBA_SQL_PROFILES 视图的 CATEGORY 字段来了解相关信息。

select category,name from dba_sql_profiles;

默认情况下,所有的 profiles 都被指定为 DEFAULT category,这就意味着所有 session 当初始化参数 SQLTUNE_CATEGORY 设置 DEFAULT 的时候都可以用这个 profile。

通过修改 SQL profile 的 category 属性,你可以指定哪个 session 受 profile 影响。例如,设置 category 为 DEV,那么 session 级设置了初始化参数 SQLTUNE_CATEGORY 为 DEV 的那些 session 可以使用这个 profile,其他 session 则不能使用这个 SQL profile,执行计划当然也不会受这个 SQL profile 影响。这个特性可以让您用来在某个限制环境上提前测试某个 SQL profile 而避免影响其他 session。

 

SQL Profile 适用什么语句??

 

SELECT 语句
UPDATE 语句
INSERT 语句(必须包含select语句)
DELETE 语句
CREATE TABLE 语句(必须包含 AS SELECT 子句)
MERGE 语句(update 或者 insert 操作)

 

如何管理 SQL Profile?

SQL Profiles 可以通过 Oracle 企业管理器(Enterprise Manager)的自动 SQL 调优流程部分来管理它,或者通过 DBMS_SQLTUNE 包。

 

使用企业管理器:

  1. 在 Performance 页面, 点击 Top Activity。
    Top Activity 页面出现。
  2. 在 Top SQL 下, 点击使用 SQL profile 的 SQL 语句的 SQL ID 链接。
    SQL Details 页面显示出来。
  3. 点击 Plan Control 栏。
    SQL Profiles 和 Outlines 部分会显示 SQL Profile 的列表。
  4. 选择想要管理的 SQL profile。
    执行下面的任意操作 one of the following:

    • 要启用一个当前被禁用的 SQL profile,点击 Disable/Enable。
    • 要禁用一个当前被启用的 SQL profile,点击 Disable/Enable。
    • 要删除一个 SQL profile,点击 Delete.
  5. Confirmation 页面显示出来。
    点击 Yes 来确认,或者 No 取消刚才的操作。

 

使用 DBMS_SQLTUNE 包:

若是用 SQL Profiles APIs,您需要有 CREATE ANY SQL_PROFILE,DROP ANY SQL_PROFILE,ALTER ANY SQL_PROFILE 等系统权限。

 

接受 SQL Profile

使用 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 存储过程来接受一个 SQL Tuning Advisor 推荐的 SQL Profile。这会创建 SQL Profile 并存储在数据字典中。

DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘my_sql_tuning_task’,
name => ‘my_sql_profile’);
END;

my_sql_tuning_task 是一个 SQL 调优任务的名字,您可以通过 DBA_SQL_PROFILES 来查看一个 SQL Profile 的相关信息。

 

修改 SQL Profile

您可以通过 ALTER_SQL_PROFILE 来修改一个已知 SQL Profile 的状态,名字,描述信息和 CATEGORY 属性。

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => ‘my_sql_profile’,
attribute_name => ‘STATUS’,
value => ‘DISABLED’);
END;
/

这个例子中,my_sql_profile 是您想要修改的 SQL Profile 名字。
状态被设置成 disabled,意思是 SQL Profile 将不会被 SQL 使用。

 

删除 SQL Profile

可以通过 DROP_SQL_PROFILE 来删除 SQL Profile。

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘my_sql_profile’);
end;
/

 

 

样例:

SESSION 1 — SCOTT

创建表,插入数据,创建索引并收集统计信息。
执行带有 no_index hint 的 SQL,全表扫描将会被使用:

SQL> create table test (n number );
Table created.
SQL> declare
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index test_idx on test(n);
Index created.
SQL> exec dbms_stats.gather_table_stats(”,’TEST’);
PL/SQL procedure successfully completed.
set autotrace on
select /*+ no_index(test test_idx) */ * from test where n=1;

 
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

SESSION 2 — SYS

创建并执行调优任务并查询它的输出结果。
然后接受一个推荐的 SQL Profile

declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := ‘select /*+ no_index(test test_idx) */ * from test where n=1’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => ‘SCOTT’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task_2’,
description => ‘Task to tune a query on a specified table’);
end;
/
PL/SQL procedure successfully completed.
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_2’);
end;
/
PL/SQL procedure successfully completed.
set long 10000
set longchunksize 1000
set linesize 100
set heading off
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_2’) from DUAL;
set heading on

Output:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_2
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 09/24/2012 12:36:44
Completed at       : 09/24/2012 12:36:49
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan  With SQL Profile  % Improved
-------------  ----------------  ----------
Completion Status:            COMPLETE          COMPLETE
Elapsed Time (s):             .001004           .000331      67.03 %
CPU Time (s):                    .001                 0        100 %
User I/O Time (s):                  0                 0
Buffer Gets:                       22                 2       90.9 %
Physical Read Requests:             0                 0
Physical Write Requests:            0                 0
Physical Read Bytes:                0                 0
Physical Write Bytes:               0                 0
Rows Processed:                     1                 1
Fetches:                            1                 1
Executions:                         1                 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
2- Using SQL Profile
--------------------
Plan hash value: 1416057887
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
-------------------------------------------------------------------------------

 

DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘my_sql_tuning_task_2’,
name => ‘my_sql_profile’);
end;
/PL/SQL procedure successfully completed.

SESSION 1 — SCOTT

再次运行 SQL
可以看到即便是有 no_index 暗示,索引仍然被使用了
注意:执行计划里面可以看到使用了 my_sql_profile

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan                                                                
-------------------------------------------------------------------------     
Plan hash value: 1416057887                                                   
----------------------------------------------------------------------------- 
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
1 - access("N"=1)                                                          
---------------                                                               
Note                                                                          
-----                                                                         
- SQL profile "my_sql_profile" used for this statement                     

 

如何获取每个调优集的报告?

 

SELECT ‘SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.*
FROM TABLE(DBMS_SQLTUNE.select_sqlset (”’||name||”’)) ss, dba_sqlset d WHERE d.name=”’||name||”’;’
FROM dba_sqlset d
ORDER BY d.last_modified DESC.

Solix优化Oracle E-Business Suite性能

Solix优化Oracle E-Business Suite性能

12c vs 11g x$messages

ACMS initialization ACMS
ADR PDB Auto Purge Task MMON
ADR Space Management Statistics Flush MMON
ARCH initialization ARC*
ASH Progressive Flusher (KEWA) MMON
ASM Audittrail cleanup MMON
AWR PDB Auto Flush Task MMON
AWR PDB Auto Purge Task MMON
AWR Raw Metrics Capture GEN1
Action-Based process Test GEN0
Archiver disconnect ARCH
Auxilary ipc finish gen0 action GEN0
Auxilary ipc init gen0 action GEN0
Auxilary ipc intr gen0 action GEN0
Auxilary ipc message gen0 action GEN0
Auxilary ipc timedout gen0 action GEN0
BA container GEN0 action GEN0
CLI AutoPartition MMON
CLI Create All Slave Tasks GEN0
CLI Create One Slave Task GEN0
Check for async in-memory job messages CJQ0
Cleanup of unpinned KGL handles MMON
Column-Level Statistics flush MMON
DBRM ADG in-memory state refresh DBRM
DBWO timeout kcbifc DBW0
DBWR write buffers DBW*|BW*
DDE Periodic Dump Scheduler MMON
DMON do critical instance eval and registration DMON
DSKM fini DSKM
DSKM init DSKM
DSKM procures HCA loadavg and computes offloaded write thresh DSKM
DTrace based Kernel IO  Outlier  Processing GEN0
Direct NFSv4 RENEW lease operation GEN0
Execute on-demand tuning task (KESTS) MMON
Free java patching locks LCK0
GEN0 Master Check GEN0
Get java patching locks LCK0
Hang Manager parameters GEN0
ILM check MMON
ILM cleanup MMON
ILM row access flush MMON
ILM segment access flush MMON
IMCO ADO action IMCO
IMCO FastStart Defer Write Scheduler IMCO
IMCO Trickle Repopulate IMCO
IMCO action IMCO
IMCO cycle action IMCO
IMCO global dictionary action IMCO
IMCO worker action IMCO
Inactive Account Time Job GEN0
Index usage tracking statistics flush MMON
Initiate KSBCITST TEST
KEWR SlavePool Test MMON Main MMON
KJBFP PBR logfile scan LMFC
KJBFP PBR recovery LMFC
KJBFP PBR writer main LMFC
KJBFP increment PRI LMFC
KJBFP pbr logFile CLose *
KJFM update process heartbeat LCK*|DIA*|LGWR|CKPT|DBRM|IPC0
KQLM interrupt action LCK1
KQLM invalidation instance lock operation LCK1
KSB GEN1 init GEN1
KSGL initialize service IPC0
KSGL mount in IPC0 IPC0
KSGL node exit IPC0
KSGL node join IPC0
KSGL notify IPC0 IPC0
KSGL timeout IPC0
KSIPC Grp Refresh action IPC0
KSIPC MGA Segment Check IPC0
KSIPC finish action IPC0
KSIPC initialize server IPC0
KSIPC interrupt action IPC0
KSIPC msg action IPC0
KSIPC reconfig action IPC0
KSIPC shutdown action IPC0
KSIPC timeout action IPC0
KSM SGA slaves spawn GEN0
KSRMA RMA OP IPC0
KSRMA Recovery Log Allocation IPC0
KSRMA mount IPC0
KSU GUID MAC Address update GEN0
LGWR flush workers LGWR
LGWR initialization LGWR
MMON request to purge LTXID history table MMON
Monitor initialization TMON
Monitor wakeup TMON
Multi procs per DTP UTMU
Network Server forced NSS*
Network Server shutdown NSS*
PDB SGA init GEN0
PDB close abort GEN0
PMON notify IPC0 of process failure IPC0
PQ: Adjust Slave Pool MMON
Payload action to BG RMON
Process new DBs that join ASM locally DIA*
RMON BG Driver RMON
RMON Init Action RMON
RTTD initialization RTTD
Real-Time ADDM Trigger MMON
Redo writer quiesce IMC on standby LGWR
Refresh active service cache MMON
Report Capture Daemon MMON
Report Capture Test (KERPI) MMON
SGA deferred allocated granules Initialization MMAN
SGA deferred allocated granules move MMAN
SMON_SCN_TIME Copy to PDBs MMON
SQL Memory Management Calculation DBRM
Spawn processes on behalf of someone else GEN0
Standby media recovery info cleanup LGWR
Suspended session cleanup GEN0
Sweep PL/SQL incidents MMON
Switchover/PDB relocate message channel subscribe LGWR
TPZ initialization TPZ*
Test Driver wakeup RTTD
Test Process wakeup TPZ*
Timeout interrupt action RBAL
Triton Session Cleanup MMON
UMF Auto Task Pool Queue Server MMON
UMF Auto Task Pool Scheduler MMON
UTS Async Dump GEN0
Volume Resource Action GEN0
Wait event outlier detection GEN0
XStream timeout action GEN0
acquire enq during pdbopen by HARIM DBW0
action for buddy instance RMS0
action to cleanup buddy instance context RMS0
check for KJCI cross-instance requests *
cleaning up workload information for optimizer MMON
clear the dependent scn DBRM
dblink logon table cleanup MMON
enter / exit graph test specified wait *
event nfy timeout action GEN0
event outlier dump info. GEN0
extend quarantine area GEN0
flushing workload information for optimizer MMON
free PX memory chunks in background PXMN
get/release open thread enqueue DBW*|BW*
init function for LCK1 *
initiate block repair GEN0
kcb DW object cooling GEN0
kcbz background redodump GEN0
kcbz update TSE bh CKPT
kill client GEN0
ksim cache line update LCK0
ksim instance group membership notifier *
kxfp remote slave spawn recv function PXMN
light-weight checks for optimizer statistics advisor MMON
mira CKPT channel CKPT
mount/dismount all db files DBW*|BW*
pdb event stats action GEN0
periodic PDB tasks GEN0
pmon dtp init PMON|CLMN
prespawn clean check GEN0
prespawn init check GEN0
prespawn timeout check GEN0
register to node local process group RBAL
shutdown RMON process RMON
sync PDB DBW0
threshold reloading MMON
unit test DBW0

oracle如何判定统计信息陈旧

本文原始地址:https://www.askmaclean.com/?p=18742

注意自动收集统计信息是从10g开始的,10g以前版本默认不自动收集统计信息。

对于自动收集统计信息而言需要知道统计信息是否陈旧stale ,判定陈旧的标准是对应的表上的数据修改超过10%(删除或插入或更新10%或以上数据行)。 这里oracle是如何知道修改超过10%的?

 

SGA的shared pool存有SQL的statistics情况,对应的有SQL处理的行数,SMON进程定期将这些信息刷到表SYS.MON_MODS$基表中(参考拙作: https://www.askmaclean.com/archives/smon-flush-dml-statistics-mon-mods.html):SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$基表中(SMON flush every 15 minutes to SYS.MON_MODS$),
同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中,并清空原MON_MODS$中的数据。
MON_MODS_ALL$作为dba_tab_modifications视图的数据来源,起到辅助统计信息收集的作用,详见拙作<Does GATHER_STATS_JOB gather all objects’ stats every time?>

这样基于之前的统计信息中的表的行数(dba_tables.num_rows),对比 MON_MODS_ALL$(dba_tab_modifications)中的update、delete、insert、truncate信息就可以知道该表从上一次收集统计信息到现在做了多少百分比的修改,若该百分比超过10%则判定为stale陈旧,否则为不陈旧。陈旧的统计信息会在自动收集统计信息时再次被收集。

 

 

DOP degree of parallelism的设计算法 ​​​​

直击oracle内核代码算法,对于11g以后的自动Parallelism算法一般只能用10053 trace来研究其算法。这里通过直接查看oracle源码设计文档,我们可以得到DOP degree of parallelism的设计算法

 

8358fa4fgy1fcr7ivo81lj20zq0kijwk

12CR2 vs 11gR2 新增optimizer 参数列表

_optimizer_adaptive_plan_control 0
_optimizer_adaptive_plans_continuous FALSE
_optimizer_adaptive_plans_iterative FALSE
_optimizer_adaptive_random_seed 0
_optimizer_ads_for_pq FALSE
_optimizer_ads_result_cache_life 3600
_optimizer_ads_spd_cache_owner_limit 64
_optimizer_ads_use_partial_results TRUE
_optimizer_ads_use_spd_cache TRUE
_optimizer_aggr_groupby_elim TRUE
_optimizer_ansi_join_lateral_enhance TRUE
_optimizer_ansi_rearchitecture TRUE
_optimizer_band_join_aware TRUE
_optimizer_batch_table_access_by_rowid TRUE
_optimizer_bushy_cost_factor 100
_optimizer_bushy_fact_dim_ratio 20
_optimizer_bushy_fact_min_size 100000
_optimizer_bushy_join off
_optimizer_cbqt_or_expansion ON
_optimizer_cluster_by_rowid TRUE
_optimizer_cluster_by_rowid_batch_size 100
_optimizer_cluster_by_rowid_batched TRUE
_optimizer_cluster_by_rowid_control 129
_optimizer_control_shard_qry_processing 65534
_optimizer_cube_join_enabled TRUE
_optimizer_db_blocks_buffers 0
_optimizer_dsdir_usage_control 0
_optimizer_eliminate_subquery TRUE
_optimizer_enable_plsql_stats TRUE
_optimizer_enhanced_join_elimination TRUE
_optimizer_gather_feedback TRUE
_optimizer_gather_stats_on_load TRUE
_optimizer_generate_ptf_implied_preds TRUE
_optimizer_generate_transitive_pred TRUE
_optimizer_hll_entry 4096
_optimizer_hybrid_fpwj_enabled TRUE
_optimizer_inmemory_access_path TRUE
_optimizer_inmemory_autodop TRUE
_optimizer_inmemory_bloom_filter TRUE
_optimizer_inmemory_capture_stored_stats TRUE
_optimizer_inmemory_cluster_aware_dop TRUE
_optimizer_inmemory_gen_pushable_preds TRUE
_optimizer_inmemory_minmax_pruning TRUE
_optimizer_inmemory_pruning_ratio_rows 100
_optimizer_inmemory_quotient 0
_optimizer_inmemory_table_expansion TRUE
_optimizer_inmemory_use_stored_stats AUTO
_optimizer_interleave_or_expansion TRUE
_optimizer_key_vector_aggr_factor 75
_optimizer_key_vector_pruning_enabled TRUE
_optimizer_multi_table_outerjoin TRUE
_optimizer_multicol_join_elimination TRUE
_optimizer_nlj_hj_adaptive_join TRUE
_optimizer_null_accepting_semijoin TRUE
_optimizer_partial_join_eval TRUE
_optimizer_performance_feedback OFF
_optimizer_proc_rate_level BASIC
_optimizer_proc_rate_source DEFAULT
_optimizer_reduce_groupby_key TRUE
_optimizer_strans_adaptive_pruning TRUE
_optimizer_synopsis_min_size 2
_optimizer_undo_cost_change 12.2.0.1
_optimizer_union_all_gsets TRUE
_optimizer_unnest_scalar_sq TRUE
_optimizer_use_feedback_for_join FALSE
_optimizer_use_gtt_session_stats TRUE
_optimizer_use_histograms TRUE
_optimizer_use_table_scanrate HADOOP_ONLY
_optimizer_use_xt_rowid TRUE
_optimizer_vector_base_dim_fact_factor 200
_optimizer_vector_cost_adj 100
_optimizer_vector_fact_dim_ratio 10
_optimizer_vector_min_fact_rows 10000000
_optimizer_vector_transformation TRUE
optimizer_adaptive_plans TRUE
optimizer_adaptive_reporting_only FALSE
optimizer_adaptive_statistics FALSE
optimizer_features_enable 12.2.0.1
optimizer_inmemory_aware TRUE

金蝶EAS HR系统后台Oracle备份恢复维护方案

很多金蝶EAS或HR系统的后台 ORACLE 数据库都处于无备份且未打开归档的状态,由于一般企业对于EAS或HR系统的后台数据库没有专职的DBA维护,所以实际也不推荐真的开归档并基于归档做备份维护,因为这样做会多一点维护的工作量(如果你是大企业 那么理应打开归档并维护归档以满足自身的备份恢复要求,例如大企业要求数据能回溯到一个月前,那么有归档才是合适的。)

对于中小企业使用金蝶EAS或HR系统而言,视乎系统后台ORACLE数据库的大小和可容忍的数据丢失时间,可以自主选择逻辑备份周期。这里说的逻辑备份主要是指ORACLE自带的EXPDP 数据泵导出工具,一般来说目前的金蝶EAS/HR用户的后台ORACLE数据库都是大于版本9i的版本(例如10g和11g等),则都可以选择使用EXPDP,其好处是逻辑导出备份要比传统export/import工具的exp速度上要快很多,且其导出格式也比exp周全。

 

一般来说中小企业大多可以容忍一天到半天的数据丢失,这部分的数据丢失一般可以基于财务或人力部分的同事通过手工补录来弥补,则对于这种场景下可以规划每12小时或24小时做一次逻辑备份:注意逻辑备份的频率就决定了数据丢失的量,因为逻辑备份是就是一次对数据的全量备份,每一次逻辑备份都是对现有数据的全量备份;所以周一中午12点备份的数据,在周二上午12点备份前的周一下午6点发生了数据库损坏/毁灭等问题,则周一中午到下午6点间产生的数据将可能丢失。

 

对于逻辑备份而言,其实维护的命令很简单:

expdp   DIRECTORY=(备份存放的目录,需要在ORACLE内以CREATE DIRECTORY创建)    dumpfile=(备份的文件名,会放在DIRECTORY下)  schemas=(EAS或HR所在的Schema)   logfile=(日志的文件名,会放在DIRECTORY下) parallel=2

例如 备份的目录叫DMP,EAS或HR所在schema伟EAS1和SHR1 则

expdp   DIRECTORY=DMP dumpfile=kingdee_20170315.dmp schemas=eas1,shr1 logfile=exp_20170315.log  parallel=2

 

对于Windows可以使用计划任务,对于Unix/Linux可以使用crontab自动调度以上备份脚本;另脚本内一般要考虑删除多久之前的备份文件。

此外要考虑  逻辑备份一般都是备份在数据库所在服务器,若服务器出现主机故障则恢复将较为麻烦,因此一般会考虑则EXPDP逻辑备份后FTP或COPY到其他远程服务器的磁盘上,以便冗余备份。

例如在 Linux下 定期备份并传到到FTP服务器上:

 

#!/bin/sh 
ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1 
export ORACLE_HOME 
export PATH=$ORACLE_HOME/bin:$PATH 
ORACLE_SID=orcl; export ORACLE_SID 
HOST='IP地址' 
USER='ftpuser' 
PASSWD='password' 
expdp eas/SHITANRUANJIAN DIRECTORY=backdir DUMPFILE=eas-$(date +%Y%m%d%H) VERSION=10.2 LOGFILE=easLOG-$(date +%Y%m%d%H).log 
zip -r /home/app/oracle/admin/orcl/dpdump/eas-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/eas-$(date +%Y%m%d%H).dmp 
cat /home/app/oracle/admin/orcl/dpdump/easLOG-$(date +%Y%m%d%H).log | mutt -s "eas Backup" eas@tiger.com 
cd /home/app/oracle/admin/orcl/dpdump 
ftp -n -v $HOST << EOT 
binary 
user $USER $PASSWD 
prompt 
put eas-$(date +%Y%m%d%H).zip 
bye 
EOT 
find /home/app/oracle/admin/orcl/dpdump -name "eas*" -mtime +10 -type f -exec rm {} ;

如上脚本首先备份EAS用户并FTP到备份服务器上,最后删除10天前的备份。

 

如上描述了对金蝶EAS的备份方案,之后谈一下恢复方案;使用EXPDP的备份方案后,若出现大规模的数据库问题 例如ORACLE数据库打不开或出现大量坏块或ORACLE
所在服务器出现故障无法启动。则可以在必要的可用服务器上安装与之前ORACLE版本一样的ORACLE数据库软件,之后使用DBCA工具创建新的数据库,最后
使用IMPDP工具将之前的备份导入数据库中。

 

导入命令也十分简单:

 

impdp DIRECTORY=(备份所存放的目录,需要在ORACLE内以CREATE DIRECTORY创建)    dumpfile=(备份的文件名)  logfile=(日志的文件名,会放在DIRECTORY下) parallel=2 full=y

 

其他的一些恢复场景下的恢复方案:

由于 EAS/HR 运行在 NOARCHIVELOG 模式下,针对不同的故障场景下可以采取以下恢复策略:

1. SPFILE 初始化参数文件物理损坏:

从 PFILE 中重新生成一个 SPFILE
CREATE SPFILE=’’ FROM PFILE=’’
或者从备份中恢复
RESTORE SPFILE TO PFILE=’’;
RESTORE SPFILE;
RESTORE SPFILE FROM AUTOBACKUP;

2. Control File 文件部分物理损坏:

关闭数据库,将其它完整的控制文件复制到已损坏的控制文件,重启数据库

3. Control File 文件全部物理损坏 在数据库关闭时或者出于 mounted 时,即数据库处于一致性状态时 control file 全部物 理损坏,则执行 create controlfile 命令重新创建控制文件,如果处于非一致性状态时 损坏,则通过最新的物理备份全库恢复,然后追加备份发生时到故障点之间的数据。

4. 非 CURRENT 在线日志文件物理损坏 执行 alter database clear logfile 语句重新创建该损坏的非 CURRENT 在线日志文件

5. CURRENT 在线日志文件物理损坏

SQL> startup mount

SQL> recover database until cancel; #(cancel immediately)

SQL> alter database open resetlogs;

 

6. SYSTEM,SYSAUX,UNDO 表空间物理文件物理损坏 :

通过最新的逻辑备份全库恢复

 

7. TEMP 表空将物理文件物理损坏

执行"alter database”重新创建 temp 物理文件
8. 用户数据表空间物理文件物理损坏

通过最新的逻辑备份全库恢复
9. 用户索引表空间物理文件物理损坏

重新创建用户索引表空间,然后 rebuilt 相应的索引
10. 物理快损坏

如果该损坏块为用户索引,则 rebuilt 相应的索引

如果该损坏块为用户表,则通过设置 event 将没有损坏的数据正常读出来(损坏块中的数据库会丢失)或者通过关键表的逻辑备份恢复
如果该损坏块为系统数据,通过最新的逻辑备份全库恢复
11. 用户表数据不正常更新(INSERT、UPDATE、DELETE)
可以通过关键表的逻辑备份恢复,或者相应的 flashback 技术恢复
12. 用户表不正常删除(drop)
可以通过关键表的逻辑备份恢复,或者 flashback drop 技术恢复

 

如果上述方案还不能搞定你的EAS/HR后台ORACLE数据库的问题那么也可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

 

 

沪ICP备14014813号

沪公网安备 31010802001379号