一、/*+ append */直接路径加载，冷备恢复后如何挽救数据实验
1.删除归档
SQL> startup mount
ORACLE instance started.
SQL> alter database open resetlogs; ---先重置归档
Database altered.
[oracle@testsvr ~]$ cd /oracle/archlog
[oracle@testsvr archlog]$ rm -rf * ---删归档
[oracle@testsvr archlog]$ ls -rtl
total 0
2、做冷备份
[oracle@testsvr ~]$ cd /oracle/oradata/zwb
[oracle@testsvr zwb]$ ls -rtl
total 1091832
-rw-r----- 1 oracle oinstall 7061504 May 24 00:12 control01.ctl.bak
-rw-r----- 1 oracle oinstall 7061504 May 24 00:12 control01.ctl.beifen
-rw-r----- 1 oracle oinstall 7061504 May 24 00:12 standby01
-rw-r----- 1 oracle oinstall 20979712 May 24 00:12 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 25 21:38 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 25 21:38 redo01.log
-rw-r----- 1 oracle oinstall 6561792 May 25 21:45 users01.dbf
-rw-r----- 1 oracle oinstall 31465472 May 25 21:45 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 May 25 21:45 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 May 25 21:45 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 25 21:45 redo03.log
-rw-r----- 1 oracle oinstall 104865792 May 25 21:45 example01.dbf
-rw-r----- 1 oracle oinstall 7389184 May 25 21:45 control03.ctl
-rw-r----- 1 oracle oinstall 7389184 May 25 21:45 control02.ctl
-rw-r----- 1 oracle oinstall 7389184 May 25 21:45 control01.ctl
[oracle@testsvr zwb]$ cp * /oracle/bak
3、重新打开数据库先切归档
[oracle@testsvr zwb]$ sqlplus '/as sysdba'
SQL> startup
ORACLE instance started.
SQL> alter system switch logfile;
System altered.
4、做插数据切归档
SQL> conn zwb/zwb
Connected.
SQL> select count(*) from t1; ---最初有19836条记录
COUNT(*)
----------
19836
SQL> insert into t1 select * from t1; --插数据
19836 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1; ---插完后39672条记录
COUNT(*)
----------
39672
SQL> alter system switch logfile; ---再切归档
System altered.
5、查看redo的使用量
SQL> create table t2 as select * from t1 where rownum<20000; ---先再建张表
Table created.
SQL> col name for a30
SQL> set linesize 100
SQL> select t1.sid,t2.name,t1.value from v$mystat t1,v$statname t2 where t1.STATISTIC#=t2.STATISTIC# and t2.name like '%redo%'; ---查看插入操作的redo使用量
SID NAME VALUE
---------- ------------------------------ ----------
214 redo synch writes 3
214 redo synch time 2
214 redo blocks read for recovery 0
214 redo entries 2
214 redo size 652
214 redo buffer allocation retries 0
214 redo wastage 0
214 redo writer latching time 0
214 redo writes 0
214 redo blocks written 0
214 redo write time 0
SID NAME VALUE
---------- ------------------------------ ----------
214 redo log space requests 0
214 redo log space wait time 0
214 redo log switch interrupts 0
214 redo ordering marks 0
214 redo subscn max counts 0
6、模拟此时发生普通大批量插入操作
SQL> insert into t1 select * from t2;
19999 rows created.
SQL> commit;
Commit complete.
SQL> select t1.sid,t2.name,t1.value from v$mystat t1,v$statname t2 where t1.STATISTIC#=t2.STATISTIC# and t2.name like '%redo%';
SID NAME VALUE
---------- ------------------------------ ----------
214 redo synch writes 5
214 redo synch time 3
214 redo blocks read for recovery 0
214 redo entries 752
214 redo size 1138772 ----此次插入redo使用量1138772
214 redo buffer allocation retries 0
214 redo wastage 0
214 redo writer latching time 0
214 redo writes 0
214 redo blocks written 0
214 redo write time 0
SID NAME VALUE
---------- ------------------------------ ----------
214 redo log space requests 0
214 redo log space wait time 0
214 redo log switch interrupts 0
214 redo ordering marks 0
214 redo subscn max counts 0
16 rows selected.
7、比较使用直接路径加载插入操作
SQL> col name for a30
SQL> set linesize 100
SQL> alter table t1 nologging;
Table altered.
SQL> insert /*+ append */ into t1 select * from t2; ---直接路径加载
19999 rows created.
SQL> commit;
Commit complete.
SQL> select t1.sid,t2.name,t1.value from v$mystat t1,v$statname t2 where t1.STATISTIC#=t2.STATISTIC# and t2.name like '%redo%';
SID NAME VALUE
---------- ------------------------------ ----------
214 redo synch writes 3
214 redo synch time 1
214 redo blocks read for recovery 0
214 redo entries 58
214 redo size 9820 ----此次插入redo使用量9820，明显比普通插入少，速度快
214 redo buffer allocation retries 0
214 redo wastage 0
214 redo writer latching time 0
214 redo writes 0
214 redo blocks written 0
214 redo write time 0
SID NAME VALUE
---------- ------------------------------ ----------
214 redo log space requests 0
214 redo log space wait time 0
214 redo log switch interrupts 0
214 redo ordering marks 0
214 redo subscn max counts 0
16 rows selected.
SQL> select count(*) from t1;
COUNT(*)
----------
79670
8、再切归档，模拟数据库当机
SQL> alter system switch logfile;
System altered.
SQL> conn /as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
9、冷备恢复，重新打开数据库
[oracle@testsvr bak]$ cp * /oracle/oradata/zwb
oracle@testsvr zwb]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 25 22:41:03 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
SQL> recover database using backup controlfile until cancel; ---不完全恢复
SQL> alter database open read only; --再打开数据库
10、再查t1表
SQL> select count(*) from t1; ---再查t1表，报数据块损坏错误
select count(*) from t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1097)
ORA-01110: data file 4: '/oracle/oradata/zwb/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
11、导出要挽救数据
SQL> desc dba_extents
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='ZWB' and SEGMENT_NAME='T1' order by 1;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 4 409 8
1 4 417 8
2 4 425 8
3 4 433 8
4 4 441 8
5 4 449 8
6 4 457 8
7 4 465 8
8 4 473 8
9 4 481 8
10 4 489 8
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
11 4 497 8
12 4 505 8
13 4 513 8
14 4 521 8
15 4 529 8
16 4 649 128
17 4 777 128
18 4 1033 128
19 4 1161 128
20 rows selected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@testsvr zwb]$ exp zwb/zwb tables=t1 file=/oracle/tmp/t1.dmp ---使用exp导出t1表
SQL> alter database open resetlogs; --打开数据库
Database altered.
SQL> conn zwb/zwb
Connected.
SQL> alter table t1 rename to t1_bak; --保险做法
Table altered.
12、导回数据（重点）
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@testsvr zwb]$ imp zwb/zwb file=/tmp/t1.dmp full=yes ---导回数据
Import: Release 10.2.0.1.0 - Production on Fri May 25 22:59:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing ZWB's objects into ZWB
. importing ZWB's objects into ZWB
. . importing table "T1" 59620 rows imported ---可以看到59620条数据挽回
Import terminated successfully without warnings.
13、结果
SQL> conn zwb/zwb
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
59620
结论：t1表已可打开，/*+ append */直接路径加载前操作的数据已挽回
注意：强制/*+ append */写归档的方法
SQL> select force_logging from v$database; --oracle默认no
FOR
---
NO
SQL> alter database force logging; ---打开后强制写归档
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
转载于:https://blog.51cto.com/maoxianwazi/878555
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
