第19次活动：2017-09-20（星期三晚上7:00-9:30）

botang · 发表于 2017-9-21 09:36:54

实验编号15的结果：

bclresult-15.zip (52.27 KB, 下载次数: 310)

2017-9-21 09:31 上传

点击文件名下载附件

制造物理坏块脚本：

#!/bin/sh
v_dbname=orcl
su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl stop database -d $v_dbname -o abort"
su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
conn / as sysdba
startup mount exclusive
EOF
rm -f /home/oracle/example01.dbf 2>/dev/null
su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
backup as copy datafile 5 format '/home/oracle/example01.dbf';
EOF
dd if=/dev/zero of=/home/oracle/example01.dbf bs=8192 count=1 seek=175 conv=notrunc
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
rm -f +data/$v_dbname/DATAFILE/example*
EOF
su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
run {
set maxcorrupt for datafile 5 to 1;
backup as copy datafilecopy '/home/oracle/example01.dbf' format '+data';
}
EOF
sleep 3
su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
delete noprompt datafilecopy '/home/oracle/example01.dbf';
switch datafile 5 to copy;
EOF
su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
conn / as sysdba
alter database open;
EOF

复制代码

关于64进制换算：

#!/bin/sh
LIST="A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z 0 1 2 3 4 5 6 7 8 9 + /"
read -p "Pleas input a base64 code: " INPUT
COUNT=0
for i in $LIST
do
if [ $INPUT == $i ]
then
echo $COUNT
else
COUNT=$[ $COUNT + 1 ]
fi
done

复制代码

select * from dba_tablespaces;
select * from v$database_block_corruption;
select * from v$backup_corruption;
select * from v$copy_corruption;
create tablespace tbslogical datafile size 5M nologging;
select a, b, rowid , dbms_rowid.rowid_block_number (rowid)
from hr.tlogical;
alter tablespace tbslogical offline immediate;
alter tablespace tbslogical online;
select department_id, rowid , dbms_rowid.rowid_block_number (rowid)
from hr.departments;
select * from dba_segments s
where s.owner='HR' and s.segment_name='DEPARTMENTS';
select * from dba_extents e where e.owner='HR'
and e.segment_name='DEPARTMENTS';
select * from dba_tablespaces;
select * from v$database_block_corruption;
select * from v$backup_corruption;
select * from v$copy_corruption;
----
create tablespace tbslogical datafile size 5M nologging;
select a, b, rowid , dbms_rowid.rowid_block_number (rowid)
from hr.tlogical;
alter tablespace tbslogical offline immediate;
alter tablespace tbslogical online;
select department_id, rowid , dbms_rowid.rowid_block_number (rowid)
from hr.departments;
select * from dba_segments s
where s.owner='HR' and s.segment_name='DEPARTMENTS';
select * from dba_extents e where e.owner='HR'
and e.segment_name='DEPARTMENTS';
alter tablespace example online;
------------------
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
table_name => 'REPAIR_TABLE',
table_type => DBMS_REPAIR.REPAIR_TABLE,
action => DBMS_REPAIR.CREATE_ACTION,
tablespace => 'USERS');
END;
select * from REPAIR_TABLE;
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
table_name => 'ORPHAN_KEY_TABLE',
table_type => DBMS_REPAIR.ORPHAN_TABLE,
action => DBMS_REPAIR.CREATE_ACTION,
tablespace => 'USERS');
END;
select * from ORPHAN_KEY_TABLE;
DECLARE
num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
schema_name => 'HR',
object_name => 'TLOGICAL',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
dbms_output.put_line( 'Find '|| num_corrupt||' Bolcks.' );
END;
select * from REPAIR_TABLE;
DECLARE
num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
schema_name => 'HR',
object_name => 'TLOGICAL',
object_type => DBMS_REPAIR.TABLE_OBJECT,
repair_table_name => 'REPAIR_TABLE',
fix_count => num_fix
);
dbms_output.put_line( 'Fix '|| num_fix||' Bolcks.' );
END;
DECLARE
num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
schema_name => 'HR',
object_name => 'ILOGICAL_A',
object_type => DBMS_REPAIR.INDEX_OBJECT,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
select * from ORPHAN_KEY_TABLE;
DECLARE
num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
schema_name => 'HR',
object_name => 'ILOGICAL_B',
object_type => DBMS_REPAIR.INDEX_OBJECT,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
---
select * from orphan_key_table where keyrowid||' '='AAASNjAIgAAAIABAAA'||' '
and index_name='ILOGICAL_B';
select dump(key) from orphan_key_table;
---
[root@station90 桌面]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 20 21:25:58 2017
SQL> conn hr/oracle_4U
Connected.
SQL> select dump(1) from dual;
DUMP(1)
------------------
Typ=2 Len=2: 193,2
SQL> select dump(10) from dual;
DUMP(10)
-------------------
Typ=2 Len=2: 193,11
SQL> select dump(100) from dual;
DUMP(100)
------------------
Typ=2 Len=2: 194,2
SQL> select dump(1000) from dual;
DUMP(1000)
-------------------
Typ=2 Len=2: 194,11
SQL> select dump(110) from dual;
DUMP(110)
---------------------
Typ=2 Len=3: 194,2,11
SQL> select dump(0) from dual;
DUMP(0)
----------------
Typ=2 Len=1: 128
SQL> select dump(-1) from dual;
DUMP(-1)
-----------------------
Typ=2 Len=3: 62,100,102
SQL> select dump(-10) from dual;
DUMP(-10)
----------------------
Typ=2 Len=3: 62,91,102
SQL> select dump(-100) from dual;
DUMP(-100)
-----------------------
Typ=2 Len=3: 61,100,102
SQL> select dump(-1000) from dual;
DUMP(-1000)
----------------------
Typ=2 Len=3: 61,91,102
SQL> select dump(-110) from dual;
DUMP(-110)
--------------------------
Typ=2 Len=4: 61,100,91,102
SQL> select dump(-1123456.789) from dual;
DUMP(-1123456.789)
--------------------------------------
Typ=2 Len=8: 59,100,89,67,45,23,11,102
SQL> select dump(-1123456.78901) from dual;
DUMP(-1123456.78901)
-----------------------------------------
Typ=2 Len=9: 59,100,89,67,45,23,11,91,102
SQL>
---
Recovery Manager complete.
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 20 21:10:20 2017
SQL> conn / as sysdba
Connected.
SQL> desc orphan_key_table
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
SQL> conn hr/oracle_4U
Connected.
SQL> select * from tlogical ;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 136)
ORA-01110: data file 6: '+DATA/orcl/datafile/tbslogical.267.955221695'
ORA-26040: Data block was loaded using the NOLOGGING option
no rows selected

复制代码