数据库管理-第九十一期 19c OCM之路-第三堂（20230711）

第九十一期 19c OCM之路-第三堂

来到第三堂了，Data management数据管理。从这里开始才是我认为的OCM最难的两门考试。

考点0：破坏 BOOM！

一般来说从这一堂开始就会有各种破坏了，除了一般的表空间损坏通过rman备份恢复以外，其实标准操作可以使用下面的流程：

1. 如遇到控制文件丢失，从副本恢复并启动数据库到mount状态（也可能只是破坏了某个PDB的某个文件，则直接第二步）
2. 紧接着用rman进行快速修复：

rman target /
	list failure;
	advise failure;
	repair failure;

当然如果不能mount或者确实不想动脑，请从冷备恢复。这个隐藏考点可能出现在本堂及下一堂（both也是有可能的）。

考点1：Memory management 内存管理

调整pdbprod2的pga初始大小为10MB，上限为50MB；SGA大小为250MB：

sqlplus sys/oracle@pdbprod2 as sysdba
	alter system set pga_aggregate_target=10m;
	alter system set pga_aggregate_limit=50m;
	alter system set sga_target=250m;	

考点2：Plugging in tablespaces and creating external tables 插入表空间和创建外部表

这里基本上参考11g、12c考试中的跨平台表空间导入和外部表创建：
1.将原平台为**Solaris[tm] OE (64-bit)**的导出表空间文件为/home/oracle/scripts/EXAMPLE，dump文件为example.dmp导入至pdbprod2中（包含多个用户需要通过提供的脚本创建）：

sqlplus sys/oracle@pdbprod2 as sysdba
	start /home/oracle/scripts/xx.sql -- create users
	create directroy ext as '/home/oracle/scripts';
	grant all on directory ext to public;

rman target sys/oracle@pdbprod2
	convert datafile '/home/oracle/scripts/EXAMPLE' from platform "Solaris[tm] OE (64-bit)" format '/u01/app/oracle/oradata/PRODCDB/PDBPROD2/example01.dbf';

impdp system/oracle@pdbprod1 dumpfile=example.dmp directory=ext transport_datafiles=/u01/app/oracle/oradata/PRODCDB/PDBPROD2/example01.dbf

sqlplus sys/oracle@pdbprod2 as sysdba
	alter tablespace example read write;

2. 创建外部表，这个题目记得不是太清楚了，需要使用/home/oracle/scripts/uncompress.sh脚本，下面只是大概操作流程：

sqlplus sys/oracle@prod4 as sysdba
	chopt disable dv
	create table prod_master (empno number,ename varahcr2(10),hiredate date,deptno number);
	create direcrtory ext as '/home/oracle/scripts';

prod4
sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY

生成文件修改表名，ACCESS PARAMETERS中添加PREPROCESSOR ext:'uncompress.sh'
chmod a+x uncompress.sh

sqlplus sh/sh@prod4 as sysdba
	drop table prod_master;
	start sh.sql

考点3：Manage tablespaces 管理表空间

prod4配置加密表空间

mkdir -p /u01/app/oracle/admin/PROD4/wallet
mkstore -wrl /u01/app/oracle/admin/PROD4/wallet -create
cp -r /u01/app/oracle/admin/PROD4/wallet /backup/path #备份

vim /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(MOTHOD_DATA=(DIRECTORY=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora)))

sqlplus sys/oracle@prod4 as sysdba
	alter system set encryotion key identified by "oracle123";
	shut immediate
	startup mount;
	alter system set encryption wallet open identified by "oracle123"
	alter database open;
	create tablespace sf_data datafile '/u01/app/oracle/oradata/PROD4/sf_data.dbf' size 10m autoextend on encryption default storage(encrypt);
	alter table hr.employees move tablespace sf_data;

其他表空间创建相对则比较简单了。

考点4：Managing Partitioning 管理分区

在pdbprod1中，sh用户下创建基于sales的分区表sales_history，要求根据1998、1999、2000、2001年份分区，分区名称分别为SAL1、SAL2、SAL3、SAL4，time_id是分区键：

sqlplus sys/oracle@pdbprod2 as sysdba
	create table sales_history
	(
	prod_id number not null,
	cust_id number not null,
	time_id date not null,
	channel_id number not null,
	promo_id number not null,
	quantity_sold number(10,2) not null,
	amount_sold number(10,2) not null
	)
	partition by range(time_id)
	interval(numtoyminterval(1,'YEAR'))
	(
	partition SAL1 values less than (to_date('1999-1-1','yyyy-mm-dd')),
	partition SAL2 values less than (to_date('2000-1-1','yyyy-mm-dd')),
	partition SAL3 values less than (to_date('2001-1-1','yyyy-mm-dd')),
	partition SAL4 values less than (to_date('2002-1-1','yyyy-mm-dd'))
	);
	inset into sh.sales_history select * from sh.sales;
	commit;

考点5：Configuration that allows searching of previous versions of data 配置允许查询之前版本的数据

pdbprod1中设置结果缓存为15MB，并确保sh.sales结果缓存：

sqlplus sys/oracle@pdbprod1 as sysdba
	alter system set result_cache_max_size=15m;
	alter table sh.sales result_cache(mode force);

考点6：Star query optimization 星形查询优化

pdbprod1中开启星形查询转换：

sqlplus sys/oracle@pdbprod1 as sysdba
	-- 在关联查询涉及的表中的等值列上建立位图索引
	-- eg. create bitmap b_ind_time_id on sales(time_id) local;
	alter system set star_transformation_enabled=true;

考点7：Managing materialized views 管理物化视图

pdbprod1中重建物化视图sh.prod_mv，支持重写：

sqlplus sys/oracle@pdbprod1 as sysdba
	select dbms_metadata.get_dd('MATERIALIZED_VIEW','PROD_ MV','SH') from dual;
	
	--create materialized view sh.prod_mv as select time_id,prod_subcategory,sum(unit_ cost),count(unit _cost),count(*) from costs c,products p where c.prod_ id=p.prod_id group by  time_id,prod_subcategory;

conn sys/oracle@pdbprod1 as sysdba
	grant create any directory,advisor to sh;
conn sh/sh
	drop materialized view log on costs;
	drop materialized view log on products;
	create or replace directory d_mv as '/home/oracle';
	grant read,write on directory d_mv to public;
	
	declare
	tname varchar2(30);
	mv varchar2(2000);
	begin
	tname:='prod_mv';
	mv:='create materialized view sh.prod_mv refresh force on demand as select time_id,prod_subcategory,sum(unit_ cost),count(unit _cost),count(*) from costs c,products p where c.prod_ id=p.prod_id group by  time_id,prod_subcategory;'
	dbms_advisor.tune_mview(tname,mv);
	dbms_advisor.create_file(dbms_advisor.get_task_script(tname),'D_MV','mvcreate.sql');
end;
/
-- 修改mvcreate.sql: disable => enable
	show parameter rewrite
	alter system set query_rewrite_enabled=true;
	alter system set query_rewrite_integrity=trusted/enforced;
	-- upgrade的话似乎只考这俩参数，嘿嘿！

conn sh/sh@pdbprod1
	drop materialized view prod_mv;
	start mvcreate.sql

考点8：Auditing 审计

将PROD4审计文件位置调整至$ORACLE_HOME/dbs，同时审计级别改为DB：

sqlplus sys/oracle@prod4 as sysdba
	alter system set audit_file_dest='/u01/app/oracle/product/19.0.0/dbhome_1/dbs' referred;
	alter system set audit_trail='DB' scope=spfile;
	shut immediate
	startup

考点9：Additional configuration 其他配置

1. pdbprod1中，sh.sales表默认开启并行，且开启自动并行仅当语句对象没有指定数据中的并行度时才生效。

sqlplus sys/oracle@pdbprod1 as sysdba
	alter table sh.sales parallel;
	alter system set parallel_degree_policy=limited;

2. 其他并行

alter session force parallel query;
alter session enable parallel dml;
alter index xxx parallel 4;
select /*+ PARALLEL(employees 4) PARALLEL(departmemts 4) */
insert /*+ append parallel(t3,2) */
delete /*+ parallel(t1,2) */
......

3. 其他的暂时不知

总结

第三堂搞定，后面继续，这里对比了下直考核升级，升级真的还是少了很多操作。
老规矩，知道写了些啥。