课程第38-41次（2017-06-17星期六上下午、2017-06-20星期二和2017-06-22星期四）

botang

上完1Z0-052第18章 My Oracle Support
上完1Z0-053第9章 ADR和上完1Z0-053第8章 RMAN Tuning
进行Scheduler
1Z0-052共19章（上完13章），1Z0-053共21章（上完15章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的28章


小补丁下载：http://124.16.180.178:8080/studentguide_sec_O11g

游客，本帖隐藏的内容需要积分高于 100 才可浏览，您当前积分为 0

推送出的环境中可能丢失的/u01/app/oraInventory/ContentsXML/inventory.xml文件内容：

1. <?xml version="1.0" standalone="yes" ?>
   
2. <!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. -->
   
3. <!-- Do not modify the contents of this file by hand. -->
   
4. <INVENTORY>
   
5. <VERSION_INFO>
   
6.    <SAVED_WITH>11.2.0.3.0</SAVED_WITH>
   
7.    <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
   
8. </VERSION_INFO>
   
9. <HOME_LIST>
   
10. <HOME NAME="Ora11g_gridinfrahome1" LOC="/u01/app/oracle/product/11.2.0/grid" TYPE="O" IDX="1"/>
    
11. <HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2"/>
    
12. <HOME NAME="Ora11g_gridinfrahome2" LOC="/u01/app/oracle/product/11.2.0.3/grid" TYPE="O" IDX="3"/>
    
13. </HOME_LIST>
    
14. <COMPOSITEHOME_LIST>
    
15. </COMPOSITEHOME_LIST>
    
16. </INVENTORY>
    

复制代码

大补丁：

1. ###########
   
2.     startup upgrade   //其实就是禁用新特性
   
3.     spool filename.log
   
4.     @utlu112i.sql         
   
5.     @catupgrd.sql >6h
   
6.     自动shutdown immediate
   
7.     startup
   
8.     @utlu112s.sql
   
9.     @catuppst.sql
   
10.     @utlrp.sql   2h  

复制代码

逻辑坏块修复：

1. 
   
2. select  * from REPAIR_TABLE;
   
3. 
   
4. BEGIN
   
5. DBMS_REPAIR.ADMIN_TABLES (
   
6.    table_name => 'REPAIR_TABLE',
   
7.    table_type => DBMS_REPAIR.REPAIR_TABLE,
   
8.    action => DBMS_REPAIR.CREATE_ACTION,
   
9.    tablespace => 'USERS');
   
10. END;
    
11. 
    
12. select  * from REPAIR_TABLE;
    
13. 
    
14. select  * from ORPHAN_KEY_TABLE;
    
15. 
    
16. BEGIN
    
17. DBMS_REPAIR.ADMIN_TABLES (
    
18.    table_name => 'ORPHAN_KEY_TABLE',
    
19.    table_type => DBMS_REPAIR.ORPHAN_TABLE,
    
20.    action => DBMS_REPAIR.CREATE_ACTION,
    
21.    tablespace => 'USERS');
    
22. END;
    
23. 
    
24. select  * from ORPHAN_KEY_TABLE;
    
25. 
    
26. ---
    
27. 
    
28. DECLARE
    
29. num_corrupt INT;
    
30. BEGIN
    
31. num_corrupt := 0;
    
32. DBMS_REPAIR.CHECK_OBJECT (
    
33.    schema_name => 'HR',
    
34.        object_name => 'T05218',
    
35.    repair_table_name => 'REPAIR_TABLE',
    
36.    corrupt_count => num_corrupt);
    
37.   dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
    
38. END;
    
39. 
    
40. select  * from REPAIR_TABLE;
    
41. 
    
42. ----
    
43. 
    
44. begin
    
45.    dbms_repair.skip_corrupt_blocks(schema_name => 'HR',
    
46.    object_name =>  'T05218');
    
47. end;
    
48. ----
    
49. 
    
50. DECLARE
    
51.   num_orphans INT;
    
52. BEGIN
    
53. num_orphans := 0;
    
54. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
55.   schema_name => 'HR',
    
56.   object_name => 'I05218_A',
    
57.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
58.   repair_table_name => 'REPAIR_TABLE',
    
59.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
60.   key_count => num_orphans);
    
61. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
62. END;
    
63. 
    
64. select  * from ORPHAN_KEY_TABLE;
    
65. 
    
66. 
    
67. DECLARE
    
68. num_orphans INT;
    
69. BEGIN
    
70. num_orphans := 0;
    
71. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
72.   schema_name => 'HR',
    
73.   object_name => 'I05218_B',
    
74.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
75.   repair_table_name => 'REPAIR_TABLE',
    
76.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
77.   key_count => num_orphans);
    
78. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
79. END;
    
80. 
    
81. 
    
82. select   dump(key)
    
83. from ORPHAN_KEY_TABLE  where keyrowid||'X'='AAASNjAIgAAAIABAAA'||'X'
    
84.   and index_name='I05218_B';
    
85.                                                    
    
86.   ---
    
87.   
    
88.   select  dump('ABC')  from dual;      
    
89.   
    
90.   ---
    
91.   select   dump(key)
    
92. from ORPHAN_KEY_TABLE  where keyrowid||'X'='AAASNjAIgAAAIABAAA'||'X'
    
93.   and index_name='I05218_A';
    
94.         
    
95.   ---195,18,91,33=179032
    

复制代码

逻辑坏块dump：

100^(193-193-0)(2-1)=1

100^(193-193-0)(11-1)=10

100^(194-193-0)(2-1)=100

100^(194-193-0)(11-1)=1000

100^(194-193-0)(2-1)+

   100^(194-193-1)(11-1)=110

100^(194-193-0)(12-1)=1100

100^(62-62-0)(101-100)=1

100^(62-62-0)(101-91)=10

100^(62-61-0)(101-100)=100

123456.789

100^(195-193-0)(13-1)+

100^(195-193-1)(35-1)+

100^(195-193-2)(57-1)+

100^(195-193-3)(79-1)+

100^(195-193-4)(91-1)=120000+3400+56+0.78+0.009=

123456.789

-123456.789

100^(62-60-0)(101-89)+

100^(62-60-1)(101-67)+

100^(62-60-2)(101-45)+

100^(62-60-3)(101-23)+

100^(62-60-4)(101-11)=120000+3400+56+0.78+0.009=

-123456.789

-123456.78901

100^(62-60-0)(101-89)+

100^(62-60-1)(101-67)+

100^(62-60-2)(101-45)+

100^(62-60-3)(101-23)+

100^(62-60-4)(101-11)+

100^(62-60-5)(101-91)

=120000+3400+56+0.78+0.009+ 0.00001=

-123456.78901

ADR体检：

1. begin
   
2. dbms_hm.run_check('Data Block Integrity Check',                                             'mycheck',0,blc_df_num=>'5',blc_bl_num=>'175');
   
3. end;
   

复制代码

RMAN调优：

1. SELECT OPNAME, CONTEXT, SOFAR, TOTALWORK,
   
2.     ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
   
3.     FROM V_$SESSION_LONGOPS sl
   
4.     WHERE OPNAME LIKE 'RMAN%'
   
5.     AND OPNAME NOT LIKE '%aggregate%'
   
6.     AND TOTALWORK != 0
   
7.     AND SOFAR <> TOTALWORK
   
8.     and sl.SID  in (select   s.sid
   
9. from v_$session s
   
10. where s.TERMINAL='pts/7');

复制代码

1. select  * from v$backup_sync_io;
   
2. 
   
3. select  * from v$backup_async_io;

复制代码

Backup Duration：

1. backup duration 00:05  minimize load  tablespace example ;

复制代码

Linux下Scheduler  脚本下载：http://124.16.180.178:8080/studentguide_sec_O11g

游客，本帖隐藏的内容需要积分高于 100 才可浏览，您当前积分为 0

Win下外部作业：

1. <font size="3">select * from dba_scheduler_credentials;
   
2. begin
   
3. dbms_scheduler.create_credential(credential_name => 'cred1',
   
4. username => 'botang',
   
5. password => 'oracle');
   
6. end;
   
7. grant execute on cred1 to hr;
   
8. grant create job to hr;
   
9. grant create external job to hr;</font>
   

复制代码

run.bat：

1. echo 12345 >> c:\data\a.txt
   
2. @exit

复制代码