文档课题：模拟oracle数据库块损坏后表数据的恢复—exp&imp恢复.
数据库：oracle 19.12 多租户
说明：此次测试在无备份的情况下出现块损坏后运用exp&imp恢复表数据，此方法会存在丢失坏块所包含数据.
1、异常模拟
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MANAGER_ID,DEPARTMENT_ID from ora1.emp;
EMPLOYEE_ID FIRST_NAM LAST_NAME MANAGER_ID DEPARTMENT_ID
----------- --------- ---------- ---------- -------------
100 Steven King 90
101 Neena Kochhar 100 90
102 Lex De Haan 100 90
103 Alexander Hunold 102 80
104 Bruce Ernst 103 80
107 Diana Lorentz 103 80
124 Kevin Mourgos 100 50
141 Trenna Rajs 124 50
142 Curtis Davies 124 50
143 Randall Matos 124 50
144 Peter Vargas 124 50
EMPLOYEE_ID FIRST_NAM LAST_NAME MANAGER_ID DEPARTMENT_ID
----------- --------- ---------- ---------- -------------
149 Eleni Zlotkey 100 80
174 Ellen Abel 149 80
176 Jonathon Taylor 149 80
178 Kimberely Grant 149 80
200 Jennifer Whalen 101 10
201 Michael Hartstein 100 20
202 Pat Fay 201 20
205 Shelley Higgins 101 110
206 William Gietz 205 110
20 rows selected.
--查表ora1.emp每行所在的块id.
SQL> select EMPLOYEE_ID,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) relative,DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'ORA1','EMP') absolute,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blocknum from ORA1.emp;
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
100 12 12 659
101 12 12 659
102 12 12 659
103 12 12 659
104 12 12 659
107 12 12 659
124 12 12 659
141 12 12 659
142 12 12 659
143 12 12 659
144 12 12 659
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
149 12 12 659
174 12 12 659
176 12 12 659
178 12 12 659
200 12 12 659
201 12 12 659
202 12 12 659
205 12 12 659
206 12 12 659
20 rows selected.
--insert数据使ora1.emp表的数据分布在多个块.
SQL> conn ora1/ora1@orclpdb;
Connected.
SQL> insert into emp select * from emp;
20 rows created.
SQL> r
1* insert into emp select * from emp
40 rows created.
SQL> r
1* insert into emp select * from emp
80 rows created.
SQL> select EMPLOYEE_ID,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) relative,DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'ORA1','EMP') absolute,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blocknum from ORA1.emp;
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
100 12 12 659
101 12 12 659
102 12 12 659
103 12 12 659
104 12 12 659
107 12 12 659
124 12 12 659
141 12 12 659
142 12 12 659
143 12 12 659
144 12 12 659
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
149 12 12 659
174 12 12 659
176 12 12 659
178 12 12 659
200 12 12 659
201 12 12 659
202 12 12 659
205 12 12 659
206 12 12 659
100 12 12 660
101 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
102 12 12 660
103 12 12 660
104 12 12 660
107 12 12 660
124 12 12 660
141 12 12 660
142 12 12 660
143 12 12 660
144 12 12 660
149 12 12 660
174 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
176 12 12 660
178 12 12 660
200 12 12 660
201 12 12 660
202 12 12 660
205 12 12 660
206 12 12 660
100 12 12 660
101 12 12 660
102 12 12 660
103 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
104 12 12 660
107 12 12 660
124 12 12 660
141 12 12 660
142 12 12 660
143 12 12 660
144 12 12 660
149 12 12 660
174 12 12 660
176 12 12 660
178 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
200 12 12 660
201 12 12 660
202 12 12 660
205 12 12 660
206 12 12 660
100 12 12 660
101 12 12 660
102 12 12 660
103 12 12 660
104 12 12 660
107 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
124 12 12 660
141 12 12 660
142 12 12 660
143 12 12 660
144 12 12 660
149 12 12 660
174 12 12 660
176 12 12 660
178 12 12 660
200 12 12 660
201 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
202 12 12 660
205 12 12 660
206 12 12 660
100 12 12 660
101 12 12 660
102 12 12 660
103 12 12 660
104 12 12 660
107 12 12 660
124 12 12 660
141 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
142 12 12 660
143 12 12 660
144 12 12 660
149 12 12 660
174 12 12 660
176 12 12 660
178 12 12 660
200 12 12 660
201 12 12 660
202 12 12 660
205 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
206 12 12 660
100 12 12 660
101 12 12 660
102 12 12 660
103 12 12 660
104 12 12 660
107 12 12 660
124 12 12 660
141 12 12 660
142 12 12 660
143 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
144 12 12 660
149 12 12 660
174 12 12 660
176 12 12 660
178 12 12 660
200 12 12 660
201 12 12 660
202 12 12 660
205 12 12 660
206 12 12 660
100 12 12 660
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
101 12 12 661
102 12 12 661
103 12 12 661
104 12 12 661
107 12 12 661
124 12 12 661
141 12 12 661
142 12 12 661
143 12 12 661
144 12 12 661
149 12 12 661
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
174 12 12 661
176 12 12 661
178 12 12 661
200 12 12 661
201 12 12 661
202 12 12 661
205 12 12 661
206 12 12 661
100 12 12 661
101 12 12 661
102 12 12 661
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
103 12 12 661
104 12 12 661
107 12 12 661
124 12 12 661
141 12 12 661
142 12 12 661
143 12 12 661
144 12 12 661
149 12 12 661
174 12 12 661
176 12 12 661
EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
----------- ---------- ---------- ----------
178 12 12 661
200 12 12 661
201 12 12 661
202 12 12 661
205 12 12 661
206 12 12 661
160 rows selected.
说明：表ora1.emp数据分别分布在块659、660、661上.
--查每个块对应的行数，可以看到损坏一个块丢失的数据量.
SQL> select count(*) from emp where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=661;
COUNT(*)
----------
39
SQL> select count(*) from emp where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=660;
COUNT(*)
----------
101
SQL> select count(*) from emp where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=659;
COUNT(*)
----------
20
--先做备份.
RMAN> backup full database plus archivelog delete all input;
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
--查表对应的数据文件
SQL> select b.file_name,a.owner,a.segment_name,a.tablespace_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and a.segment_name='EMP' and a.owner='ORA1';
FILE_NAME OWNER SEGMENT_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------- --------------- ----------------
+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667 ORA1 EMP USERS
--查数据文件号
SQL> select file_name,file_id,bytes/1024/1024 from dba_data_files;
FILE_NAME FILE_ID BYTES/1024/1024
-------------------------------------------------------------------------------- ---------- ---------------
+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/system.276.1082994661 9 480
+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/sysaux.277.1082994661 10 540
+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/undotbs1.275.1082994661 11 190
+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667 12 23.75
+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/tsp_test01.dbf 33 10
--损坏块659号
RMAN> recover datafile 12 block 659 clear;
Starting recover at 14-JAN-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=401 device type=DISK
Finished recover at 14-JAN-23
SQL> conn ora1/ora1@orclpdb;
Connected.
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 659)
ORA-01110: data file 12:
'+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667'
说明：此时已无法查询ora1.emp表.
SQL> set numw 20
SQL> set line 200
SQL> r
1* select * from v$database_block_corruption
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
-------------------- -------------------- -------------------- -------------------- --------- --------------------
12 659 1 18446744072549497550 CORRUPT 3
说明：如上所示，成功模拟出块损坏，表ora1.emp无法查询.CORRUPTION_CHANGE#非0表示逻辑损坏.
2、导出数据
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> alter system set events='10231 trace name context forever,level 10';
Session altered.
SQL> select count(*) from ora1.emp;
COUNT(*)
----------
140
说明：如上所示，损坏659号块，丢失20行数据，生产环境绝对是不允许的.
[oracle@dbserver ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@dbserver ~]$ exp ora1/ora1@orclpdb file=emp.dmp tables=emp log=exp.log
Export: Release 19.0.0.0.0 - Production on Sat Jan 14 20:41:11 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 140 rows exported
Export terminated successfully without warnings.
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 14 20:43:00 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> conn ora1/ora1@orclpdb
Connected.
SQL> drop table emp;
Table dropped.
3、导入数据
[oracle@dbserver ~]$ imp ora1/ora1@orclpdb file=emp.dmp tables=emp log=imp.log
Import: Release 19.0.0.0.0 - Production on Sat Jan 14 20:44:25 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Export file created by EXPORT:V19.00.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ORA1's objects into ORA1
. importing ORA1's objects into ORA1
. . importing table "EMP" 140 rows imported
Import terminated successfully without warnings.
说明：此时会丢失坏块对应的数据.
