公司刚做网站在那里找图片做,前端转行可以找啥工作,wordpress视频主题,购物帮–做特惠的导购网站第九十三期 19c OCM之路-第四堂#xff08;01#xff09;#xff08;20230719#xff09;
距离上一期19c OCM之路已经过去了整整8天了#xff0c;这中间发生的事情详见第九十二期。本期来到第四堂Performance management性能管理#xff0c;但是一开始需要把上一堂的一些…第九十三期 19c OCM之路-第四堂0120230719
距离上一期19c OCM之路已经过去了整整8天了这中间发生的事情详见第九十二期。本期来到第四堂Performance management性能管理但是一开始需要把上一堂的一些内容做一些调整和补遗。
第三堂调整与补遗
Additional configuration 其他配置 在上一堂的其他配置中我是把并行相关内容放过去了但是细度考点新的考试内容是把并行放在了第四堂因此放在那边不大合适。关于其他可能涉及的考试内容在这里补充一下
-- 使用安全文件
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_securefilepermitted;-- 开启表闪回根据题目要求
sqlplus sys/oraclepdbprod1 as sysdbacreate 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/oraclepdbprod1create flashback archive fra1 tablespace fra_tbs retention 1 year;alter table sh.promotions flashback archive fra1;考点4Managing Partitioning 管理分区 这里可能遇到外键分区表
原表
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)
);-- 外键分区表
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);考点8Auditing 审计 除了关于审计的数据库参数调整以为应该还会有FGAFine-Grained Audit细粒度审计的相关考试内容
BEGINdbms_fga.add_policy(object_schemaHR,object_nameEMPOLYEES,policy_nameSALARY,audit_conditionsalary is not null and commission_pct is not null,audit_columnsalary,commission_pct,statement_typesselect,audit_trailDBMS_FGA.DB_EXTENDED,audit_column_optsDBMS_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
考点0 将host01加入EMCC管理
本堂后后面的一些操作是可以通过EMCC来操作的因此需要将host01加入EMCC管理具体操作这里不再赘述请前往数据库管理-第八十一期 保姆级EMCC搭建教程20230606查看。
考点1Create synonym 创建同义词
CREATE [OR REPLACE] [PUBLIC] SYNONYM [当前用户.]synonym_name
FOR [其他用户.]object_name;create public synonym sales for sh.sale;考点2Configure 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升级试的考点引入建议使用EMCC来做
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数据库管理每周一例12.2,18c,19c 2020-06-28Create 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/oracleprodcdb as sysdba
BEGINDBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(GRANTEE_NAME SYSTEM,PRIVILEGE_NAME ADMINISTER_RESOURCE_MANAGER,ADMIN_OPTION FALSE);
END;
/--题目2
sqlplus sys/oracleprodcdb as sysdba
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(plan DAYTIME,comment CDB resource plan);
END;
/BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(plan DAYTIME, pluggable_database pdbprod1, shares 6, utilization_limit 75,parallel_server_limit 50);
END;
/BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(plan DAYTIME, pluggable_database pdbprod2, shares 3, utilization_limit 75,parallel_server_limit 50);
END;
/BEGINDBMS_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/oraclepdbprod1 as sysdba
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();BEGINDBMS_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();BEGINDBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP OLAP,COMMENT OLAP applications);
END;
/
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();--题目4
sqlplus sys/oraclepdbprod1 as sysdba
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();BEGINDBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN DAYTIMEPDB1);
END;
/
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();BEGINDBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN DAYTIMEPDB1, GROUP_OR_SUBPLAN OLTP,COMMENT OLTP group,MGMT_P1 50,ACTIVE_SESS_POOL_P1 25,QUEUEING_P1 90,SWITCH_GROUP CANCEL_SQL,SWITCH_TIME 5,MAX_EST_EXEC_TIME 5,UNDO_POOL 358400);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN DAYTIMEPDB1, GROUP_OR_SUBPLAN OLAP,COMMENT OLAP group,MGMT_P1 40,PARALLEL_DEGREE_LIMIT_P1 30,ACTIVE_SESS_POOL_P1 8,QUEUEING_P1 150,SWITCH_GROUP LOW_GROUP,SWITCH_IO_MEGABYTES 2000,SWITCH_IO_REQS 8000,SWITCH_FOR_CALL TRUE);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN DAYTIMEPDB1, GROUP_OR_SUBPLAN OTHER_GROUPS,COMMENT OTHER group,MGMT_P1 10);DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()
END;
/alter system set resource_manager_planDAYTIMEPDB1;总结
本堂考试考点2的内容着实有点多本堂剩余内容放在后面。 老规矩知道写了些啥。
