第九十三期 19c OCM之路-第四堂（01）（20230719）
距離上一期19c OCM之路已經(jīng)過(guò)去了整整8天了，這中間發(fā)生的事情詳見(jiàn)第九十二期。本期來(lái)到第四堂Performance management性能管理，但是一開(kāi)始需要把上一堂的一些內(nèi)容做一些調(diào)整和補(bǔ)遺。
第三堂調(diào)整與補(bǔ)遺
-
Additional configuration 其他配置
在上一堂的其他配置中，我是把并行相關(guān)內(nèi)容放過(guò)去了，但是細(xì)度考點(diǎn)，新的考試內(nèi)容是把并行放在了第四堂，因此放在那邊不大合適。關(guān)于其他可能涉及的考試內(nèi)容在這里補(bǔ)充一下：
-- 使用安全文件
create table profiles (id number,first_name varchar2(40),last_name varchar2(80),profile_info clob) lob(profile_info) store as securefile (tablespace sf_data compress low);
alter system set db_securefile=permitted;
-- 開(kāi)啟表閃回（根據(jù)題目要求）
sqlplus sys/oracle@pdbprod1 as sysdba
create tablespace fra_tbs datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/fra_tbs01.dbf' size 20m autoextend on;
create user fra_admin identified by oracle default tablespace fra_tbs quota unlimited on fra_tbs account unlock;
grant dba,flashback archive administer to fra_admin;
sqlplus fra_admin/oracle@pdbprod1
create flashback archive fra1 tablespace fra_tbs retention 1 year;
alter table sh.promotions flashback archive fra1;
- 考點(diǎn)4：Managing Partitioning 管理分區(qū)
這里可能遇到外鍵分區(qū)表：
原表：
create table orders
(
order_id number(6),
order_date date,
order_mode varchar2(10),
customer_id number(8,2),
order_status number(2),
order_total number(8,2),
sales_rep_id number(6),
constraint order_id_pk primary key(order_id)
)
partition by range(order_date)
(
partition 01_2005 values less than (to_date('01-APR-2005','dd-mon-yyyy')),
partition 02_2005 values less than (to_date('01-JUL-2005','dd-mon-yyyy'),
partition 03_2005 values less than (to_date('01-0CT-2005','dd-mon-yyyy'),
partition 04_2005 values less than (to_date('01-JAN-2006','dd-mon-yyy')
);
-- 外鍵分區(qū)表
create table order_items
(
order_id number(12) not null,
line_item_id number(3),
product _id number(6),
unit_price number(8,2),
quantity number(8),
constraint order_items_fk foreign key(order_id),
references orders(order_id)
)
partition by reference(order_items_fk);
- 考點(diǎn)8：Auditing 審計(jì)
除了關(guān)于審計(jì)的數(shù)據(jù)庫(kù)參數(shù)調(diào)整以為，應(yīng)該還會(huì)有FGA（Fine-Grained Audit，細(xì)粒度審計(jì)）的相關(guān)考試內(nèi)容：
BEGIN
dbms_fga.add_policy(
object_schema=>'HR',
object_name=>'EMPOLYEES',
policy_name=>'SALARY',
audit_condition=>'salary is not null and commission_pct is not null',
audit_column=>'salary,commission_pct',
statement_types=>'select',
audit_trail=>DBMS_FGA.DB_EXTENDED,
audit_column_opts=>DBMS_FGA.ALL_COLUMNS
);
END;
/
官方文檔路徑如下：
Oracle Database 19c
PL/SQL Packages and Types Reference
\qquad
-> 69 DBMS_FGA
\qquad\qquad
-> 69.3 Summary of DBMS_FGA Subprograms
\qquad\qquad\qquad
-> 69.3.1 ADD_POLICY Procedure
考點(diǎn)0 將host01加入EMCC管理
本堂后后面的一些操作是可以通過(guò)EMCC來(lái)操作的，因此需要將host01加入EMCC管理，具體操作這里不再贅述，請(qǐng)前往數(shù)據(jù)庫(kù)管理-第八十一期 保姆級(jí)EMCC搭建教程（20230606）查看。
考點(diǎn)1：Create synonym 創(chuàng)建同義詞：
CREATE [OR REPLACE] [PUBLIC] SYNONYM [當(dāng)前用戶(hù).]synonym_name
FOR [其他用戶(hù).]object_name;
create public synonym sales for sh.sale;
考點(diǎn)2：Configure Resource Manager 配置資源管理
官方文檔路徑如下：
Oracle Database 19c
Database Administrator’s Guide
-> Oracle? Database
\qquad
-> Part IV Database Resource Management and Task Scheduling
\qquad\qquad
-> 27 Managing Resources with Oracle Database Resource Manager
\qquad\qquad\qquad
-> 27.1 About Oracle Database Resource Manager
\qquad\qquad\qquad\qquad
->27.1.3 About Resource Manager Administration Privileges
-> Oracle? Multitenant
\qquad
-> Part V Using Oracle Features in a Multitenant Environment
\qquad\qquad
-> 22 Using Oracle Resource Manager for PDBs
\qquad\qquad\qquad
-> Managing CDB Resource Plans
\qquad\qquad\qquad\qquad
-> Creating a CDB Resource Plan for Managing PDBs
->Oracle? Database
\qquad
->Part IV Database Resource Management and Task Scheduling
\qquad\qquad
->27 Managing Resources with Oracle Database Resource Manager
\qquad\qquad\qquad
->27.5 Creating a Complex Resource Plan
\qquad\qquad\qquad\qquad
->27.5.3 Creating Resource Consumer Groups
\qquad\qquad\qquad\qquad
->27.5.5 Creating a Resource Plan
\qquad\qquad\qquad\qquad
->27.5.6 Creating Resource Plan Directives
這里將12c OCM升級(jí)試的考點(diǎn)引入，建議使用EMCC來(lái)做：文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-597016.html
- Assign SYSTEM user as a resource adminstrator privileges.
- Create resource plans to DAYTIME for PRODCDB and active it
pdbprod1 get 6 parts shared resource of CDB
pdbprod2 get 3 parts shared resource of CDB
pdbprod3 get 1 parts shared resource of CDB
PDB cannot get the extra 75% of availiable CPU time
PDB cannot get the extra 50% parallel_servers_target
可參考Oracle數(shù)據(jù)庫(kù)管理每周一例（12.2,18c,19c） 2020-06-28 - Create two consumer group OLTP and OLAP in the pdbprod1, use comments indicated the group used.
- Create a resource management plan in pdbprod1
a) Resource plan name: DAYTIMEPDB1
b) For OLTP group cannot be allowed more than 25 active sessions. If 26 users try to log on, wait 90 seconds later, this connection will be terminated
c) OLAP group has the maximum number of active sessions to 8. If you need extra eight sessions, the connection will be terminated after 150 seconds
d) In OLTP group, an accepted longest query execution time is 5 seconds, if the query estimate exceeds 5 seconds, the query should be eliminated
e) The maximum undo size for OLTP group is 350MB
f)the CPU ratio for OLTP, OLAP and OTHER_GROUPS are 50, 40 and 10 respectively
g) OLAP group should limit the degree of paralleism at 30
h) If the session of OLAP group exceeds 8000 I/O or data transmission over 2000MB, it should be temporary put into LOW_GROUP group. When the call is complete, the session is returned back to the original group
i) Active the plan
--題目1
sqlplus sys/oracle@prodcdb as sysdba
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME =>'SYSTEM',
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE);
END;
/
--題目2
sqlplus sys/oracle@prodcdb as sysdba
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => 'DAYTIME',
comment => 'CDB resource plan');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'DAYTIME',
pluggable_database => 'pdbprod1',
shares => 6,
utilization_limit => 75,
parallel_server_limit => 50);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'DAYTIME',
pluggable_database => 'pdbprod2',
shares => 3,
utilization_limit => 75,
parallel_server_limit => 50);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'DAYTIME',
pluggable_database => 'pdbprod3',
shares => 1,
utilization_limit => 75,
parallel_server_limit => 50);
END;
/
-- exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAYTIME';
--題目3
sqlplus sys/oracle@pdbprod1 as sysdba
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'OLTP',
COMMENT => 'OLTP applications');
END;
/
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'OLAP',
COMMENT => 'OLAP applications');
END;
/
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
--題目4
sqlplus sys/oracle@pdbprod1 as sysdba
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'DAYTIMEPDB1', comment => '',max_iops => NULL,max_mbps => NULL );
dbms_resource_manager.create_plan_directive(
plan => 'DAYTIMEPDB1',
group_or_subplan => 'OLAP',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 40,
mgmt_p1 => NULL, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 30 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => 8000 ,
switch_io_megabytes => 2000 ,
active_sess_pool_p1 => 8,
queueing_p1 => 150,
switch_group => 'LOW_GROUP',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => true
);
dbms_resource_manager.create_plan_directive(
plan => 'DAYTIMEPDB1',
group_or_subplan => 'OLTP',
comment => '',
switch_elapsed_time => 5,
max_utilization_limit => 50,
mgmt_p1 => NULL, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => NULL ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => 25,
queueing_p1 => 90,
switch_group => 'CANCEL_SQL',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => 358400 ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => 'DAYTIMEPDB1',
group_or_subplan => 'OTHER_GROUPS',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 10,
mgmt_p1 => NULL, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => NULL ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => NULL,
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.submit_pending_area();
END;
/
alter system set resource_manager_plan='DAYTIMEPDB1';
總結(jié)
本堂考試考點(diǎn)2的內(nèi)容著實(shí)有點(diǎn)多，本堂剩余內(nèi)容放在后面。
老規(guī)矩，知道寫(xiě)了些啥。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-597016.html
到了這里，關(guān)于數(shù)據(jù)庫(kù)管理-第九十三期 19c OCM之路-第四堂（01）（20230719）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
