课程第41次（2017-10-10星期二）

botang

1Z0-053第8章
1Z0-052共19章（上完13章），1Z0-053共21章（上完14章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的27章

1. select  * from repair_table;
   
2. 
   
3. select * from orphan_key_table;
   
4. 
   
5. BEGIN
   
6.    DBMS_REPAIR.ADMIN_TABLES (
   
7.    table_name => 'REPAIR_TABLE',
   
8.    table_type => DBMS_REPAIR.REPAIR_TABLE,
   
9.    action => DBMS_REPAIR.CREATE_ACTION,
   
10.    tablespace => 'USERS');
    
11. END;
    
12. 
    
13. BEGIN
    
14.    DBMS_REPAIR.ADMIN_TABLES (
    
15.    table_name => 'ORPHAN_KEY_TABLE',
    
16.    table_type => DBMS_REPAIR.ORPHAN_TABLE,
    
17.    action => DBMS_REPAIR.CREATE_ACTION,
    
18.    tablespace => 'USERS');
    
19. END;
    
20. 
    
21. ----
    
22. 
    
23. DECLARE
    
24. num_corrupt INT;
    
25. BEGIN
    
26. num_corrupt := 0;
    
27. DBMS_REPAIR.CHECK_OBJECT (
    
28.      schema_name => 'HR',
    
29.        object_name => 'TLOGICAL',
    
30.      repair_table_name => 'REPAIR_TABLE',
    
31.     corrupt_count => num_corrupt);
    
32.    dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
    
33. END;
    
34. -------
    
35. /*
    
36. DECLARE
    
37. num_fix INT;
    
38. BEGIN
    
39. num_fix := 0;
    
40. DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
    
41.        schema_name => 'HR',
    
42.        object_name => 'TLOGICAL',
    
43.        object_type => DBMS_REPAIR.TABLE_OBJECT,
    
44.        repair_table_name => 'REPAIR_TABLE',
    
45.     fix_count => num_fix);
    
46.     dbms_output.put_line( 'Find  '|| num_fix ||' Bolcks.'  );
    
47. END;
    
48. 
    
49. */
    
50. 
    
51. select  * from repair_table;
    
52. 
    
53. select * from orphan_key_table;
    
54. 
    
55. -----
    
56. DECLARE
    
57. num_orphans INT;
    
58. BEGIN
    
59. num_orphans := 0;
    
60. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
61.   schema_name => 'HR',
    
62.   object_name => 'ILOGICAL_A',
    
63.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
64.   repair_table_name => 'REPAIR_TABLE',
    
65.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
66.   key_count => num_orphans);
    
67. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
68. END;
    
69. ----
    
70. select * from orphan_key_table;
    
71. 
    
72. ----
    
73. DECLARE
    
74. num_orphans INT;
    
75. BEGIN
    
76. num_orphans := 0;
    
77. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
78.   schema_name => 'HR',
    
79.   object_name => 'ILOGICAL_B',
    
80.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
81.   repair_table_name => 'REPAIR_TABLE',
    
82.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
83.   key_count => num_orphans);
    
84. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
85. END;
    
86. ----
    
87. 
    
88. select * from orphan_key_table;
    
89. 
    
90. 
    
91. 
    
92. select dump(key) from orphan_key_table
    
93. where  keyrowid||' '='AAATrIAIgAAAIABAAA'||' '
    
94.   and index_name='ILOGICAL_B';
    
95. --196 41 2 86 11
    
96.    --40018510
    
97.   
    
98. select dump(key) from orphan_key_table
    
99. where  keyrowid||' '='AAATrIAIgAAAIABAAA'||' '
    
100.   and index_name='ILOGICAL_A';
     
101.   
     
102. --- 108 99 119 99 108 122 99 122 108
     
103. --- l      c    w    c   l      z     c   z     l
     
104. 
     
105. 
     
106. begin
     
107.     dbms_repair.skip_corrupt_blocks(schema_name => 'HR',
     
108.     object_name => 'TLOGICAL');
     
109.   end;
     
110.   
     
111.   begin
     
112.      dbms_stats.gather_table_stats('HR','TLOGICAL');
     
113.    end;
     
114.   
     
115.   
     
116.   
     
117.   select   i.index_name, i.status
     
118.     from dba_indexes i
     
119.      where i.table_owner='HR' and i.table_name='TLOGICAL';
     
120.      
     
121.     -- alter index ilogical rebuild online;
     
122.      
     
123.     drop index hr.ilogical_a;
     
124.    
     
125.     drop index hr.ilogical_b;
     
126.    
     
127.     create index hr.ilogical_a on hr.tlogical(a) ;
     
128.    
     
129.      create index hr.ilogical_b on hr.tlogical(b) ;
     
130. 
     
131. 
     
132. 
     
133. 
     

复制代码

1. SQL> select  rowid  from tlogical;
   
2. ERROR:
   
3. ORA-01578: ORACLE data block corrupted (file # 6, block # 136)
   
4. ORA-01110: data file 6: '+DATA/orcl/datafile/tbslogical.267.955745025'
   
5. ORA-26040: Data block was loaded using the NOLOGGING option
   
6. 
   
7. 
   
8. 
   
9. no rows selected
   
10. 
    
11. SQL> dsc tlogical
    
12. SP2-0734: unknown command beginning "dsc tlogic..." - rest of line ignored.
    
13. SQL> desc tlogical
    
14. Name                                           Null?    Type
    
15. ----------------------------------------- -------- ----------------------------
    
16. A                                                    VARCHAR2(20)
    
17. B                                                    NUMBER
    
18. 
    
19. SQL> select dump(1) from dual;
    
20. 
    
21. DUMP(1)
    
22. ------------------
    
23. Typ=2 Len=2: 193,2
    
24. 
    
25. SQL> select dump(10) from dual;
    
26. 
    
27. DUMP(10)
    
28. -------------------
    
29. Typ=2 Len=2: 193,11
    
30. 
    
31. SQL> select dump(100) from dual;
    
32. 
    
33. DUMP(100)
    
34. ------------------
    
35. Typ=2 Len=2: 194,2
    
36. 
    
37. SQL> select dump(1000) from dual;
    
38. 
    
39. DUMP(1000)
    
40. -------------------
    
41. Typ=2 Len=2: 194,11
    
42. 
    
43. SQL> select dump(110) from dual;
    
44. 
    
45. DUMP(110)
    
46. ---------------------
    
47. Typ=2 Len=3: 194,2,11
    
48. 
    
49. SQL> select dump(0) from dual;
    
50. 
    
51. DUMP(0)
    
52. ----------------
    
53. Typ=2 Len=1: 128
    
54. 
    
55. SQL> select dump(-1) from dual;
    
56. 
    
57. DUMP(-1)
    
58. -----------------------
    
59. Typ=2 Len=3: 62,100,102
    
60. 
    
61. SQL> select dump(-10) from dual;
    
62. 
    
63. DUMP(-10)
    
64. ----------------------
    
65. Typ=2 Len=3: 62,91,102
    
66. 
    
67. SQL> select dump(-100) from dual;
    
68. 
    
69. DUMP(-100)
    
70. -----------------------
    
71. Typ=2 Len=3: 61,100,102
    
72. 
    
73. SQL> select dump(-1000) from dual;
    
74. 
    
75. DUMP(-1000)
    
76. ----------------------
    
77. Typ=2 Len=3: 61,91,102
    
78. 
    
79. SQL> select dump(-110) from dual;
    
80. 
    
81. DUMP(-110)
    
82. --------------------------
    
83. Typ=2 Len=4: 61,100,91,102
    
84. 
    
85. SQL> select dump(123456.789) from dual;
    
86. 
    
87. DUMP(123456.789)
    
88. -------------------------------
    
89. Typ=2 Len=6: 195,13,35,57,79,91
    
90. 
    
91. SQL> select dump(-123456.789) from dual;
    
92. 
    
93. DUMP(-123456.789)
    
94. ----------------------------------
    
95. Typ=2 Len=7: 60,89,67,45,23,11,102
    
96. 
    
97. SQL> select dump(-123456.78901) from dual;
    
98. 
    
99. DUMP(-123456.78901)
    
100. -------------------------------------
     
101. Typ=2 Len=8: 60,89,67,45,23,11,91,102
     
102. 
     
103. SQL>
     

复制代码

--------------------------------------------------------------------------

1. SELECT SID, SPID, CLIENT_INFO
   
2. FROM V$PROCESS p, V$SESSION s
   
3. WHERE p.ADDR = s.PADDR
   
4. AND CLIENT_INFO LIKE '%id=zhang3%';
   
5. 
   
6. 
   
7. SELECT SID, SPID, CLIENT_INFO
   
8. FROM V$PROCESS p, V$SESSION s
   
9. WHERE p.ADDR = s.PADDR
   
10. AND s.program LIKE '%rman%';
    
11. 
    
12. 
    
13. select  * from v_$session_longops s
    
14.   where s.SID in ( 139 , 14 , 200 );
    
15.   
    
16.   
    
17.   select  * from v$backup_sync_io;
    
18.   
    
19.   select  * from v$backup_async_io;
    

复制代码

关于备份优化：
Each file need at least 4 buffers!

DISK Read(Backup/Restore)

Max4files_16Mchannel
    1. buffer=1Mbuffer
    2. 1file=16*1Mbuffer
       2files=8*1Mbuffer+8*1Mbuffer
       3files=8*1Mbuffer+4*1Mbuffer+4*1Mbuffer
       4files=4*1Mbuffer+4*1Mbuffer+4*1Mbuffer+4*1Mbuffer



5-Max8files_less16Mchannel
    1. buffer=512Kbuffer
    2. 5files=5*4*512Kbuffer=10Mchannel
    3. 6files=6*4*512Kbuffer=12Mchannel
    4. 7files=7*4*512Kbuffer=14Mchannel
    5. 8files=8*4*512Kbuffer=16Mchannel



8+files
    1. buffer=128Kbuffer
    2. 9files=9*4*128Kbuffer=4.5Mchannel
    3. 16files=16*4*128Kbuffer=8Mchannel

1. select  b.DISCRETE_BYTES_PER_SECOND    from v_$backup_sync_io  b;
   
2.   
   
3.   select  a.SHORT_WAIT_TIME_TOTAL, a.LONG_WAIT_TIME_TOTAL
   
4.     from v_$backup_async_io a ;
   

复制代码

1. RMAN> run {
   
2. 2> allocate channel c1 device type sbt  maxopenfiles 4;
   
3. 3> allocate channel c2 device type sbt  maxopenfiles 4;
   
4. 4> backup (datafile 1 channel c1) ( datafile 2,3,4,5,6 channel c2 )  ;
   
5. 5> }
   
6. 
   
7. released channel: ORA_SBT_TAPE_1
   
8. released channel: ORA_SBT_TAPE_2
   
9. allocated channel: c1
   
10. channel c1: SID=15 device type=SBT_TAPE
    
11. channel c1: Oracle Secure Backup
    
12. 
    
13. allocated channel: c2
    
14. channel c2: SID=202 device type=SBT_TAPE
    
15. channel c2: Oracle Secure Backup
    
16. 
    
17. Starting backup at 10-OCT-17
    
18. channel c1: starting full datafile backup set
    
19. channel c1: specifying datafile(s) in backup set
    
20. input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
    
21. channel c1: starting piece 1 at 10-OCT-17
    
22. channel c2: starting full datafile backup set
    
23. channel c2: specifying datafile(s) in backup set
    
24. input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.816169553
    
25. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
    
26. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.955748593
    
27. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
    
28. input datafile file number=00006 name=+DATA/orcl/datafile/tbslogical.267.955745025
    
29. channel c2: starting piece 1 at 10-OCT-17
    
30. channel c2: finished piece 1 at 10-OCT-17
    
31. piece handle=4csgml96_1_1 tag=TAG20171010T213325 comment=API Version 2.0,MMS Version 10.4.0.4
    
32. channel c2: backup set complete, elapsed time: 00:00:20
    
33. channel c1: finished piece 1 at 10-OCT-17
    
34. piece handle=4bsgml96_1_1 tag=TAG20171010T213325 comment=API Version 2.0,MMS Version 10.4.0.4
    
35. channel c1: backup set complete, elapsed time: 00:00:30
    
36. Finished backup at 10-OCT-17
    
37. 
    
38. Starting Control File and SPFILE Autobackup at 10-OCT-17
    
39. piece handle=c-1343950367-20171010-07 comment=API Version 2.0,MMS Version 10.4.0.4
    
40. Finished Control File and SPFILE Autobackup at 10-OCT-17
    
41. released channel: c1
    
42. released channel: c2
    
43. 
    
44. RMAN>
    

复制代码

1.     RMAN> backup  tag 'MYBACKUP3'   duration 00:03  minimize load  tablespace users ;
   
2. 
   
3.     Starting backup at 2017-10-10:21:39:39
   
4.     using channel ORA_SBT_TAPE_1
   
5.     using channel ORA_SBT_TAPE_2
   
6.     channel ORA_SBT_TAPE_1: starting full datafile backup set
   
7.     channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
   
8.     input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
   
9.     channel ORA_SBT_TAPE_1: starting piece 1 at 2017-10-10:21:39:39
   
10.     channel ORA_SBT_TAPE_1: finished piece 1 at 2017-10-10:21:42:42
    
11.     piece handle=4gsgmlkr_1_1 tag=MYBACKUP3 comment=API Version 2.0,MMS Version 10.4.0.4
    
12.     channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:03:03
    
13.     channel ORA_SBT_TAPE_1: throttle time: 0:02:42
    
14.     Finished backup at 2017-10-10:21:42:42
    
15. 
    
16.     Starting Control File and SPFILE Autobackup at 2017-10-10:21:42:42
    
17.     piece handle=c-1343950367-20171010-09 comment=API Version 2.0,MMS Version 10.4.0.4
    
18.     Finished Control File and SPFILE Autobackup at 2017-10-10:21:43:07
    
19. 
    
20. 
    
21. 
    

复制代码