课程第36次

botang · 发表于 2019-12-29 20:43:53

关于rowid和块号：

SQL> select t.*, t.rowid from t2057 t;
A ROWID
---------- ------------------
1 AAAVVuAAEAAAADGAAA
2 AAAVVuAAEAAAADGAAB
3 AAAVVuAAEAAAADGAAC
4 AAAVVuAAEAAAADGAAD
5 AAAVVuAAEAAAADGAAE
SQL> select 3*64+6 from dual;
3*64+6
----------
198
SQL> select dbms_rowid.rowid_block_number(rowid) from t2057;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
198
198
198
198
198
SQL>

复制代码

破坏物理坏块：

#!/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

复制代码

逻辑坏块：

SQL> create tablespace tbslog datafile '+Data' size 10M nologging ;
Tablespace created.
SQL> conn hr/oracle_4U
Connected.
SQL> create table tlog ( a number , b varchar2(20)) tablespace tbslog;
Table created.
SQL> insert into tlog values (12411.29, 'Auchenia' ) ;
1 row created.
SQL> commit;
Commit complete.

复制代码

[oracle@station76 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 26 11:07:31 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1554722616)
RMAN> backup tablespace tbslog;
Starting backup at 2019-12-26:11:08:55
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=204 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=136 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/orcl/datafile/tbslog.289.1028026985
channel ORA_SBT_TAPE_1: starting piece 1 at 2019-12-26:11:09:19
channel ORA_SBT_TAPE_1: finished piece 1 at 2019-12-26:11:09:54
piece handle=4nukcsuv_1_1 tag=TAG20191226T110918 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2019-12-26:11:09:54
RMAN>

复制代码

SQL> create index ilog_a on tlog (a ) ;
Index created.
SQL> create index ilog_b on tlog(b) ;
Index created.
SQL> insert /*+ append */ into tlog select * from tlog;
1 row created.
SQL> commit;
Commit complete.
SQL> select a, b, dbms_rowid.rowid_block_number(rowid) from tlog;
A B DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- -------------------- ------------------------------------
12411.29 Auchenia 135
12411.29 Auchenia 136
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tbslog offline immediate ;
Tablespace altered.
SQL>

复制代码

ASMCMD> cd datafile
ASMCMD> ls
SYSAUX.282.1026941797
SYSTEM.283.1026941797
TBSLOG.289.1028026985
TBSTRANS1.261.1027718433
TBSTRANS2.262.1027718497
UNDOTBS1.281.1026941797
USERS.265.1028025845
ASMCMD> rm TBSLOG.289.1028026985
ASMCMD> ls
SYSAUX.282.1026941797
SYSTEM.283.1026941797
TBSTRANS1.261.1027718433
TBSTRANS2.262.1027718497
UNDOTBS1.281.1026941797
USERS.265.1028025845
ASMCMD>

复制代码

[oracle@station76 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 26 11:28:08 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1554722616)
RMAN> restore tablespace tbslog;
Starting restore at 2019-12-26:11:28:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=76 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=144 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=204 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=15 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=79 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=138 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=203 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=16 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=17 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00007 to +DATA/orcl/datafile/tbslog.289.1028026985
channel ORA_SBT_TAPE_1: reading from backup piece 4nukcsuv_1_1
channel ORA_SBT_TAPE_1: piece handle=4nukcsuv_1_1 tag=TAG20191226T110918
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
Finished restore at 2019-12-26:11:29:31
RMAN> recover tablespace tbslog;
Starting recover at 2019-12-26:11:30:00
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019-12-26:11:30:03
RMAN>

复制代码

物理坏块有应激性响应，而逻辑坏块没有：
QQ图片20191229214415.png

但是会汇总到support workbench(incidnet , problem ) [注意和DRA中的 failure 的区别，虽然它们都来自ADR]
QQ图片20191229214415.png

在Support Workbench中最重要的东西就是Problem:
QQ图片20191229214415.png

打包：

/u01/app/oracle/product/11.2.0/dbhome_1/station76.example.com_orcl/sysman/emd/state/ORA1578_20191226115728_COM_1.zip