课程第35/36次

botang · 发表于 2019-8-3 10:05:08

2019-08-03

select s.sid, s."TERMINAL"
from v_$session s
where s."CLIENT_INFO" like '%botang%';
select * from v$session where terminal='pts/0';
select s.sid, p."SPID"
from v_$session s, v$process p
where s.paddr=p.addr and s."TERMINAL"='pts/0';
select s.sid, s."OPNAME", s."TARGET", s."SOFAR", s."TOTALWORK" from v$session_longops s
where s.sid in ( 64, 67 ) and s. < s."TOTALWORK";

复制代码

输入文件数<=filesperset(rman命令)<=maxopenfiles(通道命令）： QQ图片20190804085349.png

RMAN> run {
2> allocate channel c1 device type sbt maxopenfiles 4;
3> allocate channel c2 device type sbt maxopenfiles 4;
4> backup tag '1T-WHOLE-INCR0' filesperset 4 incremental level 0 (datafile 1,4,5,6 channel c1) (datafile 2,3,7,8 channel c2)
5> plus archivelog delete all input;
6> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=62 device type=SBT_TAPE
channel c1: Oracle Secure Backup
allocated channel: c2
channel c2: SID=74 device type=SBT_TAPE
channel c2: Oracle Secure Backup
Starting backup at 2019-08-04:06:56:14
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=35 STAMP=1015397775
channel c1: starting piece 1 at 2019-08-04:06:56:16
channel c1: finished piece 1 at 2019-08-04:06:56:41
piece handle=39u8bfcg_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
channel c1: backup set complete, elapsed time: 00:00:25
channel c1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2019_08_04/thread_1_seq_23.297.1015397775 RECID=35 STAMP=1015397775
Finished backup at 2019-08-04:06:56:41
Starting backup at 2019-08-04:06:56:41
channel c1: starting incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/TBS05319.DBF
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.1014330935
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.1013960987
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.1014504215
channel c1: starting piece 1 at 2019-08-04:06:56:41
channel c2: starting incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/TBS05319X.DBF
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/TBSSOLARIS.DBF
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.1013960883
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.1013960883
channel c2: starting piece 1 at 2019-08-04:06:56:41
channel c1: finished piece 1 at 2019-08-04:06:57:16
piece handle=3au8bfd9_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
channel c1: backup set complete, elapsed time: 00:00:35
channel c2: finished piece 1 at 2019-08-04:06:57:16
piece handle=3bu8bfd9_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
channel c2: backup set complete, elapsed time: 00:00:35
Finished backup at 2019-08-04:06:57:16
Starting backup at 2019-08-04:06:57:17
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=36 STAMP=1015397837
channel c1: starting piece 1 at 2019-08-04:06:57:17
channel c1: finished piece 1 at 2019-08-04:06:57:42
piece handle=3cu8bfed_1_1 tag=1T-WHOLE-INCR0 comment=API Version 2.0,MMS Version 10.4.0.4
channel c1: backup set complete, elapsed time: 00:00:25
channel c1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2019_08_04/thread_1_seq_24.297.1015397837 RECID=36 STAMP=1015397837
Finished backup at 2019-08-04:06:57:42
Starting Control File and SPFILE Autobackup at 2019-08-04:06:57:42
piece handle=c-1541741703-20190804-01 comment=API Version 2.0,MMS Version 10.4.0.4
Finished Control File and SPFILE Autobackup at 2019-08-04:06:58:07
released channel: c1
released channel: c2
RMAN>

复制代码

----------

RMAN> backup duration 00:03 minimize load tablespace users;
Starting backup at 2019-08-04:07:38:51
using channel ORA_SBT_TAPE_1
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=00004 name=+DATA/orcl/datafile/users.259.1014330935
channel ORA_SBT_TAPE_1: starting piece 1 at 2019-08-04:07:38:51
channel ORA_SBT_TAPE_1: finished piece 1 at 2019-08-04:07:41:56
piece handle=3qu8bhsb_1_1 tag=TAG20190804T073851 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:03:05
channel ORA_SBT_TAPE_1: throttle time: 0:02:42
Finished backup at 2019-08-04:07:41:56
Starting Control File and SPFILE Autobackup at 2019-08-04:07:41:56
piece handle=c-1541741703-20190804-05 comment=API Version 2.0,MMS Version 10.4.0.4
Finished Control File and SPFILE Autobackup at 2019-08-04:07:42:22
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
98 Incr 0 725.00M SBT_TAPE 00:00:34 2019-08-04:06:57:15
BP Key: 98 Status: AVAILABLE Compressed: NO Tag: 1T-WHOLE-INCR0
Handle: 3au8bfd9_1_1 Media: RMAN-DEFAULT-000011
List of Datafiles in backup set 98
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 1603503 2019-08-04:06:56:41 +DATA/orcl/datafile/users.259.1014330935
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
104 Full 15.50M SBT_TAPE 00:00:20 2019-08-04:07:26:03
BP Key: 104 Status: AVAILABLE Compressed: NO Tag: TAG20190804T072406
Handle: 3hu8bh3n_1_1 Media: station76-000029
List of Datafiles in backup set 104
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 1606004 2019-08-04:07:25:43 +DATA/orcl/datafile/users.259.1014330935
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
109 Full 15.50M SBT_TAPE 00:00:15 2019-08-04:07:30:42
BP Key: 109 Status: AVAILABLE Compressed: NO Tag: TAG20190804T072850
Handle: 3mu8bhcj_1_1 Media: station76-000030
List of Datafiles in backup set 109
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 1606778 2019-08-04:07:30:27 +DATA/orcl/datafile/users.259.1014330935
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
111 Full 7.75M SBT_TAPE 00:00:14 2019-08-04:07:36:51
BP Key: 111 Status: AVAILABLE Compressed: NO Tag: TAG20190804T073636
Handle: 3ou8bho5_1_1 Media: station76-000030
List of Datafiles in backup set 111
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 1627588 2019-08-04:07:36:37 +DATA/orcl/datafile/users.259.1014330935
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
113 Full 7.75M SBT_TAPE 00:03:04 2019-08-04:07:41:55
BP Key: 113 Status: AVAILABLE Compressed: NO Tag: TAG20190804T073851
Handle: 3qu8bhsb_1_1 Media: station76-000030
List of Datafiles in backup set 113
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 1627742 2019-08-04:07:38:51 +DATA/orcl/datafile/users.259.1014330935
RMAN>

复制代码

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

SQL> select department_id , rowid , substr(rowid , 10,6) , dbms_rowid.rowid_block_number(rowid) from departments;
DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
------------- ------------------ ------------------------
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
10 AAAVTAAAFAAAACvAAA AAAACv
175
20 AAAVTAAAFAAAACvAAB AAAACv
175

复制代码

---------物理坏块破坏的脚本：

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

复制代码

逻辑坏块：

[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 3 17:21:49 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create tablespace tbslogical datafile size 10M nologging;
Tablespace created.
SQL> conn hr/oracle_4U
Connected.
SQL> create table tlogical ( a number, b varchar2(30)) tablespace tbslogical ;
Table created.
SQL> create index ilogical_a on tlogical ( a ) tablespace users;
Index created.
SQL> create index ilogical_b on tlogical (b) tablespace users;
Index created.
SQL> insert into tlogical values (32691,'backwoodser') ;
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number( rowid ) , a , b from tlogical ;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A B
------------------------------------ ---------- ------------------------------
131 32691 backwoodser
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station76 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 3 17:33:56 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1541741703)
RMAN> backup tablespace tbslogical ;
Starting backup at 2019-08-03:17:34:17
using target database control file instead of recovery catalog
configuration for SBT_TAPE channel 2 is ignored
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=51 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: 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=00009 name=+DATA/orcl/datafile/tbslogical.265.1015349393
channel ORA_SBT_TAPE_1: starting piece 1 at 2019-08-03:17:34:25
channel ORA_SBT_TAPE_1: finished piece 1 at 2019-08-03:17:34:50
piece handle=45u8a0d1_1_1 tag=TAG20190803T173425 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2019-08-03:17:34:50
Starting Control File and SPFILE Autobackup at 2019-08-03:17:34:50
piece handle=c-1541741703-20190803-04 comment=API Version 2.0,MMS Version 10.4.0.4
Finished Control File and SPFILE Autobackup at 2019-08-03:17:35:15
RMAN> exit
Recovery Manager complete.
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 3 17:35:30 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> insert /*+ append */ into tlogical select * from tlogical ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tlogical;
A B
---------- ------------------------------
32691 backwoodser
32691 backwoodser
SQL> select dbms_rowid.rowid_block_number(rowid) , a , b from tlogical ;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A B
------------------------------------ ---------- ------------------------------
131 32691 backwoodser
136 32691 backwoodser
SQL> conn / as sysdba
Connected.
SQL> select file_name from dba_data_files where tablespace_name='TBSLOGICAL';
FILE_NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/tbslogical.265.1015349393
SQL> alter database datafile '+DATA/orcl/datafile/tbslogical.265.1015349393' offline ;
Database altered.
SQL> alter database datafile '+DATA/orcl/datafile/tbslogical.265.1015349393' online;
alter database datafile '+DATA/orcl/datafile/tbslogical.265.1015349393' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '+DATA/orcl/datafile/tbslogical.265.1015349393'
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station76 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 3 17:38:28 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1541741703)
RMAN> restore datafile '+DATA/orcl/datafile/tbslogical.265';
Starting restore at 2019-08-03:17:38:40
using target database control file instead of recovery catalog
configuration for SBT_TAPE channel 2 is ignored
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=66 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/03/2019 17:38:46
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: +DATA/orcl/datafile/tbslogical.265
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** +DATA/orcl/datafile/system.256.1014504215
2 620 SYSAUX *** +DATA/orcl/datafile/sysaux.257.1013960883
3 105 UNDOTBS1 *** +DATA/orcl/datafile/undotbs1.258.1013960883
4 10 USERS *** +DATA/orcl/datafile/users.259.1014330935
5 346 EXAMPLE *** +DATA/orcl/datafile/example.280.1015348375
6 10 TBS05319 *** /u01/app/oracle/oradata/orcl/TBS05319.DBF
7 10 TBS05319X *** /u01/app/oracle/oradata/orcl/TBS05319X.DBF
8 10 TBSSOLARIS *** /u01/app/oracle/oradata/orcl/TBSSOLARIS.DBF
9 10 TBSLOGICAL *** +DATA/orcl/datafile/tbslogical.265.1015349393
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 100 TEMP 32767 +DATA/orcl/tempfile/temp.267.1014724469
RMAN> restore datafile '+DATA/orcl/datafile/tbslogical.265.1015349393';
Starting restore at 2019-08-03:17:39:23
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
+DATA/orcl/datafile/tbslogical.265.10153493channel 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 00009 to +DATA/orcl/datafile/tbslogical.265.1015349393
channel ORA_SBT_TAPE_1: reading from backup piece 45u8a0d1_1_1 channel ORA_SBT_TAPE_1: piece handle=45u8a0d1_1_1 tag=TAG20190803T173425
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
Finished restore at 2019-08-03:17:39:52
RMAN> recover datafile 9;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02001: unrecognized punctuation symbol "+"
RMAN> recover datafile 9;
Starting recover at 2019-08-03:17:40:16
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019-08-03:17:40:17
RMAN> exit
Recovery Manager complete.
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 3 17:40:31 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter database datafile 9 online;
Database altered.
SQL> conn hr/oracle_4U
Connected.
SQL> select * from tlogical ;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 9, block # 136)
ORA-01110: data file 9: '+DATA/orcl/datafile/tbslogical.265.1015349393'
ORA-26040: Data block was loaded using the NOLOGGING option
no rows selected
SQL>

复制代码

Oracle内部存储数字的简单探索：

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(-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(0) from dual;
DUMP(0)
----------------
Typ=2 Len=1: 128
SQL> select dump(123456.789) from dual ;
DUMP(123456.789)
-------------------------------
Typ=2 Len=6: 195,13,35,57,79,91
SQL> select dump(-123456.789) from dual;
DUMP(-123456.789)
----------------------------------
Typ=2 Len=7: 60,89,67,45,23,11,102
SQL> select dump(-123456.78901) from dual ;
DUMP(-123456.78901)
-------------------------------------
Typ=2 Len=8: 60,89,67,45,23,11,91,102
SQL> select dump(32691) from dual ;
DUMP(32691)
------------------------
Typ=2 Len=4: 195,4,27,92
SQL>

复制代码

第一个括号的最后一个数字是“位置数”，比如Typ=2 Len=3: 194,2,11，其中2就是位置0,11就是位置1（小码机高位在前）

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