ORACLE 10G OCP 043 笔记(二)

第9章 理解自动存储管理
Understanding Automatic Storage Management
ASM is a cluster file system that can be used either with stand-alone Oracle instances or with Oracle Real Application Clusters (RAC) to provide a vertically integrated
subsystem encapsulating a file system,
ASM takes the ease of use of OMF and combines it with mirroring and striping features to provide a robust file system and logical volume manager that can even support multiple nodes in an Oracle RAC.
ASM体系介绍
ASM also eases the administrative burden of managing potentially thousands of database files by creating disk groups , which are comprised of disk devices and the files that reside on the disk devices managed as a logical unit.
ASM requires a special type of Oracle instance to provide the interface between a traditional Oracle instance and the file system.
Two new background processes support ASM instances:RBAL and ORBn . RBAL coordinates the disk activity for disk groups on an ASM instance, and ORBn, where n can be from 0
to 9, performs the actual extent movement between disks in the disk groups.
理解ASM实例
创建一个ASM实例
An ASM instance generally has a smaller memory footprint in the range of 60MB to 100MB and is automatically configured when ASM is specified as the database's file storage option when installing the Oracle software and an existing ASM instance does not already exist
While it does have an initialization parameter file and a password file, it has no data dictionary or control file. Therefore, all connections to an ASM instance are via SYS and SYSTEM
using operating system authentication only.
连接到一个ASM实例
Starting Up and Shutting Down an ASM Instance
the STARTUP command defaults to STARTUP MOUNT.
If an ASM instance is already started, you can use this command in an ASM instance to prevent other database instances from connecting to the ASM instance:
SQL> alter system enable restricted session;
Similarly, you can re-enable connections as in this example:
SQL> alter system disable restricted session
Performing a SHUTDOWN command on an ASM instance performs the same SHUTDOWN command on any database instances using the ASM instance;
if you use the SHUTDOWN ABORT command on the ASM instance,all dependent databases immediately perform a SHUTDOWN ABORT because there is no longer an ASM instance available to manage the database's storage.
 
 
模拟asm 见blog
 
 
If an ASM instance is already started, you can use this command in an ASM instance to prevent other database instances from connecting to the ASM instance:
SQL> alter system enable restricted session; （但我测试了还能数据库关闭了再打开还是能用）
SQL>alter system enable restricted session;
Similarly, you can re-enable connections as in this example:
sql>alter system disable restricted session;
定义ASM初始化参数,INSTANCE_TYPE,DB_UNIQUE_NAME,ASM_POWER_LIMIT,ASM_DISKSTRING,ASM_DISKGROUPS,LARGE_POOL_SIZE
>$ export ORACLE_SID=+ASM
>sqlplus / as sysdba
sql> show parameter INSTANCE_TYPE asm
sql> show parameter DB_UNIQUE_NAME +ASM
sql> show parameter ASM_POWER_LIMIT 1 (controls how fast rebalance operations occur)
SQL>show parameter asm_diskstring
SQL>show parameter ASM_DISKGROUPS
SQL> show parameter LARGE_POOL_SIZE 12m,和常规instance中的大池不同,所有内部的asm包都是从这个区域开始执行。
 
 

对ASM动态性能视图进行分类,分别在asm实例和rdbms实例中查看以下视图,其代表不同的含义,有些视图只在asm实例中启用。
V$ASM_DISK select group_number,disk_number,state,name,path from v$asm_disk;
V$ASM_DISKGROUP select group_number,name,state from V$ASM_DISKGROUP;
V$ASM_FILE
V$ASM_OPERATION
V$ASM_TEMPLATE
V$ASM_CLIENT
V$ASM_ALIAS
 
 
使用ASM文件名
sql> select file#,name,blocks from v$datafile;
ASM filenames can be one of six different formats.
we'll give an overview of the different formats and the context where they can be used:
As a reference to an existing file
During a single-file creation operation
During a multiple-file creation operation
 
 
完整路径的ASM文件名Fully Qualified Names
Fully qualified ASM filenames are used only when referencing an existing file.
+group/dbname/file type/tag.file.incarnation
where
group is the disk group name.
dbname is the database to which the file belongs.
file type is the Oracle file type.
tag is the type-specific information about the specific file type.
The file.incarnation pair ensures uniqueness.
 
 
+group/dbname/file type/tag.file.incarnation
 
 
数字化的名称Numeric Names Numeric names are used only when referencing an existing ASM file.
+DATA2.256.1
 
 
别名
You can use an alias either when referencing an existing object or when creating a single ASM file.
sql> alter diskgroup data2 add directory '+data2/redempt';
sql> alter diskgroup data2 add alias '+data2/redempt/users.dbf' for '+data2/rac0/datafile/users3.256.1';
sql> alter diskgroup data1 add alias '+data1/redempt/users.dbf' for '+DATA1/prod/datafile/users.259.602868565'; 我的电脑
使用模版的别名
You can use an alias with a template only when creating a new ASM file.
sql> create tablespace users4 datafile '+data2/uspare(datafile)';
不完全名称
You can use an incomplete filename format either for single-file or for multiple-file creation operations.
sql>create tablespace users5 datafile '+data1';
使用模板的不完全名
As with incomplete ASM filenames, you can use an incomplete filename with a template for singlefile or multiple-file creation operations.
sql> create tablespace users6 datafile '+data1(tempfile)';
 
 
理解ASM类型和模板
Understanding ASM File Types and Templates
individual template characteristics can be changed and apply only to the disk group where they reside.
管理ASM磁盘组
理解磁盘组体系结构
Files in a disk group are striped on the disks using either coarse striping or fine striping. Coarse striping spreads files in units of 1MB each across all disks. Coarse striping is appropriate
for a system with a high degree of concurrent small I/O requests such as an online transaction processing (OLTP) environment. Alternatively, fine striping spreads files in units of 128KB and is appropriate for traditional data warehouse environments or OLTP systems with low concurrency and improves response time for individual I/O requests.
有点费解,以后看看切割的具体原理和好处。。。。。。。。
理解故障组和磁盘组镜像Understanding Failure Groups and Disk Group Mirroring
A failure group is one or more disks within a disk group that share a common resource,unless you specifically assign a disk to a failure group, each disk in a disk group is assigned to its own failure group.
Once the failure groups have been defined, you can define the mirroring for the disk group;the number of failure groups available within a disk group can restrict the type of mirroring
available for the disk group. The following three types of mirroring are available:
External redundancy
Normal redundancy
High redundancy
理解磁盘组动态重平衡
Whenever the configuration of a disk group changes, whether it is adding or removing a failure group or a disk within a failure group, dynamic rebalancing occurs automatically to proportionally reallocate data from other members of the disk group to the new member of the disk group.
 
 
—————————————————————————————————————————————————————————————
我是使用裸分区的形式用DBCA设置的ASM磁盘组,和书中很有些不同,路径的表现形式这样的。
sql> select group_number,disk_number,name,failgroup,create_date,path from v$asm_disk
GROUP_NUMBER DISK_NUMBER NAME FAILGROUP CREATE_DATE PATH
------------ ----------- ---------- ---------- ------------ --------------------
1 0 VOL1 VOL1 03-OCT-06 ORCL:VOL1
1 1 VOL2 VOL2 03-OCT-06 ORCL:VOL2
2 0 VOL3 VOL3 03-OCT-06 ORCL:VOL3
2 1 VOL4 VOL4 03-OCT-06 ORCL:VOL4
SQL> select group_number,name,type,total_mb,free_mb from v$asm_diskgroup;
 
 
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ---------- ------------ ---------- ----------
1 DATA1 NORMAL 12284 9878
2 DATA2 NORMAL 12284 12182
SQL> drop diskgroup data2;
SQL> select group_number,disk_number,name,failgroup,create_date,path from v$asm_disk;
GROUP_NUMBER DISK_NUMBER NAME FAILGROUP CREATE_DATE PATH
------------ ----------- ---------- ---------- ------------ --------------------
0 0 03-OCT-06 ORCL:VOL3
0 1 03-OCT-06 ORCL:VOL4
1 0 VOL1 VOL1 03-OCT-06 ORCL:VOL1
1 1 VOL2 VOL2 03-OCT-06 ORCL:VOL2
————————————————————————————————————————————————————————
 
 
创建和删除磁盘组
sql> select group_number,disk_number,name,failgroup,create_date,path from v$asm_disk;
sql> select group_number,name,type,total_mb,free_mb from v$asm_diskgroup;
sql> create diskgroup data2 high redundancy
failgroup fg1 disk '/dev/raw/raw3' name d2a
failgroup fg2 disk '/dev/raw/raw4' name d2b
failgroup fg3 disk '/dev/raw/raw5' name d2c
failgroup fg4 disk '/dev/raw/raw6' name d2d;
 
 
sql> create diskgroup data2 normal redundancy
failgroup fg1 disk 'ORCL:VOL3' name d2a
failgroup fg2 disk 'ORCL:VOL4' name d2b;
 
 
Looking at the dynamic performance views, you see the new disk group available in V$ASM_DISKGROUP and the failure groups in V$ASM_DISK:
SQL> select group_number, name, type, total_mb, free_mb from v$asm_diskgroup;
SQL> select group_number, disk_number, name, failgroup, create_date, path from v$asm_disk;
 
 
in the following example, the disk /dev/raw/raw4 was previously used as part of a disk group, so you must specify FORCE:
sql>create diskgroup data2 high redundancy
failgroup fg1 disk '/dev/raw/raw3' name d2a
failgroup fg2 disk '/dev/raw/raw4' name d2b force
failgroup fg3 disk '/dev/raw/raw5' name d2c
failgroup fg4 disk '/dev/raw/raw6' name d2d;
 
 
sql>drop diskgroup data2;
sql>create diskgroup data2 high redundancy
failgroup fg1 disk '/dev/raw/raw3' name d2a
failgroup fg2 disk '/dev/raw/raw4' name d2b
failgroup fg3 disk '/dev/raw/raw5' name d2c;
 
 
sql> select group_number,disk_number,name,failgroup,create_date,path from v$asm_disk;
If the disk group had any database objects other than disk group metadata, you would have to specify INCLUDING CONTENTS in the DROP DISKGROUP command.
 
 
修改磁盘组
SQL> alter diskgroup data1 add failgroup d1fg3 disk '/dev/raw/raw6' name d1c;
检查重平衡操作的状态
SQL> select group_number, operation, state, power, actual,sofar, est_work, est_rate, est_minutes from v$asm_operation;
默认power=1,该参数控制重平衡的速度,决定为重平衡操作分配更多的资源,改变power限制 power可以为1-11
SQL> alter diskgroup data1 rebalance power 8;
检查重平衡操作的状态
SQL> select group_number, operation, state, power, actual,sofar, est_work, est_rate, est_minutes from v$asm_operation;
过几分钟再检查
sql>/
SQL> select group_number, disk_number, name,failgroup, create_date, path from v$asm_disk;
sql> select group_number,name,type,total_mb,free_mb from v$asm_diskgroup;
SQL> alter diskgroup data1 add failgroup d1fg3 disk '/dev/raw/*' name d1c;(忽略已经属于磁盘组的磁盘)
 
 
使用ALTER DISKGROUP DROP DISK,
 
 
SQL> select group_number, operation, state, power, actual,sofar, est_work, est_rate, est_minutes from v$asm_operation;
sql> alter diskgroup data2 drop disk d2d;
 
 
-------------------------------------------------------------------------------------------------------------------------------------------------------
我的机器执行了删除磁盘,但并没有生效,why
SQL> alter diskgroup data2 drop disk d2a;
SQL> select group_number, disk_number, name,failgroup, create_date, path from v$asm_disk;
 
 
GROUP_NUMBER DISK_NUMBER NAME FAILGROUP CREATE_DATE PATH
------------ ----------- ---------- ---------- ------------ --------------------
1 0 VOL1 VOL1 03-OCT-06 ORCL:VOL1
1 1 VOL2 VOL2 03-OCT-06 ORCL:VOL2
2 0 D2A FG1 05-OCT-06 ORCL:VOL3
2 1 D2B FG2 05-OCT-06 ORCL:VOL4
---------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
使用ALTER DISKGROUP UNDROP DISKS
The UNDROP DISKS clause cancels any pending drops of a disk from a disk group. If the drop
operation has completed, you must re-add the disk to the disk group manually and incur the
rebalancing costs associated with adding the disk back to the disk group.
sql>alter diskgroup data2 add failgroup fg4 disk '/dev/raw/raw6' name d2d;
sql>select adg.name DG_NAME,ad.name FG_NAME,path from v$asm_disk ad right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number
where adg.name = 'DATA2';
sql>alter diskgroup data2 drop disk d2d;
sql>alter diskgroup data2 undrop disks;
sql>select adg.name DG_NAME,ad.name FG_NAME,path from v$asm_disk ad right outer join v$asm_diskgroup adg on ad.group_number = adg.group_number
where adg.name = 'DATA2';
 
 
使用ALTER DISKGROUP REBALANCE POWER n 调整重平衡速度
使用ALTER DISKGROUP DROP ADD 只发生一次重平衡操作,而不会发生两次
sql>select adg.name DG_NAME, ad.name FG_NAME, path from v$asm_disk ad right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number where adg.name = 'DATA2';
SQL> alter diskgroup data2 add failgroup fg4 disk '/dev/raw/raw6' name d2d
drop disk d2c;
SQL> select adg.name DG_NAME, ad.name FG_NAME, path from v$asm_disk ad right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number where adg.name = 'DATA2';
 
 
使用ALTER DISKGROUP DISMOUNT
sql>alter diskgroup data2 dismount;
 
 
使用ALTER DISKGROUP MOUNT
SQL>ALTER DISKGROUP data2 MOUNT;
 
 
使用ALTER DISKGROUP CHECK ALL
SQL>alter diskgroup data2 check all;
 
 
将EM DATABASE CONTROL用于ASM磁盘组
使用RMAN是实现将数据库迁移到ASM,以后测试下
1. Note the filenames of the control files and the online redo log files.
2. Shut down the database using the NORMAL, IMMEDIATE, or TRANSACTIONAL keywords.
3. Back up the database.
4. Edit the SPFILE to use OMF for all file destinations.
5. Edit the SPFILE to remove the CONTROL_FILES parameter.
6. Run the following RMAN script, substituting your specific filenames as needed:
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '';
ALTER DATABASE MOUNT;
BACKUP AS COPY DATABASE FORMAT
'+';
SWITCH DATABASE TO COPY;
SQL "ALTER DATABASE RENAME
TO '+' ";
# repeat for all log file members
ALTER DATABASE OPEN RESETLOGS;
7. Delete or archive the old database files.
 
 

第10章 Understanding Globalization Support
 
 
全球化支持概述
Globalization Support Features
Language support
Territory support
Linguistic sorting and searching
Character sets and semantics
Locale and calendar customization
全球化支持结构 略
支持多语言应用程序 略
When a database is created, two session-independent NLS parameters are specified: the database character set and the national character set
the database character set is a strict superset of the client character set.
解析客户/服务器设置
Any application that connects to the server is considered to be a client, in terms of globalization.
When a client application is run, the client NLS environment is initialized from the environment variable settings.
When the application completes a connection to the database server, the resulting session is initialized with the NLS environment settings of the server.
However, immediately after the session is established, the client implicitly issues an ALTER SESSION statement to synchronize the session NLS environment to match the client's NLS environment.
 
 
sql>select sysdate from dual;
sql>alter session set NLS_LANGUAGE=Italian;
sql>select sysdate from dual;
 
 
在多语言数据库中使用
Unicode is a universal character set that encompasses all known written languages in the world.
 
 
使用NLS参数
Ultimately, Oracle globalization support options are defined by NLS parameter settings. By assigning values to specific NLS parameters, you can control when, where, and how Oracle will
utilize globalization support functionality.
 
 
Setting NLS Parameters
NLS parameters can be classified into the following categories:
Language and territory parameters
Date and time parameters
Calendar parameters
Numeric, list, and monetary parameters
Length semantics
Using the NLS_LANG Parameter
NLS_LANG is a client-side environment variable that defines the language, territory, and character set for the client. It is functionally equivalent to setting the NLS_LANGUAGE, NLS_TERRITORY, and NLS_CHARACTERSET parameters individually.
some example:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = MM/DD/YY;
SQL> alter session set NLS_DATE_FORMAT ='"Today''s date is "MM/DD/YYYY';
SQL> alter session set nls_date_language=Italian;
SQL> select to_char(sysdate,'Day:Dd Month YYYY') from dual;
 
 
SQL> alter session set nls_timestamp_format='MM/DD/YYYY HH24:MI:SS.FF';
SQL> select startup_time from sys.dba_hist_snapshot where rownum < 3;
 
 
SQL> alter session set nls_timestamp_tz_format = 'YYYY/MM/DD HH:MI TZH:TZM';
SQL> select startup_time from sys.dba_hist_snapshot where rownum < 3;
SQL> select next_run_date from sys.dba_scheduler_jobs;
SQL> alter session set NLS_CALENDAR = 'Persian';
SQL> select sysdate from dual;
 
 
SQL> alter session set NLS_NUMERIC_CHARACTERS=",.";
SQL> select cust_id, to_char(sum(amount_sold), '9G999G999D99') big_sales from sales
group by cust_id
having sum(amount_sold) > 30000;
 
 
SQL> alter session set NLS_CURRENCY = "￡";
SQL> select to_char(123.45,'L9G999G999D99') amount
from dual;
 
 
SQL> alter session set NLS_CURRENCY = " USD";
SQL> select to_char(123.45,'9G999G999D99L') amount
from dual;
 
 
Prioritizing NLS Parameters
NLS parameters can be defined using any of the following methods:
Server initialization parameters
Client environment variables
Using the ALTER SESSION statement
In SQL functions
Default values
Setting Server Initialization Parameters
Environment variables on the client side will govern local client-side NLS operations (operations that don't involve the database). They will also override server-side NLS settings for sessions created from the client.
$ export NLS_LANGUAGE=French
$ sqlplus "/ as sysdba"
 
 
Using the ALTER SESSION Statement
Setting NLS parameters using the ALTER SESSION statement also overrides the server-side NLS settings for the current session, as in this example:
SQL> ALTER SESSION set NLS_SORT = FRENCH;

Setting NLS Parameters in SQL Functions
NLS parameters can also be set inside certain SQL functions. Inline NLS parameter settings have the highest priority and will override any other NLS settings.
SQL> select to_char(sysdate, 'DD/MON/YYYY','nls_date_language=Italian') from dual;
SQL> select to_char(sysdate, 'DD/MON/YYYY') from dual;
Prioritization Summary
TABLE 1 0 . 3 NLS Parameter Setting Precedence
Method Priority Scope
Set in SQL functions 1 Current SQL function
Explicit ALTER SESSION statement 2 Current session
Client environment variable (implicit ALTER SESSION statement) 3 Current session
Set by server initialization parameter 4 Instance
Default 5 Instance
 
 
Using NLS Views
NLS_SESSION_PARAMETERS
NLS_INSTANCE_PARAMETERS
NLS_DATABASE_PARAMETERS
V$NLS_VALID_VALUES
 
 
Using Datetime Datatypes
The data stored using these datatypes are often called datetimes, and you'll learn about the following:
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
Oracle dates consist of seven parts: century, year, month, day, hours, minutes, and seconds (elapsed since midnight). In fact, they are stored internally in the database as seven separate elements.
SQL> create table birthdates (client_id NUMBER,birthdate DATE);
SQL> insert into birthdates values(1, sysdate);
SQL> select to_char(birthdate,'YYYY-MM-DD:HH24:MI:SS') from birthdates;
SQL> select dump(birthdate) from birthdates;
DUMP(BIRTHDATE)
------------------------------------------------------
Typ=12 Len=7: 120,104,10,22,12,30,45
Oracle stores the century and the year elements using excess 100 notation, which means that it adds 100 to the number before storing it. Also, the hours, minutes, and seconds elements are stored using excess 1 notation.
把上面的结果转换成标准的10进制表示法
DUMP(BIRTHDATE)
------------------------------------------------------
Typ=12 Len=7: 20,04,10,22,11,29,44
SQL> select to_char(birthdate,'YYYY-MM-DD:HH24:MI:SS') from birthdates;
TO_CHAR(BIRTHDATE,'
-------------------
2004-10-22:11:29:44
 
 

DATE datatypes always store both a date and a time. If no time is specified when storing a date,Oracle will use a default time of midnight. This can be problematic if you're not careful,
To resolve this problem, you can use the TRUNC function, as shown here:
SQL> select * from conv_dates where trunc(converted_date) = trunc(sysdate); 除去时间元素
 
 
When entering date information into a DATE datatype, you can specify it in several ways:
Literal
SQL> alter session set NLS_DATE_FORMAT = "MM-DD-YYYY";
SQL> insert into birthdates values(2, '12-30-1972');
ANSI date literal
It must be formatted exactly as shown here:DATE 'YYYY-MM-DD'
SQL> insert into birthdates values(3, DATE '1966-08-25');
TO_DATE function （对于转换以字符型存储的日期是很方便的）
SQL> insert into birthdates values(4,to_date('09-29-1993 13:45', 'MM-DD-YYYY HH24:MI');
 
 
TIMESTAMP Datatype
By default, the TIMESTAMP datatype stores fractional seconds to six digits of precision. This can be changed, however, by specifying a number between 0 and 9 in parentheses after the
TIMESTAMP keyword.
SQL> create table test_stamp (stamp timestamp(2));
SQL> insert into test_stamp select to_timestamp('09-SEP-2004 17:54.38.92','DD-MON-YYYY HH24:MI:SS:FF') from dual;
SQL> commit;
SQL> select * from test_stamp;
 
 
TIMESTAMP WITH TIME ZONE Datatype
SQL> create table stamp_tz (stamp_tz TIMESTAMP(4) WITH TIME ZONE);
TIMESTAMP WITH LOCAL TIME ZONE Datatype
The TIMESTAMP WITH TIME ZONE doesn't actually store time zone information at all.
转换为当地时间再存储
 
 
Using Linguistic Sorts and Searches
Binary Sorts
Binary sorts perform a numeric sort based on the encoded value (in the character set) for each character.
Linguistic Sorts
Oracle provides a rich set of linguistic sort definitions that cover most of the languages of the world. However, it also provides the ability to define new definitions or to modify existing definitions. The Oracle Locale Builder, a graphical tool that ships with Oracle 10g, can be used to view, create, and modify sort definitions and other locale definitions.
Using Linguistic Sort Parameters
NLS_SORT
The NLS_SORT parameter defines which type of sorting—binary or linguistic—should be performed for SQL sort operations.
By default, the value for NLS_SORT is the default sort method defined for the language identified in the NLS_LANGUAGE parameter.
SQL> alter session set NLS_SORT = German;
By using the NLS_SORT parameter, you can make the following changes to Oracle's default functionality:
Set the default sort method for all ORDER BY operations.
Set the default sort value for the NLSSORT function.
SQL> show parameters NLS_LANGUAGE
SQL> show parameters NLS_SORT

Remember, the NLS_SORT parameter overrides the default sorting method for ORDER BY operations and for the NLSSORT function, but has no effect on other sort operations, such as
WHERE conditions
To perform a linguistic sort, you call the NLSSORT function. Normally, the function would
be called like this:
SQL> select * from sort_test where nlssort(name, 'NLS_SORT=German_din') > nlssort('einschlie遧ich','NLS_SORT=German_din');
However, because the NLS_SORT parameter defines the default sort for the NLSSORT function,specifying the sort inside the function is unnecessary. The following method works as well:
SQL> select * from sort_test where nlssort(name) > nlssort('einschlie遧ich');
 
 
NLS_COMP
The NLS_COMP parameter works in conjunction with the NLS_SORT parameter to make linguistic
sorts easier to use. When the NLS_COMP parameter is set to a value of ANSI, all of the following SQL
operations will default to linguistic sorting (using the language specified in NLS_SORT parameter):
ORDER BY
BETWEEN
CASE WHEN
HAVING
IN/OUT
START WITH
WHERE
 
 

11 Managing Resources
Putting the Pieces Together 把最后一部分笔记放在前面了。
In this section, you will be provided step-by-step instructions for the creation of the plans shown in Figures 11.2 and 11.3. First, the pending area will be created. Next, the consumer resource groups will be created. After that, the resource plans will be created. And, finally, the resource plan directives will be created to tie them all together.
OFF_HOURS_PLAN Ratio 10:5:2:1
|
----------------------------------------------------------------------------------------
| | | |
SYS_GROUP 10 NIGHTLY_PROCESSING_GROUP 5 OLAP_REPORTS_GROUP 2 OTHER_GROUPS 1
 
 
word复制这个图 11.3
Creating the Pending Area
SQL> exec dbms_resource_manager.create_pending_area();
 
 
Creating the Consumer Resource Groups
SQL> begin
dbms_resource_manager.create_consumer_group(
'OLTP_GROUP','Incoming orders');
end;
SQL>/

SQL> begin
dbms_resource_manager.create_consumer_group(
'DAY_REPORTS_GROUP','DAYTIME REPORTS');
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_consumer_group(
'NIGHTLY_PROCESSING_GROUP','BULK LOADS, ETL, ETC.');
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_consumer_group(
'OLAP_REPORTS_GROUP','OFF HOURS REPORTS');
end;
SQL>/
 
 
Creating the Resource Plans
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'DAY_PLAN',
COMMENT => 'GOVERNS NORMAL WORKING HOURS ');
end;
SQL> /
 
 
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'OLTP_PLAN',
COMMENT => 'ORDER ENTRY SUB-PLAN');
end;
SQL> /
 
 
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'OFF_HOURS_PLAN',
COMMENT => 'GOVERNS NON-WORKING HOURS',
CPU_MTH => 'RATIO');
end;
SQL> /
 
 

Creating the Resource Plan Directives
Creating the OFF_HOURS_PLAN Plan Directives
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 10);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'NIGHTLY_PROCESSING_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY JOBS',
CPU_P1 => 5);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OLAP_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY REPORTS',
CPU_P1 => 2);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P1 => 1);
end;
SQL>/
 
 
Creating the OLTP_PLAN Plan Directives
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_GROUP',
COMMENT => 'CPU ALLOCATION FOR OLTP USERS',
CPU_P1 => 90);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'DAY_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR DAYTIME REPORTING',
CPU_P1 => 10);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P2 => 100);
end;
SQL>/
 
 
Creating the DAY_PLAN Plan Directives
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 100);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_PLAN',
COMMENT => 'CPU ALLOCATION FOR OLTP_PLAN SUB-PLAN',
CPU_P2 => 100);
end;
SQL>/
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P3 => 100);
end;
SQL>/
 
 
Validating the Pending Area
SQL> exec dbms_resource_manager.validate_pending_area; 出错,以后修正
Submitting the Pending Area
SQL> exec dbms_resource_manager.submit_pending_area;
Enabling the Resource Plans
Only one resource plan may be enabled at any given time, and the enabled plan can be switched at any time.
There are two methods in which resource plans can be enabled:
Initialization parameter (at instance startup time)
ALTER SYSTEM statement
Initialization Parameter Method
the following code can be added to the init.ora file:
RESOURCE_MANAGER_PLAN = DAY_PLAN;
ALTER SYSTEM Statement Method
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘DAY_PLAN’ [SCOPE = BOTH];
 
 
Switching the Enabled Resource Plan
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘FORCE:OFF_HOURS_PLAN’;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 

Oracle's Database Resource Manager (DRM) circumvents the inefficient operating system management process by giving the Oracle database server more control over resource management decisions.
 
 
An Overview of the Database Resource Manager
There are three main elements that comprise the DRM:

Resource consumer groups

Resource plans

Resource plan directives
DRM allocates resources among resource consumer groups based on a resource plan.A resource plan consists of resource plan directives that specify how resources should be distributed among the groups. Resource consumer groups are categories to which user sessions can be assigned. These groups can then be allocated resources through plan directives.
 
 
Working with the Pending Area
Before defining or updating any DRM objects, you must first establish a pending area
Creating a Pending Area
SQL> exec dbms_resource_manager.create_pending_area();
Validating Changes
SQL> exec dbms_resource_manager.validate_pending_area;
Submitting the Pending Area
SQL> exec dbms_resource_manager.submit_pending_area;
Clearing the Pending Area
SQL> exec dbms_resource_manager.clear_pending_area;
 
 
Resource Consumer Groups
In either situation, users can be assigned to one or more resource consumer groups, although each active session can be assigned to only one resource consumer group at a time.
Managing Resource Consumer Groups
Creating Resource Consumer Groups
TABLE 1 1 . 1 CREATE_CONSUMER_GROUP Parameters
Parameter Description
CONSUMER_GROUP Consumer group name.
COMMENT Any comment (usually a description of the group).
CPU_MTH Method used to schedule CPU resources between sessions in the group. Valid values are:ROUND_ROBIN (the default) ensures fair distribution by using a roundrobin
schedule. RUN_TO_COMPLETION schedules the most active sessions ahead of other sessions.
SQL> begin
dbms_resource_manager.create_consumer_group('developers',
'application developers');
end;
SQL>/
SQL> select consumer_group, cpu_method, comments from dba_rsrc_consumer_groups
where consumer_group = 'DEVELOPERS';
By default, there are four resource consumer groups predefined in the database. They are defined in Table 11.2.
 
 
TABLE 1 1 . 2 Predefined Resource Consumer Groups
Group Name Description
DEFAULT_CONSUMER_GROUP Default group for all users/sessions not assigned to an initial consumer
group.
OTHER_GROUPS Catch-all group for users assigned to groups that are not part of the currently active plan. This group cannot be explicitly assigned to users.
SYS_GROUP Used by the Oracle-provided SYSTEM_PLAN plan.
LOW_GROUP Used by the Oracle-provided SYSTEM_PLAN plan.
 
 
Updating Resource Consumer Groups
SQL> begin
dbms_resource_manager.update_consumer_group(
CONSUMER_GROUP => 'DEVELOPERS',
NEW_CPU_MTH => 'RUN-TO-COMPLETION');
end;
SQL> /
 
 
Deleting a Resource Consumer Group
SQL> begin
dbms_resource_manager.delete_consumer_group('DEVELOPERS');
end;
SQL>/
 
 
Assigning User Sessions to Consumer
You still need a method of assigning consumer groups to user sessions. DRM can be configured to automatically assign consumer groups to sessions based on specific session attributes. This process is called consumer group mapping
Creating Consumer Group Mappings
SQL> begin
dbms_resource_manager.set_consumer_group_mapping(ATTRIBUTE => dbms_resource_manager.CLIENT_OS_USER,
VALUE => 'a',CONSUMER_GROUP => 'developers');
end;
SQL>/
 
 
Establishing Mapping Priorities
SQL> begin
dbms_resource_manager.SET_CONSUMER_GROUP_MAPPING_PRI(
EXPLICIT => 1,
CLIENT_OS_USER => 2,
CLIENT_MACHINE => 3,
CLIENT_PROGRAM => 4,
ORACLE_USER => 5,
MODULE_NAME => 6,
MODULE_NAME_ACTION => 7,
SERVICE_NAME => 8,
SERVICE_MODULE => 9,
SERVICE_MODULE_ACTION => 10);
end;
SQL>/
 
 
Changing Resource Consumer Groups
When a user is granted the switch privilege, they can use the DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP procedure to change the consumer group for their current session.
Switching Groups using DBMS_RESOURCE_MANAGER Procedures
The first procedure, SWITCH_CONSUMER_GROUP_FOR_SESS, explicitly assigns an active session to a new consumer group.
SQL> begin
dbms_resource_manager.switch_consumer_group_for_sess (
SESSION_ID => '56',
SESSION_SERIAL=> '106',
CONSUMER_GROUP => 'LOW_GROUP');
end;
SQL>
 
 
The second method of changing the consumer group for an active session is the SWITCH_CONSUMER_GROUP_FOR_USER procedure. This procedure changes all active sessions for a given Oracle username. Here's an example:
SQL> begin
dbms_resource_manager.switch_consumer_group_for_user (
USER => 'test01',
CONSUMER_GROUP => 'LOW_GROUP');
end;
SQL>/
 
 
Switching Groups Using DBMS_SESSION
略
 
 
Managing the Switch Privilege
--GRANTEE_NAME,CONSUMER_GROUP,GRANT_OPTION
SQL> begin
dbms_resource_manager_privs.grant_switch_consumer_group(
'PROG_ROLE', 'developers',FALSE);
end;
SQL>/
In this example, the switch privilege is granted to the PROG_ROLE role. Any user granted that role will be able to switch to the DEVELOPERS group, but they cannot grant the privilege to any other users.
SQL>
begin
dbms_resource_manager_privs.revoke_switch_consumer_group('PROG_ROLE', 'developers');
end;
SQL>/
 
 

Resource Plans
DRM allocates resources among resource consumer groups based on a resource plan.
Resource plans prioritize resource allocation through the use of levels, with level 1 being the highest priority and level 8 being the lowest.
Resource Plan CPU Allocation Methods
EMPHASIS The allocated amount is treated as a percentage
RATIO The allocated amount is treated as a ratio of the total CPU resources
Creating Simple Resource Plans
They are distinct from complex plans in that they create a resource plan, resource plan directives, and resource consumer groups in one simple procedure
SQL> begin
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(
SIMPLE_PLAN => 'DEPARTMENTS',
CONSUMER_GROUP1 => 'PAYROLL',
GROUP1_CPU => 50,
CONSUMER_GROUP2 => 'SALES',
GROUP2_CPU => 25,
CONSUMER_GROUP3 => 'MARKETING',
GROUP3_CPU => 25);
end;
SQL> /
Final DEPARTMENTS Plan
Level Sys_Group Payroll Sales Marketing Other_Groups
1 100%
2 50% 25% 25%
3 100%
 
 

Creating Complex Resource Plans
For complex plans, each of these elements is defined and stored separately.
Creating a plan involves defining the name of the plan, a comment or description regarding the plan, and the methods that the plan will follow when allocating specific resources. Notice
that the plan does not determine which resources it will manage. Those are predefined by Oracle
DBMS_RESOURCE_MANAGER.CREATE_PLAN procedure, whose parameters are described in Table 11.15.
PLAN COMMENT CPU_MTH ......
SQL> begin
dbms_resource_manager.create_plan(
PLAN => 'DAY',
COMMENT => 'CREATED BY TBB');
end;
SQL>/
SQL> select plan, num_plan_directives, cpu_method from dba_rsrc_plans;
 
 
Creating Resource Sub-Plans
A sub-plan is a plan. It only becomes a sub-plan if a higher level plan allocates resources to it (through a resource plan directive).
Modifying Resource Plans
exec不能换行？
SQL> exec dbms_resource_manager.update_plan(PLAN => 'DAY',NEW_COMMENT => 'Plan for scheduled work hours');
Deleting Resource Plans
SQL> exec dbms_resource_manager.delete_plan(‘DAY');
 
 
Resource Plan Directives
Resource plan directives assign consumer groups to resource plans and define the resource allocations for each. In addition to consumer groups, plan directives can
allocate resources to sub-plans.
Resource allocation methods are predefined by Oracle and, as such, are not modifiable.These represent the various methods available to DRM to allocate resources.
CPU
Active session pool with queuing
Degree of parallelism limit
Automatic consumer group switching
Canceling SQL and terminating sessions
Execution time limit
Undo pool
Idle time limit
 
 
Creating Resource Plan Directives
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
The following example creates a resource plan directive for the DAY plan, which limits the
parallel degree settings for the DEVELOPERS group:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'DEVELOPERS DAY PLAN',
GROUP_OR_SUBPLAN => 'DEVELOPERS',
PARALLEL_DEGREE_LIMIT_P1 => '4');
end;
SQL> /
Creating Sub-Plan Directives
For example, to define a sub-plan under the DAY plan, you would set the GROUP_OR_SUBPLAN parameter to the name of the target plan, as follows:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'DEPARTMENTS SUB-PLAN',
GROUP_OR_SUBPLAN => 'DEPARTMENTS',
CPU_P2=> 50);
end;
SQL> /
 
 
Creating Multi-Level Plan Directives
Multi-level plan directives are used to prioritize CPU allocation for consumer groups and subplans.
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'SYSTEM USERS',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
CPU_P1=> 100);
end;
SQL> /
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'DEPARTMENTS SUB-PLAN',
GROUP_OR_SUBPLAN => 'DEPARTMENTS',
CPU_P2=> 50);
end;
SQL> /
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'DEVELOPERS GROUP CPU ALLOCATION',
GROUP_OR_SUBPLAN => 'DEVELOPERS',
CPU_P2=> 50);
end;
SQL> /
 
 
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'OTHER_GROUPS CPU ALLOCATION',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
CPU_P3=> 100);
end;
SQL> /
 
 
Creating Automatic Consumer Group Switching Directives
SWITCH_TIME The SWITCH_TIME parameter sets the maximum execution time (in seconds) allowed for any operation.转换后不会回到原来的群
SWITCH_TIME_IN_CALL 类似SWITCH_TIME ,但违规的会话在转换群中停留的时间至操作结束为止,会话将返回他原来的群
SWITCH_ESTIMATE 预测执行时间,如果超过,就进行消费群转换
the switch group can also be set to the Oracle constants KILL_SESSION or CANCEL_SQL
To create a plan directive that automatically cancels operations that execute for more than
one hour, see the following example:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'LIMIT DEVELOPERS EXECUTION TIME',
GROUP_OR_SUBPLAN => 'DEVELOPERS',
SWITCH_GROUP => 'CANCEL_SQL',
SWITCH_TIME => 3600);
end;
SQL> /
 
 
To create a plan directive that temporarily moves DEVELOPERS sessions to a lower priority group whenever Oracle estimates that an operation will execute for more than 15 minutes, see
this example:
SQL> begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY',
COMMENT => 'SWITCH DEVELOPERS TEMPORARILY',
GROUP_OR_SUBPLAN => 'DEVELOPERS',
SWITCH_TIME_IN_CALL => 900,
SWITCH_GROUP => 'LOW_GROUP',
SWITCH_ESTIMATE => TRUE);
end;
SQL> /
 
 
Updating Resource Plan Directives
SQL> begin
dbms_resource_manager.update_plan_directive(
PLAN => ‘DAY’,
GROUP_OR_SUBPLAN => ‘DEVELOPERS’,
NEW_SWITCH_ESTIMATE => FALSE);
end;
SQL>/
加前缀NEW_到科修改的参数……
 
 
Deleting Resource Plan Directives
SQL> begin
dbms_resource_manager.delete_plan_directive(
PLAN => ‘DAY’,
GROUP_OR_SUBPLAN => ‘DEVELOPERS’);
end;
SQL>/
 
 
 
 

12 Using the Scheduler to Automate Tasks
Scheduler Overview
Jobs A job instructs the Scheduler to run a specific program at a specific time on a specific date.
Programs A program contains the code (or a reference to the code) that needs to be run to accomplish a task.
Schedules A schedule contains a start date, an optional end date, and a repeat interval.
Windows A window identifies a recurring block of time during which a specific resource plan should be enabled to govern resource allocation for the database.
Job groups A job group is a logical method of classifying jobs with similar characteristics.
Window groups A window group is a logical method of grouping windows.
 
 
Scheduler Architecture
The job table, which houses all the active jobs within the database
SQL>select owner, job_name, state,NEXT_RUN_DATE from dba_scheduler_jobs;
The job coordinator, a key Oracle process that ensures that jobs are being run on schedule
Job slaves, processes that carry out the execution of jobs under the guidance of the job coordinator
The architecture in Real Application Cluster (RAC) environments and how it differs only slightly from a stand-alone database environment
Each instance in the cluster will have its own job coordinator.The job coordinators can communicate with one another to share formation.
. Jobs can be defined with a service affinity (they should run on a specific service) as opposed to an instance affinity (they should run on a pecific instance).
 
 
Common Administration Tools
The Oracle Scheduler is implemented through a PL/SQL package named DBMS_SCHEDULER
Using the ENABLE Procedure
With the exception of schedules, all Scheduler objects have a common attribute named ENABLED
SQL> begin
dbms_scheduler.enable('BACKUP_JOB');
end;
SQL> begin
dbms_scheduler.enable( 'BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB');
end;
SQL> begin
dbms_scheduler.enable('BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1');
end;
/
When a job group is enabled, all members of that job group will be enabled.
When a window group is enabled, only the window group object is enabled. Windows that are members of the group are not enabled.
When a window or window group is referenced in the ENABLE procedure, it must always be prefixed with the SYS schema name as shown in the preceding example (SYS.WINDOW_GROUP_1).
Using the DISABLE Procedure
DBMS_SCHEDULER.DISABLE
This procedure accepts two parameters: NAME and FORCE.
The FORCE parameter is a Boolean (TRUE or FALSE) value that tells the procedure how to handle the request if dependencies exist. The default value is FALSE.
There are two situations that could be classified as dependencies:
A job object that references a program object is considered to be dependent on that object.
If an instance of an object is currently running (for example, a window is open or a job is running), there may be a dependency issue.
SQL>
begin
dbms_scheduler.disable('BACKUP_JOB');
end;
SQL> begin
dbms_scheduler.disable(
'BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB',TRUE);
end;
 
 
SQL> begin
dbms_scheduler.disable(
'BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1');
end;
/
 
 
Setting Attributes
Scheduler objects are collections of attributes. To make a change to an object requires setting its attributes
SQL> begin
dbms_scheduler.set_attribute (
name => 'TEST_JOB',
attribute => 'ENABLED',
value => TRUE);
end;
/
SQL> begin
dbms_scheduler.set_attribute_null (
name => 'TEST_SCHEDULE',
attribute => 'END_DATE');
end;
 
 
Using Scheduler Jobs
A Scheduler job defines a specific program to be executed, the arguments (or parameters) to be passed to the program, and the schedule defining when the program should be executed.
Creating Jobs
a job combines a program and a schedule for execution of that program.
Jobs are created by using the DBMS_SCHEDULER.CREATE_JOB procedure. The CREATE_JOB procedure is an overloaded procedure.
调度器允许用四种不同的方法创建任务。
The following example creates a job that will run once every year to enact cost of living adjustments for all employees:
SQL> begin
dbms_scheduler.create_job (
job_name => 'cola_job',
job_type => 'PLSQL_BLOCK',
job_action => 'update employee set salary = salary*1.05;',
start_date => '10-OCT-2004 06:00:00 AM',
repeat_interval => 'FREQ=YEARLY',
comments => 'Cost of living adjustment');
end;
/
To verify that the job was created, the DBA|ALL|USER_SCHEDULER_JOBS view can be queried,as shown here:
SQL> select job_name, enabled, run_count from user_scheduler_jobs;
 
 
Copying Jobs(默认停用)
SQL> begin
dbms_scheduler.copy_job('COLA_JOB','RAISE_JOB');
end;
/
SQL> select job_name, enabled from user_scheduler_jobs;
 
 
Running Jobs
SQL> begin
dbms_scheduler.run_job('COLA_JOB',TRUE); 第2个参数 同步或异步
end;
/
 
 
Stopping Jobs
SQL> begin
dbms_scheduler.stop_job(job_name => 'COLA_JOB',
force => TRUE);
end;
/
when a job is stopped using the STOP_JOB procedure, only the most recent transaction is rolled back.
 
 
Dropping Jobs
SQL> begin
dbms_scheduler.drop_job(job_name => 'COLA_JOB',
force => TRUE);
end;
 
 
Using Job Classes
Job Class Parameters
JOB_CLASS_NAME
RESOURCE_CONSUMER_GROUP
SERVICE
LOGGING_LEVEL
LOG_HISTORY
Creating Job Classes
SQL> begin
dbms_scheduler.create_job_class(
job_class_name => 'LOW_PRIORITY_CLASS',
resource_consumer_group => 'LOW_GROUP',
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
log_history => 60,
comments => 'LOW PRIORITY JOB CLASS');
end;
/
In this example, a job class named LOW_PRIORITY_CLASS was created that will assign all jobs in the group to the LOW_GROUP consumer group.
Dropping Job Classes
SQL> begin
dbms_scheduler.drop_job_class(
'LOW_PRIORITY_CLASS, HIGH_PRIORITY_CLASS');
end;
/
 
 
Using Scheduler Programs
A program defines the action that will occur when a job runs. It can be a PL/SQL block, a stored procedure, or an operating system executable.
Program Attributes
PROGRAM_NAME
PROGRAM_TYPE
PROGRAM_ACTION
NUMBER_OF_ARGUMENTS
Creating Programs
SQL> begin
dbms_scheduler.create_program(
program_name => 'STATS_PROGRAM',
program_type => 'STORED_PROCEDURE',
program_action => 'DBMS_STATS.GATHER_SCHEMA_STATS',
number_of_arguments => 1,
comments => 'Gather stats for a schema');
end;
/
参数：the name of the schema
The ARGUMENT_NAME parameter is available, but is completely optional.
SQL> begin
dbms_scheduler.define_program_argument(
program_name => 'STATS_PROGRAM',
argument_position => 1,
argument_type => 'VARCHAR2');
end;
/
Arguments can be dropped from programs as well.
SQL> begin
dbms_scheduler.drop_program_argument(
program_name => 'STATS_PROGRAM',
argument_position => 1,
end;
/
or its name:
SQL> begin
dbms_scheduler.drop_program_argument(
program_name => 'STATS_PROGRAM',
argument_name => 'SCHEMA_NAME',
end;
/

Dropping Programs
Program objects can be dropped through the use of the DBMS_SCHEDULER.DROP_PROGRAM procedure. This procedure removes the procedure entirely from the database. If existing job definitions include the program that you are attempting to drop, the drop will fail. However, if you
set the FORCE parameter to TRUE, the program will be dropped and the referencing jobs will become disabled.
The following example drops the STATS_PROGRAM program and disables any referencing jobs:
SQL> begin
dbms_scheduler.drop_program (
program_name => 'STATS_PROGRAM',
force => TRUE
end;
/
 
 
Using Schedules
Schedules define not only when a job will start, but also how often the job will be repeated.
Schedule Attributes
SCHEDULE_NAME
START_DATE
END_DATE
REPEAT_INTERVAL
COMMENTS
Creating Schedules
By default, schedules are created with access to the PUBLIC role.
To create a schedule that repeats every night at 8:00 P.M., use the following example:
SQL> begin
dbms_scheduler.create_schedule(
schedule_name => 'NIGHTLY_8_SCHEDULE',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=20',
comments => 'Runs nightly at 8:00 PM');
end;
/
Setting Repeat Intervals
calendaring syntax 日程语法 书404 英文书551页
TABLE 1 2 . 4 Calendaring Syntax Examples
Goal Expression
Every Monday FREQ=WEEKLY; BYDAY=MON;
Every other Monday FREQ=WEEKLY; BYDAY=MON; INTERVAL=2;
Last day of each month FREQ=MONTHLY; BYMONTHDAY=-1;
Every January 7 FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=7;
Second Wednesday of each month FREQ=MONTHLY; BYDAY=2WED;
Every hour FREQ=HOURLY;
Every 4 hours FREQ=HOURLY; INTERVAL=4;
Hourly on the first day of each month FREQ=HOURLY; BYMONTHDAY=1;
15th day of every other month FREQ=MONTHLY; BYMONTHDAY=15; INTERVAL=2
Testing Repeat Intervals
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
CALENDAR_STRING
START_DATE
RETURN_DATE_AFTER
NEXT_RUN_DATE
 
 
SQL> DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := to_timestamp_tz(
'10-OCT-2004 10:00:00','DD-MON-YYYY HH24:MI:SS');
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15',
start_date, null, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' ||
next_run_date);
END;
/
next_run_date: 15-OCT-04 10.00.00.000000 AM
 
 
The procedure returns only a single value for NEXT_RUN_DATE, but you may want to see more
than one. If so, you can use the SQL shown here:
SQL> DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := to_timestamp_tz('10-OCT-2004 10:00:00','DD-MON-YYYY HH24:MI:SS');
return_date_after := start_date;
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15',start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
return_date_after := next_run_date;
END LOOP;
END;
 
 

Using Scheduler Windows
Unlike most of the other Scheduler objects that you've seen so far, windows are created in the SYS schema.
Creating Windows
The parameters for the CREATE_WINDOW procedure are described here:
WINDOW_NAME
RESOURCE_PLAN
START_DATE
DURATION
SCHEDULE_NAME
REPEAT_INTERVAL
END_DATE
WINDOW_PRIORITY
COMMENTS
To create a window that activates the DAY_PLAN resource plan and uses a schedule named WORK_HOURS_SCHEDULE, see the following example:
SQL> begin
dbms_scheduler.create_window (
window_name => 'WORK_HOURS_WINDOW',
resource_plan => 'DAY_PLAN',
schedule_name => 'WORK_HOURS_SCHEDULE',
duration => INTERVAL '10' HOUR,
window_priority => 'HIGH');
end;
/
PL/SQL procedure successfully completed.
 
 
Opening and Closing Windows
There are two distinct ways that a window can be opened. The first is based on the window's schedule. The second is they can be opened manually by using the DBMS_SCHEDULER.OPEN_WINDOW procedure.
When opening a window manually, you can specify a new duration for the window to remain open; otherwise it will remain open for the duration defined when the window was created.
The OPEN_WINDOW procedure opens a window independent of its schedule. The associated resource plan is enabled immediately, and currently executing jobs are subjected to the change in resource plan
SQL> begin
dbms_scheduler.open_window (
window_name => 'WORK_HOURS_WINDOW',
duration => INTERVAL '20' MINUTE,
force => TRUE);
end;
/
This example forces the WORK_HOURS_WINDOW to be opened and any current window to close.
The new window will remain open for a duration of 20 minutes
 
 
SQL> begin
dbms_scheduler.close_window (
window_name => 'WORK_HOURS_WINDOW');
end;
/
 
 
Window Logging
For example, use the following query to view window log entries:
SQL> select log_id, trunc(log_date) log_date,window_name, operation
from dba_scheduler_window_log;
For each CLOSE operation logged in the DBA_SCHEDULER_WINDOW_LOG view, there will be an associated record in the DBA_SCHEDULER_WINDOW_DETAILS view, as shown here:
SQL> select log_id, trunc(log_date) log_date,window_name, actual_duration
from dba_scheduler_window_details;
 
 
Purging Logs
Scheduler job and window logs will be automatically purged based on the setting of the LOG_HISTORY attribute of the Scheduler itself.
SQL> begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE( 'LOG_HISTORY','60');
end;
/
By default, this procedure sets the history retention period for both Scheduler window logs and Scheduler job logs. To set only one, the WHICH_LOG parameter may be included to specify either WINDOW_LOG or JOB_LOG.
 
 
Using Scheduler Views
To see information on completed instances of a job, use the example shown here:
SQL> select job_name, status, error# from dba_scheduler_job_run_details
where job_name = 'FAIL_JOB';
To see the current state of all jobs, use the following example:
SQL> select job_name, state from dba_scheduler_jobs;
 
 
Scheduler Views Available p412页
TABLE 1 2 . 6 Scheduler Views Available
View Description
*_SCHEDULER_SCHEDULES Shows information on all defined schedules.
*_SCHEDULER_PROGRAMS Shows information on all defined programs.
*_SCHEDULER_PROGRAM_ARGUMENTS Shows all registered program arguments, and the
default values if they exist.
*_SCHEDULER_JOBS Shows all defined jobs, both enabled and disabled.
*_SCHEDULER_GLOBAL_ATTRIBUTE Shows the current values of all Scheduler attributes.
*_SCHEDULER_JOB_ARGUMENTS Shows the arguments for all defined jobs.
*_SCHEDULER_JOB_CLASSES Shows information on all defined job classes.
*_SCHEDULER_WINDOWS Shows information about all defined windows.
*_SCHEDULER_JOB_RUN_DETAILS Shows information about all completed (failed or
successful) job runs.
*_SCHEDULER_WINDOW_GROUPS Shows information about all window groups.
*_SCHEDULER_WINGROUP_MEMBERS Shows the members of all window groups.
*_SCHEDULER_RUNNING_JOBS Shows the state information on all jobs that are
currently being run.
 
 
To view windows and their next start dates, the following SQL can be used:
SQL> select window_name, next_start_date from dba_scheduler_windows;
 
 
The DBA_SCHEDULER_JOB_LOG view can be used to view log entries for previously executed
jobs, as shown here:
SQL> select log_id, trunc(log_date) log_date, owner, job_name, operation
from dba_scheduler_job_log;
 
 
……
You also learned that, in a RAC environment, each instance has its own job coordinator, and
the job coordinators have the ability to communicate with each other to keep information current.
You learned that a RAC environment will still have only one job table that is shared by all
the instances. You also learned that jobs can be assigned to a service, as opposed to an instance,
ensuring that the job can be run by a different node if an instance is down.
……