第60次：2014-11-06， 053第 9章

botang

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

physical-block.sh

1. #!/bin/sh
   
2. v_dbname=orcl
   
3. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl stop database -d $v_dbname"
   
4. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
   
5. conn / as sysdba
   
6. startup mount exclusive
   
7. EOF
   
8. 
   
9. rm -f /home/oracle/example01.dbf 2>/dev/null
   
10. 
    
11. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
    
12. backup as copy datafile 5 format '/home/oracle/example01.dbf';
    
13. EOF
    
14. 
    
15. dd if=/dev/zero of=/home/oracle/example01.dbf bs=8192 count=1 seek=175 conv=notrunc
    
16. 
    
17. su - oracle -c "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid ; export ORACLE_SID=+ASM ; /u01/app/oracle/product/11.2.0/grid/bin/asmcmd" <<EOF
    
18. rm -f +data/$v_dbname/DATAFILE/example*
    
19. EOF
    
20. 
    
21. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
    
22. run {
    
23. set maxcorrupt for datafile 5 to 1;
    
24. backup as copy  datafilecopy '/home/oracle/example01.dbf' format '+data';
    
25. }
    
26. EOF
    
27. 
    
28. sleep 3
    
29. 
    
30. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
    
31. delete noprompt datafilecopy '/home/oracle/example01.dbf';
    
32. switch datafile 5 to copy;
    
33. EOF
    
34. 
    
35. 
    
36. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
    
37. conn / as sysdba
    
38. alter database open;
    
39. EOF
    

复制代码

2014-11-06-dbms_repair.sql：

1. select  * from dba_indexes i where i.owner='HR' and i.table_name='T05309';
   
2. 
   
3. select  * from  REPAIR_TABLE;
   
4. 
   
5. 
   
6. BEGIN
   
7. DBMS_REPAIR.ADMIN_TABLES (
   
8.    table_name => 'REPAIR_TABLE',
   
9.    table_type => DBMS_REPAIR.REPAIR_TABLE,
   
10.    action => DBMS_REPAIR.CREATE_ACTION,
    
11.    tablespace => 'USERS');
    
12. END;
    
13. 
    
14. 
    
15. select  * from  REPAIR_TABLE;
    
16. 
    
17. select  * from ORPHAN_KEY_TABLE;
    
18. 
    
19. BEGIN
    
20. DBMS_REPAIR.ADMIN_TABLES (
    
21.    table_name => 'ORPHAN_KEY_TABLE',
    
22.    table_type => DBMS_REPAIR.ORPHAN_TABLE,
    
23.    action => DBMS_REPAIR.CREATE_ACTION,
    
24.    tablespace => 'USERS');
    
25. END;
    
26. 
    
27. select  * from ORPHAN_KEY_TABLE;
    
28. 
    
29. DECLARE num_corrupt INT;
    
30. BEGIN
    
31. num_corrupt := 0;
    
32. DBMS_REPAIR.CHECK_OBJECT (
    
33.    schema_name => 'HR',
    
34.            object_name => 'T05309',
    
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. DECLARE num_fix INT;
    
43. BEGIN
    
44. num_fix := 0;
    
45. DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
    
46.            schema_name => 'HR',
    
47.            object_name => 'T05309',
    
48.            object_type => DBMS_REPAIR.TABLE_OBJECT,
    
49.            repair_table_name => 'REPAIR_TABLE',
    
50.    fix_count => num_fix);
    
51. END;
    
52. 
    
53. select  * from  REPAIR_TABLE;
    
54. 
    
55. 
    
56. DECLARE num_orphans INT;
    
57. BEGIN
    
58. num_orphans := 0;
    
59. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
60.   schema_name => 'HR',
    
61.   object_name => 'I1_05309',
    
62.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
63.   repair_table_name => 'REPAIR_TABLE',
    
64.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
65.   key_count => num_orphans);
    
66. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
67. END;
    
68. 
    
69. select  * from ORPHAN_KEY_TABLE;
    
70. 
    
71. DECLARE num_orphans INT;
    
72. BEGIN
    
73. num_orphans := 0;
    
74. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
75.   schema_name => 'HR',
    
76.   object_name => 'I2_05309',
    
77.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
78.   repair_table_name => 'REPAIR_TABLE',
    
79.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
80.   key_count => num_orphans);
    
81. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
82. END;
    
83. 
    
84. 
    
85. select  * from ORPHAN_KEY_TABLE;
    
86. 
    
87. select  * from ORPHAN_KEY_TABLE where keyrowid||' '='AAASNiAIgAAAIABAAA'||' ';
    
88. 
    
89. select dump(o.key)
    
90. from ORPHAN_KEY_TABLE  o;
    
91. 
    
92. begin
    
93.    dbms_repair.skip_corrupt_blocks(schema_name => 'HR',object_name => 'T05309');
    
94.   end;

复制代码