|
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 19:55:20 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select department_id, rowid from departments;
- DEPARTMENT_ID ROWID
- ------------- ------------------
- 10 AAAR5kAAFAAAACvAAA
- 20 AAAR5kAAFAAAACvAAB
- 30 AAAR5kAAFAAAACvAAC
- 40 AAAR5kAAFAAAACvAAD
- 50 AAAR5kAAFAAAACvAAE
- 60 AAAR5kAAFAAAACvAAF
- 70 AAAR5kAAFAAAACvAAG
- 80 AAAR5kAAFAAAACvAAH
- 90 AAAR5kAAFAAAACvAAI
- 100 AAAR5kAAFAAAACvAAJ
- 110 AAAR5kAAFAAAACvAAK
- DEPARTMENT_ID ROWID
- ------------- ------------------
- 120 AAAR5kAAFAAAACvAAL
- 130 AAAR5kAAFAAAACvAAM
- 140 AAAR5kAAFAAAACvAAN
- 150 AAAR5kAAFAAAACvAAO
- 160 AAAR5kAAFAAAACvAAP
- 170 AAAR5kAAFAAAACvAAQ
- 180 AAAR5kAAFAAAACvAAR
- 190 AAAR5kAAFAAAACvAAS
- 200 AAAR5kAAFAAAACvAAT
- 210 AAAR5kAAFAAAACvAAU
- 220 AAAR5kAAFAAAACvAAV
- DEPARTMENT_ID ROWID
- ------------- ------------------
- 230 AAAR5kAAFAAAACvAAW
- 240 AAAR5kAAFAAAACvAAX
- 250 AAAR5kAAFAAAACvAAY
- 260 AAAR5kAAFAAAACvAAZ
- 270 AAAR5kAAFAAAACvAAa
- 27 rows selected.
- SQL> select department_id, rowid, substr(rowid, 10 ,6 ) from departments;
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- 10 AAAR5kAAFAAAACvAAA AAAACv
- 20 AAAR5kAAFAAAACvAAB AAAACv
- 30 AAAR5kAAFAAAACvAAC AAAACv
- 40 AAAR5kAAFAAAACvAAD AAAACv
- 50 AAAR5kAAFAAAACvAAE AAAACv
- 60 AAAR5kAAFAAAACvAAF AAAACv
- 70 AAAR5kAAFAAAACvAAG AAAACv
- 80 AAAR5kAAFAAAACvAAH AAAACv
- 90 AAAR5kAAFAAAACvAAI AAAACv
- 100 AAAR5kAAFAAAACvAAJ AAAACv
- 110 AAAR5kAAFAAAACvAAK AAAACv
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- 120 AAAR5kAAFAAAACvAAL AAAACv
- 130 AAAR5kAAFAAAACvAAM AAAACv
- 140 AAAR5kAAFAAAACvAAN AAAACv
- 150 AAAR5kAAFAAAACvAAO AAAACv
- 160 AAAR5kAAFAAAACvAAP AAAACv
- 170 AAAR5kAAFAAAACvAAQ AAAACv
- 180 AAAR5kAAFAAAACvAAR AAAACv
- 190 AAAR5kAAFAAAACvAAS AAAACv
- 200 AAAR5kAAFAAAACvAAT AAAACv
- 210 AAAR5kAAFAAAACvAAU AAAACv
- 220 AAAR5kAAFAAAACvAAV AAAACv
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- 230 AAAR5kAAFAAAACvAAW AAAACv
- 240 AAAR5kAAFAAAACvAAX AAAACv
- 250 AAAR5kAAFAAAACvAAY AAAACv
- 260 AAAR5kAAFAAAACvAAZ AAAACv
- 270 AAAR5kAAFAAAACvAAa AAAACv
- 27 rows selected.
- 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 AAAR5kAAFAAAACvAAA AAAACv
- 175
- 20 AAAR5kAAFAAAACvAAB AAAACv
- 175
- 30 AAAR5kAAFAAAACvAAC AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 40 AAAR5kAAFAAAACvAAD AAAACv
- 175
- 50 AAAR5kAAFAAAACvAAE AAAACv
- 175
- 60 AAAR5kAAFAAAACvAAF AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 70 AAAR5kAAFAAAACvAAG AAAACv
- 175
- 80 AAAR5kAAFAAAACvAAH AAAACv
- 175
- 90 AAAR5kAAFAAAACvAAI AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 100 AAAR5kAAFAAAACvAAJ AAAACv
- 175
- 110 AAAR5kAAFAAAACvAAK AAAACv
- 175
- 120 AAAR5kAAFAAAACvAAL AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 130 AAAR5kAAFAAAACvAAM AAAACv
- 175
- 140 AAAR5kAAFAAAACvAAN AAAACv
- 175
- 150 AAAR5kAAFAAAACvAAO AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 160 AAAR5kAAFAAAACvAAP AAAACv
- 175
- 170 AAAR5kAAFAAAACvAAQ AAAACv
- 175
- 180 AAAR5kAAFAAAACvAAR AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 190 AAAR5kAAFAAAACvAAS AAAACv
- 175
- 200 AAAR5kAAFAAAACvAAT AAAACv
- 175
- 210 AAAR5kAAFAAAACvAAU AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 220 AAAR5kAAFAAAACvAAV AAAACv
- 175
- 230 AAAR5kAAFAAAACvAAW AAAACv
- 175
- 240 AAAR5kAAFAAAACvAAX AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 250 AAAR5kAAFAAAACvAAY AAAACv
- 175
- 260 AAAR5kAAFAAAACvAAZ AAAACv
- 175
- 270 AAAR5kAAFAAAACvAAa AAAACv
- 175
- 27 rows selected.
- 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 AAAR5kAAFAAAACvAAA AAAACv
- 175
- 20 AAAR5kAAFAAAACvAAB AAAACv
- 175
- 30 AAAR5kAAFAAAACvAAC AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 40 AAAR5kAAFAAAACvAAD AAAACv
- 175
- 50 AAAR5kAAFAAAACvAAE AAAACv
- 175
- 60 AAAR5kAAFAAAACvAAF AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 70 AAAR5kAAFAAAACvAAG AAAACv
- 175
- 80 AAAR5kAAFAAAACvAAH AAAACv
- 175
- 90 AAAR5kAAFAAAACvAAI AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 100 AAAR5kAAFAAAACvAAJ AAAACv
- 175
- 110 AAAR5kAAFAAAACvAAK AAAACv
- 175
- 120 AAAR5kAAFAAAACvAAL AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 130 AAAR5kAAFAAAACvAAM AAAACv
- 175
- 140 AAAR5kAAFAAAACvAAN AAAACv
- 175
- 150 AAAR5kAAFAAAACvAAO AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 160 AAAR5kAAFAAAACvAAP AAAACv
- 175
- 170 AAAR5kAAFAAAACvAAQ AAAACv
- 175
- 180 AAAR5kAAFAAAACvAAR AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 190 AAAR5kAAFAAAACvAAS AAAACv
- 175
- 200 AAAR5kAAFAAAACvAAT AAAACv
- 175
- 210 AAAR5kAAFAAAACvAAU AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 220 AAAR5kAAFAAAACvAAV AAAACv
- 175
- 230 AAAR5kAAFAAAACvAAW AAAACv
- 175
- 240 AAAR5kAAFAAAACvAAX AAAACv
- 175
- DEPARTMENT_ID ROWID SUBSTR(ROWID,10,6)
- ------------- ------------------ ------------------------
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------------------------------
- 250 AAAR5kAAFAAAACvAAY AAAACv
- 175
- 260 AAAR5kAAFAAAACvAAZ AAAACv
- 175
- 270 AAAR5kAAFAAAACvAAa AAAACv
- 175
- 27 rows selected.
- SQL> select department_id, rowid, substr(rowid, 10 ,6 ), dbms_rowid.rowid_block_number(rowid) from departments;
复制代码- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:19:56 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> Connected.
- SQL> ORACLE instance shut down.
- SQL> ORACLE instance started.
- Total System Global Area 6664212480 bytes
- Fixed Size 2240944 bytes
- Variable Size 3640659536 bytes
- Database Buffers 3003121664 bytes
- Redo Buffers 18190336 bytes
- Database mounted.
- SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:20:08 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1343950367, not open)
- RMAN>
- Starting backup at 2017-09-26:20:20:09
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=130 device type=DISK
- allocated channel: ORA_DISK_2
- channel ORA_DISK_2: SID=194 device type=DISK
- allocated channel: ORA_DISK_3
- channel ORA_DISK_3: SID=6 device type=DISK
- allocated channel: ORA_DISK_4
- channel ORA_DISK_4: SID=70 device type=DISK
- allocated channel: ORA_DISK_5
- channel ORA_DISK_5: SID=131 device type=DISK
- allocated channel: ORA_DISK_6
- channel ORA_DISK_6: SID=195 device type=DISK
- allocated channel: ORA_DISK_7
- channel ORA_DISK_7: SID=7 device type=DISK
- allocated channel: ORA_DISK_8
- channel ORA_DISK_8: SID=71 device type=DISK
- channel ORA_DISK_1: starting datafile copy
- input datafile file number=00005 name=+DATA/orcl/datafile/example.265.955743535
- output file name=/home/oracle/example01.dbf tag=TAG20170926T202011 RECID=3 STAMP=955743611
- channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
- Finished backup at 2017-09-26:20:20:12
- Starting Control File and SPFILE Autobackup at 2017-09-26:20:20:12
- piece handle=+FRA/orcl/autobackup/2017_09_26/s_955743590.329.955743615 comment=NONE
- Finished Control File and SPFILE Autobackup at 2017-09-26:20:20:15
- RMAN>
- Recovery Manager complete.
- 记录了1+0 的读入
- 记录了1+0 的写出
- 8192字节(8.2 kB)已复制,2.2317e-05 秒,367 MB/秒
- ASMCMD> ASMCMD> exit
- Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:20:16 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1343950367, not open)
- RMAN> 2> 3> 4>
- executing command: SET MAX CORRUPT
- using target database control file instead of recovery catalog
- Starting backup at 2017-09-26:20:20:17
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=131 device type=DISK
- allocated channel: ORA_DISK_2
- channel ORA_DISK_2: SID=194 device type=DISK
- allocated channel: ORA_DISK_3
- channel ORA_DISK_3: SID=7 device type=DISK
- allocated channel: ORA_DISK_4
- channel ORA_DISK_4: SID=70 device type=DISK
- allocated channel: ORA_DISK_5
- channel ORA_DISK_5: SID=130 device type=DISK
- allocated channel: ORA_DISK_6
- channel ORA_DISK_6: SID=193 device type=DISK
- allocated channel: ORA_DISK_7
- channel ORA_DISK_7: SID=6 device type=DISK
- allocated channel: ORA_DISK_8
- channel ORA_DISK_8: SID=69 device type=DISK
- channel ORA_DISK_1: starting datafile copy
- input is copy of datafile 00005: /home/oracle/example01.dbf
- output file name=+DATA/orcl/datafile/example.265.955743619 tag=TAG20170926T202011 RECID=4 STAMP=955743621
- channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
- Finished backup at 2017-09-26:20:20:22
- Starting Control File and SPFILE Autobackup at 2017-09-26:20:20:22
- piece handle=+FRA/orcl/autobackup/2017_09_26/s_955743590.332.955743623 comment=NONE
- Finished Control File and SPFILE Autobackup at 2017-09-26:20:20:25
- RMAN>
- Recovery Manager complete.
- Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:20:29 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1343950367, not open)
- RMAN>
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=130 device type=DISK
- allocated channel: ORA_DISK_2
- channel ORA_DISK_2: SID=194 device type=DISK
- allocated channel: ORA_DISK_3
- channel ORA_DISK_3: SID=6 device type=DISK
- allocated channel: ORA_DISK_4
- channel ORA_DISK_4: SID=70 device type=DISK
- allocated channel: ORA_DISK_5
- channel ORA_DISK_5: SID=131 device type=DISK
- allocated channel: ORA_DISK_6
- channel ORA_DISK_6: SID=195 device type=DISK
- allocated channel: ORA_DISK_7
- channel ORA_DISK_7: SID=7 device type=DISK
- allocated channel: ORA_DISK_8
- channel ORA_DISK_8: SID=71 device type=DISK
- List of Datafile Copies
- =======================
- Key File S Completion Time Ckp SCN Ckp Time
- ------- ---- - ------------------- ---------- -------------------
- 3 5 A 2017-09-26:20:20:11 2072566 2017-09-26:20:19:47
- Name: /home/oracle/example01.dbf
- Tag: TAG20170926T202011
- deleted datafile copy
- datafile copy file name=/home/oracle/example01.dbf RECID=3 STAMP=955743611
- Deleted 1 objects
- RMAN>
- datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.265.955743619"
- RMAN>
- Recovery Manager complete.
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:20:33 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> Connected.
- SQL>
- Database altered.
- SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- [root@station90 pub]# su - oracle
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:20:44 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select * from departments;
- select * from departments
- *
- ERROR at line 1:
- ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
- ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
- SQL> select * from departments;
- select * from departments
- *
- ERROR at line 1:
- ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
- ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
- SQL> select * from departments;
- select * from departments
- *
- ERROR at line 1:
- ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
- ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
- SQL> desc departments
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- DEPARTMENT_ID NOT NULL NUMBER(4)
- DEPARTMENT_NAME NOT NULL VARCHAR2(30)
- MANAGER_ID NUMBER(6)
- LOCATION_ID NUMBER(4)
- SQL> insert into departments values ( 999, 'XXX', 100, 1700 );
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from departments;
- ERROR:
- ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
- ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
- no rows selected
- SQL> select * from departments where department_id=999;
- DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
- ------------- ------------------------------ ---------- -----------
- 999 XXX 100 1700
- SQL> select department_id , dbms_rowid.rowid_block_number(rowid) from departments where department_id=999;
- DEPARTMENT_ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- ------------- ------------------------------------
- 999 171
复制代码 逻辑坏块的制造:
- [root@station90 ~]# su - oracle
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:43:23 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> create tablespace tbslogical datafile size 5M nologging;
- Tablespace created.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> create table tlogical( a varchar2(20), b number ) tablespace tbslogical;
- Table created.
- SQL> insert into tlogical values ( 'lcwclzczl' , 40018510);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- [oracle@station90 ~]$ rman target /
- Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:48:49 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1343950367)
- RMAN> backup tablespace tbslogical;
- Starting backup at 2017-09-26:20:48:57
- using target database control file instead of recovery catalog
- allocated channel: ORA_SBT_TAPE_1
- channel ORA_SBT_TAPE_1: SID=68 device type=SBT_TAPE
- channel ORA_SBT_TAPE_1: Oracle Secure Backup
- allocated channel: ORA_SBT_TAPE_2
- channel ORA_SBT_TAPE_2: SID=14 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=00006 name=+DATA/orcl/datafile/tbslogical.267.955745025
- channel ORA_SBT_TAPE_1: starting piece 1 at 2017-09-26:20:49:09
- channel ORA_SBT_TAPE_1: finished piece 1 at 2017-09-26:20:49:34
- piece handle=1tsff125_1_1 tag=TAG20170926T204909 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 2017-09-26:20:49:34
- Starting Control File and SPFILE Autobackup at 2017-09-26:20:49:34
- piece handle=c-1343950367-20170926-02 comment=API Version 2.0,MMS Version 10.4.0.4
- Finished Control File and SPFILE Autobackup at 2017-09-26:20:49:59
- RMAN> exit
- Recovery Manager complete.
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:50:51 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> create index ilogical_a on tlogical( a ) ;
- Index created.
- SQL> create index ilogical_b on tlogical( b ) ;
- Index created.
- SQL> select a, b, dbms_rowi.rowid_block_number(rowid) from tlogical;
- select a, b, dbms_rowi.rowid_block_number(rowid) from tlogical
- *
- ERROR at line 1:
- ORA-00904: "DBMS_ROWI"."ROWID_BLOCK_NUMBER": invalid identifier
- SQL> select a, b, dbms_rowid.rowid_block_number(rowid) from tlogical;
- A B DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- -------------------- ---------- ------------------------------------
- lcwclzczl 40018510 135
- SQL> insert /*+ append */ into tlogical select * from tlogical ;
- 1 row created.
- SQL> select * from tlogical;
- select * from tlogical
- *
- ERROR at line 1:
- ORA-12838: cannot read/modify an object after modifying it in parallel
- SQL> commit;
- Commit complete.
- SQL> select * from tlogical;
- A B
- -------------------- ----------
- lcwclzczl 40018510
- lcwclzczl 40018510
- SQL> select a, b, dbms_rowid.rowid_block_number(rowid) from tlogical;
- A B DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
- -------------------- ---------- ------------------------------------
- lcwclzczl 40018510 135
- lcwclzczl 40018510 136
- SQL> conn / as sysdba
- Connected.
- SQL> alter tablespace tbslogical offline immediate ;
- Tablespace altered.
- SQL> alter tablespace tbslogical online;
- alter tablespace tbslogical online
- *
- ERROR at line 1:
- ORA-01113: file 6 needs media recovery
- ORA-01110: data file 6: '+DATA/orcl/datafile/tbslogical.267.955745025'
- SQL> exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- [oracle@station90 ~]$ rman target /
- Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:55:32 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1343950367)
- RMAN> restore tablespace tbslogical ;
- Starting restore at 2017-09-26:20:55:48
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=12 device type=DISK
- allocated channel: ORA_DISK_2
- channel ORA_DISK_2: SID=204 device type=DISK
- allocated channel: ORA_DISK_3
- channel ORA_DISK_3: SID=15 device type=DISK
- allocated channel: ORA_DISK_4
- channel ORA_DISK_4: SID=78 device type=DISK
- allocated channel: ORA_DISK_5
- channel ORA_DISK_5: SID=203 device type=DISK
- allocated channel: ORA_DISK_6
- channel ORA_DISK_6: SID=6 device type=DISK
- allocated channel: ORA_DISK_7
- channel ORA_DISK_7: SID=79 device type=DISK
- allocated channel: ORA_DISK_8
- channel ORA_DISK_8: SID=141 device type=DISK
- allocated channel: ORA_SBT_TAPE_1
- channel ORA_SBT_TAPE_1: SID=205 device type=SBT_TAPE
- channel ORA_SBT_TAPE_1: Oracle Secure Backup
- allocated channel: ORA_SBT_TAPE_2
- channel ORA_SBT_TAPE_2: SID=80 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 00006 to +DATA/orcl/datafile/tbslogical.267.955745025
- channel ORA_SBT_TAPE_1: reading from backup piece 1tsff125_1_1
- channel ORA_SBT_TAPE_1: piece handle=1tsff125_1_1 tag=TAG20170926T204909
- channel ORA_SBT_TAPE_1: restored backup piece 1
- channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
- Finished restore at 2017-09-26:20:56:27
- RMAN> recover tablespace tbslogical ;
- Starting recover at 2017-09-26:20:56:39
- 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 2017-09-26:20:56:40
- RMAN> exit
- Recovery Manager complete.
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:57:07 2017
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> alter tablespace tbslogical online;
- Tablespace altered.
- 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.955745025'
- ORA-26040: Data block was loaded using the NOLOGGING option
- no rows selected
- SQL>
复制代码- select * from dba_extents e
- where e.owner='HR' and e.segment_name='DEPARTMENTS';
-
- select * from dba_segments where
- owner='HR' and segment_name='DEPARTMENTS';
-
-
- select * from v$database_block_corruption;
-
- select * from v$backup_corruption;
-
- select * from v$copy_corruption;
复制代码
|
|