SECTION4数据仓库.docx

1 SYSTEM系统表空间故障处理问隐含题目，返回考场时发现PROD数据库实例已经异常停止，需定位故障并进行恢复故障模拟过程1 将数据库以ABORT主方式关闭Shutdown abort；ALERT日志记录的信息如下：Shutting down instance(abort)2到SYSTEM表空间所在的目录删除SYSTEM表空间对应的数据文件Cd /u01/app/答：OLTP大量的事务，绑定变量，小事务短事务，相同的SQL语句密集处理绑定变量窥视：第一次SQL执行完所生成的执行计划，后续的SQL也跟着使用这个执行计划跑的，数据仓库要避免这种现象发生，少用绑定变量，禁用绑定变量.OLAP：SQL解析时间特别短，但执行时间特别长OLTP和OLAP不能在同一库中视图是一段SQL语句的定义，该视图存储在DD里物化视图要把显示出来的视图保存下来物化视图全部刷亲新：把视图全部跑一变FAST：基于日志的，同步变化叫做快速刷新或增量刷新SQLLDR是实现加载数据的工具，数据库加载的最高境界是不加载，使用外部表读数据启动到MOUNT状态,然后读取控制文件信息，控制文件读取备份信息1.1 模拟系统表空间损坏1.1.1 查看SYSTEM 表空间对应的数据文件信息select file_name,tablespace_name,status from dba_data_files where tablespace_name=SYSTEM;1.1.2 删除SYSTEM 表空间对应的数据文件rm rf 1.1.3 停启数据库过程中会发现报错信息分析过程启动时报错信息Sqlplus / as sysdbaConnect to an idle instance;1.2 故障恢复1.2.1 启动到MOUNT状态Startup; Database mount.Ora-01157 cannot identify /lock datafile 1 see dbwr trace fileOra-01110 datafile 1.处理过程：将数据启动到MOUNT状态Shutdown immediate;Startup mount;1.2.2 连接到恢复目录数据库Rman target sys/oraclPROD catalog rc_admin/rc_adminemrep1.2.3 还原RESTORE一号数据文件Restore datafile 1;1.2.4 恢复RECOVER一号数据文件Recover datafile 11.2.5 打开数据库Alter database open;1.2.6 确认SYSTEM 表空间状态select file_name,tablespace_name,status from dba_data_files where tablespace_name=SYSTEM;2 去重物化视图DISINCT不支持快速刷新的问：创建具有快速刷新功能的物化视图，要求物化视图能消除表中重复数据样本脚本存放目录/home/oracle/scripts，内容如下：Select distinct country_id,country_name from country;Books-data warehousing guide -8 basic materialized view 和9 advanced materialized viewBooks- sql reference-检索关键字”create library”和”create materialized view”Books-pl/sql packages and types reference -61 dbms_mview创建automaticallyRefresh的物化视图:selectdistinctprod_name,prod_ducts创建manuallyRefresh的物化视图:selectsum(prod_list_price-prod_min_price),count(prod_category),prod_categoryfromproductsgroupbyprod_categorycreatematerializedviewlogonproductswithsequence,rowid(prod_name,prod_category,prod_list_price,prod_min_price)includingnewvalues;creatematerializedviewproducts_mvbuildimmediaterefreshfastoncommitenablequeryrewriteasselectprod_name,prod_category,count(*)fromproductsgroupbyprod_name,prod_category;creatematerializedviewproducts_mv1asselectsum(prod_list_price-prod_min_price),count(prod_category),prod_categoryfromproductsgroupbyprod_category; 2.1 初始化实验环境SQL create user sec identified by sec;User created.SQL grant connect,resource,create materialized view to sec;Grant succeeded.SQL conn sec/sec;Connected.SQL create table t(x int,y int,z int);Table created.SQL Insert into t values(1,1,1);Insert into t values(2,2,2);Insert into t values(3,3,3);Insert into t values(4,4,4);Insert into t values(5,5,5);Insert into t values(6,6,6);Insert into t values(6,6,6);1 row created.SQL 1 row created.SQL 1 row created.SQL 1 row created.SQL 1 row created.SQL 1 row created.SQL 1 row created.SQL SQL commit;Commit complete.2.2 两种去重物化视图创建的实现方法2.2.1 使用GROUP BY 方法实现物化视图日志的作用是记录基表的变化，物化视图一定和基表在一起的，使用GROUP BY方式才支持增量刷新和快速刷新，基于增量刷新需要物化视图日志，物化视图日志和基表在一个库上 Sequence：序列，记录所有操作的顺序，在基表当中做UDI操作，然后做UPDATE，在做UPDATE，除了SELECT和INSERT不记录操作顺序，如果没有SEQUENCE那么物化视图日志就少一列，这样就会减少开销，但是有大量的并行的DML操作，那么就必须有SEQUENCEROWID：更改的哪一行，要么通过基于ROWID，要么通过基于主键确定更改的哪一列（默认是主建）Including new values：操作的时候记录先前的记录，GROUP BY 方式必须保留之前的数值，并且匹配比对到底这些信息的GROUP BY 结果是否是最终去重的结果。无论DUI只要COMMIT就会把物化视图日志更新到物化视图，如果在生产环境的话，一般不用COMMIT选项，否是频繁的UDI那么就会频繁的更新物化视图。enable query rewrite如果这句话没写，那么ORACLE不会考虑去执行物化视图中的记录，查询重写在物化视图创建的时候做 的group by方式实现快速刷新，必须加including new values，通过including new values的DML操作之前的值做为参考，来判是否有重复值如果想使用查询重写的特性，建立物化视图必须显示的写出enable query rewrite选项；查询的结果集必须落在物化视图的内部；只有支持CBO的优化模式；启用SESSION级和SYSTEM级参数query_rewrite_enabled的查询重写的特性；比对走基表的代价低还是走物化视图代价低。这些是必要条件。 （1）创建物化视图日志 Drop materialized view log on t;Create materialized view log on t with sequence ,rowed(x,y,z) including new values;SQL create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;验证：SQL select * from t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 6 67 rows selected.SQL select * from mv_t order by x; X Y Z COUNT(*)- - - - 1 1 1 1 2 2 2 1 3 3 3 1 4 4 4 1 5 5 5 1 6 6 6 26 rows selected.SQL insert into t values(7,7,7);1 row created.SQL select * from t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 6 6 7 7 78 rows selected.SQL select * from mv_t order by x; X Y Z COUNT(*)- - - - 1 1 1 1 2 2 2 1 3 3 3 1 4 4 4 1 5 5 5 1 6 6 6 26 rows selected.SQL commit;Commit complete.SQL select * from t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 6 6 7 7 78 rows selected.SQL select * from mv_t order by x; X Y Z COUNT(*)- - - - 1 1 1 1 2 2 2 1 3 3 3 1 4 4 4 1 5 5 5 1 6 6 6 2 7 7 7 17 rows selected.（2）创建物化视图 注：若查询不包含COUNT（*）关键字，仅支持INSERT操作，对基本的DELETE和UPDATE操作将不会同步刷新到物化视图中（可以手动完全刷新解决此问题）称作insert-only materialized view。 Drop materialized view mv_t; Create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;注释：查看物化视图的数据字典:user_mviews物化视图日志结构信息解释：Desc mlog$_t;前几个是表的几个列M_ROW$这是ROWIDSEQUENCE$ SEQUENCE是序列，建立视图时不加sequence时这一列就没有SNAPTIME$操作的时间DMLTYPE$记录操作的类型UDLOLD_NEW$标识是新数据还是旧数据，只有加INCLUDE NEW VALUES才会记录原来的数据CHANGE_VECTOR$修改矢量2.2.2 使用distinct方法实现题目中给出的样本内容是以distinct方式给出的，不使用此方法（）创建物化视图日志Drop materialized view log on t;Create materialized view log on t with rowid (x,y,z) including new values;Materialized view log created.创建物化视图SQL create materialized view mv_t as select distinct x,y,z from t;Materialized view created.支持全部刷新SQL exec dbms_mview.refresh(mv_t,c);PL/SQL procedure successfully completed.DISTINCT不支持快速刷新SQL exec dbms_mview.refresh(mv_t,f);BEGIN dbms_mview.refresh(mv_t,f); END;*ERROR at line 1:ORA-12004: REFRESH FAST cannot be used for materialized view SEC.MV_TORA-06512: at SYS.DBMS_SNAPSHOT, line 2255ORA-06512: at SYS.DBMS_SNAPSHOT, line 2461ORA-06512: at SYS.DBMS_SNAPSHOT, line 2430ORA-06512: at line 1验证结果：SQL select * from t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 6 6 7 7 78 rows selected.SQL select * from mv_t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 77 rows selected.SQL insert into t values(8,8,8);1 row created.SQL select * from t; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 6 6 7 7 7 8 8 89 rows selected.SQL select * from mv_t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 77 rows selected.SQL commit;Commit complete.SQL select * from t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 6 6 6 7 7 7 8 8 89 rows selected.SQL select * from mv_t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 77 rows selected.SQL exec dbms_mview.refresh(mv_t,c);PL/SQL procedure successfully completed.SQL select * from mv_t order by x; X Y Z- - - 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 7 8 8 88 rows selected.2.3 测试物化视图使用效果2.3.1 验证创建结果Select * from t order by x;Select * from mv_t order by x;2.3.2 验证物化视图是否随记录增加而更新Insert into t values(7,7,7);Select * from t order by x;Select * from mv_t order by x;Commit;Select * from t order by x;Select * from mv_t order by x;2.3.3 验证物化视图是否随记录删除而更新Delete from t where x=1;Select * from t order by x;Select * from mv_t order by x;Commit;Select * from t order by x;Select * from mv_t order by x;DISTINCT这种方式刷新之后 mlog$_t还是有日志的2.4 补充1-物化视图查询重写所谓物化视图查询重写就是，如果初始化参数query_rewrite_enable设置为TRUE，并且数据库运行在CBO优化模式下，当对基表进行查询时，ORACLE会自动判断是否能利用这个基表的所有包含enable query rewrite关键字的物化视图，如果可以且根据统计信息判断通过查询物化视图代价更小，则ORACLE自动重写查询语句，通过查询物化视图行到正确的结果。2.4.1 启用查询重写功能（默认）Conn / as sysdbaShow parameter query_rewrite_enableSet autot traceonly explainSelect x,y,z,count(*) from sec.t group by x,y,z;2.4.2 停用查询重写功能Alter session set query_rewrite_enable=false;Set autot traceonly explainSelect x,y,z,count(*) from sec.t group by x,y,z;2.5 补充2-手工刷新方法2.5.1 若物化视图创建时不带on commit选项，物化视图将不会根据commit 进行刷新，需要强制使用手工方式进行刷新Drop materialized view mv_t;Create materialized view mv_t build immediate refresh fast enable query rewrite as select x from t group by x;2.5.2 手工刷新方法：Exec dbms_mview.refresh(mv_t,c);2.6 针对两个查询语句，构造物化视图Select ,x,y,avg(x) as aa,count(x),sum(y-x) as bb From t group by x,y;Select avg(x),k,sum(y-z) as sales,count(*) from t group by x;针对这两个SQL语句，创造两个物化视图，要求实现快速刷新，并且支持查询重写特性。Create materialized view log on t With rowid, sequence (x,y,z) including new values;Create materialized view tt_mv build immediate refresh fast on commit enable query rewrite as select x,y,avg(x) as aa ,count(x),sum(y-z) as bb from t group by x,y;Create materialized view ttt_mv build immediate refresh fast on commit enable query rewrite as select x,y,avg(x) as aa ,count(x),sum(y-z) as bb from t group by x,y;问创建跨库可更新物化视图，要求可手动刷新Books-administrators guide -29 distributed database concepts - database links-creation of database links:examplesBooks-pl/sql packages and types reference -61 dbms_mview答约定：在PROD数据库实例上创建物化视图mv_t，物化视图对应的基表所在实例为另外一套数据库实例EMREP2.6.1 在EMREP数据库实例上创建基表TExport ORACLE_SID=EMREPSqlplus / as sysdbaSQL create user user_emrep identified by user_emrep;User created.SQL grant connect,resource to user_emrep;Grant succeeded.SQL conn user_emrep/user_emrepConnected.SQL create table t (x varchar2(20);Table created.SQL insert into t values (secooler);1 row created.SQL commit;Commit complete.SQL select * from t;X-secoolerSQL create materialized view log on t with rowid;Materialized view log created.2.6.2 在T表所在实例EMREP上创建物化视图日志Create materialized view log on t with rowed;2.6.3 在物化视图所在实例PROD上创建DATABASE LINK1) 调整global_names 参数为false此步骤是为了防止在使用与目标实例名不同的database link时报ORA-02085错误奇数机操作如下Alter system set global_names=FALSE;Show parameter global_names2) 创建database linkCreate public database link dblink_to_emrep connect to user_emrep identified by user_emrep using EMREP;global_names为TRUE的时候创建的DBLINK名字为数据库的实例名，这样好认，但如果创建的名字不是和实例名一样，那么创建的时候不会报错，使用的时候会告诉你不可用；如果为FALSE那么叫什么都可以。EMREP为FLASE，PROD为TRUE的话，那么EMREP连接到PROD随便起；如果PROD到EMREP的时候就会报错3) 为了以防万一，可以创建与secgc同名dblink，命令参考如下：Create public database link emrep connect to user_emrep identified by user_emrep using EMREP;4) 在PROD实例上用户user_prod下创建物化视图Create user user_prod identified by user_prod;Grant dba to user_prod;Conn user_prod/user_prodCreate materialized view mv_t refresh fast with rowed as select * from tdblink_to_emrep;5) 对物化视图进行快速刷新和完全刷新1、快速刷新方法Exec dbms_mview.refresh(mv_t,f);2、完全刷新方法Exec dbms_mview.refresh(mv_t,c);6） 测试物化视图刷新效果1、在基表T中插入一条数据Export ORACLE_SID=EMREPSqlplus / as sysdbaConn user_emrep/user_emrepSelect * from t;Insert into t values(ANDY);Commit;Select * from t;2、 在对物化视图进行快速刷新（或完全刷新）前数据Export ORACLE_SID=PRODSqlplus / as sydbaConn user_prod/user_prodSelect * from mv_t;3、在对物化视图进行快速刷新（或完全刷新）后数据Exec dbms_mview.refresh(mv_t,f);Select * from mv_t;3 外部表问使用sql*loader根据给定的数据文件和控制文件生成并创建外部表Books-utilities -7 sql*loader command line reference -external_tableBook-administrators guide -15 managing tables - managing external tables3.1.1 环境的准备创建HR用户和SH用户并授权Export ORACLE_SID=PRODSqlplus / as sysdbaCreate user hr identified by hr default tablespace users;Grant dba to hr;Create user sh identified by sh default tablespace users;Grant dba to sh;数据文件内容奇数机：Vi /home/oracle/t.datP,James,31,P,Thomas,22,E,Pat,38,93645,1122,Engineering,P,Bill,19,P,Scott,55,S,Judy,45,27316,English,S,Karen,34,80356,History,E,Karen,61,90056,1323,Manufa,cturing,S,Pat,29,98625,Spanish,S,Cody,22,99743,Math,P,Ted,43,E,Judy,44,87616,1544,Accounting,E,Bob,50,63421,1314,Shipping,S,Bob,32,67420,Psychology,E,Cody,33,25143,1002,Hum,an Resources,控制文件内容奇数机:vi /home/oracle/t.ctlLOAD DATAinfile /home/oracle/scripts/t.datbadfile /home/oracle/scripts/t.baddiscardfile /home/oracle/scripts/t.dscAPPENDINTO TABLE HR.tFIELDS TERMINATED BY , optionally enclosed by TRAILING NULLCOLS(x1, x2, x3, x4, x5, x6)3.1.2 在HR用户中创建表根据数据文件和控制文件内容创建表T奇数机：sqlplus hr/hrcreate table t( x1 varchar2(20), x2 varchar2(20), x3 number(10), x4 number(10), x5 varchar2(20), x6 varchar2(20);3.1.3 使用sql*loader生成创建外部表的命令使用SQL*LOADER工具的external_table选项获得外部表的创建语句奇数机oraclesecdb1 $ sqlldr userid=hr/hr control=/home/oracle/t.ctl external_table=GENERATE_ONLY log=/home/oracle/t.logSQL*Loader: Release .0 - Production on Sun Mar 31 17:50:05 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.3.1.4 连接SH用户创建外部表1）连接到SH用户奇数Sqlplus sh/sh2）使用t.log文件中的内容创建derectory注意命令结尾处添加分号。Create directory sys_sqlldr_xt_tmpdir_00000 as /home/oracle/;3）调整t.log文件中的内容创建外部表注意其中需要修改的内容：1修改表名2去掉LOGFILE中的绝对路径内容3 结尾处添加分号CREATE TABLE SH.t ( X1 VARCHAR2(20), X2 VARCHAR2(20), X3 NUMBER(10), X4 NUMBER(10), X5 VARCHAR2(20), X6 VARCHAR2(20)ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE SYS_SQLLDR_XT_TMPDIR_00000:t.bad DISCARDFILE SYS_SQLLDR_XT_TMPDIR_00000:t.dsc LOGFILE t.log_xt READSIZE 1048576 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( X1 CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY , X2 CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY , X3 CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY , X4 CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY , X5 CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY , X6 CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY ) ) location ( t.dat )REJECT LIMIT UNLIMITED ;3.1.5 查看外部表内容Select * from sh.t问使用ORACLE_DATAPUMP卸载表数据到文件并跨库读取Books-utilities-part III external tables -14 the ORACLE_DAT