11g

11g DEFAULT_MAINTENANCE_PLAN 总结

June 18, 2012 11g, oracle No comments

查看数据仓库的awr报告发现以下问题 顺便总结一下之前碰到的一类问题

CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
13,664.95 12 1,138.75 8.99 15,656.35 87.28 11.83 3a1wvmtvsdvqv PL/SQL Developer call pkg_rpt_union.pro_rpt_uni…
10,474.67 5 2,094.93 6.89 14,773.14 70.90 28.90 6mcpb06rctk0x DBMS_SCHEDULER call dbms_space.auto_space_adv…
9,215.02 5 1,843.00 6.06 17,031.19 54.11 45.65 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas…
8,881.02 2,737 3.24 5.84 11,250.82 78.94 21.14 9q7k9nbpvk8pv DBMS_SCHEDULER SELECT NVL(SUM(TIME_WAITED/100…
8,555.57 96 89.12 5.63 22,080.53 38.75 4.02 7ctdtkwpbrj4k sqlplus@racdb02 (TNS V1-V3) BEGIN edw1_user.pkg_rpt_today….
7,474.88 65,717 0.11 4.92 7,538.15 99.16 0.08 6m4s9pjrydbxt oracle@tracker-db2 (TNS V1-V3)

dbms_stats.gather_databas dbms_space.auto_space_advisor 耗费了系统大量的IO 并且耗费了大量的CPU 这可能引起cpu quantum 等待 相信不少TX遇到过 resmgr:cpu quantum等待 而时间点恰恰是22:00整,在11g 中oracle对automatic maintenance做出了调整 :


The Wait Event: resmgr: cpu quantum

DBRM allocates CPU resources by maintaining an execution queue similar to the way the operating
system’s scheduler queues processes for their turn on the CPU. The time a session spends waiting in this
execution queue is assigned the wait event resmgr: cpu quantum. A CPU quantum is the unit of CPU
time (fraction of CPU) that Resource Manager uses for allocating CPU to consumer groups. This event
occurs when Resource Manager is enabled and is actively throttling CPU consumption. Increasing the
CPU allocation for a session’s consumer group will reduce the occurrence of this wait event and increase
the amount of CPU time allocated to all sessions in that group. For example, the CPU quantum wait
events may be reduced for the APPS resource group (currently 70% at level 1) by increasing the group’s
CPU allocation to 80%.

In Oracle 11 the automatic maintenance jobs (Space Advisor, Gather Stats, Tuning Advisor) are by default run using the Resource Manager. The default settings give these tasks up to 25% of the CPU during their scheduler windows.
If you prefer these tasks to always be taking a “back seat” compared to any other jobs running on the system it may be worth slightly adjusting the resource allocation of the DEFAULT_MAINTENANCE_PLAN.

In the DEFAULT_MAINTENANCE_PLAN (the default version) we have:
Level 1: SYS_GROUP: 100%
Level 2: ORA$AUTOTASK_SUB_PLAN 25% / ORA$DIAGNOSTICS: 5% / OTHER_GROUPS 70%
Level 3+: none

This means that OTHER_GROUPS may only get 70% of the CPU (unless the other plans are not using up their quota, in which case some of the spare quota will be available to OTHER_GROUPS). To ensure that the auto maintenance tasks only get resources when OTHER_GROUPS don’t need them you can simply change the plan directive for the DEFAULT_MAINTENANCE_PLAN

并且我们可以看到一个有趣的现象 在低版本升级到11g的库中 WEEKNIGHT_WINDOW,WEEKEND_WINDOW 也是作为了 DEFAULT_MAINTENANCE_PLAN的子windows,在Oracle 11g中,有7个预定义维护时间窗,5个针对每个工作日的,两个针对周末的(周六和周日),七个预定义时间窗是按一周的每天命名的,所以即便默认开启了WEEKNIGHT_WINDOW,WEEKEND_WINDOW oracle依然会按照七个预定义时间窗一次执行
我们可以从一套10g升级到11g的库中得到证实:

9i/10g->11g

SQL> select window_name,resource_plan,comments from dba_scheduler_windows ;

WINDOW_NAME RESOURCE_PLAN COMMENTS
—————————— —————————— ————————————————————
WEEKNIGHT_WINDOW DEFAULT_MAINTENANCE_PLAN Weeknight window for maintenance task
WEEKEND_WINDOW DEFAULT_MAINTENANCE_PLAN Weekend window for maintenance task
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Monday window for maintenance tasks
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Tuesday window for maintenance tasks
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Wednesday window for maintenance tasks
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Thursday window for maintenance tasks
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Friday window for maintenance tasks
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Saturday window for maintenance tasks
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Sunday window for maintenance tasks

SQL> SELECT LOG_ID, TO_CHAR(LOG_DATE, ‘MM/DD/YYYY’), WINDOW_NAME, OPERATION
FROM DBA_SCHEDULER_WINDOW_LOG order by 2;

LOG_ID TO_CHAR(LO WINDOW_NAME OPERATION
———- ———- ———————————————————— ——————————
19763 06/05/2012 TUESDAY_WINDOW OPEN
19756 06/05/2012 MONDAY_WINDOW CLOSE
19798 06/06/2012 WEDNESDAY_WINDOW OPEN
19784 06/06/2012 TUESDAY_WINDOW CLOSE
19817 06/07/2012 WEDNESDAY_WINDOW CLOSE
19824 06/07/2012 THURSDAY_WINDOW OPEN
19845 06/08/2012 THURSDAY_WINDOW CLOSE
19850 06/08/2012 FRIDAY_WINDOW OPEN
19871 06/09/2012 FRIDAY_WINDOW CLOSE
19876 06/09/2012 SATURDAY_WINDOW OPEN
19903 06/10/2012 SUNDAY_WINDOW OPEN

LOG_ID TO_CHAR(LO WINDOW_NAME OPERATION
———- ———- ———————————————————— ——————————
19900 06/10/2012 SATURDAY_WINDOW CLOSE
19929 06/11/2012 MONDAY_WINDOW OPEN
19926 06/11/2012 SUNDAY_WINDOW CLOSE
19955 06/12/2012 TUESDAY_WINDOW OPEN
19948 06/12/2012 MONDAY_WINDOW CLOSE
19981 06/13/2012 WEDNESDAY_WINDOW OPEN
19976 06/13/2012 TUESDAY_WINDOW CLOSE
20007 06/14/2012 THURSDAY_WINDOW OPEN
20000 06/14/2012 WEDNESDAY_WINDOW CLOSE
20030 06/15/2012 THURSDAY_WINDOW CLOSE
20033 06/15/2012 FRIDAY_WINDOW OPEN

LOG_ID TO_CHAR(LO WINDOW_NAME OPERATION
———- ———- ———————————————————— ——————————
20052 06/16/2012 FRIDAY_WINDOW CLOSE
20059 06/16/2012 SATURDAY_WINDOW OPEN
20083 06/17/2012 SATURDAY_WINDOW CLOSE
20086 06/17/2012 SUNDAY_WINDOW OPEN

oracle按照 MONDAY_WINDOW->SUNDAY_WINDOW 一次执行 而在一套新装的11g中 Weeknight window and Weekend window are just for compatibility only

11g

SQL> select window_name,resource_plan,comments from dba_scheduler_windows ;

WINDOW_NAME RESOURCE_PLAN COMMENTS
—————————— —————————— ————————————————————
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Monday window for maintenance tasks
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Tuesday window for maintenance tasks
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Wednesday window for maintenance tasks
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Thursday window for maintenance tasks
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Friday window for maintenance tasks
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Saturday window for maintenance tasks
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Sunday window for maintenance tasks
WEEKNIGHT_WINDOW Weeknight window – for compatibility only
WEEKEND_WINDOW Weekend window – for compatibility only

在系统出现大量的resmgr:cpu quantum等待时 我们可以采取以下方法

关闭 Resource Manager plan 这个方法xifenfei同学写过一篇故障处理过程 可以参照这篇metalink

对于DBRM来说 oracle通过调用DEFAULT_MAINTENANCE_PLAN来控制系统资源的分配,一般来说通过增加consumer group的最大资源可以减少调度过程
中的资源争用。其实我们同样可以通过disable auto task的方法来避免这个问题。

[oracle@db-41 ~]$ ora params _resource_manager_always_on

NAME VALUE DESCRIPTION
——————————————— ——————– ———————————————————————-
_io_resource_manager_always_on FALSE io resource manager always on
_resource_manager_always_on TRUE enable the resource manager always

对于这个问题 其实有一个更折中的方法 关闭 WEEKNIGHT_WINDOW,WEEKEND_WINDOW 禁止 Space Advisor,Tuning Advisor 但是Gather Stats还是建议不要禁止,对于很多系统而言 准确的统计信息往往更能辅助CBO做出正确的判断 这里给个例子 :

BEGIN
dbms_auto_task_admin.disable(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/

BEGIN
dbms_auto_task_admin.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/

begin
sys.dbms_scheduler.set_attribute(name => ‘SYS.xxx_WINDOW’, attribute => ‘repeat_interval’, value => ‘Freq=daily;ByDay=TUE;ByHour=2;ByMinute=0;BySecond=0’);
sys.dbms_scheduler.set_attribute(name => ‘SYS.xxx_WINDOW’, attribute => ‘duration’, value => ‘0 04:00:00’);
end;
/

修改为凌晨2点到6点收集统计信息 同时禁止 sql tuning advisor ,auto space advisor

11.2.0.3 datapump got ORA-38500

February 23, 2012 maintain, oracle No comments

从10.2.0.5的库导入一张表的增量数据到11.2.0.3 impdp报出如下错误,对比两边表结构一致,tablespace有剩余空间 报错如下:


[oracle@localhost ~]$ impdp \’\/ as sysdba \’ directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append

Import: Release 11.2.0.3.0 – Production on Thu Feb 23 09:44:49 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: “/******** AS SYSDBA” directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “USER_DATA2″.”SO” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
ORA-38500: Unsupported operation: Oracle XML DB not present

reference:

Data Pump Import Fails With ORA-942 ORA-06512 And ORA-38500 [ID 1350414.1]

Master Note for Oracle XML Database (XDB) Install / Deinstall [ID 1292089.1]

11.2 – XDB Removal and Reinstall

XDB Removal

The catnoqm.sql script drops XDB.

SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;

XDB Installation

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D

For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

SQL> select owner, object_name, object_type, status
from dba_objects
where status = ‘INVALID’
and owner = ‘XDB’; 2 3 4

no rows selected

SQL> select comp_name, version, status
from dba_registry
where comp_id = ‘XDB’; 2 3

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ———————————
Oracle XML Database
11.2.0.3.0 VALID

1 row selected.

SQL> !

重新导入:

[oracle@localhost ~]$ impdp \’\/ as sysdba \’ directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append

Import: Release 11.2.0.3.0 – Production on Thu Feb 23 09:44:49 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: “/******** AS SYSDBA” directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “USER_DATA2″.”SO” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “USER_DATA2″.”SO” 225.1 MB 428745 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at 10:23:05

[转] 11g Release 2 RMAN Backup Compression

December 16, 2011 11g, backup, oracle No comments

11g Release 2 RMAN Backup Compression
Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well.

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.

To use this option, we can run the following RMAN commands

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

followed by ..

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;

Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.

Compression Level ‘HIGH’

backupset size: 226.18M
time: 00:02:21

Compression Level ‘Medium’

backupset size: 293.80M
time: 00:00:30

Compression Level ‘Low’

backupset size: 352.59M
time: 00:00:20

Compression Level ‘Basic’

backupset size: 276.55M
time: 00:00:50

To summarise we can conclude:

LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower

reference:http://gavinsoorma.com/2009/12/11g-release-2-rman-backup-compression/

11G新特性 IO Calibration 评测

December 13, 2011 11g, hardware, oracle No comments

11G新特性 IO Calibration可以帮我们估算出存储的读写性能,在使用这个特性之前 我们需要满足一些条件:

.在linux系统中默认是不开启异步IO的
SQL> show parameter filesystemio_options

NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string none

可以通过以下语句查找asynchronous I/O是否被开启:

SQL> col name format a50
select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name=’Data File’ or filetype_name=’Temp File’);

NAME ASYNCH_IO
————————————————– ———
/data/oracle/oradata/yhddb1/system.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/system.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/sysaux.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/sysaux.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_index01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_data02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_data03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/qipei_index02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/undotbs03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_idx01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/pos_data_01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/pos_data_02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/pos_index_01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ims_data01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index06.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ims_index01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_idx02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index07.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/md_data04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser01.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser02.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index08.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index09.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index10.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index11.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index12.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/lg_index13.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser03.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/tms_data05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser04.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser05.dbf ASYNC_OFF
/data/oracle/oradata/yhddb1/ttuser06.dbf ASYNC_OFF

我们需要打开异步io
SQL> show parameter FILESYSTEMIO_OPTIONS;

NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string SETALL

设置这个参数需要重启数据库。参数“filesystemio_options” 支持4种值:
ASYNCH: 使Oracle支持文件的异步(Asynchronous)IO;
DIRECTIO:使Oracle支持文件的Direct IO;
SETALL:使Oracle同时支持文件的Asynchronous IO和Direct IO;
NONE:使Oracle关闭对Asynchronous IO和Direct IO的支持。


Syntax

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
num_physical_disks IN PLS_INTEGER DEFAULT 1,
max_latency IN PLS_INTEGER DEFAULT 20,
max_iops OUT PLS_INTEGER,
max_mbps OUT PLS_INTEGER,
actual_latency OUT PLS_INTEGER);

num_physical_disks —— Approximate number of physical disks in the database storage
max_latency —— Maximum tolerable latency in milliseconds for database-block-sized IO requests
max_iops —— Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
max_mbps —— Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads.
actual_latency —— Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds

我们可以通过 DBMS_RESOURCE_MANAGER.CALIBRATE_IO 测算出存储的性能 disk_count表示实际的物理磁盘个数,max_latency为最大容忍的延迟,这里我们设置为10

————————–

SQL> set serveroutput on;
SQL> DECLARE
2 lat INTEGER;
3 iops INTEGER;
4 mbps INTEGER;
5 BEGIN
6 — DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9 DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
10 DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
11 dbms_output.put_line(‘max_mbps = ‘ || mbps);
12 end;
13 /

max_iops = 901
latency = 15
max_mbps = 800

通过以下视图可以查看 I/O calibration results

SQL> desc V$IO_CALIBRATION_STATUS
Name Null? Type
—————————————– ——– —————————-
STATUS VARCHAR2(13)
CALIBRATION_TIME TIMESTAMP(3)

SQL> desc gv$io_calibration_status
Name Null? Type
—————————————– ——– —————————-
INST_ID NUMBER
STATUS VARCHAR2(13)
CALIBRATION_TIME TIMESTAMP(3)

Column explanation:
——————-
STATUS:
IN PROGRESS : Calibration in Progress (Results from previous calibration
run displayed, if available)
READY : Results ready and available from earlier run
NOT AVAILABLE : Calibration results not available.

CALIBRATION_TIME: End time of the last calibration run
DBA table that stores I/O Calibration results

SQL> desc DBA_RSRC_IO_CALIBRATE
Name Null? Type
—————————————– ——– —————————-
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
MAX_IOPS NUMBER
MAX_MBPS NUMBER
MAX_PMBPS NUMBER
LATENCY NUMBER
NUM_PHYSICAL_DISKS NUMBER

comment on table DBA_RSRC_IO_CALIBRATE is
‘Results of the most recent I/O calibration’

comment on column DBA_RSRC_IO_CALIBRATE.START_TIME is
‘start time of the most recent I/O calibration’

comment on column DBA_RSRC_IO_CALIBRATE.END_TIME is
‘end time of the most recent I/O calibration’

comment on column DBA_RSRC_IO_CALIBRATE.MAX_IOPS is
‘maximum number of data-block read requests that can be sustained per second’

comment on column DBA_RSRC_IO_CALIBRATE.MAX_MBPS is
‘maximum megabytes per second of maximum-sized read requests that can be
sustained’

comment on column DBA_RSRC_IO_CALIBRATE.MAX_PMBPS is
‘maximum megabytes per second of large I/O requests that
can be sustained by a single process’

comment on column DBA_RSRC_IO_CALIBRATE.LATENCY is
‘latency for data-block read requests’

comment on column DBA_RSRC_IO_CALIBRATE.NUM_PHYSICAL_DISKS is
‘number of physical disks in the storage subsystem (as specified by user)’

ORA-01555 on Active Physical standby and patchset issue without Central Inventory

December 7, 2011 maintain, oracle, replication 2 comments

今天在生产库解决一个ORA-01555 on Active Physical standby问题:
Applies to:

Oracle Server – Enterprise Edition – Version: 11.2.0.2.0 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Primary and Standby databases at 11.2.0.2
Symptoms

After upgrading to 11.2.0.2 we see this ORA-1555 on the Standby Database. It is not possible to logon with system, dbsnmp or other db-users. Only sys as sysdba is working.
It’s like we are running on system’s undo Tablespace, – and not the one which has been restored..

查看系统alert日志发现:
ORA-01555 caused by SQL statement below (SQL ID: 1f9apznp767fc, Query Duration=0 sec, SCN: 0x0006.62ab3687):
select object_name synonym_name, object_name, owner object_owner, status, object_type
from sys.all_objects o
where o.owner = object_owner
and o.object_type in (‘TABLE’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘SEQUENCE’, ‘FUNCTION’, ‘PROCEDURE’, ‘PACKAGE’, ‘TYPE’)
and o.object_name not like ‘BIN$%’

order by o.object_type, o.object_name

一个简单的查询 都导致了ora-01555 undo回滚段出现严重问题,同时出现如下问题:

ORA-00313: open failed for members of log group 15 of thread 1
ORA-00312: online log 15 thread 1: ‘/data/oracle/oradata/edw1/redo15.log’
ORA-27037: unable to obtain file status

客户端报如下错误:

同时发现mutex 等待 以及大量library cache lock 等待

通过查找metalink 发现与以下描述极其相似

This problem is introduced in 11.2.0.2 by the fix for bug 9214531

A self-hang / spin can occur when getting a library cache lock
leading to a wider hang scenario with other sessions waiting
for the blocked lock.

Rediscovery Notes:
A process spins in kglLock(),
other processes waiting for the spinning session
ORA-1555 on Active data Guard

解决方案:通过打p10018789_112020_Linux-x86-64解决此问题

打patch 过程过遇到很多问题,下面一一阐述:

opatch lsinventory raise error code 104

OPatch cannot find a valid oraInst.loc file to locate Central Inventory.
OPatch failed with error code 104

通过了解发现此RDBMS是通过tar包过来直接relink的,缺少全局的 Inventory 所以我们需要手动创建Inventory
首先要编辑一个oraInst.loc文件,使之指向我们要创建全局inventory的目录

inventory_loc=/data/oracle/oraInventory
inst_group=oinstall

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”” ORACLE_HOME_NAME=”

我们这里采用的是 ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/data/oracle/product/11.2.0/db1″ ORACLE_HOME_NAME=”oracle11g”

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /data/oracle/oraInventory

‘AttachHome’ was successful.

针对rac的Inventory重建 我们需要指定crs_home 和oracle_home

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”<10g Ora_Crs_Home Path>” ORACLE_HOME_NAME=”” LOCAL_NODE=’node1′ CLUSTER_NODES=node1,node2 CRS=true

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”<10g Oracle_Home Path>” ORACLE_HOME_NAME=”” LOCAL_NODE=’node1′ CLUSTER_NODES=node1,node2

下面的过程就显得很简单了:

解压缩补丁文件

unzip p10018789_112020_Linux-x86-64.zip

重新生成oraInventory

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/data/oracle/product/11.2.0/db1″ ORACLE_HOME_NAME=”oracle11g”

设置环境变量

export PATH=$ORACLE_HOME/OPatch:$PATH

停止redo apply

alter database recover managed standby database cancel;

关闭数据库

shutdownn immediate

打补丁

cd 10018789

opatch apply

打开数据库并开始redo应用

startup mount

alter database open read only

alter database recover managed standby database disconnect from session using current logfile;

观察日志 发现问题解决